In my first portion of this assignment I decided to use the method like the dice. Using the repair time and probability given to us in the book. I computed the cumulative Probability by adding cells A & B; lines 5- 8 which gave me the Cum. Prob. for each day it would take for repair of the copiers.
Next, I took the Cumulative Probability and the Repair Time in Days, named it Repairs. This will assist me later in finding the random numbers for the number of days the possibility of the copier breaking down. I choose to do 30 days because all months except one have 30 days in them. I also used the process of VLOOKUP, the equation =VLOOKUP (F5, Repair,2).
Taking the random function (=rand), I received this for the first day randomized number as well as the number of days it took to repair in the event of a breakdown. This step I used for the next 29 days. As you can see the maximum number of days that they will have in between repair cycles is 4 and they would happen closer to the end of the month. The number of breakdowns I based on the weeks, 52 weeks in a year. Using the same random function (=rand), I received the randomized number, within the 6 (=6*SQRT(I5); weeks intervals the square root gave me the time in between those breakdowns. My next step was to find the average of the square rooted number, lines J5- J56 of the weeks in between, which gave me that average. I also did an average of the days the repair time would take, lines G5-G34, which gave me that average. Of course these numbers can change from time to time. Upon gathering all of this info I need to find the loss of revenue for JET Copies. They estimated that they could produce 2,000 to 8,000 copies in a months’ time. That means possible revenue per day between $200 to $800. On the average of those copies we found that 5000 copies were or could be successful per day, which means the average revenue per day could be $500. My thinking at this time due to the competition that may be around they may want to increase the cost per day. Instead of $.10 they may want to decrease to $.05 per copy. This will give them less revenue at the end of each day (see excel decrease). This would reduce the loss per year. Revenue Loss per year, 52 weeks, I arrived at this by multiplying the weeks per year by the weeks between breakdowns, which gave me the breakdowns per year. Then I took the loss per day and multiplied it by the average revenue per day giving me the revenue loss per breakdown. Then multiplying the breakdowns per year by the revenue loss per breakdown, gave me the Revenue loss per year. Now I figure if Jet Copies had invested $18,000 already in this particular copier. They thought ahead which was smart on their part, thinking in the long run of the first copier’s possible breakdowns the revenue that they would lose. Purchasing another copier for $8,000 at the current charge of $.10/copy they would benefit from that extra purchase. Because what are they chances of both copiers breaking down at the same time, in that the smart thing to do is to not run both copiers at the same time. Use the backup copier for very small orders while you’re more heavy duty copier can handle the majority of the work.