F-i

APPENDIX F. SHIPMENTS SPREADSHEET

TABLE OF CONTENTS

F.1 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2 WORKSHEETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2.1 Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2.2 Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2.3 Input Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2.3.1 Engineering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2.3.2 Price Projections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-1

F.2.3.3 Housing and Income . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-2

F.2.3.4 Energy Price . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-2

F.2.3.5 Retirement Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-2

F.2.4 Calculation Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-2

F.2.4.1 Market Share New . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-2

F.2.4.2 Elec WH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-2

F.2.4.3 Gas WH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.4.4 Oil WH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.4.5 LPG WH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.5 Output Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.5.1 Shipment Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.5.2 ChartBase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.5.3 Chart1-4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.5.4 Charts-fuel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.2.5.5 Chart - Incremental . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-3

F.3 USING THE SPREADSHEET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F-4
F-1

APPENDIX F. SHIPMENTS SPREADSHEET

F.1 INTRODUCTION

The Water Heater Shipments Spreadsheet contains all of the necessary input information and

calculations to forecast water heater shipments by fuel type for a set of regulatory scenarios (trial

standard levels). The spreadsheet is contained in a Microsoft Excel 97 file. The filename is

Shipments.xls.

F.2 WORKSHEETS

F.2.1 Start

This worksheet provides an outline of the contents of the spreadsheet. In addition, it serves

as the ‘user interface’ for updating and modifying model parameters (see Section F.3 below). 

F.2.2 Reference

This worksheet describes of the sources of key input data, and the mathematical form of the

model which predicts fuel-type market shares to new housing. 

F.2.3 Input Worksheets

There are several worksheets which contain data from other sources which are used in the

calculation of shipments predictions. These are:

F.2.3.1 Engineering

The engineering worksheets contain water heater equipment and energy consumption data

for design options of varying efficiency. This data comes directly from the Life Cycle Cost analysis.

Each trial standard level is composed of a combination of one design option for each fuel type (see

Tables 9.7.1, 9.7.2, 9.7.3 and 9.7.4 in Section 9.7 for definitions of trial standard levels). The

configuration defining each trail standard level is contained explicitly in the engineering worksheets.

The engineering worksheets also contains an estimate of market share by efficiency level, which is

used along with the other data to calculate average values of equipment cost and energy consumption

for each trial standard level. 

F.2.3.2 Price Projections

This worksheet contains fuel prices for each year from 1980 to 2030. Predictions of future

prices are given for four cases: Reference, Low and High growth scenarios from Annual Energy

Outlook 2000 (AEO2000), and a projection provided by the Gas Research Institute. The user can

select which scenario to use via a pull-down menu on the ‘START’ worksheet (see Section F.3). 
F-2

F.2.3.3 Housing and Income

This worksheet is similar to the ‘Price Projections’ worksheet. It contains predictions of new

housing starts and average household income for the AEO2000 Reference, Low and High Growth

Cases.

F.2.3.4 Energy Price

This worksheet contains the user’s selection of fuel price, housing and income data from the

‘Price Projections’ and ‘Housing and Income’ worksheets (see Section F.3). In the case that fuel

price projections from the Gas Research Institute are selected, housing and income predictions are

taken from the AEO2000 Reference Case.

F.2.3.5 Retirement Function

This worksheet contains data relating to the forecast of water heater replacements. The

retirement function gives the probability that a water heater will fail, and therefore need replacement,

depending on how old it is. 

F.2.4 Calculation Worksheets

These worksheets perform all of the calculations necessary to forecast water heater

shipments. 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. 

F.2.4.1 Market Share New

This worksheet implements the fuel-type market share model. The model consists of an

econometric calculation which depends on equipment costs and energy consumption taken from the

‘Engineering’ worksheets, as well as fuel price, housing and income data taken from the ‘Energy

Price’ worksheet. 

F.2.4.2 Elec WH

This worksheet calculates the total number of electric water heaters shipped, for each year

in the forecast period. It uses the ‘Retirement Function’ worksheet to keep track of how many

electric water heaters are replaced, and keeps track of the age of the remaining stock. It also

calculates the number of electric water heaters which are shipped to new housing, based on the

housing data contained in ‘Energy Price’ and the electric water heater market share calculated in

‘Market Share New’. 
F-3

F.2.4.3 Gas WH

Same as ‘Elec WH’, for gas water heaters.

F.2.4.4 Oil WH

Same as ‘Elec WH’, for oil water heaters.

F.2.4.5 LPG WH

Same as ‘Elec WH’, for LPG water heaters.

F.2.5 Output Worksheets

These worksheets provide the user with both detailed and at-a-glance results. They also

provide output data to the National Energy Savings analysis.

F.2.5.1 Shipment Summary

This worksheet contains the results of the calculations worksheets. Annual shipments are

listed for each trial standard level by fuel type, for the period 1981-2030. The ‘Shipment Summary’

worksheet serves as the primary output data used by the National Energy Savings spreadsheet

described in Appendix G.

F.2.5.2 ChartBase

This chart displays each year’s shipments of each fuel type for the Base Case scenario.

F.2.5.3 Chart1-4

These charts display each year’s shipments of each fuel type for Trial Standard Levels 1

through 4.

F.2.5.4 Charts-fuel

There is one chart for each fuel type - each chart displays annual shipments for the base case

scenario and all trial standard levels.

F.2.5.5 Chart - Incremental

These charts are identical to ‘Charts-fuel’, except that each shows the difference in shipments

between a given trial standard level and the base case scenario. 
F-4

F.3 USING THE SPREADSHEET

The ‘Shipments Summary’ worksheet contains the results of the water heater shipments

forecast for a reference case of assumptions and inputs. In order to provide more flexibility, the

spreadsheet facilitates some user modifications to the model. These modifications are controlled

from the ‘START’ worksheet. The user may select a particular macroeconomic forecast which

determines fuel prices, housing construction and income data to be used by the model. The user may

also directly input new values for implicit discount rates, which quantify consumer preference for

immediate, over delayed savings. The trial standard level represented in the calculation worksheets

is also controlled from the ‘START’ worksheet.

 The ‘Update’ button reruns all model calculations and summarizes the result in the

‘Shipments Summary’ 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 ‘Shipment Summary’ really correspond to the parameters

selected.