Model number of days to repair
In regard to the first part of the Case Problem (The average number of days needed to repair the copier), I worked on the Excel to find the number of the days required to repair the copier (Repair Time (days). In Excel, I wrote down the table information given from the case study to make it easier to find it and copy, if necessary. I used the RAND() function in to create random values for column F4:F16. After created the RAND()values, I copied and pasted the values in the column F4:F16 because I needed to freeze the number, otherwise it would keep changing. Using the random probabilities numbers created previously by the function RAND(). I used Excel function VLOOKUP to find the number of days needed to repair =VLOOKUP(F4,LookUp,2) to =VLOOKUP(F16,LookUp,2) and the sum gave me the information of number of days needed to repair the copier.=2 days. Per the calculations, the average repair time is 2 days.
Model number of weeks between breakdowns
In regard to the second part of the case (The average number of weeks between breakdowns), to find the time between breakdowns in weeks, I used the maximum number of 6 weeks which is in cell I4, and I used formula =6*SQRT(H4). I pulled the formula down and erase the numbers that exceed 52 weeks, once the problem wanted to find the cost of breakdowns over a year only. For each information required I calculate a total =SUM(I4:I28) = 53.67. The average time between breakdowns is 4.13 (or 4.00 rounded) weeks.
Model lost revenue due to breakdowns
In regard to the third part of the case (Lost revenue due to copier being out of service), I first had to find the uniform probability distribution between 2,000 and 8,000 (the estimated copies per day by the owners). To find the numbers of copies lost, I used the formula =6000*J4+2000 and I pulled the formula down from the cell K4 to. The owners are selling copies for $0.10 per copy. To find the total revenue loss per each breakdown, I used the formula =K7*0.1 in cell to find the probable loss of revenue each day. I pulled the formula down in cells from L4:L31 and got the sum=14.767,49. The total annual loss of revenue due to breakdowns will be $27,425.34.
The lost revenue due to copier breakdowns over 1 year
In 52 weeks the copier breaks down every 4.13 weeks with the repair time of 4.13 days. The copier breaks 12.5 times a year. The revenue lost per day is $527.41 with a revenue lost per breakdown of $2177.59. The revenue lost per year is 14767.49. This is more than limit set at $12000.
6. Combine model components to produce a coherent answer to the question posed in the case study.
(a) Answer the question posed in the case study.
In regard to the case study, I would suggest James, Ernie, and Terri to purchase the backup copier because the average annual loss of revenue due to breakdowns would be more than their maximum limit of $12,000.00.
(b) How confident are you that this answer is a good one?
In regard to validate the confidence in my answer, I used confidence limits. I am 95% confident that the average loss per breakdown will be between $630.78.
=B37+1.96*B37/SQRT(100)
UCL (95%) = $630.78
(c) What are the limits of the study?
For the limits of the study I used the formula =B37-1.96*B37/SQRT(100)
LCL (95%) = $404.04
Repair time days | 28 |
Weeks per year | 52 |
Weeks Between Breakdowns | 4.13 |
Breakdowns Per Year | 12.594 | | |
| |
Days Lost Per Breakdown | 4.13 |
Revenue Lost Per Day | $527.41 |
Revenue Lost Per Breakdown | $2,177.59 | | |
Revenue Lost Per Year | $14,767.49 |
In 52 weeks the copier breaks down every 4.13 weeks with the repair time of 4.13 days. The copier breaks 12.5 times a year. The revenue lost per day is $527.41 with a revenue lost per breakdown of $2177.59. The revenue lost per year is 14767.49. This is more than limit set at $12000. 