10-A-i

APPENDIX 10-A. USER INSTRUCTIONS FOR SHIPMENTS AND NIA

SPREADSHEET MODEL

TABLE OF CONTENTS

10-A.1 USER INSTRUCTIONS .....................................................................................10-A-1

10-A.1.1 Worksheet Descriptions.......................................................................................10-A-1

10-A.1.1.1 Introduction..................................................................................10-A-1

10-A.1.1.2 National Impact Summary ...........................................................10-A-1

10-A.1.1.3 Summary Results.........................................................................10-A-1

10-A.1.1.4 Model Flowchart..........................................................................10-A-2

10-A.1.1.5 Candidate Standard Level Definitions and Design-Lines-toProduct-Classes Mapping ............................................................10-A-2

10-A.1.1.6 Ship-NES-NPV Output................................................................10-A-2

10-A.1.1.7 Energy Impacts from Candidate Standard Levels—Charts.........10-A-2

10-A.1.1.8 Annual Impacts............................................................................10-A-2

10-A.1.1.9 Shipments Forecast across Candidate Standard Levels...............10-A-2

10-A.1.1.10 Shipments Chart...........................................................................10-A-2

10-A.1.1.11 Shipments Data ............................................................................10-A-2

10-A.1.1.12 Stock Sheets.................................................................................10-A-3

10-A.1.1.13 Lifetime........................................................................................10-A-3

10-A.1.1.14 Life-Cycle Cost Data by Equipment Class..................................10-A-3

10-A.1.1.15 Life-Cycle Cost Input Data..........................................................10-A-3

10-A.1.1.16 Market Share Data .......................................................................10-A-3

10-A.1.1.17 Site2Source ..................................................................................10-A-3

10-A.1.1.18 Energy Information Administration Electricity Sales Data .........10-A-3

10-A.1.1.19 Annual Energy Price Forecast .....................................................10-A-3

10-A.1.1.20 Rescaled Life-Cycle Cost Inputs .................................................10-A-4

10-A.1.2 National Impact Analysis Spreadsheet Operating Instructions ...........................10-A-4
10-A-1

APPENDIX 10-A. USER INSTRUCTIONS FOR SHIPMENTS AND NIA 

SPREADSHEET MODEL

10-A.1 USER INSTRUCTIONS

The results obtained in the shipments analysis and the national impact analysis (NIA) can 

be examined and reproduced using the Microsoft Excel spreadsheet available on the U.S. 

Department of Energy (DOE)’s website at: 

http://www.eere.energy.gov/buildings/appliance_standards/commercial/dist_transformers.html. 

The spreadsheet is called “NIA_DT_NOPR.xls,” and it enables the user to perform a NIA

of distribution transformer standards for both the liquid-immersed and dry-type equipment 

classes. This spreadsheet also enables the user to analyze the policy options considered in the 

regulatory impact analysis. To run the spreadsheet, the user needs to have Microsoft Excel 2000 

or a later version. 

10-A.1.1 Worksheet Descriptions

The NIA spreadsheet performs calculations to forecast the changes in national energy 

savings (NES) and net present value (NPV) due to an energy efficiency standard. The energy 

use and associated costs for a given standard are determined first by calculating the shipments 

and then calculating the energy use and costs for a product class. The differences between the 

standards and base cases (absent a national standard) can then be compared and the NES and 

NPV determined. The NIA spreadsheet, or workbook, consists of the following 20 worksheets

that are described below.

10-A.1.1.1 Introduction

This worksheet provides an outline of the contents of the entire national impact 

spreadsheet and describes the calculations contained in individual worksheets. 

10-A.1.1.2 National Impact Summary

This worksheet contains user input selections and a summary table containing cumulative 

shipments, cumulative energy savings, and NPV for each candidate standard level (CSL). The 

worksheet also graphically summarizes the energy and economic savings resulting from 

standards.

10-A.1.1.3 Summary Results

This worksheet provides a summary of the shipments forecast, national source energy 

savings, and the NPV of the savings for candidate standard levels (CSLs) for both the liquidimmersed and dry-type distribution transformers.
10-A-2

10-A.1.1.4 Model Flowchart

This worksheet presents a flowchart of the shipments and the NES and NPV modules.

10-A.1.1.5 Candidate Standard Level Definitions and Design-Lines-to-ProductClasses Mapping

This worksheet defines efficiency levels for the CSLs by design lines and provides 

design-lines-to-product-classes mapping.

10-A.1.1.6 Ship-NES-NPV Output

This worksheet presents the consolidated outputs produced for each product class when 

the user clicks the button “All Classes Summary” on the “National Impact Summary” worksheet. 

Total savings and NPV for the forecast period are summarized in tables for each standard level. 

10-A.1.1.7 Energy Impacts from Candidate Standard Levels—Charts

This worksheet displays the savings charts for years 2015 through 2044 for all the CSLs 

when the user runs a specific scenario or a product class in the “National Impact Summary” 

sheet. 

10-A.1.1.8 Annual Impacts

This worksheet makes several calculations for the base case as well as for all the CSLs. 

Savings are calculated as the difference between the base case and the CSL energy consumption.

10-A.1.1.9 Shipments Forecast across Candidate Standard Levels

This worksheet compiles the shipments forecast from the stock sheets (see below) for the 

years 2010 through 2038 for all the CSLs. The output of this worksheet corresponds to the 

product class for which the model is run.

10-A.1.1.10 Shipments Chart

This sheet contains the shipments model chart illustrating the forecast and backcast from 

the shipments model. 

10-A.1.1.11 Shipments Data

This worksheet calculates total historical shipments for the years 1977 through 2009 and 

forecasts shipments for the years 2010 through 2044. These shipments are calculated for both 

liquid-immersed and dry-type transformers.
10-A-3

10-A.1.1.12 Stock Sheets

There are two stock sheets—one for the base case and another for the standards case. 

Each stock sheet calculates the shipments, retirements, and affected stock in a particular year for 

liquid-immersed and dry-type transformers.

10-A.1.1.13 Lifetime

This worksheet contains the transformer reliability function and produces the retirement 

rates. The probabilities of retirement generated in this worksheet are used to calculate the annual 

retirements in the stock sheets.

10-A.1.1.14 Life-Cycle Cost Data by Equipment Class

This worksheet contains energy loss (or consumption) and first-cost data for different 

equipment classes. As the name suggests, the data for this worksheet come directly from the 

life-cycle cost (LCC) analysis. The load growth calculations for liquid-immersed and dry-type 

transformers are also carried out in this worksheet.

10-A.1.1.15 Life-Cycle Cost Input Data

Data in this worksheet are the mean values for the different variables from the Monte 

Carlo simulation runs of the LCC for individual design lines.

10-A.1.1.16 Market Share Data

This worksheet contains the market shares of the different transformer design lines. The 

worksheet also calculates the 0.75 scaling factors for each design line.

10-A.1.1.17 Site2Source

This worksheet contains the conversion factors for calculating source energy from site 

energy. 

10-A.1.1.18 Energy Information Administration Electricity Sales Data

This worksheet contains the historical and forecasted retail electricity sales data (obtained 

from DOE's Energy Information Administration (EIA)). 

10-A.1.1.19 Annual Energy Price Forecast

This worksheet contains the EIA’s Annual Energy Outlook (AEO) 2010 forecast data for 

the electric power sector for the different economic growth scenarios (i.e., reference, high, and 

low). This forecast is used to estimate future growth in shipments.
10-A-4

10-A.1.1.20 Rescaled Life-Cycle Cost Inputs

This worksheet applies the 0.75 scaling factors to convert the LCC input data for 

representative units into LCC input values representing all sizes within a design line. 

10-A.1.2 National Impact Analysis Spreadsheet Operating Instructions

Basic instructions for operating the NIA spreadsheet are as follows:

1. After downloading the NIA spreadsheet file from DOE's website, open the file using 

Excel. At the bottom, click on the tab for the worksheet “National Impact Summary.” 

This worksheet serves as the user interface for running the model for a particular product 

class. To provide flexibility, the spreadsheet permits some user modifications to the 

model. The user may select a particular macroeconomic forecast which determines fuel 

prices, electricity sales, and income data to be used by the model. The user may also 

directly input new values for implicit discount rates, which quantify consumer preference 

for immediate, instead of delayed, savings. Additionally, the user can select long-term 

purchase elasticities for transformers.

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. The user can change the model parameters listed in the gray box labeled “Inputs.” The 

parameters are:

a) Economic Growth: To change the value, use the drop-down menu. Select the 

desired growth level (Reference, Low, or High).

b) Discount Rate: To change the value, use the drop-down menu. Select the desired 

discount rate.

c) Elasticity Liquid-Immersed Type: To change the value, use the drop-down menu to 

pick a level (Medium, High, Low).

d) Elasticity Dry-Type: To change the value, use the drop-down menu to pick a level 

(Medium, High, Low).

4. The user can now select the desired equipment class from the drop-down menu and view 

the results in the summary table for that equipment class. To produce results for all 

equipment classes, the user will need to click the “All Classes Summary” button. When 

the user gets a message saying “Done,” the calculations are complete. Results from this 

run will be available in the “Ship-NES-NPV Outputs” worksheet.