APPENDIX 8A. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET 

TABLE OF CONTENTS
 

8A.1 Introduction...................................................................................................................... 8A-1
 

8A.2 User Instructions for LLC Spreadsheet............................................................................ 8A-1
 

LIST OF TABLES 

Table 8A.2.1 Description of Tabs in the Life-Cycle Cost Spreadsheet............................... 8A-1
 

8A-i 
 

 

 

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET 

8A.1 Introduction 

Appendix E contains instructions to examine and reproduce the detailed results of the 

life-cycle cost (LCC) and payback period (PBP) analyses for beverage vending machine (BVM) 

equipment. This analysis is performed using a Microsoft Excel spreadsheet available on the U.S. 

Department of Energy’s website at: 

http://www1.eere.energy.gov/buildings/appliance_standards/residential/central_ac_hp.html. 

8A.2 User Instructions for LLC Spreadsheet 

The spreadsheet is called “CAC_LCC_PreNOPR_03-18-10_yyy_topost.xls” where yyy 

indicates whether the spreadsheet addresses Coil-Only systems or Coil and Blower systems. It 

allows the user to perform LCC analyses of any of seven equipment classes CAC and CHP 

equipment, of which four are active—Split System A/C, Split System HP, Single-Package A/C 

and Single Package HP. To fully execute in sensitivity mode the spreadsheet requires only 

Microsoft Excel software. To fully execute in uncertainty mode, the model also requires 

commercially-available Crystal Ball®. 

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 tabs as described in Table 8A.2.1. 

Table 8A.2.1 Description of Tabs in the Life-Cycle Cost Spreadsheet 

Notes Notes on changes to this model since the advance notice of proposed rulemaking 

(ANOPR) 

Instructions Instructions for use of the model 

Revision Notes Provides a history of changes that have been made to the model 

Split AC Replacement 

Scenarios 

Split-System air conditioners have system components that can be replaced 

separately. Most depend on a furnace fan to circulate air. The need to replace 

the A/C condenser (outside unit) can be combined with a decision to replace the 

furnace. This sheet specifies assumed scenarios for furnace replacement. For 

Coil-Only system replacements, the air handling unit (furnace fan) is not 

included. 

Split AC Costs This sheet provides data and calculations for purchase and installation of a 

system for various efficiency levels (SEER levels). The cost curve for a coilonly system shows higher costs to meet a given SEER than if both coil and 

blower are considered. 

Furnace This sheet shows the expected life and percent of furnaces reaching retirement, 

based on age of the furnace. 

Replacement Costs This sheet assigns equipment price and installation costs based on whether or not 

early costs of furnace replacement are considered in the cost of air conditioner 

replacement. These costs are not considered in the cost of coil-only 

replacements. 

Equip Price, Repair, Mntc This sheet estimates equipment price, transportation costs, installation costs, and 

repair and maintenance costs based on equipment class and SEER level. 

Names 5-06-2009 This sheet describes the purpose of named ranges in the model 

CB Statistical Results .TWhen the model is run in Crystal Ball this sheet captures regional summary 

statistics (e.g., mean median, 5th,25th, 75th, and 95th percentiles) on key model 

outputs (installed costs, annual energy use and operating costs, LCC and 

8A-1 
 

 

 

 

 

 

 

 

 

 

payback period) from the distributions of model results. 

Table 8A.2.1 (contd) Description of Tabs in the Life-Cylce Cost Spreadsheet 

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

elements of regional installed cost, regional annual operating costs, and regional 

results for LCC and payback period. 

Labels This is used as a go-between for user inputs and the rest of the tabs—do not 

modify this sheet. 

National Variables This sheet provides values for input data that only varies at the national level— 

wholesale and contractor markups, purchaser discount rates. It also determines 

from probability distributions whether any given sale of AC equipment is new or 

replacement and whether it is being installed in a residential or commercial 

building. 

Commercial Int. Processing This sheet provides regional calculations for year 2016 projected annual energy 

use, heating and cooling bills based on data concerning the equipment class, 

location and projected efficiency of individual commercial installations of AC 

and HP equipment. 

Commercial Sim Results This sheet queries the database of individual commercial equipment locations for 

an equipment type and provides information on current cooling and heating 

energy consumption and annual cooling and heating bill (based on 8760 hours 

with price variation by time of use, where relevant) by efficiency level (SEER 

level) 

Residential Int. Processing This sheet provides regional calculations for year 2016 projected annual energy 

use, heating and cooling bills based on data concerning the equipment class, 

location and projected efficiency of individual residential installations of AC and 

HP equipment. 

Input Data on kWh Use, Price This sheet queries the database of individual residential equipment locations for 

an equipment type and provides information on current annual cooling and 

heating energy consumption and average and marginal cost for cooling and 

heating by efficiency level (SEER level) by region for individual observations. 

Costs Workup Uses equipment class and locations, national and regional markup factors, sales 

tax rates and average manufacturer sales prices (including delivery charges) by 

efficiency level to calculate delivered equipment prices by efficiency level by 

region for individual observations. 

Base Install Costs Contains national average data on components of installation costs for each 

equipment class. 

Calculated Regional Indices Contains factors for calculating regionalized values for wholesale and contractor 

markups, sales taxes, and installation costs for individual observations. 

First Cost Region Vals. Provides projected values for new and replacement installation costs, annual 

repair costs, and annual maintenance costs for individual observations by 

efficiency level. 

State-Level Variables and 

Wts. 

Provides data and likelihood of wholesale markup index, contractor markup 

indices, sate and local sales tax index, and installation cost index for individual 

states. Used to calculate markups and other indices for individual observations 

in the Calculated Regional Indices tab. 

Chart Res Prices Chart of real price indices for future escalation of electricity prices in the 

residential sector. 

Chart Commercial Prices Chart of real price indices for future escalation of electricity prices in the 

commercial sector. 

Energy Price Escalation Translates scenario projections of future energy prices from the Annual Energy 

Outlook into average real residential and commercial electricity price indices. 

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

rates are determined for residential and commercial consumers. 

Summary Cost of Capital Contains the commercial firm financial data and calculations used to estimate a 

weighted average cost of capital and discount rate for commercial consumers. 

Lifetime Contains the calculations for the survival function for central air conditioning 

8A-2 
 

 

 

 

 

and heat pump units (lifetime in years). 

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. 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) installation option (currently limited to only new 

units). (6) level of any cost-reducing measures such as Federal tax credits. 

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. 

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 

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

reduced by minimizing the Crystal Ball window in Excel. Ten thousand runs 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 toolbar (at top of screen) 

2) Click on RUN 

3) Select Preference and choose from the following choices: 

8A-3 
 

 

 

 

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

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

(typically, about 90 minutes on a Pentium 5 machine for 10,000 runs). 

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. 

8A-4