8A-i

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE-CYCLE COST AND 

PAYBACK PERIOD 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 LIFE-CYCLE COST AND PAYBACK 

PERIOD 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 

regulations.gov, docket number EERE-2011-BT-STD-0006 at 

www.regulations.gov/#!docketDetail;D=EERE-2011-BT-STD-0006. 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 www.decisioneering.com. 

 The spreadsheet posted on regulations.gov represents the latest version of the LCC and 

PBP analysis model. It has been tested with Microsoft Excel 2010 and Crystal Ball 11.1.2.2. 

8A.2 LCC AND PBP WORKBOOK 

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

LCC Summary (Sum) Contains the input selections and a summary table 

including energy use, operating costs, installed costs, LCC, 

LCC savings, and PBP for each selection. This worksheet 

also works as an interface between user inputs and the rest 

of the worksheets. 

Import Parameters (Para) 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. 

Lighting Controls (Cont) Contains relevant statistics and assumptions regarding the 

prevalence and lumen reduction of lighting controls, as 

well as their effects on energy usage and lamp lifetime. 

Learning Curve (Learn) Contains the data to account for learning curve adjustments 

to the installed price and tables displaying installed prices 

adjusted for learning curves and rare earth factors for each 

product during each year. 

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

total discounted costs in each year, as well as tables 

detailing the years that require a lamp or ballast 

replacement. Tables are only relevant for the lamp type 

and/or product class selected by the user.
8A-2

Assumptions and Inputs (Assump) Includes sales tax data, labor costs, disposal costs, rare 

earth scenario values, ballast data (lifetimes, lamps per 

ballast), lifetimes used to calculate the analysis period, and 

the percent mean versus initial lumens for incandescent 

reflector lamps (IRLs). 

Electricity Prices (EPrices) Contains reference year electricity prices for the sampled 

households and Consumer Price Index values for scaling. 

Electricity Trends (EPriceTrend) Contains the electricity price trends for the reference-, 

high-, and low-economic-growth scenarios based on the 

U.S. Energy Information Administration’s Annual Energy 

Outlook 2012. 

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

distribution of discount rates are determined. 

Commercial Op. Hours (ComOH) Contains Commercial Building Energy Consumption 

Survey data and 2010 US Lighting Market Characterization 

(2010 LMC) data from which average commercial 

operating hours and a distribution of commercial operating 

hours for each lamp type are determined. 

Residential Op. Hours (ResOH) Contains Residential Energy Consumption Survey data and 

2010 LMC data from which average residential operating 

hours and a distribution of residential operating hours for 

each lamp type are determined. 

Industrial Op. Hours (IndOH) Contains Manufacturing Energy Consumption Survey data 

and 2010 LMC data from which 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 Sum. 

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 Sum. Select choices from the various inputs listed under the “User 

Input” heading. 
8A-3

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) Lamp 

Type, (3) Product Class, (4) Baseline Lamp, (5) Sector, (6) Number of Lamps, (7) Ballast 

Type, (8) Electricity Price Trend, (9) Start Year, (10) Learning Curve (11) Multiple of 

Baseline Service Life (for Analysis Period), (12) Lamp Price, (13) Ballast Price, (14) 

Rare Earth Scenario, (15) Residential Lamp Preservation (select lamp preservation Avg. 

Op Hours or High Op. Hours scenario, and select user-specified Remaining Fixture Life 

at AP Start), (12) Op. Hrs Sensitivity (Low Op. Hours or High Op. Hours), (13) New 

Construction Spacing (No spacing adjustments or spacing adjustments), (14) Base Case 

Dimmer Selection (No Base Case Dimmers, Base Case Dimmers, or Distribution of Base 

Case Dimmers), and (15) Equalize Lumens Selection (Equalize Lumens at Each Level or 

Dim By Same Percentage at Each Level). 

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 “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 “Crystal Ball” 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 Start from the Run menu 

(on the Crystal Ball toolbar). 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 Start can be selected again. Once Crystal 

Ball has completed its run sequence, it will produce a series of distributions. Using the Analyze 

menu on the Crystal Ball Excel tab, 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. The following outlines a step-by-step summary of the procedure for running a 

distribution analysis: 

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

2. Click on Start from the Run menu bar 

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

a. Monte Carlo (recommended) 

b. Latin Hypercube 
8A-4

c. Initial seed choices and whether or not to be constant between runsa

d. Select number of Monte Carlo Trials (1,000 was used for this analysis) 

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. 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 Crystal Ball toolbar or 

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 LCC savings and PBPs 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 for each chart can be used to specify whether you 

want cumulative or frequency plots shown. 

3. To calculate the certainty that the LCC savings will be above a particular value, either 

type that value in the box by the left arrow at the bottom of the chart window, or move 

the arrow key with the cursor to that value on the scale. The value in the Certainty box 

shows the likelihood that the LCC savings will be above that value. To calculate the 

certainty of the PBP 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 Analyze 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. 

5. To determine an average of the results reported by Crystal Ball for a forecasted value, 

select Extract Data from the Analyze menu and calculate the average of the trial values 

reported for that variable. 

 

a 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. The same sequence of random numbers should be used to reproduce results 

consistent with the presented values. To do this, select “Use same sequence of random numbers” and an initial seed 

value of 999 in the sampling tab.