8A-i

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE-CYCLE COST ANALYSIS 

SPREADSHEET

TABLE OF CONTENTS

8A.1 INTRODUCTION .................................................................................................. 8A-1

8A.2 DESCRIPTION OF LIFE-CYCLE COST WORKSHEET.................................... 8A-1

8A.3 BASIC INSTRUCTIONS....................................................................................... 8A-2
8A-1

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE-CYCLE COST ANALYSIS 

SPREADSHEET

8A.1 INTRODUCTION

It is possible to examine and reproduce the detailed results of the life-cycle cost (LCC) 

and payback period (PBP) analysis using a Microsoft Excel spreadsheet available on the U.S. 

Department of Energy’s (DOE’s) rulemaking website for refrigerated beverage vending 

machines (BVMs).

1

 To fully execute the spreadsheets requires both Microsoft Excel and Crystal 

Ball2 software. Both applications are commercially available. The spreadsheet posted on the 

DOE website represents the latest version and has been tested with Microsoft Excel 2010. 

8A.2 DESCRIPTION OF LIFE-CYCLE COST WORKSHEET

For all of the equipment classes, DOE created a single LCC workbook file containing a 

collection of worksheets. Each worksheet represents a conceptual component within the LCC 

calculation. To facilitate navigability and identify how worksheets are related, each worksheet 

contains an area on the extreme left showing variables imported to and exported from the current 

worksheet. The LCC workbook contains the following worksheets:

Summary The Summary worksheet contains a user interface to manipulate energy price 

trends, start year, and number of trials in the Crystal Ball simulation. It also 

includes a button to run the Crystal Ball simulation. LCC and PBP simulation 

results for each efficiency level are displayed on this sheet.

LCC & Payback The LCC & Payback worksheet shows LCC calculation results for different 

efficiency levels for a single sampled customer. During a Crystal Ball 

simulation, the spreadsheet records the LCC and PBP values for every 

sampled customer.

Sample The Sample worksheet contains the customer data for each equipment class. 

During a Crystal Ball simulation, DOE uses these customer characteristics to 

determine the analysis parameters.

Cost Efficiency

Data

The Cost Efficiency Data worksheet provides estimated retail prices as a 

function of efficiency levels, and this set of data is used as inputs into the 

LCC calculations in the LCC & Payback worksheet. 

Energy Use The Energy Use worksheet calculates annual energy use for each equipment

class. The annual energy use calculations for each design option are inputs to 

the LCC & Payback worksheet to calculate the annual operating cost of the 

LCC.

Base Case 

Distribution

The BaseCase Distribution worksheet includes the equipment efficiency 

distribution for the effective year of the standard.
8A-2

Electricity 

Prices

The Electricity Prices worksheet shows the estimated electricity price for each 

state.

Electricity 

Prices Ratios 

CBECS

This is an adjustment for electricity prices based on the type of business that 

BVM equipment is located in. DOE used the 2003 Commercial Building 

Energy Consumption Survey (CBECS) data3 set to identify the average 

prices paid by the four kinds of commercial businesses in this analysis 

compared with the average prices paid by all commercial customers 

(manufacturing and federal facilities are assumed to pay the average 

industrial price). The ratio of prices paid by the four types of commercial 

buildings versus the national average rate was used to increase or decrease 

the average electricity prices paid by business type. 

Electricity Price 

Trend

The Electricity Price Trend worksheet shows the future price trend for 

electricity. DOE used energy price data and forecasts from the Energy 

Information Administration’s (EIA's) Annual Energy Outlook 20134 for the 

period until 2040 and extrapolated beyond 2040.

Discount Rate The Discount Rate worksheet contains the distributions of discount rates.

Lifetime The Lifetime worksheet contains the distribution of lifetimes.

LCC Box Plots The LCC Box Plots worksheet can display box plots of the LCC savings for 

all efficiency levels and equipment classes. Box plots for a single equipment 

class are visible at any given time. The user can select the equipment class to 

show on the sheet. 

PBP Box Plots The PBP Box Plots worksheet can display box plots of the PBP for all 

efficiency levels and equipment classes. Box plots for a single equipment 

class are visible at any given time. The user can select the equipment class to 

show on the sheet.

8A.3 BASIC INSTRUCTIONS

Basic instructions for operating the LCC spreadsheet are as follows:

1. Once the LCC spreadsheet has been downloaded, open the file using Excel. Click 

“Enable Content” when prompted and then click on the tab for the Summary worksheet.

2. Use Excel's View/Zoom commands at the top menu bar to change the size of the display 

to fit your monitor.

3. The user can change the parameters listed under "User Input:" on the Summary

worksheet. There are three drop-down boxes. The default parameters are:
8A-3

a. Energy Price Trend: Defaults to “AEO 2014 Reference Case.” To change the 

input, use the drop-down menu and select the desired trend (Reference, Low, or 

High).

b. Start Year: Defaults to “2019.” To change the value, use the drop-down menu and 

select the desired year.

c. # of Trials: Defaults to “10,000.” To change the value, use the drop-down menu 

and select the desired number of trials (100, 1,000, 2,000, 5,000, 10,000, or 

25,000).

d. Subgroup: Defaults to “Full Sample.” To change the value, use the drop-down 

menu and select the desired customer subgroup. Customer subgroups will be 

included at the notice of proposed rulemaking (NOPR) stage. 

4. To run the Crystal Ball simulation, click the “Run” button (you must re-run after 

changing any parameters). The spreadsheet will then be minimized. You can monitor the 

progress of the simulation by watching the count of iterations at the left bottom corner. 

When the simulation is finished, the worksheet named Summary will reappear with the 

results. The results for all equipment classes will reflect the results of the new 

simulation. 
8A-4

REFERENCES

1 http://www1.eere.energy.gov/buildings/appliance_standards/rulemaking.aspx/ruleid/73. 

Accessed April 24, 2014. This material is available in Docket #EERE-2013-BT-STD-0022 at 

regulations.gov.

2 Crystal Ball is available at 

http://www.oracle.com/us/products/applications/crystalball/overview/index.html. Accessed May 

9, 2014. This material is available in Docket #EERE-2013-BT-STD-0022 at regulations.gov. 

3 U.S. Department of Energy–Energy Information Administration. 2003. Commercial Building

Energy Consumption Survey (CBECS), Public Use Data, Micro-data files. (Last accessed August 12, 

2014) <http://www.eia.gov/consumption/commercial/data/2003/> 

4 Energy Information Administration, Annual Energy Outlook 2014 with Projections to 2040, 

2014. Washington, DC.