QUESTION 10

1. Please answer questions 10 -16 according to the given data.

   Grear Tire Company has produced a new tire with an estimated mean lifetime mileage of 36,500 miles. Management also believes that the standard deviation is 5,000 miles and that tire mileage is normally distributed. To promote the new tire, Grear has offered to refund a portion of the purchase price if the tire fails to reach 30,000 miles before the tire needs to be replaced. Specifically, for tires with a lifetime below 30,000 miles, Grear will refund a customer $1 per 100 miles short of 30,000. We will construct a simulation model and obtain the summary statistics. (Use 1,000 trials.)

   Download the attached Excel file and answer the question accordingly.

   HW 8 Gear Tire Company - Simulation.xlsx

   In column B, for each trial from 1 to 1000, we want to know how many miles a tire will go before it fails.

   What is the correct formula we write in cell B11 that will give us the number of miles a tire will go before it fails?

   Important: Please pay attention to absolute references, i.e., $ signs in the formula when implementing it in Excel.

   	A.	=NORM.DIST(RAND(),$B$4,$B$5)
   	B.	=NORM.S.INV(RAND(),$B$4,$B$5)
   	C.	=NORM.S.DIST(RAND(),$B$4,$B$5)
   	D.	=NORM.INV(RAND(),$B$4,$B$5)

QUESTION 11

1. In column C, we want to know if the number of miles a tire went before it failed is less than 30,000. Since we will refund customers accordingly, this information is important for our analysis.

   Assuming that cell B11 has the correct formula. Which one do we write in cell C11 to get 1 if the number of miles driven is less than 30,000 and 0 otherwise?

   Important: Please pay attention to absolute references, i.e., $ signs in the formula when implementing it in Excel.

   	A.	=IF(B11>$E$1,1,0)
   	B.	=IF(B11>$E$1,30000,0)
   	C.	=IF(B11<$E$1,1,0)
   	D.	=IF(B11<$E$1,30000)

QUESTION 12

1. In column D, we want to know if the tire's lifetime mileage was below 30,000, then by how many 100 miles was it short. Since we will refund customers $1 per 100 miles short of 30,000, this information is also necessary.

   Assuming that cell C11 has the correct formula. Which one do we write in cell D11 to get the number of 100-miles if the lifetime is less than 30,000 and 0 otherwise?

   Important: Please pay attention to absolute references, i.e., $ signs in the formula when implementing it in Excel.

   	A.	=MIN(($E$1-B11)/100,0)
   	B.	=IF(B11=1,($E$1-C11)/100,0)
   	C.	=($E$1-B11)/100
   	D.	=IF(C11=1,($E$1-B11)/100,0)

QUESTION 13

1. In column E, we want to calculate the amount of money we should refund per tire.

   Assuming that cell D11 has the correct formula. Which one do we write in cell E11 to get the refunded amount per tire?

   Important: Please pay attention to absolute references, i.e., $ signs in the formula when implementing it in Excel.

   	A.	=IF(B11<$E$1,0,(B11-$E$1)*$C$1)
   	B.	=(B11-$E$1)*$C$1
   	C.	=D11*$E$1
   	D.	=D11*$C$1

QUESTION 14

1. Now that we have the correct formulas in B11:E11, run the simulation for 1000 trials.

   Since the first trial is in row 11, drag down each cell until row 1010 to obtain 1000 observations. Note: You can also double-click the lower right corner of each cell (B11, C11, D11, and E11).

   Make sure there is no error message in the table you obtained. If there are error messages, it probably means that you didn't make an absolute reference to a cell when it was necessary.

   What is the mean refund amount?

QUESTION 15

1. In cell H14, we want to calculate the number of times Gear paid more than a $50 refund for a tire.

   Which one do we write in cell H14 that number?

   	A.	=COUNTIF(E11:E1010,">0")
   	B.	=COUNTIF(E11:E1010,">50")
   	C.	=COUNTIF(E11:E1010,"<50")
   	D.	=COUNTIF(E11:E1010,"<0")

QUESTION 16

1. What is the probability that Grear will refund more than $50 for a tire? This can be calculated as follows: divide [the number of times they pay more than $50] by [the number of trials]. So, it is H14/1000 in our case since we did 1000 trials. (Round your answer to three decimal places.)