10C-i

APPENDIX 10C. USER INSTRUCTIONS FOR SHIPMENTS AND NIA 

SPREADSHEETS

TABLE OF CONTENTS

10C.1 INTRODUCTION .........................................................................................................1

10C.2 MODEL CONVENTIONS............................................................................................1

10C.3 INDIVIDUAL SHIPMENTS MODEL WORKSHEETS .............................................3

10C.4 INDIVIDUAL NIA MODEL WORKSHEETS.............................................................5
10C-1

APPENDIX 10C. USER INSTRUCTIONS FOR SHIPMENTS AND NIA 

SPREADSHEETS

10C.1 INTRODUCTION

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

examined and reproduced using the Microsoft Excel spreadsheets available on the U.S. 

Department of Energy Building Technologies website at: 

http://www1.eere.energy.gov/buildings/appliance_standards/commercial/wicf.html. The 

spreadsheets that are posted on the DOE website represent the latest versions and have been 

tested with both Microsoft Excel 2003 and Microsoft Excel 2007. To execute the spreadsheets

requires Microsoft Excel 2003 or a later version.

There is one spreadsheet for the shipments analysis, and a different spreadsheet for the 

national impacts analysis. DOE structured both the life cycle cost spreadsheet (described in 

preliminary TSD appendix 8C) and the shipments spreadsheet so as to reduce the complexity of 

the NIA spreadsheet. As a result, the shipments spreadsheet is a large and complicated 

spreadsheet, whereas the NIA spreadsheet is comparatively small. DOE used this approach out 

of concern that the large number of equipment classes and candidate standard levels (CSLs) in 

this particular analysis might otherwise require very large amounts of calculation space for the 

NIA, and might exceed the limits of what is supported by Microsoft Excel 2003.

This spreadsheet models do not need to be “run”; permutations are presented in parallel 

and calculated in real-time. The only exception is that the Shipments Model can run at either a 3 

percent or 7 percent discount rate. This is useful for producing outputs for the NIA, but does not 

alter the shipments analysis results themselves. Therefore, strictly speaking there are essentially 

no “instructions” necessary to operate the spreadsheets. Rather, in this appendix DOE provides a 

description of the models, in case users wish to examine DOE’s assumptions and methods or to 

test alternative assumptions.

10C.2 MODEL CONVENTIONS

As noted above, because of the large number of equipment classes and CSLs considered 

in this analysis, the WICF model is structured somewhat differently than other LCC models 

DOE has published in the past. DOE uses several conventions throughout the spreadsheet to 

reduce the complexity of the model: 

• Many worksheets are arranged with each row representing a particular combination of 

envelope equipment class and refrigeration system equipment class. 
10C-2

• The model tracks how much equipment shipped before or after any energy conservation

standard. It is also possible for a walk-in to consist of an envelope that shipped before the 

standard is in place, combined with a refrigeration system that shipped afterwards. The 

model uses the naming conventions “old-old”, “new-new”, and “old-new” to refer to 

these possibilities.

• Efficiency levels are not presented in the same way that they are in the life cycle cost 

(LCC) and NIA spreadsheets. Rather, all equipment that shipped before the standard is 

baseline equipment, of candidate standard level 0; and all equipment that ships afterwards 

is of whatever CSL is in place. Efficiency levels are not used in the shipments analysis, 

but this information is relevant for the NIA.

• In general, logic flows from data sources and assumptions that are assembled on the 

right-most worksheets towards outputs, which are produced on the left-most worksheets. 

• This spreadsheet makes use of the Microsoft Excel named ranges feature. These ranges 

function like variables in mathematics or programming, rather than according to cell 

references. To locate a particular named range, users can press the F5 key. In many cases, 

the first two letters of a named range describes its function: the prefix “c_” indicates a 

constant, the prefix “t_” indicates a tables, and the prefix “o_” indicates a cell which is 

used for the Microsoft Excel offset function.

• Shipments and stocks are calculated as a factor of the initial (2013) stock for use on the 

red “workhorse” worksheets that do the main calculations. For example, in 2016 

refrigeration system shipments amount to 16.11% of the initial stock. Many of the 

worksheets to the right of the red worksheets are devoted to calculating those ratios for 

different building types, years, and equipment types. They are then used on the red sheets 

to calculate the actual number of refrigeration systems shipped, envelopes shipped, and 

stock in a particular year. 

Given the large number of equipment classes and CSLs considered in this rulemaking, DOE also 

found it necessary to take various measures to contain spreadsheet size. Accordingly, in several 

places DOE compresses a very large but straightforward calculation into a shorter but less 

intuitive calculation. In every case the less intuitive calculation is mathematically identical to the 

longer calculation. In most cases the purpose of such calculations is to provide information that 

feeds into the NIA spreadsheet. Key examples:

• Stock-years. By adding up the size of the stock in each year, DOE can arrive at a single 

number which, when multiplied by an annual operating cost or energy use, will produce 

the total operating costs or energy use over the entire analysis period in a much shorter 

calculation than if DOE were to calculate each year separately, and then add them 

together.

• Discounted shipments. By adding up the shipments in each year and applying the same 

discount rate to those shipments that is used in the NIA, DOE can find a single number 

which takes the place of long lists of shipments by year that will later have to be 

discounted. For example, to find the discounted total first costs over the analysis period, 

the NIA model can simply multiply the discounted shipment by a single purchase price, 

rather than multiplying shipments by purchase price in each year, then discounting, then 

adding the results together.
10C-3

• Discounted dollars per kilowatt-hour per year per unit. This non-intuitive number 

compresses a great many calculations into a single step. It represents the lifetime energy 

costs of operating all walk-ins of this equipment class combination, divided by the energy 

used by the average walk-in of that type. The reason it is useful is that in the NIA 

spreadsheet, it can be multiplied by the energy use of walk-ins of that equipment class 

combination to calculate the total operating costs for that type of walk-in. Because there 

are up to 160 possible levels of energy consumption depending on the envelope and 

refrigeration system CSL level for each product class, making this a single step saves a 

great deal of spreadsheet space.

10C.3 INDIVIDUAL SHIPMENTS MODEL WORKSHEETS

The WICF Shipments workbook consists of the following worksheets:

Shipments Summary This sheet aggregates shipments results by envelope or 

refrigeration system equipment class only, rather than by matched 

pair of envelope and refrigeration system equipment classes. 

Stock Summary This sheet is identical to the Shipments Summary sheet, but 

aggregates stock information rather than shipments information. 

This is for informational purposes only.

Shipments Aggregation This sheet gathers shipments and stock data in a useful format for 

aggregation in the Shipments Summary and Stock Summary tabs.

Box Shipments This sheet is the “workhorse” of the model. The main envelope 

shipments calculations take place on this sheet. Most of the tabs to 

the right simply aggregate inputs into the appropriate format for 

this sheet. Individual rows in this sheet describe combinations of 

envelope and refrigeration system equipment classes. The left 

summarize key information that is useful for the National Impact 

Analysis (NIA) spreadsheet, including several of the highly nonintuitive calculations described above.

Ref Shipments This sheet is laid out in the same manner as the Box Shipments 

worksheet but provides the number of refrigeration system 

shipments rather than the number of envelope shipments.

Stock This sheet is laid out in the same manner as the Box Shipments 

worksheet but provides the size of the stock rather than the number 

of shipments. Note that, unlike shipments, the stock size refers to 

both envelopes and refrigeration systems because the number of 

envelopes and refrigeration systems is always the same. Although 

the two components are replaced at different rates and so the 
10C-4

number of shipments is different between them, they are used in a 

one-to-one ratio and so the size of the stock is the same.

Constants etc Constants are put on this sheet and can be manipulated by users, 

e.g. the analysis begins in 2014 but by changing the First Year 

input on this tab users could cause the model to begin in some 

other year. This tab contains the only user input in the model – the 

discount rate can be set to 3 percent or 7 percent on this sheet. 

Note that this does not affect Shipment Model results in any way –

it simply alters the few outputs that the National Impacts Model 

uses (e.g. discounted stock-years).

Small Tables This sheet handles several miscellaneous concepts that require 

tables in smaller format than the 2,880 rows of the Summary 

sheets. Heat rates, electricity costs, and equipment lifespan are all 

included.

Initial Stock This sheet stores data on the initial stock and the breakdown of that 

stock by equipment class. 

Growth Rates Summary This sheet summarizes growth in the five examined building types 

in addition to tracking the type and quantity of replacements, 

across the analysis period. Please note that all values are expressed 

as a percent of the initial stock.

Growth This sheets “translates” growth rates expressed as a share of initial 

stock into stock and shipments numbers expressed as a share of 

initial stock.

OldBoxOldRef Replacem. This sheet tracks the phasing out of the old, pre-standard, stock. 

Because the envelopes and refrigeration systems operate together, 

but age at different rates according to nonlinear Weibull functions, 

this calculation is quite involved. For each year, the share of units 

that are of each possible combination of envelope and refrigeration 

system ages is tracked according to the stock accounting methods 

described in chapter 9 of the preliminary TSD.

OldBoxNewRef Replacem. This sheet is similar to the OldBoxOldRef Replacement sheet and 

calculates the stock and shipments for those units where the prestandard refrigeration unit needs replacing before the pre-standard 

box does.
10C-5

NewBoxNewRef Replacem. This sheet is similar to the OldBoxOldRef Replacement sheet and 

calculates the shipments and stock of replacements of original 

units.

NewBoxNewRef Growth This sheet is similar to the OldBoxOldRef Replacement sheet and 

calculates the shipments and stock of growth over and above the 

stock of original units.

Lifetime calcs This sheet is where DOE specifies the Weibull lifetime distribution 

functions and derives estimates of the initial age distribution of the 

stock, as described in preliminary TSD chapter 9.

Initial Distribution Calcs This sheet calculates the shipments and stock of replacements of 

original units.

Product_Class DOE estimated the share of initial stock that falls into each product 

class based on manufacturer estimates and DOE estimates. The 

resultant calculations are shown on this tab. 

WICF Stock This worksheet is where DOE combines CBECS and Census data 

to estimate the size of the initial stock, as explained in preliminary 

TSD chapter 9.

AEO Buildings Data This sheet contains data on building stock from the Annual Energy 

Outlook, and on milk production over time from the U.S. 

Agricultural Census. DOE uses this information to forecast growth 

rates in the WICF industry as described in preliminary TSD 

chapter 9.

Dairy Farms data This sheet contains data from the U.S. Agricultural Census that 

feeds into the AEO Buildings Data sheet.

10C.4 INDIVIDUAL NIA MODEL WORKSHEETS

The NIA Model contains ten worksheets. Four of the worksheets are presented twice each, once 

at a 3 percent discount rate and once at a 7 percent discount rate.

Readme This worksheet lists a few simple naming conventions used in the 

spreadsheet.
10C-6

Instructions This sheet provides instructions similar to those presented here.

NES Summary tables This sheet aggregates the national energy savings (NES) summary

results that are presented in the preliminary TSD chapter 10 and 

the detailed results that are presented in preliminary TSD appendix 

10A.

NPV Summary tables This sheet aggregates the national NIA summary results that are 

presented in the preliminary TSD chapter 10 and the detailed 

results that are presented in preliminary TSD appendix 10B.

Coolers This sheet is structured identically to the LCC model on the left 

(see preliminary TSD appendix 8C for details). The columns on 

the right combine inputs from the LCC model and the shipments 

model to estimate national energy savings and net present value in 

the manner described in chapter 10 of the preliminary TSD. 

Freezers This sheet calculates the same values as the Coolers sheet, except 

for walk-in freezers