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-i 

EERE-2006-STD-0125 COMMENT 46
E-ii 

EERE-2006-STD-0125 COMMENT 46
APPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET 

E.1 INTRODUCTION 

Appendix E contains instructions to examine and reproduce the detailed results of the 

life-cycle cost (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/buildings/appliance_standards/commercial/beverage_machines.ht

ml

E.2 USER INSTRUCTIONS FOR LCC SPREADSHEET 

The spreadsheet is called “BVM_LCC_2-6-2009.xls” and it allows the user to perform 

LCC analyses of any of two equipment classes and three size categories of BVM equipment. To 

fully execute in sensitivity mode the spreadsheet 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 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 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 between 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 value 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 summary 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 dollar 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 dollar expenditures for electricity for the 

various 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 dollar 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 dollar expenditures for electricity for the 

E-1 

EERE-2006-STD-0125 COMMENT 46
various 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 dollar 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 dollar 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 other 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 sales 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 classes 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 classes 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 equipment 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 costs 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-2 

EERE-2006-STD-0125 COMMENT 46
To execute the spreadsheets fully 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 Crystal Ball using Excel. At the bottom, click on the tab for sheet 

Summary. 

2. Use Excel’s commands at the top 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 spreadsheet. Choices can be selected from the 

various inputs listed under "User Options" heading. 

Under the "User Options" heading select choices from the selection buttons and boxes for 

the following: (1) Uncertainty option (2) electricity price projection, (3) start year, 

(4) equipment class (and capacity), (5) efficiency level, (6) market channel (currently 

limited to market-weighted average) (7) region, (8) location of machine (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, and (14) repair cost projection, (15) level of any costreducing measures such as Federal tax credits. 

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 overwrites the existing formula. As a 

result, the U.S. Department of Energy does not recommend saving the spreadsheet after 

the code is changed. The default discount rates 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” either select the input you wish to change 

by either clicking on the appropriate button or selecting the appropriate input from the 

input box. If the Crystal Ball Option is selected, red panels appear on the Summary page 

advising the user to not use the data that appear 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 basic 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 Reset (also from the Run menu) before Run can 

be selected again. Once Crystal Ball has completed its run sequence it will produce a 

series of distributions. Using the menu bars on the distribution results it is possible to 

E-3 

EERE-2006-STD-0125 COMMENT 46
E-4 

obtain further statistical information. The time 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 choose from the following choices: 

a) Monte Carlo 

b) Latin Hypercube (recommended) 

c) Initial seed choices and 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 following sequence (on the Crystal 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 Pentium 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 see the distribution charts 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 simulations, or trials, performed by Crystal Ball. 

Click on any chart to bring it into view. The charts show the low and high 

endpoints of the forecasts. The View selection on the Crystal Ball toolbar can be 

used to specify whether you want cumulative or frequency plots shown. 

3) To calculate the probability that LCC savings 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 payback period 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 allows you to select the charts and statistics 

you are interested in. For further information 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. 

EERE-2006-STD-0125 COMMENT 46