APPENDI X 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. US ER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

E.1 INTRODUCTION
Appendix E contains instructions to examin e and reproduce the deta iled results of the life-cycle co st (LCC) and payback period (PBP) analyses for beverage vending machine (BVM) equipment. This analysis is performed using a Microsoft Excel spreadsheet available on the U.S. Department of Energy’s website at: http://www1.eere.energy.gov/build ings/appliance_standards/comm ercial/beverage_machines.ht ml

E.2 USER INSTRUCTIONS FOR LCC SPREADSHEET
The spreadsheet is called “BVM_LCC_2-6-2009.xl s” and it allows th e user to perform LCC analyses of any of two equi pment classes and three si ze cat egories of BVM equipment. To fully execute in sensitivity mode the spreadsh eet requires only Microsoft Excel software. To fully execute in uncertainty mode, the model also requires commercially-available Crystal Ball ®

. The spreadsheet posted on the DOE website repres ents 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 followi ng worksheets as described in Table E.2.1

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

Notes Notes on changes to this model since the advance notice of proposed rulemaking (ANOPR) Instructions Instructions for use of the model Names ranges Describes the purpose of named ranges in the model CH Prob Ship Chart showing the probably of a BVM shipment being delivered to a state. CH Life-cycle vs. Installed Cost Chart showing tradeoff betw een efficiency level (Installed Cost) and LCC for the current analysis options. CH Payback vs. Installed Cost Chart showing tradeoff between efficiency level (Installed Cost) and PBP period in years. Equipment Parameters Calculates values for selected va lue and performance metrics of BVM equipment that are needed by the National Impact Analysis model. Table shows the annual equipment cost data in 2008$ by efficiency level for each equipment class and capacity. Summary Contains the input selections and a summar y table of energy use, operating costs, LCC and payback. Energy $ Expend 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. Energy $ Expend, Manufacturing Contains estimates of base annual dolla r expenditures for electricity for the various BVM system options, locations when the owner of the site is a manufacturing business. Energy $ Expend, Office Health Contains estimates of base annual dolla r expenditures for electricity for thevarious BVM system options, locations when the owner of the site is an officeoriented service business or a health care facility Energy $ Expend, Retail Contains estimates of base annual dolla r expenditures for electricity for the various BVM system options, locations when the owner of the site is a retail
store. Energy $ Expend, School Contains estimates of base annual dolla r expenditures for electricity for the

E-1various BVM system options, locations when the owner of the site is a school, college or university or other state-run facility such as a prison.

Table E.2.1 . (contd)
Energy $ Expend, Military Contains estimates of base annual dolla r expenditures for electricity for the various BVM system options, locations when the owner of the site is a military facility. Energy $ Expend, Other Contains estimates of base annual dolla r expenditures for electricity for the various BVM 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 refurbishment 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 BallTM. Install and Maint Cost Index Contains detailed data on relative installation and maintenance cost indices by State used in the model Bldg Energy, Manufacturing Contains data on energy use in standardized BVM units in manufacturing buildings for the various equipment cl asses by efficiency level, State Bldg Energy, Office Health Contains data on energy use in standardized BVM units in office and health care buildings for the various equipment cl asses by efficiency level, State Bldg Energy, Retail Contains data on energy use in standardized BVM units in retail buildings for the various equipment classes by efficiency level, State Bldg Energy, School Contains data on energy use in standardized BVM units in school, college, and prison buildings for the various equipmen t classes by efficiency level, State Bldg Energy, Military Contains data on energy use in standardized BVM units in military facilities for the various equipment classes by efficiency level, State Bldg Energy, Other Contains data on energy use in standardized BVM units in "other" buildings (warehouses and entertainment venues) for the various equipment classes by efficiency level, State Energy Price Projections Translate average commercial electricity prices into prices by building type. Also contains current energy prices and projections of future energy prices from the Annual Energy Outlook Electricity Price Ratios CBECS Contains the national average electricity prices for commercial (businesses) and industrial facilities that use BVMs, compared with average commercial electricity prices, from microdata in the Energy Information Administration (EIA) Commercial Buildings Energy Consumption Survey (CBECS) 2003. Also shows the ratio of each buildings electricity prices to all commercial buildings Electricity Price Data Contains average electricity cost s breakdown for all building types State Energy Price Detail Contains data on the average commercial and industrial electricity prices by State in 2008 Discount Rate Contains data from which an average discount rate and a distribution of discount rates are determined Lifetime Contains the calculations for the survival function for BVM units lifetime in years Labels This is used as a go-between for user inputs and the rest of the worksheets—do not modify this sheet.
Basic instructions for operating the LCC spreadsheets are as follows:

E-2To execute the spreadsheets f ully you will need Microsoft Excel and Crystal Ball, Version 7.0 or later. The spreadsheet can be run in sensitivity mode using only Excel.

1. Once you have downloaded the LCC file from the Web, first open Crystal Ball and then open the file inside of Crysta l Ball using Excel. At the bottom, click on the tab for sheet Summary.

2. Use Excel’s commands at the t op View/Zoom 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 that comes with the spr eadsheet. Choices can be selected from the various inputs listed under "User Options" heading.
Under the "User Options" heading select choi ces from the selection buttons and boxes for the following: (1) Uncertainty option (2) el ectricity price projec tion, (3) start year, (4) equipment class (and capacity), (5) effici ency level, (6) market channel (currently limited to market-weighted average) (7) regi on, (8) location of machine (building type), (9) owner (business type), (10) region, (11) installation optio n (currently limited to only new units), (12) shipment weights op tion (currently limited to population), (13) installation cost projecti on, and (14) repair cost project ion, (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, the U.S. Department of Energy doe s not recommend saving the spreadsheet after the code is changed. The default discount ra tes can be changed by going 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 value in the green box at cell D4 and Cell I3.

4. To change inputs listed under “User Options” ei ther select the input you wish to change by either clicking on the appropr iate button or selecting the appropriate input from the input box. If the Crystal Ball Option is select ed, red panels appear on the Summary page advising the user to not use the data that a ppear in the results section of the Summary 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, you need simply select Run from the Run menu (on the menu bar). To make basi c changes in the run sequence, including altering the number of trials, select Run Preferences from the Run menu. After each simulation run, the user needs to select Rese t (also from the Run menu) before Run can be selected again. Once Crystal Ball has co mpleted its run sequence it will produce a series of distributions. Using the menu bars on the distribution result s it is possible to

E-3E-4 obtain further statistical information. The tim e taken to complete a run sequence can be reduced 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 a nd whether you want it to be constant between runs d) Select number of Monte Carlo Trials (we suggest 1,000 or 5,000). 4) To run the simulation, follow the followi ng sequence (on the Crys tal Ball toolbar) RUN RESET RUN 5) Now wait until the program informs you that the simulation is completed. Depending on the speed of your computer , this takes only a few minutes (typically, about 5-10 minutes on a Pen tium 4 machine, depending on whether you suppress the figures for speed or want to watch the figures update).
Crystal Ball Results 1) After the simulation has finished, to s ee the distribution char ts generated, click on the Windows tab bar that is labeled Crystal Ball. 2) Several of the variables of interest, including life-cycle cost, the life-cycle cost savings, and the payback periods are defined as Forecast cells. The frequency charts display the results of the simulati ons, or trials, performed by Crystal Ball. Click on any chart to bring it into vi ew. The charts show the low and high endpoints of the forecasts. The View sele ction on the Crystal Ball toolbar can be used to specify whether you want cu mulative or frequency plots 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 like lihood that the LCC savings will occur. To calculate the certainty of payback period being below a certain number of years, choose that value as the high endpoint. 4) To generate a printout report, sele ct Create Report fr om the Run menu. The toolbar choice of Forecast Windows allows you to select the charts and statistics you are interested in. For further informa tion on Crystal Ball outputs, please refer to Understanding the Forecast Chart in the Crystal Ball manual. Crystal Ball results also may be saved to a separate Excel spreadsheet by using the Extract Data function under Analyze on the Control Panel window.