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 CO ST SPREADSHEET

E.1 INTRODUCTION

It is possible to examine and reproduce the deta iled 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. De partment of Energy’s (DOE) website at: http://www.eere.energy.gov/bu ildings/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 th e user to perform LCC analyses of any of 60 potential separate equipment classes of commercial refrigeration equipment, of which 15 are valid equipment clas ses with actual market shipments. To fully execute the spreadsheet requires only Microsof t 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 Mi crosoft Excel® 2003. The LCC spreadsheet or
workbook consists of the follo wing worksheets (Table E.2.1):

Table E.2.1 Description of Tabs in LCC Spreadsheet

Summary Contains the input sele ctions and a summary table of energy use, operating costs, LCC, and Payback. Ranges Provides the name, loca tion, 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 gr aph 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.

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 da tasets for LCC and PBP from the model. Output contains all the LCC and PBP output for all of the states, effi ciency 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. La rge Grocery includes both supermarkets and large multi-line retailer business types. Small grocery incl udes both convenience stores with and without gasoline pumps and small specialty stores, such as meat market s 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 ca lculates weighted average sales tax rates and weight ed 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. Al so 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 C ontains 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 sp ecialty stores, such as
meat markets and beer and wine stores.

Energy Price Projections Contains projecti ons of future energy prices and price indices from the AEO2008 in 2007 prices. Also

contains estimates of comme rcial–sector energy prices by state, and an index of energy prices by business type relative to the commercial average

Electricity Prices Ratios CBE CS 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 multip liers 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 in terface between us er 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 th e 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 c hoices from the various inputs listed under the "User Options" heading.

4. Under the "User Options" heading, select c hoices from the selection buttons and boxes for the following: (1) type of calculation, (2) energy price proj ection, (3) start year, (4) region (state), (5) product family, (6) product opera ting mode, (7) rating temperature, (8) baseline efficiency level, (9) selected efficiency level, (10) building type, ( 11) analysis type, (12) installation options, (13) shipme nt weights option, (14) installa tion cost projection, and (15) repair cost projection, and (16) any tax credits, rebates, or si milar policies used to reduce purchase prices of equipment. The default calcul ation 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 classe s, 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 inpu ts listed under "User Options," select the input you wish to cha nge by either clicking on the appr opriate button or selecting the appropriate input fr om the input box.

a. Equipment classes are defined as part icular 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 co mbination of product family, operating mode,

E-4and 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 Summ ary tab will be $0 per year and the cells containing those data will turn red a nd 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 cla ss 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 va lue other than the
default value or default distribution is wanted; however, this will overwrite the existing formula. As a result, the Department do es 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 cha nging 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 calculati on method (Sample Calculation), then all calculations are performed fo r 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 m odel 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 data set 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 a nd 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