8A-i 

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

TABLE OF CONTENTS

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

8A.2 USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET ...................... 8A-1

REFERENCES ......................................................................................................................... 8A-4

LIST OF TABLES

Table 8A.2.1 Description of Worksheets in LCC Spreadsheet ................................................ 8A-1
8A-1 

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

8A.1 INTRODUCTION

Detailed results of the life-cycle cost (LCC) and payback period (PBP) analyses for 

automatic commercial ice-making equipment can be examined using a Microsoft Excel 

spreadsheet available on the U.S. Department of Energy’s (DOE’s) Building Technologies (BT) 

website at

http://www1.eere.energy.gov/buildings/appliance_standards/commercial/automatic_ice_making_

equipment.html. 

8A.2 USER INSTRUCTIONS FOR LIFE-CYCLE COST SPREADSHEET

The spreadsheet allows the user to perform LCC analyses of any of 21 separate 

equipment classes of automatic commercial ice-making (ACIM) equipment. To fully execute the 

spreadsheet requires Microsoft Excel and Oracle’s Crystal Ball software, both of which are

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 2007 and with Oracle Crystal 

Ball, Fusion Edition, Release 11.1.2.0.00. Table 8A.2.1 describes the worksheets in the LCC 

spreadsheet. 

Table 8A.2.1 Description of Worksheets in LCC Spreadsheet

Worksheet Description

Labels This is used as a go-between for user inputs and the rest of the worksheets. 

Modifying this worksheet can change and possibly impair operation of the model, 

so DOE recommends caution when editing this worksheet.

LCC Summary Contains the input selections and a summary table of energy and water use, 

operating costs, LCC, and payback.

Crystal Ball Contains outputs for each equipment class from the Crystal Ball/Monte Carlo 

analysis. 

NIA Output Contains a full output of all data that is uploaded to the National Impacts Analysis 

Model. 

Econ Trends Contains electricity and water price forecasts. Also contains experiential (price)

learning data inputs.

Markups Contains data used to derive industry baseline and incremental markups.

Engineering Contains the manufacturer price data for units at each efficiency level. Also 

contains data on harvest rates, utilization rates, energy and water consumption, and 

summarizes other engineering derived data. 

Discounted Costs Contains annual operating costs in cash flow tables. 

Discount Rate Contains all data used to calculate discounts rates for each building type and an 

aggregate discount rate for the United States.

Electricity Prices Contains AEO20111 population-weighted electricity prices by state, AEO2011 

electricity price projections, and electricity price ratios derived for the various 

building types using 2003 Commercial Building Energy Consumption (CBECS) 

data.

Water Prices Contains population-weighted water/wastewater prices by state, and 

water/wastewater price projections.

Installation Costs Contains adjustment factors to vary installation costs by state. Derived from data 

published by RS Means Publishers and Consultants.
8A-2 

Table 8A.2.1 (cont.) 

Worksheet Description

Sales Tax Contains data on sales tax rates and population data by state.

Population Contains 2010 census data on population by state.

Maintenance and 

Repair Costs

Contains inputs for different schedules of maintenance and repair costs and 

summarizes the data in an annualized format.

Basic instructions for operating the LCC spreadsheets are as follows:

1. Once you have downloaded the LCC file from the DOE BT website, open the file using 

Excel. Select “Yes” when asked if you want to enable macros. At the bottom of the Excel 

window, click the tab for the Summary worksheet. Note that if you plan to run the Monte 

Carlo routine, you will need to have Crystal Ball loaded as an add-in and activated. 

2. Use Excel’s View/Zoom commands at the top menu bar to size the display to your 

monitor. 

3. You can interact with the spreadsheet by clicking choices or entering data using the 

graphical interface on the LCC Summary worksheet. Select choices from the various 

drop-down input boxes throughout the LCC Summary worksheet. 

4. From the user-defined input options, select from the buttons and boxes for the following: 

(1) assumed baseline level; (2) equipment class; (3) analysis year (year of purchase) ; 

(4) economic growth case; (5) year to discount costs to; (6) standard efficiency level; 

(7) building type; (8) state or region to analyze; (9) conservation program to model; 

(10) installation cost escalation; (11) repair cost escalation; (12) analysis type; (13) price 

learning toggle; and (14) discounting toggle. You can start a complete Crystal Ball 

simulation by clicking the Monte Carlo button. All inputs, with the exception of some 

conservation program inputs, are either drop-down option boxes or toggle boxes (checked 

boxes make the statement true).

a. Equipment classes are defined as particular combinations of equipment families, 

cooling methods, harvest rate bins, and ice-making processes. Because there are three

potential equipment families, two potential cooling methods (air and water), and two 

or three possible harvest rate bins, and two different ice-making processes, there are 

21 potential equipment classes that could be analyzed. If you select a combination of 

equipment families, cooling methods, harvest rate bins, and ice-making processes that 

results in a potential equipment class that has not been analyzed by DOE (because

few or no shipments have been identified), the Annual Electricity Bill shown on the 

Summary tab will be $0 or blank. 

b. There is room for up to eight possible efficiency levels, including the baseline 

efficiency level, though not all equipment classes have eight representative levels. 

If an equipment class contains fewer than eight levels, the extra levels will have blank 

values in the calculated output cells on the LCC Summary tab.
8A-3 

5. This spreadsheet gives the user two types of calculation methods: 

a. If base and sensitivity analysis is chosen, then all calculations are performed for a set 

of single input values, usually an average. The new results are shown on the 

Summary tab as soon as the input values are changed through a drop-down or toggle 

box. 

b. Alternatively, if the Monte Carlo option is chosen or if the button is clicked, the 

spreadsheet generates a set of results from calculations for each equipment family, 

cooling method, harvest rate bin, and ice-making process (which together define an 

equipment class). For a number of inputs, the Crystal Ball software has custom 

distributions that it uses to set the level of the input. The model runs each of the 

studied classes in turn, performing 10,000 model runs for each. At the end of each 

equipment class run, the model records to the Crystal Ball tab a large number of 

outputs that you can then work with for further analyses. The Monte Carlo button 

executes a macro written in Visual Basic for Applications to loop through all 

equipment classes and selected efficiency levels. The Monte Carlo analysis usually 

takes 4–8 hours to complete for all 21 equipment classes. 
8A-4 

REFERENCES

1. DOE 2011. Annual Energy Outlook. U.S. Department of Energy, Energy Information 

Administration. Washington, D.C. DOE/EIA-0383(2011).