*** I ONLY NEED PART 2 DONE OF THIS ASSIGNMENT (500-750-word summary to company management). I HAVE ATTACHED THE ORIGINAL ASSIGNMENT THAT HAS BOTH PARTS COMPLETED BUT PART 2 WAS NOT ANSWERED CORRECTLY AND I NEED A BETTER SUMMARY FORTHE INSTRUCTOR***The purpose of this assignment is to use analytics techniques to analyze a case problem.Part 1Read Case Study Case 15.2 “Ebony Bath Soap” from the textbook, and then complete the following items.For Questions 1 and 2 of the case, use the Palisade DecisionTools Excel software to set up a simulation model and run a simulation with 500 trials for the case. Ensure that all Palisade software output is included in your files and that only one Excel file is open when running a simulation. Use the “Topic 3 Case Study Template” file as a starting point. Hint: The RiskSimtable function was be helpful for running the simulations.Respond to Question 3 as written in the problem. Ignore the confidence interval portion of the question.Respond to Question 4 as written in the problem.To receive full credit on the assignment, complete the following.Ensure that the Palisade software output is included with your submission.Ensure that Excel files include the associated cell functions and/or formulas if functions and/or formulas are used.Include a written response to all narrative questions presented in the problem by placing it in the associated Excel file.Include screenshots of all simulation distribution results for output variables.Place each problem in its own Excel file. Ensure that your first and last name are in your Excel file names.Part 2In a 500-750-word summary to company management, address the following. Include relevant charts and graphs within your summary, as needed.Describe the case specific business requirements and how they can be communicated across all levels of the organization.Based on the simulation results, discuss the Annual Cost output statistical distributions. Assume that your audience as minimal background in statistics.Discuss which Annual Cost output probability distribution has the most dispersion, and explain why this is so. Explain the descriptive, predictive, and prescriptive analytics that have been used to formulate the solutions to the business needs.Based on the Annual Cost output statistical distributions and other information gleaned from your analysis, discuss the specific prescribed course of action you would recommend to company management and justify your recommendations. Include discussion of how the proposed analytics solutions can optimize organizational performance and effectiveness.*** I ONLY NEED PART 2 DONE OF THIS ASSIGNMENT. I HAVE ATTACHED THE ORIGINAL ASSIGNMENT THAT HAS BOTH PARTS COMPLETED BUT PART 2 WAS NOT ANSWERED CORRECTLY AND I NEED A BETTER SUMMARY FORTHE INSTRUCTOR***

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 …

Purchase answer to see full

attachment