Lecture 4 Analysis Using Spreadsheets

1. Lecture 4Analysis Using Spreadsheets

2. Five Categories of Spreadsheet Analysis • Base-case analysis • What-if analysis • Breakeven analysis • Optimization analysis • Risk analysis • A detailed analysis may include all of the above steps.

3. Base Case Analysis • Base case can describe one or more of the following: • Current policy, most likely scenario, best or worst case scenarios • Answers questions such as: • If we follow last year’s plan, how much profit should we expect next year? • How many items do we expect to sell next week?

4. What-if Analysis • Analyzes how key outputs change with changes in one or more of the inputs • May vary a parameter, a decision variable, or the model structure • Also called sensitivity analysis • Also part of debugging process • If output is unexpected we have uncovered either a bug or an insight.

5. Varying a Parameter • Asking what if given information were different • Tests numerical assumptions of model • e.g., how much will profit change if our product costs turn out to be 10% higher or lower than we have assumed?

6. Varying a Decision Variable • Exploring outcomes we can influence • Leads us to better decisions • e.g., how much will profit change if we spend an extra $1000 on advertising in the first quarter?

7. Varying the Model Structure • Tests key structural assumptions in model • More complex than changes to parameters or decision variables • e.g., how does profit change if we change our linear model of price and demand to a non-linear one?

8. Benchmarking • Record base-case to compare to results of what-if analysis • Base case can be recorded by: • Home►Clipboard►Copy • Home►Clipboard►Paste►Paste Special with Paste Values Option selected

9. The Paste Special Window

10. Scenarios • Sets of parameter values often go together. • A scenario is a set of parameter values that are internally consistent. • Adding scenarios • Data►Data Tools►What-if Analysis►Scenario Manager • Enter the first scenario by clicking on the Add button • Enter the information required in the Add Scenario window

11. The Scenario Manager Window

12. The Summary Produced by the Scenario Manager

13. CHOOSE Function • When scenarios involve a large number of parameters it is convenient to be able to switch from one set of parameters to another all at once. • This can be accomplished using the Excel CHOOSE function. • CHOOSE selects a value from a range based on an index number. The index number is the number of the scenario and the range contains the inputs for a given parameter.

14. Use of CHOOSE Function to Implement Scenarios

15. Data Sensitivity • Tabulates output based on varying inputs • Found in Sensitivity Toolkit add-in • Steps • Select Sensitivity Toolkit – Data Sensitivity • Choose table type One-Way or Two-Way • Enter output to tabulate in result cell • Click Next

16. One-Way Tables • Cell to Vary is (single) input to vary • Input Type: Begin, End, Increment • Will vary input from first value to last value in steps of size increment • Input Type: Begin, End, Num Obs • Will vary input from first value to last value using N steps

17. The One-Way Inputs For Data Sensitivity

18. Two-Way Tables • Allows two inputs to be varied • Will output a two-dimensional table that displays the output based on the varying values of the two input variables

19. Tornado Charts • Measures the sensitivity of parameters defined in the model • Determines how the output changes based on changes in the inputs • Shows which parameters have a major impact on the results and which do not

20. Types of Tornado Chart • Constant Percentage • Will vary all parameters by a constant percentage entered by the user • Variable Percentage • Percentages can be different for all parameters. • Should be entered in a column beside original parameters • 10/90 Percentiles • Discussed in Chapter 9

21. Tornado Chart Window Cell value to evaluate Cell inputs to vary (should be numbers)

22. Limitations of Tornado Charts • There are potential pitfalls of assuming every parameter varies by the same percentage of the base case. • In models with significant nonlinearities there is an additional pitfall: If the output is related to an input in a nonlinear fashion, the degree of sensitivity implied by a tornado chart may be misleading. • In general, we must be cautious about drawing conclusions about the sensitivity of a model outside the range of the parameters tested in a tornado chart.

23. Breakeven Analysis • Analyzes where a particular point of interest occurs • Answers questions such as: • How high does our market share need to be before we turn a profit? • How high would the discount rate have to be in order for this project to have a NPV of zero? • Excel Goal Seek is a useful tool.

24. Goal Seek • Used for a single output and a single input Output cell address Target level sought Input to vary

25. Optimization Analysis • Finds set of decision variables that achieves best possible value of an output • Excel Solver is important tool. • Answers questions such as: • How should we allocate our budget to maximize profit? • How much inventory should we stock of each type of product, given constraints on shelf size and budget?

26. Simulation: Risk Analysis • Analyzes the effect of uncertainty on the output • Crystal Ball is an important tool. • Answers questions such as: • What is the expected NPV after 25 years given that my return in each year is uncertain? • What is the probability that the NPV after 25 years is greater than $1,000,000, given that my return in each year is uncertain? • What is the distribution of NPV after 25 years given that my return in each year is uncertain?

27. Summary • Base-case analysis • What-if analysis • Scenario analysis • Data Sensitivity tool • Tornado charts • Breakeven analysis • Goal Seek tool • Optimization analysis • Simulation or risk analysis

28. Demo • Excel Model