No constraint
Ordering Multiple Products with Demand Uncertainty
High End Mid Range
Retail price, p150$ 100$
Purchase price, c50$ 40$
Salvage price, s35$ 25$
Mean demand, µ1,000 2,000
S.D. of demand, σ300 400
Available Capacity 3,000
Cost of Understocking, Cu100$ 60$
Cost of Overstocking, Co15$ 15$
Critical Fractile, CSL *0.87 0.80
Optimal Order, O*1,337 2,337 3,674
Expected Profit 92,685$ 111,601$
Marginal Profit –$ –$
Total Expected Profit 204,286$
Page 1
capacity constraint
Ordering Multiple Products with Demand Uncertainty under Capacity Constraints
High End Mid Range
Retail price, p150$ 100$
Purchase price, c50$ 40$
Salvage price, s35$ 25$
Mean demand, µ1,000 2,000
S.D. of demand, σ300 400
Available Capacity 3,000
Unconstrained Order, O*1,337 2,337
Total Unconstrained Order 3,674
Optimize
Order qty, O1,000 2,000
Expected Profit 86,236$ 108,032$
Exp. Mar. Profit 42.50$ 22.50$
Total Expected Profit 194,268$
Insert the desired High End quantity in Cell B14. The capacity
constraint will be used to obtain the Mid Range production in
Cell C14. The total profit is provided in Cell B17 and marginal
profits in Cells B16 and C16. If Cell B16 is larger that Cell C16,
increase the quantity in Cell B14. The optimal allocation assigns
1,089 high-end sweaters (Cell B14) and 1,911 mid-range sweaters.
Solving capacitated problem using Solver
High End Mid Range
Retail price, p150$ 100$
Purchase price, c50$ 40$
Salvage price, s35$ 25$
Mean demand, µ1,000 2,000
S.D. of demand, σ300 400
Available Capacity 3,000
Optimize
Order qty, O1,089 1,911
Expected Profit 89,416$ 105,736$
Total Expected Profit 195,152$
Data | Solver and click Solve in the dialog box.
Capacity allocation
Ordering Multiple Products with Demand Uncertainty under Capacity Constraints
High End Mid Range
Retail price 150$ 100$
Purchase price 50$ 40$
Salvage price 35$ 25$
Mean demand 1,000 2,000
S.D. of demand 300 400
Available Capacity 3,000
Marginal Contribution
In the procedure detailed in rows 13 down, production
is assigned to the product with the higher marginal
contribution until all capacity is exhausted. Thus, the
first 100 units of production is assigned to the high–
end product because it has a higher marginal
contribution (99.95 versus 60).
70 31.89 32.76 1,070 1,860
40 31.89 30.63 1,070 1,890
30 30.41 30.63 1,080 1,890
25 30.41 30.26 1,080 1,895
24 30.26 30.26 1,081 1,895
19 30.26 29.90 1,081 1,900
18 30.11 29.90 1,082 1,900
17 29.97 29.90 1,083 1,900
16 29.82 29.90 1,084 1,900
11 29.82 29.54 1,084 1,905
10 29.67 29.54 1,085 1,905
929.53 29.54 1,086 1,905
829.53 29.47 1,086 1,906
729.38 29.47 1,087 1,906
629.38 29.39 1,087 1,907
529.38 29.32 1,087 1,908
429.23 29.32 1,088 1,908
329.23 29.25 1,088 1,909
229.23 29.18 1,088 1,910
129.09 29.18 1,089 1,910
029.09 29.10 1,089 1,911
Page 5