Ebony Bath Soap Simulation and Risk Analysis

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

  1. 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.
  2. Respond to Question 3 as written in the problem. Ignore the confidence interval portion of the question.
  3. Respond to Question 4 as written in the problem.

To receive full credit on the assignment, complete the following.

  1. Ensure that the Palisade software output is included with your submission.
  2. Ensure that Excel files include the associated cell functions and/or formulas if functions and/or formulas are used.
  3. Include a written response to all narrative questions presented in the problem by placing it in the associated Excel file.
  4. Include screenshots of all simulation distribution results for output variables.
  5. 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.

  1. Describe the case specific business requirements and how they can be communicated across all levels of the organization.
  2. Based on the simulation results, discuss the Annual Cost output statistical distributions. Assume that your audience as minimal background in statistics.
  3. Discuss which Annual Cost output probability distribution has the most dispersion, and explain why this is so.
  4. Explain the descriptive, predictive, and prescriptive analytics that have been used to formulate the solutions to the business needs.
  5. 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.


Ebony Bath Soap Simulation and Risk Analysis
ebony_soap_case_study.png
Ebony Bath Soap Simulation Inputs Average demand Stdev of demand Unit holding cost Prod change cost Initial inventory Current prod level 120 15 $30 $3,000 60 120 Simulation of 52 weeks Week Normal Production policy: If inventory < If inventory > Otherwise, don't change production level. Demand Inventory 30 then produce 80 then produce 130 110 Annual cost Next week Production Holding cost Change cost Weekly cost

Tutor Answer

Lessermaster
School: Cornell University

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
Review

Anonymous
Excellent job

Similar Questions
Hot Questions
Related Tags
Study Guides

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