8A-i 

APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS 

TABLE OF CONTENTS 

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

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

8A.3 BASIC INSTRUCTIONS ......................................................................................... A-3

EERE-2008-BT-STD-0005 COMMENT 31
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 spreadsheets posted on the DOE website represent the latest versions and have been 

tested with Microsoft Excel 2007 and Crystal Ball 11.1.1.1.00. 

8A.2 LCC AND PBP WORKBOOK 

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

App. Sample Calc. Presents shipment-weighted results for the application-specific 

Summary Sample Calculation.

Application LCC Results Presents LCC and PBP results for each application within a 

representative unit or product class. 

Avg. Sample Calc. Presents results for the basic Sample Calculation. These results are 

Summary based on a Sample Calculation for each representative unit or 

product class where application-specific inputs are averaged prior 

to calculation.

LCC Summary Contains the input selections and summary tables 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. 

Setup Contains tables listing the contents of the menus shown in the 

"LCC Summary" worksheet along with indicators showing the 

current user selections. 

Cash Flows Contains the discounted cash flows for each unit. 

Variables Contains a list of the major variables (Excel defined names) in 

usage in the LCC model, along with their values. 

EERE-2008-BT-STD-0005 COMMENT 31
8A-2

Rep Unit Summary Contains a list of the parameters for the representative units. 

Class A EPS Input Contains the inputs for the Class A External Power Supply

Selection scenario chosen. 

BC Input Selection Contains the inputs for the Battery Charger scenario chosen. 

Class A EPS Inputs Contains all Class A External Power Supply product information, 

such as unit energy consumption (UEC), markups, and lifetimes 

for each input scenario. 

BC Inputs Contains Battery Charger product information, such as UEC, 

markups, and lifetimes for each input scenario. 

Application Data Contains a summary of all the application-specific data from the 

input sheets so that applications can be sampled in the LCC model. 

Lifetime Contains information on the lifetimes of the units so that a lifetime 

estimate can be selected for input into the LCC model. 

Markups Contains information related to the price markups used in the 

analysis, including sales tax. 

Unit Price Contains information used in the development of unit purchase 

prices, including markups and sales tax. 

Base Case Eff Dist Contains tables of the unit base case efficiency distributions so that 

the CSLs of the baseline units can be determined. 

Discount Rate Contains information used to develop the discount rate for the LCC 

analysis. 

Gas Prices Contains gasoline price data, engine efficiency assumptions, and 

consumer price indices used to develop the gasoline prices for the 

LCC and PBP analyses for Battery Charger product class 9 (DCDC, ≥9 V Input).

Elec Prices Contains electricity price data and consumer price indices used to 

develop the electricity prices for the LCC and PBP analyses. 

Elec Price Trends Contains data used to develop the electricity price trends for 

projection of electricity prices into the future. 

EERE-2008-BT-STD-0005 COMMENT 31
8A-3

8A.3 BASIC INSTRUCTIONS 

 Basic instructions for operating the LCC spreadsheets are as follows: 

1. Once you have downloaded the LCC file from the Web, open the file using Excel. At the 

bottom, click on the tab for sheet 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. The user interacts with the spreadsheet by clicking choices or entering data using the 

interface that comes with the spreadsheet. 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) Run the 

application-specific sample calculation, (3) Energy Price Trend, (4) Start Year, (5) 

Sector, (6) Lifetime, (7) Market Distribution, (8) LCC Input Scenario, and (9) Battery 

Charger Inputs. Under the “Application Selection” heading, the user can select a specific 

application to examine within a given representative unit or product class. 

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

clicking on the appropriate button or selecting the appropriate input from the input box. 

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

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

input values, usually an average. This calculation averages all application-specific 

data prior to performing calculations. The new results are shown on the same sheet as 

soon as the new values are entered, and are summarized on the “Avg. Sample Calc. 

Summary” tab.

b. 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. 

c. If the “Run” button next to “Application-Specific Sample Calculation” is clicked, the 

spreadsheet will execute a macro to perform a sample calculation on each individual 

application within a representative unit or product class. Each run typically takes 10-

30 minutes to finish. Once finished, results will be presented in the “Application 

LCC Results” tab, and summarized in the “App. Sample Calc. Summary” tab. The 

shipment-weighted results of this sample calculation differ from the basic “Sample 

Calculation” in that application inputs are not averaged prior to performing 

calculations. This methodology generates results that better approximate Crystal Ball 

results than the basic “Sample Calculation” tool.

EERE-2008-BT-STD-0005 COMMENT 31
8A-4

 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 Carloa

b. Latin Hypercube (recommended) 

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

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. 

 

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.

EERE-2008-BT-STD-0005 COMMENT 31
8A-5

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. 

EERE-2008-BT-STD-0005 COMMENT 31