Sensitivity Analysis in Decision Models

Sensitivity analysis using goal seek, data tables, and SolverTable in decision models for finance and marketing scenarios. Includes examples on European call options and Microsoft's pricing strategy.

• sensitivity analysis
• decision models
• finance example
• marketing example
• European call options
• pricing strategy
• goal seek
• data table

saanvi Follow

Uploaded on Dec 09, 2023 | 5 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.

Presentation Transcript

1. Session 2a

2. Overview Sensitivity Analysis Goal Seek and Data Table Marketing and Finance examples Call Center LP More Sensitivity Analysis SolverTable Decision Models -- Prof. Juran 2

3. Sensitivity Analysis How do key outputs change in response to changes in inputs? Which inputs are the most important? How robust is our decision? Decision Models -- Prof. Juran 3

4. Finance Example A European call option on a stock earns the owner an amount equal to the price at expiration minus the exercise price, if the price of the stock on which the call is written exceeds the exercise price. Otherwise, the call pays nothing. A European put option earns the owner an amount equal to the exercise price minus the price at expiration, if the price at expiration is less than the exercise price. Otherwise the put pays nothing. Decision Models -- Prof. Juran 4

5. Finance Example The Black-Scholes formula calculates the price of a European options based on the following inputs: today's stock price the duration of the option (in years) the option's exercise price the risk-free rate of interest (per year) the annual volatility (standard deviation) in stock price Decision Models -- Prof. Juran 5

6. Managerial Problem Definition How do the parameters in Black-Scholes affect the option price? Decision Models -- Prof. Juran 6

7. Formulation The Black-Scholes model: ( ) ( ) = rt C SN d Ee N d 1 2 where: S E r 2 t = current stock price = exercise price = risk-free rate of return = variance of the stock s return = time to expiration S 2 = t d 1 = probability that z < d 2 + + ln r t E d1 = 2 t 2 d2 N(d) Decision Models -- Prof. Juran 7

8. Solution Methodology A B C D E F G H 1 2 3 4 5 6 7 8 9 Inputs 1 35 40 0.5 0.05 0.4 Type of option (1 for call, 2 for put) Stock price Exercise price Duration (years) Riskfree interest rate Volatility =IF(B2=1,NORMSDIST(B10),NORMSDIST(-B10)) =(LN(B3/B4)+(B6+B7^2/2)*B5)/(B7*SQRT(B5)) Quantities for Black-Scholes formula d1 d2 10 11 12 13 14 15 16 -0.242 -0.525 N(d1) N(d2) 0.404 0.300 =B10-SQRT(B7^2*B5) =IF(B2=1,NORMSDIST(B11),NORMSDIST(-B11)) Option price 2.456 =IF(B2=1,B3*E10-B4*EXP(-B5*B6)*E11,-(B3*E10-B4*EXP(-B5*B6)*E11)) Notice the use of if statements in cells E10:E11 and B13, so that the same model can be used for both puts and calls. Decision Models -- Prof. Juran 8

9. Data Table Similar to copying a formula over many cells, but better for complicated functions (e.g. Black-Scholes) Specify Row and/or Column Input Cells Tricky to learn, but worth it Decision Models -- Prof. Juran 9

10. Solution Methodology A B C D E 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Inputs 1 35 40 0.5 0.05 0.4 Type of option (1 for call, 2 for put) Stock price Exercise price Duration (years) Riskfree interest rate Volatility Quantities for Black-Scholes formula d1 d2 -0.242 -0.525 N(d1) N(d2) 0.404 0.300 Option price 2.456 Volatility Price 2.456 =B13 Decision Models -- Prof. Juran 10

11. Solution Methodology A B 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Volatility Price 2.456 0.01 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.85 0.90 0.95 1.00 Decision Models -- Prof. Juran 11

12. Solution Methodology Decision Models -- Prof. Juran 12

13. Solution Methodology A B 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Volatility Price 2.456 0.000 0.000 0.071 0.312 0.664 1.075 1.518 1.981 2.456 2.939 3.426 3.917 4.410 4.903 5.397 5.890 6.382 6.873 7.362 7.850 8.335 0.01 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.85 0.90 0.95 1.00 Decision Models -- Prof. Juran 13

14. Conclusions Price vs. Volatility $10.00 $8.00 $6.00 Price $4.00 $2.00 $- 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Volatility Decision Models -- Prof. Juran 14

15. Conclusions Option Price vs. Current Stock Price $70.00 $60.00 $50.00 Option Price $40.00 $30.00 $20.00 $10.00 $- $- $10.00 $20.00 $30.00 $40.00 $50.00 $60.00 $70.00 $80.00 $90.00 $100.00 Current Stock Price Decision Models -- Prof. Juran 15

16. Conclusions Option Price vs. Duration $25.00 $20.00 Option Price $15.00 $10.00 $5.00 $- 0 1 2 3 4 5 6 7 8 9 10 Duration Decision Models -- Prof. Juran 16

17. Marketing Example Microsoft is trying to determine whether to give a $10 rebate, a $6 price cut, or have no price change on a software product. Currently 40,000 units of the product are sold each week for $45. The variable cost of the product is $5. The most likely case appears to be that a $10 rebate will increase sales 30% and half of all people will claim the rebate. For the price cut, the most likely case is that sales will increase 20%. Decision Models -- Prof. Juran 17

18. Managerial Problem Definition Under what circumstances should Microsoft offer the rebate, and under what circumstances should they offer the price cut? (Or should they do neither?) Decision Models -- Prof. Juran 18

19. Formulation Decision variables: 3 possible marketing policies. Objective: Maximize Profit. Constraints: Various assumptions have been made (current sales level, current cost structure, consumer behavior in response to marketing policies). Decision Models -- Prof. Juran 19

20. Formulation Under the current policy, Profit = Variable Revenue Variable Cost = Volume*(Price Variable Cost) 45 $ 000 , 40 = 000 , 600 , 1 $ = ( ) 5 $ Decision Models -- Prof. Juran 20

21. Formulation Under the rebate policy: Profit = Variable Revenue Variable Cost Rebate Cost = Volume*(Price Variable Cost) (Claim Volume*Rebate) ( 5 $ 45 $ * 3 . 1 * 000 , 40 = 000 , 820 , 1 $ = ) ( ) ( ) ( * ) 40 , 000 3 . 1 * 5 . 0 * $ 10 Decision Models -- Prof. Juran 21

22. Formulation With the price cut: Profit = Variable Revenue Variable Cost = Volume*(Price Variable Cost) 000 , 40 * 2 . 1 = 000 , 632 , 1 $ = ) ( * ) 5 $ ( $ 39 Decision Models -- Prof. Juran 22

23. Solution Methodology A B C D E F G H 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Inputs Current sales Current price Unit variable cost 40000 $45 $5 Data on rebates Amount of rebate Pct taking advantage Increase in sales $10 50% 30.00% Data on price cut Amount of cut Increase in sales $6 20% Profits Current With rebate With price cut =B2*(B3-B4) $1,600,000 $1,820,000 $1,632,000 =((B2*(1+B9))*(B3-B4))-((B2*(1+B9)*B8)*B7) =B2*(1+B13)*(B3-B12-B4) Decision Models -- Prof. Juran 23

24. Under current assumptions, the rebate policy appears to be optimal. How sensitive is this result to possible errors in our assumptions? Specifically, how wrong could we be as to the 30% assumption and still be correct in using the rebate? What is the point of indifference between the rebate and the price cut? Decision Models -- Prof. Juran 24

25. Goal Seek Similar to Solver, but simpler Specify a Target Cell and a Changing Cell Value must be a number (not a cell reference) Decision Models -- Prof. Juran 25

26. Goal Seek Decision Models -- Prof. Juran 26

27. Solution Methodology A B C D E F G 1 2 3 4 5 6 7 8 9 Inputs Current sales Current price Unit variable cost 40000 $45 $5 Data on rebates Amount of rebate Pct taking advantage Increase in sales $10 50% Use Goal Seek to make the value in cell B17 equal to 1632000 (the value in B18), using cell B9 as the changing cell. 16.57% 10 11 12 13 14 15 16 17 18 Data on price cut Amount of cut Increase in sales $6 20% Profits Current With rebate With price cut $1,600,000 $1,632,000 $1,632,000 Decision Models -- Prof. Juran 27

28. Conclusions and Recommendations Go with the rebate as long as the increase in sales is expected to be at least 16.57%. Under current assumptions, Microsoft would earn $1,820,000 profit (an improvement of $220,000). Decision Models -- Prof. Juran 28

29. What If? Important parameters are not known; they are only estimates. How robust is the rebate strategy? Decision Models -- Prof. Juran 29

30. Two-Way Data Table A B C D E F G H I J 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Rebate Inputs Current sales Current price Unit variable cost Best policy 40000 $45 =IF(B16=MAX(B16:B18),"Current",IF(B17=MAX(B16:B18),"Rebate","Price cut")) $5 Data on rebates Amount of rebate Pct taking advantage Increase in sales $10 50% 30% Data on price cut Amount of cut Increase in sales $6 20% Profits Current With rebate With price cut $1,600,000 $1,820,000 $1,632,000 Decision Models -- Prof. Juran 30

31. Two-Way Data Table A B C D E F G H I J 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Rebate Inputs Current sales Current price Unit variable cost Best policy 40000 $45 =IF(B16=MAX(B16:B18),"Current",IF(B17=MAX(B16:B18),"Rebate","Price cut")) $5 Data on rebates Amount of rebate Pct taking advantage Increase in sales Two-way data table for best policy Increase from rebate (along side) and from price cut (along top) Rebate 10% 15% 15% 20% 25% 30% 35% 40% $10 50% 30% 20% 25% 30% =E1 Data on price cut Amount of cut Increase in sales $6 20% Profits Current With rebate With price cut $1,600,000 $1,820,000 $1,632,000 Decision Models -- Prof. Juran 31

32. Two-Way Data Table Decision Models -- Prof. Juran 32

33. Two-Way Data Table A B C D E F G H I J Inputs Current sales Current price Unit variable cost Best policy 1 2 3 4 5 6 7 8 9 Rebate 40000 $45 =IF(B16=MAX(B16:B18),"Current",IF(B17=MAX(B16:B18),"Rebate","Price cut")) $5 Data on rebates Amount of rebate Pct taking advantage Increase in sales Two-way data table for best policy Increase from rebate (along side) and from price cut (along top) Rebate 10% 15% 15% Rebate Rebate 20% Rebate Rebate 25% Rebate Rebate 30% Rebate Rebate 35% Rebate Rebate 40% Rebate Rebate $10 50% 30% 20% 25% 30% Price cut Rebate Rebate Rebate Rebate Rebate Price cut Price cut Rebate Rebate Rebate Rebate Price cut Price cut Price cut Rebate Rebate Rebate 10 11 12 13 14 15 16 17 18 =E1 Data on price cut Amount of cut Increase in sales $6 20% Profits Current With rebate With price cut Unless Microsoft thinks the sales increase from a price cut will be high and the sales increase from a rebate will be low, it looks like the rebate is the way to go. $1,600,000 $1,820,000 $1,632,000 Decision Models -- Prof. Juran 33

34. Conclusions and Recommendations Unless Microsoft thinks the sales increase from a price cut will be high and the sales increase from a rebate will be low, it looks like the rebate is the way to go. Decision Models -- Prof. Juran 34

35. Call Center Example For a telephone survey, a marketing research group needs to contact at least 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. It costs $2 to make a daytime call and (because of higher labor costs) $5 to make an evening call. Because of a limited staff, at most half of all phone calls can be evening calls. Decision Models -- Prof. Juran 35

36. Call Center Example Person Responding Wife Husband Single male Single female None Percentage of Daytime Calls 30 10 10 10 40 Percentage of Evening Calls 30 30 15 20 5 Decision Models -- Prof. Juran 36

37. Managerial Problem Definition We want to minimize the total cost of completing the survey, subject to the various probabilities of reaching certain types of people at certain times of the day, costs of making calls, and minimum requirements for numbers of calls to certain demographic groups. Decision Models -- Prof. Juran 37

38. Formulation Decision Variables We need to decide how many evening calls and how many daytime calls to make. Objective Minimize the total cost. Constraints We need to contact 150 wives, 120 husbands, 100 single adult males, and 110 single adult females. At most half of all phone calls can be evening calls. Decision Models -- Prof. Juran 38

39. Formulation Decision Variables X1 = Daytime Calls, X2 = Evening Calls Objective Minimize Z = 2X1 + 5X2 Constraints 0.30X1 + 0.30X2 150 0.10X1 + 0.30X2 120 0.10X1 + 0.15X2 100 0.10X1 + 0.20X2 110 1X1 1X2 1X1, 1X2 0 Decision Models -- Prof. Juran 39

40. Solution Methodology A B C D E F G H 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Percentages Wife Husband Single male Single female None Sum Daytime 30% 10% 10% 10% 40% 100% Evening 30% 30% 15% 20% 5% 100% Cost/call $ 2.00 $ 5.00 Daytime 1 Evening 1 <= 1 Total 2 =SUM(B12:C12) Calls made =0.5*D12 Max evening calls Contacts Wife Husband Single male Single female Made 0.6 0.4 0.25 0.3 0 $ Required 150 120 100 110 0 >= >= >= >= Total cost 7.00 =SUMPRODUCT($B$12:$C$12,B5:C5) =SUMPRODUCT($B$12:$C$12,B9:C9) Decision Models -- Prof. Juran 40

41. Solution Methodology Decision Models -- Prof. Juran 41

42. Solution Methodology A B Percentages Daytime Wife 30% Husband 10% Single male 10% Single female 10% None 40% Sum 100% C D 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Evening 30% 30% 15% 20% 5% 100% Cost/call $ 2.00 $ 5.00 Daytime 900 Evening 100 <= 500 Total 1000 Calls made Max evening calls Contacts Wife Husband Single male Single female Made 300 120 105 110 0 2,300.00 $ Required 150 120 100 110 0 >= >= >= >= Total cost Decision Models -- Prof. Juran 42

43. Optimal Solution Make 900 Daytime calls and 100 Evening calls. Total cost = $2,300. Decision Models -- Prof. Juran 43

44. SolverTable Similar to Data Table; works with Solver Solves optimization problems repeatedly and automatically One or two inputs can be varied Decision Models -- Prof. Juran 44

45. Example: Sensitivity to Calling Costs Starting with the optimal solution to the initial problem, use the SolverTable add-in to investigate changes in the unit cost of either type of call. Specifically, investigate changes in the cost of a daytime call, with the cost of an evening call fixed, to see when (if ever) only daytime calls or only evening calls will be made. Decision Models -- Prof. Juran 45

46. Solution Methodology Decision Models -- Prof. Juran 46

47. Solution Methodology Decision Models -- Prof. Juran 47

48. SolverTable Output F G H I 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Daytime 1200 1200 Evening Total Cost $ 1,200.00 $ 2,300.00 $ 3,100.00 $ 3,600.00 $ 4,000.00 $ 4,400.00 $ 4,800.00 $ 5,200.00 $ 5,600.00 $ 6,000.00 $ 6,400.00 $ 6,800.00 $ 7,200.00 $ 7,600.00 $ 8,000.00 $ 8,400.00 $ 8,800.00 $ 9,200.00 $ 9,600.00 $ 10,000.00 $ 0 1 2 3 4 5 6 7 8 9 0 0 - 900 700 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 100 200 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 400 10 11 12 13 14 15 16 17 18 19 20 Decision Models -- Prof. Juran 48

49. Conclusions Sensitivity Analysis 1400 $7,000 1200 $6,000 Daytime Evening Total Cost 1000 $5,000 Calls Made Total Cost 800 $4,000 600 $3,000 400 $2,000 200 $1,000 0 $- $- $1.00 $2.00 $3.00 $4.00 $5.00 $6.00 $7.00 $8.00 $9.00 $10.00 Cost per Daytime Call Decision Models -- Prof. Juran 49

50. Conclusions If daytime calls are very inexpensive, we can dispense with evening calls altogether. However, we will always have to make at least 400 daytime calls, no matter how expensive they are. Decision Models -- Prof. Juran 50

Load More ...