APPENDIX E. USER INSTRUCTIONS 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 the Life-Cycle Cost Spreadsheet .......................................E-1 

E-i 

BVM_FINAL_FR_TSD_AppendixE_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
 

 

 

 

 

APPENDIX E. USER INSTRUCTIONS FOR THE 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 

equipment. This analysis is performed using a Microsoft Excel 2003 spreadsheet available on the 

U.S. Department of Energy’s (DOE’s) website: 

www1.eere.energy.gov/buildings/appliance_standards/commercial/beverage_machines.html 

E.2 USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET 

The LCC spreadsheet allows the user to perform LCC analyses of any of two equipment 

classes and three size categories of beverage vending machine equipment. To execute in 

sensitivity mode, the spreadsheet requires only Microsoft Excel 2003 or a later version. To 

execute in uncertainty mode, the model requires the commercially-available Crystal Ball 

software. 

The spreadsheet represents the latest version of the applicable model and has been 

developed and tested with Microsoft Excel. 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 

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 for 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 summary 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 expenditures 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 expenditures 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.doc 

EERE-2006-STD-0125 COMMENT 74
 

 

 

 

 

 

Energy $ Expend, Retail	 Contains estimates of base annual dollar expenditures 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 expenditures 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 expenditures 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 expenditures 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 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 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 equipment classes by efficiency 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 various 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 classes 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 the various equipment classes by efficiency 

level, State. 

Building Energy, Military	 Contains data on energy use in standardized beverage vending machine units in 

military facilities for the various equipment 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 prices 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.doc 

EERE-2006-STD-0125 COMMENT 74
Lifetime 	 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 inputs 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 Excel 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 top to change the size of the display for 

personal monitor fit. 

3.	 Interact 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, make 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; (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 entered if a value other than the default value 

or default distribution is wanted; however, this overwrites the existing formula. As a 

result, DOE 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,” select input to be changed 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 not to 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, select Run from 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 should choose 

E-3 

BVM_FINAL_FR_TSD_AppendixE_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
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 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 potential constancy between runs, or 

d) Select number of Monte Carlo Trials (1,000 or 5,000 recommended). 

4) To run the simulation, use the following 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 charts display the results of the Crystal Ball 

simulations or trials. 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 cumulative or frequency plots are 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 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 function under Analyze on the Control Panel 

window. 

E-4 

BVM_FINAL_FR_TSD_AppendixE_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74