P-i

APPENDIX P. USER INSTRUCTIONS FOR LIFE-CYCLE COST (LCC) AND

PAYBACK PERIOD (PBP) SPREADSHEETS

TABLE OF CONTENTS

P.1 User Instructions for Tariff-Based Spreadsheet ............................. P-1

P.2 User Instructions for Hourly Based Spreadsheet ............................. P-4
P-1

APPENDIX P. USER INSTRUCTIONS FOR LIFE-CYCLE COST (LCC) AND

PAYBACK PERIOD (PBP) SPREADSHEETS

P.1 User Instructions for Tariff-Based Spreadsheet

It is possible to examine and reproduce the detailed results of the tariff-based LCC and

PBP analyses using a Microsoft Excel® spreadsheet available on the U.S. Department of Energy’s

website at: http://www.eere.energy.gov/buildings/appliance_standards/commercial/ac_hp.html.

The spreadsheet is called “comm-ac_tariff.xls” and it allows the user to perform tariffbased LCC analyses of either 7.5-ton air conditioners (representing the $65,000 Btu/h to

<135,000 Btu/h equipment class) or 15-ton air conditioners (representing the $135,000 Btu/h to

<240,000 Btu/h equipment class). To fully execute the spreadsheet 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 represents the latest version of the applicable

model and has been tested with Microsoft Excel® 2000. The tariff-based LCC spreadsheet or

workbook consists of the following worksheets:

Summary Contains the input selections and a summary table of energy use, operating

costs, LCC, and Payback.

Engineering Contains the manufacturer price data for 7.5-ton and 15-ton systems at

each efficiency level. Also includes the wholesale, general contractor,

mechanical contractor, and national accounts markups, the sales tax, the

installation price, and the repair and maintenance costs.

Bldg Records with 

Annual Bills For each commercial building simulated, contains the annual energy

expense at each efficiency level. Also includes data showing the annual air

conditioner energy expense, annual air conditioner energy use, and average

and marginal electricity prices. Summary information for each building

record is also provided.

Installation Cost by

State Contains multipliers to vary the installation cost by State.

Energy Price Contains projections of future energy prices from the AEO2003.

Discount Rate Contains data from which an average discount rate and a distribution of

discount rates are determined.
P-2

Lifetime Contains the survival function for commercial unitary air conditioners and

the average commercial unitary air conditioner lifetime in years.

Setup This is used as an interface between user inputs and the rest of the

worksheets -- do not modify this sheet.

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

graphical interface that comes with the spreadsheet. Select choices from the various inputs

listed under "User Options" heading.

Under the "User Options" heading, select choices from the selection buttons and boxes for

the following: (1) type of calculation (Sample or Crystal Ball®), (2) energy price

projection, (3) start year, (4) equipment class, (5) installation cost projection, and (6)

repair cost projection. A new discount rate or lifetime can also be entered if a value other

than the default value or default distribution is wanted; however, this changes the code. As

a result, the Department does not recommend saving the spreadsheet after the code is

changed.

4. To change inputs listed under "User Options", select the input you wish to change by

either clicking on the appropriate button or selecting the appropriate input from the input

box.

5. This spreadsheet gives the user two types of calculation methods:

a. If the "Sample Calculation" 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 "Crystal Ball 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.
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.

P-3

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 (the Department suggests 10,000).

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.

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
P-4

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.

P.2 User Instructions for Hourly Based Spreadsheet

It is possible to examine and reproduce the detailed results of the hourly based LCC and

PBP analysis using a Microsoft Excel® spreadsheet available on the U.S. Department of Energy’s

website at: http://www.eere.energy.gov/buildings/appliance_standards/commercial/ac_hp.html.

The spreadsheet is called “comm-ac_hourly.xls” and allows the user to perform hourly

based LCC and PBP analyses of either 7.5-ton air conditioners (representing the $65,000 Btu/h to

<135,000 Btu/h equipment class) or 15-ton air conditioners (representing the $135,000 Btu/h to

<240,000 Btu/h equipment class). To execute the spreadsheet fully 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 represents the latest version of the applicable

model and has been tested with Mircosoft Excel® 2000. The hourly based LCC spreadsheet or

workbook consists of the same worksheets as the tariff-based LCC spreadsheet with the

exception that the hourly based spreadsheet contains an additional worksheet called Cost

Formula. The Cost Formula worksheet contains the necessary parameters for converting

generation cost and peak demand savings into customer expense savings.

The user instructions for the hourly based LCC spreadsheet are identical to those for the

tariff-based LCC spreadsheet.