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

EERE-2006-STD-0125 COMMENT 22
A­ii

EERE-2006-STD-0125 COMMENT 22
            

                     

                             

               

                          

                        

                           

                          

                          

                   

   

                     

               

     

         

         

           

         

       

                         

                 

     

                           

                          

                                                

                                        

                                    

                                 

                      

                                        

                                        

  

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 standalone, 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

EERE-2006-STD-0125 COMMENT 22
                            

                             

                            

                                 

     

                  

   

               

                           

                                

     

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

EERE-2006-STD-0125 COMMENT 22
                           

     

                         

                            

                         

                            

                              

                      

                           

                     

                                                

                                 

                                         

                                      

                              

       

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

EERE-2006-STD-0125 COMMENT 22
                     

                         

                          

                           

                     

         

                           

                                  

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

EERE-2006-STD-0125 COMMENT 22
                        

                            

                              

                                

                                  

                              

                           

                      

                   

                            

                             

                    

                            

                             

                            

                              

     

     

                       

                              

                       

                            

                            

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

EERE-2006-STD-0125 COMMENT 22
                         

                          

                            

                         

                          

                                

                             

                                

       

                         

                              

                          

                               

                                

                          

                            

                                

               

                            

                             

                              

                          

                             

                            

                             

                                

                          

                       

                            

                             

                                  

                              

                      

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

EERE-2006-STD-0125 COMMENT 22
Figure                   A.2.5 Screen Shot of the “Advanced Inputs” Worksheet.
 

A­7

EERE-2006-STD-0125 COMMENT 22
                     

                       

                            

                           

                              

                         

                        

                         

                                    

                          

                            

                     

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

EERE-2006-STD-0125 COMMENT 22
                    

     

                   

                           

                            

                                

                              

                                 

           

   

                   

                    

                      

                              

                              

                            

                         

            

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

EERE-2006-STD-0125 COMMENT 22
   

                           

                                 

                              

                           

     

                     

                            

                            

                               

                                    

                                    

                              

                      

                     

                       

                              

                          

                     

                                    

                         

       

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

EERE-2006-STD-0125 COMMENT 22