supply chain exercise
DESCRIPTION
Supply Chain exerciseTRANSCRIPT
Newsvendor model with normally distributed demand
200125
Optimal Order Quantity Performance Measures
12
35
0.7447z #VALUE! 0.6579037Q #VALUE! 282.23797
Order quantity to achieve a target in-stock probability Stockout probability:Target in-stock 0.9500
z #VALUE!
Q #VALUE!Expected profit
Order Quantity to achieve a target fill rate Maximum profitTarget fill rate 99% Mismatch costTarget lost sales 0.016z #VALUE!Q #VALUE!
Inputs are in red, outputs in blackMean demand, m :Standard deviation of demand, s :
Overage penalty, Co : Order quantity, Q :
Underage penalty, Cu : Standard Normal order quantity, z :
Critical ratio: Cu / (Co + Cu) Expected Lost Sales, L(Q) :Expected Sales, S(Q) :Expected Left Over Inventory, V(Q) :Fill rate, f :
Overage penalty, Co :
Underage penalty, Cu :
Purchase Price 100 100Cost 65 Cost 55Salvage Value 53 0
17875 11000
275 6875 5912
0.60 5088
#VALUE! 18.8375#VALUE! #VALUE! #VALUE!#VALUE! #VALUE! #VALUE!#VALUE! #VALUE!
27.43%
12
35#VALUE!7000.00
#VALUE!
Newsvendor model with normally distributed demand
200125
Optimal Order Quantity Performance Measures
55
45
0.4500z #VALUE! -0.1256613Q #VALUE! 184.29233
Order quantity to achieve a target in-stock probability Stockout probability:Target in-stock 0.9500
z #VALUE!
Q #VALUE!Expected profit
Order Quantity to achieve a target fill rate Maximum profitTarget fill rate 99% Mismatch costTarget lost sales 0.016z #VALUE!Q #VALUE!
Inputs are in red, outputs in blackMean demand, m :Standard deviation of demand, s :
Overage penalty, Co : Order quantity, Q :
Underage penalty, Cu : Standard Normal order quantity, z :
Critical ratio: Cu / (Co + Cu) Expected Lost Sales, L(Q) :Expected Sales, S(Q) :Expected Left Over Inventory, V(Q) :Fill rate, f :
Overage penalty, Co :
Underage penalty, Cu :
Purchase Price 100 100Cost 55 Cost 55Salvage Value 0 0
184
-0.13
#VALUE! 58.4125 18.8375#VALUE! #VALUE! #VALUE!#VALUE! #VALUE! #VALUE!#VALUE! #VALUE!
55.17%
55
45#VALUE!9000.00
#VALUE!
Newsvendor model with Poisson distributed demand
9
Optimal Order Quantity Performance Measures
0.05
0.35
0.8750Q #VALUE!
Order quantity to achieve a target in-stock probability Stockout probability:Target in-stock 0.9500
Q #VALUE!
Order Quantity to achieve a target fill rate Expected profitTarget fill rate 99% Maximum profitTarget lost sales 0.09 Mismatch costQ #VALUE!
Inputs are in red, outputs in blackMean demand, m :
Overage penalty, Co : Order quantity, Q :
Underage penalty, Cu : Expected Lost Sales, L(Q) :
Critical ratio: Cu / (Co + Cu) Expected Sales, S(Q) :Expected Left Over Inventory, V(Q) :Fill rate, f :
Overage penalty, Co :
Underage penalty, Cu :
8
#VALUE!
#VALUE!#VALUE!#VALUE!
45.57%
0.05
0.35#VALUE!
3.15#VALUE!
Functions for evaluating the newsvendor and order up-to models
lz(z) Return the standard normal loss function for the z-statistic z. Returns 4 significant digits.Phi(z ) Return Prob(D<=z), where D is a standard normal. Returns 4 significant digits.
loss_normal(q , mu , sigma ) Return expected lost sales with a normal distributionloss_poisson(s, mu ) Return expected lost sales with a Poisson distribution
find_z(target) Return the z in the Standard Normal Distribution Function Table such that Phi(z) = target. Uses the round up rule.find_s_poisson(mu, target) Return the S in the Poisson Distribution Function Table such that F(S) = target. Uses the round-up rule.
find_lz(target): Return the z in the Standard Normal Loss Function Table such that LZ(z) = target. Uses the round up rule.find_s_loss_poisson(mu , target ) Return the S in the Poisson Loss Function Table such that L(S) = target. Uses the round-up rule.
opt_order_normal(mu , sigma, co, cu) Return the quantity, Q, such that F(Q) = critical ratio = cu / (co+cu).
sales(mu, loss) Return expected salesleftover(q , mu , loss ) Return expected left over inventoryprofit(q , mu , loss , co , cu ) Return expected profit fillrate(mu , loss ) Return expected fill raterevenue(q , mu , loss , price , v ) Return expected revenue
Notation:q or s either an order quantity (q) or an order up-to level (s)mu mean of the distributionsigma standard deviation of the distributionz standard normal z-statisticco overage costcu underage costv salvage value
Return the standard normal loss function for the z-statistic z. Returns 4 significant digits.Return Prob(D<=z), where D is a standard normal. Returns 4 significant digits.
Return the z in the Standard Normal Distribution Function Table such that Phi(z) = target. Uses the round up rule.Return the S in the Poisson Distribution Function Table such that F(S) = target. Uses the round-up rule.
Return the z in the Standard Normal Loss Function Table such that LZ(z) = target. Uses the round up rule.Return the S in the Poisson Loss Function Table such that L(S) = target. Uses the round-up rule.