Euro Watch Company Report
1 ) The Euro Watch Company assembles expensive wristwatches and then sells them to retailers throughout Europe. The watches are assembled with two assembly lines with below specification:
Line 1:
Old equipment
Less reliable
Defect rate of 2%
Capacity: 500 watches per hour
Line 2:
New equipment
More reliable
Defect rate of 1%
Capacity: 500 watches per hour
We need to find the smallest number of defected watches each line produces independently in a given hour with success rate of 99%
The distribution is a binomial distribution since we have 500 independent and identical trials with a certain probability of success and we see a defected or non- defected option therefore in the excel file we should use
4) There is an order of 100 watches with value of $ 500 per watch with a maximum value of $ 50.000, for any number of watches sent, unit cost is $ 450, and expedited cost is $ 1000 per watch, as a loss. We need to develop spreadsheet model to find Euro watch expected profit for any number of watches they send to the customer, with consideration that they will never send more than 110 watches.
Using BINOMDIST we calculate the probability of having any number of defect-free watches, later on we calculate the profit of each one.
After we calculate the expected profit by using SUMPRODUCT function, then we make trials to see the expected profit of sending a package between 100, 110.
As we see in below table sending 103 watches has the highest profit of 3302 USD.
Line 1
Total Watches Sent 100
101
102
103
104
105
106
107
108
109
110
Expected Profit 2000
2825
3260
3302
3075
2711
2289
1847
1399
950
500
In case we produce entirely with line 2 that has a lower defect rate we need to send 101 watches to reach the highest profit of $ 3991.
Line 2
Total Watches Sent 100
101
102
103
104
105
106
107