8-A-i

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

SPREADSHEET FOR BOILERS

TABLE OF CONTENTS

8-A.1 USER INSTRUCTIONS ............................................................................................ 8-A-1

8-A.2 STARTUP................................................................................................................... 8-A-1

8-A.3 DESCRIPTION OF LIFE-CYCLE COST WORKSHEETS ..................................... 8-A-1

8-A.3.1 Main LCC worksheet............................................................................................ 8-A-1

8-A.3.2 LCC input worksheet ............................................................................................ 8-A-4

8-A.4 BASIC INSTRUCTIONS FOR OPERATING THE LIFE-CYCLE COST 

SPREADSHEETS....................................................................................................... 8-A-5

LIST OF FIGURES

Figure 8-A.3.1 LCC and Payback Calculation Process......................................................8-A-4
8-A-1

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

SPREADSHEET FOR RESIDENTIAL BOILERS

8-A.1 USER INSTRUCTIONS

The results obtained in this analysis can be examined and reproduced using the Microsoft Excel 

spreadsheets available on the Department of Energy’s (DOE's) boiler rulemaking website: 

http://www1.eere.energy.gov/buildings/appliance_standards/product.aspx/productid/72. From 

that page, follow the links to the notice of data availability rulemaking phase and then to 

Analytical Tools. 

8-A.2 STARTUP

DOE’s spreadsheets enables users to perform life-cycle cost (LCC) and payback period 

(PBP) analyses for each product class. Two spreadsheets exist for all six boiler product classes: a 

spreadsheet labeled LCC and another labeled LCC input. The LCC input contains the raw data 

used for the analysis as well the formulas that led to the processed data that are used in the LCC.

The LCC input serves the purpose of delivering the raw input used in the analysis as well as of 

informing the public on how the processed data in the main LCC is derived, for complete 

transparency. 

The two spreadsheets are independent. The main LCC spreadsheet can be downloaded 

and run separately. In order to change the input of the main LCC based on updated data from the 

LCC input, the user will need to manually copy/paste the data that was modified in the LCC 

input into the main LCC spreadsheet.

To examine the spreadsheets, DOE assumes that the user has access to a personal 

computer with a hardware configuration capable of running Windows XP or later. All LCC 

spreadsheets require Microsoft Excel 2003 or later installed under the Windows operating 

system. Because certain variables inside the spreadsheets are defined as distributions, a copy of 

Crystal Balla (a commercially available add-on program) is required to view them. 

8-A.3 DESCRIPTION OF LIFE-CYCLE COST WORKSHEETS

8-A.3.1 Main LCC worksheet

For all of the boiler product classes, DOE created a single LCC spreadsheet 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 spreadsheet contains the following worksheets: 

 

a

 See http://www.oracle.com/us/products/applications/crystalball/overview/index.html
8-A-2

Statistics The Statistics worksheet contains the statistics of key parameters from the outcome 

of the Monte Carlo simulations for the sample of households

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

and start year inputs, and to run the Crystal Ball simulation. LCC and PBP simulation 

results for each efficiency level are also displayed here.

LCC&PB Calcs The LCC&PB Calcs worksheet shows LCC calculation results for different 

efficiency levels for a single Residential Energy Consumption Survey (RECS) 2009 

household1 or Commercial Building Energy Consumption Survey (CBECS) 2003 

building.

2 During a Crystal Ball simulation, the spreadsheet records the LCC and 

PBP values for every sampled household.

LCC&PB by 

Category

The LCC&PB Calcs worksheet shows LCC calculation results from LCC&PB Calcs 

disaggregated by different markets (residential replacement, residential new 

construction, commercial replacement, and commercial new construction).

Rebuttable 

Payback

The Rebuttable Payback worksheet contains the total and incremental manufacturer 

costs, retail prices, the installation costs, the repair and maintenance costs, energy use 

calculations, and the simple PBP calculations for each efficiency level. DOE’s 

residential furnace and boiler test procedure is used to calculate parameters used in 

energy use calculations.

Equip Price The Equip Price worksheet calculates retail price values used as inputs in the LCC 

calculations in the Summary worksheet. 

Markups The Markups worksheet calculates markup values used as inputs in the Equip Price

worksheet. DOE applied baseline and incremental markups to calculate final retail 

prices. DOE calculated the markups differently for replacement units and new units.

Learning Rate The Markups worksheet calculates learning rate values used as inputs in the Equip 

Price worksheet.

Installation Cost The Installation Cost worksheet provides the weighted average installation cost for 

each design option. These results are used to calculate the total installed prices of the 

design options.

Maintenance 

and Repair Cost

The Maintenance and Repair Cost worksheet provides the maintenance and repair 

costs for each design option. These results are used to determine operating costs for 

the design options.

Labor Costs The Labor Cost worksheet provides the labor cost by region as used to determine the 

installation and repair/maintenance costs.

Building Sample The Building Sample worksheet contains the RECS 2009 and CBECS 2003 

household data for each product class. During a Crystal Ball simulation, DOE uses 

these household characteristics to determine the analysis parameters.

Base Case 

AFUE

The BaseCase AFUE worksheet includes the boiler efficiency distribution for 2020.

AFUE (Existing) The Existing AFUE (existing) worksheet includes the boiler efficiency for all years 

during the period 1978-2009.
8-A-3

Energy Use The Energy Use worksheet calculates annual energy use by fuel type, depending on 

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

the LCC&PB Calcs worksheet to calculate the annual operating cost of the LCC.

Energy Price The Energy Price worksheet shows the estimated monthly natural gas, electricity, 

and oil prices.

Energy Price 

Trends

The Energy Price Trends worksheet shows the future price trends of the different 

heating fuels. DOE used energy price data and forecasts from the Energy Information 

Administration’s (EIA's) Annual Energy Outlook 2013 for the period until 40 and 

extrapolated beyond 2040.3

Equipment 

Specs

The Equipment Specs worksheet contains parameters data used in the analysis.

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

replacement and new units.

Lifetime The Lifetime worksheet contains the distribution of lifetimes for equipment of that 

product class.

Energy Use 

Adjustment 

Factors

The Energy Use Adjustment Factors worksheet contains adjustment factors for 

normal heating degree days and cooling degree days, as well as building shell 

efficiency index.

Weather Data The Weather Data worksheet contains heating degree days, cooling degree days, 

heating and cooling outdoor design temperature, and annual mean temperature by 

weather station.

Forecast Cells The Forecast Cells worksheet contains the outcome of the Monte Carlo simulations 

for the sample of 10,000 households for many parameters used in the analysis and 

the documentation.

Labels The Labels worksheet contains labels used in graphical user interface.

LCC Outputs The LCC Outputs worksheet contains intermediate inputs used for DOE’s National 

Impact Analysis. These inputs include fuel and electricity use, total installed price, 

operating cost, and base case distributions for each product class and efficiency level. 

The inputs are presented for the South and North regions, and for replacement and 

new construction housing markets. The NIA Inputs worksheet also includes energy 

price and shipment information, as well as household sample fractions by Census 

division.

LCC TSD 

Tables

The LCC TSD Tables worksheet contains the tables generated for use in the chapter 

documentation describing the LCC Analysis.

Appendix 

Tables

The Appendix Tables worksheet contains the tables generated for use in the appendix 

documentation describing the LCC Analysis.

TSD Ch.8 

(Figures)

The TSD Ch.8 (Figures) worksheet contains the Figures generated for use in the 

documentation describing the LCC Analysis results in Ch.8.

Figure 8-A.3.1 depicts how these various inputs are used in order to generate the LCC and PBP 

outputs.
8-A-4

Figure 8-A.3.1 LCC and Payback Calculation Process 

8-A.3.2 LCC input worksheet

The LCC input spreadsheet contains the following worksheets:

• Markup Input

• Learning Rate

• Labor Cost Dat

• RECS Sample

• CBECS Sample

• Sample Stats

• RECS Codebook

• Res Energy Price Input

• Res Energy Price Data

• Res Energy Price Trends

• Com Energy Price Input

• Com Energy Price Data

• Com Energy Price Trends

• Equip Spec

• AFUE (Existing)
8-A-5

• Base Case Eff

• Equip Spec Charts

• Models Directory

• Energy Price Trends

• Energy Use Adj Input

• Energy Use Adj Factors

• LCC Outputs

• Census Pop. Data

• Shipments

• Weather Data

• Definitions

• Discount Rate

• Lifetime Calcs

• Lifetime (Ref)

• Warranty (Ref)

Each of those worksheets is designed to calculate the input that will be used in the 

corresponding worksheet in the main LCC. 

8-A.4 BASIC INSTRUCTIONS FOR OPERATING THE LIFE-CYCLE 

COST SPREADSHEETS 

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 Macro” 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 OPTIONS on the Summary

worksheet. There are three drop-down boxes and one command button. 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 “2020.” 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 (1,000, 2,000, 3,000, 5,000, or 10,000).
8-A-6

d. Learning Curve: Defaults to “No Learning.” To change the value, use the dropdown menu.

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.
8-A-7

REFERENCES

1. U.S. Department of Energy: Energy Information Administration, Residential Energy 

Consumption Survey: 2009 RECS Survey Data, 2013. (Last accessed March, 2013.) 

<http://www.eia.gov/consumption/residential/data/2009/>

2. U.S. Department of Energy: Energy Information Administration, Commercial Buildings 

Energy Consumption Survey, 2003. (Last accessed September, 2013.) 

<http://www.eia.doe.gov/emeu/cbecs/>

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

2040, 2013. Washington, DC. <http://www.eia.gov/forecasts/aeo/pdf/0383(2013).pdf>