10-A-i

APPENDIX 10-A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL 

IMPACT ANALYSIS SPREADSHEET MODELS

TABLE OF CONTENTS

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

10-A.1.1 Shipments Model Spreadsheet Description .........................................................10-A-1

10-A.1.2 National Impact Analysis Spreadsheets Description ...........................................10-A-1

10-A.1.2.1 Summary Spreadsheet Organization....................................................................10-A-2

10-A.1.2.2 Accountability Spreadsheets Organization..........................................................10-A-3

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

APPENDIX 10-A. USER INSTRUCTIONS FOR SHIPMENTS AND NATONAL IMPACT 

ANALYSIS 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’s (DOE)’s website at: 

http://www1.eere.energy.gov/buildings/appliance_standards/commercial/electric_motors.html. 

The shipments model is in the spreadsheet called “MEM_Prelim_Shipments_Model.xls,” 

and the NIA in the spreadsheets “MEM_Prelim_NIA_Summary.xlsm,” 

“MEM_Prelim_NIA_DesignAB.xlsx,” “MEM_Prelim_NIA_DesignC.xlsx” and 

“MEM_Prelim_NIA_FirePump.xlsx.” These spreadsheets implement the calculations described 

in Chapters 9 and 10. Further, the NIA spreadsheets enable the user to simulate national impacts 

under different parameters and scenarios. To run the spreadsheets the user needs to have 

Microsoft Excel 2007 or a later version. 

10-A.1.1 Shipments Model Spreadsheet Description

The shipments model spreadsheet performs calculations to forecast the shipments of 

motors covered by the rulemaking. The methodology for developing the shipments model is 

described in Chapter 9. The shipments model spreadsheet, or workbook, consists of the 

following worksheets:

(a) Shipments: Calculates and provides a summary of the shipment forecasts for the 

entire analysis period (2015-2044) and beyond.

(b) Invest. vs. Ship.: Presents how DOE developed a relationship between shipments and 

private fixed investment in selected equipment and structure.

(c) Invest. vs. Tol. Invest.: Calculates projections for private fixed investment in 

equipment and structure for selected sectors.

(d) Tot. Invest. vs. GDP: Calculates projections for total private fixed investment.

(e) Census: Presents the Census data used to develop the historical shipments index

10-A.1.2 National Impact Analysis Spreadsheets Description

The NIA spreadsheets perform calculations to forecast the changes in national energy 

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

set at a given candidate standard level (CSL), the energy consumption and the costs associated 

with each equipment class, as well as the corresponding NES and NPV results rely on the 
10-A-2

shipments estimated in the shipments spreadsheet and on calculation performed by three 

accountability spreadsheets, each dedicated to a specific equipment category. A fourth,

summary spreadsheet provides the accountability spreadsheets with general parameters and 

tables, and summarizes their results. Figure 10-A.1.1 presents the general organization and 

interactions between the spreadsheets comprising the NIA model. The following subsections 

describe, respectively, the worksheets comprising the summary and the accountability 

spreadsheets, and provide instructions to operate the NIA model.

Figure 10-A.1.1 National lmpact Analysis Spreadsheets Architecture

10-A.1.2.1 Summary Spreadsheet Organization

The summary spreadsheet consists of the following six worksheets which support the 

accountability spreadsheets and summarize their results. 

(a) Lifetime: Presents, for each equipment category, motor survival probabilities by 

sector and horsepower (HP) range.

(b) Efficiency Tables: Presents, for each equipment category, the efficiency levels by 

CSL and equipment class.

(c) General Tables & Parameters: Presents all tables and single-value parameters used by 

the accountability spreadsheets.

(d) Shipments: Presents total historical and forecast shipments, as well as shipment 

distributions across equipment class groups, and motor HP and configuration.

Design C

Accountability

NIA

Summary

Design A & B

Accountability

parameters

and tables

energy savings

energy cost savings

incremental eqpt costs

parameters

and tables

energy savings

energy cost savings

incremental eqpt costs

Fire Pumps

Accountability

parameters

and tables

energy savings

energy cost savings

incremental eqpt costs
10-A-3

(e) Summary: Enables the user to select CSLs, scenarios and sensitivity levels to be 

simulated by the accountability spreadsheets, and summarize their results. 

(f) Scenario Results: Automatically simulates pre-determined combinations of scenarios 

and sensitivity levels, and summarize results in a pivot-table. 

10-A.1.2.2 Accountability Spreadsheets Organization

The accountability spreadsheets consist of the following 11 worksheets which calculate 

the national energy savings, the national energy cost savings, and the national (non-energy) 

incremental equipment costs for all equipment classes of each equipment category.

(a) Shipments: Presents the base case shipments forecast by sector for all equipment 

classes, and estimates shipments for the standards case scenario (in this version, equal 

to the base case).

(b) Efficiency Distribution: Presents the base case energy efficiency distribution by 

motor HP, and calculates the corresponding distributions to the standards case 

according to the CSL selected in the Summary spreadsheet.

(c) Unit Energy Consumption: Calculates, for all equipment classes and efficiency levels,

the lifetime source energy consumption of a unit shipped in each year of the analysis 

period, according to the sector to which it is shipped and the application for which it 

is used.

(d) Natl Energy Consumption: Calculates, for all equipment classes, the base case and the 

standards case national lifetime energy consumption and losses from units shipped in 

each year of the analysis period. The calculation is disaggregated by sector and 

application.

(e) Natl Energy Savings: Calculates, for all equipment classes, the national energy 

savings by sector.

(f) Unit Energy Cost: Calculates, for all equipment classes and efficiency levels, the 

lifetime energy cost of a unit shipped in each year of the analysis period, according to 

the sector to which it is shipped and the application for which it is used.

(g) Natl Energy Cost: Calculates, for all equipment classes, the base case and the 

standards case national lifetime energy costs from units shipped in each year of the 

analysis period. The calculation is disaggregated by sector and application.

(h) Natl Energy Cost Savings: Calculates, for all equipment classes, the present-value of 

the national energy cost savings by sector.
10-A-4

(i) Unit Eqpt Costs: Calculates, for all equipment classes and efficiency levels, the 

lifetime non-energy equipment costs of a unit shipped in each year of the analysis 

period, according to the sector to which it is shipped.

(j) Natl Eqpt Costs: Calculates, for all equipment classes, the base case and the standards 

case national lifetime non-energy equipment costs from units shipped in each year of 

the analysis period. The calculation is disaggregated by sector and application.

(k) Natl Eqpt Incr Costs: Calculates, for all equipment classes, the present-value of the 

national (non-energy) incremental equipment costs by sector.

10-A.1.2.3 National Impact Analysis Spreadsheet Operating Instructions

Basic instructions for operating the NIA spreadsheet are as follows:

1. After downloading the NIA set of spreadsheet files from DOE's website, open the 

Summary file using Excel. Once loaded, this spreadsheet will ask if the user wants 

to open the additional files. If you intend only to see the existing results, the answer 

maybe “No.” However, if you plan to do your own simulations you must answer 

with “Yes,” in which case Excel will automatically open the three additional 

accountability spreadsheet files and activate back the Summary spreadsheet.

2. If you intend only to see the existing results, click on the tab for the worksheet 

“Scenario Results.” To select results for specific combinations of parameters and 

scenarios one can either use: (a) the filtering feature in the column headers, or (b) the 

pivot-table located at the right side of the results listing.a

3. If you intend to run your own simulations, there are two options: (a) running the 

model for a specific combination of parameters and scenarios, and (b) running the 

model for pre-determined combinations of parameters and scenarios. The two 

options can be operated as follows:

(a) For a specific combination of parameters and scenarios:

Click on the tab for the worksheet “Summary.” This worksheet serves as the user 

interface for running the model for a particular combination of parameters and 

scenarios. To provide flexibility, the spreadsheet permits some user modifications 

to the model. The user may select a particular:

 

a To learn more on how to use Excel pivot-tables refer to “PivotTable I: Get started with PivotTable reports in Excel 

2007” in <http://office.microsoft.com/en-us/excel-help/pivottable-i-get-started-with-pivottable-reports-in-excel2007-RZ010205886.aspx>.
10-A-5

• Discount rate, which enables the user to set a discount rate (in percentage) and 

affects the present-values of energy savings and incremental equipment (nonenergy) costs; 

• Economic growth which enables the user to select an annual economic 

outlook (AEO) macroeconomic forecast and determines the electricity prices 

to be used by the model;

• Product price trend, which enables the user to select a scenario of motor price 

trends and affects motor manufacturer selling prices (MSPs) over the analysis 

period;

• Energy Savings, which enables the user to select whether the energy savings 

are to be reported as source energy savings or as full-fuel-cycle energy 

savings;

• CSLs, which enables the user to select a CSL as the standard level for each 

equipment category, and affects the standards case efficiency distribution; and

• Sensitivity, which enables the user to change (with a direct multiplier) all 

motors MSP, repair cost and operating hours values, and affects energy 

consumption and costs, as well as equipment non-energy costs. 

Once the desired parameters are set, the user should start the spreadsheet 

calculation. This can be done either by pressing F9 or navigating through the 

Excel menu as follows: Formulas >> Calculate Now.

(b) For pre-determined combinations of parameters and scenarios:

Click on the tab for the worksheet “Scenario Results.” This worksheet can 

automatically calculate results for all equipment class groups, CSLs, and discount 

rates considering all Reference scenarios. It can further extend these calculations 

to selected alternative scenarios (including scenarios for sensitivity analysis). To 

enable the automatic calculation one must answer “Yes” to the “Recalculate all?”

question, or otherwise the worksheet will just show the results from the earlier run

(see item 2 above on how to examine results from a model run). After answering 

with a “Yes” to the “Recalculate all?” message, the following alternatives will be 

posted to the user: 

• “Only Reference scenarios?”

Yes: simulate only the Reference economic growth and the Constant product 

price trend scenarios

No: enables the selection of additional scenarios to be simulated (see the next 

item).

• “Select scenarios to simulate:”

“E=Economic growth,”

“F=Source/FFC savings,”

“P=Prod price trend,”

“*=All”
10-A-6

E: simulates the Low- and High AEO economic growth scenarios, in addition 

to the Reference one

F: calculates both source and full-fuel-cycle energy savings

P: simulates the Decreasing and Increasing product price trend scenarios, in 

addition to the Constant one

*: simulates all economic growth and product price trend scenarios.

• “Include sensitivity analysis?”

Yes: enables the user to setup the sensitivity level to be simulated (see the 

next item)

No: only the reference values for hours of operation, MSP and repair cost 

will be simulated.

• “Enter percentage:”

Enables the user to type the percentage corresponding to the desired 

sensitivity level to be simulated (for example, to simulate hours of operation, 

MSP and repair cost values 10 percent lower and higher than the former 

values just enter the number 10).

• “Run:”

“<...> scenarios,”

“<yes/no> sensitivity analysis.”

This message summarizes what it will be simulated. To start the simulation 

process, click Ok; otherwise, click Cancel. 

During the simulation process, messages in the left side of the lower message bar 

will report the process progress and an estimate of the remaining time. Once the 

simulation is over, the user can then examine the results (see item 2 above on how 

to examine results from a model run).