APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS

TABLE O F CONTENTS

**8A.1 I**  **NTRODUCTION**

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

**It i**  **s 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**  **t he**  **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**  **http://www.decisioneering.com**

**.**

**Th e**  **s preadsheet**  **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 L CC AND PBP WORKBOOK

**LCC a**  **nd PBP**  **spreadsheet or**  **workbook consists**  **of the**  **following worksheets:** 

LCC Summary **Contains t**  **he 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 P arameters **Contains t**  **he 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 **C ontains**  **p lots**  **of the**  **rated efficacy**  **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.** 

Annual C ash Flow **Contains t**  **ables 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 a nd Inputs **Includes s**  **ales 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­eighths of**  **an inch.**  **T8 refers**  **to tubular** 

**fluorescent lamps**  **with diameters**  **of eight­eighths**  **of an**  **inch.**

**8A­1 percent**  **mean versus**  **initial lumens**  **for incandescent**  **reflector lamps** 

**(IRL).**

Electricity Pr ices **Contains reference**  **year electricity**  **prices for**  **the sampled** 

**households.**

Electricity Pr ice Trend **Contains the**  **electricity price**  **trends for**  **the reference,**  **high, and** 

**low economic**  **growth scenarios**  **based on**  AEO2008

**.**

Discount R ates **Contains data**  **from which**  **an average**  **discount rate**  **and a** 

**distribution of**  **discount rates**  **are determined.** 

Commercial O p. 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 O p. 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 O p. 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 B ASIC INSTRUCTIONS

**Basic i**  **nstructions for**  **operating the**  **LCC spreadsheets**  **are as**  **follows:**

**1. O**  **nce y**  **ou 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. U**  **se Ex**  **cel’s “View/Zoom”**  **commands at**  **the top**  **menu bar**  **to change**  **the size**  **of the** 

**display to**  **make it**  **fit your**  **monitor.**

**3. Th**  **e u**  **ser 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. U**  **nder t**  **he "User**  **Input" heading,**  **select choices**  **from the**  **selection buttons**  **and boxes**  **for**

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

**Type (3)**  **Product Class**  **(4) Baseline**  **Lamp (5)**  **Sector (6)**  **Electricity Price**  **Trend, (7)**  **Start**

**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), and**  **(12)**

**Consumer Subgroup.** 

**8A­2 5.**  **To change**  **i nputs**  **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. Th**  **is s**  **preadsheet gives**  **the user**  **two types**  **of calculation**  **methods:**

**a. I**  **f t**  **he "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. A**  **lternately, i**  **f 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**  **s ensitivity**  **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. F**  **ind t**  **he Crystal**  **Ball toolbar**  **(at top**  **of screen)** 

**2. C**  **lick o**  **n Run**  **from the**  **menu bar** 

**3. S**  **elect R**  **un Preferences**  **and choose**  **from the**  **following choices:** 

**b a.**  **Monte C**  **arlo**

**b. L**  **atin H**  **ypercube (recommended)** 

**c. I**  **nitial s**  **eed choices**  **and whether**  **you want**  **it to**  **be constant**  **between runs** 

**d. S**  **elect n**  **umber of**  **Monte Carlo**  **Trials**

**4. To**  **run t**  **he simulation,**  **select the**  **green right**  **arrow (“Start**  **Simulation”) button**  **on the** 

**Crystal Ball**  **toolbar or**  **select “Start**  **Simulation” from**  **the Run**  **menu.**

**5. No**  **w wait**  **until the**  **program informs**  **you that**  **the simulation**  **is completed.**  **Note that**  **to**

**run a**  **new simulation,**  **“Reset Simulation”**  **must first**  **be selected**  **from the**  **Run menu.** 

**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. A**  **fter t**  **he simulation**  **has finished,**  **to see**  **the distribution**  **charts generated,**  **click on**  **the**

**Windows task**  **button that**  **is labeled**  **Crystal Ball.** 

**2. Th**  **e l**  **ife­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 t**  **he 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**