8A -i

APPENDIX 8A. USER INSTRUCTIONS FOR LCC AND PBP SPREADSHEETS

TABLE OF CONTENTS

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

.................................................................... A-1

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

................ A-1

8A.3 BASIC INSTRUCTIONS ................................................................

......................... A-38A-1

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

.

The spreadsheets posted on the DOE website represent the latest version s and have b een tested with Microsoft Excel 200 7 and Crystal Ball 11.1.1.1.00.

8A.2 LCC AND PBP WORKBOOK

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

App. Sample Calc. Presents shipment-weighted results for the application-specific Summary Sample Calculation.

Application LCC Results Presents LCC and PBP results for each application within a representative unit or product class.

Avg. Sample Calc. Presents results for the basic Sample Calculation. These results are Summary based on a Sample Calculation for each representative unit or product class where application-specific inputs are averaged prior

to calculation.

LCC Summary Contains the input selections and summary tables 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.

Setup Contains tables listing the contents of the menus shown in the "LCC Summary" worksheet along with indicators showing the

current user selections.

Cash Flows Contains the discounted cash flows for each unit.

Variables Contains a list of the major variables (Excel defined names) in usage in the LCC model, along with their values.8A-2

Rep Unit Summary Contains a list of the parameters for the representative units.

Class A EPS Input Contains the inputs for the Class A External Power Supply

Selection scenario chosen.

BC Input Selection Contains the inputs for the Battery Charger scenario chosen.

Class A EPS Inputs Contains all Class A External Power Supply product information, such as unit energy consumption (UEC), markups, and lifetimes for each input scenario.

BC Inputs Contains Battery Charger product information, such as UEC, markups, and lifetimes for each input scenario.

Application Data Contains a summary of all the application-specific data from the input sheets so that applications can be sampled in the LCC model.

Lifetime Contains information on the lifetimes of the units so that a lifetime estimate can be selected for input into the LCC model.

Markups Contains information related to the price markups used in the analysis, including sales tax.

Unit Price Contains information used in the development of unit purchase prices, including markups and sales tax.

Base Case Eff Dist

Contains tables of the unit base case efficiency distributions so that

the CSLs of the baseline units can be determined.

Discount Rate Contains information used to develop the discount rate for the LCC analysis.

Gas Prices Contains gasoline price data, engine efficiency assumptions, and consumer price indices used to develop the gasoline prices for the LCC and PBP analyses for Battery Charger product class 9 ( DC

DC, ≥9 V Input ).

Elec Prices Contains electricity price data and consumer price indices used to develop the electricity prices for the LCC and PBP analyses.

Elec Price Trends Contains data used to develop the electricity price trends for projection of electricity prices into the future.8A-3

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) Run the application-specific sample calculation, (3 ) Energy Price Trend, (4 ) Start Year, (5 ) Sector, (6 ) Lifetime

, (7 ) Market Distribution, (8) LCC Input Scenario, and (9) Battery Charger Inputs. Under the “Application Selection” heading, the user can select a specific application to examine within a given representative unit or product class.

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 three types of calculation methods:

a. If the "Sample Calculation" is selected, then all calculations are performed for single input values, usually an average. This calculation averages all application-specific data prior to performing calculations. The new results are shown on the same sheet as

soon as the new values are entere d, and are summarized on the “Avg. Sample Calc. Summary” tab.

b. If "Crystal Ball" 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.

c. If the “Run” button next to “Application

- Specific Sample Calculation” is clicked, the spreadsheet will execute a macro to perform a sample calculation on each individual application within a representative unit or product class. Each run typically takes 10- 30 minutes to finish. Once finished, results will be presented in the “Application LCC Results” tab, and summarized in the “App. Sample Calc. Summary” tab. The shipment- weighted results of this sample calculation differ from the basic “Sample Calculation” in that application inputs are not average d prior to performing calculations. This methodology generates results that better approximate Crystal Ball results than the basic “Sample Calculation” tool.8A-4

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:

a. Monte Carlo a

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, select the green right arrow (“Start Simulation”) button on the Crystal Ball toolbar or select “Start 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” must first be selected from the Run menu.

The following instructions are provided to view the o utput 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 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.

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

.8A-5

4. To generate a printout report, select Create Report from the Run menu and then select the charts and statistics that you are interested in. For further information on Crystal Ball outputs, please refer to Understanding the Forecast Chart in the Crystal Ball manual.