10 C

- i APPENDIX 10C. USER INSTRUCTIONS FO R SHIPMENTS AND NIA

SPREADSHEETS

TABLE OF CO NTENTS

10C.1 INTRODUCTION

................................ ................................ ................................ ......... 1

10C.2 MODEL CONVENTIONS

................................ ................................ ............................ 1

10C.3 INDIVIDUAL SHIPMENTS MODEL WORKS HEETS

................................ ............. 3

10C.4 INDIVIDUAL NIA MODEL WORKSHEETS

................................ ............................. 510C

- 1 APPENDIX 10C. USER INSTRUCTIONS FO R SHIPMENTS AND NIA

SPREADSHEET S

10C.1 INTRODUCTION

The results obtained for the shipments analysis and na tional impacts analysis (NIA) can be examined and reproduced using the Microsoft Excel spreadsheet s available on the U.S. Department of Energy Building Technologies website at: http://www1.eere.energy.gov/buildings/appliance_standards/commercial/wicf.html

. The spreadsheet s that are post ed on the DOE website represent the latest version s and have been

tested with both Microsoft Excel 2003 and Microsoft Excel 2007. T o execute the spreadsheet s 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 (describe d in preliminary TSD appendix 8C) and the shipments spreadsheet so as to reduce the complexity of the NIA spreadsheet. As a result, t he shipments spreadsheet is a large and complicated spreadsheet, whereas the NIA spreadsheet is comparatively small. DOE us ed 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 paral lel

and calculated in real

- time. The only exception is that the Shipments Model can run at either a 3 percent or 7 percent discount rat e. 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 spreadsheet s . Rather, in this appendix DOE prov ides a description of the model s , in case users wish to examine DOE’s assumptions and methods or to test alternative assumptions.

10C.2 MODEL CONVENTIONS

As noted above, b ecause of the large number of equipment classes and CSLs considered in this analys is, 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 represent ing a particular combination of envelope equipment class and refrigeration system equipment class.10C

- 2 **•** T he 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 th at 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. E fficiency 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.

**•** Shi pments and stock s 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

workshe ets 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 l arge 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. B y adding up the shipments in each year and applying the same

discount rate to t hose 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 mod el 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 k ilowatt

- hour per year per unit. This no n

- intuitive number

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

cost s 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 consum ption 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 S hipments workbook consists of the followin g 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 Shipm ents 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 s heet. 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 Im pact Analysis (NIA) spreadsheet, including several of the highly non

intu itive 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 la id 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 the10C

- 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 C onstants 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 mode l to begin in some other year. This tab contains the only user input in the mode l

– 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 ini tial stock and the breakdown of that stock by equipment class

.

Growth Rates Summary T his 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 t hat 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 OldBoxOldRe f Rep lacement sheet and calculates the stock and shipments for those units where the pre

standard refrigeration unit needs replacing before the pre

- standard

box does.10C

- 5 NewBoxNewRef Replacem. This sheet is similar to the OldBoxOldRe f Replacement sheet and

calcul ates the shipments and stock of replacements of original

units.

NewBoxNewRef Growth This sheet is similar to the OldBoxOldRe f 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 C alcs 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 w orksheet 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 producti on 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 worksh eet 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 presen ted 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 detai led 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