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-18A -1 APPENDIX 8A. USER INSTRUCTIONS FOR LIFE

- CYCLE COST SPREADSHEET

8A.1 INTRODUCTION

D etailed 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 FO R LIFE

- CYCLE COST SPREADSHE ET

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 200 7 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 Descrip tion Summary Contains the input selections and a summary table of energy use, operating costs, LCC, and p ayback. Ranges Provides the name, location, definition

, and purpose of each of the named ranges in the LCC m odel. CH of Lifecycle v Installed Cost Provides a graph of the lifecycle cost v ersus 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 v ersus 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 sp readsheet captures (via formula and a Visual Basic for Applications macro) statistical data from the Crystal Ball analyses. These data are store d in this worksheet. Eq Distributions P rovides distributions for various inputs used by Crystal Ball during the Monte Carlo analyses. OutputUS P rovides LCC and PBP output for all of the equipment class es, efficiency levels

, and building types, but only for average U

. S

. conditions. Equipment Parameters Contains calculations of equipment purchase, maintenance and r epair costs, and energy usage data used in the n ational i mpact a nalysis 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.
B ui ld in g Energy C aptures 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 energ y prices by state, and an index of energy prices by business type relative to the commercial average
E lectricity Prices Ratios CBECS Contains data from the 2003 Commercial Building Energy Consumption Survey (CBECS) on electricity prices paid by selected business type s. 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 200 9 , together with calcu lations to estimate the 20 1 0 prices. Installation Cost by State Contains multipliers to vary the installation cost by s tate. 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 d ata on individual firms published at the Damodaran Online website. Sales Tax Contains data on sales tax rates by state and population data by sta te. Also contains c ensus 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 multi

line 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 informatio n 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 co mmercial refrigeration equipment lifetime in years

. Labels U sed 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. S elect 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 Summary8A -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 spreadshe et 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 follow ing: (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 effici e ncy 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 macr o 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 an d 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 equipme nt 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 addi tion, 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 analyze d 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 a n 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 the8A -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 spre adsheet 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.