11A-i 

APPENDIX 11A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL 

IMPACT ANALYSIS SPREADSHEETS

TABLE OF CONTENTS

11A.1 INTRODUCTION ...................................................................................................... 11A-1

11A.2 SHIPMENTS MODEL WORKSHEETS ................................................................... 11A-1

11A.3 INDIVIDUAL NIA MODEL WORKSHEETS.......................................................... 11A-3

11A.4 BASIC INSTRUCTIONS ........................................................................................... 11A-4
11A-1 

APPENDIX 11A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL 

IMPACT ANALYSIS SPREADSHEETS 

11A.1 INTRODUCTION

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

be examined and reproduced using a Microsoft Excel 2007 workbook 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. 

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

performs calculations to forecast shipments during the analysis period (2014–2043), 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 trial standard level (TSL) are determined first by calculating the 

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

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

and NPV determined. 

11A.2 SHIPMENTS MODEL WORKSHEETS 

The order of the worksheets in the workbook is the same as the order of the analysis. 

Moving from left to right, shipments worksheets are followed by NIA worksheets. The former 

are listed and described below. Most of the spreadsheets are structured similarly—they have 

timelines going across (in the columns) and product classes going down (in the rows). Some 

spreadsheets have product classes running both down and across and they model either 

substitution effects among product classes for both base case and standards case shipments, or 

shifts in shipments that occur due to the introduction of a standard. Italicized text highlights 

some formulas found within the worksheets. 

Formats This sheet is the source of the product class structure and contains 

values to be used in all other sheets, and presents the product 

classes and ballast mappings. 

Shipments Input Data This sheet contains the historical shipments data that was 

developed from Census (Current Industry Reports) data. It is 

separated by product class and sometimes ballasts within product 

class. 

Shipments exist This sheet contains the historical shipments data and projected 

shipments for the entire analysis period for the Existing 

Technologies base case scenario. The calculations in this sheet 

depend on other sheets: “Stock exist,” “Failed,” “Repl failed,”

“Removed for retro,” “New construction,” and “Repl retro+new.”

To calculate total shipments, the shipments from two life events 
11A-2 

are summed up: ballast failure, and ballast replacement due to 

retrofit/ballast delivery for new construction. 

New ballasts shipments = Shipments to replace failed ballasts (“Failed”, “Repl failed”) 

+ Ballasts shipments for retrofits (“Removed for retro,” “Repl 

retro +new”) 

+ Ballasts shipments for new construction (“New construction,” 

“Repl retro + new”) 

 

Stock exist This sheet contains ballast stock calculations for the Existing 

technologies base case. Stock accounting based on the following 

formula:

Number of ballasts in 2006 stock = Number of ballasts in 2005 Stock 

- Failed ballasts, ballasts removed due to retrofits 

+ Ballast shipped to replace failed and retrofitted 

+ Ballasts shipped to new construction 

Failed, Repl failed The number of ballasts leaving the stock due to failure is 

calculated on the tab “Failed” based on the lifetimes from the 

“Lifetimes” tab. Then, the number of ballasts that replace them is 

calculated on the tab “Repl failed.” This is done because failed 

ballasts are not always replaced in kind and substitution is 

addressed by multiplying by a substitution matrix from the tab 

“Subst_failed.” 

Removed for retro The number of ballasts leaving the stock due to retrofit is 

calculated on the tab “Failed” based on the retrofit rate from tab 

“Lifetimes.” 

New construction Shipments in “New construction” are modeled as a function of the 

current stock of ballasts. 

 

Repl retro+new The number of ballasts that are shipped for retrofit/new 

construction is calculated on this tab. This is done because ballasts 

are not always replaced in kind and substitution is addressed by 

multiplying by substitution matrix from the tab “Subst_new_retro.” 

Shipments emerg This sheet contains the historical shipments data and projected 

shipments for the entire analysis period for the Emerging 

Technologies base case scenario. The calculations in this sheet are 

identical to the calculations in the “Shipments exist” tab, but the 

supporting tabs are hidden. 

Stock emerg This sheet contains ballast stock calculations for the Emerging 

Technologies base case. Calculations are similar to those in the tab 

“Stock exist.” 
11A-3 

Growth rates This sheet contains growth rates needed to estimate shipments due 

to new construction by sector, and penetration rates for the 

Existing Technologies scenario. 

Lifetimes This tab contains the lifetime distributions by sector, and retrofit 

rates. 

Subst_new_retro This tab contains substitution matrices for the retrofit/new 

construction life event. 

Subst_failed This tab contains substitution matrices for the ballast failure life 

event. 

Roll Scenario This tab has conversion matrices for the Roll-up scenario for all 

TSLs. 

Shift Scenario This tab has conversion matrices for the Shift scenario for all 

TSLs. 

Ship_exist_st_1stage Shipments from the base case Existing Technologies scenario are 

adjusted to be used with conversion matrices. 

Ship_exist_shift This spreadsheet calculates shipments for the Existing 

Technologies, Shift scenario for all TSLs (Shipments for Existing 

Technologies, Roll scenario are on a hidden tab “Ship_exist_roll”). 

Stock_exist_shift This spreadsheet takes care of stock accounting for the Existing 

Technologies, Shift scenario for all TSLs. 

Ship_emerg_st_1stage, Ship_emerg_st_2stage 

Shipments from the base case Emerging Technologies scenario are 

adjusted to be used with conversion matrices. 

Ship_emerg_roll This spreadsheet calculates shipments for the Emerging 

Technologies, Roll scenario for all TSLs (Shipments for Emerging 

Technologies, Shift scenario are on a hidden tab 

“Ship_emerg_shift”). 

Stock_emerg_roll This spreadsheet takes care of stock accounting for the Emerging 

Technologies, Roll scenario for all TSLs. 

11A.3 INDIVIDUAL NIA MODEL WORKSHEETS

The NIA Model, which consists of nine worksheets, relies on shipments data and lifecycle cost (LCC) results previously produced. Two of the worksheets are presented twice each: 

once at a 3-percent discount rate and once at a 7-percent discount rate.
11A-4 

Assumptions This worksheet contains the assumptions for energy consumption, 

operating hours, heating, ventilation, and air-conditioning (HVAC) 

factor, rebound rate and estimated marginal heat rates for Existing 

Technologies, Shift and Emerging Technologies, and Roll 

scenarios.

Oper_inst costs This worksheet contains data on operating costs and installed costs, 

taken from the LCC spreadsheet. 

NIA output This sheet provides the main results of the modeling: it aggregates 

the NES and NPVs for both Existing Technologies, Shift and 

Emerging Technologies, Roll scenarios. These results are 

presented in the technical support document (TSD) executive 

summary and chapter 11, National Impact Analysis.

Energy_sav_ExShift This tab calculates energy savings (NES values) for the Existing 

Technologies, Shift scenario.

Energy_sav_EmRoll This tab calculates energy savings (NES values) for the Existing 

Technologies, Shift scenario.

NPV_ExShift_3 This tab calculates net present values (NPVs) for the Existing 

Technologies, Shift scenario at a 3 percent discount rate. 

NPV_ExShift_7 This tab calculates net present values (NPVs) for the Existing 

Technologies, Shift scenario at a 7 percent discount rate. 

NPV_EmRoll_3 This tab calculates net present values (NPVs) for the Emerging 

Technologies, Roll scenario at a 3 percent discount rate. 

NPV_EmRoll_7 This tab calculates net present values (NPVs) for the Emerging 

Technologies, Roll scenario at a 7-percent discount rate.

11A.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. 

2. The user can modify values on the tabs “Growth rates”, “Lifetimes”, “Assumptions” and 

“Oper_inst costs.” Users can the look at the “NIA output” sheet to see how the changes

they implemented affect NES and NPVs. Also, discount rates can be changed on 

NPV-generating tabs to evaluate the effect of various discount rates.