- PLEASE USE THE SAME EXCEL SHEET

- I WILL LIKE THE ANSWER

In one of PLEs manufacturing facilities, a drill press that has three drill bits is used to fabricate metal parts. Drill bits break occasionally and need to be replaced. The present policy is to replace a drill bit when it breaks or can no longer be used. The operations manager is considering a different policy in which all three drill bits are replaced when any one bit breaks or needs replacement. The rationale is that this would reduce downtime. It costs $200 each time the drill press must be shut down. A drill bit costs $85, and the variable cost of replacing a drill bit is $15 per bit. The company that supplies the drill bits has historical evidence that the reliability of a single drill bit is described by a Poisson probability distribution with the mean number of failures per hour equal to =0.01. Thus, the time between failures is an exponential distribution with mean =1/=1/0.01=100 hours.

The operations manager at PLE would like to compare the cost of the two replacement policies. Develop spreadsheet simulation models to determine the total cost for each policy over 1,000 hours and make a recommendation.

Activity 4 Datasheet helps you to structure and simulate the case.

Activity 4 Datasheet:

*Number of Failure goes till 50*

D F H M M 0 P R T T B Drill Bit Replacment Policies 1 2 E N We simulate each policy. The time to next failure is exponentially distributed. The niodels are set up to identify all failures within 1000 hours (highlight them using conditional formatting) using a MATCH function, and then calculate the replacement costs. The simulations are run until 50 failures occur to ensure that 1000 hours are achieved. Policy 1: Replace each bit when it fails Policy 2: Replace all bits when one fails 4 5 Mean time betyeen 6 failures (hours) Shut down cost Total cost of replacing each bit vhen it fails Total cost of replacing all bits vhen one fails = 7 8 Drill bit cost Variable replacement 9 cost 10 11 Cost for Bit 1 Cost for Bit 2 Cost for Bit 3 Drill Bit 1 Failu Time to next re failure 1 2 Time of failure Individual Drill Bit 2 Replacemen Time to next t cost failure Time of failure Individual Drill Bit 3 Replacemen Time to next t cost failure Time of failure Individual Replacement cost Time of failure Drill Bit 2 Time to next failure Time of failure Drill Bit 3 Time to next failure Time of failure Time of first failure Group Replacemer t cost 12 Drill Bit 1 Fail Time to next ure failure 10 2 31 4 5 6 7 8 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 37 38 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 10 11 12 13 14 15 16 17 18 19 20 21 22 23 241 25 26 271 28 29 30 31 32 33 34 35 36 37 40 41 42 43 44 45 46 47 48 49 50 51 52 53 39 40 41