8C

- i APPENDIX 8C. USER INSTRUCTIONS FOR LCC AND PBP

SPREADSHEETS

TABLE OF CO NTENTS

8C.1 INTRODUCTION

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

8C.1 MODEL CONVENTIONS

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

8C.2 INDIVIDUAL WORKSHEET S

................................ ................................ .................... 28C

- 1 APPENDIX 8C. USER INSTRUCTIONS FO R LCC AND PBP

SPREADSHEET

8C.1 INTRODUCTION

The results obtained for the life cycle cost (LCC) and payback period (PBP) analyses can be examined and reproduced using the Microsoft Excel spreadsheet avail able on the U.S. Department of Energy Building Technologies website

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

.

There is one spreadsheet covering all combinations of envelope and refrigeration system equipment classe s

. The spreadsheet post ed on the DOE website represent s the latest version and

has bee n tested with both Microsoft Excel 2003 and Microsoft Excel 2007

.

To execute the sprea dsheet requires Microsoft Excel 2003 or a later version. The LCC

and PBP spreadsheet performs calculations to forecast the change in LCC due to an energy

conservation standard , and the PBP that such a change implies. These concepts are ex plained in the main body of preliminary technical support document (TSD) chapter 8

.

The spreadsheet model does not need to be “run” in any way. All permutations are presented in parallel and all fields are calculated in real

- time , so no user action of a ny kind is necessary. Therefore, strictly speaking there are no “instructions” necessary to operate the spreadsheet. Rather, in this appendix DOE provides a description of the model, in case u sers wish to examine DOE’s assumptions and methods or to test al ternative assumptions

.

8C.1 MODEL CONVENTIONS

Because of the large number of equipment classes and candidate standard levels (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 red uce the complexity of the model:

**•** Many worksheets are arranged with each row representing a particular combination of envelope equipment class, refrigeration system equipment class, envelope CSL, and refrigeration system CSL. There are 2,880 such combinations for walk

- in coolers and 2,880 for walk

- in freezers. To reduce complexity, wherever possible DOE laid out worksheets manipulating all 2,880 such combinations in precisely the same order and with the same formulae so that it is possible to “read” the spreadsheet from left to right without examining each row separately.

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

- most worksheets towards o utputs, which are produced on the left

- most worksheets.8C

- 2 **•** 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 parti cular 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 table, and the prefix “o_” indicates a cell which is

used for the Microsoft Excel offset function.

The model’s primary outputs, LCC savings and PBP, are both calculated on the Summary_Clr and Summary_Frz worksheets by using base case an d standards case rows as inputs ( e .g., the life cycle cost for a small non

- display c ooler with a small dedicated indoor medium

- temperature refrigeration system at envelope CSL 4 and refrigeration system CSL 1 is calculated by subtracting the LCC associated with that unit from the LCC associated with the same unit at envelope CSL 0 and r ef rigeration system, CSL 0 )

.

8C.2 INDIVIDUAL WORKSHEET S

The LCC/PBP spreadsheet or workbook consists of the following worksheets:

Readme This sheet contains notes from the model developers for each other and for spreadsheet users.

LCC Summary tables T his sheet gathers LCC savings and PBP results by envelope and

refri geration system equipment class at the bottom half of the page to produce the results that DOE provides in preliminary TSD appendices 8a and 8B. The functions at the top of the page draw on this information to provide the summary table provided in preliminary TSD chapter 8.

Coolers This worksheet reorganizes calculated data from the Summary

_ Clr sheet which is fed into the LCC Summary T ables sheet.

Freezers This worksheet reorganizes ca lculated data from the Summary

_ Frz sheet which is fed into the LCC Summary T ables sheet.

Summary_Clr This sheet is the “workhorse” of the model. The basic LCC and PBP equations described in preliminary TSD chapter 8 are

calculated here. Most of the tabs to the right simply aggregate inputs into the appropriate format for this sheet.

Summary_Frz This worksheet is laid out in the same manner as the Summary_Clr worksheet. They are only separated to facilitate the spreadsheet

convention that all formulae i n a given column should be identical where possible.8C

- 3 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 model to begin in some other year. Note: the Monte Carlo feature has not been enabled for the preliminary analysis, so the cells labelled “Monte Carlo Parameters” on this tab should not be adjusted.

Smaller Tables This sheet handles several miscellaneous concepts that require tables in smaller format than the 2,880 rows of the Summary sheets. Replacement costs, electricity costs, mark

- ups, and equipment lifespan are all included.

Monte Carlo This sheet handles disaggregated information by state. For the next phase of the analysis, DOE will use this information to conduct the LCC subgroup analysis and Monte Carlo

- style uncertainty analysis on the LCC results. The current version of the model simply averages the inputs that are on this page.

LCC_inputs This worksheet handles engineering calculations to do with the envelope and is analogous to the Ref_LCC_inputs tab.

Energy_Clr The worksheet calculates the annual energy use, envelope first cost, and refrigeration system first cost for each type of equipment

at each combination of possible CSLs. These values are provided both as a national average and as specific values for each U.S. state. As with Ref_LCC_inputs, these calculations are described in preliminary TSD chapter 7, not chapter 8.

Energy_Frz This workshe et handles engineering calculations to do with the envelope and is analogous to the Energy_Clr tab.