G-i APPENDIX G. NATIONAL ENERGY SAVINGS SPREADSHEET TABLE OF CONTENTS

G.1 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-1
G.2 WORKSHEETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-1 G.2.1 Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-1 G.2.2 Input Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-1 G.2.2.1 Shipment Summary (Shipments Spreadsheet) . . . . . . . . . . . . . . . . . . G-1 G.2.2.2 Marginal Prices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-2 G.2.2.3 SiteSource . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-2 G.2.3 Calculation Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-2 G.2.3.1 EWH Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-2 G.2.3.2 EWH Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-2 G.2.3.3 GWH Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.3.4 GWH Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.3.5 OWH Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.3.6 OWH Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.3.7 LWH Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.3.8 LWH Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.4 Output Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.4.1 Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-3 G.2.4.2 Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-4 G.2.4.3 Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-4
G.3 USING THE SPREADSHEET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G-4G-1 APPENDIX G. NATIONAL ENERGY SAVINGS SPREADSHEET

G.1 INTRODUCTION The purpose of the National Energy Savings (NES) spreadsheet is to calculate some of the key quantities by which a proposed water heater energy efficiency standard may be evaluated. Two such quantities are national source energy consumption and net present value (NPV). Source energy is total energy consumed by water heaters, including production and transmission losses. NPV is a measure of the net benefit to consumers due to proposed water heater energy efficiency standards. The NES spreadsheet is contained in a Microsoft Excel 97 file. The filename is Nes.xls

. G.2 WORKSHEETS All of the calculations relating to the energy impacts analysis of proposed water heater efficiency standards are contained in the NES spreadsheet. However, not all of the necessary input data are contained in the same file. In particular, data contained in the Shipments spreadsheet (see Appendix F) form the primary input to the NES calculation. In order to correctly operate the NES spreadsheet, therefore, the Shipments spreadsheet must be open simultaneously (works best if Shipments.xls is opened first, and Nes.xls second). G.2.1 Start This worksheet provides an outline of the contents of the spreadsheet and a summary of the most currently updated results. Two tables are shown: Net Present Value for each trial standard level by fuel type and Shipments for each trial standard level by fuel type. In addition, the ‘START’ worksheet serves as the ‘user interface’ for updating and modifying model parameters (see Section G.3 below). G.2.2 Input Worksheets The following worksheets contain data from other sources which are used in the calculation of NPV and energy savings. G.2.2.1 Shipment Summary (Shipments Spreadsheet) In order to forecast water heater energy use, it is crucial to have an accurate estimate of water heater shipments and subsequent efficiency of the stock. Since water heater energy consumption differs by fuel type, relative market share by fuel type is also important. The shipments calculation comprises its own analysis, described in detail in Chapter 11. Calculations and results are located in the Shipments spreadsheet (see Appendix F). Shipment forecasts are summarized in the ‘Shipment Summary’ worksheet of the Shipments spreadsheet. The NES spreadsheet references this data directly. Therefore, in order to operate the NES spreadsheet, the Shipments spreadsheet, Shipments.xlsmust be open simultaneously

.G-2 G.2.2.2 Marginal Prices This worksheet contains marginal fuel price data. Marginal fuel prices, which differ from average fuel prices, are used to calculate savings in operating cost due to improved efficiency of the water heater stock (see Section 12.3 for details). G.2.2.3 SiteSource This worksheet contains the conversion factors for calculating source energy from site energy. Site energy is the energy consumed by a water heater itself, that is, what is reflected on the consumer’s meter. Source energy includes all energy used in the production and delivery of the fuel consumed in the residence. Source energy consumption has general economic, industrial and environmental effects and is therefore a key indicator of the desirability of any proposed standard (see Section 12.6). G.2.3 Calculation Worksheets These worksheets perform all of the calculations necessary for an assessment of NPV and source energy savings. The algorithm of the model is implemented by applying formulas to input data contained in the above worksheets. The calculation worksheets also serve a ‘bookkeeping’ function, displaying intermediate results for each year and water heater fuel type. G.2.3.1 EWH Base This worksheet makes several calculations regarding electric water heaters in the base case scenario, that is, in the absence of energy efficiency standards. Total equipment costs for each year are derived from shipments and average unit retail price and installation costs. Total fuel cost expenditures are calculated by combining unit fuel prices and average energy consumption of the water heater stock. Finally, the total source energy consumption of electric water heaters is calculated using the conversion factors located in the ‘SiteSource’ worksheet. The calculations made in this worksheet form the basis of comparison by which each trial standard level may be evaluated. G.2.3.2 EWH Standards This worksheet calculates energy and equipment cost for electric water heaters, as well as annual source energy consumption, assuming that a particular energy efficiency standard has been implemented. Each of these calculations is compared to the equivalent one in ‘EWH Base’ to give amounts of savings for each year in the forecast period. Net present value is defined as the difference between operating cost savings and equipment cost increases, discounted to the present year. The discount rate quantifies consumer preference for immediate, rather than postponed savings. The present year and discount rate can both be adjusted by the user via the ‘START’ worksheet.G-3 G.2.3.3 GWH Base Same as ‘EWH Base’, except for gas water heaters. G.2.3.4 GWH Standards Same as ‘EWH Standards’, except for gas water heaters. G.2.3.5 OWH Base Same as ‘EWH Base’, except for oil water heaters. G.2.3.6 OWH Standards Same as ‘EWH Standards’, except for oil water heaters. G.2.3.7 LWH Base Same as ‘EWH Base’, except for LPG water heaters. G.2.3.8 LWH Standards Same as ‘EWH Standards’, except for LPG water heaters. G.2.4 Output Worksheets The output worksheets serve two purposes. First, they provide both detailed and summarized results of the calculation worksheets. Secondly, they serve as an interface to the utility impacts and national employment analyses described in Chapters 14 and 15. G.2.4.1 Results and Expenditure by Fuel Worksheets The ‘Results’ worksheet displays the results of the calculation worksheets. For each trial standard level, there is a table containing equipment and operating cost savings for each year in the forecast period. Another table provides the total source savings by year. Total savings and net present value for the forecast period are summarized in a separate table for each fuel type. For convenience, these results are summarized on the ‘START’ page. The worksheets ‘Expenditures by Fuel’ and ‘Expenditures by Fuel (2)’ summarize some of the results separately for electric and natural gas water heaters. These worksheets are needed as the source data for the figures on the chart worksheets ‘Charts-Discounted Expenditures’ and ‘Chart-NPV’ described in section G.2.4.2 below.G-4 G.2.4.2 Charts There are six charts contained in the NES spreadsheet: ‘Chart-Savings’, ‘Chart Site-Savings’, ‘Chart-Consump’, ‘Chart-Discounted Expenditures’, ‘Chart-NPV’, and ‘Chart-NPV vs. Source Savings’. The first three present data tabulated in the ‘Results’ worksheet. ‘Chart Site-Savings’ plots the amount of energy consumed in each year of the forecast period, separately for each of the four fuel types. ‘Chart-Savings’ displays consumer energy savings, equipment losses and resulting net savings. ‘Chart Consumption’ plots total source energy. ‘Chart-Discounted Expenditures’ displays data from the ‘Expenditures by Fuel (2)’ worksheet, comparing fuel and equipment expenditures for electric and gas water heaters for the four trial standard levels. ‘Chart-NPV’ displays data from the ‘Expenditures by Fuel’ worksheet, displaying separate and combined NPV values for electric and natural gas water heaters. ‘Chart-NPV vs. Source Savings’ displays additional data from the ’Results’ worksheet, showing the source energy savings and NPV for each of the four trial standard levels. G.2.4.3 Outputs This worksheet is the interface by which energy impacts of efficiency standards can be used by manufacturer impacts and environmental analyses. There are several tables containing detailed information by fuel type for each year. Included are: equipment and energy cost savings, site energy savings and source energy savings. G.3 USING THE SPREADSHEET In order to provide more flexibility, the NES spreadsheet facilitates some user modifications to the model. The user may select several economic scenarios with which to forecast shipments and fuel prices. This parameter is controlled from the ‘START’ worksheet of the Shipments spreadsheet. The ‘Update’ button repeats all model calculations and summarizes the results in the ‘Results’ worksheet. The user must click on the ‘Update’ button in order to implement desired modifications. The user should also update the spreadsheet whenever it is first opened, in order to be certain that the results listed in ‘Results’ really correspond to the parameters selected. The ‘Output’ worksheet is updated in a similar way, using a button located at the top of the worksheet itself. To update the chart worksheet ‘Chart-NPV vs Source Savings’, the user must run the macro ‘CreateCh12Charts’. To update ‘Chart-Discounted Expenditures’ the user must run the macro ‘CreateCh12Charts2'. The macros can be run by choosing Tools, Macro, Macros from the menu, then selecting and running the desired macro.