02b - NYU Stern

Report
Session 2b
Overview
• More Sensitivity Analysis
– Solver Sensitivity Report
• More Malcolm
• Multi-period Models
– Distillery Example
– Project Funding Example
Decision Models -- Prof. Juran
2
Solver Sensitivity Report
• Provides sensitivity information about
constraint “right-hand sides” and
objective function coefficients
• Shadow prices
• Allowable increases and decreases
Decision Models -- Prof. Juran
3
Malcolm Revisited
A B
C
D
E
F
G
H
1 Microsoft Excel 15.0 Sensitivity Report
2 Worksheet: [01b-01-malc.xlsx]Optimized
3 Report Created: 9/3/2014 8:20:58 AM
4
5
6 Variable Cells
7
Final
Reduced
Objective Allowable Allowable
8
Cell
Name
Value
Cost
Coefficient Increase
Decrease
9
$B$2 6-oz
6.428571429
0
500
40
275
10
$C$2 10-oz
4.285714286
0
450
550 33.33333333
11
12 Constraints
13
Final
Shadow
Constraint Allowable Allowable
14
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
15
$E$7 Molding Capacity
60 78.57142857
60
5.5
22.5
16
$E$8 Demand for 6-oz
6.428571429
0
8
1E+30 1.571428571
17
$E$9 Storage Space
150 2.857142857
150
90
22
Decision Models -- Prof. Juran
4
Shadow Price
•The effect on the value of the
objective function resulting from a
one-unit change in the constraint’s
right-hand side
•May be viewed as an upper bound
on the value of one additional unit
of a constrained resource
Decision Models -- Prof. Juran
5
Constraints
•Sensitivity to changes in constraint
right-hand sides
•Allowable increase and decrease
define a range within which the
constraint right-hand sides can vary
without affecting the shadow price
Decision Models -- Prof. Juran
6
Example
How much would Malcolm pay for more
molding capacity?
How much more capacity would he buy at that
price?
Decision Models -- Prof. Juran
7
Malcolm's Glass Problem
10
8
8
10-oz. Glasses (100 cases)
10-oz. Glasses (100 cases)
Malcolm's Glass Problem
10
6
Demand Constraint
Molding Constraint
Storage Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Corner Points
4
2
6
Demand Constraint
Molding Constraint
Storage Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Corner Points
4
2
0
0
0
2
4
6
8
10
0
2
6-oz Glasses (100 cases)
Molding Capacity = 60
8
10
8
10
Malcolm's Glass Problem
10
8
8
10-oz. Glasses (100 cases)
10-oz. Glasses (100 cases)
Malcolm's Glass Problem
6
Demand Constraint
Molding Constraint
Storage Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Corner Points
2
6
Molding Capacity = 62
10
4
4
6-oz Glasses (100 cases)
6
Demand Constraint
Molding Constraint
Storage Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Corner Points
4
2
0
0
0
2
4
6
6-oz Glasses (100 cases)
Molding Capacity = 64
Decision Models -- Prof. Juran
8
10
0
2
4
6
6-oz Glasses (100 cases)
Molding Capacity = 66
8
If the limit on molding time is exactly 65.5 hours, then three
constraints all intersect at one point.
Malcolm's Glass Problem
10
10-oz. Glasses (100 cases)
8
6
Demand Constraint
Molding Constraint
Storage Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Corner Points
4
2
0
0
2
4
6
8
10
6-oz Glasses (100 cases)
In this situation there is no utility in further increasing molding
capacity (all other things held constant).
Decision Models -- Prof. Juran
9
Adjustable Cells
•Sensitivity to changes in objective
function coefficients
•Allowable increase and decrease
define a range within which the
objective function coefficients can
vary without affecting the decision
variable values
Decision Models -- Prof. Juran
10
Example
How much does the profit per unit on the 6-oz
product have to go up before Malcolm would
want to increase production of that product?
Decision Models -- Prof. Juran
11
Increases in the profitability of the 6-oz product have
the effect of changing the slope of the isoprofit lines.
Malcolm's Glass Problem
10
8
8
10-oz. Glasses (100 cases)
10-oz. Glasses (100 cases)
Malcolm's Glass Problem
10
6
Demand Constraint
Molding Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Storage Constraint
Corner Points
4
2
6
Demand Constraint
Molding Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Storage Constraint
Corner Points
4
2
0
0
0
2
4
6
8
10
0
2
6-oz Glasses (100 cases)
6-oz profit = 300
10
8
8
10-oz. Glasses (100 cases)
10-oz. Glasses (100 cases)
8
10
8
10
Malcolm's Glass Problem
10
6
Demand Constraint
Molding Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Storage Constraint
Corner Points
2
6
6-oz profit = 400
Malcolm's Glass Problem
4
4
6-oz Glasses (100 cases)
6
Demand Constraint
Molding Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Storage Constraint
Corner Points
4
2
0
0
0
2
4
6
6-oz Glasses (100 cases)
6-oz profit = 500
Decision Models -- Prof. Juran
8
10
0
2
4
6
6-oz Glasses (100 cases)
6-oz profit = 600
12
If the profit on 6-oz glasses is $540, then the objective
function is exactly parallel to the storage constraint.
Malcolm's Glass Problem
10
10-oz. Glasses (100 cases)
8
6
Demand Constraint
Molding Constraint
Profit = $2000
Profit = $4000
Profit = $6000
Profit = $8000
Storage Constraint
Corner Points
4
2
0
0
2
4
6
8
10
6-oz Glasses (100 cases)
In this situation there are an infinite number of optimal solutions
– every point on the line segment between two corner points.
Decision Models -- Prof. Juran
13
• This allowable increase of $40 can be seen in
the sensitivity report without re-solving the
model.
• Similarly, if the 6-oz. profit drops by $275 or
more, a new corner point will be optimal.
• This section of the report assesses the
robustness of the current optimal solution
with respect to changes in the objective
function coefficients.
Decision Models -- Prof. Juran
14
Multi-Period Models
Example: Traverso Distillery
• Traverso has 1,000 cases on hand of “Mays & McCovey”.
• 2,700 cases capacity with regular-time labor, $40 per case.
• Unlimited capacity with overtime labor, $60 per case.
• Only 80% production yield is “Mays & McCovey” grade.
– (Remaining 20%is sold under the bargain-rate brand “Asterisk 762”. )
• Employees drink or accidentally break 10% of inventory.
• $15 per case cost against ending inventory.
1st Quarter 2nd Quarter 3rd Quarter
Cases to ship:
3000
2000
4000
Decision Models -- Prof. Juran
15
Managerial Formulation
Decision Variables
We need to decide on production quantities, both regular
and overtime, for three quarters (six decisions).
Note that on-hand inventory levels at the end of each
quarter are also being decided, but those decisions will be
implied by the production decisions.
Decision Models -- Prof. Juran
16
Managerial Formulation
Objective Function
We’re trying to minimize the total labor cost of
production, including both regular and overtime labor,
plus inventory cost.
Decision Models -- Prof. Juran
17
Managerial Formulation
Constraints
Upper limit on the number of bottles produced with
regular labor in each quarter.
No backorders are allowed.
Production quantities must be non-negative.
Mathematical relationships:
• Inventory balance equations
• 80% yield on production
• 10% Shrinkage
Decision Models -- Prof. Juran
18
Managerial Formulation
Note that there is also an accounting constraint: Ending Inventory
for each period is defined to be:
Beginning Inventory + Production – Demand
This is not a constraint in the usual Solver sense, but useful to link
the quarters together in this multi-period model.
Decision Models -- Prof. Juran
19
Mathematical Formulation
Decision Variables
Xij = Production of type i in period j.
Let i index labor type; 0 is regular and 1 is overtime.
Let j index quarters; 1 through 3
Decision Models -- Prof. Juran
20
Mathematical Formulation
Non-Decision Variables
Define Ij to be ending inventory for quarter j
1
 = 0.9 −1 +
 − 
=0
Decision Models -- Prof. Juran
21
Mathematical Formulation
Parameters
Define Ci to be the production cost of type i
Define Dj to be demand during quarter j
Decision Models -- Prof. Juran
22
Mathematical Formulation
Objective Function
Minimize
1
3
=
3
  + 15
=0 =1
Decision Models -- Prof. Juran

=1
23
Mathematical Formulation
Constraints
For each quarter,
0 ≤ 2,700
 ≥ 0
Decision Models -- Prof. Juran
24
Solution Methodology
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Beginning Inventory
Regular Time
B
C
D
1st Quarter 2nd Quarter 3rd Quarter
1000
-1799
-3417
1
1
1
Capacity
2700
2700
2700
Overtime
1
1
1
2
2
3000
-1998
-200
-1799
2
2
2000
-3797
-380
-3417
2
2
4000
-7416
-742
-6674
Total Production
Usable for Mays
Demand
Inventory
Breakage, etc.
Ending Inventory
Decision Models -- Prof. Juran
E
F
=C15
=D4+D8
=D10*$H$11
=D2+D11-D12
=D13*$H$12
=D2+D11-D12-D14
G
H
Objective
$ (178,049)
Regular Cost
Overtime Cost
Inventory Cost
$
120
$
180
$ (178,349)
Yield
Shrinkage
Regular cost
Overtime cost
Inventory cost
$
$
$
I
J
=SUM(H4:H6)
=SUM(B4:D4)*H13
=SUM(B8:D8)*H14
=SUM(B15:D15)*H15
0.8
0.1
40.00
60.00
15.00
25
K
Decision Models -- Prof. Juran
26
Decision Models -- Prof. Juran
27
Solution Methodology
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Beginning Inventory
B
C
D
1st Quarter 2nd Quarter 3rd Quarter
1000
0
144
Regular Time
2500
2700
2700
Capacity
2700
2700
2700
Overtime
0
0
2120
2500
2000
3000
0
0
0
2700
2160
2000
160
16
144
4820
3856
4000
0
0
0
Total Production
Usable for Mays
Demand
Inventory
Breakage, etc.
Ending Inventory
Decision Models -- Prof. Juran
E
F
=C15
=D4+D8
=D10*$H$11
=D2+D11-D12
=D13*$H$12
=D2+D11-D12-D14
G
H
Objective
$ 445,360
Regular Cost
Overtime Cost
Inventory Cost
$ 316,000
$ 127,200
$
2,160
Yield
Shrinkage
Regular cost
Overtime cost
Inventory cost
0.8
0.1
40.00
60.00
15.00
$
$
$
I
J
=SUM(H4:H6)
=SUM(B4:D4)*H13
=SUM(B8:D8)*H14
=SUM(B15:D15)*H15
28
K
Optimal Solution
1st Quarter
2nd Quarter
3rd Quarter
2500
2700
2700
0
0
2120
2500
2700
4820
Regular Time
Overtime
Total Production
Decision Models -- Prof. Juran
29
Sensitivity Analysis
Investigate changes in the holding cost, and determine if
Traverso would ever find it optimal to eliminate all
inventory.
Prepare some graphs showing how Traverso’s optimal
decision depends on the holding cost.
Decision Models -- Prof. Juran
30
Decision Models -- Prof. Juran
31
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
1.00
2.00
3.00
4.00
5.00
6.00
7.00
8.00
9.00
10.00
11.00
12.00
13.00
14.00
15.00
16.00
17.00
18.00
19.00
20.00
21.00
22.00
23.00
24.00
25.00
26.00
27.00
28.00
29.00
30.00
H
I
J
1st Quarter2nd Quarter3rd Quarter1st Quarter2nd Quarter3rd Quarter1st Quarter2nd Quarter3rd Quarter
2700
2700
2700
0
0
1958
144
274
0 $
2700
2700
2700
0
0
1958
144
274
0 $
2700
2700
2700
0
0
1958
144
274
0 $
2700
2700
2700
0
0
1958
144
274
0 $
2700
2700
2700
0
0
1958
144
274
0 $
2700
2700
2700
0
0
1958
144
274
0 $
2700
2700
2700
0
0
1958
144
274
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2700
2700
0
0
2120
0
144
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
2500
2500
2700
0
0
2300
0
0
0 $
Decision Models -- Prof. Juran
K
L
M
N
O
P
Sensitivity of Total Cost to Inventory cost
Q
Data for chart
441,480
441,898
442,315
442,733
443,150
443,568
443,986
444,208
444,352
444,496
444,640
444,784
444,928
445,072
445,216
445,360
445,504
445,648
445,792
445,936
446,000
446,000
446,000
446,000
446,000
446,000
446,000
446,000
446,000
446,000
446,000
10
Total Cost
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
G
Total Cost
A
B
C
D
E
F
1 Oneway analysis for Solver model in Model worksheet
2
3 Inventory cost (cell $H$15) values along side, output cell(s) along top
When you select an output from the
dropdown list in cell $M$4, the chart will
adapt to that output.
441480
441897.6
442315.2
442732.8
443150.4
443568
443985.6
444208
444352
444496
444640
444784
444928
445072
445216
445360
445504
445648
445792
445936
446000
446000
446000
446000
446000
446000
446000
446000
446000
446000
446000
32
• Never optimal to hold inventory at end of 3rd quarter
• 1st and 2nd Quarters the optimal level depends on cost
Decision Models -- Prof. Juran
33
Decision Models -- Prof. Juran
34
Decision Models -- Prof. Juran
35
• “Tipping points” are at about $6.287 and $19.444.
Decision Models -- Prof. Juran
36
Sensitivity Analysis
Conclusions:
It is never optimal to completely eliminate overtime,
but sometimes it is optimal to eliminate inventory.
In general, as holding costs increase, Traverso will
decide to reduce inventories and therefore produce
more cases on overtime.
Even if holding costs are reduced to zero, Traverso will
need to produce at least 1958 cases on overtime.
Demand exceeds the total capacity of regular time
production.
Critical cost points at $6.287 and $19.444.
Decision Models -- Prof. Juran
37
Multi-Period Models
Example: Project Funding
It is January 1, 2008. Director of Special Projects Rakesh Parameshwar has a
planned $20.5 million project, which will require the following expected cash
flows between 2008 and 2012:
Date
01-Jul-08
01-Jan-09
01-Jul-09
01-Jan-10
01-Jul-10
01-Jan-11
01-Jul-11
01-Jan-12
Decision Models -- Prof. Juran
Cash Requirement
($ millions)
7.50
4.50
1.00
1.00
1.00
1.00
1.00
3.50
38
Rakesh turns to his Director of Financial Planning, Christine Reyling, and asks her to
ensure that funding is available for the project. Christine is considering buying a
portfolio of bonds, with cash flows from the bonds arranged to coincide with the needs
of Rakesh’s project. The following bonds are available, and can be purchased in any
quantity:
Maturity 01-Jul-08 01-Jan-09 01-Jul-09 01-Jan-10 01-Jul-10 01-Jan-11 01-Jul-11 01-Jan-12
Coupon 7.00%
7.50%
6.75%
0.00%
10.00%
9.00%
10.25% 10.00%
Price
1.00
1.03
1.02
0.81
1.16
1.15
1.23
1.25
What is the minimum cost portfolio of these bonds that will meet the project’s
requirements? Assume that any cash can be reinvested at an annual rate of 4%, and
don’t worry about discounting.
Decision Models -- Prof. Juran
39
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
B
C
D
Reinv
E
0.02
F
G
H
I
J
Purchased
Bonds
8.650
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
Coupon
Price
01-Jul-08
1
7.00%
1.00
01-Jan-09
2
7.50%
1.03
01-Jul-09
3
6.75%
1.02
01-Jan-10
4
0.00%
0.81
01-Jul-10
5
10.00%
1.16
01-Jan-11
6
9.00%
1.15
01-Jul-11
7
10.25%
1.23
01-Jan-12
8
10.00%
1.25
01-Jul-08
01-Jan-09
01-Jul-09
01-Jan-10
01-Jul-10
01-Jan-11
01-Jul-11
01-Jan-12
1.0350
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0450
0.0450
0.0450
0.0450
0.0450
1.0450
0.0000
0.0000
0.0513
0.0513
0.0513
0.0513
0.0513
0.0513
1.0513
0.0000
0.0500
0.0500
0.0500
0.0500
0.0500
0.0500
0.0500
1.0500
Maturity
Periods
1
2
3
4
5
6
7
8
0.0375
0.0338
0.0000
0.0500
=IF($A11>C$6,0,(IF(C$6=$A11,(C$7/2)+1,(C$7/2))))
1.0375
0.0338
0.0000
0.0500
0.0000
1.0338
0.0000
0.0500
0.0000
0.0000
1.0000
0.0500
0.0000
0.0000
0.0000
1.0500
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
Decision Models -- Prof. Juran
40
Decision Models -- Prof. Juran
41
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
26
27
A
B
C
D
Reinv
E
0.02
F
G
H
I
J
Purchased
Bonds
8.650
1.000
1.000
1.000
1.000
1.000
1.000
1.000
1.000
Coupon
Price
01-Jul-08
1
7.00%
1.00
01-Jan-09
2
7.50%
1.03
01-Jul-09
3
6.75%
1.02
01-Jan-10
4
0.00%
0.81
01-Jul-10
5
10.00%
1.16
01-Jan-11
6
9.00%
1.15
01-Jul-11
7
10.25%
1.23
01-Jan-12
8
10.00%
1.25
Periods
1
2
3
4
5
6
7
8
01-Jul-08
01-Jan-09
01-Jul-09
01-Jan-10
01-Jul-10
01-Jan-11
01-Jul-11
01-Jan-12
1.0350
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0375
1.0375
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0338
0.0338
1.0338
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
1.0000
0.0000
0.0000
0.0000
0.0000
0.0500
0.0500
0.0500
0.0500
1.0500
0.0000
0.0000
0.0000
0.0450
0.0450
0.0450
0.0450
0.0450
1.0450
0.0000
0.0000
0.0513
0.0513
0.0513
0.0513
0.0513
0.0513
1.0513
0.0000
0.0500
0.0500
0.0500
0.0500
0.0500
0.0500
0.0500
1.0500
1
2
3
4
5
6
7
8
01-Jul-08
01-Jan-09
01-Jul-09
01-Jan-10
01-Jul-10
01-Jan-11
01-Jul-11
01-Jan-12
Maturity
Cash In from Prev Cash from Bonds Cash to Project Surplus Cash
0.00
1.30
7.50
-6.20
>=
0
-6.32
1.27
4.50
-9.55
>=
0
-9.75
1.23
1.00
-9.52
>=
0
-9.71
1.20
1.00
-9.51
>=
0
-9.70
1.20
1.00
-9.50
>=
0
=F24*(1+$E$1)
-9.69
1.15
1.00
-9.55
>=
0
=SUMPRODUCT($C$3:$J$3,C16:J16)
-9.74
1.10
1.00
-9.64
>=
0
=C27+D27-E27
-9.83
1.05
3.50
-12.28
>=
0
Decision Models -- Prof. Juran
42
Decision Models -- Prof. Juran
43
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
26
27
A
B
C
D
Reinv
E
0.02
F
G
H
I
J
Purchased
Bonds
18.198
11.446
0.000
0.967
7.117
0.000
0.000
0.000
0.000
Coupon
Price
01-Jul-08
1
7.00%
1.00
01-Jan-09
2
7.50%
1.03
01-Jul-09
3
6.75%
1.02
01-Jan-10
4
0.00%
0.81
01-Jul-10
5
10.00%
1.16
01-Jan-11
6
9.00%
1.15
01-Jul-11
7
10.25%
1.23
01-Jan-12
8
10.00%
1.25
Periods
1
2
3
4
5
6
7
8
01-Jul-08
01-Jan-09
01-Jul-09
01-Jan-10
01-Jul-10
01-Jan-11
01-Jul-11
01-Jan-12
1.0350
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0375
1.0375
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0338
0.0338
1.0338
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
1.0000
0.0000
0.0000
0.0000
0.0000
0.0500
0.0500
0.0500
0.0500
1.0500
0.0000
0.0000
0.0000
0.0450
0.0450
0.0450
0.0450
0.0450
1.0450
0.0000
0.0000
0.0513
0.0513
0.0513
0.0513
0.0513
0.0513
1.0513
0.0000
0.0500
0.0500
0.0500
0.0500
0.0500
0.0500
0.0500
1.0500
1
2
3
4
5
6
7
8
01-Jul-08
01-Jan-09
01-Jul-09
01-Jan-10
01-Jul-10
01-Jan-11
01-Jul-11
01-Jan-12
>=
>=
>=
>=
>=
>=
>=
>=
0
0
0
0
0
0
0
0
Maturity
Cash In from Prev Cash from Bonds Cash to Project Surplus Cash
0.00
11.88
7.50
4.38
4.47
0.03
4.50
0.00
0.00
1.00
1.00
0.00
0.00
7.12
1.00
6.12
6.24
0.00
1.00
5.24
5.34
0.00
1.00
4.34
4.43
0.00
1.00
3.43
3.50
0.00
3.50
0.00
Decision Models -- Prof. Juran
44
Summary
• More Sensitivity Analysis
– Solver Sensitivity Report
• More Malcolm
• Multi-period Models
– Distillery Example
– Project Funding Example
Decision Models -- Prof. Juran
45

similar documents