Cash Flow and Life Cycle Cost Model
For Financing Energy Efficiency & Conservations
(Version 1.0, release date 01/22/2007)
User should only input data in cells shaded light green
Step 1: Input Tariffs Data ("Tariffs" Worksheet) Enter tariffs data on the Tariffs Worksheet for each electricity tariff that applies to your facilities.
- Rows 8-17: Enter non-TOU tariffs (if applicable).
- Rows 24-42: Enter TOU tariffs (if applicable).
- If facility has some ECMs on TOU tariffs and some on non-TOU tariffs, then enter the data for both.
- Both Non-TOU and TOU sections follow a similar format:
- Row 8 and 24: This is an identifier used in the model to correlate the tariff information on this worksheet to calculations within the model. No user input is required.
- Row 9 and 25: Enter the utility name. You may use abbreviations; these are merely to help you identify the tariffs entered.
- Row 10 and 26: Enter the name of the tariff. Abbreviations are fine.
- Row 11 and 27: Enter the number of summer months for that tariff (required).
- Enter the current tariff prices in the appropriate rows (required).
Step 2: Input ECM Data ("User Inputs" Worksheet)
Enter each ECM in a separate column. The model will track the ECMs by columns.
- "General Assumptions" Section:
- Row 8: Enter ECM name (required).
- Row 11: Enter years of analysis in cell J11 (required). You may change this entry later as discussed under Step 3. This number tells the model how many years of cash flow analysis to generate. It applies to all ECMs. If the years entered in J11 exceed the life of an ECM, the cash flow after the end of the useful life will simply be treated as zero.
- Row 12: Enter the first full calendar year in which the ECM will be operational.
- Row 18: Answer Y or N to "Is the ECM photovoltaic?" A degradation factor is applied to the output if ECM is photovoltaic. This degradation factor is listed in the "DGS Inputs" sheet.
- "ECMs That Save Gas" Section:
Skip this section if it doesn't apply to your ECM.
- Row 24: Enter the name of the gas utility. Abbreviations are fine.
- Row 25: Enter the name of the gas tariff. Abbreviation is fine.
- Row 27: Enter the average gas price the facility paid for the past 12 months in $/therm.
- Rows 37 and 40: Enter the estimated gas therms that the ECM will save as follows:
- If you know the usage before and after ECM, enter annual pre-ECM therms usage under "business as usual" in row 37 and the annual post-ECM therms usage under ECM in row 40. The model will calculate the difference as the annual therms saved.
- If you only know the savings, then enter the estimated annual therms of savings from the ECM in row 37. The model will treat the entry as the therms saved.
- "ECMs That Save Electricity" Section:
This section of inputs is split into two categories, those under TOU tariffs and those under non-TOU tariffs.
- ECMs that save electricity on different meters and different tariffs must be entered as separate ECMs in separate columns in order for the model to correlate the correct tariff with each ECM and its savings.
- Row 49: Answer Y or N to "Is ECM under a TOU tariff?"
- Row 50: Answer Y or No to "Does tariff have a demand ratchet?" If tariff has a demand ratchet, the model assumes that the ratchet is approximately 12 months, and the first year demand savings are eliminated.
- Row 59-77: Input data for non-TOU ECMs
- Row 84-125: Input data for TOU ECMs.
- For ECMs that will save electricity on a non-TOU tariff:
- Enter the electric utility tariff identifier from Row 8 on "Tariffs" for each non-TOU ECM entered.
- Rows 66-74: Enter the electricity savings, demand and usage for each ECM:
- If you know the usage both pre-ECM and post-ECM, enter the pre-ECM usage under "Business as Usual" in rows 66 and 67 and the post-ECM usage under ECM in rows 73 and 74.
- If you only know the savings, enter the savings in rows 66 and 67 under "Business as Usual."
- For ECMs that will save electricity on a TOU tariff:
- Row 84: Enter the electric utility tariff identifier from Row 24 on "Tariffs" for each TOU ECM entered.
- Rows 94-125: Enter the electricity savings, demand and usage for each ECM.
- If you know the usage both pre-ECM and post-ECM, enter the pre-ECM usage under "business as usual" in rows 94 through 107 and the post-ECM usage under ECMs in rows112 through 125. Both numbers should be positive. The model will calculate the difference as savings.
- If you only know the savings, enter the savings as a positive number in rows 94 through 107. The model will treat that entry as the savings.
- "Capital Cost and Financing" Section:
For each ECM:
- Row 131: Enter the effective date of the construction cost estimate. This is needed in order to escalate the ECM cost estimate to actual costs. If the estimate is already in actual costs that will be incurred, see instruction b. that follows.
- Row 132: Enter the number of months from the effective date of the construction cost estimate until the start of construction (to escalate construction cost). If no escalation is required, input 0 as the number of months.
- Row 133: Enter construction cost estimate.
- Row 134: Enter any "soft costs" (e.g. overhead, contract management).
- Row 135: Enter the number of months from the date of loan closing to completion of construction. The model uses this number to calculate capitalized interest, UNLESS you choose to calculate capitalized interest outside the model and input yourself. (See g. below.)
- Row 136: Enter any applicable rebates as a negative number.
- Row 137: Enter Y if you want to input the capitalized interest rather than let the model calculate it.
- Row 138: Enter the amount of capitalized interest, only if you have input Y in the above row.
- Debt Terms for the Project
- Rows 141-143: The model assumes that the entire project of ECMs will be financed together and thus enters financing data for the project as a whole. Enter the portion of the project costs that will be debt-financed (usually 100%) in cell J141, the term of the debt (years) in cell J142, and the expected interest rate in cell J143.
- "O&M and Other Costs" Section
- Row 148: Enter routine O&M. This amount may be zero.
- Rows 150-151: Enter Major Repair and Replacements. If these costs exist, you must enter frequency (years) of these repairs or replacements in row 150 along with the cost in row 151. These costs may be zero. For example, if an ECM has major repair and replacement cost of $1000 every 3 years, the model will put in $1000, escalated to the current year, every three years. (For now, if an ECM has more than one component of these costs, you must make a separate calculation outside the model and enter the average cost and years).
- Row 155-158: Enter any O&M and major repair expenses avoided by the ECM. These may be zero.
- Rows 163-164: Finally, if the ECM has any other incremental costs associated with it such as G&A, insurance, etc., enter those costs in Row 163 (or 163 and 164) if you have pre-ECM and post-ECM data). All of these entries may be zero.
Step 3: Run the Model
- Row 6: Select the ECM(s) to be analyzed by entering in cell G6 the individual ECMs number or a group of ECMs numbers to analyze as a "project."
- Any combinations of ECMs can be entered as a "project."
- ECMs are identified for the selection by the ECM numbers in row 9.
- ECM numbers in G6 must be separated by commas but consecutive ECMs numbers can be entered with a dash (e.g., 2,5-8,15 would combine ECMs 2, 5, 6, 7, 8 and 15 in a "project" to run). If you have entered 18 ECMs in the first 18 columns and want to "project" all of the ECMs for a particular model run, you may enter 1-18 in G6.
- Row 11: Check the years of analysis in cell J11. Change as appropriate for the particular run of the model.
- Go to the "Project Analysis" sheet and click the "AccumulateResults" button in row 1 under columns J, K and L. The model will not recalculate until you click the Accumulate Results!
- You can at any time go back to "User Input" and select a different set of ECMs or "project" of ECMs and click again on the "AccumulateResults" button to analyze a different set of ECMs.
- Sensitivity Tables - On both the "Project Analysis" and "ECM Analysis" sheets, the Sensitivity Tables indicate how differences in the escalation of electric (or gas) prices will impact your Project's or ECM's Net Present Value Savings, Nominal Savings, and Payback Period. To see a finer or broader spectrum of escalation rates, you may change the "Table Interval" in Cell P222 on the ECM Analysis sheet, which is highlighted in light green. Any change you make to the Table Interval on the ECM Analysis sheet will automatically carry over to the Project Analysis sheet. However, please note that to fully update the Sensitivity Table on the Project Analysis sheet after making a change to the table interval on the ECM Analysis sheet, you must click the AccumulateResults button at the top of the Project Analysis sheet.
VERY IMPORTANT: Do not change any numbers in any of the Sensitivity Tables other than the Table Interval cell which is highlighted in light green. All of the other figures are fixed based on DGS Inputs and should not be altered.
- Printing Results: You can print out the results of running the LCCA model as follows:
- To print out a summary of the project's cash flow savings, financial measures, and kW, kWh, and therms saved, print the worksheet titled "Project Summary Printout".
- To print out a summary of the PV and nominal cash savings, financial measures, and energy saved of each individual ECM in the project, click button "Click to Print Summary of Individual ECMs".
- To print out sensitivity tables showing how the NPV and nominal savings are impacted by varying electric price and gas price escalation, print the worksheets titled "Sensitivity Table 1" and "Sensitivity Table 2".
- Clearing Input Values: If you want to clear all input values in the "User Inputs" worksheet, click on the purple button, in column L at the top of the sheet. This is not advised unless you are starting to work on a new project and will be inputting all new ECMs. If you do clear the inputs, you should save the existing workbook under a new name before proceeding.