8A-i

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

PAYBACK PERIOD SPREADSHEETS

TABLE OF CONTENTS

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

8A.1.1 Liquid-Immersed Transformers Worksheet Overview ........................................... 8A-1

8A.1.1.1 Options .................................................................................................. 8A-1

8A.1.1.2 Description ............................................................................................ 8A-1

8A.1.1.3 Summary ............................................................................................... 8A-2

8A.1.1.4 Summary Results .................................................................................. 8A-2

8A.1.1.5 Capacity Costs ...................................................................................... 8A-2

8A.1.1.6 A & B Dist. ........................................................................................... 8A-2

8A.1.1.7 Design Table ......................................................................................... 8A-2

8A.1.1.8 System Loads ........................................................................................ 8A-3

8A.1.1.9 Price Load MoEL .................................................................................. 8A-3

8A.1.1.10 Joint PDF .............................................................................................. 8A-3

8A.1.1.11 Utilities .................................................................................................. 8A-3

8A.1.1.12 LCC & Payback Calc. ........................................................................... 8A-3

8A.1.1.13 Annual Energy Price Forecast .............................................................. 8A-3

8A.1.1.14 Discount Rate ........................................................................................ 8A-3

8A.1.1.15 Lifetime ................................................................................................. 8A-3

8A.1.1.16 Forecast Cells ........................................................................................ 8A-4

8A.1.2 Dry-Type Transformers Worksheet Overview ....................................................... 8A-4

8A.1.2.1 Options .................................................................................................. 8A-4

8A.1.2.2 Description ............................................................................................ 8A-4

8A.1.2.3 Summary ............................................................................................... 8A-4

8A.1.2.4 Summary Results .................................................................................. 8A-5

8A.1.2.5 Design Table ......................................................................................... 8A-5

8A.1.2.6 Demand & Usage .................................................................................. 8A-5

8A.1.2.7 LCC & Payback Calc. ........................................................................... 8A-5

8A.1.2.8 Annual Energy Price Forecast .............................................................. 8A-5

8A.1.2.9 Discount Rate ........................................................................................ 8A-5

8A.1.2.10 Lifetime ................................................................................................. 8A-5

8A.1.2.11 Forecast Cells ........................................................................................ 8A-5

8A.2 Basic Instructions for Operating the Life-Cycle Cost Spreadsheets ....................... 8A-6
8A-1

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

PAYBACK PERIOD SPREADSHEETS

To execute the LCC spreadsheet, it is necessary for the user to have the appropriate 

hardware and software tools. DOE assumed the user has a reasonably current computer 

operating under the Windows operating system. The development team uses relatively new 

systems and has not defined the minimum system requirements. At a minimum, users need 

Microsoft Excel to execute the spreadsheet. For full functionality in running Monte Carlo 

simulations, users will need a copy of a spreadsheet add-in called Crystal Ball, in addition to 

Excel. Without Crystal Ball, one can still use the LCC spreadsheet moEL, but will not be able to 

examine inputs and outputs as distributions. Approximate results are provided through a sample 

calculation that uses average values for the inputs and outputs, as displayed in the “Summary” 

worksheet. 

8A.1 STARTUP

The LCC spreadsheet is a stored Excel file. It can be found on the DOE website at 

http://www.eere.energy.gov/buildings/appliance_standards/commercial/distribution_transformer

s.html. Open the file. (Each computer system will have a unique setup for loading a file. Users 

should refer to their software manuals if they have problems loading the spreadsheet file.) For 

users new to Excel and/or Crystal Ball, section 8.8.2 contains basic instructions for operating the 

LCC spreadsheets.

8A.1.1 Liquid-Immersed Transformers Worksheet Overview

Each of the LCC spreadsheets for the five liquid-immersed transformer design lines

(DLs) contains the following worksheets: 

8A.1.1.1 Options

 This worksheet contains the variables used to create the spreadsheet options on the 

Summary worksheet.

8A.1.1.2 Description

The Life-Cycle Cost (LCC) spreadsheet is used to estimate consumer economic impacts 

of efficiency standards. It does this by calculating the present value impacts of two regulatory 

scenarios. The first scenario is a regulatory base case in which no standard is imposed, and the 

second is a standard case where future purchases of an appliance must conform to a minimum 

energy efficiency performance standard. 
8A-2

8A.1.1.3 Summary

 This worksheet provides a summary of the LCC and PBP results for each draft efficiency 

level (EL). The spreadsheet/user interface is centered in the “Summary” worksheet. This 

worksheet contains a number of user-selectable options. These options, each with its own pulldown menu, are: 

• Transformer Load Growth/Year

• Transformer Loading

• Electricity Prices

• Transformer Customer As & Bs

• Future Energy Price Trend

• Equipment Price Scenario

• Efficiency Standard Effective Date

 Changing user-selectable options will produce average results shown on the “Summary,” 

i.e., results using the mean inputs. Because of the very nature of Monte Carlo mathematics, the 

average results will not be identical to the mean of the distribution produced from Monte Carlo 

Crystal Ball simulations, but will provide quick feedback reflecting nominal results to 

spreadsheet users. 

8A.1.1.4 Summary Results

 This worksheet contains the summary results tables and chart from the most recent 

simulation.

8A.1.1.5 Capacity Costs 

 This worksheet contains utility capacity cost calculations for both regulated markets and 

markets with fully functioning capacity markets.

8A.1.1.6 A & B Dist.

 This worksheet contains utility A and B models and data derived from utility transformer 

bids. 

8A.1.1.7 Design Table 

 This worksheet contains the database of transformer design options. These are the results 

from the engineering analysis (Chapter 5). For each transformer design option these include: 

manufacture selling prices, and load and no-load loss coefficients. This worksheet also contains 

transformer markup and cost parameters and, for the appropriate design lines, pole cost 

parameters.
8A-3

8A.1.1.8 System Loads 

 This worksheet contains the regional hourly electrical system load calculations as an 

input to the Price Load MoEL worksheet. This calculation is described in greater detail in 

Chapter 7. 

8A.1.1.9 Price Load MoEL

 This worksheet contains the regional hourly electrical system price calculations as an 

input to the Joint PDF worksheet. This calculation is described in greater detail in Chapter 7. 

8A.1.1.10 Joint PDF 

 This worksheet calculates the load and system price of that load for the selected 

transformer and the probability of the transformer load being coincident with system peak. This 

calculation is described in greater detail in Chapter 7.

8A.1.1.11 Utilities

 This worksheet provides a listing of the utilities and the operating regions that were used 

to determine system prices.

8A.1.1.12 LCC & Payback Calc. 

 This worksheet is used to estimate consumer economic impacts of efficiency standards. It 

does this by calculating the present value impacts of two regulatory scenarios. The first scenario 

is a regulatory base case in which no standard is imposed, and the second is a standard case 

where future purchases of an appliance must conform to a minimum energy efficiency 

performance standard.

8A.1.1.13 Annual Energy Price Forecast

This worksheet contains regional electricity price trend data for the analysis period.

8A.1.1.14 Discount Rate

 This worksheet contains the discount rate analysis. It also contains the transformer 

market share data.

8A.1.1.15 Lifetime

This worksheet contains the transformer lifetime distribution.
8A-4

8A.1.1.16 Forecast Cells

 This worksheet contains the statistical results from the most recent simulation.

8A.1.2 Dry-Type Transformers Worksheet Overview

 Each of the LCC spreadsheets for the eight dry-type transformer design lines contains the 

following worksheets: 

8A.1.2.1 Options

 This worksheet contains the variables used to create the spreadsheet options on the 

Summary worksheet.

8A.1.2.2 Description

The Life-Cycle Cost (LCC) spreadsheet is used to estimate consumer economic impacts 

of efficiency standards. It does this by calculating the present value impacts of two regulatory 

scenarios. The first scenario is a regulatory base case in which no standard is imposed, and the 

second is a standard case where future purchases of an appliance must conform to a minimum 

energy efficiency performance standard. 

8A.1.2.3 Summary

This worksheet provides a summary of the LCC and PBP results for each draft efficiency 

level (EL). The spreadsheet/user interface is centered in the “Summary” worksheet. This 

worksheet contains a number of user-selectable options. These options, each with its own pulldown menu, are: 

• Transformer Load Growth/Year

• Transformer Loading

• Electricity Prices

• Transformer Customer As & Bs

• Future Energy Price Trend

• Equipment Price Scenario

• Efficiency Standard Effective Date

Changing user-selectable options will produce average results shown on the “Summary,” i.e., 

results using the mean inputs. Because of the very nature of Monte Carlo mathematics, the 

average results will not be identical to the mean of the distribution produced from Monte Carlo 

Crystal Ball simulations, but will provide quick feedback reflecting nominal results to 

spreadsheet users. 
8A-5

8A.1.2.4 Summary Results

 This worksheet contains the summary results tables and chart from the most recent 

simulation.

8A.1.2.5 Design Table 

 This worksheet contains the database of transformer design options. These are the results 

from the engineering analysis (Chapter 5). For each transformer design option these include: 

manufacture selling prices, and load and no-load loss coefficients. This worksheet also contains 

transformer markup and cost parameters and, for the appropriate design lines, pole cost 

parameters.

8A.1.2.6 Demand & Usage 

 This worksheet contains the sample of transformer customers that are used in the 

simulation. For each of those customers this worksheet contains: monthly electricity demand and 

usage rates, seasonal marginal energy and demand prices, and A and B parameters.

8A.1.2.7 LCC & Payback Calc. 

 This worksheet is used to estimate consumer economic impacts of efficiency standards. It 

does this by calculating the present value impacts of two regulatory scenarios. The first scenario 

is a regulatory base case in which no standard is imposed, and the second is a standard case 

where future purchases of an appliance must conform to a minimum energy efficiency 

performance standard.

8A.1.2.8 Annual Energy Price Forecast

This worksheet contains regional electricity price trend data for the analysis period.

8A.1.2.9 Discount Rate

 This worksheet contains the discount rate analysis. It also contains the transformer 

market share data.

8A.1.2.10 Lifetime

This worksheet contains the transformer lifetime distribution.

8A.1.2.11 Forecast Cells

This worksheet contains the statistical results from the most recent simulation.
8A-6

8A.2 Basic Instructions for Operating the Life-Cycle Cost Spreadsheets

1. Once you have downloaded the LCC file from the Web, open the file using Excel. At the 

bottom, click on the tab for sheet “Summary.” 

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

to make it fit your monitor.

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

graphical interface that comes with the spreadsheet. Select choices from the various userselectable options.

4. Click the “Run” button to run the simulation using DOE’s parameters. 

To produce custom sensitivity results using directly Crystal Ball, 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 from the menu bar. 

3. Select Run Preferences and choose either Monte Carlo or Latin Hypercube.a

4. To run the simulation, choose the following sequence (on the Crystal Ball toolbar): Run, 

Reset, Run

 Select 

number of Trials (DOE suggests 10,000). 

5. Now wait until the program informs you that the simulation is completed.

 DOE provides the following instructions to view the output generated by Crystal Ball: 

1. After the simulation has finished, click on the Windows tab bar labeled Crystal Ball to 

see the distribution charts. 

2. The LCC savings and paybacks 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 cumulative 

or frequency plots are to be shown. 

2a. To calculate the probability that a particular value of LCC savings will occur, either 

type 0 in the box by the left arrow, or move the arrow key with the cursor to 0 on 

 

a

Because of the nature of the program, there is some variation in results due to random sampling when MonteCarlo 

or Latin Hypercube sampling is used.
8A-7

the scale. The value in the Certainty box shows the likelihood that the LCC savings 

will occur. 

2b. To calculate the certainty of the payback period being below a certain number of 

years, insert that value in the far-right box. 

3. To generate a printed report, select Create Report from the Run menu. The toolbar 

choice of Forecast Windows allows you to select the charts and statistics in which you 

are interested. For further information on Crystal Ball outputs, refer to Understanding 

the Forecast Chart in the Crystal Ball manual.