# Ebony Bath Soap Simulation and Risk Analysis

*label*Mathematics

*timer*Asked: Jan 30th, 2019

*account_balance_wallet*$30

### Question Description

The purpose of this assignment is to use analytics techniques to analyze a case problem.

Part 1

Read 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 2

In 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.

## Tutor Answer

Attached.

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$5,$G$5,E13))

=IF(D15$E$5,$G$5,E14))

=IF(D16$E$5,$G$5,E15))

=IF(D17$E$5,$G$5,E16))

=IF(D18$E$5,$G$5,E17))

=IF(D19$E$5,$G$5,E18))

=IF(D20$E$5,$G$5,E19))

=IF(D21$E$5,$G$5,E20))

=IF(D22$E$5,$G$5,E21))

=IF(D23$E$5,$G$5,E22))

=IF(D24$E$5,$G$5,E23))

=IF(D25$E$5,$G$5,E24))

=IF(D26$E$5,$G$5,E25))

=IF(D27$E$5,$G$5,E26))

=IF(D28$E$5,$G$5,E27))

=IF(D29$E$5,$G$5,E28))

=IF(D30$E$5,$G$5,E29))

=IF(D31$E$5,$G$5,E30))

=IF(D32$E$5,$G$5,E31))

=IF(D33$E$5,$G$5,E32))

=IF(D34$E$5,$G$5,E33))

=IF(D35$E$5,$G$5,E34))

=IF(D36$E$5,$G$5,E35))

=IF(D37$E$5,$G$5,E36))

=IF(D38$E$5,$G$5,E37))

=IF(D39$E$5,$G$5,E38))

=IF(D40$E$5,$G$5,E39))

=IF(D41$E$5,$G$5,E40))

=IF(D42$E$5,$G$5,E41))

=IF(D43$E$5,$G$5,E42))

=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

...

*flag*Report DMCA

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors