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 Worksheet .............................................................................................. A-3 

A.2.4 Calculation Worksheet ........................................................................................ A-7 

A.2.5 Equipment Classes Worksheet ............................................................................ A-8 

A.2.6 Assumptions Worksheet ..................................................................................... A-8 

A.2.7 Model Worksheet ................................................................................................ A-9 

A.2.8 Design Options Worksheet ................................................................................. A-9 

A.2.9 Supporting Worksheets ....................................................................................... A-9 

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 Worksheet. ................................................................. A-4 

Figure A.2.4 Screen Shot of Design Specifications Worksheet ................................................ A-6 

Figure A.2.5 Screen Shot of the Design Option Order Worksheet ............................................ A-7 

Figure A.2.6 Screen Shot of the Calculation Worksheet (not all content shown). .................... A-8 

A-i 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
 

APPENDIX A. INSTRUCTIONS FOR USING THE ENGINEERING ANALYSIS 

SPREADSHEET
 

A.1 INTRODUCTION 

This appendix presents instructions for using the engineering analysis spreadsheet for 

beverage vending machines. 

The engineering analysis spreadsheet details the assumptions, methodology, and 

calculation of cost-efficiency curves using a design-option approach (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.2.1 Start Here Worksheet 

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

Figure A.2.1). 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. 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. 

A-1 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
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 beverage 

vending machine rulemaking and a brief description of each of the other primary content 

worksheets 

A-2 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
(
 

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 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
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 

equipment classes DOE examined ( 

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 class 

and machine size using the appropriate option buttons. Displayed below the button are the 

equipment class description and tabulated cost-efficiency data for both the analytically derived 

DOE curve and the user-defined curve. Below that information is a cost-efficiency plot with the 

appropriate curves. 

A-4 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
Figure A.2.3 Screen Shot of the Results Worksheet 

A-5
 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
Figure A.2.3 Screen Shot of Results Worksheet (continued) 

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

normalized by refrigerated volume (V). Use the “Data Normalization” option buttons in the top 

right of the screen to select. 

To view or modify equipment 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 (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” 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. 

A-6 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
Figure A.2.4 Screen Shot of Design Specifications Worksheet 

The “View/Change 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 ( 

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

A-7 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
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 ( 

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 class and 

machine size using the appropriate option 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” option buttons. The cost-efficiency data are calculated using the 

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

A-8 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
Figure A.2.6 Screen Shot of the Calculation Worksheet (not all content shown) 

A.2.5 Equipment Classes Worksheet 

The Equipment Classes worksheet contains information on the equipment classes covered 

by this rulemaking. Details of equipment class organization are presented in chapter 3 of this 

TSD. To view the results or modify the inputs for either equipment class, 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. 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 worksheet. 

A-9 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74
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 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 13 design options are currently employed in the model 

(see chapters 4 and 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 

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 

BVM_FINAL_FR_TSD_AppendixA_08-14-09.doc 

EERE-2006-STD-0125 COMMENT 74