Benedictine Bus Anlytcs I: Predictive BALT6102

 

Attempt Score	25 / 25 - 100 %
Overall Grade (Highest Attempt)	25 / 25 - 100 %

Question 1

5 / 5 points

Q1 to Q5 are  the textbook.

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 5000 miles and that tire mileage is normally distributed. Use an Excel worksheet to simulate the miles obtained for a sample of 500 tires. Feel free to use my Excel spreadsheet.

Use the Excel COUNTIF function (see my spreadsheet or Appendix A for a description of the Excel COUNTIF function) to determine the number of tires that last longer than 40,000 miles.

What of the following is close to your estimate of the percentage of tires that will exceed 40,000 miles?

Question options:

	A)  24%
	B)  18%
	C)  10%
	D)  4%

Feedback Feel free to use my spreadsheet Your answer is CORRECT.

Question 2

5 / 5 points

See Q1. What of the following is close to your estimate of the percentage of tires that will be less than 32,000 miles?

Question options:

	A)  24%
	B)  18%
	C)  10%
	D)  4%

Feedback Feel free to use my spreadsheet Your answer is CORRECT.

Question 3

5 / 5 points

See Q1. What of the following is close to your estimate of the percentage of tires that will be less than 30,000 miles?

Question options:

	A)  24%
	B)  18%
	C)  10%
	D)  4%

Feedback Feel free to use my spreadsheet Your answer is CORRECT.

Question 4

5 / 5 points

See Q1. What of the following is close to your estimate of the percentage of tires that will be less than 28,000 miles?

Question options:

	A)  24%
	B)  18%
	C)  10%
	D)  4%

Feedback Feel free to use my spreadsheet Your answer is CORRECT.

Question 5

5 / 5 points

See Q1.

If management would like to advertise a tire mileage guarantee such that approximately no more than 10% of the tires would obtain mileage low enough to qualify for the guarantee, what tire mileage would you recommend for the guarantee?

Question options:

	A)  40,000
	B)  32,000
	C)  30,000
	D)  28,000

Feedback Feel free to use my spreadsheet Your answer is CORRECT. To solve this problem, we can simulate the tire mileage data using Excel and apply the COUNTIF function to determine how many tires last longer than 40,000 miles. Steps to Simulate the Tire Mileage in Excel: Generate Random Data: In Excel, we can use the NORM.INV function (if using newer Excel versions) or NORMINV (for older versions) to generate normally distributed data with a specified mean and standard deviation. The syntax for NORM.INV is: =NORM.INV(RAND(), mean, standard_deviation) RAND() generates a random number between 0 and 1, which is then used to find a value from a normal distribution with a given mean and standard deviation. Simulate the Data: In cell A1, input the formula to generate a random tire mileage: =NORM.INV(RAND(), 36500, 5000) Drag this formula down to fill cells A1 to A500 (for a sample size of 500 tires). Count the Tires Exceeding 40,000 Miles: Use the COUNTIF function to count how many of the simulated tire lifetimes exceed 40,000 miles. In an empty cell, input: =COUNTIF(A1:A500, ">40000") This will count how many of the 500 simulated tire lifetimes are greater than 40,000 miles. Calculate the Percentage: To calculate the percentage of tires that last more than 40,000 miles, divide the count by 500 and multiply by 100: =(COUNTIF(A1:A500, ">40000") / 500) * 100 Interpretation: The percentage of tires that last more than 40,000 miles is the result of the calculation. Based on the normal distribution with a mean of 36,500 miles and a standard deviation of 5,000 miles, we expect this percentage to be a certain value that we will determine through the simulation. Would you like help with a specific aspect of this simulation in Excel? for 5th question  To determine the mileage that would guarantee that no more than 10% of tires fall below this mileage, we need to find the value corresponding to the bottom 10% of the normal distribution with a mean of 36,500 miles and a standard deviation of 5,000 miles. This is essentially asking for the 10th percentile of the normal distribution. Steps: We use the NORM.INV function to find the value that corresponds to the 10th percentile of the distribution. The syntax is: =NORM.INV(0.1, mean, standard_deviation) Where: 0.1 represents the 10th percentile (i.e., 10% of values will be below this). The mean is 36,500 miles. The standard deviation is 5,000 miles. In Excel, you can input the formula: =NORM.INV(0.1, 36500, 5000) This will return the mileage value below which 10% of the tires will fall. Calculation: By calculating this, we can determine the mileage value that represents the 10th percentile. Let me calculate it quickly for you. It seems like I can’t do more advanced data analysis right now. Please try again later. Let me know if you’d like me to guide you through the process manually or with any other questions you might have!