I NEED STEP BY STEP EXCEL INSTRUCTIONS! WHAT FORMULAS TO PUT INTO EACH CELL AND EXACTLY WHAT TO CLICK IN ANALYTICAL SOLVER AND WHAT VALUES TO PUT IN IT! PLEASE. HELP!

5. Reconsider the Heavy Duty Co, problem that was presented as Example 2 in Section 12.1. For each of the following three options in parts a through c, obtain an estimate of the expected cost per day by using Analytic Solver to perform 1,000 trials of a computer simulation of the problem on a spreadsheet. Estimate the mean and generate a frequency chart.

a. The option of not replacing a motor until a breakdown occurs.

b. The option of scheduling the replacement of a motor after four days (but replacing it sooner if a breakdown occurs).

c. The option of scheduling the replacement of a motor after five days (but replacing it sooner if a breakdown occurs).

d. An analytical result of $2,000 per day is available for the expected cost per day if a motor is replaced every three days. Comparing this option and the above three, which one appears to minimize the expected cost per day?

Example 2: Corrective Maintenance versus Preventive Maintenance The Heavy Duty Company has just purchased a large machine for a new production process. The machine is powered by a motor that occasionally breaks down and requires a major overhaul because of its heavy-duty use. Therefore, the manufacturer of the machine also provides a second standby motor. The two motors are rotated in use, with each one remaining in the machine until it is removed for an overhaul (perhaps because it has just broken down) and replaced by the other one. Given the planned usage of the machine, its manufacturer has provided the company with information about the durability of the motors (the number of days of usage until a breakdown occurs). This information is shown in the first two columns of Table 12.1. The first column lists the number of days the current machine has been in use. For each of these days, the second column then gives the probability that the breakdown will occur on that day. Since these probabilities are 0 except for days 4,5 , and 6 , the breakdown always occurs during the fourth, fifth, or sixth day. The third column provides a straightforward way of assigning random numbers to correspond to each of these three possibilities. TABLE 12.1 The Probability Distribution of Breakdowns for Heavy Duty's Motors, and the Corresponding Random Numbers Fortunately, the time required to overhaul a motor never exceeds three days, so a replacement motor always is ready when a breakdown occurs. When this happens, the remainder of the day (plus overtime if needed) is used to remove the failed motor and install the replacement motor, so the machine then is ready to begin operation again at the beginning of the next day. The average costs incurred during each replacement cycle (the time from when a replacement of a motor begins until just before another replacement is needed) are estimated as summarized below. Heavy Duty management now needs to decide on the policy for when a motor should be replaced. One option (referred to as corrective maintenance) is to wait until a breakdown occurs before removing and replacing the motor. Another option (referred to as preventive maintenance) is to try to replace the motor before a breakdown occurs. The three possibilities for the latter option are to always replace the motor (i) after three days, or (ii) after four days, or (iii) after five days, assuming it didn't already break down and so required early replacement. The analysis below begins with the corrective maintenance option and then turns to the preventive maintenance option a little later. Using Computer Simulation for the Corrective Maintenance Option Computer simulation can be used to estimate what the average daily cost would be for replacing the motors whenever they break down (but not earlier). This requires using random numbers to determine when breakdowns occur in the simulated process. Using the probabilities in the second column of Table 12.1,25 percent of the possible random numbers need to correspond to a breakdown on day 4,50 percent to a breakdown on day 5 , and the remaining 25 percent to a breakdown on day 6 . The rightmost column of Table 12.1 shows the natural way of doing this. Excel provides a convenient VLOOKUP function for implementing this correspondence between a random number and the associated event. Figure 12.4 illustrates how it works. One step is to create the table shown in columns I, J, and K, where columns K and I come directly from the first two columns of Table 12.1. Column J gives the cumulative probability prior to the number of days in column K, so J8=I7 and J9=I7+I8. Cells J7:K9 then constitute the lookup table for the VLOOKUP function. The bottom of the figure displays how the VLOOKUP command has been entered into the column D cells. The first argument of this function indicates that the cell in the same row of RandomNumber ( C5:C34 ) provides the random number being used. The second argument gives the range for the lookup table. The third argument (2) indicates that column 2 of the lookup table (K7:K9 in this case) is providing the number being entered into this cell in column D. The choice of the number in column 2 of the lookup table is based on where the random number falls within the ranges implied by the rows in column 1 of this table. In particular, the three possible choices are if 0 RAND 0