8-E-i

APPENDIX 8-E. USER INSTRUCTIONS FOR CAC LIFE CYCLE COST 

SPREADSHEET

TABLE OF CONTENTS

8-E.1 Introduction............................................................................................................ 8-E-1

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

LIST OF TABLES

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

APPENDIX 8-E. USER INSTRUCTIONS FOR CAC LIFE CYCLE COST 

SPREADSHEET

8-E.1 Introduction

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

life-cycle cost (LCC) and payback period (PBP) analyses for Residential Central Air 

Conditioners (CACs) and Central Heat Pumps (CHPs). 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.

8-E.2 User Instructions for LLC Spreadsheet

The spreadsheet is called “CAC_LCC_FR_4-25-2011_Post.xls”. It allows the user to 

perform LCC analyses of up to seven product classes of CAC and CHP equipment, of which five 

are active—Split-System A/C (separated into Coil-Only and Blower-Coil), Split System HP, 

Single-Package A/C, Single Package HP, and SDHV. 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 2007. The LCC spreadsheet or 

workbook consists of the following tabs as described in Table 8E.2.1.

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

Sheet Name Description

Instructions Instructions for use of the model

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

Names 3-4-2011 This sheet describes the purpose of named ranges in the model

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

replacement split-system A/C units for various efficiency levels (SEER 

levels). The cost curve for a coil-only system shows higher costs to meet a 

given SEER than if both coil and blower are considered.

Replacement Costs 2 This sheet estimates manufacturer selling price, transportation cost, and 

installation costs for replacement split-system air conditioners of 2 tons 

(24,000 Btu/hr) capacity. 

Replacement Costs 3 This sheet estimates manufacturer selling price, transportation cost, and 

installation costs for replacement split-system air conditioners of 3 tons 

(36,000 Btu/hr) capacity. 

Replacement Costs 5 This sheet estimates manufacturer selling price, transportation cost, and 

installation costs for replacement split-system air conditioners of 5 tons 

(60,000 Btu/hr) capacity. 

Equip Price, Repair, Mntc 2 This sheet estimates manufacturer selling price, transportation costs, 

installation costs, and repair and maintenance costs based on product class and 

SEER level for Split-system A/C (except replacements) and HP units of 2-ton 

capacity.
8-E-2

Equip Price, Repair, Mntc 3 This sheet estimates manufacturer selling price, transportation costs, 

installation costs, and repair and maintenance costs based on product class and 

SEER level for Split-system A/C (except replacements) and HP units of 3-ton 

capacity and all single-package and SDHV units.

Equip Price, Repair, Mntc 5 This sheet estimates manufacturer selling price, transportation costs, 

installation costs, and repair and maintenance costs based on product class and 

SEER level for Split-system A/C (except replacements) and HP units of 5-ton 

capacity.

Split AC Coil Only (L) Pre-processing sheet for manufacturer's production cost, dimensions and 

weight for Coil-Only Split-System A/C for use in calculating first cost of 

replacement split-system A/C units. Modifies Split AC Coil Only input data 

for learning that reduces first cost.

Split AC Blower-Coil (L) Pre-processing sheet for manufacturer's production cost, dimensions and 

weight for Blower-Coil Split System A/C for use in calculating first cost of 

replacement split-system A/C units. Modifies Split AC Coil Only input data 

for learning that reduces first cost.

Split AC Coil Only Data for input to Split AC Coil-Only sheet.

Split AC Blower-Coil(L) Data for input to Split AC Coil-Only sheet.

EquipPRM Input Main input sheet for manufacturer's production cost except replacement Split 

System A/C, shipping (transportation) cost, repair cost, maintenance costs, 

installation costs, equipment weight, and efficiency levels (SEER and HSPF) 

for all products 

CB Statistical Results When 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 payback period) from the distributions of model results.

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.

RPBCalcs_Sensitivity This sheet contains all data and calculations for rebuttable payback analysis on 

all product classes.

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. Finally, it provides the distribution of 

products by efficiency level in the market baseline for the year 2016.

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

energy use, heating and cooling bills based on data concerning the product 

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 a product class 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)
8-E-3

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

energy use, heating and cooling bills based on data concerning the product 

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 the product class 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 product 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.

First Cost, Repair Intermediate processing page used to collect information on national average 

manufacturer selling price, transportation cost, repair cost maintenance cost, 

and installation cost for use in the Costs Workup sheet. 

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

product class. 

Installation Cost Indexes Calculates state-level regional installation price indexes.

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

Electricity Charts Provides forecast charts for all of the electricity price indexes used in the 

model

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

Energy Outlook into inflation-adjusted regional residential and commercial 

heating and cooling electricity price indices. 

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

discount rates are determined for residential 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 

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 Excel alone.
8-E-4

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.

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

dollar year, (5) product class, (6) installation option (replacement or new—applies only 

for sensitivity mode to split-system A/C). (7) subgroup—low-income households, senior 

households, or no subgroup (general population), (8) level of any cost-reducing measures 

such as Federal tax credits. To change inputs listed under “User Options” select the input 

you wish to change 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, or by copying and pasting output from the CB 

Statistical Results tab to another Excel spreadsheet. Rerunning the model wipes out 

results in the CB Statistical Results tab.

To run a Crystal Ball simulation, do the following:

To produce Monte Carlo results using Crystal Ball, you need simply select Start from the 

menu (on the Crystal Ball toolbar). 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 Start 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. A full 10,000 runs takes approximately 90 minutes.

The model runs most efficiently by minimizing turning off updating and 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) On Excel Options-Formulas, make sure that “Enable iterative calculation is 

checked. We recommend 100 maximum iterations and 0.001 maximum 

change.

2) Find the Crystal Ball toolbar (at top of screen)

3) Click on RUN
8-E-5

4) 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 10,000).

5) To run the simulation, follow the following sequence (on the Crystal Ball toolbar)

RESET (if not already done)

START

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

Depending on the speed of your computer, this takes up to about 90 minutes on a 

current generation PC for 10,000 runs. Although the model is operating properly, 

it will indicate on completion that "errors were encountered".

Crystal Ball Results

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

the Windows toolbar 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 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.