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­i

EERE-2007-BT-TP-0013 COMMENT 05
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 spreadsheets requires

both Microsoft Excel and Crystal Ball® software. Both applications are commercially available.

Crystal Ball is available at http://www.decisioneering.com.

The spreadsheet posted on the DOE website represent the latest versions and have been

tested with Microsoft Excel 2003 and Crystal Ball 7.2.2.

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.

Candidate standard levels are also shown in the plot.

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

rating, ballast price, lamps per ballast), T12 to T8a substitution

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

a T12 refers to tubular fluorescent lamps with diameters of twelve, eights of an inch. T8 refers to tubular fluorescent

lamps with diameters of eight, eights of an inch.

8A­1

EERE-2007-BT-TP-0013 COMMENT 05
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 AEO 2007.

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 IRL 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 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) Product

Class (3) Lamp Type (4) Baseline Lamp (6) Sector (7) Electricity price Trend, (8) start

year, (9) Multiple of Baseline Service Life (for Analysis Period), (10) Lamp Price, (11)

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

8A­2

EERE-2007-BT-TP-0013 COMMENT 05
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 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 same sheet 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:

b a. Monte Carlo

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, follow the following sequence (on the Crystal Ball toolbar)

Run

Reset

Run

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

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­3

EERE-2007-BT-TP-0013 COMMENT 05
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, click on the

Windows tab bar that is labeled Crystal Ball.

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. The toolbar

choice of Forecast Windows allows you to select the charts and statistics in which you

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

Understanding the Forecast Chart in the Crystal Ball manual.

8A­4

EERE-2007-BT-TP-0013 COMMENT 05