APPENDIX D. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET 

TABLE OF CONTENTS 

D.1 INTRODUCTION ............................................................................................................. D-1

D.2 USER INSTRUCTIONS FOR LCC SPREADSHEET ..................................................... D-1

LIST OF TABLES 

Table D.2.1 Description of Tabs in LCC Spreadsheet ............................................................... D-2

 EERE-2007-BT-STD-0012 COMMENT 32
APPENDIX D. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET 

D.1 INTRODUCTION 

It is possible to examine and reproduce the detailed results of the life-cycle cost (LCC) 

and payback periods (PBP) analyses for packaged terminal air conditioners (PTACs) and 

packaged terminal heat pump (PTHPs) equipment using a Microsoft Excel® spreadsheet 

available on the U.S. Department of Energy (DOE)’s website at: 

http://www.eere.energy.gov/buildings/appliance_standards/commercial/packaged_ac_hp.html 

D.2 USER INSTRUCTIONS FOR LCC SPREADSHEET 

The spreadsheet is called “PTAC-PTHP_LCC_Final.xls” and it allows the user to 

perform LCC analyses of any of four equipment classes and two cooling capacities of standard 

size PTAC and PTHP equipment. To fully execute the spreadsheet requires only Microsoft 

Excel® software, which is commercially available. 

The spreadsheet posted on the DOE website represents the latest version of the applicable 

model and has been developed and tested with Microsoft Excel® 2003. The LCC spreadsheet or 

workbook consists of the following worksheets: 

 EERE-2007-BT-STD-0012 COMMENT 32
Table D.2.1 Description of Tabs in LCC Spreadsheet 

CH Lifecycle vs 

Installed Cost 

Chart showing tradeoff between efficiency level and lifecycle cost for the current analysis 

options. 

CH Payback vs 

Installed Cost 

Chart showing tradeoff between efficiency level and payback period in years. 

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

Payback. 

Energy $ Expend Contains Summary calculations that translate energy savings in individual building types and 

equipment classes into the values needed for the particular selections made in a given run of the 

model. 

Energy $ Save, 

Large Chain 

Contains estimates of base annual dollar expenditures in Large Chain hotel buildings for energy 

for the various system options, locations. 

Energy $ Save, 

Independent Hotel 

Contains estimates of base annual dollar expenditures in Independent Hotel buildings for 

energy for the various system options, locations 

Energy $ Save, 

Health Care 

Contains estimates of base annual dollar expenditures in Health Care buildings (nursing homes, 

assisted living, and extended care facilities) for energy for the various system options, 

locations. 

Energy $ Save, 

Office 

Contains estimates of base annual dollar expenditures in Office buildings (small Office 

buildings, frequently occupied by medical and dental offices) for energy for the various system 

options, locations. 

Engineering Contains the manufacturer price data for systems at each efficiency level. Also contains data 

on energy consumption in various configurations, and summarizes data on cost elements other 

than energy by efficiency level. 

Markups and 

Market 

Contains the distributor, contractor, and national accounts markups, the sales tax, the 

installation price, and the repair and maintenance costs. 

Sales Tax Contains detailed calculations to determine weighted average sales tax rates from state data. In 

addition, contains calculation cells for several of the variables that vary by state when the 

model is run in uncertainty mode (Monte Carlo analysis) using Crystal Ball. 

Contractor 

Markup Index 

Contains detailed data on contractor markups by State used in the model 

Bldg Energy, 

Large Chain 

Contains data on energy use in standardized PTAC and PTHP units in Large Chain Hotel 

buildings for the various efficiency classes by Efficiency Level, State 

Bldg Energy, 

Independent Hotel 

Contains data on energy use in standardized PTAC and PTHP units in Independent Hotel 

buildings for the various efficiency classes by Efficiency Level, State 

Bldg Energy, 

Health Care 

Contains data on energy use in standardized PTAC and PTHP units in Health Care buildings 

for the various efficiency classes by Efficiency Level, State 

Bldg Energy, 

Office 

Contains data on energy use in standardized PTAC and PTHP units in Office buildings for the 

various efficiency classes by Efficiency Level, State 

Energy Price 

Projections 

Contains current energy prices by State, and projections of future energy prices from the 

Annual Energy Outlook. Also performs functions to translate average commercial electricity 

prices into prices by state and building type 

Electricity Price 

Ratios CBECS 

Contains the national average electricity prices experienced in the four types of buildings 

(businesses) that use PTACs and PTHPs, compared with average commercial electricity prices, 

from microdata in the EIA Commercial Buildings Energy Consumption Survey (CBECS) 2003 

State Energy Price 

Detail 

Contains data on the average commercial electricity prices by state in 2007 

Installation Cost 

by State 

Contains detailed calculations to determine multipliers to vary the installation cost from state 

data. 

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

determined. 

Lifetime Contains the calculations for the survival function for PTAC and PTHP units lifetime in years. 

Labels This is used as an interface between user inputs and the rest of the worksheets -- do not modify 

this sheet. 

 EERE-2007-BT-STD-0012 COMMENT 32
Basic instructions for operating the LCC spreadsheets are as follows: 

To execute the spreadsheets fully you will need Microsoft Excel and Crystal Ball, Version 7.0 or 

later. The spreadsheet can be run in sensitivity mode using only Excel. 

1) Once you have downloaded the LCC file from the Web, first open Crystal Ball and then 

open the file inside of Crystal Ball using Excel. If you are not using Crystal Ball, simply 

open the file in Excel. At the bottom, click on the tab for sheet Summary. 

2) Use Excel’s commands at the top View/Zoom 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. Choices can be selected 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) Uncertainty option (2) electricity price projection, (3) start year, (4) 

equipment class and capacity, (5) efficiency level, (6) region, (7) business (and 

building) type, (8) installation option, (9) shipment weights option, (7) installation cost 

projection, and (8) repair cost projection, (9) level of any cost-reducing measures such as 

Federal tax credits. 

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 overwrites the existing formula. As a 

result, the DOE does not recommend saving the spreadsheet after the code is changed. . 

The default discount rates can be changed by going to the Discount Rate tab and 

changing the default values in Cells C4 through C7. A different lifetime can be selected 

by going to the Lifetime tab and entering a different value in the green box at cell D4 and 

Cell I3. 

4) To change inputs listed under "User Options" either select the input you wish to change 

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

input box. If the Crystal Ball Option is selected, red panels appear on the Summary page 

advising the user to not use the data that appear in the results section of the Summary 

page, but instead to use the Crystal Ball results. 

5) 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, you need 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 

 EERE-2007-BT-STD-0012 COMMENT 32
 D-4

obtain further statistical information. The time taken to complete a run sequence can be 

reduced by minimizing the Crystal Ball window in 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 

3) Select Preference and choose from the following choices: 

 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 (we suggest 1,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. 

Depending on the speed of your computer, this takes only a few minutes 

(typically, anywhere from 1 minute to 5-10 minutes on a Pentium 4 machine, 

depending on whether you suppress the figures for speed or want to watch the 

figures update) 

Crystal Ball Results 

1) After the simulation has finished, to see the distribution charts generated, click on 

the Windows tab bar that is labeled Crystal Ball. 

2) Several of the variables of interest, including life cycle cost, the life-cycle cost 

savings, and the 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 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 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 

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

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

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

Data function under Analyze on the Control Panel window. 

 EERE-2007-BT-STD-0012 COMMENT 32