Select Page

week_3_assignment___case_study_screen_shot.png

tom_salmons___week_3_assignment.xlsx

mis_665_rs_topic_3_case_study_template.xlsx

Unformatted Attachment Preview

Tom Salmons – Part I _Answer
Ebony Bath Soap Solution
Question 1)
See the Simulation sheet, columns A-H, for the solution to the 52-week simulation. The major components to calculating the
inventory calculation and production level setting.
Column D tracks the inventory which is calculated as last week’s inventory plus this week’s production (which was set last w
larger. This insures that inventory cannot be negative, and thus, no backorders.
Column E indicates the production level to be set for the following week. A nested IF statement is used. The first check is t
30). If so, next week’s production level is set to 130. If not, the inventory level is checked to whether is greater than u (
Otherwise, the production level is unchanged.
All that remains is to calculate the inventory and production change cost. The inventory cost calculated in column F is simpl
week’s inventory. Calculating the production change cost in column G is more challenging. The production change cost (here
used to check if the production level has been changed. If there was a change, 3000 is multiplied by one, otherwise, if no ch
simply the sum of the two costs for the week. Cell H9 totals the costs over the year.
Page 1
Tom Salmons – Part I _Answer
Question 2)
Risk is used to simulate 500 iterations of each of 6 values of U (those in the range J13:J18), using a RiskSimtable function in c
The Summary Report shows the results, some of which are copied to the Simulation sheet.
Question 3)
The mean, standard deviation and confidence intervals for each value of U is tabulated in the Simulation sheet. The smallest
U= 60, although this could change if the simulation were done with different random numbers. A plot of the mean annual co
Mean Annual Cost
Page 2
Tom Salmons – Part I _Answer
Mean Annual Cost
\$116,000
\$114,000
\$112,000
\$110,000
\$108,000
\$106,000
\$104,000
\$102,000
\$100,000
\$98,000
\$96,000
30
40
50
60
70
80
U
Question 4)
Other values of U and L could be tested. Note that the policy as stated never returns to a production level of 120 onve the pr
be investigated which return to a 120 production level. For example, another policy would be to produce 120 units if invento
Page 3
Tom Salmons – Part I _Answer
major components to calculating the cost for a given week are the demand generation,
k’s production (which was set last week) minus this week’s demand or zero, whichever is
atement is used. The first check is to see whether this week’s inventory is less than l (here,
d to whether is greater than u (here, 80). If so, next week’s production level is set to 110.
cost calculated in column F is simply the per unit inventory cost (here, 30) multiplied by this
ng. The production change cost (here, 3000) is multiplied by the reusult of an IF statement is
multiplied by one, otherwise, if no change occured, then 3000 is multiplied by 0. Column H is
Page 4
Tom Salmons – Part I _Answer
Page 5
Tom Salmons – Part I _Answer
Page 6
Tom Salmons – Simulation
Ebony Bath Soap Simulation
Inputs
Average demand
Stdev of demand
Unit holding cost
Prod change cost
Initial inventory
Current prod level
Production policy:
If inventory < If inventory >
Otherwise, don’t change production level.
120
15
\$30
\$3,000
60
120
Simulation of 52 weeks
Week
Normal
0
1 =RiskNormal(\$B\$4,\$B\$5
2 =RiskNormal(\$B\$4,\$B\$5)
3 =RiskNormal(\$B\$4,\$B\$5)
4 =RiskNormal(\$B\$4,\$B\$5)
5 =RiskNormal(\$B\$4,\$B\$5)
6 =RiskNormal(\$B\$4,\$B\$5)
7 =RiskNormal(\$B\$4,\$B\$5)
8 =RiskNormal(\$B\$4,\$B\$5)
9 =RiskNormal(\$B\$4,\$B\$5)
10 =RiskNormal(\$B\$4,\$B\$5)
11 =RiskNormal(\$B\$4,\$B\$5)
12 =RiskNormal(\$B\$4,\$B\$5)
13 =RiskNormal(\$B\$4,\$B\$5)
14 =RiskNormal(\$B\$4,\$B\$5)
15 =RiskNormal(\$B\$4,\$B\$5)
16 =RiskNormal(\$B\$4,\$B\$5)
17 =RiskNormal(\$B\$4,\$B\$5)
18 =RiskNormal(\$B\$4,\$B\$5)
19 =RiskNormal(\$B\$4,\$B\$5)
20 =RiskNormal(\$B\$4,\$B\$5)
21 =RiskNormal(\$B\$4,\$B\$5)
22 =RiskNormal(\$B\$4,\$B\$5)
23 =RiskNormal(\$B\$4,\$B\$5)
24 =RiskNormal(\$B\$4,\$B\$5)
25 =RiskNormal(\$B\$4,\$B\$5)
26 =RiskNormal(\$B\$4,\$B\$5)
27 =RiskNormal(\$B\$4,\$B\$5)
28 =RiskNormal(\$B\$4,\$B\$5)
29 =RiskNormal(\$B\$4,\$B\$5)
30 =RiskNormal(\$B\$4,\$B\$5)
31 =RiskNormal(\$B\$4,\$B\$5)
Demand
=ROUND(MAX(B14,0),0)
=ROUND(MAX(B15,0),0)
=ROUND(MAX(B16,0),0)
=ROUND(MAX(B17,0),0)
=ROUND(MAX(B18,0),0)
=ROUND(MAX(B19,0),0)
=ROUND(MAX(B20,0),0)
=ROUND(MAX(B21,0),0)
=ROUND(MAX(B22,0),0)
=ROUND(MAX(B23,0),0)
=ROUND(MAX(B24,0),0)
=ROUND(MAX(B25,0),0)
=ROUND(MAX(B26,0),0)
=ROUND(CMAX(B27,0),0)
=ROUND(MAX(B28,0),0)
=ROUND(MAX(B29,0),0)
=ROUND(MAX(B30,0),0)
=ROUND(MAX(B31,0),0)
=ROUND(MAX(B32,0),0)
=ROUND(CMAX(B33,0),0)
=ROUND(MAX(B34,0),0)
=ROUND(MAX(B35,0),0)
=ROUND(MAX(B36,0),0)
=ROUND(MAX(B37,0),0)
=ROUND(MAX(B38,0),0)
=ROUND(MAX(B39,0),0)
=ROUND(MAX(B40,0),0)
=ROUND(MAX(B41,0),0)
=ROUND(MAX(B42,0),0)
=ROUND(MAX(B43,0),0)
=ROUND(MAX(B44,0),0)
Page 7
Inventory
=B8
=MAX(D13+E13-C14,0)
=MAX(D14+E14-C15,0)
=MAX(D15+E15-C16,0)
=MAX(D16+E16-C17,0)
=MAX(D16D17+E17-C18,0)
=MAX(D18+E18-C19,0)
=MAX(D19+E19-C20,0)
=MAX(D20+E20-C21,0)
=MAX(D21+E21-C22,0)
=MAX(D22+E22-C23,0)
=MAX(D23+E23-C24,0)
=MAX(D24+E24-C25,0)
=MAX(D25+E25-C26,0)
=MAX(D26+E26-C27,0)
=MAX(D27+E27-C28,0)
=MAX(D28+E28-C29,0)
=MAX(D29+E29-C30,0)
=MAX(D30+E30-C31,0)
=MAX(D31+E31-C32,0)
=MAX(D32+E32-C33,0)
=MAX(D33+E33-C34,0)
=MAX(D34+E34-C35,0)
=MAX(D35+E35-C36,0)
=MAX(D36+E36-C37,0)
=MAX(D37+E37-C38,0)
=MAX(D38+E38-C39,0)
=MAX(D39+E39-C40,0)
=MAX(D40+E40-C41,0)
=MAX(D41+E41-C42,0)
=MAX(D42+E42-C43,0)
=MAX(D43+E43-C44,0)
Tom Salmons – Simulation
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=RiskNormal(\$B\$4,\$B\$5)
=ROUND(CMAX(B45,0),0)
=ROUND(MAX(B46,0),0)
=ROUND(MAX(B47,0),0)
=ROUND(MAX(B48,0),0)
=ROUND(MAX(B49,0),0)
=ROUND(MAX(B50,0),0)
=ROUND(MAX(B51,0),0)
=ROUND(MAX(B52,0),0)
=ROUND(MAX(B53,0),0)
=ROUND(MAX(B54,0),0)
=ROUND(MAX(B55,0),0)
=ROUND(MAX(B56,0),0)
=ROUND(MAX(B57,0),0)
=ROUND(MAX(B58,0),0)
=ROUND(MAX(B59,0),0)
=ROUND(MAX(B60,0),0)
=ROUND(MAX(B61,0),0)
=ROUND(MAX(B62,0),0)
=ROUND(MAX(B63,0),0)
=ROUND(MAX(B64,0),0)
=ROUND(MAX(B65,0),0)
Page 8
=MAX(D44+E44-C45,0)
=MAX(D45+E45-C46,0)
=MAX(D46+E46-C47,0)
=MAX(D47+E47-C48,0)
=MAX(D48+E48-C49,0)
=MAX(D49+E49-C50,0)
=MAX(D50+E50-C51,0)
=MAX(D51+E51-C52,0)
=MAX(D52+E52-C53,0)
=MAX(D53+E53-C54,0)
=MAX(D54+E54-C55,0)
=MAX(D55+E55-C56,D0)
=MAX(D56+E56-C57,0)
=MAX(D57+E57-C58,0)
=MAX(D58+E58-C59,0)
=MAX(D59+E59-C60,0)
=MAX(D60+E60-C61,0)
=MAX(D61+E61-C62,0)
=MAX(D62+E62-C63,0)
=MAX(D63+E63-C64,0)
=MAX(D64+E64-C65,0)
Tom Salmons – Simulation
#NAME?
30 then produce
then produce
130
110
t change production level.
Annual cost
Next week
Production
=B9
=IF(D14<\$E\$4,\$G\$4,IF(D14>\$E\$5,\$G\$5,E13))
=IF(D15<\$E\$4,\$G\$4,IF(D15>\$E\$5,\$G\$5,E14))
=IF(D16<\$E\$4,\$G\$4,IF(D16>\$E\$5,\$G\$5,E15))
=IF(D17<\$E\$4,\$G\$4,IF(D17>\$E\$5,\$G\$5,E16))
=IF(D18<\$E\$4,\$G\$4,IF(D18>\$E\$5,\$G\$5,E17))
=IF(D19<\$E\$4,\$G\$4,IF(D19>\$E\$5,\$G\$5,E18))
=IF(D20<\$E\$4,\$G\$4,IF(D20>\$E\$5,\$G\$5,E19))
=IF(D21<\$E\$4,\$G\$4,IF(D21>\$E\$5,\$G\$5,E20))
=IF(D22<\$E\$4,\$G\$4,IF(D22>\$E\$5,\$G\$5,E21))
=IF(D23<\$E\$4,\$G\$4,IF(D23>\$E\$5,\$G\$5,E22))
=IF(D24<\$E\$4,\$G\$4,IF(D24>\$E\$5,\$G\$5,E23))
=IF(D25<\$E\$4,\$G\$4,IF(D25>\$E\$5,\$G\$5,E24))
=IF(D26<\$E\$4,\$G\$4,IF(D26>\$E\$5,\$G\$5,E25))
=IF(D27<\$E\$4,\$G\$4,IF(D27>\$E\$5,\$G\$5,E26))
=IF(D28<\$E\$4,\$G\$4,IF(D28>\$E\$5,\$G\$5,E27))
=IF(D29<\$E\$4,\$G\$4,IF(D29>\$E\$5,\$G\$5,E28))
=IF(D30<\$E\$4,\$G\$4,IF(D30>\$E\$5,\$G\$5,E29))
=IF(D31<\$E\$4,\$G\$4,IF(D31>\$E\$5,\$G\$5,E30))
=IF(D32<\$E\$4,\$G\$4,IF(D32>\$E\$5,\$G\$5,E31))
=IF(D33<\$E\$4,\$G\$4,IF(D33>\$E\$5,\$G\$5,E32))
=IF(D34<\$E\$4,\$G\$4,IF(D34>\$E\$5,\$G\$5,E33))
=IF(D35<\$E\$4,\$G\$4,IF(D35>\$E\$5,\$G\$5,E34))
=IF(D36<\$E\$4,\$G\$4,IF(D36>\$E\$5,\$G\$5,E35))
=IF(D37<\$E\$4,\$G\$4,IF(D37>\$E\$5,\$G\$5,E36))
=IF(D38<\$E\$4,\$G\$4,IF(D38>\$E\$5,\$G\$5,E37))
=IF(D39<\$E\$4,\$G\$4,IF(D39>\$E\$5,\$G\$5,E38))
=IF(D40<\$E\$4,\$G\$4,IF(D40>\$E\$5,\$G\$5,E39))
=IF(D41<\$E\$4,\$G\$4,IF(D41>\$E\$5,\$G\$5,E40))
=IF(D42<\$E\$4,\$G\$4,IF(D42>\$E\$5,\$G\$5,E41))
=IF(D43<\$E\$4,\$G\$4,IF(D43>\$E\$5,\$G\$5,E42))
=IF(D44<\$E\$4,\$G\$4,IF(D44>\$E\$5,\$G\$5,E43))
Holding cost
=D14*\$B\$6
=D15*\$B\$6
=D16*\$B\$6
=D17*\$B\$6
=D18*\$B\$6
=D19*\$B\$6
=D20*\$B\$6
=D21*\$B\$6
=D22*\$B\$6
=D23*\$B\$6
=D24*\$B\$6
=D25*\$B\$6
=D26*\$B\$6
=D27*\$B\$6
=D28*\$B\$6
=D29*\$B\$6
=D30*\$B\$6
=D31*\$B\$6
=D32*\$B\$6
=D33*\$B\$6
=D34*\$B\$6
=D35*\$B\$6
=D36*\$B\$6
=D37*\$B\$6
=D38*\$B\$6
=D39*\$B\$6
=D40*\$B\$6
=D41*\$B\$6
=D42*\$B\$6
=D43*\$B\$6
=D44*\$B\$6
Page 9
#NAME?
Change cost
=\$B\$7*IF(E14<>E13,1,0)
=\$B\$7*IF(E15<>E14,1,0)
=\$B\$7*IF(E16<>E15,1,0)
=\$B\$7*IF(E17<>E16,1,0)
=\$B\$7*IF(E18<>E17,1,0)
=\$B\$7*IF(E19<>E18,1,0)
=\$B\$7*IF(E20<>E19,1,0)
=\$B\$7*IF(E21<>E20,1,0)
=\$B\$7*IF(E22<>E21,1,0)
=\$B\$7*IF(E23<>E22,1,0)
=\$B\$7*IF(E24<>E23,1,0)
=\$B\$7*IF(E25<>E24,1,0)
=\$B\$7*IF(E26<>E25,1,0)
=\$B\$7*IF(E27<>E26,1,0)
=\$B\$7*IF(E28<>E27,1,0)
=\$B\$7*IF(E29<>E28,1,0)
=\$B\$7*IF(E30<>E29,1,0)
=\$B\$7*IF(E31<>E30,1,0)
=\$B\$7*IF(E32<>E31,1,0)
=\$B\$7*IF(E33<>E32,1,0)
=\$B\$7*IF(E34<>E33,1,0)
=\$B\$7*IF(E35<>E34,1,0)
=\$B\$7*IF(E36<>E35,1,0)
=\$B\$7*IF(E37<>E36,1,0)
=\$B\$7*IF(E38<>E37,1,0)
=\$B\$7*IF(E39<>E38,1,0)
=\$B\$7*IF(E40<>E39,1,0)
=\$B\$7*IF(E41<>E40,1,0)
=\$B\$7*IF(E42<>E41,1,0)
=\$B\$7*IF(E43<>E42,1,0)
=\$B\$7*IF(E44<>E43,1,0)
Weekly cost
=F14+G14
=F15+G15
=F16+G16
=F17+G17
=F18+G18
=F19+G19
=F20+G20
=F21+G21
=F22+G22
=F23+G23
=F24+G24
=F25+G25
=F26+G26
=F27+G27
=F28+G28
=F29+G29
=F30+G30
=F31+G31
=F32+G32
=F33+G33
=F34+G34
=F35+G35
=F36+G36
=F37+G37
=F38+G38
=F39+G39
=F40+G40
=F41+G41
=F42+G42
=F43+G43
=F44+G44
Tom Salmons – Simulation
=IF(D45<\$E\$4,\$G\$4,IF(D45>\$E\$5,\$G\$5,E44))
=IF(D46<\$E\$4,\$G\$4,IF(D46>\$E\$5,\$G\$5,E45))
=IF(D47<\$E\$4,\$G\$4,IF(D47>\$E\$5,\$G\$5,E46))
=IF(D48<\$E\$4,\$G\$4,IF(D48>\$E\$5,\$G\$5,E47))
=IF(D49<\$E\$4,\$G\$4,IF(D49>\$E\$5,\$G\$5,E48))
=IF(D50<\$E\$4,\$G\$4,IF(D50>\$E\$5,\$G\$5,E49))
=IF(D51<\$E\$4,\$G\$4,IF(D51>\$E\$5,\$G\$5,E50))
=IF(D52<\$E\$4,\$G\$4,IF(D52>\$E\$5,\$G\$5,E51))
=IF(D53<\$E\$4,\$G\$4,IF(D53>\$E\$5,\$G\$5,E52))
=IF(D54<\$E\$4,\$G\$4,IF(D54>\$E\$5,\$G\$5,E53))
=IF(D55<\$E\$4,\$G\$4,IF(D55>\$E\$5,\$G\$5,E54))
=IF(D56<\$E\$4,\$G\$4,IF(D56>\$E\$5,\$G\$5,E55))
=IF(D57<\$E\$4,\$G\$4,IF(D57>\$E\$5,\$G\$5,E56))
=IF(D58<\$E\$4,\$G\$4,IF(D58>\$E\$5,\$G\$5,E57))
=IF(D59<\$E\$4,\$G\$4,IF(D59>\$E\$5,\$G\$5,E58))
=IF(D60<\$E\$4,\$G\$4,IF(D60>\$E\$5,\$G\$5,E59))
=IF(D61<\$E\$4,\$G\$4,IF(D61>\$E\$5,\$G\$5,E60))
=IF(D62<\$E\$4,\$G\$4,IF(D62>\$E\$5,\$G\$5,E61))
=IF(D63<\$E\$4,\$G\$4,IF(D63>\$E\$5,\$G\$5,E62))
=IF(D64<\$E\$4,\$G\$4,IF(D64>\$E\$5,\$G\$5,E63))
=IF(D65<\$E\$4,\$G\$4,IF(D65>\$E\$5,\$G\$5,E64))
=D45*\$B\$6
=D46*\$B\$6
=D47*\$B\$6
=D48*\$B\$6
=D49*\$B\$6
=D50*\$B\$6
=D51*\$B\$6
=D52*\$B\$6
=D53*\$B\$6
=D54*\$B\$6
=D55*\$B\$6
=D56*\$B\$6
=D57*\$B\$6
=D58*\$B\$6
=D59*\$B\$6
=D60*\$B\$6
=D61*\$B\$6
=D62*\$B\$6
=D63*\$B\$6
=D64*\$B\$6
=D65*\$B\$6
Page 10
=\$B\$7*IF(E45<>E44,1,0)
=\$B\$7*IF(E46<>E45,1,0)
=\$B\$7*IF(E47<>E46,1,0)
=\$B\$7*IF(E48<>E47,1,0)
=\$B\$7*IF(E49<>E48,1,0)
=\$B\$7*IF(E50<>E49,1,0)
=\$B\$7*IF(E51<>E50,1,0)
=\$B\$7*IF(E52<>E51,1,0)
=\$B\$7*IF(E53<>E52,1,0)
=\$B\$7*IF(E54<>E53,1,0)
=\$B\$7*IF(E55<>E54,1,0)
=\$B\$7*IF(E56<>E55,1,0)
=\$B\$7*IF(E57<>E56,1,0)
=\$B\$7*IF(E58<>E57,1,0)
=\$B\$7*IF(E59<>E58,1,0)
=\$B\$7*IF(E60<>E59,1,0)
=\$B\$7*IF(E61<>E60,1,0)
=\$B\$7*IF(E62<>E61,1,0)
=\$B\$7*IF(E63<>E62,1,0)
=\$B\$7*IF(E64<>E63,1,0)
=\$B\$7*IF(E65<>E64,1,0)
=F45+G45
=F46+G46
=F47+G47
=F48+G48
=F49+G49
=F50+G50
=F51+G51
=F52+G52
=F53+G53
=F54+G54
=F55+G55
=F56+G56
=F57+G57
=F58+G58
=F59+G59
=F60+G60
=F61+G61
=F62+G62
=F63+G63
=F64+G64
=F65+G65
Tom Salmons – Simulation
Sensitivity to U (cell E5) – see next sheet for more @Risk results
U
Min
Max
Mean
Stdev
Low
30 \$82,200 \$147,180 \$114,141 \$11,768 \$113,089
40 \$72,930 \$130,650 \$106,347 \$10,119 \$105,442
50 \$72,270 \$126,930 \$103,071
\$9,776 \$102,197
60 \$65,490 \$130,560 \$102,709
\$9,674 \$101,843
70 \$71,040 \$131,820 \$105,159 \$10,306 \$104,237
80 \$71,040 \$142,140 \$108,303 \$10,964 \$107,322
High
\$115,194
\$107,252
\$103,946
\$103,574
\$106,080
\$109,283
Mean Annual Cost
\$116,000
\$114,000
\$112,000
\$110,000
\$108,000
\$106,000
\$104,000
\$102,000
\$100,000
\$98,000
\$96,000
30
40
50
60
U
Page 11
70
80
@RISK Summary Reports
This case has trying to evaluate its inventory. This can be comunicated across the all levels by having the
evalaution of good performces of its annual costs, asstets, and revenues.
The production for each week is found such that if the inventory for the previous week is less than 30 units, then the
production level is 130 units; if the previous week’s inventory is greater than 80 units, then the production level will be 110 u
the production level is kept at the same level as the previous week. The inventory level for each week is also found
by summing the previous week’s inventory level with the production level, then subtracting the demand. The total
cost for each week is found by multiplying the inventory level by the holding cost of \$30 per unit. If the production level was
then there is a cost of \$3,000 in addition. Finally, the total average annual cost was found by adding all of the weekly costs t
@Risk is used to run 500 iterations of the simulations with a range of upper limit (U) inventory values. First, we con
units, each having an incremental increase of 10 units from the previous simulation. Second, we used =RiskSimtable
added =RiskOutput() to our average total annual cost. Finally, we are able to find the value of U that gives us the low
1.
The inventory level over the span of 52 weeks is shown below. The corresponding total cost for the 52-week period is \$
1.
a.
b.
c.
d.
e.
f.
With the values of U ranging from 30 to 80 units in increments of 10 units (L = 30 units throughout), the average total a
The average total cost of \$114,375.37 when U=30 units
The average total cost of \$106,614.36 when U=40 units
The average total cost of \$103,177.16 when U=50 units
The average total cost of \$102,633.32 when U=60 units
The average total cost of \$104,305.55 when U=70 units
The average total cost of \$108,073.62 when U=80 units
Using the simulated results, the average 52-week cost for each value is shown in the table below, along with their sta
The 95% Confidence Interval can be generate using “RiskPercentile ”. The graph of total cost versus U is also shown
From the simulated results, the best upper inventory level(U) is 60 units when the lower inventory level (L) = 30 uni
The simulation is shown in Exhibit 1
Simulation
Decision: Upper Inventory Average Total Annual
Standard
Lower
deviation
Limit Confidence
[\$]
Interval (95%) [\$]
Level [units]
Cost [\$]
1
2
3
4
5
6
30
40
50
60
70
80
114,354.37
106,614.36
103,177.16
102,633.32
104,305.55
108,073.62
12,237.20
10,952.92
10,101.97
10,023.55
10,328.10
10,502.45
89,789.51
83,491.46
82,704.06
82,318.04
83,833.10
85,917.21
Other than finding the best upper inventory limit, Ebony can also conduct the same analysis to find optimum lower l
If the lower limit is to low, the company may not be able to react to the sudden surge in demand. As a result, the com
sales from the supply shortage. Therefore, the company need to find the optimize level of inventory that will minimi
Furthermore, they should investigate the optimum production level to maintain over the 52-week period. If it is poss
will not have lower the switching cost. Lastly, if the production capacity is not limited to the incremented of 10, then
minimize the total cost while meeting most of the demand.
General Information
Workbook Name
C16_3.xls
Number of Simulations6
Number of Iterations 500
Number of Inputs
53
Number of Outputs 1
Sampling Type
Latin Hypercube
Simulation Start Time ##########
Simulation Stop Time ##########
Simulation Duration 0:00:13
Random Seed
144998495
Output and Input Summary Statistics
Output Name
Annual cost
Output Cell Simulation
\$H\$9
1
2
3
4
5
6
Input Name
If inventory >
Input Cell
\$E\$5
Normal
\$B\$14
Simulation
1
2
3
4
5
6
1
2
Normal
\$B\$15
Normal
\$B\$16
Normal
\$B\$17
Normal
\$B\$18
Normal
\$B\$19
Normal
\$B\$20
Normal
\$B\$21
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
Normal
\$B\$22
Normal
\$B\$23
Normal
\$B\$24
Normal
\$B\$25
Normal
\$B\$26
Normal
\$B\$27
Normal
\$B\$28
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
Normal
\$B\$29
Normal
\$B\$30
Normal
\$B\$31
Normal
\$B\$32
Normal
\$B\$33
Normal
\$B\$34
Normal
\$B\$35
Normal
\$B\$36
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
Normal
\$B\$37
Normal
\$B\$38
Normal
\$B\$39
Normal
\$B\$40
Normal
\$B\$41
Normal
\$B\$42
Normal
\$B\$43
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
Normal
\$B\$44
Normal
\$B\$45
Normal
\$B\$46
Normal
\$B\$47
Normal
\$B\$48
Normal
\$B\$49
Normal
\$B\$50
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
Normal
\$B\$51
Normal
\$B\$52
Normal
\$B\$53
Normal
\$B\$54
Normal
\$B\$55
Normal
\$B\$56
Normal
\$B\$57
Normal
\$B\$58
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
Normal
\$B\$59
Normal
\$B\$60
Normal
\$B\$61
Normal
\$B\$62
Normal
\$B\$63
Normal
\$B\$64
Normal
\$B\$65
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
orresponding total cost for the 52-week period is \$104,451.58. The model is shown in Exhibit 1.
units (L = 30 units throughout), the average total average annual cost of each upper inventory level is:
is shown in the table below, along with their standard deviations and 95% confidence intervals
”. The graph of total cost versus U is also shown below
nits when the lower inventory level (L) = 30 units, because it results in the lowest average total cost.
Upper Limit Confidence Interval (95%) [\$]
138,169.19
126,169.19
121,533.62
120,876.08
124,279.20
129,116.30
duct the same analysis to find optimum lower limits of the inventory before switching the production level.
he sudden surge in demand. As a result, the company will lose
he optimize level of inventory that will minimize the total cost, which included the cost of loss of sales.
o maintain over the 52-week period. If it is possible for them to adjust their production level to more consistent value, then they
city is not limited to the incremented of 10, then the company may need to find the new optimize production level which will
Minimum
82200
72930
72270
65490
71040
71040
Minimum
Maximum
Mean
Std Dev
147180
114141.48
11768.21441
130650
106346.82
10118.58003
126930
103071.24
9776.486887
130560
102708.66
9673.583371
131820
105158.58
10306.18548
142140
108302.7
10963.57842
Maximum
30
40
50
60
70
80
69.96292877
69.96292877
30
40
50
60
70
80
172.0926666
172.0926666
Mean
30
40
50
60
70
80
120.0113503
120.0113503
Std Dev
0
0
0
0
0
0
15.06389868
15.06389868
69.96292877
69.96292877
69.96292877
69.96292877
71.20896912
71.20896912
71.20896912
71.20896912
71.20896912
71.20896912
71.15851593
71.15851593
71.15851593
71.15851593
71.15851593
71.15851593
76.22753906
76.22753906
76.22753906
76.22753906
76.22753906
76.22753906
69.76818848
69.76818848
69.76818848
69.76818848
69.76818848
69.76818848
71.78302765
71.78302765
71.78302765
71.78302765
71.78302765
71.78302765
74.57580566
74.57580566
74.57580566
74.57580566
74.57580566
74.57580566
76.41662598
76.41662598
76.41662598
76.41662598
172.0926666
172.0926666
172.0926666
172.0926666
163.7991486
163.7991486
163.7991486
163.7991486
163.7991486
163.7991486
167.9855042
167.9855042
167.9855042
167.9855042
167.9855042
167.9855042
168.0211792
168.0211792
168.0211792
168.0211792
168.0211792
168.0211792
163.5778046
163.5778046
163.5778046
163.5778046
163.5778046
163.5778046
167.2139435
167.2139435
167.2139435
167.2139435
167.2139435
167.2139435
174.1224213
174.1224213
174.1224213
174.1224213
174.1224213
174.1224213
165.8055267
165.8055267
165.8055267
165.8055267
120.0113503
120.0113503
120.0113503
120.0113503
119.9849081
119.9849081
119.9849081
119.9849081
119.9849081
119.9849081
119.9977835
119.9977835
119.9977835
119.9977835
119.9977835
119.9977835
120.0103269
120.01 …