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­2** 

**A.2.2 Introduction**  **Worksheet**

**...................................................................................... A­3** 

**A.2.3 Results**  **Worksheet**

**.............................................................................................. A­4** 

**A.2.4 Calculation**  **Worksheet........................................................................................ A­8** 

**A.2.5 Equipment**  **Classes Worksheet..........................................................................**  **A­10**

**A.2.6 Assumptions**  **Worksheet**

**................................................................................... A­10** 

**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­3** 

**Figure A.2.2**  **Screen shot**  **of the**  **Introduction worksheet**  **(not all**  **content shown).** 

**.................... A­4** 

**Figure A.2.3**  **Screen shot**  **of the**  **Results worksheet.** 

**.................................................................. A­5** 

**Figure A.2.4**  **Screen shot**  **of Design**  **Specifications worksheet.................................................. A­7** 

**Figure A.2.5**  **Screen shot**  **of the**  **Design Option**  **Order worksheet** 

**.............................................. A­8** 

**Figure A.2.6**  **Screen shot**  **of the**  **Calculation worksheet**  **(not all**  **content shown).......................**  **A­9**

**A­i A­ii** APPENDIX A. INSTRUCTIONS FOR USING THE ENGINEERING ANALYSIS

SPREADSHEET

A.1 INTRODUCTION

**This appendix**  **presents instructions**  **for using**  **the engineering**  **analysis spreadsheet**  **for**

**commercial refrigeration**  **equipment (CRE),**  **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_NOPR_Engineering_2007­11­** 

**29_Final.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

**– DOE’s**  **cost­efficiency results.** 

• Calculation

**– Detailed**  **calculations of**  **energy consumption**  **and cost.** 

• 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 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.** 

**A­1**A.2.1 Start Here Worksheet

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

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

**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 green**  **boxes with** 

**dashed gray**  **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.** 

**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­2**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 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 buttons**  **before each** 

**worksheet description**  **can be**  **used to**  **go to**  **those worksheets.** 

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

A.2.3 Results Worksheet

**The Results**  **worksheet provides**  **plots and**  **tables of**  **cost­efficiency results**  **for each**  **of the** 

**fifteen equipment**  **classes DOE**  **directly examined**  **(see Figure**  **A.2.3). The**  **top portion**  **of the** 

**worksheet contains**  **a button**  **that activates**  **a pop­up**  **window for**  **selecting an**  **equipment class,**  **as**

**well as**  **several user­selectable**  **options for**  **displaying different**  **information about**  **that class.**  **The**

**“Equipment Class**  **Selection” menu**  **appears when**  **the “Change**  **Equipment Class”**  **button is** 

**clicked, and**  **allows the**  **user to**  **select the**  **equipment family,**  **condensing unit**  **type, and**  **operating**

**temperature using**  **the appropriate**  **radio buttons.**  **c Displayed**  **below the**  **button are**  **the equipment** 

**c 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­4 class**  **description, a**  **picture of**  **the equipment,**  **and tabulated**  **cost­efficiency data**  **for both**  **the**

**analytically derived**  **DOE curve**  **and the**  **user­defined curve.**  **Below that**  **information are**  **tables**

**containing a**  **more detailed**  **breakdown of**  **the components**  **of the**  **refrigeration load**  **and daily** 

**energy consumption**  **for both**  **the DOE**  **and user­defined**  **curves. Beneath**  **those tables,**  **a cost** 

**efficiency plot**  **with the**  **appropriate curves**  **is displayed**  **on the**  **left, and,**  **where applicable,** 

**industry­supplied data**  **is shown**  **on the**  **right. d** 

Figure A.2.3 Screen shot of the Results 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**Figure A.2.3 Screen shot of Results worksheet (continued).

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

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

**Normalization” radio**  **buttons in**  **the top**  **right of**  **the screen**  **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 Cost”**  **radio buttons**  **in the**  **upper right**  **of**

**the screen**  **to select.**  **(Note that**  **the industry­supplied**  **data is**  **always displayed**  **in the**  **form of** 

**TDA­normalized MLP**  **and CDEC,**  **and is**  **not affected**  **by the**  **radio button**  **selection.)**

**To view**  **or modify**  **case design**  **specifications, press**  **the “View/Change**  **Design**

**Specifications” button**  **located in**  **the upper**  **section of**  **the worksheet.**  **This button**  **takes the**  **user**

**to the**  **“Design Specifications”**  **sheet (see**  **Figure A.2.4),**  **which contains**  **the DOE**  **specifications,**

**as well**  **as a**  **column to**  **input user­defined**  **specifications. Initially,**  **user design**  **specifications are** 

**identical to**  **DOE design**  **specifications. However,**  **any changes**  **made to**  **the spreadsheet**  **will be** 

**stored, meaning**  **that if**  **the spreadsheet**  **is saved,**  **any changes**  **to user­defined**  **design**

**specifications will**  **be saved**  **as well.**  **The user**  **may change**  **the user**  **design specification**  **values by** 

**entering new**  **values in**  **the “User”**  **column, and**  **then clicking**  **the “Save**  **and Return**  **to Results”** 

**A­6 button.**  **Clicking the**  **“Save and**  **Return to**  **Results” button**  **will return**  **the user**  **to the**  **results sheet** 

**and instantly**  **update the**  **user­defined results**  **tables with**  **the results**  **from the**  **user­input values.** 

**The “Set**  **to DOE**  **Default Specifications”**  **button will**  **reset the**  **user­defined values**  **to the**  **default**

**DOE specifications.**  **Press the**  **“Cancel” button**  **to cancel**  **any changes**  **and return**  **to the**  **Results**

**sheet.**

Figure A.2.4 Screen shot of Design Specifications worksheet

**The “View/Select**  **Design Options”**  **button in**  **the upper**  **middle portion**  **of the**  **page takes** 

**the user**  **to the**  **“Design Option**  **Order” sheet,**  **which contains**  **a design**  **options selection**  **matrix**

**(see Figure**  **A.2.5). 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**  **window containing**  **a design**  **options selection**  **matrix (similar**  **to the**  **one**

**seen in**  **Figure A.2.5)**  **with the**  **design option**  **order used**  **by DOE**  **will be**  **displayed. To**  **return the** 

**A­7 design**  **option matrix**  **to the**  **default DOE**  **values (where**  **applicable), press**  **the “Set**  **to DOE** 

**Design Option**  **Order” button.**  **When complete,**  **press the**  **“Save and**  **Return to**  **Results” button**  **to**

**return to**  **the Results**  **worksheet. Press**  **the “Cancel”**  **button to**  **cancel any**  **changes and**  **return to** 

**the Results**  **sheet.**

Figure A.2.5 Screen shot of the Design Option Order worksheet

A.2.4 Calculation Worksheet

**The Calculation**  **worksheet shows**  **detailed calculations**  **of energy**  **consumption and**  **cost**

**for the**  **current equipment**  **class (see**  **Figure A.2.6).**  **To select**  **an equipment**  **class, click**  **the**

**“Change Equipment**  **Class” button**  **in the**  **upper left**  **of the**  **worksheet, and**  **on the**  **pop up**  **menu**

**choose the**  **equipment family,**  **condensing unit**  **type, and**  **operating temperature**  **using the** 

**appropriate radio**  **buttons. The**  **Calculation worksheet**  **presents two**  **sets of**  **calculations: one** 

**using DOE's**  **default values**  **and one**  **using user­input**  **values. Switch**  **between the**  **two with**  **the**

**"Display" radio**  **buttons. The**  **cost­efficiency data**  **are calculated**  **using the**  **case design** 

**specifications, design**  **options, and**  **assumptions determined**  **by DOE.**  **Case design**  **specifications**

**are presented,**  **followed by**  **a matrix**  **of design**  **options used**  **in the**  **model and**  **energy consumption** 

**and cost**  **calculations. Each**  **row in**  **the calculation**  **tables represents**  **a different**  **step in**  **the**

**calculation of**  **cost or**  **energy consumption.**  **Each column**  **represents a**  **different design**  **option**

**level, from**  **baseline up**  **to the**  **maximum technology**  **level (max**  **tech). Columns**  **above the**  **max**

**A­8 tech**  **level repeat**  **the max**  **tech configuration**  **and are**  **grayed out.**  **View the**  **Model sheet**  **for a** 

**schematic representation**  **of the**  **model linked**  **to specific**  **calculations.**

Figure A.2.6 Screen shot of the Calculation worksheet (not all content shown).

**A­9**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 or**  **modify the**  **inputs for**  **any of**  **the 38**  **equipment classes,**  **follow the** 

**link for**  **that equipment**  **class under**  **the “Results**  **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. Non** 

**numerical 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**  **worksheet.**

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**  **to be**  **taken to**  **the corresponding**  **calculation in**  **the Calculation**  **worksheet. The** 

**user can**  **return to**  **the Model**  **worksheet using**  **the navigation**  **links that**  **are located**  **at the**  **top of** 

**each sheet.** 

A.2.8 Design Options Worksheet

**The Design**  **Options worksheet**  **lists the**  **design options**  **used in**  **constructing cost** 

**efficiency 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 13**  **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 numbered**  **design options** 

**A­10 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­11**