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 22
E­ii
 

EERE-2006-STD-0125 COMMENT 22
                

                               

                         

           

     

                     

                             

                       

     

                           

                              

                     

              

                           

           

       

     

                         

     

                       

                       

                 

                           

                         

   

                           

                         

                

                             

                           

  

                             

                     

                             

                             

                 

                        

                     

                      

APPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

E.1 INTRODUCTION

It is possible to examine and reproduce the detailed results of the LCC and PBP analyses

for beverage vending machines (BVMs) using a Microsoft Excel® spreadsheet available on the

U.S. Department of Energy’s website at:

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

E.2 USER INSTRUCTIONS FOR LCC SPREADSHEET

The spreadsheet is called “BVM_LCC_5CB_09­12­2007.xls” and it allows the user to

perform LCC analyses of any of two equipment classes and three capacities of BVM equipment.

To fully execute the spreadsheet requires only Microsoft Excel® software, which is

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® 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 LCC Spreadsheet

CH Lifecycle vs. Installed

Cost

Chart showing tradeoff between efficiency level (Installed Cost) and lifecycle

cost for the current analysis options.

CH Payback vs. Installed

Cost

Chart showing tradeoff between efficiency level (Installed Cost) and payback

period in years.

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 $ Save, Manufacturer 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 $ Save, OfficeHealth 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 $ Save, 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 $ Save, School Contains estimates of base annual dollar expenditures for electricity for the

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

Energy $ Save, 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 $ Save, 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.

E­1
 

EERE-2006-STD-0125 COMMENT 22
    

                           

                 

                       

                          

                           

       

                               

         

                         

                   

                                 

                                 

               

                                 

                     

                                 

               

                               

                   

     

                             

                      

                   

                             

                     

                   

                  

                               

   

                             

     

                           

                               

         

                   

                               

                          

                                 

                                    

                                   

       

Table E.2.1. (contd)
 

Markups and Market Contains the distributor, contractor, and national accounts markups, the sales tax,

the installation price, and the repair and maintenance costs.

Sales Tax Contains detailed calculations to determine weighted average sales tax 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 Maint Cost Index Contains detailed data on relative installation and maintenance cost indices by

State used in the model

Energy $ Save, Manufacturer Contains data on energy use in standardized BVM units in Manufacturing

buildings for the various equipment classes by efficiency level, State

Energy $ Save, OfficeHealth Contains data on energy use in standardized BVM units in office and health care

buildings for the various equipment classes by efficiency level, State

Energy $ Save, Retail Contains data on energy use in standardized BVM units in retail buildings for

the various equipment classes by efficiency level, State

Energy $ Save, 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

Energy $ Save, Military Contains data on energy use in standardized BVM units in military facilities for

the various equipment classes by efficiency level, State

Energy $ Save, 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 Contains current energy prices by State, and projections of future energy prices

from the Annual Energy Outlook. Also performs functions to translate average

commercial electricity prices into prices by state and building type

Electricity Price Ratios

CBECS

Contains the national average electricity prices experienced in the four types of

commercial buildings (businesses) and two types of industrial facilities that use

BVMs, compared with average commercial electricity prices, from microdata in

the EIA Commercial Buildings Energy Consumption Survey (CBECS) 2003

State Energy Price Detail Contains data on the average commercial and industrial electricity prices by state

in 2006

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

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.

E­2
 

EERE-2006-STD-0125 COMMENT 22
                             

                           

               

                           

                        

                            

                       

                           

     

                                   

                          

                         

                               

                              

                                 

           

                               

                           

                               

                                 

                 

                                 

           

                 

                             

                              

                            

                               

                             

                              

                            

                 

                             

  

                     

        

                   

         

           

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) region, (7) business (and

building) type, (8) installation option, (9) shipment weights option, (7) installation cost

projection, and (8) repair cost projection, (9) level of any cost­reducing 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 Department 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 C4 through C7. 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

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)
 

E­3
 

EERE-2006-STD-0125 COMMENT 22
                               

                       

                            

                         

                         

                          

                           

   

       

                           

                 

                         

                        

                          

                                

                            

                     

                               

                                    

                           

                          

                 

                            

                         

                          

                      

                           

                 

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).

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, anywhere from 1 minute to 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 of 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.

E­4
 

EERE-2006-STD-0125 COMMENT 22