8B-i

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

PERIOD SPREADSHEET

TABLE OF CONTENTS

8B.1 INTRODUCTION ...................................................................................................8B-1

8B.2 MODEL CONVENTIONS ......................................................................................8B-1

8B.3 INDIVIDUAL WORKSHEETS ..............................................................................8B-1

8B.4 UNCERTAINTY ANALYSIS INSTRUCTIONS ..................................................8B-2
8B-1

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

PERIOD SPREADSHEET

8B.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 fluorescent ballast system product 

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

tested with both Microsoft Excel 2003 and 2007. 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 analysis mode, consumer 

subgroup, life event, and economic growth scenarios desired for examination. Any market and 

energy price behaviour can be set by adjusting the named ranges throughout the LCC/PBP 

spreadsheet model. 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.

8B.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 inputs (e.g., the LCC savings for a particular 

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

LCC associated with a unit at CSL 0 of the same product class). 

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 generally presented at the top of a worksheet. 

8B.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&PBP This worksheet presents LCC savings and PBP results by fluorescent 

ballast type to produce the results that DOE provides in the NOPR
8B-2

notice and TSD chapter 8. This worksheet also condenses the 

information from the Lifetime Costs worksheet, for user ease of use.

Lifetime Costs This worksheet compiles data from several other worksheets to 

calculate the actual life cycle costs of all fluorescent ballasts. This 

worksheet also contains assumptions for labor costs, some product 

prices, and mark-up values.

Discount Rate This worksheet derives discount rates for commercial, industrial, and 

residential sector fluorescent ballasts.

Operating Hours This worksheet provides the data and calculations used to develop 

operating hours for the commercial, industrial, and residential sectors.

Electricity and Tax Rates This worksheet contains data and calculations for electricity prices 

over time, and sales tax rates. Economic growth scenario information 

is also contained in this worksheet.

Information Hub This worksheet contains the engineering summary data, and many 

important quantities that pertain to each product class and 

representative product. 

Output to NIA This worksheet contains a few calculated values for easy output to the 

NIA model.

8B.4 UNCERTAINTY ANALYSIS INSTRUCTIONS 

The following instructions assume users run the uncertainty analysis in Microsoft Excel 

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

functions in the Crystal Ball ribbon. 

To run a Crystal Ball simulation, do the following:

To produce Monte Carlo results using Crystal Ball, select Monte Carlo Analysis Mode 

from the drop-down menu in the LCC&PBP worksheet. 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 options in the Analyze menu, it is 

possible to obtain further statistical information. The time taken to complete a run 

sequence can be reduced by selecting `Suppress chart windows’ in the Run Preferences 

box. 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 ribbon (at the top of the screen) 

2. Click on Run Preferences in the Run menu 
8B-3

3. Select from the following choices within the tabs in the Run Preferences box: 

a. Number of trials (10,000 is suggested)

b. Initial Seed Value

c. Monte Carlo 

d. Latin Hypercube

e. Other options are available 

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

a. Ensure Monte Carlo Analysis Mode is selected from the drop-down menu in 

the LCC&PBP worksheet

b. Select Reset from the Run menu if you have run previous simulations

c. Select Start from the Run menu

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 30 minutes on an Intel Xeon CPU machine for 10,000 runs). 

Crystal Ball Results

1. After the simulation has finished, click on the Crystal Ball ribbon to view the 

options available for data analysis. 

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. 

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 Analyze menu. 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 the 

Analyze menu in the Crystal Ball ribbon.