APPENDIX 8A. USER 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­2** 

**8A­i**APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS

8A.1 INTRODUCTION

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

**and payback**  **period (PBP)**  **analyses using**  **Microsoft Excel®**  **spreadsheets available**  **on the**  **U.S.**

**Department of**  **Energy (DOE)**  **website at:** 

**http://www.eere.energy.gov/buildings/appliance_standards/**

**. Running**  **the spreadsheets**  **requires**

**both Microsoft**  **Excel and**  **Crystal Ball®**  **software. Both**  **applications are**  **commercially available.** 

**Crystal Ball**  **is available**  **at http://www.decisioneering.com** 

**.**

**The spreadsheet**  **posted on**  **the DOE**  **website represent**  **the latest**  **versions and**  **have been** 

**tested with**  **Microsoft Excel**  **2003 and**  **Crystal Ball**  **7.2.2.**

8A.2 LCC AND PBP WORKBOOK

**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 analysis**  **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 efficacy**  **versus the**  **rated watts**  **of all** 

**lamps used**  **in the**  **LCC for**  **each lamp**  **type and**  **product class.** 

**Candidate standard**  **levels are**  **also shown**  **in the**  **plot.**

Annual Cash Flow **Contains tables**  **of the**  **equipment 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 costs,**  **ballast data**  **(system power** 

**rating, ballast**  **price, lamps**  **per ballast),**  **T12 to**  **T8 a**  **substitution**

**ratios, lifetime**  **used to**  **calculate the**  **analysis period,**  **and the** 

**a T12**  **refers to**  **tubular fluorescent**  **lamps with**  **diameters of**  **twelve, eights**  **of an**  **inch. T8**  **refers to**  **tubular fluorescent** 

**lamps with**  **diameters of**  **eight, eights**  **of an**  **inch.**

**8A­1 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**  **trends for**  **the reference,**  **high, and** 

**low economic**  **growth scenarios**  **based on**  **AEO 2007.** 

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 IRL**  **are determined.** 

Industrial Op. Hours **Contains MECS**  **data and**  **LMC Vol.**  **I data**  **from which**  **an 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 file**  **from the**  **Web, open**  **the file**  **using Excel.**  **At the** 

**bottom, click**  **on the**  **tab for**  **sheet LCC**  **Summary.**

**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. The**  **user interacts**  **with the**  **spreadsheet by**  **clicking choices**  **or entering**  **data using**  **the**

**interface that**  **comes with**  **the spreadsheet.**  **Select choices**  **from the**  **various inputs**  **listed**

**under "User**  **Input" heading.** 

**4. Under**  **the "User**  **Input" heading,**  **select choices**  **from the**  **selection buttons**  **and boxes**  **for**

**the following:**  **(1) Type**  **of calculation**  **(Sample Calculation**  **or Crystal**  **Ball), (2)**  **Product**

**Class (3)**  **Lamp Type**  **(4) Baseline**  **Lamp (6)**  **Sector (7)**  **Electricity price**  **Trend, (8)**  **start**

**year, (9)**  **Multiple of**  **Baseline Service**  **Life (for**  **Analysis Period),**  **(10) Lamp**  **Price, (11)** 

**Ballast Price,**  **and (12)**  **Op. Hrs**  **Sensitivity (Low**  **Op. Hours**  **or High**  **Op. Hours).** 

**8A­2 5.**  **To change**  **inputs listed**  **under "User**  **Input", select**  **the input**  **you wish**  **to change**  **by either** 

**clicking on**  **the appropriate**  **button or**  **selecting the**  **appropriate 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 performed**  **for single**  **input**

**values, usually**  **an average.**  **The new**  **results are**  **shown on**  **the same**  **sheet as**  **soon as** 

**the new**  **values are**  **entered.**

**b. Alternately,**  **if the**  **"CB Calculation"**  **is selected,**  **the spreadsheet**  **generates results**  **that**

**are distributions.**  **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 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**  **Microsoft**

**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**  **from the**  **menu bar** 

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

**b a.**  **Monte Carlo** 

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

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

**d. Select**  **number of**  **Monte Carlo**  **Trials**

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

**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­3 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,**  **click on**  **the**

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

**2. The**  **life­cycle cost**  **savings and**  **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 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 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**  **in which**  **you**

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

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

**8A­4**