The purpose of this assignment is to use analytics techniques to analyze a case problem. Part 1 Read Case Study Case 15.2 Ebo
*** 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 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.
*** 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***
Tom Salmons – Part I _Answer
| Ebony Bath Soap Solution |
| Question 1) |
| 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 ce |
| 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 yearly value occurs with |
| U= 60, although this could change if the simulation were done with different random numbers. A plot of the mean annual cost is shown below the tabulated results |
| 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 production level changes. Other policies could |
| be investigated which return to a 120 production level. For example, another policy would be to produce 120 units if inventory crosses the midpoint of the range [L, U]. |
See the Simulation sheet, columns A-H, for the solution to the 52-week simulation. The major components to calculating the cost for a given week are the demand generation, 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 week) minus this week's demand or zero, whichever is 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 to see whether this week's inventory is less than l (here, 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 (here, 80). If so, next week's production level is set to 110. 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 simply the per unit inventory cost (here, 30) multiplied by this week's inventory. Calculating the production change cost in column G is more challenging. The production change cost (here, 3000) is multiplied by the reusult of an IF statement is used to check if the production level has been changed. If there was a change, 3000 is multiplied by one, otherwise, if no change occured, then 3000 is multiplied by 0. Column H is simply the sum of the two costs for the week. Cell H9 totals the costs over the year.
Mean Annual Cost
Mean Profit 30 40 50 60 70 80 114141.48 106346.82 103071.24 102708.66 105158.58 108302.7
U
Tom Salmons – Simulation
| Ebony Bath Soap Simulation | ||||||||||||||||||
| Inputs | Production policy: | |||||||||||||||||
| Average demand | 120 | If inventory < | 30 | then produce | 130 | |||||||||||||
| Stdev of demand | 15 | If inventory > | ERROR:#NAME? | then produce | 110 | |||||||||||||
| Unit holding cost | $30 | Otherwise, don't change production level. | ||||||||||||||||
| Prod change cost | $3,000 | |||||||||||||||||
| Initial inventory | 60 | |||||||||||||||||
| Current prod level | 120 | Annual cost | ERROR:#NAME? | |||||||||||||||
| Simulation of 52 weeks | Next week | Sensitivity to U (cell E5) – see next sheet for more @Risk results | ||||||||||||||||
| Week | Normal | Demand Chris Albright: Make sure demand is an integer and not negative |
Inventory | Production | Holding cost | Change cost | Weekly cost | U | Min | Max | Mean | Stdev | Low Chris Albright: Lower limit of 95% confidence interval |
High Chris Albright: Upper limit of 95% confidence interval |
||||
| Chris Albright: Make sure demand is an integer and not negative | 0 | =B8 | =B9 | 30 | $82,200 | $147,180 | $114,141 | $11,768 | $113,089 | $115,194 | ||||||||
| 1 | =RiskNormal($B$4,$B$5 | =ROUND(MAX(B14,0),0) | =MAX(D13+E13-C14,0) | =IF(D14<$E$4,$G$4,IF(D14>$E$5,$G$5,E13)) | =D14*$B$6 | =$B$7*IF(E14<>E13,1,0) | =F14+G14 | 40 | $72,930 | $130,650 | $106,347 | $10,119 | $105,442 | $107,252 | ||||
| 2 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B15,0),0) | =MAX(D14+E14-C15,0) | =IF(D15<$E$4,$G$4,IF(D15>$E$5,$G$5,E14)) | =D15*$B$6 | =$B$7*IF(E15<>E14,1,0) | =F15+G15 | 50 | $72,270 | $126,930 | $103,071 | $9,776 | $102,197 | $103,946 | ||||
| 3 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B16,0),0) | =MAX(D15+E15-C16,0) | =IF(D16<$E$4,$G$4,IF(D16>$E$5,$G$5,E15)) | =D16*$B$6 | =$B$7*IF(E16<>E15,1,0) | =F16+G16 | 60 | $65,490 | $130,560 | $102,709 | $9,674 | $101,843 | $103,574 | ||||
| 4 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B17,0),0) | =MAX(D16+E16-C17,0) | =IF(D17<$E$4,$G$4,IF(D17>$E$5,$G$5,E16)) | =D17*$B$6 | =$B$7*IF(E17<>E16,1,0) | =F17+G17 | 70 | $71,040 | $131,820 | $105,159 | $10,306 | $104,237 | $106,080 | ||||
| 5 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B18,0),0) | =MAX(D16D17+E17-C18,0) | =IF(D18<$E$4,$G$4,IF(D18>$E$5,$G$5,E17)) | =D18*$B$6 | =$B$7*IF(E18<>E17,1,0) | =F18+G18 | 80 | $71,040 | $142,140 | $108,303 | $10,964 | $107,322 | $109,283 | ||||
| 6 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B19,0),0) | =MAX(D18+E18-C19,0) | =IF(D19<$E$4,$G$4,IF(D19>$E$5,$G$5,E18)) | =D19*$B$6 | =$B$7*IF(E19<>E18,1,0) | =F19+G19 | |||||||||||
| 7 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B20,0),0) | =MAX(D19+E19-C20,0) | =IF(D20<$E$4,$G$4,IF(D20>$E$5,$G$5,E19)) | =D20*$B$6 | =$B$7*IF(E20<>E19,1,0) | =F20+G20 | |||||||||||
| 8 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B21,0),0) | =MAX(D20+E20-C21,0) | =IF(D21<$E$4,$G$4,IF(D21>$E$5,$G$5,E20)) | =D21*$B$6 | =$B$7*IF(E21<>E20,1,0) | =F21+G21 | |||||||||||
| 9 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B22,0),0) | =MAX(D21+E21-C22,0) | =IF(D22<$E$4,$G$4,IF(D22>$E$5,$G$5,E21)) | =D22*$B$6 | =$B$7*IF(E22<>E21,1,0) | =F22+G22 | |||||||||||
| 10 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B23,0),0) | =MAX(D22+E22-C23,0) | =IF(D23<$E$4,$G$4,IF(D23>$E$5,$G$5,E22)) | =D23*$B$6 | =$B$7*IF(E23<>E22,1,0) | =F23+G23 | |||||||||||
| 11 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B24,0),0) | =MAX(D23+E23-C24,0) | =IF(D24<$E$4,$G$4,IF(D24>$E$5,$G$5,E23)) | =D24*$B$6 | =$B$7*IF(E24<>E23,1,0) | =F24+G24 | |||||||||||
| 12 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B25,0),0) | =MAX(D24+E24-C25,0) | =IF(D25<$E$4,$G$4,IF(D25>$E$5,$G$5,E24)) | =D25*$B$6 | =$B$7*IF(E25<>E24,1,0) | =F25+G25 | |||||||||||
| 13 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B26,0),0) | =MAX(D25+E25-C26,0) | =IF(D26<$E$4,$G$4,IF(D26>$E$5,$G$5,E25)) | =D26*$B$6 | =$B$7*IF(E26<>E25,1,0) | =F26+G26 | |||||||||||
| 14 | =RiskNormal($B$4,$B$5) | =ROUND(CMAX(B27,0),0) | =MAX(D26+E26-C27,0) | =IF(D27<$E$4,$G$4,IF(D27>$E$5,$G$5,E26)) | =D27*$B$6 | =$B$7*IF(E27<>E26,1,0) | =F27+G27 | |||||||||||
| 15 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B28,0),0) | =MAX(D27+E27-C28,0) | =IF(D28<$E$4,$G$4,IF(D28>$E$5,$G$5,E27)) | =D28*$B$6 | =$B$7*IF(E28<>E27,1,0) | =F28+G28 | |||||||||||
| 16 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B29,0),0) | =MAX(D28+E28-C29,0) | =IF(D29<$E$4,$G$4,IF(D29>$E$5,$G$5,E28)) | =D29*$B$6 | =$B$7*IF(E29<>E28,1,0) | =F29+G29 | |||||||||||
| 17 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B30,0),0) | =MAX(D29+E29-C30,0) | =IF(D30<$E$4,$G$4,IF(D30>$E$5,$G$5,E29)) | =D30*$B$6 | =$B$7*IF(E30<>E29,1,0) | =F30+G30 | |||||||||||
| 18 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B31,0),0) | =MAX(D30+E30-C31,0) | =IF(D31<$E$4,$G$4,IF(D31>$E$5,$G$5,E30)) | =D31*$B$6 | =$B$7*IF(E31<>E30,1,0) | =F31+G31 | |||||||||||
| 19 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B32,0),0) | =MAX(D31+E31-C32,0) | =IF(D32<$E$4,$G$4,IF(D32>$E$5,$G$5,E31)) | =D32*$B$6 | =$B$7*IF(E32<>E31,1,0) | =F32+G32 | |||||||||||
| 20 | =RiskNormal($B$4,$B$5) | =ROUND(CMAX(B33,0),0) | =MAX(D32+E32-C33,0) | =IF(D33<$E$4,$G$4,IF(D33>$E$5,$G$5,E32)) | =D33*$B$6 | =$B$7*IF(E33<>E32,1,0) | =F33+G33 | |||||||||||
| 21 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B34,0),0) | =MAX(D33+E33-C34,0) | =IF(D34<$E$4,$G$4,IF(D34>$E$5,$G$5,E33)) | =D34*$B$6 | =$B$7*IF(E34<>E33,1,0) | =F34+G34 | |||||||||||
| 22 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B35,0),0) | =MAX(D34+E34-C35,0) | =IF(D35<$E$4,$G$4,IF(D35>$E$5,$G$5,E34)) | =D35*$B$6 | =$B$7*IF(E35<>E34,1,0) | =F35+G35 | |||||||||||
| 23 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B36,0),0) | =MAX(D35+E35-C36,0) | =IF(D36<$E$4,$G$4,IF(D36>$E$5,$G$5,E35)) | =D36*$B$6 | =$B$7*IF(E36<>E35,1,0) | =F36+G36 | |||||||||||
| 24 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B37,0),0) | =MAX(D36+E36-C37,0) | =IF(D37<$E$4,$G$4,IF(D37>$E$5,$G$5,E36)) | =D37*$B$6 | =$B$7*IF(E37<>E36,1,0) | =F37+G37 | |||||||||||
| 25 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B38,0),0) | =MAX(D37+E37-C38,0) | =IF(D38<$E$4,$G$4,IF(D38>$E$5,$G$5,E37)) | =D38*$B$6 | =$B$7*IF(E38<>E37,1,0) | =F38+G38 | |||||||||||
| 26 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B39,0),0) | =MAX(D38+E38-C39,0) | =IF(D39<$E$4,$G$4,IF(D39>$E$5,$G$5,E38)) | =D39*$B$6 | =$B$7*IF(E39<>E38,1,0) | =F39+G39 | |||||||||||
| 27 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B40,0),0) | =MAX(D39+E39-C40,0) | =IF(D40<$E$4,$G$4,IF(D40>$E$5,$G$5,E39)) | =D40*$B$6 | =$B$7*IF(E40<>E39,1,0) | =F40+G40 | |||||||||||
| 28 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B41,0),0) | =MAX(D40+E40-C41,0) | =IF(D41<$E$4,$G$4,IF(D41>$E$5,$G$5,E40)) | =D41*$B$6 | =$B$7*IF(E41<>E40,1,0) | =F41+G41 | |||||||||||
| 29 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B42,0),0) | =MAX(D41+E41-C42,0) | =IF(D42<$E$4,$G$4,IF(D42>$E$5,$G$5,E41)) | =D42*$B$6 | =$B$7*IF(E42<>E41,1,0) | =F42+G42 | |||||||||||
| 30 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B43,0),0) | =MAX(D42+E42-C43,0) | =IF(D43<$E$4,$G$4,IF(D43>$E$5,$G$5,E42)) | =D43*$B$6 | =$B$7*IF(E43<>E42,1,0) | =F43+G43 | |||||||||||
| 31 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B44,0),0) | =MAX(D43+E43-C44,0) | =IF(D44<$E$4,$G$4,IF(D44>$E$5,$G$5,E43)) | =D44*$B$6 | =$B$7*IF(E44<>E43,1,0) | =F44+G44 | |||||||||||
| 32 | =RiskNormal($B$4,$B$5) | =ROUND(CMAX(B45,0),0) | =MAX(D44+E44-C45,0) | =IF(D45<$E$4,$G$4,IF(D45>$E$5,$G$5,E44)) | =D45*$B$6 | =$B$7*IF(E45<>E44,1,0) | =F45+G45 | |||||||||||
| 33 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B46,0),0) | =MAX(D45+E45-C46,0) | =IF(D46<$E$4,$G$4,IF(D46>$E$5,$G$5,E45)) | =D46*$B$6 | =$B$7*IF(E46<>E45,1,0) | =F46+G46 | |||||||||||
| 34 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B47,0),0) | =MAX(D46+E46-C47,0) | =IF(D47<$E$4,$G$4,IF(D47>$E$5,$G$5,E46)) | =D47*$B$6 | =$B$7*IF(E47<>E46,1,0) | =F47+G47 | |||||||||||
| 35 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B48,0),0) | =MAX(D47+E47-C48,0) | =IF(D48<$E$4,$G$4,IF(D48>$E$5,$G$5,E47)) | =D48*$B$6 | =$B$7*IF(E48<>E47,1,0) | =F48+G48 | |||||||||||
| 36 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B49,0),0) | =MAX(D48+E48-C49,0) | =IF(D49<$E$4,$G$4,IF(D49>$E$5,$G$5,E48)) | =D49*$B$6 | =$B$7*IF(E49<>E48,1,0) | =F49+G49 | |||||||||||
| 37 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B50,0),0) | =MAX(D49+E49-C50,0) | =IF(D50<$E$4,$G$4,IF(D50>$E$5,$G$5,E49)) | =D50*$B$6 | =$B$7*IF(E50<>E49,1,0) | =F50+G50 | |||||||||||
| 38 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B51,0),0) | =MAX(D50+E50-C51,0) | =IF(D51<$E$4,$G$4,IF(D51>$E$5,$G$5,E50)) | =D51*$B$6 | =$B$7*IF(E51<>E50,1,0) | =F51+G51 | |||||||||||
| 39 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B52,0),0) | =MAX(D51+E51-C52,0) | =IF(D52<$E$4,$G$4,IF(D52>$E$5,$G$5,E51)) | =D52*$B$6 | =$B$7*IF(E52<>E51,1,0) | =F52+G52 | |||||||||||
| 40 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B53,0),0) | =MAX(D52+E52-C53,0) | =IF(D53<$E$4,$G$4,IF(D53>$E$5,$G$5,E52)) | =D53*$B$6 | =$B$7*IF(E53<>E52,1,0) | =F53+G53 | |||||||||||
| 41 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B54,0),0) | =MAX(D53+E53-C54,0) | =IF(D54<$E$4,$G$4,IF(D54>$E$5,$G$5,E53)) | =D54*$B$6 | =$B$7*IF(E54<>E53,1,0) | =F54+G54 | |||||||||||
| 42 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B55,0),0) | =MAX(D54+E54-C55,0) | =IF(D55<$E$4,$G$4,IF(D55>$E$5,$G$5,E54)) | =D55*$B$6 | =$B$7*IF(E55<>E54,1,0) | =F55+G55 | |||||||||||
| 43 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B56,0),0) | =MAX(D55+E55-C56,D0) | =IF(D56<$E$4,$G$4,IF(D56>$E$5,$G$5,E55)) | =D56*$B$6 | =$B$7*IF(E56<>E55,1,0) | =F56+G56 | |||||||||||
| 44 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B57,0),0) | =MAX(D56+E56-C57,0) | =IF(D57<$E$4,$G$4,IF(D57>$E$5,$G$5,E56)) | =D57*$B$6 | =$B$7*IF(E57<>E56,1,0) | =F57+G57 | |||||||||||
| 45 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B58,0),0) | =MAX(D57+E57-C58,0) | =IF(D58<$E$4,$G$4,IF(D58>$E$5,$G$5,E57)) | =D58*$B$6 | =$B$7*IF(E58<>E57,1,0) | =F58+G58 | |||||||||||
| 46 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B59,0),0) | =MAX(D58+E58-C59,0) | =IF(D59<$E$4,$G$4,IF(D59>$E$5,$G$5,E58)) | =D59*$B$6 | =$B$7*IF(E59<>E58,1,0) | =F59+G59 | |||||||||||
| 47 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B60,0),0) | =MAX(D59+E59-C60,0) | =IF(D60<$E$4,$G$4,IF(D60>$E$5,$G$5,E59)) | =D60*$B$6 | =$B$7*IF(E60<>E59,1,0) | =F60+G60 | |||||||||||
| 48 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B61,0),0) | =MAX(D60+E60-C61,0) | =IF(D61<$E$4,$G$4,IF(D61>$E$5,$G$5,E60)) | =D61*$B$6 | =$B$7*IF(E61<>E60,1,0) | =F61+G61 | |||||||||||
| 49 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B62,0),0) | =MAX(D61+E61-C62,0) | =IF(D62<$E$4,$G$4,IF(D62>$E$5,$G$5,E61)) | =D62*$B$6 | =$B$7*IF(E62<>E61,1,0) | =F62+G62 | |||||||||||
| 50 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B63,0),0) | =MAX(D62+E62-C63,0) | =IF(D63<$E$4,$G$4,IF(D63>$E$5,$G$5,E62)) | =D63*$B$6 | =$B$7*IF(E63<>E62,1,0) | =F63+G63 | |||||||||||
| 51 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B64,0),0) | =MAX(D63+E63-C64,0) | =IF(D64<$E$4,$G$4,IF(D64>$E$5,$G$5,E63)) | =D64*$B$6 | =$B$7*IF(E64<>E63,1,0) | =F64+G64 | |||||||||||
| 52 | =RiskNormal($B$4,$B$5) | =ROUND(MAX(B65,0),0) | =MAX(D64+E64-C65,0) | =IF(D65<$E$4,$G$4,IF(D65>$E$5,$G$5,E64)) | =D65*$B$6 | =$B$7*IF(E65<>E64,1,0) | =F65+G65 |
Mean Annual Cost
Mean Profit 30 40 50 60 70 80 114141.48 106346.82 103071.24 102708.66 105158.58 108302.7
U
Tom – Part II_Summary Report
| @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 units; otherwise, | ||||||||||
| 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 switched, | ||||||||||
| 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 together. | ||||||||||
| @Risk is used to run 500 iterations of the simulations with a range of upper limit (U) inventory values. First, we constructed a table with six simulations with U values ranging from 30 units to 80 | ||||||||||
| units, each having an incremental increase of 10 units from the previous simulation. Second, we used =RiskSimtable() formula to pull the U values from the table that we have just created. Third, we | ||||||||||
| added =RiskOutput() to our average total annual cost. Finally, we are able to find the value of U that gives us the lowest average cost | ||||||||||
| 1. The inventory level over the span of 52 weeks is shown below. The corresponding total cost for the 52-week period is $104,451.58. The model is shown in Exhibit 1. | ||||||||||
| 1. With the values of U ranging from 30 to 80 units in increments of 10 units (L = 30 units throughout), the average total average annual cost of each upper inventory level is: | ||||||||||
| a. The average total cost of $114,375.37 when U=30 units | ||||||||||
| b. The average total cost of $106,614.36 when U=40 units | ||||||||||
| c. The average total cost of $103,177.16 when U=50 units | ||||||||||
| d. The average total cost of $102,633.32 when U=60 units | ||||||||||
| e. The average total cost of $104,305.55 when U=70 units | ||||||||||
| f. 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 standard deviations and 95% confidence intervals | ||||||||||
| The 95% Confidence Interval can be generate using “RiskPercentile”. The graph of total cost versus U is also shown below | ||||||||||
| From the simulated results, the best upper inventory level(U) is 60 units when the lower inventory level (L) = 30 units, because it results in the lowest average total cost. | ||||||||||
| The simulation is shown in Exhibit 1 | ||||||||||
| Simulation | Decision: Upper Inventory Level [units] | Average Total Annual Cost [$] | Standard deviation [$] | Lower Limit Confidence Interval (95%) [$] | Upper Limit Confidence Interval (95%) [$] | |||||
| 1 | 30 | 114,354.37 | 12,237.20 | 89,789.51 | 138,169.19 | |||||
| 2 | 40 | 106,614.36 | 10,952.92 | 83,491.46 | 126,169.19 | |||||
| 3 | 50 | 103,177.16 | 10,101.97 | 82,704.06 | 121,533.62 | |||||
| 4 | 60 | 102,633.32 | 10,023.55 | 82,318.04 | 120,876.08 | |||||
| 5 | 70 | 104,305.55 | 10,328.10 | 83,833.10 | 124,279.20 | |||||
| 6 | 80 | 108,073.62 | 10,502.45 | 85,917.21 | 129,116.30 | |||||
| Other than finding the best upper inventory limit, Ebony can also conduct the same analysis to find optimum lower limits of the inventory before switching the production level. | ||||||||||
| 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 company will lose | ||||||||||
| sales from the supply shortage. Therefore, the company need to find the optimize level of inventory that will minimize the total cost, which included the cost of loss of sales. | ||||||||||
| Furthermore, they should investigate the optimum production level to maintain over the 52-week period. If it is possible for them to adjust their production level to more consistent value, then they | ||||||||||
| will not have lower the switching cost. Lastly, if the production capacity is not limited to the incremented of 10, then the company may need to find the new optimize production level which will | ||||||||||
| minimize the total cost while meeting most of the demand. | ||||||||||
| General Information | ||||||||||
| Workbook Name | C16_3.xls | |||||||||
| Number of Simulations | 6 | |||||||||
| Number of Iterations | 500 | |||||||||
| Number of Inputs | 53 | |||||||||
| Number of Outputs | 1 | |||||||||
| Sampling Type | Latin Hypercube | |||||||||
| Simulation Start Time | 7/7/00 9:29:28 | |||||||||
| Simulation Stop Time | 7/7/00 9:29:41 | |||||||||
| Simulation Duration | 0:00:13 | |||||||||
| Random Seed | 144998495 | |||||||||
| Output and Input Summary Statistics | ||||||||||
| Output Name | Output Cell | Simulation | Minimum | Maximum | Mean | Std Dev | 5% | 95% | ||
| Annual cost | $H$9 | 1 | 82200 | 147180 | 114141.48 | 11768.2144078998 | 94590 | 133440 | ||
| 2 | 72930 | 130650 | 106346.82 | 10118.5800293926 | 88890 | 121950 | ||||
| 3 | 72270 | 126930 | 103071.24 | 9776.486887124 | 85560 | 117990 | ||||
| 4 | 65490 | 130560 | 102708.66 | 9673.5833709577 | 86280 | 117780 | ||||
| 5 | 71040 | 131820 | 105158.58 | 10306.1854777509 | 88290 | 122010 | ||||
| 6 | 71040 | 142140 | 108302.7 | 10963.5784219217 | 89370 | 125940 | ||||
| Input Name | Input Cell | Simulation | Minimum | Maximum | Mean | Std Dev | 5% | 95% | ||
| If inventory > | $E$5 | 1 | 30 | 30 | 30 | 0 | 30 | 30 | ||
| 2 | 40 | 40 | 40 | 0 | 40 | 40 | ||||
| 3 | 50 | 50 | 50 | 0 | 50 | 50 | ||||
| 4 | 60 | 60 | 60 | 0 | 60 | 60 | ||||
| 5 | 70 | 70 | 70 | 0 | 70 | 70 | ||||
| 6 | 80 | 80 | 80 | 0 | 80 | 80 | ||||
| Normal | $B$14 | 1 | 69.962928772 | 172.092666626 | 120.0113503265 | 15.0638986751 | 95.0924758911 | 144.4532928467 | ||
| 2 | 69.962928772 | 172.092666626 | 120.0113503265 | 15.0638986751 | 95.0924758911 | 144.4532928467 | ||||
| 3 | 69.962928772 | 172.092666626 | 120.0113503265 | 15.0638986751 | 95.0924758911 | 144.4532928467 | ||||
| 4 | 69.962928772 | 172.092666626 | 120.0113503265 | 15.0638986751 | 95.0924758911 | 144.4532928467 | ||||
| 5 | 69.962928772 | 172.092666626 | 120.0113503265 | 15.0638986751 | 95.0924758911 | 144.4532928467 | ||||
| 6 | 69.962928772 | 172.092666626 | 120.0113503265 | 15.0638986751 | 95.0924758911 | 144.4532928467 | ||||
| Normal | $B$15 | 1 | 71.2089691162 | 163.7991485596 | 119.9849081116 | 14.9976170327 | 95.3269577026 | 144.6170654297 | ||
| 2 | 71.2089691162 | 163.7991485596 | 119.9849081116 | 14.9976170327 | 95.3269577026 | 144.6170654297 | ||||
| 3 | 71.2089691162 | 163.7991485596 | 119.9849081116 | 14.9976170327 | 95.3269577026 | 144.6170654297 | ||||
| 4 | 71.2089691162 | 163.7991485596 | 119.9849081116 | 14.9976170327 | 95.3269577026 | 144.6170654297 | ||||
| 5 | 71.2089691162 | 163.7991485596 | 119.9849081116 | 14.9976170327 | 95.3269577026 | 144.6170654297 | ||||
| 6 | 71.2089691162 | 163.7991485596 | 119.9849081116 | 14.9976170327 | 95.3269577026 | 144.6170654297 | ||||
| Normal | $B$16 | 1 | 71.1585159302 | 167.9855041504 | 119.9977834778 | 15.0205687364 | 95.1532897949 | 144.4892272949 | ||
| 2 | 71.1585159302 | 167.9855041504 | 119.9977834778 | 15.0205687364 | 95.1532897949 | 144.4892272949 | ||||
| 3 | 71.1585159302 | 167.9855041504 | 119.9977834778 | 15.0205687364 | 95.1532897949 | 144.4892272949 | ||||
| 4 | 71.1585159302 | 167.9855041504 | 119.9977834778 | 15.0205687364 | 95.1532897949 | 144.4892272949 | ||||
| 5 | 71.1585159302 | 167.9855041504 | 119.9977834778 | 15.0205687364 | 95.1532897949 | 144.4892272949 | ||||
| 6 | 71.1585159302 | 167.9855041504 | 119.9977834778 | 15.0205687364 | 95.1532897949 | 144.4892272949 | ||||
| Normal | $B$17 | 1 | 76.2275390625 | 168.0211791992 | 120.0103268585 | 14.9868392666 | 95.0752334595 | 144.6616973877 | ||
| 2 | 76.2275390625 | 168.0211791992 | 120.0103268585 | 14.9868392666 | 95.0752334595 | 144.6616973877 | ||||
| 3 | 76.2275390625 | 168.0211791992 | 120.0103268585 | 14.9868392666 | 95.0752334595 | 144.6616973877 | ||||
| 4 | 76.2275390625 | 168.0211791992 | 120.0103268585 | 14.9868392666 | 95.0752334595 | 144.6616973877 | ||||
| 5 | 76.2275390625 | 168.0211791992 | 120.0103268585 | 14.9868392666 | 95.0752334595 | 144.6616973877 | ||||
| 6 | 76.2275390625 | 168.0211791992 | 120.0103268585 | 14.9868392666 | 95.0752334595 | 144.6616973877 | ||||
| Normal | $B$18 | 1 | 69.7681884766 | 163.5778045654 | 119.9828871307 | 15.0067829976 | 95.3220596313 | 144.5030212402 | ||
| 2 | 69.7681884766 | 163.5778045654 | 119.9828871307 | 15.0067829976 | 95.3220596313 | 144.5030212402 | ||||
| 3 | 69.7681884766 | 163.5778045654 | 119.9828871307 | 15.0067829976 | 95.3220596313 | 144.5030212402 | ||||
| 4 | 69.7681884766 | 163.5778045654 | 119.9828871307 | 15.0067829976 | 95.3220596313 | 144.5030212402 | ||||
| </t
Collepals.com Plagiarism Free Papers Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers. Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS Why Hire Collepals.com writers to do your paper? Quality- We are experienced and have access to ample research materials. We write plagiarism Free Content Confidential- We never share or sell your personal information to third parties. Support-Chat with us today! We are always waiting to answer all your questions.  All Rights Reserved Terms and Conditions |