APPENDIX G. USER INSTRUCTIONS FOR NIA SPREADSHEET 

TABLE OF CONTENTS 

G.1 INTRODUCTION ............................................................................................................. G-1

G.2 USER INSTRUCTIONS FOR NIA SPREADSHEET...................................................... G-1

LIST OF TABLES 

Table G.2.1 Description of Tabs in NIA Spreadsheet ......................................................... G-1

G-i 

EERE-2006-STD-0125 COMMENT 46
G-ii 

EERE-2006-STD-0125 COMMENT 46
APPENDIX G. USER INSTRUCTIONS FOR NIA SPREADSHEET 

G.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 Building Technologies website: 

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

The spreadsheet file <BVM_NIA_NOPR_03-10-2009.xls> enables the user to perform 

national energy savings and national net present value analyses of two equipment classes and 

three different capacities of beverage vending machine (BVM) equipment for up to seven trial 

standard levels (TSLs). To execute the spreadsheet requires Microsoft Excel 2003 or a later 

version. 

G.2 USER INSTRUCTIONS FOR NIA SPREADSHEET 

The national impacts analysis (NIA) spreadsheet performs calculations to forecast the 

change in national energy use and net present value resulting from an energy efficiency standard 

for BVM equipment. 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 as described in 

Table G.2.1. 

Table G.2.1 Description of Tabs in NIA Spreadsheet 

Instructions Provides instructions for operating the spreadsheet 

Changes Shows changes to the spreadsheet since the advance notice of proposed 

rulemaking (ANOPR) 

Analysis Flow Summary diagram of analyses conducted in the spreadsheet 

Supplemental Table 

on Shipments 

Contains detailed forecasts of base case shipments of BVMs by equipment 

class, capacity and efficiency level for selected years. 

Details_ Ship Calculates total shipments by efficiency level for an equipment class by year, 

given a trial standard level. Also contains market weighted-average 

percentages of shipments by efficiency level of each equipment class of new 

BVM equipment. 

Product Shipped Contains a summary of the shipments by equipment class and capacity for 

selected years and cumulative from 2012–2042 

Shipments-Weighted 

Data (hidden) 

Contains calculated interim tables of both input data and results from the 

spreadsheet 

G-1 

EERE-2006-STD-0125 COMMENT 46
Table G.2.1 (continued) 

National Impacts 

Summary 

Summarizes impacts on national energy savings and present value and 

annualized value of equipment savings or costs, operating savings or costs, and 

the net difference at 3-percent and 7-percent discount rates for up to seven 

TSLs. 

Savings Summary A bookkeeping table used to store the results of the shipments and energy 

savings calculations for an individual equipment class and up to seven TSLs 

(e.g., levels 2–8). 

Details_Save An accounting worksheet used to tally the energy and cost savings year by year 

for an individual equipment class and TSL. 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 level energy use and cost. 

Details_Stock A worksheet that keeps track of all surviving stock of BVM equipment in a 

equipment class from the start year forward. Stock is updated for each year. 

Initial Stock Details A worksheet that keeps track of all surviving stock of BVM equipment in an 

equipment class from 2006 to the start year of the forecast. Stock is updated for 

each year. 

Initial Shipments 

Detail 

Calculates total shipments by efficiency level and market weighted-average 

percentages of shipments by efficiency level of each equipment class of new 

BVM equipment for baseline market conditions and up to seven TSLs. 

Shipments_Summary Contains estimates of shipments of an equipment class of BVM equipment by 

efficiency level and year, given a trial standard level. Also contains estimates 

of total shipments of each equipment class by year for each trial standard level. 

Base Product Market 

Share Calcd 

Worksheet performs calculations to estimate total market shipments by 

equipment class and year in the base case. 

Product Shares Contains equipment class percentages of overall BVM equipment shipments. 

Marginal Btu 

Conversion Rate 

Provides data on the conversion rate from site energy savings to primary 

energy savings. 

Economic Trends Contains historical and projected economic values as well as shipments data. 

Shipments 

Background 

Contains historical data and detailed forecast calculations to feed the market 

size estimates in Economic Trends sheet. 

Equipment 

Parameters 

Contains the economic, energy, and size parameters for BVM equipment and 

other equipment parameters used to calibrate the model. 

Labels This is used as an interface between user inputs and the rest of the 

worksheets—do not modify this sheet. 

G-2 

EERE-2006-STD-0125 COMMENT 46
Basic instructions for operating the NIA spreadsheet are as follows: 

1) Once the NIA spreadsheet file has been downloaded from the Web, open the file using 

Excel. A dialog box will appear as the file opens; be sure to Enable Macros (select “Yes” 

when this option is presented). At the bottom, click on the tab for the worksheet titled 

Summary of Results. 

2) Use Excel’s View/Zoom commands at the top menu bar to adjust the display size for 

personal preference. 

3) The user 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. Some of the potential equipment 

classes may have no shipments. 

b. Efficiency Level Used: Selects an efficiency level from the life-cycle cost (LCC) 

analysis to be 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. These levels 

are mapped to the current TSLs in a table beginning in column U of the Summary 

of Results worksheet. 

c. Max Tech: To change the value, select the Max Tech box. A drop-down menu pops 

up. Select the desired type of maximum technologically feasible efficiency level 

(level 8). The Max Tech should be selected for the equipment class that was 

analyzed in the LCC analysis. 

d. 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 (EIA) projected rates from Annual Energy Outlook (AEO) 2009.

1

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

should be set to 2012 for the notice of proposed rulemaking (NOPR). 

f. Standard Last Year: This is the first year in which the standard will have an effect 

(the period of 30 years from the Standard First Year, plus the length of time until 

the last unit purchased during 30-year period leaves the inventory. This should be 

set to 2057 for the NOPR. 

g. Site-Source Conversion Rate: Specifies which conversion rate is used to convert 

site energy savings to primary energy savings. This is the marginal rate for the 

NOPR 

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

menu pops up. Select the desired Discount Rate. 

i. Price Year: This specifies the year’s dollars in which to conduct the value analyses. 

This should be 2008$ for the NOPR. 

j. Market Share Assumptions: The user selects the shares that each equipment class 

represents in the market. The shares are constant for the entire forecast period since 

G-3 

EERE-2006-STD-0125 COMMENT 46
there is little data concerning how the market shares have changed or will change. 

The shares must add to 100 percent. 

4) Once the user parameters have been reset the model must be rerun. To rerun the model 

press the green “Update Values” button. Note: The output values are not correctly 

updated until the “Update Values” button is pressed. To run all cases and update the 

summary information in Sales-Weighted Data sheet, press the “Run All Cases” button. 

The model will take about 10 minutes to rerun all cases. 

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

case (level 1) and the efficiency level 2–8, mapped to the current trial standard levels. 

Tabular results are summarized as: 

 cumulative shipments, 

 percentage change in total shipments as efficiency level increases (currently, this does 

not change), 

 present value of national equipment cost savings in billions of dollars, 

 present value of national operating cost savings in billions of dollars, 

 national net present value in billions of dollars, and 

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

for all standards cases energy savings are tabulated for two periods: 2012–2042 and 

2012 to the last year selected. Net present value results are tabulated for 2012 to the 

last year. 

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 parameters, the user can also view the annual trend in the 

non-discounted net impacts for an individual standard level. To view an efficiency level select 

efficiency level, and then select a level from the drop-down list. 

G-4 

EERE-2006-STD-0125 COMMENT 46
G-5 

REFERENCES 

1. Energy Information Administration (EIA). Annual Energy Outlook 2009. EIA, Washington, 

D.C., DOE/EIA-0554, Early Release. 

EERE-2006-STD-0125 COMMENT 46