10A-i 

APPENDIX 10A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL 

IMPACT ANALYSIS SPREADSHEETS

TABLE OF CONTENTS

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

10A.2 INDIVIDUAL SHIPMENTS MODEL WORKSHEETS ...................................................1

10A.3 INDIVIDUAL NIA MODEL WORKSHEETS...................................................................2

10A.4 BASIC INSTRUCTIONS ....................................................................................................3

EERE-2007-BT-STD-0016 COMMENT 25
10A-1 

APPENDIX 10A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL 

IMPACT ANALYSIS SPREADSHEETS 

10A.1 INTRODUCTION

The results obtained for the shipments analysis and national impacts analysis (NIA) can 

be examined and reproduced using Microsoft Excel spreadsheets available on the 

U.S. Department of Energy (DOE) Building Technologies website at:

http://www1.eere.energy.gov/buildings/appliance_standards/residential/fluorescent_lamp_ballast

s.html. The spreadsheets posted represent the latest versions that have been tested with both 

Microsoft Excel 2003 and 2007. Microsoft Excel 2003 or later is required to execute the 

spreadsheets. 

There is one spreadsheet for both the shipments and the NIA model. This spreadsheet

performs calculations to forecast shipments during the analysis period (2014–2015), the national 

energy savings (NES, i.e., the change in national energy use from the base to standards cases), 

and the net present value (NPV) from an energy conservation standard. The energy use and 

associated costs for a given candidate standard level (CSL) are determined first by calculating 

the shipments and then calculating the energy use and costs for all products shipped under that 

CSL. The differences between the standards and base cases can then be compared, and the 

overall NES and NPV determined. 

10A.2 INDIVIDUAL SHIPMENTS MODEL WORKSHEETS

There are several shipment worksheets in the NIA workbook, as listed and described 

below. 

Assumptions This worksheet contains the assumptions for ballast lifetime and 

operating hours. 

Charts This sheet contains stock data organized into various charts. 

Data Tables Ship This sheet summarises data from “Details Ship” for standards and 

base case by type of lamps operated by a ballast. 

Data Tables Stock This sheet is summarises data from “Details Stock” for standards 

and base case by type of lamps operated by a ballast. 

Details Growth (LE2) This sheet contains data on shipments from life event 2 (LE2): 

installation of a new lamp-and-ballast system by product class,

CSL, and sector. 

Details Ship This sheet contains shipments results in any given analysis year by 

product class and standard level both for base case and standards 

case as well as sector (residential, commercial, or industrial). 

EERE-2007-BT-STD-0016 COMMENT 25
10A-2 

Details Stock This sheet contains data on stock of ballast for any given analysis 

year by product class and standard level both for base case and 

standards case as well as sector (residential, commercial, or 

industrial). 

Details Replacement (Life Event 1, LE1) 

 This sheet contains data on shipments from LE1: replacement of a 

failed ballast by product class, CSL, and sector. 

Emerging Technology This sheet describes the effective light-emitting diode (LED) 

penetration rate over the analysis period for selected product 

classes. The numbers presented represent the quantity of LED 

purchased in displaced ballast sales.

Engineering Summary This sheet contains engineering data about the various 

representative products.

Historical Shipments This sheet summarizes historical data on ballast shipments in 

1990−2005. 

Initial Stock This sheet stores data on the initial stock and the breakdown of that 

stock by product class. 

Summary Shipments This sheet aggregates shipments results by product class and 

standard level both for base case and standards case, projected 

until 2045. 

Summary Stock This sheet is identical to the Shipments Summary sheet but 

aggregates stock information rather than shipments information. 

10A.3 INDIVIDUAL NIA MODEL WORKSHEETS

The NIA Model, which consists of nine worksheets, relies on shipments data produced. 

Four of the worksheets are presented twice each: once at a 3-percent discount rate and once at a 

7-percent discount rate. 

Assumptions This worksheet contains the assumptions of ballasts lifetime and 

operating hours. 

Ballasts Cost List This spreadsheet presents LCC and its components by product 

class and life event. 

Data Tables NPV Net installed price, annual electricity use, annual maintenance 

costs, and LCC values for both base and standards cases are 

calculated by product class and life event on this worksheet 

according to user inputs from “Summary Results.” 

EERE-2007-BT-STD-0016 COMMENT 25
10A-3 

Electricity Prices This sheet contains projected average electricity prices by sector 

(residential, industrial, and commercial) for the economic growth 

scenarios: reference, high, and low. 

Engineering Summary This sheet contains engineering data about the various 

representative products. 

 

NPV Tables Ship This sheet provides results similar to “Data Tables NPV” but also 

gives a breakdown by sector (residential, commercial, and 

industrial). 

NIA Outputs This sheet provides the main results of the simulation; it aggregates 

the NES, annual operating cost savings, instalment costs, and 

NPV. These results are presented in the technical support 

document (TSD) executive summary and chapter 10, National 

Impact Analysis.

Operating Hours This sheet contains operating hour distributions by sector 

(residential, commercial, or industrial). 

Summary of Results This spreadsheet contains the main switchboard that takes in user 

inputs. Model parameters such as efficiency level, energy prices 

trend, discount rate, etc. are defined by user. Shipments resulting 

from chosen parameters are also presented. 

10A.4 BASIC INSTRUCTIONS 

Below are basic instruction for operating shipments and NIA workbook: 

1. Once the spreadsheet file is downloaded, open the file using Excel, and enable macros. 

Begin with the tab, “Summary of Results.” 

2. The user can change the model parameters listed in the light blue box named “User 

Inputs” as follows: 

a. CSL Standard: Select the desired candidate standard level from the drop-down menu. 

b. Standards Case Efficacy: Select either Roll-up or Shift scenario for the standards 

case. The difference between the scenarios is described in TSD chapter 9, Shipments 

Analysis. 

c. Technology Level: Select between Existing Technologies and Emerging 

Technologies for the base case. The Existing Technologies scenario implies that LED 

and ceramic metal halide (CMH) systems do not affect FLB market, while the 

Emerging Technologies scenario considers penetration of LED and CMH systems. 

d. Standard First Year: Fixed at 2014, this sets the first year that the CSL standards are 

in effect. 

EERE-2007-BT-STD-0016 COMMENT 25
10A-4 

e. Impact Last Year: The default setting is 2045 for the 30-year analysis period; use the 

drop-down menu to select a shorter period. 

f. Discount Rate: Select the desired discount rate for the NIA analysis. To change the 

value (ranges from 3 percent to 9 percent), use the drop-down menu. 

g. Electricity Price Growth: Use the drop-down menu to select the desired electricity 

price growth case (reference, high, or low). 

3. The user can change the forecast settings, view the current precision level, and run the 

model by changing the parameter listed in the yellow box named “Forecast Settings” with 

the following parameter: 

 

Precision Level: Select the level of precision with which to increase the model. A higher 

level will produce more accurate results but will require more time to generate the 

forecast. DOE produces its results by setting this value to Level 10 (maximum).

4. Once the selection is made, click the “Run Forecast” button on the right. This will 

increase the current precision level of the model by the value set by the precision level 

drop-down box. Depending on computer speed, it could take from 30 minutes to 2 hours 

to complete a simulation. 

Note: After a simulation finishes, clicking the “Run Simulation” button will cause the 

model to run again and will incrementally increase the precision level. Using this 

technique, gradually increase the precision level through successive runs; this is an 

effective way of running the model with slower machines. Similarly, clicking the “Clear 

Simulation” button will reset the model, clearing results and setting the precision level 

back to 0. 

5. The NIA results are automatically updated and reported for each product class on the 

“NIA Outputs” tab. Shipments results are aggregated by product class on the “Summary 

of Results,” “Summary Shipments,” and “Summary Stock” sheets. 

EERE-2007-BT-STD-0016 COMMENT 25