8A-i 

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET

TABLE OF CONTENTS

8A.1 INTRODUCTION ........................................................................................................ 8A-1

8A.2 USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET ...................... 8A-1

LIST OF TABLES

Table 8A.2.1 Description of Worksheets in LCC Spreadsheet ................................................ 8A-1
8A-1 

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET

8A.1 INTRODUCTION

Detailed results of the life-cycle cost (LCC) and payback period (PBP) analyses for 

commercial refrigeration equipment can be examined using a Microsoft Excel spreadsheet 

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

http://www.eere.energy.gov/buildings/appliance_standards/commercial_products.html. 

8A.2 USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET

The spreadsheet allows the user to perform LCC analyses of any of 24 separate 

equipment classes of commercial refrigeration equipment. To fully execute the spreadsheet 

requires Microsoft Excel and Oracle’s Crystal Ball software, both of which are 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 2007 and with Oracle Crystal 

Ball, Fusion Edition, Release 11.1.2.0.00. Table 8A.2.1 describes the worksheets in the LCC 

spreadsheet. 

Table 8A.2.1 Description of Worksheets in LCC Spreadsheet

Worksheet Description

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

costs, LCC, and payback.

Ranges Provides the name, location, definition, and purpose of each of the named 

ranges in the LCC model.

CH of Lifecycle v Installed Cost 

 

Provides a graph of the lifecycle cost versus installed cost by efficiency level 

for the currently selected equipment class. 

CH of Payback v Installed Cost Provides a graph of the simple PBP in years versus installed cost by 

efficiency level for the currently selected equipment class.

CB_Outputs Stores Crystal Ball outputs. When Crystal Ball performs a 10,000-run 

analysis, it stores a large amount of data in active memory. At the end of the 

analysis, the spreadsheet captures (via formula and a Visual Basic for 

Applications macro) statistical data from the Crystal Ball analyses. These 

data are stored in this worksheet.

Eq Distributions Provides distributions for various inputs used by Crystal Ball during the

Monte Carlo analyses.

OutputUS Provides LCC and PBP output for all of the equipment classes, efficiency 

levels, and building types, but only for average U.S. conditions.

Equipment Parameters Contains calculations of equipment purchase, maintenance and repair costs, 

and energy usage data used in the national impact analysis model.

Energy Expenditures Summarizes energy expenditure data for all building types and all equipment 

classes. Data includes the 24 equipment classes currently being analyzed.
8A-2 

Table 8A.2.1 Cont. 

Engineering Contains the per-unit manufacturer price data and energy use data for a 

standard-sized unit of equipment for each of the 24 equipment classes 

currently being analyzed. Also includes calculation of equipment price 

(including retail markups and the sales tax), the installation price, and the 

repair and maintenance costs per unit.

Markups and Market Calculates the wholesale and retail markup for the selected equipment class, 

including any state and local sales tax. Also calculates weighted average 

sales tax rates and weighted average contractor’s markup.

Contains data on maintenance and repair costs and market channels.

Contractor Markup Index Contains data on contractor markups by state for calculating state-level 

overall wholesale and retail markups.

Building Energy Captures the per-unit energy usage for a standard-sized unit of equipment in 

each equipment class, efficiency level, and state in kilowatt-hours and 

million British thermal units. 

AEO2010 Projections Contains projections of future energy prices and price indices from the 2010 

Annual Energy Outlook (AEO2010). Also contains estimates of commercial

sector energy prices by state, and an index of energy prices by business type 

relative to the commercial average 

Electricity Prices Ratios CBECS Contains data from the 2003 Commercial Building Energy Consumption 

Survey (CBECS) on electricity prices paid by selected business types. 

State Energy Price Detail Contains data on prices paid for energy by commercial sector customers 

from the Energy Information Administration Form EIA-826 Database 

Monthly Electric Utility Sales and Revenue Data (“EIA-826 Sales and 

Revenue Spreadsheets”) for 2009, together with calculations to estimate the 

2010 prices. 

Installation Cost by State Contains multipliers to vary the installation cost by state. Derived from data 

published by RS Means Publishers and Consultants.

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

discount rates are determined using weighted average cost of capital derived 

from data on individual firms published at the Damodaran Online website.

Sales Tax Contains data on sales tax rates by state and population data by state. Also 

contains census data on sales of refrigerated and frozen food by state for 

food sales. Calculates population and shipment weights for the four types of 

business types with shipment data (supermarkets, convenience stores and 

small specialty stores, convenience stores with gas pumps, and large multiline retailers), and selects the weights to be used for each state for purposes 

of calculating weighted average national values. Currently using population 

weighting given lack of food shipment information for food service building 

types.

GDP Deflator Contains Gross Domestic Product Implicit Price Deflators (GDP Deflators) 

from the AEO2010 database. The primary use of the data is inflating 2009 to 

2010 dollars.

Lifetime Contains the estimated average commercial refrigeration equipment lifetime 

in years. 

Labels 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 DOE BT website, open the file using 

Excel. Select Yes when asked if you want to enable macros. Select No when asked if you 

want to update data. At the bottom of the Excel window, click the tab for the Summary
8A-3 

sheet. Note that if you plan to run the Monte Carlo routine, you will need to have Crystal 

Ball loaded as an add-in and activated.

2. Use Excel’s View/Zoom commands at the top menu bar to size the display to your 

monitor. 

3. You can interact with the spreadsheet by clicking choices or entering data using the 

graphical interface on the Summary tab. Select choices from the various inputs listed 

under the User Options heading.

4. Under the User Defined Inputs heading, select from the buttons and boxes for the 

following: (1) energy price projection; (2) start year; (3) region (state); (4) equipment 

family; (5) equipment operating mode; (6) rating temperature; (7) baseline efficiency 

level; (8) selected efficiency level; (9) building type; (10) analysis type; (11) installation 

options; (12) installation cost projection; and (13) repair cost projection. If you are 

investigating the impacts of reducing first costs (e.g., by a tax credit), enter percentage 

reductions at each efficiency level affected. The default calculation is a single sample 

case, which runs almost instantaneously. You can run all cases for U.S. average 

conditions by clicking All US Cases. You can start a complete Crystal Ball simulation by 

clicking the Monte Carlo button. While there is no macro button, you can run all cases for 

all states by running the macro on the Developer tab titled AllRunner_15. To change 

inputs listed under User Defined Inputs, select the input you wish to change by either 

clicking the appropriate button or selecting the appropriate input from the input box. 

a. Equipment classes are defined as particular combinations of equipment families, 

operating modes, and rating temperatures. Because there are ten potential equipment 

families, two potential operating modes (remote and self contained), and three 

possible temperature ratings provided, there are 60 potential equipment classes that 

could be analyzed. If you select a combination of equipment family, operating mode, 

and temperature ranges that results in a potential equipment class that has not been 

analyzed by DOE (because few or no shipments have been identified), the Annual 

Electricity Bill shown on the Summary tab will be $0 per year and the cells 

containing those data will turn red and will be cross-hatched out. In addition, there 

will be a blue background note on the summary page that reads “Not an Analyzed 

Equipment Class.” If data exist for the equipment class, the note will read 

“Equipment Class O.K.” If the All US Cases option is run, the last case is not an 

analyzed equipment class, so the note panel will read “Not an Analyzed Equipment 

Class” at the conclusion of the run. 

b. There are up to eight possible efficiency levels. If an equipment class contains fewer 

than eight levels, the extra levels will be shown as #NA in the Total Installed Price 

column on the Summary tab. 

c. 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 will overwrite the existing 

formula. As a result, the DOE does not recommend saving the spreadsheet after the 
8A-4 

code is changed. The default discount rate can be changed by going to the Discount 

Rate tab and changing the default values in cells C4 through C10. A different lifetime 

can be selected by going to the Lifetime tab and entering a different value in the white

box at cell D3. 

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

a. If nothing is selected for a calculation method (Sample Calculation), then all 

calculations are performed for a set of single input values, usually an average. The 

new results are shown on the Summary tab as soon as the new input values are 

entered.

b. Alternatively, if the Monte Carlo button is clicked, the spreadsheet generates a set of 

results from calculations for each equipment family, equipment operating mode, 

rating temperature (which together define an equipment class). For a number of 

inputs, the Crystal Ball software has custom distributions that it uses to set the level 

of the input. The model runs each of the studied classes in turn, performing 10,000 

model runs for each. At the end of each equipment class run, the model records to the 

CB_Outputs worksheet a large number of outputs that you can then work with for 

further analyses. The Monte Carlo button executes a macro written in Visual Basic 

for Applications to loop through all equipment classes and selected efficiency levels. 

The Monte Carlo analysis takes as little as 8 hours on a powerful desktop computer 

with four processors and as long as 15 hours on a new, powerful but compact, laptop 

computer. In short, if you run the Monte Carlo analysis, have a backup computer 

available. 

c. The third alternative, All US Cases, runs each equipment class, for each building type 

and efficiency level only at U.S. average prices, markups, etc. This option produces 

the required output file to run the National Energy Savings/National Impact 

Assessment spreadsheet.