8A-i APPENDIX 8A. U SER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS

TABLE OF CONTENTS

8A.1 INTRODUCTION .................................................................................................. 8A-1

8A.2 LCC AND PBP WORKBOOK .............................................................................. 8A-1

8A.3 BASIC INSTRUCTIONS....................................................................................... 8A-28A-1 APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS

8A.1 INTRODUCTION

It is possible to examine and reproduce the de tailed results of the life-cycle cost (LCC) and payback period (PBP) analyses using Microsof t Excel® spreadsheets available on the U.S. Department of Energy (DOE) website at: http://www.eere.energy.gov/build ings/appliance_standards/ . Running the spreadsheet sample calculations requires Microsoft Ex cel. Additionally, Crystal Ball® software is required to run the Monte Carlo simulations. Both applications are commercially available. Crystal Ball is

available at http://www.decisioneering.com

.

The spreadsheet posted on the DOE website repr esents the latest version of the LCC and PBP analysis model. It has been tested w ith Microsoft Excel 2007 and Crystal Ball 11.1.1.1.

8A.2 LCC AND PBP W ORKBOOK

LCC and PBP spreadsheet or workbook consists of the following worksheets:

LCC Summary Contains the input selections and a summary table of energy use, operating costs, LCC, and PBP for each selection. This worksheet also works as an interface between user inputs and the rest of the worksheets – do not modify this sheet.

Import Parameters Contains the engineering analys is as well as all parameters imported into the LCC summary page. Tables of results are only relevant for the lamp type and/or product class selected by the user.

Plots Contains plots of the rated effi cacy versus the rated watts of all lamps used in the LCC for each lamp type and product class. Efficacy levels are also shown in the plot. Note that the plotted efficacy levels are based on lamp catalog values and do not incorporate manufact uring variability.

Annual Cash Flow Contains tables of the equipmen t costs, operating costs and total discounted costs in each year. Tables are only relevant for the lamp type and/or product class selected by the user.

Assumptions and Inputs Includes sales tax data, labor co sts, ballast data (system power ratings, ballast prices, lamp s per ballast), T12 to T8 a substitution

a T12 refers to tubular fluorescent lamps with diameters of twelve-eighths of an inch. T8 refers to tubular fluorescent lamps with diameters of eight-eighths of an inch.8A-2 ratios, lifetimes used to calcula te the analysis period, and the percent mean versus initial lumens for incandescent reflector lamps (IRL).

Electricity Prices Contains reference year electricity prices for the sampled

households.

Electricity Price Trend Contains the electricity price tren ds for the reference-, high-, and low-economic-growth scenarios based on the April 2009 AEO2009 with impacts of the American R ecovery and Reinvestment Act of February 2009 (Pub. L. 111-5).

Discount Rates Contains data from which an average discount rate and a

distribution of discount rates are determined.

Commercial Op. Hours Contains CBECS data and LMC Vol. I data from which an average commercial operating hours and a distribution of commercial operating hours for each lamp type are determined.

Residential Op. Hours Contains RECS data and LMC Vol. I data from which an average residential operating hours and a distribution of residential operating hours for each lamp type are determined.

Industrial Op. Hours Contains MECS data and LMC Vol. I data from which an average industrial operating hours and a di stribution of industrial operating hours for each lamp type are determined.

8A.3 BASIC INST RUCTIONS

Basic instructions for operating the LCC spreadsheets are as follows:

1. On ce you have downloaded the LCC spreadsheet file from the web, open the file using Excel. Click on the tab for the worksheet named LCC Summary.

2. Us e Excel’s “View/Zoom” commands at the t op menu bar to change the size of the display to make it fit your monitor.

3. In teract with the spreadsheet by clicking choi ces or entering data using the interface on

the worksheet named LCC Summa ry. Select choices from th e various inputs listed under "User Input" heading.

4. Under the "User Input" heading, select choices from the se lection buttons and boxes for the following: (1) Type of calculation (Sample Calculation or Crystal Ball), (2) Product Class, (3) Lamp Type, (4) Baseline Lamp, (5) Sector, (6) Electricity Price Trend, (7) Start8A-3 Year, (8) Multiple of Baseline Service Life (for Analysis Period), (9) Lamp Price, (10) Ballast Price, (11) Op. Hrs Sensitivity (Low Op. Hours or High Op. Hours), (12)

Residential Lam p Preservation (select la mp preservation Avg. Op Hours or High Op. Hours scenario, and select au tomatically-computed or user-s pecified Remaining Fixture Life at AP Start), and (13) LCC Subgroup (Low Income Consumers, Institutions of Religious Worship, Institutions Serving Low Income Populations, and Consumers of T12 Electronic Ballasts).

5. To change inputs listed under "User Input", sel ect the input you wish to change by either clicking on the appropriate button/checkbox or selecting the appropria te input from the input box.

6. This spreadsheet gives the user two types of calculation methods:

a. If the "Sample Calc" is selected, then all calculations are perf ormed for single-input values, usually an average. The ne w results are shown on the LCC Summary

worksheet as soon as the new values are entered.

b. Alternately, if the "CB Calculation" is sele cted, the spreadsheet generates results that

are distributions. Some of th e inputs are also distributions . The results from the LCC distribution are shown as singl e values and refer only to the results from the last Monte Carlo sample and are therefore not m eaningful. To run the distribution version of the spreadsheet, the Microsoft Excel add- in software called Crystal Ball must be

enabled.

To produce sensitivity results using Crystal Ball, simply select R un from the Run menu (on the menu bar). To make basic changes in the run sequence, includi ng altering the number of trials, select Run Preferences from the Run menu. After each si mulation run, the user needs to select Reset (also from the Run menu) before R un 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 possi ble to obtain further statistical information. The time taken to complete a run sequence can be reduced by mi nimizing the Crystal Ball window in Microsoft Excel. A step-by-step summary of the procedur e for running a distribution analysis is outlined

below:

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

2. Click on Run from the menu bar

3. Select Run Preferences and choos e from the following choices:

a. Monte Carlo b

b. Latin Hypercube (recommended)

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

b Because of the nature of the program, there is some variation in results due to random sampling when Monte Carlo or Latin Hypercube sampling is used.8A-4 d. Select number of Monte Carlo Trials

4. To run the simulation, select the green ri ght arrow (“Start Simulation”) button on the Crystal Ball toolbar or select “S tart Simulation” from the Run menu.

5. Now wait until the program informs you that the simulation is completed. Note that to run a new simulation, “Reset Simulation” mu st first be selected from the Run menu.

The following instructions are provided to view the output genera ted by Crystal Ball.

1. After the simulation has finished, to see th e distribution charts generated, select the Crystal Ball window in the Windows task bar.

2. The life-cycle cost savings and payback pe riods 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 that a particular value of LCC savings will occur, either type “0” in the box by the left arrow, or move the arrow key with the cursor to “0” on the scale. The value in the Certainty box show s the likelihood that the LCC savings will occur. To calculate the certainty of payb ack 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 th e Run menu and then select the charts and statistics that you are interested in. For furt her information on Crystal Ball outputs, please refer to Understanding the Forecast Chart in the Crystal Ball manual.