8 A

- i

APPENDIX 8A. USER INSTRUCTIONS FOR LIFE-CYCLE COST AND

PAYBACK PERIOD 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 LIFE-CYCLE COST AND PAYBACK
P ERIOD SPREADSHEETS

8A.1 INTRODUCTION It is possible to examine and reproduce the detailed results of the life-cycle cost (LCC )

a nd payback period (PBP) analyses using Microsoft Excel® spreadsheets available on regulations.gov, docket number EERE-2011- BT

- STD-0006 at

ww w.regulations.gov/#!docketDetail;D=EERE-2011- BT -STD-0006 . Running the spreadsheet sample calculations requires Microsoft Excel. Additionally, Crystal Ball® software is required to run the Monte Carlo simulations. Both applications are commercially available. Crystal Ball is available at www.decisioneering.com

. The spreadsheet posted on regulations.gov represents the latest version of the LCC and P BP analysis model. It has been tested with Microsoft Excel 20 10 and Crystal Ball 11. 1

. 2

. 2

.

8A.2 LCC AND PBP WORKBOOK The LCC and PBP spreadsheet or workbook consists of the following worksheets:

LCC Summary (Sum) Contains the input selections and a summary table including energy use, operating costs, installed costs, LCC, L CC savings, and PBP for each selection. This worksheet also works as an interface between user inputs and the rest of the worksheets.

Import Parameters (Para) Contains the engineering analysis, as well as all parameters im ported into the LCC Summary page. Tables of results are onl y relevant for the lamp type and/or product class selected by the user.

Lighting Controls (Cont) Contains relevant statistics and assumptions regarding the
pr evalence and lumen reduction of lighting controls, as well as their effects on energy usage and lamp lifetime.

Learning Curve (Learn) Contains the data to account for learning curve adjustments to the installed price and tables displaying installed prices adjusted for learning curves and rare earth factors for each product during each year.

Annual Cash Flow (Cash) C ontains tables of the equipment costs, operating costs, and total discounted costs in each year, as well as tables de tailing the years that require a lamp or ballast replacement. Tables are only relevant for the lamp type and/or product class selected by the user.8A

- 2

Assumptions and Inputs (Assump) I ncludes sales tax data, labor costs, disposal costs, rare

e arth scenario values, ballast data (lifetimes, lamps per ba llast), lifetimes used to calculate the analysis period, and the pe rcent mean versus initial lumens for incandescent

re flector lamps (IRLs).

Electricity Prices (E P rices) Contains reference year electricity prices for the sampled house holds and Consumer Price Index values for scaling.

Electricity Trends (EPriceTrend) Contains the electricity price trends for the reference-, high -, and low-economic-growth scenarios based on the U.S. Energy Information Administration’s Annual Energy Outlook 20 12

.

Discount Rates (D R ates) Contains data from which an average discount rate and a dist ribution of discount rates are determined.

Commercial Op

. Hours (ComOH) Contains Commercial Building Energy Consumption Survey data and 2010 US Lighting Market Characterization (2010 LMC) data from which average commercial operating hours and a distribution of commercial operating hours for each lamp type are determined.

Residential Op

. Hours (ResOH) C ontains Residential Energy Consumption Survey data and 2010 LMC data from which average residential operating hours and a distribution of residential operating hours for each lamp type are determined.

Industrial Op

. Hours (IndOH) Contains Manufacturing Energy Consumption Survey data and 2010 LMC data from which average industrial operating hours and a distribution of industrial operating hours for each lamp type are determined.

8A.3 BASIC INSTRUCTIONS Basic instructions for operating the LCC spreadsheets are as follows:

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

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

3. Interact with the spreadsheet by clicking choices or entering data using the interface on the w orksheet named Sum. Select choices from the various inputs listed under the “User I nput” heading.8A

- 3

4. Under the “User Input” heading, select choices from the selection buttons and boxes for the f ollowing: (1) Type of calculation (Sample Calculation or Crystal Ball), (2) Lamp Type, (3) Product Class, (4) Baseline Lamp, (5) Sector, (6) Number of Lamps, (7) Ballast T ype, (8 ) Electricity Price Trend, (9) Start Year, (10) Learning Curve (11) Multiple of B aseline Service Life (for Analysis Period), (12) Lamp Price, (13 ) Ballast Price, (14) Rare Earth Scenario, (15) Residential Lamp Preservation (select lamp preservation Avg. Op H ours or High Op. Hours scenario, and select user-specified Remaining Fixture Life at AP Start), (1 2 ) Op. Hrs Sensitivity (Low Op. Hours or High Op. Hours), (13) New Construction Spacing (No spacing adjustments or spacing adjustments), (14) Base Case Dimmer Selection (No Base Case Dimmers, Base Case Dimmers, or Distribution of Base Case Dimmers), and (15) Equalize Lumens Selection (Equalize Lumens at Each Level or Dim By Same Percentage at Each Level).

5. To change inputs listed under “User Input,” select the input you wish to change by either

c licking on the appropriate button/checkbox or selecting the appropriate input from the i nput box.

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

a. If “Sample Calc” is selecte d, then all calculations are performed for single-input

va lues, usually an average. The new results are shown on the LCC Summary work sheet as soon as the new values are entered.

b. Alternately, if “Crystal Ball” is selected, the spreadsheet generates results that are dist ributions. Some of the inputs are also distributions. The results from the LCC distribution are shown as single values and refer only to the results from the last Monte Carlo sample, and are therefore not meaningful. 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 Start from the Run menu (on the Crystal Ball toolbar). 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

ne eds to select Reset (also from the Run menu) before Start can be selected again. Once Crystal B all has completed its run sequence, it will produce a series of distributions. Using the Analyze menu on the Crystal Ball Excel tab, 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 i n Microsoft Excel. The following outlines a step- by

- step summary of the procedure for running a distribution analysis:

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

2. Click on Start from the Run menu bar

3. Select Run Preferences and choose from the following choices:

a. Monte Carlo (recommended) b. Latin Hypercube8A

- 4

c. Initial seed choices and whether or not to be constant between runs a d. Select number of Monte Carlo Trials (1,000 was used for this analysis)

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

5. Wait until the program informs you that the simulation is completed. Note that to run a

ne w simulation, “Reset Simulation” must first be selected from the Crystal Ball toolbar or Run menu.

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

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

2. The LCC savings and PBPs are defined as Forecast cells. The frequency charts display the r esults 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 Vie w

se lection on the Crystal Ball toolbar for each chart can be used to specify whether you

wa nt cumulative or frequency plots shown.

3. To calculate the certainty that the LCC savings will be above a particular value, either
t ype that value in the box by the left arrow at the bottom of the chart window, or move the a rrow key with the cursor to that value on the scale. The value in the Certainty box shows the likelihood t hat the LCC savings will be above that value. To calculate the certainty of the PBP being below a certain number of years, choose that value as the high

e ndpoint.

4. To generate a printout report, select Create Report from the Analyze menu and then

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

5. To determine an average of the results reported by Crystal Ball for a forecasted value, select Extract Data from the Analyze menu and calculate the average of the trial values reported for that variable.

a 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 . Th e same sequence of random numbers should be used to reproduce results consistent with the presented values. To do this, select “Use same sequence of random numbers” and an initial seed value of 999 in the sampling tab.