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

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 

http://www.eere.energy.gov/buildings/appliance_standards/commercial_products.html. 

The spreadsheet enables the user to perform national impact analysis (NIA) of up to 24 

classes of commercial refrigeration equipment. 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 an energy efficiency standard for commercial refrigeration equipment, and net present 

value of the change in energy usage. 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 

commercial refrigeration 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 consists of the following worksheets (Table 10A.2.1). 

Some detailed worksheets are hidden for ease in examining final results quickly, but can be 

retrieved by using the command option on the Excel 2007 Home tab: Format, Visibility – Hide 

& Unhide, Unhide Sheet. 

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

Tab Description

Analysis Flow Figure that illustrates the calculations performed by the NIA 

spreadsheet.

Summary of Results Worksheet that contains user input selections and, for the selected 

equipment class, a Summary table, Shipments graph, Cumulative 

Energy Savings and NPV graph, and an Annual Non-Discounted 

Savings Trend plot, which also contains a plot of discounted net 

savings by year.

Shipments-Weighted Data Worksheet that contains calculated tables of both input data and 

results from the spreadsheet. For the preliminary analysis, many 

summary tables shown in the TSD are taken from this worksheet, 

including the Cumulative NPV and Energy Savings results.

Savings Summary Bookkeeping worksheet used to store the results of the shipments 

and energy savings calculations for an individual equipment class 

and up to seven efficiency levels (e.g., Levels 2–8).

Details_Save (Hidden) Accounting worksheet used to tally the energy and cost savings 

year by year for an individual equipment class and standard level. 

The energy and cost savings in a single year are the difference 

between the base case energy use and costs for that year and the 

standard case energy use and costs in the same year.
10A-2 

Table 10A.2.1 Cont. 

Details_Stock (Hidden) Worksheet that keeps track of all surviving stock of commercial 

refrigeration equipment in an equipment class from the start year 

forward. Stock is updated for each year.

Initial Stock Details (Hidden) Worksheet that keeps track of all surviving stock of commercial 

refrigeration equipment in an equipment class from 2010 to the 

start year of the forecast (2016). Stock is updated for each year.

Shares Output 1, Shares Output 2, 

Shares Output 3, Shares Output 4,

Shares Output 5, Shares Output 6, 

Shares Output 7 (All are Hidden)

Seven worksheets that estimate percentage distribution shipments 

for all equipment classes of commercial refrigeration equipment, 

by efficiency level and year, from the start year of the forecast 

forward. Each worksheet applies to a different type of business 

(supermarkets, convenience and specialty stores, convenience 

stores with gas pumps, large multi-line retailers, limited service 

restaurants, full service restaurants, and other foodservice).

Shares Processing (Hidden) Worksheet that performs the detailed calculations based on 

annualized cost to estimate market shares of an equipment class, 

by efficiency level, in a business type, based on risk preferences of 

purchasers for a baseline market condition and up to seven 

candidate standard levels.

Input (Hidden) Worksheet that performs detailed calculations by risk preference 

class to estimate annualized cost for a newly purchased unit of an 

equipment class of commercial refrigeration equipment by 

efficiency level in a business type. 

CRE Cost List Contains input data on installed cost (total price), repair and 

maintenance cost, and annual electricity cost for units of 

commercial refrigeration equipment, calculated by the life-cycle 

cost (LCC) model by equipment class, business type, and 

efficiency level at U.S. average prices and market conditions. 

Details_ Ship (Hidden) Calculates total shipments by efficiency level for an equipment 

class by year, given a candidate standard level. Also contains 

market weighted-average percentages of shipments by efficiency 

level of each equipment class of new commercial refrigeration 

equipment for baseline market conditions and up to seven 

candidate standard levels. Percentages are reported for each year 

from the start year forward. 

Initial Shipments Detail (Hidden) Calculates total shipments by efficiency level and market 

weighted-average percentages of shipments by efficiency level of 

each equipment class of new commercial refrigeration equipment 

for baseline market conditions and up to seven candidate standard 

levels. Values are reported for each year from 2010 to the start 

year of the forecast.

Shipments_Summary Contains estimates of shipments of an equipment class of 

commercial refrigeration equipment by efficiency level and year.

Also contains estimates of total shipments of each equipment class 

by year for each candidate standard level.

Base Product Market Share Calcd Worksheet that performs calculations to estimate total market 

shipments by equipment class and year in the base case. 

Product Shares Contains percentages of overall commercial refrigeration 

equipment shipments (linear feet) by equipment class based on 

2009 historical data. Populated from Summary of Results. 

Econ Trends Worksheet that contains historical and projected economic values 

as well as shipments data. 
10A-3 

Table 10A.2.1 Cont. 

Equipment Parameters Worksheet that contains the economic, energy, and size 

parameters for commercial refrigeration equipment and other 

equipment parameters used to project survival and calibrate the 

model.

Labels Used as an interface between user inputs and the rest of the 

worksheets—do not modify this sheet

Shipments (Hidden) Worksheet that contains a single tabular summary of shipments for 

use in the TSD chapter 6.

 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 tab for the worksheet Summary of Results. 

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 listed in the gray box labeled “User defined 

inputs.” The parameters are:

a. Equipment Class: To change the value, select the menu box. A drop-down list

appears. Select the desired equipment class. Many of the potential classes have no 

shipments (i.e., they are not “real.”) If you select one these classes, a warning will 

appear in the space immediately above the menu box and the first row in the 

summary of results will show up as zeroes; the cells will turn red and will be crosshatched out. 

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

impacts figure. Must be less than or equal to the Max Tech level. If a higher standard 

level than Max Tech is selected, no results will be available for the levels above Max 

Tech. 

c. Max Tech: This value is currently set by the model, not by the user, and is provided 

on the Summary of Results worksheet for the information of the model user. There is 

a routine within the model that requires the Max Tech be set at a level no greater than 

the currently modeled limit. Thus, Max Tech is presently not treated as a variable.

d. Rate of Growth of the CRE Market: Not an option for the preliminary analysis phase.

e. Growth in Energy Prices: To the change value, select the Growth in Energy Prices

box. A drop-down menu pops up. Select the desired Growth level (Constant, 

Reference, Low, or High). The scenarios refer to the Energy Information 

Administration’s projected U.S. national average rates from Annual Energy Outlook 

2010. 
10A-4 

f. Standard First Year: This is the first year in which the standard will take effect. This 

should be set to 2016 for the preliminary analysis, but other options are available. 

g. Discount Rate: To the change value, select the Discount Rate box. A drop-down 

menu pops up. Select the desired Discount Rate. Three options are provided: 3, 7 and 

9 percent.

4. Once the user parameters have been reset, the model must be re-run. To re-run the model 

for single equipment option, click the Update Values button. Note: The output values are 

not correctly updated until the Update Values button is clicked. To run all equipment 

classes, click the Run All Cases button. The Sales-Weighted Data worksheet is only 

updated when the Run All Cases button is used. This takes a few minutes. The SalesWeighted Data worksheet is not updated by Update Values or by simply changing any of 

the input parameters. 

5. If a new set of LCC results has been loaded into the CRE Cost List and Equipment 

Parameters worksheets, it will be necessary to recalculate the baseline equipment 

shipment shares by efficiency level in sheets Output1–Output7. To do this, click the 

Shares Processing button. The shares automatically update (this process takes about 3 

minutes), but it will not be necessary to update again unless LCC input data change. 

 

6. Tabular results are presented to the right of the “User defined inputs” box for the base 

case (Level 1) and the Level 2 through Level 8 standards cases. Tabular results are 

summarized as: (1) cumulative shipments; (2) percentage change in total shipments as 

efficiency level increases (currently, this does not change); (3) present value of national 

equipment cost savings in billions of dollars; (4) present value of national operating cost 

savings in billions of dollars; (5) national net present value in billions of dollars; and (6) 

cumulative national energy savings in quadrillion British thermal units (quads). Results 

for all standards cases energy savings are tabulated for two periods: 2016 to 2045, and 

2016 to 2060. Net present value results are tabulated for 2016–2060, with equipment 

costs included for the period 2016–2045. 

Graphical results are presented for the main tabular results. Two charts are provided for 

the given model parameters: (1) shipments forecasts for the base case and all standards 

cases and (2) national energy savings and net present values for all the standards cases.

For a given set of user-defined equipment choices and parameters, you can view the 

annual trend in the non-discounted net impacts for individual candidate standard levels. 

To view an efficiency level, select the drop-down menu bar with that title, and select the 

desire candidate standard level from the drop-down list. The annual purchase savings and 

operating cost savings are not discounted values. However, the net savings value in each 

year is shown as a discounted value.