Question

1. Dumoor Appliance Center sells and services several brands of major appliances. Past sales for a particular model of refrigerator have resulted in the following probability distribution for demand:

Demand per week	0	1	2	3
Probability	0.10	0.40	0.40	0.10

The lead time, in weeks, is described by the following distribution:

Lead Time (weeks)	1	2	3
Probability	0.15	0.80	0.05

Based on the cost considerations as well as storage space, the company has decided to order 6 of these each time an order is placed. The holding cost is $2 per week for each unit that is left in inventory at the end of the week. The stockout cost has been set at $30 per stockout. The company has decided to place an order whenever there are only 2 refrigerators left at the end of the week. Simulate 10 weeks of operation for Dumoor Appliance assuming there are currently 5 units in inventory. Determine what the weekly stockout cost and weekly holding cost would be for the problem. (10 points)

Answer 1

The simulation model is following:

F G H K L M Lead Time 1 Prob. 0.15 0.80 0.05 Random Number Range 0 15 16 95 96 100 2 3 Order Quantity Reorder Point 6 2 A B с D E Random Number 1 Demand Prob. Range 2 0 0.10 0 10 3 1 0.40 11 50 4 2 0.40 51 90 5 3 0.10 91 100 6 7 8 Simulation Demand Leadtime Opening Order 9 Week RN RN Inventory Receipts 10 1 4 78 5 0 11 2 56 57 5 0 12 3 41 30 3 0 13 4 76 51 2 0 14 5 45 23 0 0 15 6 61 32 -1 6 16 7 13 23 3 0 17 8 60 98 2 0 18 9 47 35 0 0 19 10 23 59 -1 6 20 @ $ 2 Holding cost 10 6 4 @ $ 30 Stockout Cost 0 0 Daily Closing Qty Demand Inventory Ordered Lead Time shortage 0 5 0 0 0 2 3 0 0 0 1 2 6 2 0 2 0 0 0 0 1 -1 0 0 1 2 3 0 0 0 1 2 6 2 0 2 0 0 0 0 1 -1 0 0 1 1 4 0 0 0 Average weekly cost = 0 0 6 4 0 0 0 30 0 0 0 0 30 0 $6.00 8 $ 3.80 21 22 23

EXCEL FORMULA:

А B с D E G H K Prob. Random Number Range 0.15 0.8 0.05 0 =J2+1 =J3+1 =H2*100+12 =H3*100+13-1 =H4*100+14-1 4 6 2 2 1 Demand Prob. Random Number Range Lead Time 2 0 0.1 0 =C2*100+D2 3 1 0.4 =E2+1 =C3*100+D3-1 2 2 0.4 =E3+1 =C4*100+D4-1 5 3 0.1 =E4+1 =C5*100+D5-1 Order Quantity Reorder Point 8 Simulation Opening Closing 9 Week Demand RN Leadtime RN Inventor Order Receipts Daily Demand Inventory 10 1 =RANDBETWEEN(1,100) =RANDBETWEEN(1,100) 5 0 =LOOKUP(B10,$D$2:$D$6,$B$2:$B$6) =D10+E10-F10 11 2 =RANDBETWEEN(1,100) =RANDBETWEEN (1,100) =G10 JO =LOOKUP(B11, $D$2:$D$6,$B$2:$B$6) =D11+E11-F11 12 3 =RANDBETWEEN(1,100) =RANDBETWEEN(1,100) =G11 =IF(110=1, H10,0) =LOOKUP(B12, $D$2:$D$6,$B$2:$B$6) =D12+E12-F12 13 4 =RANDBETWEEN(1,100)=RANDBETWEEN (1,100) =G12 =IF(111=1, H11,0)+IF(110=2,H10,0) =LOOKUP(B13, $D$2:$D$6,$B$2:$B$6) =D13+E13-F13 14 5 =RANDBETWEEN (1,100) =RANDBETWEEN (1,100) =G13 =IF(112=1, H12,0)+IF(111=2, H11,0)+1F(110=3,H10,0)=LOOKUP(B14,$D$2:$D$6,$B$2:$B$6) =D14+E14-F14 15 6 |=RANDBETWEEN (1,100) =RANDBETWEEN (1,100) =G14 =1F(113=1, H13,0)+IF(112=2, H12,0)+IF(111=3,H11,0) =LOOKUP(B15, $D$2:$D$6,$B$2:$B$6) =D15+E15-F15 16 7 =RANDBETWEEN(1,100)=RANDBETWEEN(1,100) =G15 =IF(114=1, H14,0)+IF(113=2,H13,0)+IF(112=3, H12,0) =LOOKUP(B16, $D$2:$D$6,$B$2:$B$6) =D16+E16-F16 17 8 =RANDBETWEEN(1,100)=RANDBETWEEN(1,100) =G16 =IF(115=1, H15,0)+IF(114=2,H14,0)+IF(113=3, H13,0) =LOOKUP(B17,$D$2:$D$6, $B$2:$B$6) =D17+E17-F17 18 9 =RANDBETWEEN(1,100) =RANDBETWEEN(1,100) =G17 =IF(116=1, H16,0)+IF(115=2,H15,0)+IF(114=3,H14,0) =LOOKUP(B18, $D$2:$D$6,$B$2:$B$6) =D18+E18-F18 19 10 =RANDBETWEEN(1,100) =RANDBETWEEN (1,100) =G18 =IF(117=1, H17,0)+1F(116=2, H16,0)+IF(115=3,H15,0)=LOOKUP(B19,$D$2:$D$6,$B$2:$B$6) =D19+E19-F19 20 21 22 23 Qty Ordered Lead Time Shortage Holding cost =IF(G10<=$I$7,$I$6,0) =IF(H10=0,0,LOOKUP(C10, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G10) |=MAX(0, G10) "$K$8 =IF(G11+H10<=$I$7, $I$6,0) I=IF(H11=0,0,LOOKUP(C11, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G11) =MAX(0, G11)*$K$8 =IF(G12+H11+H10<=$1$7,$I$6,0) =IF(H12=0, 0, LOOKUP(C12,$I$2:$I$4,$G$2:$G$4)=MAX(0,-G12) =MAX(0, G12)"$K$8 =IF(G13+H12+H11+H10<=$I$7,$I$6,0) =IF(H13=0,0,LOOKUP(C13, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G13) =MAX(0, G13)*$K$8 =IF(G14+H13+H12+H11<=$I$7,$I$6,0) =IF(H14=0,0,LOOKUP(C14, $I$2:$1$4,$G$2:$G$4)) =MAX(0,-614)=MAX(0,614) *$K$8 =IF(G15+H14+H13+H12<=$I$7,$I$6,0) =IF(H15=0,0,LOOKUP(C15, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G15) =MAX(0, G15)*$K$8 =IF(G16+H15+H14+H13<=$I$7,$I$6,0) =IF(H16=0,0,LOOKUP(C16, $I$2:$I$4,$G$2:$G$4)=MAX(0,-G16) =MAX(O, G16)*$K$8 =IF(G17+H16+H15+H14<=$I$7,SI$6,0) =IF(H17=0,0,LOOKUP(C17, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G17) =MAX(0, G17)*$K$8 |=IF(G18+H17+H16+H15<=$I$7,$I$6,0) =IF(H18=0,0,LOOKUP(C18, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G18) |=MAX(0, G18)*$K$8 |=IF(G19+H18+H17+H16<=$I$7,$I$6,0) =IF(H19=0,0,LOOKUP(C19, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G19) =MAX(0, G19) *$K$8 Average weekly cost = =AVERAGE(K10:K19)

D H 1 Prob. Random Number Range 0.15 0.8 0.05 0 =J2+1 =J3+1 =H2*100+12 =H3*100+13-1 =H4*100+14-1 6 2 30 Random Number Range Lead Time lo =C2*100+D2 3 =E2+1 =C3*100+D3-1 2 4 =E3+1 =C4*100+D4-1 3 5 =E4+1 =C5*100+D5-1 6 Order Quantity 7 Reorder Point 8 Opening Closing 9 Inventor Order Receipts Daily Demand Inventory 10 5 0 =LOOKUP(B10, $D$2:$D$6,$B$2:$B$6) =D10+E10-F10 11 =G10 0 =LOOKUP(B11, $D$2:$D$6,$B$2:$B$6) =D11+E11-F11 12 =G11 =IF(110=1, H10,0) =LOOKUP(B12, $D$2:$D$6, $B$2:$B$6) =D12+E12-F12 13 =G12 =IF(111=1, H11,0)+1F(110=2, H10,0) =LOOKUP(B13, $D$2:$D$6,$B$2:$B$6) =D13+E13-F13 14 =G13 =IF(112=1, H12,0)+1F(111=2, H11,0)+1F(110=3,H10,0) =LOOKUP(B14, $D$2:$D$6,$B$2:$B$6) =D14+E14-F14 15 =G14 =IF(113=1, H13,0)+IF(112=2, H12,0)+IF(111=3, H11,0) =LOOKUP(B15, $D$2:$D$6,$B$2:$B$6) =D15+15-F15 16 =G15 =IF(114=1, H14,0)+IF(113=2,H13,0)+IF(112=3,H12,0) =LOOKUP(B16, $D$2:$D$6,$B$2:$B$6) =D16+E16-F16 17 =G16 =IF(115=1, H15,0)+IF(114=2, H14,0)+IF(113=3, H13,0) =LOOKUP(B17, $D$2:$D$6, $B$2:$B$6) =D17+E17-F17 18 =G17 =IF(116=1, H16,0)+IF(115=2,H15,0)+IF(114=3,H14,0) =LOOKUP(B18, $D$2:$D$6,$B$2:$B$6) =D18+E18-F18 19 =G18 =IF(117=1, H17,0)+IF(116=2, H16,0)+IF(115=3,H15,0) =LOOKUP(B19, $D$2:$D$6, $B$2:$B$6) =D19+E19-F19 20 21 22 23 Qty Ordered Lead Time Shortage Holding cost Stockout Cost =IF(G10<=$1$7,$1$6,0) =1F/H10=0,0, LOOKUP(C10, $I$2:$I$4,$G$2:$G$4))=MAX(0,-G10)_1=MAX(0,610) *$K$8 =1F(J10>0,$L$8,0) |=IF(G11+H10<=$I$7, $I$6,0) |=IF(H11=0,0,LOOKUP(C11, $I$2:$1$4,$G$2:$G$4)) =MAX(0,-G11) =MAX(O, G11)*$K$8 FIF(J11>0,$L$8,0) =IF(G12+H11+H10<=$1$7,$1$6,0) |=IF(H12=0,0,LOOKUP(C12,$I$2:$I$4,$G$2:$G$4)) =MAX(0,-G12) =MAX(0, G12)*$K$8 =IF(J12>0, $L$8,0) |=IF(G13+H12+H11+H10<=$I$7,$I$6,0) =IF(H13=0,0,LOOKUP(C13, $I$2:$I$4,$G$2:$G$4)=MAX(0,-G13) =MAX(0, G13)*$K$8 =IF(J13>0, $L$8,0) |=1F(G14+H13+H12+H11<=$I$7,$I$6,0) =IF(H14=0,0,LOOKUP(C14, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-614) =MAX(O, G14)*$K$8 =IF(J14>0,$L$8,0) =IF(G15+H14+H13+H12<=$I$7,$I$6,0) =IF(H15=0,0,LOOKUP(C15, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G15) =MAX(0, G15)*$K$8 =1F(J15>0, $L$8,0) =IF(G16+H15+H14+H13<=$I$7, $I$6,0) =IF(H16=0,0,LOOKUP(C16, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-616) =MAX(O, G16)"$K$8 =IF(J16>0, $L$8,0) =IF(G17+H16+H15+H14<=$I$7,$I$6,0) =IF(H17=0,0,LOOKUP(C17,$I$2:$I$4,$G$2:$G$4)) =MAX(0,-G17) =MAX(O, G17)*$K$8 =IF(J17>0, $L$8,0) |=IF(G18+H17+H16+H15<=$I$7,$I$6,0) =IF(H18=0,0,LOOKUP(C18, $I$2:$I$4,$G$2:$G$4))=MAX(0,-G18) =MAX(0, G18)*$K$8 =1F(J18>0,$L$8,0) =IF(G19+H18+H17+H16<=$I$7, $I$6,0) =IF(H19=0,0,LOOKUP(C19, $I$2:$I$4,$G$2:$G$4)) =MAX(0,-G19) =MAX(O, G19)*$K$8 =IF(J19>0, $L$8,0) Average weekly cost = =AVERAGE(K10:K19) =AVERAGE(L10:L19)