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­iE­iiAPPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

E.1 INTRODUCTION

It is possible to examine and reproduce the detailed results of the LCC and PBP analyses for

commercial refrigeration equipment using a Microsoft Excel® spreadsheet available on the U.S.

Department of Energy’s 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_Version_2007_07_03.xls” and it allows the user to

perform LCC analyses of any of 15 separate equipment classes of commercial refrigeration

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:

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­1Table E.2.1 Description of Tabs in LCC Spreadsheet

– Cont.

Output1, Output2, OutputUS Provides output datasets for LCC and PBP from the

model. Output contains all of the LCC and PBP

output for all of the states, efficiency levels, and

building types, for a single equipment class, Output1

and Output2 together provide LCC and PBP output

data on all of the equipment classes and all of 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, Large Grocery; These two tabs capture the per­unit dollar values

Energy $ Expend Small Grocery 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.

Engineering Contains the per­unit manufacturer price data and

energy use data for a standard­sized unit of equipment

for each of 15 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­2Table 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. Also 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 AEO2006. 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 2006 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­3Table 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, (5) installation cost projection, and (6) repair

cost projection. 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 ten potential

product 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 the user selects a combination of product

family, operating mode, and temperature ranges that results in a potential

E­4equipment 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 are 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 a 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 green 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 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 4 hours on a Pentium 4 computer.

c. The third alternative, “All US Cases” is similar to “All Cases”, but 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. This option also produces the required output file to run the National

Energy Savings/National Impact Assessment spreadsheet.

E­5