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-4
G-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.xls must 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.