Decision and Risk Analysis

1. Decision and Risk Analysis Financial Modelling & Risk Analysis Kiriakos Vlahos Spring 2000

2. Session overview • Why do we need risk analysis? • Project evaluation • Risk analysis approaches • Scenario analysis • Sensitivity analysis • Monte-Carlo simulation • Summary

3. Risk management in business Corporate risk Capital budgeting and portfolio evaluation Project Evaluation

4. Why do we need risk analysis? • Single point forecasts are dangerous! • Derive bounds for the range of possible outcomes • Sensitivity testing of the assumptions • Better perception of risks and their interaction • Anticipation and contingency planning • Overall reduction of risk exposure through hedging Risk analysis helps you develop insights, knowledge and confidence for better decision making and risk management.

5. Risk analysis approaches • Scenario analysis • Sensitivity analysis • Monte-Carlo simulation • Decision Analysis • Option theory

6. Skywalker Proposal to open and operate a video store. “You can expect to make at least £50,000 in the first year”

7. Project Evaluation • Evaluating a business proposition • Does it make sense overall? • Market conditions • Trust issues • What is the outlook under a basic set of assumptions? (Base Case) • What are the risks involved? • Writing a business plan

8. Base case model Closing cash exceeds £50000 at the end of the year

9. Scenario analysis “Scenarios are discrete internally consistent views of how the world will look in the future, which can be selected to bound the possible range of outcomes that might occur.” Michael Porter in “Competitive Strategy” “Shell flavour” of scenarios Scenarios should present testing conditions for the business. The future will of course be different from all of these views/scenarios, but if the company is prepared to cope with any of them, it will be able to cope with the real world. Do not assign probabilities to scenarios!

10. Skywalker - Scenarios analysis

11. Sensitivity analysis Explore robustness of results to variations in model parameters Understand and challenge assumptions Methodology • Identify variables to which results are particularly sensitive and those to which they are relatively insensitive • Gain an indication into range over which results might vary, thus assessing the risks Tools • What-if questions • One-way sensitivity analysis • Two-way sensitivity analysis • Tornado diagrams • Spider plots

12. What-if analysis • What-if Tape Price turns out to be 35? • Changing Tape Price to 35, and leaving all other planning values at their base value, we get a December Closing Cash of £30,926 • If Tape Price is 25, December Closing Cash is £70,982

13. One-way sensitivity analsysis e.g. Sensitivity of closing cash to Rent per day

14. Two-way sensitivity analysis Two-variable data table can be applied to a single cell such as December Closing Cash cell:

15. 3-D plot of two-way sensitivity analysis Skywalker: Sensitivity of closing cash to to Rental & Plays per month Tutorial on data tables in Datatables.xls

16. Tornado diagrams Helps us determine visually the main uncertainty drivers. Tutorial on Tornado diagrams in Tornado.xls

17. Constructing spider plots

18. Skywalker: Spider plot

19. Price/Demand Relationship Price is a decision variable and demand should depend on price, e.g. Regression equation: PlaysperMonth = 13.13 - 3.80RentperDay One-way sensitivity analysis to Rent per day Which price maximises closing cash?

20. Monte-Carlo simulation Uncertain variables Base Case Model Uncertain Parameters Base Value Hours Flown 800 Charter Price/Hour 700 Ticket Price/Hour 90 Capacity of Sch. flights 60% Ratio of charter flights 40% Operating Cost/hour 445 Profit & Loss Income from Scheduled £259,200 Income from Chartered £224,000 Operating costs (£356,000) Fixed Costs (£60,000) Taxable profit £67,200 Tax (£22,176) Profit after tax £45,024 Simulate Output distribution

21. Merck’s Research Planning Model Scientific, Medical Monte-Carlo constraints Simulation R&D variables Technological constraints Manufacturing Economic variables relationships Probability distributions for cash-flow Marketing Projections ROI, NPV variables of variables Macro- economic assumptions

22. @RISK - How it works Single simulation trial INPUTS MODEL CALCULATIONS RESULT = Profit = $62 Sales * Price - Cost 211 $5 $993 Multiple simulation trials INPUTS MODEL CALCULATIONS RESULT Profit Trial 1: 211 * 5 - 993 = $62 Trial 1: 193 * 8 - 700 = $884 Trial 1: 219 * 6 - 999 = $315 ... Trial N: 233 * 6 - 975 = $423

23. Novaduct case

24. Novaduct - Uncertainty “Market share increase is equally likely to be any value between -0.2% and 0.8%” -0.2 0.8 “Market growth is most likely to be a 2% increase but could range from a 10% decrease to an 8% increase” 90 108 102

25. Using @RISK 1. Introduce uncertainty into base model eg =RiskUniform(min, max) =RiskTriang(min, most likely, max) =RiskNormal(mean, std.dev.) 2. Select output cells (Cells for which we want simulation results) 3. Select simulation settings Number of iterations, random number seed 4. Execute simulation 5. View results Graphs, summary statistics 6. Return to spreadsheet and possibly repeat previous steps

26. ASSUMPTIONS Discount Rate 15% Prod Cost 5 103.0% Price 7 106.0% Market Share 15% MS Incr 0.3% MktGrowth 102.0% Novaduct using @RISK =RiskUniform(-0.2%,0.8%) =RiskTriang(0.9,1.02,1.08) @Risk Toolbar Simulation settings Specify output cells Simulate Open & Save Simulation Results View @RISK Window View input & output cells

27. Simulation settings

28. @RISK Window

29. Simulation results NPVIRR Mean 914 Mean 25% Max 3174 Max 45% Min -1360 Min -14% P(NPV<0) = 0.17 P(IRR<15%) = 0.15 P(NPV<1,000) = 0.52 P(IRR<35%) = 0.85

30. Cashflow Summary Graph • Central line connects mean values • First band is 1 std.dev. • Second band is interval between 5% and 95% percentiles

31. Summary • Single point forecasts are dangerous! • Challenge assumptions • Scenario Planning • Sensitivity analysis • Data tables • Tornado diagrams • Monte-Carlo simulation • Preparation for Workshop • Datatables.xls and Tornado.xls • @RISK tutorial • Exercises