8C-i

APPENDIX 8C. USER INSTRUCTIONS FOR LCC AND PBP

SPREADSHEETS

TABLE OF CONTENTS

8C.1 INTRODUCTION .........................................................................................................1

8C.1 MODEL CONVENTIONS............................................................................................1

8C.2 INDIVIDUAL WORKSHEETS....................................................................................2
8C-1

APPENDIX 8C. USER INSTRUCTIONS FOR 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 available 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 classes. The spreadsheet posted on the DOE website represents the latest version and 

has been tested with both Microsoft Excel 2003 and Microsoft Excel 2007. 

To execute the spreadsheet 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 explained 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 any 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 users 

wish to examine DOE’s assumptions and methods or to test alternative 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 reduce 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 outputs, 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 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 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 and standards case rows as inputs (e.g., the life 

cycle cost for a small non-display cooler 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 refrigeration system, CSL 0).

8C.2 INDIVIDUAL WORKSHEETS 

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 This sheet gathers LCC savings and PBP results by envelope and 

refrigeration 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 Tables sheet.

Freezers This worksheet reorganizes calculated data from the Summary_Frz 

sheet which is fed into the LCC Summary Tables 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 in a given column should be identical 

where possible.
8C-3

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. 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 worksheet handles engineering calculations to do with the 

envelope and is analogous to the Energy_Clr tab.