APPENDIX G. USER INSTRUCTIONS FOR THE NATIONAL IMPACT ANALYSIS 

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 

BVM_FINAL_FR_TSD_AppendixG_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
 

 

 

APPENDIX G. USER INSTRUCTIONS FOR THE NATIONAL IMPACT ANALYSIS 

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 (DOE) website at 

http://www1.eere.energy.gov/buildings/appliance_standards/commercial/beverage_machines_no 

pr.html. The national impact analysis (NIA) spreadsheet 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 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 NIA spreadsheet performs calculations to forecast the change in national energy use 

and net present value resulting from an energy efficiency standard for beverage vending machine 

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 

Worksheet Tab Title Description 

Instructions Provides instructions for operating the spreadsheet 

Changes Shows changes to the spreadsheet since the notice of proposed rulemaking (NOPR) 

Analysis Flow Summary diagram of analyses conducted in the spreadsheet 

Summary of Results Operates the spreadsheet and provides certain summary information. 

Supplemental Table on 

Shipments 

Contains detailed forecasts of base case shipments of beverage vending machines 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 beverage vending machine 

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 

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 (corresponding to 

efficiency 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 

G-1 

BVM_FINAL_FR_TSD_AppendixG_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
 

 

 

 

 

 

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 beverage vending machine 

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 beverage vending machine 

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 beverage 

vending machine equipment for baseline market conditions and up to seven TSLs. 

Shipments_Summary	 Contains estimates of shipments of an equipment class of beverage vending machine 

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 Worksheet performs calculations to estimate total market shipments by equipment class 

Calcd	 and year in the base case. 

Product Shares	 Contains equipment class percentages of overall beverage vending machine equipment 

shipments. 

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

Rate 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 beverage vending machine 

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. 

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. 

G-2 

BVM_FINAL_FR_TSD_AppendixG_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
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 the April 2009 update to 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 

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 

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. 

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$ 

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 

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 (efficiency 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 

G-3 

BVM_FINAL_FR_TSD_AppendixG_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
	 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 

BVM_FINAL_FR_TSD_AppendixG_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
REFERENCES 

1. U.S. Department of Energy–Energy Information Administration. An Updated Annual Energy 

Outlook 2009 Reference Case Reflecting Provisions of the American Recovery and 

Reinvestment Act and Recent Changes in the Economic Outlook. EIA, Washington, D.C., 

SR/OIAF/2009-03 

G-5 

BVM_FINAL_FR_TSD_AppendixG_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74