### APPENDIX E. USER INSTRUCTIO NS FOR THE LIFE-CYCLE COST SPREADSHEET

TABLE OF CONTENTS

E.1 INTRODUCTION

.............................................................................................................. E-1 E.2 USER INSTRUCTIONS FOR LIFE -CYCLE COST SPREADSHEET

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

LIST OF TABLES

Table E.2.1 Description of Tabs in th e Life-Cycle Cost Spreadsheet

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

E-i BVM_FINAL_FR_TSD_AppendixE_08-14-09.doc

### APPENDIX E. USER INSTRUCTIO NS FOR THE LI FE-CYCLE COST SPREADSHEET

E.1 INTRODUCTION

Appendix E contains instructions to examin e and reproduce the deta iled results of the life-cycle cost (LCC) and payback period (P BP) analyses for beverage vending machine equipment. This analysis is performed using a Microsoft Excel 2003 spread sheet available on the U.S. Department of Energy’s (DOE’s) website: www1.eere.energy.gov/buildings/appliance_stan dards/commercial/beverage_machines.html

E.2 USER INSTRUCTIONS FOR LIFE -CYCLE COST SPREADSHEET

The LCC spreadsheet allows the user to perf orm LCC analyses of any of two equipment classes and three size categories of beverage vending machine equipment. To execute in sensitivity mode, the spreadsh eet requires only Microsoft Ex cel 2003 or a later version. To execute in uncertainty mode, the model requir es the commercially-available Crystal Ball software.

The spreadsheet represents the latest vers ion of the applicable model and has been developed and tested with Microsoft Excel. Th e LCC spreadsheet or workbook consists of the following worksheets as described in Table E.2.1.

Table E.2.1 Description of Tabs in the Life-Cycle Cost Spreadsheet
Worksheet Tab Title Description

Notes
Instructions Names ranges CH Prob Ship

CH LCC vs. Installed Cost

CH PBP vs. Installed Cost

Equipment Parameters

Summary

Energy $ Expend

Energy $ Expend, Manufacturing

Energy $ Expend, Office Health Notes on changes to this model since the notice of proposed rulemaking
Instructions for use of the model. Describes the purpose of named ranges in the model. Chart showing the probably of a beverage vending machine shipment being delivered to a state. Chart showing tradeoff between efficiency level (installed cost) and LCC for the current analysis options. Chart showing tradeoff between efficiency level (installed cost) and PBP period in
years. Calculates values for selected value and performance metrics of beverage vending machine equipment needed fo r the National Impact Analysis model. Table shows the annual equipment cost data in 2008$ by efficiency level for each equipment class and capacity. Contains the input selections and a summar y table of energy use, operating costs, LCC, and payback. Contains summary calculations that translate energy savings in individual building types and equipment classes into the values needed for the particular selections made in a given run of the model.
Contains estimates of base annual dollar ex penditures for electricity for the various beverage vending machine system options, locations when the owner of the site is a manufacturing business.
Contains estimates of base annual dollar ex penditures for electricity for the various beverage vending machine system options, locations when the owner of the site is an office-oriented service business or a health care facility.

E-1 BVM_FINAL_FR_TSD_AppendixE_08-14-09.docEnergy $ Expend, Retail Contains estimates of base annual dollar ex penditures for electricity for the various beverage vending machine system options, locations when the owner of the site is a retail store. Energy $ Expend, School Contains estimates of base annual dollar ex penditures for electricity for the various beverage vending machine system options, locations when the owner of the site is a school, college or university or other state-run facility such as a prison. Energy $ Expend, Military Contains estimates of base annual dollar ex penditures for electricity for the various beverage vending machine system options, locations when the owner of the site is a military facility. Energy $ Expend, Other Contains estimates of base annual dollar ex penditures for electricity for the various beverage vending machine system options, locations when the owner of the site is a business in the “other” category (mostly warehouses and entertainment venues). Engineering Contains the manufacturer price data for systems at each efficiency level. Also contains data on unit weight, energy consumption in various configurations, and summarizes data on cost elements othe r than energy by efficiency level. Markups and Market Contains the distributor, contractor, and national accounts markups, the sales tax, the installation price, and the refurb ishment and maintenance costs.
Sales Tax Contains state and state plus local sale s tax rates as well as weighted average population rates from state data. In addition, contains calculation cells for several of the variables that vary by state when the model is run in uncertainty mode (Monte Carlo analysis) using Crystal Ball.
Install and Maintenance Contains detailed data on relative installation and maintenance cost indices by State Cost Index used in the model. Building Energy, Contains data on energy use in standardized beverage vending machine units in Manufacturing manufacturing buildings for the various equipm ent classes by effici ency level, State. Building Energy, Office Contains data on energy use in standardized beverage vending machine units in Health office and health care buildings for the variou s equipment classes by efficiency level, State. Building Energy, Retail Contains data on energy use in standardized beverage vending machine units in retail buildings for the various equipment cl asses by efficiency level, State. Building Energy, School Contains data on energy use in standardized beverage vending machine units in school, college, and prison buildings for th e various equipment cl asses by efficiency level, State. Building Energy, Military Contains data on energy use in standardized beverage vending machine units in military facilities for the various equipmen t classes by efficiency level, State. Building Energy, Other Contains data on energy use in standardized beverage vending machine units in “other” buildings (warehouses and entertainment venues) for the various equipment classes by efficiency level, State. Energy Price Projections Translate average commercial electricity pr ices into prices by building type. Also contains current energy prices and projections of future energy prices from the most
current Annual Energy Outlook

. Electricity Price Ratios Contains the national average electricity prices for commercial (businesses) and Commercial Buildings industrial facilities that use beverage vending machines, compared with average Energy Consumption commercial electricity prices, from microdata in the Energy Information Survey (CBECS) Administration CBECS 2003. Also shows the ratio of each buildings electricity prices to all commercial buildings. Electricity Price Data Contains average electricity costs breakdown for all building types. State Energy Price Detail Contains data on the average commercial and industrial electricity prices by the State in 2008.
Discount Rate Contains data from which an average discount rate and a distribution of discount rates are determined.

E-2 BVM_FINAL_FR_TSD_AppendixE_08-14-09.docLifetime Contains the calculations for the survival function for beverage vending machine units lifetime in years.
Labels This is used as a go-between for user in puts and the rest of the worksheets—do not modify this sheet.



### Basic instructions for operating the LCC spreadsheets are as follows:

To execute the spreadsheet, use Microsoft Ex cel and Crystal Ball Version 7.0 or later. The spreadsheet can be run in sensitivity mode using only Excel.

1. Once LCC file is downloaded, open Crystal Ball, and open the file inside of Crystal Ball using Excel. At the bottom, click on the tab for sheet summary.

2. Use Excel’s commands (View/Zoom) at the t op to change the size of the display for personal monitor fit.

3. Interact with the spreadsheet by clicking c hoices or entering data using the graphical interface that comes with the spreadsheet. Choices can be selected from the various inputs listed under “Use r Options” heading.

Under the “User Options” heading, make choi ces from the selection buttons and boxes for the following: (1) uncertainty option; (2) electri city price projection; (3) start year; (4) equipment class (and capacity); (5) effici ency level; (6) market channel (currently limited to market-weighted average); (7) region; (8) location of mach ine (building type); (9) owner (business type); (10) region; (11) installation option (currently limited to only new units); (12) shipment weights option (currently limited to population); (13) installation cost projection; (14) repair cost projection; and (15) level of any costreducing measures, such as Federal tax credits.

A new discount rate or lifetime can also be en tered if a value other than the default value or default distribution is wa nted; however, this overwrite s the existing formula. As a result, DOE does not recommend saving the sp readsheet after the code is changed. The default discount rates can be changed by goi ng to the “Discount Rate” tab and changing the default values in Cells D4 through D10. A different lifetime can be selected by going to the Lifetime tab and entering a different va lue in the green box at cell D4 and Cell I3.

4. To change inputs listed under “User Options,” select input to be changed by either clicking on the appropriate button or selecting the appropriate input fr om the input box. If the Crystal Ball Option is selected, red pane ls appear on the Summ ary page advising the user not to use the data that appear in the results section of the Su mmary page, but instead to use the Crystal Ball results.

5. Crystal Ball results may be saved by using the options in Create Report and Extract Data functions on the Control Box window.

To run a Crystal Ball simulation, do the following:

To produce Monte Carlo results using Crystal Ball, select Run from the menu bar. To make basic changes in the run sequence (incl uding altering the number of trials), select Run Preferences from the Run menu. After each simulation run, the user should choose

E-3 BVM_FINAL_FR_TSD_AppendixE_08-14-09.doc

### Reset (also from the Run menu) before Run can be selected again. Once Crystal Ball has completed its run sequence, it will produce a se ries of distributions. Using the menu bars on the distribution results, it is possible to obtain further statistical information. The time taken to complete a run sequence can be re duced by minimizing the Crystal Ball window in Excel.

A step-by-step summary of the procedure for running a distribution analysis is outlined below: 1) Find the Crystal Ball toolbar (at top of screen),
2) Click on RUN,
3) Select Preference and choos e from the following choices: a) Monte Carlo, b) Latin Hypercube (recommended), c) Initial seed choices and pote ntial constancy between runs, or d) Select number of Monte Carlo Trials (1,000 or 5,000 recommended).
4) To run the simulation, use the followi ng sequence on the Crystal Ball toolbar: RUN RESET RUN
5) Wait until the program shows that the simulation is complete. This takes only a few minutes, depending on computer speed.

Crystal Ball Results
1) After completion of the simulation, click on the Windows tab bar labeled Crystal Ball to see the distribution charts.
2) Several of the variables of interest , including LCC, LCC savings, and PBPs are defined as Forecast cells. The frequency char ts display the results of the Crystal Ball simulations or trials. Click on any chart to bring it into vi ew. The charts show the low and high endpoints of the forecasts. The Vi ew selection on the Crystal Ball toolbar can be used to specify whether cumula tive or frequency plots are shown. 3) To calculate the probability that LCC sa vings will occur, either type 0 in the box by the right arrow, or move the arrow key with the cursor to 0 on the scale. The value in the Certainty box shows the likelihood that the LCC savings will occur. To calculate the certainty of PBP being below a certain number of years, choose that value as the high endpoint. 4) To generate a printout report, select Create Report from the Run menu. The toolbar choice of Forecast Windows enables the ability to select the charts and statistics. For further information on Crystal Ball outputs, refer to Understanding the Forecast Chart in the Crystal Ball manual. Crystal Ball results may also be saved to a separate Excel spreadsheet by using the Extract Data f unction under Analyze on the Control Panel window.

E-4 BVM_FINAL_FR_TSD_AppendixE_08-14-09.doc