APPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

TABLE OF CONTENTS

**E.1 INTRODUCTION**  **...........................................................................................................E­1**

**E.2 USER**  **INSTRUCTIONS FOR**  **LCC SPREADSHEET**  **...................................................E­1**

LIST OF TABLES

**Table E.2.1.**  **Description of**  **Tabs in**  **LCC Spreadsheet...............................................................E­1** 

**E­i E­ii** APPENDIX E. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

E.1 INTRODUCTION

**It is**  **possible to**  **examine and**  **reproduce the**  **detailed results**  **of the**  **LCC and**  **PBP analyses** 

**for beverage**  **vending machines**  **(BVMs) using**  **a Microsoft**  **Excel® spreadsheet**  **available on**  **the**

**U.S. Department**  **of Energy’s**  **website at:** 

**www.eere.energy.gov/buildings/appliance_standards/commercial/beverage_machines.html**

**.**

E.2 USER INSTRUCTIONS FOR LCC SPREADSHEET

**The spreadsheet**  **is called**  **“BVM_LCC_5CB_09­12­2007.xls” and**  **it allows**  **the user**  **to**

**perform LCC**  **analyses of**  **any of**  **two equipment**  **classes and**  **three capacities**  **of BVM**  **equipment.**

**To fully**  **execute the**  **spreadsheet requires**  **only Microsoft**  **Excel® software,**  **which is** 

**commercially available.** 

**The spreadsheet**  **posted on**  **the DOE**  **website represents**  **the latest**  **version of**  **the applicable** 

**model and**  **has been**  **developed and**  **tested with**  **Microsoft Excel®**  **2003. The**  **LCC spreadsheet**  **or**

**workbook consists**  **of the**  **following worksheets**  **as described**  **in Table**  **E.2.1**

Table E.2.1

**. Description**  **of Tabs**  **in LCC**  **Spreadsheet**

CH Lifecycle vs. Installed

Cost **Chart showing**  **tradeoff between**  **efficiency level**  **(Installed Cost)**  **and lifecycle** 

**cost for**  **the current**  **analysis options.** 

CH Payback vs. Installed

Cost **Chart showing**  **tradeoff between**  **efficiency level**  **(Installed Cost)**  **and payback** 

**period in**  **years.**

Summary **Contains the**  **input selections**  **and a**  **summary table**  **of energy**  **use, operating**  **costs,**

**LCC and**  **Payback.**

Energy $ Expend **Contains Summary**  **calculations that**  **translate energy**  **savings in**  **individual**

**building types**  **and equipment**  **classes into**  **the values**  **needed for**  **the particular** 

**selections made**  **in a**  **given run**  **of the**  **model.**

Energy $ Save, Manufacturer **Contains estimates**  **of base**  **annual dollar**  **expenditures for**  **electricity for**  **the**

**various BVM**  **system options,**  **locations when**  **the owner**  **of the**  **site is**  **a**

**manufacturing business.** 

Energy $ Save, OfficeHealth **Contains estimates**  **of base**  **annual dollar**  **expenditures for**  **electricity for**  **the**

**various BVM**  **system options,**  **locations when**  **the owner**  **of the**  **site is**  **an office** 

**oriented service**  **business or**  **a health**  **care facility** 

Energy $ Save, Retail **Contains estimates**  **of base**  **annual dollar**  **expenditures for**  **electricity for**  **the**

**various BVM**  **system options,**  **locations when**  **the owner**  **of the**  **site is**  **a retail** 

**store.**

Energy $ Save, School **Contains estimates**  **of base**  **annual dollar**  **expenditures for**  **electricity for**  **the**

**various BVM**  **system options,**  **locations when**  **the owner**  **of the**  **site is**  **a school,** 

**college or**  **university or**  **other state­run**  **facility such**  **as a**  **prison**

Energy $ Save, Military **Contains estimates**  **of base**  **annual dollar**  **expenditures for**  **electricity for**  **the**

**various BVM**  **system options,**  **locations when**  **the owner**  **of the**  **site is**  **a military** 

**facility.**

Energy $ Save, Other **Contains estimates**  **of base**  **annual dollar**  **expenditures for**  **electricity for**  **the**

**various BVM**  **system options,**  **locations when**  **the owner**  **of the**  **site is**  **a business** 

**in the**  **"other" category**  **(mostly warehouses**  **and entertainment**  **venues)**

Engineering **Contains the**  **manufacturer price**  **data for**  **systems at**  **each efficiency**  **level. Also** 

**contains data**  **on unit**  **weight, energy**  **consumption in**  **various configurations,**  **and**

**summarizes data**  **on cost**  **elements other**  **than energy**  **by efficiency**  **level.**

**E­1**Table E.2.1

**. (contd)** 

Markups and Market **Contains the**  **distributor, contractor,**  **and national**  **accounts markups,**  **the sales**  **tax,**

**the installation**  **price, and**  **the repair**  **and maintenance**  **costs.**

Sales Tax **Contains detailed**  **calculations to**  **determine weighted**  **average sales**  **tax rates** 

**from state**  **data. In**  **addition, contains**  **calculation cells**  **for several**  **of the**  **variables**

**that vary**  **by state**  **when the**  **model is**  **run in**  **uncertainty mode**  **(Monte Carlo** 

**analysis) using**  **Crystal Ball.** 

Install and Maint Cost Index **Contains detailed**  **data on**  **relative installation**  **and maintenance**  **cost indices**  **by**

**State used**  **in the**  **model**

Energy $ Save, Manufacturer **Contains data**  **on energy**  **use in**  **standardized BVM**  **units in**  **Manufacturing**

**buildings for**  **the various**  **equipment classes**  **by efficiency**  **level, State** 

Energy $ Save, OfficeHealth **Contains data**  **on energy**  **use in**  **standardized BVM**  **units in**  **office and**  **health care** 

**buildings for**  **the various**  **equipment classes**  **by efficiency**  **level, State** 

Energy $ Save, Retail **Contains data**  **on energy**  **use in**  **standardized BVM**  **units in**  **retail buildings**  **for**

**the various**  **equipment classes**  **by efficiency**  **level, State** 

Energy $ Save, School **Contains data**  **on energy**  **use in**  **standardized BVM**  **units in**  **school, college**  **and**

**prison buildings**  **for the**  **various equipment**  **classes by**  **efficiency level,**  **State**

Energy $ Save, Military **Contains data**  **on energy**  **use in**  **standardized BVM**  **units in**  **military facilities**  **for**

**the various**  **equipment classes**  **by efficiency**  **level, State** 

Energy $ Save, Other **Contains data**  **on energy**  **use in**  **standardized BVM**  **units in**  **"other" buildings** 

**(warehouses and**  **entertainment venues)**  **for the**  **various equipment**  **classes by** 

**efficiency level,**  **State**

Energy Price Projections **Contains current**  **energy prices**  **by State,**  **and projections**  **of future**  **energy prices** 

**from the**  **Annual Energy**  **Outlook. Also**  **performs functions**  **to translate**  **average**

**commercial electricity**  **prices into**  **prices by**  **state and**  **building type** 

Electricity Price Ratios

CBECS **Contains the**  **national average**  **electricity prices**  **experienced in**  **the four**  **types of** 

**commercial buildings**  **(businesses) and**  **two types**  **of industrial**  **facilities that**  **use**

**BVMs, compared**  **with average**  **commercial electricity**  **prices, from**  **microdata in** 

**the EIA**  **Commercial Buildings**  **Energy Consumption**  **Survey (CBECS)**  **2003**

State Energy Price Detail **Contains data**  **on the**  **average commercial**  **and industrial**  **electricity prices**  **by state** 

**in 2006** 

Discount Rate **Contains data**  **from which**  **an average**  **discount rate**  **and a**  **distribution of**  **discount**

**rates are**  **determined.**

Lifetime **Contains the**  **calculations for**  **the survival**  **function for**  **BVM units**  **lifetime in** 

**years.**

Labels **This is**  **used as**  **an interface**  **between user**  **inputs and**  **the rest**  **of the**  **worksheets**

**­­**

**do not**  **modify this**  **sheet.**

**Basic instructions**  **for operating**  **the LCC**  **spreadsheets are**  **as follows:** 

**To execute**  **the spreadsheets**  **fully you**  **will need**  **Microsoft Excel**  **and Crystal**  **Ball, Version**  **7.0 or** 

**later. The**  **spreadsheet can**  **be run**  **in sensitivity**  **mode using**  **only Excel.** 

**1) Once**  **you have**  **downloaded the**  **LCC file**  **from the**  **Web, first**  **open Crystal**  **Ball and**  **then**

**open the**  **file inside**  **of Crystal**  **Ball using**  **Excel. At**  **the bottom,**  **click on**  **the tab**  **for sheet** 

**Summary.**

**2) Use**  **Excel’s commands**  **at the**  **top View/Zoom**  **to change**  **the size**  **of the**  **display to**  **make it** 

**fit your**  **monitor.**

**E­2 3)**  **The user**  **interacts with**  **the spreadsheet**  **by clicking**  **choices or**  **entering data**  **using the** 

**graphical interface**  **that comes**  **with the**  **spreadsheet. Choices**  **can be**  **selected from**  **the**

**various inputs**  **listed under**  **"User Options"**  **heading.**

**Under the**  **"User Options"**  **heading select**  **choices from**  **the selection**  **buttons and**  **boxes for** 

**the following:**  **(1) Uncertainty**  **option (2)**  **electricity price**  **projection, (3)**  **start year,** 

**(4) equipment**  **class and**  **capacity, (5)**  **efficiency level,**  **(6) region,**  **(7) business**  **(and**

**building) type,**  **(8) installation**  **option, (9)**  **shipment weights**  **option, (7)**  **installation cost** 

**projection, and**  **(8) repair**  **cost projection,**  **(9) level**  **of any**  **cost­reducing measures**  **such as** 

**Federal tax**  **credits.**

**A new**  **discount rate**  **or lifetime**  **can also**  **be entered**  **if a**  **value other**  **than the**  **default value** 

**or default**  **distribution is**  **wanted; however,**  **this overwrites**  **the existing**  **formula. As**  **a**

**result, the**  **Department does**  **not recommend**  **saving the**  **spreadsheet after**  **the code**  **is**

**changed.**

**. The**  **default discount**  **rates can**  **be changed**  **by going**  **to the**  **Discount Rate**  **tab**

**and changing**  **the default**  **values in**  **Cells C4**  **through C7.**  **A different**  **lifetime can**  **be**

**selected by**  **going to**  **the Lifetime**  **tab and**  **entering a**  **different value**  **in the**  **green box**  **at**

**cell D4**  **and Cell**  **I3**

**4) To**  **change inputs**  **listed under**  **"User Options"**  **either select**  **the input**  **you wish**  **to change** 

**by either**  **clicking on**  **the appropriate**  **button or**  **selecting the**  **appropriate input**  **from the** 

**input box.**  **If the**  **Crystal Ball**  **Option is**  **selected, red**  **panels appear**  **on the**  **Summary page** 

**advising the**  **user to**  **not use**  **the data**  **that appear**  **in the**  **results section**  **of the**  **Summary**

**page, but**  **instead to**  **use the**  **Crystal Ball**  **results.**

**5) Crystal**  **Ball results**  **may be**  **saved by**  **using the**  **options in**  **Create Report**  **and Extract**  **Data**

**functions on**  **the Control**  **Box window.** 

**To run**  **a Crystal**  **Ball simulation,**  **do the**  **following.**

**To produce**  **Monte Carlo**  **results using**  **Crystal Ball,**  **you need**  **simply select**  **Run from**  **the**

**Run menu**  **(on the**  **menu bar).**  **To make**  **basic changes**  **in the**  **run sequence,**  **including**

**altering the**  **number of**  **trials, select**  **Run Preferences**  **from the**  **Run menu.**  **After each** 

**simulation run,**  **the user**  **needs to**  **select Reset**  **(also from**  **the Run**  **menu) before**  **Run can** 

**be selected**  **again. Once**  **Crystal Ball**  **has completed**  **its run**  **sequence it**  **will produce**  **a**

**series of**  **distributions. Using**  **the menu**  **bars on**  **the distribution**  **results it**  **is possible**  **to**

**obtain further**  **statistical information.**  **The time**  **taken to**  **complete a**  **run sequence**  **can be** 

**reduced by**  **minimizing the**  **Crystal Ball**  **window in**  **Excel.**

**A step**  **by step**  **summary of**  **the procedure,**  **for running**  **a distribution**  **analysis, is**  **outlined**

**below:**

**1) Find**  **the Crystal**  **Ball toolbar**  **(at top**  **of screen)** 

**2) Click**  **on RUN** 

**3) Select**  **Preference and**  **choose from**  **the following**  **choices:**

**a) Monte**  **Carlo**

**b) Latin**  **Hypercube (recommended)** 

**E­3 c)**  **Initial seed**  **choices and**  **whether you**  **want it**  **to be**  **constant between**  **runs**

**d) Select**  **number of**  **Monte Carlo**  **Trials (we**  **suggest 1,000).** 

**4) To**  **run the**  **simulation, follow**  **the following**  **sequence (on**  **the Crystal**  **Ball toolbar)** 

**RUN**

**RESET**

**RUN**

**5) Now**  **wait until**  **the program**  **informs you**  **that the**  **simulation is**  **completed.**

**Depending on**  **the speed**  **of your**  **computer, this**  **takes only**  **a few**  **minutes**

**(typically, anywhere**  **from 1**  **minute to**  **5­10 minutes**  **on a**  **Pentium 4**  **machine,**

**depending on**  **whether you**  **suppress the**  **figures for**  **speed or**  **want to**  **watch the** 

**figures update)** 

**Crystal Ball**  **Results**

**1) After**  **the simulation**  **has finished,**  **to see**  **the distribution**  **charts generated,**  **click on** 

**the Windows**  **tab bar**  **that is**  **labeled Crystal**  **Ball.**

**2) Several**  **of the**  **variables of**  **interest, including**  **life­cycle cost,**  **the life­cycle**  **cost**

**savings, and**  **the payback**  **periods are**  **defined as**  **Forecast cells.**  **The frequency** 

**charts display**  **the results**  **of the**  **simulations, or**  **trials, performed**  **by Crystal**  **Ball.**

**Click on**  **any chart**  **to bring**  **it into**  **view. The**  **charts show**  **the low**  **and high** 

**endpoints of**  **the forecasts.**  **The View**  **selection on**  **the Crystal**  **Ball toolbar**  **can be** 

**used to**  **specify whether**  **you want**  **cumulative or**  **frequency plots**  **shown.**

**3) To**  **calculate the**  **probability of**  **that LCC**  **savings will**  **occur, either**  **type 0**  **in the** 

**box by**  **the right**  **arrow, or**  **move the**  **arrow key**  **with the**  **cursor to**  **0 on**  **the scale.** 

**The value**  **in the**  **Certainty box**  **shows the**  **likelihood that**  **the LCC**  **savings will** 

**occur. To**  **calculate the**  **certainty of**  **payback period**  **being below**  **a certain**  **number**

**of years,**  **choose that**  **value as**  **the high**  **endpoint.**

**4) To**  **generate a**  **printout report,**  **select Create**  **Report from**  **the Run**  **menu. The** 

**toolbar choice**  **of Forecast**  **Windows allows**  **you to**  **select the**  **charts and**  **statistics**

**you are**  **interested in.**  **For further**  **information on**  **Crystal Ball**  **outputs, please**  **refer**

**to Understanding**  **the Forecast**  **Chart in**  **the Crystal**  **Ball manual.**  **Crystal Ball** 

**results also**  **may be**  **saved to**  **a separate**  **Excel spreadsheet**  **by using**  **the Extract** 

**Data function**  **under Analyze**  **on the**  **Control Panel**  **window.**

**E­4**