APPENDIX D. USER INSTRUCTIONS FOR LIFE CYCLE COST SPREADSHEET

TABLE OF CONTENTS

D.1 INTRODUCTION ............................................................................................................. D-1 D.2 USER INSTRUCTIONS FOR LCC SPREADSHEET ..................................................... D-1

LIST OF TABLES

Table D.2.1 Description of Tabs in LCC Spreadsheet ............................................................... D-2APPENDIX D. USER INSTRUCTIONS FOR LIFE CYCLE CO ST SPREADSHEET

D.1 INTRODUCTION

It is possible to examine and reproduce the de tailed results of the life-cycle cost (LCC) and payback periods (PBP) analyses for pack aged 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/co mmercial/packaged_ac_hp.html

D.2 USER INSTRUCTIONS FOR LCC SPREADSHEET

The spreadsheet is called “PTAC-PTHP_LCC _Final.xls” and it allows the user to perform LCC analyses of any of four equipmen t classes and two cooli ng capacities of standard size PTAC and PTHP equipment. To fully ex ecute the spreadsheet requires only Microsoft Excel® software, which is commercially available.

The spreadsheet posted on the DOE website repres ents 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:Table D.2.1 Description of Tabs in LCC Spreadsheet
CH Lifecycle vs Installed Cost Chart showing tradeoff between efficiency leve l and lifecycle cost for the current analysis options. CH Payback vs Installed Cost Chart showing tradeoff between efficiency level and payback period in years.

Summary Contains the input selections and a summary ta ble 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, Large Chain Contains estimates of base annual dollar expenditu res 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 da ta for systems at each efficiency level. Also contains data on 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 classe s 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 functi ons 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 Ener gy Price Detail Contains data on the average commercial electricity prices by state in 2007

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 func tion 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.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 Crysta l Ball using Excel. If you are not using Crystal Ball, simply open the file in Excel. At the botto m, 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 spread sheet. Choices can be selected from the various inputs listed under "User Options" heading.

Under the "User Options" heading select choi ces from the selection buttons and boxes for the following: (1) Uncertainty option (2) elect ricity price projecti on, (3) start year, (4) equipment class and capacity, (5) efficien cy 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-reduci ng measures such as Federal tax credits.

A new discount rate or lifetime can also be en tered if a value other than the default value or default distribution is want ed; however, this overwrites the existing formula. As a result, the DOE does not recommend saving the spreadsheet after the code is changed. . The default discount rates can be change d by going to the Discount Rate tab and changing the default values in Cells C4 thr ough C7. A different lifetime can be selected by going to the Lifetime tab and entering a differe nt value in the gree n 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 appropr iate button or selecting the appropriate input from the input box. If the Crystal Ball Option is select ed, red panels appear on the Summary page advising the user to not use the data that a ppear 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 th e 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 basi c 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 Rese t (also from the Run menu) before Run can be selected again. Once Crystal Ball has co mpleted its run sequence it will produce a series of distributions. Using the menu bars on the distribution result s it is possible toD-4 obtain further statistical information. The tim e 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 choos e from the following choices: a) Monte Carlo b) Latin Hypercube (recommended) c) Initial seed choices a nd whether you want it to be constant between runs d) Select number of Monte Ca rlo Trials (we suggest 1,000). 4) To run the simulation, follow the followi ng sequence (on the Crys tal 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 s ee the distribution char ts 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 defi ned as Forecast cells. The frequency charts display the results of the simulati ons, 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 sele ction on the Crystal Ball toolbar can be used to specify whether you want cu mulative or frequency plots shown. 3) To calculate the probability that LCC sa vings 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 like lihood 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, sele ct 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 inform ation 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.