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

**A.2.5 “Equipment**  **Classes” Worksheet** 

**........................................................................ A­9** 

**A.2.6 “Key**  **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** 

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

**the U.S.**  **Department of**  **Energy (DOE)**  **made public**  **on its**  **website for**  **stakeholder review**  **at**

**http://www.eere.energy.gov/buildings/appliance_standards/commercial/beverage_machines.**

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

**Technical Support**  **Document (TSD)**  **for more**  **information on**  **this approach.)**  **This spreadsheet** 

**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 in**  **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**

• Key Assumptions **–List of**  **numerical and**  **non­numerical assumptions** 

• Model **–Description of**  **the energy­consumption**  **model**

• Design Options **–Description of**  **design options** 

**The eight**  **primary worksheet**  **tabs are**  **colored gray,**  **while the**  **supporting worksheet**  **tabs**

**(containing calculations,**  **stored values,**  **and other**  **information) are**  **colored black.** 

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

**a Upon**  **opening the**  **spreadsheet, the**  **user may**  **be asked**  **to enable**  **macros. Several**  **macros are**  **included 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 lose**  **significant**

**functionality. 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,**  **although the**  **tabs at**  **the bottom**  **of the**  **workbook can**  **be used**  **to**

**navigate as**  **well.**

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

**refrigerated beverage**  **vending machine**  **(BVM) rulemaking**  **and a**  **brief description**  **of each**  **of the** 

**other primary**  **content worksheets**  **(see Figure**  **A.2.2). A**  **link is**  **also provided**  **to the**  **online TSD** 

**for reference.** 

**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 sizes**  **and**

**equipment classes**  **DOE 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. Directly**  **beneath the**  **tabulated cost­efficiency**  **data is**  **the “Refresh** 

**Calculation” button;**  **due to**  **the iterative**  **nature of**  **cost­efficiency calculation,**  **it may**  **be necessary** 

**to refresh**  **the values**  **using this**  **button if**  **an error**  **occurs.**

**c Equipment**  **class designations**  **consist of**  **a combination**  **(in sequential**  **order separated**  **by periods)**  **of an**  **equipment**

**family code**  **(A=Type A**  **and B=Type**  **B), a**  **test temperature**  **code (75** 

**= 75**  ° **F /45%RH**  **and 90** 

**= 90°F/65%RH),**  **and a** 

**vending capacity**  **code (S** 

**= Small,**  **M= Medium,**  **and L** 

**= Large).**  **For example,**  **“A75S” refers**  **to “Type**  **A,**

**75** ° **F/45%RH, small**  **capacity.” See**  **chapter 3,**  **Market and**  **Technology Assessment,**  **for a**  **more detailed**  **explanation**

**of the**  **equipment class**  **terminology.**

**A­3**Figure A.2.3 Screen Shot of the “Results Summary” Worksheet.

**Using the**  **“Normalization” radio**  **buttons on**  **the left,**  **the tabulated**  **cost­efficiency data**  **can**

**be displayed**  **without normalization**  **or be**  **normalized by**  **refrigerated volume**  **(V). Using**  **the**

**“Manufacturer Cost”**  **radio buttons**  **on the**  **left, the**  **data can**  **also be**  **displayed as**  **manufacturer**

**incremental cost**  **(INC), manufacturer**  **production cost**  **(MPC), manufacturer**  **selling price**  **(MSP),**

**or manufacturer**  **list price**  **(MLP).**

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

**A­4 normalization**  **and manufacturer**  **cost. To**  **view case**  **design specifications,**  **press the**  **“View**

**Design Specifications”**  **button. To**  **view design**  **options for**  **the baseline**  **and all**  **levels above**  **the**

**baseline, press**  **the “View**  **Design Options”**  **button on**  **the left**  **(see Figure**  **A.2.4). Changes**  **from**

**one level**  **to the**  **next are**  **highlighted in**  **yellow. Design**  **options not**  **applicable to**  **the current** 

**equipment class**  **are hashed**  **in gray.**  **To view**  **a breakdown**  **of the**  **heat and**  **electrical loads**  **based**

**on Design**  **Option Level,**  **click "View**  **Heat and**  **Electrical Loads."**  **A pop­up**  **window will**  **display**

**two pie**  **charts graphically**  **representing the**  **components of**  **heat load**  **and daily**  **energy use.** 

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,**  **or select**  **the**

**“Analysis” tab.**  **The “Results**  **Summary” worksheet**  **displays the**  **case design**  **specifications,**

**design options,**  **and the**  **energy consumption**  **calculation for**  **each design**  **option level.**  **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.**

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,**  **size, and**  **operating temperature**  **using the** 

**appropriate radio**  **buttons. The**  **equipment class**  **description is**  **displayed, along**  **with two**  **sets of** 

**tabulated cost­efficiency**  **data. The**  **first set**  **is identical**  **to the**  **data displayed**  **in the**  **“Results**

**A­5 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**  **Cost” 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.**  **The “View**  **Heat and**  **Electrical Loads**  **(AI)” button** 

**provides the**  **same function**  **as in**  **the “Results**  **Summary” worksheet,**  **except it**  **displays data**  **for**

**the user**  **inputs. The**  **“Refresh Calculation**  **(On Error)”**  **button similarly**  **has the**  **same effect**  **as in** 

**the “Results**  **Summary” worksheet.** 

**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;**  **the second**  **column**

**contains user**  **values. Upon**  **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 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.** 

**To the**  **left of**  **the case**  **design specifications**  **is the**  **“View/Select Design**  **Options” button.** 

**Pressing this**  **button takes**  **the user**  **to the**  **“Design Option**  **Order” worksheet,**  **which contains**  **a**

**design options**  **selection matrix**  **(see Figure**  **A.2.6). Use**  **this 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**  **in 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**  **minimum technology,**  **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**  **order to**  **the DOE**  **version,**

**press the**  **“Default Order”**  **button. When**  **complete, press**  **the “Complete”**  **button to**  **return to**  **the**

**“Advanced Inputs”**  **worksheet. Press**  **the “Cancel”**  **button to**  **cancel any**  **changes.**

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

**The user**  **input calculation**  **is identical**  **to the**  **calculation used**  **in the**  **“Results Summary”** 

**worksheet, but**  **it is**  **conducted in**  **a separate**  **worksheet. To**  **view the**  **spreadsheet calculation**  **of**

**this data,**  **follow the**  **“View Advanced**  **Inputs Summary**  **Calculation” link**  **in the**  **yellow**

**information box,**  **or select**  **the “Analysis_AI”**  **tab. The**  **“Advanced Inputs”**  **worksheet displays** 

**the case**  **design specifications,**  **design options,**  **and the**  **energy consumption**  **calculation for**  **each**

**design option**  **level for**  **the user**  **inputs. 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**  **12 equipment**  **classes analyzed**  **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**  **sizes or**  **types equipment**  **classes analyzed,** 

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

**results or**  **modify inputs**  **for any**  **of the**  **equipment classes,**  **follow the**  **link for**  **that equipment**  **class**

**under the**  **“Advanced Inputs**  **Link” column.** 

A.2.6 “Key Assumptions” Worksheet

**The “Key**  **Assumptions” worksheet**  **lists numerical**  **and non­numerical**  **assumptions 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 displayed**  **in the**  **“Results Summary”**  **worksheet.**

**Links are**  **provided in**  **the “Results**  **Summary” 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**  **to 20)**  **where detailed**  **information is**  **contained. Although**  **the spreadsheet**  **has room** 

**to describe**  **20 design**  **options, only**  **12 design**  **options are**  **currently employed**  **in the**  **model (see** 

**chapter 4**  **and chapter**  **5 of**  **this TSD).**  **Each design**  **option has**  **two to**  **four 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

**In addition**  **to the**  **eight primary**  **content worksheets,**  **several supporting**  **worksheets are** 

**used in**  **the spreadsheet.**  **Some of**  **these contain**  **constants or**  **other information**  **not necessary**  **for**

**user interaction**  **and are**  **hidden from**  **view. Those**  **that are**  **visible (the**  **“Analysis,”**

**“Analysis_AI,” “Design**  **Option Order,”**  **and numbered**  **design options**  **worksheets) are**  **indicated**

**by black**  **worksheet tabs**  **and are**  **accessible by**  **clicking on**  **the tab.**  **Links are**  **also 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**