10A-i 

APPENDIX 10A. USER INSTRUCTIONS FOR NIA SPREADSHEET

TABLE OF CONTENTS

10A.1 INTRODUCTION ...................................................................................................... 10A-1

10A.2 USER INSTRUCTIONS FOR NIA SPREADSHEET ............................................... 10A-1

REFERENCES ....................................................................................................................... 10A-5

LIST OF TABLES

Table 10A.2.1 Description of Tabs in the NIA Spreadsheet .................................................. 10A-1
10A-1 

APPENDIX 10A. USER INSTRUCTIONS FOR NIA SPREADSHEET

10A.1 INTRODUCTION

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

Excel spreadsheet available on the U.S. Department of Energy (DOE) Building Technologies 

website at www.eere.energy.gov/buildings/appliance_standards/commercial_products.html. 

The spreadsheet enables the user to perform the DOE’s national impact analysis (NIA) 

for 21 equipment classes of automatic commercial ice makers. The spreadsheet was designed and 

tested using Excel 2007. 

10A.2 USER INSTRUCTIONS FOR NIA SPREADSHEET

The NIA spreadsheet performs calculations to forecast the change in national energy use 

due to energy efficiency standards for automatic commercial ice-making (ACIM) equipment, and 

the net present value of the change in energy usage.a The energy usage and associated costs for a 

given standard are determined first by calculating the shipments, and then calculating the energy

and resulting costs for automatic commercial ice-making equipment shipped under that standard. 

The differences between the standards and base cases can then be compared and the overall 

energy savings and present values determined. 

The NIA spreadsheet or workbook contains the worksheets listed on Table 10A.2.1 . 

Some of the worksheets are hidden to make it easier for users to find and examine final results 

quickly. Hidden worksheets can be retrieved in Excel 2007 by right clicking on any worksheet 

tab, selecting the “Unhide” option, and the choosing a specific worksheet to unhide from the list 

of hidden worksheets. 

Table 10A.2.1 Description of Tabs in the NIA Spreadsheet

Worksheet Description

Labels Used as a go-between for user inputs and the rest of the worksheets. Modifying this 

worksheet will change and may impair the overall operation of the model, so DOE 

recommends caution when modifying this worksheet.

Shipments 

Summary

Contains the input selections and a summary table of shipments and stock across the entire 

analysis period for the selected equipment class.

Shares Contains market shares of units by equipment class, efficiency level, and building type.

Stock Contains the accounting for equipment stocks for the complete analysis period.

Econ Trends Contains electricity and water price forecasts and experiential (price) learning data inputs.

LCC Input Contains data derived in the life-cycle cost (LCC) analysis spreadsheet. Some data is 

replicated in the NIA model to allow variations defined explicitly in the NIA.

NIA Compiled Data Contains data from several other worksheets that have been organized in a way that allows 

for discounting and output table creation.

Installed Costs Contains data summarizing the full range of aggregate costs associated with installation. 

Uses per-unit installation costs from the LCC Inputs worksheet along with shipment data.

Annual Operating 

Costs

Contains data summed from the Annual Repair & Maint, Annual Water Cost, and Annual 

Elec Cost worksheets.

 

a Note that the NIA model can also forecast impacts arising from changing water usage. In the DOE’s preliminary 

analysis, no technologies were included that would result in changes in water usage. For this reason, the discussion 

in this appendix focuses on energy impacts. The discussion would apply equally to water usage impacts.
10A-2 

Table 10A.2.1 (cont) 

Annual Repair & 

Maint

Contains maintenance and repair cost data summed from the maintenance costs worksheet

and either the 3% or 7% repair cost worksheets, depending on the user-defined discount 

rate.

Repair Costs 3% Contains repair cost data using stock data and data imported from the LCC on a per-unit 

basis and annualized at 3%.

Repair Costs 7% Contains repair cost data using stock data and data imported from the LCC on a per-unit 

basis and annualized at 7%.

Maintenance Costs Contains maintenance cost data using stock data and data imported from the LCC on a perunit basis.

Annual Water Cost Contains aggregated water cost data using inputs from the LCC Inputs worksheet and stock 

data.

Annual Elec Cost Contains aggregated electricity cost data using inputs from the LCC Inputs worksheet and 

stock data.

Electricity Use Contains aggregated water usage data using inputs from the LCC Input worksheet and 

stock data.

Water Use Contains aggregated electricity usage data using inputs from the LCC Input worksheet and 

stock data.

Full Shipments 

Output

Contains the full set of shipments data for all years, efficiency levels, and equipment 

classes.

Full Affected Stock 

Output

Contains the full set of stock data for all years, efficiency levels, and equipment classes.

Historical 

Shipments

Contains past shipments data by year and equipment class. 

Lifetime 

Distribution

Contains a Weibull lifetime distribution used in determining probabilities of survival and 

breakdown by year.

Total New Floor 

Space

Contains the summation of data from the seven building floor space worksheets.

Healthcare Contains historic and projected floor space data from the Annual Energy Outlook 20111

(AEO2011) to account for growth rates in healthcare buildings.

Lodging Contains historic and projected floor space data from the AEO2011 to account for growth 

rates in lodging buildings.

Food Service Contains historic and projected floor space data from the AEO2011 to account for growth 

rates in foodservice buildings.

Retail Contains historic and projected floor space data from the AEO2011 to account for growth 

rates in retail buildings.

Education Contains historic and projected floor space data from the AEO2011 to account for growth 

rates in education buildings.

Food Sales Contains historic and projected floor space data from the AEO2011 to account for growth 

rates in food sales buildings.

Office Contains historic and projected floor space data from the AEO2011 to account for growth 

rates in office buildings.

Shipment-Weighted 

Data

Contains output tables to present in the technical support document.

Appendix Charts Contains output tables to present in appendix 10B of the technical support document.

Basic instructions for using the NIA spreadsheet are as follows:

1. Once the NIA spreadsheet file has been downloaded from the DOE Building 

Technologies website, open the file using Excel. If you receive a dialog box that asks 

whether you want to enable macros, select “Yes.” At the bottom of the Excel window, 

click on the worksheet for the worksheet Shipments Summary. 
10A-3 

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

monitor. 

3. You can change the model parameters in the Shipments Parameters box. The parameters

are:

a. Discount Year: Selects a year to which all monetary values are discounted. 

b. Equipment Class: Selects the desired equipment class. To change the value, select the 

menu box. A drop-down list appears. 

c. Efficiency Level: Selects the efficiency level used in the non-discounted annual net 

impacts figure. Must be less than or equal to the maximum achievable technology

(Max Tech) level. If a higher standard level than Max Tech is selected, no results will 

be available for the levels above Max Tech. 

d. Market Scenario: Not an option for the preliminary analysis phase. The default is 

“Rollup.” 

e. Electricity Price Scenario: Selects the desired Growth level (Constant, Reference, 

Low, or High). To the change value, select the Electricity Price Scenario box. A dropdown menu pops up. The scenarios refer to the Energy Information Administration’s 

projected U.S. national average rates from AEO2011. 

f. Water Price Scenario: Selects the desired growth level (reference, low, or high). To 

the change value, select the Electricity Price Scenario box. A drop-down menu pops 

up. 

g. Discount Rate: Selects the desired discount rate. To the change value, select the 

Discount Rate box. A drop-down menu pops up. Three options are provided: 3, 7, and 

0 percent.

h. Price Learning Scenario: Selects the desired growth level (no real price change, 

NEMS Price Deflator, low, reference, or high). To the change value, select the Price 

Learning Scenario box. A drop-down menu pops up. 

4. Once the user parameters have been set, a macro must be run to compile all of the stock 

and shipments data for each equipment class, efficiency level, and year. To run the 

macro, click the “Build Shipment/Stock Tables” button. Please note that output values are 

not correctly updated until the Build Shipment/Stock Tables macro has been run. All 

compiled shipments data will be output to tables in the Full Shipments Output worksheet. 

All compiled stock data will be output to tables in the Full Affected Stock Output 

worksheet. 

5. If a new set of LCC results is to be loaded, they will load into the LCC Input worksheet. 

To do this, open the LCC workbook and execute as desired (see appendix 8A). Once 
10A-4 

results are compiled in the NIA Output worksheet of the LCC workbook, click the Copy 

All button at the top of the page. This will copy the exact range that is compatible with 

the NIA model. Once the range has been copied, switch back to the NIA workbook, in 

the LCC Inputs worksheet, and click the Paste LCC Input button to paste in the correct 

placement. The formulas automatically update throughout the model and present results 

by equipment class and year in the NIA Compiled Data worksheet. 

 

6. Results are compiled further, into tables, in the Shipments-Weighted Data worksheet. 

Although many of the tables are formula based, and will automatically update with a 

change in the user-defined parameters, some are reliant on a macro to update table values. 

This macro can be triggered by clicking the Build Tables button located at the top right 

portion of the Shipment-Weighted Data worksheet. 
10A-5 

REFERENCES

 

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

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