8A-i

APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS 

TABLE OF CONTENTS 

8A.1 INTRODUCTION .................................................................................................. 8A-1

8A.2 LCC AND PBP WORKBOOK .............................................................................. 8A-1

8A.3 BASIC INSTRUCTIONS....................................................................................... 8A-2
8A-1

APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS 

8A.1 INTRODUCTION 

It is possible to examine and reproduce the detailed results of the life-cycle cost (LCC) 

and payback period (PBP) analyses using Microsoft Excel® spreadsheets available on the U.S. 

Department of Energy (DOE) website at: 

http://www.eere.energy.gov/buildings/appliance_standards/. Running the spreadsheet sample 

calculations requires Microsoft Excel. Additionally, Crystal Ball® software is required to run 

the Monte Carlo simulations. Both applications are commercially available. Crystal Ball is 

available at http://www.decisioneering.com. 

 The spreadsheet posted on the DOE website represents the latest version of the LCC and 

PBP analysis model. It has been tested with Microsoft Excel 2007 and Crystal Ball 11.1.1.1. 

8A.2 LCC AND PBP WORKBOOK 

LCC and PBP spreadsheet or workbook consists of the following worksheets: 

LCC Summary Contains the input selections and a summary table of energy use, 

operating costs, LCC, and PBP for each selection. This worksheet 

also works as an interface between user inputs and the rest of the 

worksheets – do not modify this sheet. 

Import Parameters Contains the engineering analysis as well as all parameters 

imported into the LCC summary page. Tables of results are only 

relevant for the lamp type and/or product class selected by the user. 

Plots Contains plots of the rated efficacy versus the rated watts of all 

lamps used in the LCC for each lamp type and product class. 

Efficacy levels are also shown in the plot. Note that the plotted 

efficacy levels are based on lamp catalog values and do not 

incorporate manufacturing variability. 

Annual Cash Flow Contains tables of the equipment costs, operating costs and total 

discounted costs in each year. Tables are only relevant for the 

lamp type and/or product class selected by the user.

Assumptions and Inputs Includes sales tax data, labor costs, ballast data (system power 

ratings, ballast prices, lamps per ballast), T12 to T8a

 substitution 

 

a

 T12 refers to tubular fluorescent lamps with diameters of twelve-eighths of an inch. T8 refers to tubular 

fluorescent lamps with diameters of eight-eighths of an inch. 
8A-2

ratios, lifetimes used to calculate the analysis period, and the 

percent mean versus initial lumens for incandescent reflector lamps 

(IRL). 

Electricity Prices Contains reference year electricity prices for the sampled 

households. 

Electricity Price Trend Contains the electricity price trends for the reference-, high-, and 

low-economic-growth scenarios based on the April 2009 AEO2009

with impacts of the American Recovery and Reinvestment Act of 

February 2009 (Pub. L. 111-5). 

Discount Rates Contains data from which an average discount rate and a 

distribution of discount rates are determined. 

Commercial Op. Hours Contains CBECS data and LMC Vol. I data from which an average 

commercial operating hours and a distribution of commercial 

operating hours for each lamp type are determined. 

Residential Op. Hours Contains RECS data and LMC Vol. I data from which an average 

residential operating hours and a distribution of residential 

operating hours for each lamp type are determined. 

Industrial Op. Hours Contains MECS data and LMC Vol. I data from which an average 

industrial operating hours and a distribution of industrial operating 

hours for each lamp type are determined. 

8A.3 BASIC INSTRUCTIONS 

Basic instructions for operating the LCC spreadsheets are as follows: 

1. Once you have downloaded the LCC spreadsheet file from the web, open the file using 

Excel. Click on the tab for the worksheet named LCC Summary. 

2. Use Excel’s “View/Zoom” commands at the top menu bar to change the size of the 

display to make it fit your monitor. 

3. Interact with the spreadsheet by clicking choices or entering data using the interface on 

the worksheet named LCC Summary. Select choices from the various inputs listed under 

"User Input" heading. 

4. Under the "User Input" heading, select choices from the selection buttons and boxes for 

the following: (1) Type of calculation (Sample Calculation or Crystal Ball), (2) Product 

Class, (3) Lamp Type, (4) Baseline Lamp, (5) Sector, (6) Electricity Price Trend, (7) Start 
8A-3

 

Year, (8) Multiple of Baseline Service Life (for Analysis Period), (9) Lamp Price, (10) 

Ballast Price, (11) Op. Hrs Sensitivity (Low Op. Hours or High Op. Hours), (12) 

Residential Lamp Preservation (select lamp preservation Avg. Op Hours or High Op. 

Hours scenario, and select automatically-computed or user-specified Remaining Fixture 

Life at AP Start), and (13) LCC Subgroup (Low Income Consumers, Institutions of 

Religious Worship, Institutions Serving Low Income Populations, and Consumers of T12 

Electronic Ballasts). 

5. To change inputs listed under "User Input", select the input you wish to change by either 

clicking on the appropriate button/checkbox or selecting the appropriate input from the 

input box. 

6. This spreadsheet gives the user two types of calculation methods: 

a. If the "Sample Calc" is selected, then all calculations are performed for single-input 

values, usually an average. The new results are shown on the LCC Summary 

worksheet as soon as the new values are entered. 

b. Alternately, if the "CB Calculation" is selected, the spreadsheet generates results that 

are distributions. Some of the inputs are also distributions. The results from the LCC 

distribution are shown as single values and refer only to the results from the last 

Monte Carlo sample and are therefore not meaningful. To run the distribution version 

of the spreadsheet, the Microsoft Excel add-in software called Crystal Ball must be 

enabled. 

 To produce sensitivity results using Crystal Ball, simply select Run from the Run menu 

(on the menu bar). To make basic changes in the run sequence, including altering the number of 

trials, select Run Preferences from the Run menu. After each simulation run, the user needs to 

select Reset (also from the Run menu) before Run can be selected again. Once Crystal Ball has 

completed its run sequence it will produce a series of distributions. Using the menu bars on the 

distribution results, it is possible to obtain further statistical information. The time taken to 

complete a run sequence can be reduced by minimizing the Crystal Ball window in Microsoft 

Excel. A step-by-step summary of the procedure for running a distribution analysis is outlined 

below: 

1. Find the Crystal Ball toolbar (at top of screen) 

2. Click on Run from the menu bar 

3. Select Run Preferences and choose from the following choices: 

a. Monte Carlob

b. Latin Hypercube (recommended) 

c. Initial seed choices and whether you want it to be constant between runs 

b

 Because of the nature of the program, there is some variation in results due to random sampling when Monte Carlo 

or Latin Hypercube sampling is used. 
8A-4

d. Select number of Monte Carlo Trials 

4. To run the simulation, select the green right arrow (“Start Simulation”) button on the 

Crystal Ball toolbar or select “Start Simulation” from the Run menu. 

5. Now wait until the program informs you that the simulation is completed. Note that to 

run a new simulation, “Reset Simulation” must first be selected from the Run menu. 

The following instructions are provided to view the output generated by Crystal Ball. 

1. After the simulation has finished, to see the distribution charts generated, select the 

Crystal Ball window in the Windows task bar. 

2. The life-cycle cost savings and payback periods are defined as Forecast cells. The 

frequency charts display the results of the simulations, or trials, performed by Crystal 

Ball. Click on any chart to bring it into view. The charts show the low and high 

endpoints of the forecasts. The View selection on the Crystal Ball toolbar can be used to 

specify whether you want cumulative or frequency plots shown. 

3. To calculate the probability that a particular value of LCC savings will occur, either type 

“0” in the box by the left arrow, or move the arrow key with the cursor to “0” on the 

scale. The value in the Certainty box shows the likelihood that the LCC savings will 

occur. To calculate the certainty of payback period being below a certain number of 

years, choose that value as the high endpoint. 

4. To generate a printout report, select Create Report from the Run menu and then select the 

charts and statistics that you are interested in. For further information on Crystal Ball 

outputs, please refer to Understanding the Forecast Chart in the Crystal Ball manual.