Decision Models Prof Juran 2 Overview Sensitivity Analysis Goal Seek and Data Table Marketing and Finance examples Call Center LP More Sensitivity Analysis SolverTable Decision Models Prof Juran ID: 256074
Download Presentation The PPT/PDF document "Session 2a" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Session 2aSlide2
Decision Models -- Prof. Juran
2
Overview
Sensitivity Analysis
Goal Seek and Data Table
Marketing and Finance examples
Call Center LP
More Sensitivity Analysis
SolverTableSlide3
Decision Models -- Prof. Juran
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?Slide4
Decision Models -- Prof. Juran
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. Slide5
Decision Models -- Prof. Juran
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 priceSlide6
Decision Models -- Prof. Juran
6
Managerial Problem Definition
How do the parameters in Black-Scholes affect the option price?Slide7
Decision Models -- Prof. Juran
7
FormulationSlide8
Decision Models -- Prof. Juran
8
Solution Methodology
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. Slide9
Decision Models -- Prof. Juran
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 itSlide10
Decision Models -- Prof. Juran
10
Solution MethodologySlide11
Decision Models -- Prof. Juran
11
Solution MethodologySlide12
Decision Models -- Prof. Juran
12
Solution MethodologySlide13
Decision Models -- Prof. Juran
13
Solution MethodologySlide14
Decision Models -- Prof. Juran
14
ConclusionsSlide15
Decision Models -- Prof. Juran
15
ConclusionsSlide16
Decision Models -- Prof. Juran
16
ConclusionsSlide17
Decision Models -- Prof. Juran
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%.Slide18
Decision Models -- Prof. Juran
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?)Slide19
Decision Models -- Prof. Juran
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).Slide20
Decision Models -- Prof. Juran
20
FormulationSlide21
Decision Models -- Prof. Juran
21
FormulationSlide22
Decision Models -- Prof. Juran
22
FormulationSlide23
Decision Models -- Prof. Juran
23
Solution Methodology
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
A
B
C
D
E
F
G
H
Inputs
Current sales
40000
Current price
$45
Unit variable cost
$5
Data on rebates
Amount of rebate
$10
Pct taking advantage
50%
Increase in sales
30.00%
Data on price cut
Amount of cut
$6
Increase in sales
20%
Profits
Current
$1,600,000
With rebate
$1,820,000
With price cut
$1,632,000
=B2*(B3-B4)
=((B2*(1+B9))*(B3-B4))-((B2*(1+B9)*B8)*B7)
=B2*(1+B13)*(B3-B12-B4)Slide24
Decision Models -- Prof. Juran
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?Slide25
Decision Models -- Prof. Juran
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)Slide26
Decision Models -- Prof. Juran
26
Goal SeekSlide27
Decision Models -- Prof. Juran
27
Solution MethodologySlide28
Decision Models -- Prof. Juran
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).Slide29
Decision Models -- Prof. Juran
29
What If?
Important parameters are not known; they are only estimates.
How robust is the rebate strategy?Slide30
Decision Models -- Prof. Juran
30
Two-Way Data TableSlide31
Decision Models -- Prof. Juran
31
Two-Way Data Table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
A
B
C
D
E
F
G
H
I
J
Inputs
Best policy
Rebate
Current sales
40000
Current price
$45
Unit variable cost
$5
Data on rebates
Two-way data table for best policy
Amount of rebate
$10
Increase from rebate (along side) and from price cut (along top)
Pct taking advantage
50%
Rebate
10%
15%
20%
25%
30%
Increase in sales
30%
15%
20%
Data on price cut
25%
Amount of cut
$6
30%
Increase in sales
20%
35%
40%
Profits
Current
$1,600,000
With rebate
$1,820,000
With price cut
$1,632,000
=IF(B16=MAX(B16:B18),"Current",IF(B17=MAX(B16:B18),"Rebate","Price cut"))
=E1Slide32
Decision Models -- Prof. Juran
32
Two-Way Data TableSlide33
Decision Models -- Prof. Juran
33
Two-Way Data Table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
A
B
C
D
E
F
G
H
I
J
Inputs
Best policy
Rebate
Current sales
40000
Current price
$45
Unit variable cost
$5
Data on rebates
Two-way data table for best policy
Amount of rebate
$10
Increase from rebate (along side) and from price cut (along top)
Pct taking advantage
50%
Rebate
10%
15%
20%
25%
30%
Increase in sales
30%
15%
Rebate
Rebate
Price cut
Price cut
Price cut
20%
Rebate
Rebate
Rebate
Price cut
Price cut
Data on price cut
25%
Rebate
Rebate
Rebate
Rebate
Price cut
Amount of cut
$6
30%
Rebate
Rebate
Rebate
Rebate
Rebate
Increase in sales
20%
35%
Rebate
Rebate
Rebate
Rebate
Rebate
40%
Rebate
Rebate
Rebate
Rebate
Rebate
Profits
Current
$1,600,000
With rebate
$1,820,000
With price cut
$1,632,000
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.
=IF(B16=MAX(B16:B18),"Current",IF(B17=MAX(B16:B18),"Rebate","Price cut"))
=E1Slide34
Decision Models -- Prof. Juran
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.Slide35
Decision Models -- Prof. Juran
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. Slide36
Decision Models -- Prof. Juran
36
Call Center ExampleSlide37
Decision Models -- Prof. Juran
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.Slide38
Decision Models -- Prof. Juran
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. Slide39
Decision Models -- Prof. Juran
39
Formulation
Decision Variables
X
1
= Daytime Calls,
X
2
= Evening Calls
Objective
Minimize
Z
= 2
X
1
+ 5
X
2
Constraints
0.30
X
1
+ 0.30
X
2
≥ 150
0.10
X
1
+ 0.30
X
2
≥ 120
0.10
X
1
+ 0.15
X
2
≥ 100
0.10
X
1
+ 0.20
X
2
≥ 110
1
X
1
≥ 1
X
2
1
X
1
, 1
X
2
≥ 0
Slide40
Decision Models -- Prof. Juran
40
Solution MethodologySlide41
Decision Models -- Prof. Juran
41
Solution MethodologySlide42
Decision Models -- Prof. Juran
42
Solution MethodologySlide43
Decision Models -- Prof. Juran
43
Optimal Solution
Make 900 Daytime calls and 100 Evening calls.
Total cost = $2,300.Slide44
Decision Models -- Prof. Juran
44
SolverTable
Similar to Data Table; works with Solver
Solves optimization problems repeatedly and automatically
One or two inputs can be variedSlide45
Decision Models -- Prof. Juran
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. Slide46
Decision Models -- Prof. Juran
46
Solution MethodologySlide47
Decision Models -- Prof. Juran
47
Solution MethodologySlide48
Decision Models -- Prof. Juran
48
SolverTable OutputSlide49
Decision Models -- Prof. Juran
49
ConclusionsSlide50
Decision Models -- Prof. Juran
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.Slide51
Decision Models -- Prof. Juran
51
ConclusionsSlide52
Decision Models -- Prof. Juran
52
Summary
Sensitivity Analysis
Goal Seek and Data Table
Marketing and Finance examples
Call Center LP
More Sensitivity Analysis
SolverTable