11A -i APPENDIX 11A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL IMPACT ANALYSIS SPREADSHEETS
TABLE OF CO NTENTS

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-411A -1 APPENDIX 11A. USER INSTRUCTIONS FOR SHIPMENTS AND NATIONAL IMPACT ANALYSIS SPREADSHEET S

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: h ttp://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 p resent value (NPV) from an energy conservation standard. The energy use and associated costs for a g iven trial standard level (TSL) are determined first by calculating the shipments and then calculating the energy use and costs for a ll products shipped und er that T SL. 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 timeline s 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 T echnologies base case s cenario. The calculations in this sheet depend on other sheets: “Stock exist,” “Failed,” “Repl failed,” “Removed for retro,” “New construction,” and “Repl retro+new.” T o calculate total shi pments, the shipments from two life events11A -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 fo r retrofits (“Removed for retro

, ” “Repl retro +new”) + Ballasts shipments for new construction (“New construction,” “Repl retro + new”)

Stock exist This sheet contains ballast stock calculation s for the Existing technologies base case. Stock accounting based on th e 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

Faile d, Repl failed The number of ballasts leaving the stock due to failure is calculated on the tab “Failed” based on the lifetime s from the “L ifetimes” tab . Then, the number of ballasts that replace them is calculated on the tab “Repl failed .” This is done b ecause failed ballasts are not always replaced in kind and substitution is addressed by multiplying by a substitution matrix from the tab “Subst_ fail ed.”

Removed for retro The number of ballasts leaving the stock due to retrofit is calculated on the tab “Failed” based on the ret rofit 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 calculati ons in this sheet are identical to the calculation s in the “Shipments exist” tab, but the supporting tabs are hidden.

Stock emerg This sheet contains ballast stock calculation s for the Emerging Technologies base case. Calculation s 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 rate s for the Existing Technologies scenario.

Lifetimes This tab contains the lifetime distributions by sector

, and retrof it
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 life

cycle 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, v entilation, 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 co sts, taken from the LCC spreadsheet.

NIA o utput This sheet provides the main results of the modeling: i t aggregates the NES and NPVs for both Existing Technologies, Shift and Emerging Technologies, Roll scenarios. These results are pr esented 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 s avings (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 ope rating 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.