8A-i

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

PERIOD SPREADSHEET

TABLE OF CONTENTS

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

8A.2 MODEL CONVENTIONS ............................................................................................1

8A.3 INDIVIDUAL WORKSHEETS ....................................................................................2

8A.4 UNCERTAINTY ANALYSIS INSTRUCTIONS ........................................................3

EERE-2007-BT-STD-0016 COMMENT 25
8A-1

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

PERIOD SPREADSHEET

8A.1 INTRODUCTION

 The results obtained for the life-cycle cost (LCC) and payback period (PBP) analyses can 

be examined and reproduced using the Microsoft Excel spreadsheet available on the 

U.S. Department of Energy (DOE) Building Technologies website at: 

http://www1.eere.energy.gov/buildings/appliance_standards/residential/fluorescent_lamp_ballast

s.html. 

There is one spreadsheet covering all combinations of lamp and ballast system equipment 

classes. The spreadsheet posted on the DOE website represents the latest version that has been 

tested with both Microsoft Excel 2003 and 2007. To execute the spreadsheet in “sample 

calculation” mode requires Microsoft Excel 2003 or later. To execute fully in uncertainty mode 

or “CB Simulation,” the model requires the commercially available Crystal Ball add-in. The LCC 

and PBP spreadsheet performs calculations to forecast the change in LCC from an energy 

conservation standard and the PBP that such a change implies. These concepts are explained in 

the main body of preliminary technical support document (TSD) chapter 8. 

To operate the spreadsheet, a user simply describes the florescent lamp and ballast system 

desired for examination. Any market and energy price behaviour can be set by adjusting the 

fields in the “User Input” box in the LCC Summary sheet. In “sample calculation” mode, the

model does not need to be “run” in any way. All permutations are presented in parallel and all 

fields are calculated in real-time, so no further action is necessary. Therefore, strictly speaking, 

there are no “instructions” necessary to operate the spreadsheet. Rather, in this appendix, DOE 

provides a description of the model in case users wish to examine DOE’s assumptions and 

methods or to test alternative assumptions. However, users who desire uncertainty analysis 

results can follow the instructions in section 8A.4. 

8A.2 MODEL CONVENTIONS

Both of the model’s primary outputs, LCC savings and PBP, are calculated on the LCC 

worksheet by using base case and standards case as inputs (e.g., the LCC for a particular lamp 

and ballast system is calculated by subtracting the LCC associated with that unit from the LCC 

associated with the same unit at CSL 0). 

In general, logic flows from data sources and assumption (assembled on the right-most 

worksheets) toward outputs (produced on the left-most worksheets). Further data carried from 

one sheet and reproduced in another are specifically called “Export Parameter” and generally 

presented in a box in the upper left- or upper right-hand side of a worksheet. 

EERE-2007-BT-STD-0016 COMMENT 25
8A-2

8A.3 INDIVIDUAL WORKSHEETS 

The LCC/PBP spreadsheet or workbook consists of the following worksheets described 

below. 

Instructions This worksheet contains notes from the model developers for each 

other and for spreadsheet users. 

LCC Summary This worksheet presents LCC savings and PBP results by ballast 

type at the bottom half of the page to produce the results that DOE 

provides in the preliminary TSD executive summary and chapter 8. 

Equipment Details This worksheet gathers the relevant information for the selected 

product classes and market behavior before it is presented in the 

LCC Summary sheet.

Assumptions & Inputs This worksheet presents data on life-event related replacement, 

disposal, and labor costs.

Equipment Usage Rate This worksheet contains data on all representative product classes 

examined and presents the annual operating hours for the selected 

product class in the examined sector.

Equipment Price This worksheet contains pricing breakdowns for components and 

mark-ups as well as various other details by product class.

Electricity Prices This worksheet organizes the different energy price trends 

selectable on the LCC Summary sheet.

Electricity Price Trends This sheet provides the time-series specifics for each of the energy 

price trends contained in the model. 

Discount Rate This worksheet derives a discount rate based on the user’s 

selection from the LCC Summary sheet. Data for residential, 

commercial, and industrial sectors are presented.

Residential Op. Hrs This worksheet presents the data and calculations for describing 

the operating hours for a ballast operating in this sector. 

Commercial Op. Hrs This worksheet presents the data and calculations for describing 

the operating hours for a ballast operating in this sector.

Industrial Op. Hrs This worksheet presents the data and calculations for describing 

the operating hours for a ballast operating in this sector. 

Lifetime This worksheet describes the lifetime assumptions of this model 

for all examined sectors.

EERE-2007-BT-STD-0016 COMMENT 25
8A-3

Engineering Summary This worksheet describes the details of each representative 

product, organized by product class. These specifics drive the 

whole of the analysis.

Labels This worksheet organizes the user selections and their current 

values from the LCC Summary page.

8A.4 UNCERTAINTY ANALYSIS INSTRUCTIONS 

Crystal Ball results may be saved by using the options in Create Report and Extract Data 

functions on the Control Box window. 

To run a Crystal Ball simulation, do the following: 

To produce Monte Carlo results using Crystal Ball, 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 it

has completed its run sequence, Crystal Ball 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 Excel. Ten thousand is the default number of runs for this 

analysis. 

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

below: 

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

2. Click on RUN 

3. Select Preference and choose from the following choices: 

a. Monte Carlo 

b. Latin Hypercube (recommended) 

c. Initial seed choices and whether it should be constant between runs 

d. Select number of Monte Carlo Trials (10,000 is suggested). 

4. To run the simulation, follow the sequence below (on the Crystal Ball toolbar): 

a. RUN

b. RESET

c. RUN

5. Wait until the program provides a message that the simulation is completed. 

Depending on computer speed, this function can take a few minutes (typically, 

approximately 90 minutes on a Pentium 5 machine for 10,000 runs). 

EERE-2007-BT-STD-0016 COMMENT 25
8A-4

Crystal Ball Results

1. After the simulation has finished, click on the Windows tab bar labeled Crystal 

Ball to view the distribution charts generated. 

2. Several of the variables of interest, including LCC, LCC savings, and PBP 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 cumulative or 

frequency plots. 

3. To calculate the probability that LCC savings will occur, either type 0 in the box 

by the right 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 PBP as 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. The 

toolbar choice of Forecast Windows allows selecting charts and statistics of 

interest. For further information on Crystal Ball outputs, please refer to 

Understanding the Forecast Chart in the Crystal Ball manual. Crystal Ball results 

may also be saved to a separate Excel spreadsheet by using the Extract Data 

function under Analyze on the Control Panel window. 

EERE-2007-BT-STD-0016 COMMENT 25