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-i
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) analyses using a Microsoft Excel spreadsheet available on the U.S. 

Department of Energy’s rulemaking website for miscellaneous refrigeration products.

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 product 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 household. During a Crystal Ball 

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

sampled household.

Household 

Sample

The Household Sample worksheet contains the household data for each 

product class. During a Crystal Ball simulation, DOE uses these household 

characteristics to determine the analysis parameters.

Equipment

Price

The Equipment Price worksheet calculates retail price values used as inputs in 

the LCC calculations in the LCC & Payback worksheet. 

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

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 product efficiency 

distribution for the effective year of the standard.

8A-1
Electricity 

Prices

The Electricity Prices worksheet shows the estimated electricity price for each 

state.

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 20133 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 for equipment of 

that product class.

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

all efficiency levels and product classes. Box plots for a single product are 

visible at any given time. The user can select the product 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 product classes. Box plots for a single product are 

visible at any given time. The user can select the product to show on the sheet. 

Frequency 

Charts

The Frequency Charts worksheet contains example histograms of LCC, LCC 

savings, and PBP. These are the values calculated in the Monte Carlo 

simulations of 25,000 households. DOE can create frequency charts for any 

product and efficiency level. 

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:

a. Energy Price Trend: Defaults to “AEO 2013 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 “2021.” To change the value, use the drop-down menu and 

select the desired year.

8A-2
c. # of Trials: Defaults to “25,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).

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 product classes will reflect the results of the new simulation. 

8A-3
REFERENCES

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

Accessed June 26, 2014. This material is available in Docket #EERE-2011-BT-STD-0043 at 

regulations.gov.

2 Crystal Ball is available at 

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

November 15, 2013. This material is available in Docket #EERE-2011-BT-STD-0043 at 

regulations.gov. 

3 Energy Information Administration, Annual Energy Outlook 2013 with Projections to 2040, 

2013. Washington, DC.

8A-4