APPENDIX A. INSTRUCTIONS FOR USING THE ENGINEERING ANALYSIS

SPREADSHEET

TABLE OF CONTENTS

A.1 INTRODUCTION...........................................................................................................A­1

A.2 USING THE ENGINEERING ANALYSIS SPREADSHEET ......................................A­1

A.2.1 “Start Here” Worksheet.......................................................................................A­1

A.2.2 “Introduction” Worksheet ...................................................................................A­2

A.2.3 “Results Summary” Worksheet...........................................................................A­3

A.2.4 “Advanced Inputs” Worksheet............................................................................A­6

A.2.5 “Equipment Classes” Worksheet ........................................................................A­9

A.2.6 “Assumptions” Worksheet ..................................................................................A­9

A.2.7 “Model” Worksheet...........................................................................................A­10

A.2.8 “Design Options” Worksheet............................................................................A­10

A.2.9 Supporting Worksheets .....................................................................................A­10

LIST OF FIGURES

Figure A.2.1 Screen shot of the “Start Here” worksheet. ..........................................................A­2

Figure A.2.2 Screen shot of the “Introduction” worksheet (not all content shown)..................A­3

Figure A.2.3 Screen shot of the “Results Summary” worksheet. ..............................................A­4

Figure A.2.4 Screen shot of the “Design Option Matrix” pop­up window................................A­5

Figure A.2.5 Screen shot of the “Advanced Inputs” worksheet.................................................A­7

Figure A.2.6 Screen shot of the “Design Option Order” worksheet..........................................A­9

A­i

 EE-2006-STD-0126 COMMENT 11.2
A­ii

 EE-2006-STD-0126 COMMENT 11.2
APPENDIX A. INSTRUCTIONS FOR USING THE ENGINEERING ANALYSIS

SPREADSHEET

A.1 INTRODUCTION

This appendix presents instructions for using the engineering analysis spreadsheet, which

DOE made public on its website for stakeholder review

(http://www.eere.energy.gov/buildings/appliance_standards/commercial/refrigeration_equipment

.html). The engineering analysis spreadsheet filename is

“cre_anopr_engineering_20070703.xls.”a

The engineering analysis spreadsheet details the assumptions, methodology, and

calculation of cost­efficiency curves using a design­option approach (see chapter 5 of this TSD

© for more information on this approach). It was developed using Microsoft Excel , which is a

general spreadsheet application and is known to most stakeholders. In addition, the engineering

analysis spreadsheet is designed to be stand­alone, and has been thoroughly documented. This

appendix serves as additional guidance on how to navigate, use, and understand the engineering

analysis spreadsheet.

A.2 USING THE ENGINEERING ANALYSIS SPREADSHEET

The content of the spreadsheet is contained on eight primary worksheets:

• Start Here – General information on how to navigate the spreadsheet.

• Introduction – Overview of the spreadsheet.

• Results Summary – DOE’s cost­efficiency results.

• Advanced Inputs – Cost­efficiency results with user­defined inputs.

• Equipment Classes – Description of covered equipment classes.

• Assumptions – List of numerical and non­numerical assumptions used.

• Model – Description of the energy­consumption model.

• Design Options – Description of design options.

A.2.1 “Start Here” Worksheet

When the spreadsheet is opened, the “Start Here” worksheet will be displayed (see Figure

b A.2.1). This worksheet contains a legend that describes the formatting used throughout the

a Upon opening the spreadsheet, the user may be asked to enable macros. Several macros are utilized in the user

interface and calculation routines, so macros should be enabled for full functionality. If macro security in Excel is

set to High or Very High, macros will be automatically disabled and the spreadsheet will loose significant

functionality or may crash. Set macro security to Medium or Low and restart Excel.

b The eight primary worksheets are limited in width in order to fit comfortably on all monitors. Scrolling down may

be necessary to see all content. Each primary worksheet has an “END OF PAGE” footer to indicate the end of

content.

A­1

 EE-2006-STD-0126 COMMENT 11.2
spreadsheet. This formatting is intended to highlight different types of information and areas of

user input (e.g., yellow boxes are used to highlight important information and blue boxes with

black borders accept user input). The “Start Here” worksheet also contains navigation links to

the other main worksheets, which are duplicated on each of the primary sheets for navigation.

Figure A.2.1 Screen shot of the “Start Here” worksheet.

A.2.2 “Introduction” Worksheet

The “Introduction” worksheet provides general background information on the

commercial refrigeration equipment (CRE) rulemaking and a brief description of each of the

other primary content worksheets (see Figure A.2.2). Additionally, a link is provided to the

online TSD for reference. The links after each worksheet description can be used to go to those

worksheets.

A­2

 EE-2006-STD-0126 COMMENT 11.2
Figure A.2.2 Screen shot of the “Introduction” worksheet (not all content shown).

A.2.3 “Results Summary” Worksheet

The “Results Summary” worksheet provides tabulated results for each of the fifteen

equipment classes DOE directly examined (see Figure A.2.3). The left portion of the worksheet

contains a number of user­selectable options for selecting an equipment class and displaying

different information about that class. The “Equipment Class” menu lets the user select the

equipment class by its lettering designation.c Displayed to the right are the equipment class

description and tabulated cost­efficiency data for both the analytically derived DOE curve and,

where applicable, the industry­supplied curve.

Equipment class designations consist of a combination (in sequential order separated by periods) of a product

family code (VOP=vertical open, SVO=semivertical open, HZO=horizontal open, VCT=vertical transparent doors,

VCS=vertical solid doors, HCT=horizontal transparent doors, HCS=horizontal solid doors, or SOC=service over

counter), an operating mode code (RC=remote condensing or SC=self­contained), and a rating temperature code

(M=medium temperature (38°F), L=low temperature (0°F), or I=ice­cream temperature (­15°F)). For example,

“VOP.RC.M” refers to the “vertical open, remote condensing, medium temperature” equipment class. See Chapter

3, market and technology assessment, for a more detailed explanation of the equipment class terminology.

A­3

c

 EE-2006-STD-0126 COMMENT 11.2
Figure A.2.3 Screen shot of the “Results Summary” worksheet.

The tabulated cost­efficiency data can be displayed without normalization, or be

normalized by total display area (TDA), or refrigerated volume (V). Use the “Normalization”

radio buttons on the left to select. The data can also be displayed as manufacturer production

cost (MPC), manufacturer selling price (MSP), or manufacturer list price (MLP). Use the

“Manufacturer Pricing” radio buttons on the left to select. (Note that the industry­supplied data

A­4

 EE-2006-STD-0126 COMMENT 11.2
is always displayed in the form of TDA­normalized MLP and CDEC, and is not affected by the

radio button selection.)

To view a plot of the cost­efficiency curve, press the “Plot Cost­Efficiency Curve” button

on the left. A pop­up window displays a plot of the data and provides user­selectable options for

normalization and manufacturer pricing, and display of the corresponding industry­supplied

d curve. To view case design specifications, press the “View Design Specifications” button and a

pop­up window will display them. To view design options, press the “View Design Options”

button on the left. A pop­up window will display a matrix of design options for the baseline and

all levels above the baseline (see Figure A.2.4). Changes from one level to the next are

highlighted in yellow and design options not applicable to the current equipment class are hashed

in gray.

Figure A.2.4 Screen shot of the “Design Option Matrix” pop­up window.

The cost­efficiency data are calculated using the case design specifications, design

options, and assumptions determined by DOE. To view the spreadsheet calculation of this data,

follow the “View Results Summary Calculation” link in the yellow information box. The

Results Summary Calculation worksheet displays the case design specifications, design options,

and the energy consumption calculation for each design option level (baseline, plus all levels

above the baseline). Each row of the model contains a different step in the calculation, while

each column contains the calculation for a particular design option level. Columns for levels

above the maximum design option level are grayed­out. Follow the “Return to Results

Summary” link to return to the Results Summary worksheet.

d Industry­supplied curves were provided for the VOP.RC.M, SVO.RC.M, HZO.RC.L, and VCT.RC.L equipment

classes. The option to display an industry­supplied curve is not provided for other equipment classes.

A­5

 EE-2006-STD-0126 COMMENT 11.2
A.2.4 “Advanced Inputs” Worksheet

The “Advanced Inputs” worksheet is similar to the “Results Summary” worksheet, but

allows for greater user interaction and customization of inputs (see Figure A.2.5). To select an

equipment class, choose the equipment family, condensing unit type, and operating temperature

using the appropriate radio buttons. The equipment class description is displayed, along with

two sets of tabulated cost­efficiency data.e The first set is identical to the data displayed in the

“Results Summary” worksheet, using the DOE case design specifications and baseline design

options for that equipment class. The second set of data calculates the cost­efficiency data using

user­defined inputs contained on the lower part of the worksheet. The “Plot Cost­Efficiency

Curves” button and the “Normalization” and “Manufacturer Pricing” radio buttons are identical

in function to the Results Summary worksheet. When the cost­efficiency curve is plotted,

however, both sets of cost efficiency data will be displayed.

Below the tabulated cost­efficiency data are case design specifications for both the DOE

curve and the user­defined curve. The first column contains the DOE values while the second

column contains user values (upon opening the spreadsheet or changing the equipment class, the

user values automatically default to the values DOE used, thus the tabulated cost­efficiency data

will initially be identical for DOE and user values). The user values can be edited, and the

updated cost­efficiency data will be displayed immediately (hashed blue cells are not relevant to

the current equipment class and have no effect on the calculation). Pressing the “Plot CostEfficiency Curves” button will display the DOE and user curves side by side. After editing the

user values, pressing the “Default” button will return all values to the DOE values.

The “View/Select Design Options” button takes the user to the “Design Option Order”

sheet, which contains a design options selection matrix (see Figure A.2.6). Use this design

option selection matrix to change the baseline design options and implementation order of design

options for the current equipment class. Design options not applicable to the current equipment

class are hashed gray, and design options changed from the previous level are highlighted in

yellow. Using the in­cell drop­down menus, select baseline design options, then select one or

more design option changes for each level above the baseline, up to 15 levels. If there are fewer

than 15 design option changes, fill the remaining levels with the last configuration. To order

design options automatically by payback period (shortest to longest), press the "Auto­Sort

Design Options" button after selecting baseline design options. To compare to the order DOE

used (where applicable), press the "View DOE Design Option Order" button, and a pop­up

similar to that shown in Figure A.2.4 will be displayed. To return the design option matrix to the

default DOE values (where applicable), press the “Return to Default DOE Order” button. When

complete, press the “Complete” button to return to the “Advanced Inputs” worksheet. Press the

“Cancel” button to cancel any changes.

e Equipment classes that DOE did not analyze directly do not have default case design specifications or baseline

design options, and will not display cost­efficiency results. To generate results, enter the necessary case design

specifications and use the “View/Select Design Options” button to specify baseline design options and design option

implementation order.

A­6

 EE-2006-STD-0126 COMMENT 11.2
Figure A.2.5 Screen shot of the “Advanced Inputs” worksheet.

A­7

 EE-2006-STD-0126 COMMENT 11.2
Figure A.2.5 Screen shot of the “Advanced Inputs” worksheet (continued).

The cost­efficiency data for the DOE curve and the user­defined curve are calculated

using the case design specifications, design options, and assumptions from DOE and user inputs.

These two calculations are identical to the calculation used in the Results Summary worksheet,

but they are carried out in a separate worksheet. To view the spreadsheet calculation of this data,

follow the “View Advanced Inputs Calculation” link in the yellow information box. The

Advanced Inputs Calculation worksheet displays the case design specifications, design options,

and the energy consumption calculation for each design option level (baseline, plus all levels

above the baseline) for both calculations (it may be necessary to scroll to the right to see the user

input calculation). Each row of the model contains a different step in the calculation, while each

column contains the calculation for a particular design option level. Columns for levels above

the maximum design option level are grayed­out. Follow the “Return to Advanced Inputs” link

to return to the Advanced Inputs worksheet.

A­8

 EE-2006-STD-0126 COMMENT 11.2
Figure A.2.6 Screen shot of the “Design Option Order” worksheet.

A.2.5 “Equipment Classes” Worksheet

The “Equipment Classes” worksheet contains information on the equipment classes

covered by this rulemaking, and the subset of 15 equipment classes analyzed directly in DOE’s

engineering analysis. Details of equipment class organization are presented in chapter 3 of this

TSD. To view the results summary for any of the 15 equipment classes analyzed directly, follow

the link for that equipment class under the “Results Summary Link” column. To view results or

modify inputs for any of the 38 equipment classes, follow the link for that equipment class under

the “Advanced Inputs Link” column.

A.2.6 “Assumptions” Worksheet

The “Assumptions” worksheet lists non­numerical assumptions as well as numerical

assumptions and engineering constants used in the calculation of cost­efficiency data. Nonnumerical assumptions include general assumptions about the analysis and design options.

Numerical assumptions include constants and other parameters used in the calculation of energy

use. All sources of information are documented in chapter 5 of this TSD. Additional

assumptions can be found for specific design options on their respective sheets (see section

A.2.8). The user can alter the numerical assumptions, which will immediately be reflected in the

tabulated cost­efficiency data in the “Results Summary” and “Advanced Inputs” worksheets.

A­9

 EE-2006-STD-0126 COMMENT 11.2
A.2.7 “Model” Worksheet

The “Model” worksheet contains a diagram of the energy consumption model used in the

engineering analysis. Details of this model are presented in chapter 5 of this TSD. Click any

part of the model and the calculation will be highlighted in the “Results Summary Calculation”

worksheet. Links are provided in the “Results Summary Calculation” worksheet to return to the

“Model” worksheet.

A.2.8 “Design Options” Worksheet

The “Design Options” worksheet lists the design options used in constructing costefficiency curves. Each design option is given an index number and a corresponding worksheet

(numbered 1.0 to 20.0) where detailed information is contained. Although the spreadsheet has

room to describe 20 design options, only 11 design options are currently employed in the model

(see chapter 4 and chapter 5 of this TSD). Each design option has two to three levels, ranked by

performance from lowest to highest. Each level is given a lettering code that is used to identify it

in the design options matrix for each equipment class. Links are provided to the individual

numbered worksheets, where detailed information is contained about the design options.

Additional information is also provided in chapter 5 of the TSD.

A.2.9 Supporting Worksheets

Several supporting worksheets are used in the spreadsheet in addition to the eight primary

content worksheets. Some of these contain constants or other information not necessary for user

interaction and are hidden from view. Those that are visible (the “CalcRS,” “CalcAI,” and

“Design Option Order” worksheets, and the numbered design options worksheets) are accessible

using the links provided on the primary worksheets when the information on the supporting

worksheets is relevant (see sections A.2.3, A.2.4, and A.2.7).

A­10

 EE-2006-STD-0126 COMMENT 11.2