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 A­ii** 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 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**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** 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 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**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**  **Cost**

**Efficiency 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**Figure A.2.5 Screen shot of the “Advanced Inputs” worksheet.

**A­7**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**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. 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**  **Summary” and**  **“Advanced Inputs”**  **worksheets.**

**A­9**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 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 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**