Text-Alternative: Version Retrofit Financial Analysis Tool Videos
Following is a text version of the DOE Municipal Solid-State Street Lighting Consortium's Retrofit Financial Analysis Tool video chapters.
Chapter 1: Introduction
Emma Berndt, Program Director, Clinton Climate Initiative: What we've been working on with the Municipal Solid State Lighting Consortium is an economic cost benefit-modeling tool. It's Excel based and we're actually going to take a look at it today.
This model is meant to give you sort of a general sense of what a project might look like. So it's for a city to use once they have a sense of what they might want to do with their streetlights. And this model can help you understand what are the costs involved. What are the kilowatt-hour savings, the dollar savings, the greenhouse gas emissions reductions and also how does finance sort of layer onto all of this.
So it will help you analyze different financing options. And we're going to try and sort of go through a hypothetical case today that will take you through this model and show you how it works. These are the key inputs that if you were thinking about okay, how do I do this? How do I look at the economics of a project? This is where you'd want to start. You'd want to start collecting these data points and we'll see those in the model that we look at in a minute. So I thought it would make it a little more interesting today if we took a look at a hypothetical case.
So we have the very cleverly named City X and they want to evaluate a project so they've done some work. They have a sense of what they want to do, someone in their city, maybe their mayor's office, has asked them, that's nice, how much is this going to cost? And how much are we going to save? And are there any greenhouse gas emissions benefits? And so they're interested in looking at replacing 10,000 high pressure sodium lamps at 100 watts with 50 watt LED lights.
And they want to do it over a period of three years, which adds a little complication because they don't want to put them all in year one. They want to sort of phase it in. So what does that mean for our economic model? And again, this is all just made-up numbers to give us something to look at today. And we've been asked to give them these answers and so that's what we're going to try and do right now.
There's a landing page for you. So if you were to download this and you're like, what do I do with this? There's an intro page that describes how the model works and describes its functionality. So it looks at three main things. It's going to look at sort of the cost, the economics of your analysis. It's going to look at the environmental impacts and then it has a financing component.
So once you know sort of roughly what your project costs, if you want to play around with debt and equity, there's some money you're going to get as rebates. Some you're going to get that doesn't need to be repaid and some you're going to have to borrow. You can play around with all those mixes to see if your energy savings for instance can cover the cost of your borrowing, things like that. So it has that functionality that's optional.
The two pages we really want to focus on are input and output pages. And the other thing I'll mention is it has a definitions page, so when you open it up I think you'll see about five tabs. The definitions page is just a reference. So when you're on the input page and you're thinking, "I don't know what they're asking for," you can flip over to the definitions page and there's more detail on what type of input we're looking for.
There are two optional pages. So the finance page I mentioned is optional. You don't need to use that to run your results of your model and it's not going to change the results of your model.
Chapter 2: Input, Part 1
Let's just start on the input page. And this is where you land when you need to start putting in your variables. The input page is three main categories of information we're going to look for. One is just general information, which is what you see here at the top, so things like your electricity price, your sales tax, so pretty general and non-specific to your street lighting inventory.
When you scroll down you're going to come to a second category of information. This is where you're going to enter information about your street lighting system. So specific information related to its operations and its maintenance. And this is a section where there are a lot of cells. I'm not going to count them right now, but you'll see this section extends down really long. The reason why is this is not driving your results right now.
This is just collecting data and the model then knows how to talk to this section and pull what you tell it to pull. And so this is a great place to log all your data about your street lighting inventory because this will allow you to look at basically anything you want to look at.
If you log your whole system and any technologies you might want to look at, this section will then capture that and then going forward it will be really easy to just toggle. And where you toggle is down below. So at the bottom this is what's driving your results. This is where you're going to tell the model what to do in this third section down here.
It says, "high pressure sodium 100 watt, new technology LED 50 watt." You can enter the exact number of fixtures you want to look at. City X said 10,000 so we put in 10,000, so pretty straightforward. That's the section where you want to be careful because that's what's driving your results right now.
But if we quickly go up, I did want to go through the assumptions that we're making because I think it'll help you evaluate the results a little bit better when we see the results page. So just to give you a sense of what is happening in City X and you can think as you go through it if this matches or doesn't match your own city's experience.
City X pays no sales tax. Lucky for them, they're exempt. And they have about a ten percent electricity rate, that's fairly high, but they're paying ten cents per kilowatt hour in terms of their electricity price. And we do have an escalation rate going in this model so we're assuming that the city is not going to pay ten cents for the lifetime of the model, which is about 15 years. You can always shorten it. That's your choice.
So if you don't want to assume they're going to pay ten cents per kilowatt hour for all the 15 years you're looking at, you can escalate it. And that's what that three percent number in there is — escalating the cost of electricity a little bit every year.
If you don't believe that's what is going to happen you can zero that out. You can make it one percent. Whatever makes the most sense. The next set of inputs have to do with your installation costs. So this is where you're going to capture not the price of the fixture but the cost of actually installing these fixtures in your city so that you're going to have a labor rate, which is going to capture the rate per hour of labor that you're using to install the fixtures, and a vehicle rate because most likely you're going to use a vehicle to go out there and do it. And you have the choice also to inflate those costs over time.
City X wants to do this over a three-year period. It could be it'll be more expensive to do those installations in year three. If you want to try and capture that you can by escalating it. If that's too complicated, you don't think that's worthwhile, just zero those numbers out.
And then there's a number that says finance discount rate. We'll look on the results page about where that number becomes relevant, this basically should match your city's cost of capital, its cost of borrowing roughly. Why we put this number in here is it does affect some of the results we're going to look at in a minute or so. So if a city wants to know what its net present value of all of these investments is, figures like that, this is where your discount rate comes into play.
It's reflecting the fact that money you receive next year or three years from now is not in dollar value equivalent to what you would receive today. And that's just because if you got a dollar today, I could invest it; earn some interest. Three years from now it would be worth more than a dollar. So any money you receive three years from now you need to discount back to the present value, if a city wants to know how much money should I be investing.
And we do also try and ask for your emissions factor in this model. This will help you analyze in the results page what your greenhouse gas emissions reduction, how much carbon you've reduced. And then we're typing in the years of implementation. This one and three is where we've specified the period of time that you want to look at for the installation of the project. So we could change it to two. If you wanted to do it all in one year you'd change the last year of implementation to one and you're done.
Chapter 3: Input, Part 2
One thing I did want to focus on right here is the technology types. These are user input. When you open this, this will be blank. It's blank for a reason. We want to give you the flexibility to look at anything you want to look at.
So right now City X asks for HPS and LED, but if you wanted to look at induction you would just type "induction" in this next box and you could look at that. Or you might want to break it out by arterial and have a whole separate category for arterial high-pressure sodium lights versus roadway versus alley versus pedestrian. You can do that. Any categories you want to create here is fine.
What it will do is if we scroll down now, you'll be able to select them in this. This is a drop box. Anything you type in the box above will appear down here. You're creating the categories that you want to look at. You haven't told it anything else. Now in this section is where you tell it more specifically what's associated with these categories. It's asking now to type in the lamp watts, so you can type in 100 watts. You can list HPS again and type in 250 watts or you could list 50. You can do this as many times as you want to get your whole inventory.
And then it'll ask for your system watts and your annual operating hours. That's pretty straightforward. It's going to ask for your fixture cost. It says current fixture cost, but this is the cost of the fixtures, the new and the old that you're expecting. So these are City X's expected costs.
And this is an interesting one. It gives you the ability to model a change in fixture cost. So again, this is only relevant if you are implementing a project over a period of time, but if you believe that the cost of LED is going to decline and you want to try and capture that in your model, you can model it so that you're sort of de-escalating them.
So here they're falling at five percent a year. If you don't like that, if your city thinks that's too risky you can zero it out. And then it's just going to be you're buying them each year that you want to buy them for the same price. So here we have LEDs. They're decreasing in cost at five percent a year. We have high-pressure sodium staying the same. That's what we're modeling for City X right now.
Install time — so you want to log how long you think it's going to take to add or install these fixtures. This will ultimately get multiplied by your labor and your vehicle rate and it'll produce your installation cost.
And a rebate value. So here we're lucky. City X will get a rebate for purchasing an LED fixture of $20.00 per fixture. So we're typing that in here and so this is where you would capture any rebates that you would get.
One more quick thing and then we'll get to the results. Maintenance costs — if you know what your dollar per unit per month or dollar per unit per year maintenance cost is for a particular fixture, you enter it here in these cells. If you don't, that's okay. We have a worksheet that can try and help you calculate it.
I'm not going to get into it today, but you go over here and you can select maintenance page. When I do that, you'll actually see in column V the maintenance cost has changed. I typed in 2.64. It's saying it's 2.37. That's because I filled out the maintenance page and I've come up with a different answer. So the maintenance page is only to help you get to this number.
If you don't know what it is, if you're pretty sure you know it, don't bother with the maintenance page. It's optional. Just ignore it. Leave it blank, but make sure you select user entered and type in the number you want in column T and it'll then pull it into column V. When I go back here and now I'll switch it back to user entered, you'll see that column T and V match again. So it now knows that it should pull whatever I put into column T into column V, which then gets pulled into the model. We're almost at the results.
I showed you what's below. That's where you tell it actually what to do. We've done this, but we haven't told the model what to do at all yet. So we've typed in all this data. We gathered it. We feel really good, but we still need to actually say what do you want to analyze and that's what this section below here is.
You're telling it now, okay, take the 100 watt high-pressure sodium. Take all that data I just entered above and replace it with all that data associated with 50 watt LED that I mentioned above. Now if you do that, you then get some results.
Chapter 4: Results
This is your results summary and the first thing you'll see is this box on the left-hand side. And after we typed in all that data, what the model can tell you first is what your simple payback is. So this isn't taking into account any cost of financing, although it is taking into account the rebate that we believe you can get for the LED fixtures.
That's calculating the simple payback, the stream of benefits, which are your energy and maintenance savings, how long it takes them plus the rebates to pay back your initial cost. We also now know that your project is expected to cost about $3.5, $3.6 million. So we didn't know that before.
Now we have a rough number of what this project might be expected to cost if you want to replace 10,000 lights using the assumptions that we used. You also have some other numbers here. You have your total annual kilowatt hour savings, so that's annualized. We're not using the escalation factor here.
You have your total energy cost savings, so that should actually have a little dollar sign, but that's how much you're saving in terms of your costs that you're not paying to your utility bill assuming City X owns their street lights and they're on to a new rate schedule that reflects the lower energy usage of this new technology.
And you can also calculate your greenhouse gas emissions reduction, so you see that here. If you want to see how these numbers are calculated, I'm going to scroll over here. This nicely lays out for you the simple cash flow. Year on year what's happening and how your simple payback was calculated.
So each year now it's showing you you're spending roughly $1.2 million because you're buying your fixtures in three years and you'll see that price going down because we have that five percent decrease in price each year. So that's partly what's driving that.
We have your rebate. So that's how much your rebate is worth each year. And you have your energy and maintenance savings. Basically when you net all those numbers out you get your cash flow. So that's just showing you basically year on year how those numbers total and then your cumulative cash flow. And your cumulative cash flow essentially is your payback.
So you're just adding up all of your negative numbers, how much you're spending each year and then how long it takes all those stream of benefits to pay it back. That's what you get with your cumulative cash flow line. So you should see in year nine that's when it goes positive because you have a roughly nine-year payback.
So that's how you can sort of fact check if you want to know how these numbers are adding up. You can do it over here. You don't just have to take the model's word for granted. And then finally if we scroll down, just one more way to look at your cumulative cash flow.
There's the little graph that I just wanted to show you. This is cumulative, so when you're summing up all of the expenditures and the benefits, this just shows you how they work and again where it crosses the axis should be where your payback is. We found often people just like visuals better.
And these are the types of numbers you can take to your city. They're rough numbers still. These are still pretty broad assumptions that we're making, but you can take these types of answers to your city and say okay. We do have a sense of how much our project would cost and we do think we know how much energy it would save year on year.
Chapter 5: Finance
The finance page looks kind of complicated. Once you spend a little time with it, it's pretty simple. What this top part is just doing is allow you — now you know you need to get about $3 million, $3.5 million, but you're not sure how you're going to pay for it. The rebate is already factored in here.
What it allows you to do is play around with two different types of monies: debt or equity. So we tried to make it kind of simple, so money that you're going to have to repay, at interest, and money that might be available to you through a grant, through a rebate, and separate those out.
So the equity money or any grant money goes up in this column. Any debt goes in below. It also allows you to borrow sequentially so you don't have to take out all the money necessarily. In year one for the project, you might not want to borrow all $3 million, so right now you're only borrowing the amount you need for that first year.
Next year, in year two, you're going to go back and borrow again. And it's sort of adding, it's sort of taking out loans sequentially. So that's what this model is showing right here and why the debt source has loan one, loan two, loan three, you can see there are three different loans starting in years one, two and three, all with a ten year term though. So they're all ending in year ten measured from that first year.
What this allows you to do, it produces a cash flow, which can be useful. And your cash flow tells a useful story. What this story is telling you here, you can ignore sources and uses on top. That's just the model checking to make sure if you have to pay a certain amount, we've identified the correct amount of sources for the project. So those numbers should match. If they don't, you have too much or too little money that you're spending on this project.
But the project operating cash flow is telling you a story. It's lining up year on year the amount of money you need to spend and any, we'll call them revenues. In this case, though, it's energy efficiency. So it's money you're not spending, but any benefits that the project is producing.
So where it says O&M savings total, savings cash flow, those are all the benefits you're receiving from the project so they're going to be listed up top. So these are the energy savings, your maintenance savings in dollar form, all lined up here. And then below where it says debt service payments, that's showing you how much debt you have to pay each year.
And what it's showing you is that we're a little shy. Your savings aren't quite enough to cover the cost of your debt payments. And we were borrowing I think at like roughly five percent above is what we were assuming. So you're a little shy for a five percent loan for ten years. Your energy savings can almost pay for the project each year, but not quite. You're a little bit shy, so in year one the city would have to come up with $20,000 because that's money they're going to have to pay if they borrow it and your project isn't going to produce that $20,000.
The reason you'll see the rebate up there and it's not factored in if you add up total savings cash flow and rebates and the rebate is not added in to your total cash flow available for debt service is up above we told the model that we didn't want to borrow that $66,000. So I'll show you where we do that and then that will be the last thing we look at, but it's not added in because we didn't borrow that $66,000.
So instead we used it to reduce the amount we have to borrow. The model is set up so you can change that pretty easily where it says "Show Rebate as Grant?" Probably not the easiest way to say it. "Yes, if received up front." That's sort of more what we're going after. So if you're going to get this money as a rebate right up front and you're not going to borrow it, then you say yes. If not, you say no.
All of a sudden we just increased the amount that we're borrowing per year. And we're using that rebate to pay back the borrowing, but that means we're also using it to pay interest on that money we just borrowed. But the model does give you that functionality so you can decide, depending on if you believe your rebate is going to come in fast enough, all those kinds of things, how to do that.