APPENDIX D. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

TABLE OF CONTENTS

**D.1 INTRODUCTION** 

**............................................................................................................. D­2** 

**D.2 USER**  **INSTRUCTIONS FOR**  **LCC SPREADSHEET** 

**..................................................... D­2** 

LIST OF TABLES

**Table D.2.1**  **Description of**  **Tabs in**  **LCC Spreadsheet...............................................................**  **D­3**

**D­1**APPENDIX D. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

D.1 INTRODUCTION

**It is**  **possible to**  **examine and**  **reproduce the**  **detailed results**  **of the**  **life­cycle cost**  **(LCC)**

**and payback**  **periods (PBP)**  **analyses for**  **packaged terminal**  **air conditioners**  **(PTACs) and** 

**packaged terminal**  **heat pump**  **(PTHPs) equipment**  **using a**  **Microsoft Excel®**  **spreadsheet**

**available on**  **the U.S.**  **Department of**  **Energy (DOE)’s**  **website at:** 

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

D.2 USER INSTRUCTIONS FOR LCC SPREADSHEET

**The spreadsheet**  **is called**  **“PTAC­PTHP_LCC_NOPR_03­14­2008.xls” and**  **it allows**  **the**

**user to**  **perform LCC**  **analyses of**  **any of**  **four equipment**  **classes and**  **two cooling**  **capacities of** 

**standard size**  **PTAC and**  **PTHP 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:** 

**D­2**Table D.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 sele**  **ctions made**  **in a**  **given run**  **of the** 

**model.**

Energy $ Save,

Large Chain **Contains estimates**  **of base**  **annua l**  **dollar expenditures**  **in Large**  **Chain hotel**  **buildings for**  **energy**

**for the**  **various system**  **options, locations.** 

Energy $ Save,

Independent Hotel **Contains estimates**  **of base**  **annual dollar**  **expenditures in**  **Independent Hotel**  **buildings for** 

**energy for**  **the various**  **system options,**  **locations**

Energy $ Save,

Health Care **Contains estimates**  **of base**  **annual dollar**  **expenditures in**  **Health Care**  **buildings (nursing**  **homes,**

**assisted living,**  **and extended**  **care facilities)**  **for energy**  **for the**  **various system**  **options,**

**locations.**

Energy $ Save,

Office **Contains estimates**  **of base**  **annual dollar**  **expenditures in**  **Office buildings**  **(small Office** 

**buildings, frequently**  **occupied by**  **medical and**  **dental offices)**  **for energy**  **for the**  **various system** 

**options, locations.** 

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

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

Contractor

Markup Index **Contains detailed**  **data on**  **contractor markups**  **by State**  **used in**  **the model** 

Bldg Energy,

Large Chain **Contains data**  **on energy**  **use in**  **standardized PTAC**  **and PTHP**  **units in**  **Large Chain**  **Hotel**

**buildings for**  **the various**  **efficiency classes**  **by Efficiency**  **Level, State** 

Bldg Energy,

Independent Hotel **Contains data**  **on energy**  **use in**  **standardized PTAC**  **and PTHP**  **units in**  **Independent Hotel** 

**buildings for**  **the various**  **efficiency classes**  **by Efficiency**  **Level, State** 

Bldg Energy,

Health Care **Contains data**  **on energy**  **use in**  **standardized PTAC**  **and PTHP**  **units in**  **Health Care**  **buildings**

**for the**  **various efficiency**  **classes by**  **Efficiency Level,**  **State**

Bldg Energy,

Office **Contains data**  **on energy**  **use in**  **standardized PTAC**  **and PTHP**  **units in**  **Office buildings**  **for the** 

**various efficiency**  **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**  **buildings**

**(businesses) that**  **use PTACs**  **and PTHPs,**  **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**  **electricity prices**  **by state**  **in 2006** 

Installation Cost

by State **Contains detailed**  **calculations to**  **determine multipliers**  **to vary**  **the installation**  **cost from**  **state**

**data.**

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**  **PTAC and**  **PTHP 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.** 

**D­3 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.**

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

**D­4 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)**

**b)**

**c)**

**d) Monte**  **Carlo**

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

**Initial seed**  **choices and**  **whether you**  **want it**  **to be**  **constant between**  **runs**

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

**D­5**