APPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET
 

TABLE OF CONTENTS 

E.1 INTRODUCTION ............................................................................................................E-1
 

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

LIST OF TABLES 

Table E.2.1 Description of Tabs in LCC Spreadsheet................................................................E-1 

E-i 

EERE-2006-BT-STD-0126 COMMENT 52.2 
 E-ii 

EERE-2006-BT-STD-0126 COMMENT 52.2 
 

 

APPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET 

E.1 INTRODUCTION 

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

payback period (PBP) analyses for commercial refrigeration equipment using a Microsoft 

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

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

E.2 USER INSTRUCTIONS FOR LCC SPREADSHEET 

The spreadsheet is called “CRE_LCC_FR_11-19-2008.xls” and it allows the user to perform 

LCC analyses of any of 60 potential separate equipment classes of commercial refrigeration 

equipment, of which 15 are valid equipment classes with actual market shipments. 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 (Table E.2.1): 

Table E.2.1 Description of Tabs in LCC Spreadsheet 

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 payback period in years 

versus installed cost by efficiency level for the 

currently selected equipment class. 

E-1
 

EERE-2006-BT-STD-0126 COMMENT 52.2 
 

Table E.2.1 Description of Tabs in LCC Spreadsheet – Cont. 

 Equipment Parameters Provides selected national average data on equipment, 

standardized per linear foot of display space, for use in 

the National Impact Analysis spreadsheet. 

Output15, OutputUS Provides output datasets for LCC and PBP from the 

model. Output contains all the LCC and PBP output 

for all of the states, efficiency levels, and building 

types, for a single equipment class. Output15 provides 

LCC and PBP output data on all 15 of the valid 

equipment classes and all the states, efficiency levels, 

and building types. Output US provides LCC and 

PBP output for all of the equipment classes, efficiency 

levels and building types, but only for average US 

conditions. 

Energy $ Expend; Energy $ Expend, 

Large Grocery; Energy $ Expend, 

Small Grocery 

These three tabs capture the per-unit dollar values 

associated with energy savings for a standard-sized 

unit of equipment in each equipment class, efficiency 

level, and state. Large Grocery includes both 

supermarkets and large multi-line retailer business 

types. Small grocery includes both convenience stores 

with and without gasoline pumps and small specialty 

stores, such as meat markets and beer and wine stores. 

Energy $ Expend performs summary calculations. 

Engineering Contains the per-unit manufacturer price data and 

energy use data for a standard-sized unit of equipment 

for each of the 15 valid equipment classes at each 

efficiency level. 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 state and local 

sales tax (if any). Also calculates weighted average 

sales tax rates and weighted average contractor’s 

markup. Contains data on maintenance and repair 

costs and market channels. 

E-2
 

EERE-2006-BT-STD-0126 COMMENT 52.2 
 

Table E.2.1 Description of Tabs in LCC Spreadsheet – Cont. 

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 and 

business type. Calculates population and shipment 

weights for each of four types of business 

(supermarkets, convenience stores and small specialty 

stores, convenience stores with gas pumps, and large 

multi-line retailers) and selects the shipment weights 

to be used for each state for purposes of calculating 

weighted average national values. 

Contractor Markup Index Contains data on contractor markups by state for the 

purpose of calculating state-level overall wholesale 

and retail markups. 

Bldg Energy, Large Grocery; Bldg 

Energy, Small Grocery 

These two tabs capture the per-unit energy savings for 

a standard-sized unit of equipment in each equipment 

class, efficiency level, and state in kWh and million 

Btus. Large Grocery includes both supermarkets and 

large multi-line retailer business types. Small grocery 

includes both convenience stores with and without 

gasoline pumps and small specialty stores, such as 

meat markets and beer and wine stores. 

Energy Price Projections Contains projections of future energy prices and price 

indices from the AEO2008 in 2007 prices. 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 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 EIA State Energy 

Consumption, Price and Expenditure report for 2003, 

together with calculations to estimate the 2007 prices. 

Installation Cost by State Contains multipliers to vary the installation cost by 

State. Derived from data published by RS Means 

Publishers and Consultants. 

E-3
 

EERE-2006-BT-STD-0126 COMMENT 52.2 
 

Table E.2.1 Description of Tabs in LCC Spreadsheet – Cont. 

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 Value 

Line Data on individual firms published at the 

Damodaran Online website. 

Lifetime Contains the estimated average commercial 

refrigeration equipment lifetime in years. 

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

will be confronted with two dialog boxes as the file opens. You DO want to Enable Macros 

(Select “Yes” when this option is presented.) You DO NOT want to Update Data (“Select “No” 

when that option is presented.) 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 on the Summary tab. Select choices from the various inputs listed under the 

"User Options" heading. 

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

for the following: (1) type of calculation, (2) energy price projection, (3) start year, (4) region 

(state), (5) product family, (6) product operating mode, (7) rating temperature, (8) baseline 

efficiency level, (9) selected efficiency level, (10) building type, (11) analysis type, (12) 

installation options, (13) shipment weights option, (14) installation cost projection, and (15) 

repair cost projection, and (16) any tax credits, rebates, or similar policies used to reduce 

purchase prices of equipment. The default calculation is a single sample case, which is run 

almost instantaneously when the user modifies a user option. Optionally, the user may run all 

cases for all states, equipment classes, and business types by pressing the All Cases button, or 

the user may run all equipment classes and business types for U.S. average conditions by 

pressing All US Cases—note their run times. 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. 

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

operating modes, and rating temperatures. Because there are 10 potential product 

families, 2 potential operating modes (remote and self contained), and 3 possible 

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

be analyzed. If the user selects a combination of product family, operating mode, 

E-4
 

EERE-2006-BT-STD-0126 COMMENT 52.2 
 

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

been analyzed by DOE (due to little or no shipments having 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 crossed out. In addition, a red 

background note will appear on the summary page that says “Not an Analyzed 

Equipment Class.” If the equipment class is one for which data exist, the note 

area will be green and will read “Equipment Class O.K.” If the All Cases or All 

US Cases option is run, the last case is not an analyzed equipment class, so the 

note panel will be red at the conclusion of the run. The selection of an analyzed 

equipment class will return the user to the normal screen view. 

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 Department does not recommend saving the 

spreadsheet after the 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 C7. A different lifetime can be selected by going to the Lifetime tab and 

entering a different value in the boxes at cell D9 and D13. 

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

entered. 

b.	 Alternatively, if the "All Cases” button is clicked, the spreadsheet generates a set 

of results from calculations for each product family, product operating mode, 

rating temperature (which together define an equipment class), state, building 

type, and efficiency level. The LCC model then uses a macro code written in 

Visual Basic for Applications to process these data to calculate cumulative 

distributions of values of LCC and PBP for each equipment class and selected 

efficiency level. Generating the initial dataset for these cumulative 

distributions takes about 1.5 hours on a Pentium 4 computer. 

c. The third alternative (“All US Cases”) is similar to “All Cases.” However, 

instead of running using each state’s prices, markups, taxes, etc., it runs each 

equipment class, for each building type and efficiency level only at U.S. average 

prices, markups, etc., which makes the problem about 50 times smaller and 

quicker to solve (about 7 minutes). This option also produces the required output 

file to run the National Energy Savings/National Impact Assessment spreadsheet. 

E-5
 

EERE-2006-BT-STD-0126 COMMENT 52.2