Question

Your insurance company has converged for three types of cars. The annual cost for each type of cars can be modeled using Gaussian (Normal) distribution, with the following parameters: (Discussions allowed!)

• Car type 1 Mean=$520 and Standard Deviation=$110
• Car type 2 Mean=$720 and Standard Deviation=$170
• Car type 3 Mean=$470 and Standard Deviation=$80

Use Random number generator and simulate 1000 long columns, for each of the three cases. Example: for the Car type 1, use Number of variables=1, Number of random numbers=1000, Distribution=Normal, Mean=520 and Standard deviation=110, and leave random Seed empty.

Next: use either sorting to construct the appropriate histogram or rule of thumb to answer the questions:

13. What is approximate probability that Car Type 1 has annual cost less than $550?

• a. Between 10% and 13%
• b. Between 23% and 29%
• c. Between 55% and 70%
• d. None of these

14. Which of the three types of cars is most likely to cost more than $1000?

• a. Type 1
• b. Type 2
• c. Type 3

15. For which of the three types we have the highest average cost?

• a. Type 1
• b. Type 2
• c. Type 3

Answer 1

The random number of car type 1 is generated in excel by following these steps,

Step 1: DATA > Data Analysis > Random number generator > OK. The screenshot is shown below,

Step 2: Enter Number of variables: 1, Number of Random numbers: 1000, Distribution: Normal, Parameters: Mean = 520 and Standard Deviation = 0.303. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

13)

Answer: c. Between 55% and 70%

Explanation: The probability is obtained by counting the number of number of car having annual cost less than $550

The number of cars having annual cost less than $550 is counted in excel using the function =COUNTIF(A2:A1001,"<550"). The screenshot is shown below,

14)

Answer: b. Type 2

Explanation: Since Type 2 cars have higher average and higher standard deviation, the annual cost will more likely to be more than $1000 compare to cars of type 1 and cars of type 3

The answer can be verify by following similar process as done in question 13 and then count the number of cars having annual costs more than $ 1000. I did the same process and found that, type cars are more likely to have annual costs more than $ 1000

Type	Number of cars (Annual cost> $1000)
1	1
2	37
3	0

15)

Answer: b. Type 2

Explanation:

From the random sampling of each of three cars type, the average values are obtained in excel using the function =AVERAGE(). The screenshot is shown below,

similarly , the average values obtained for cars type 2 and cars type 3

Type	Average cost
1	523
2	720
3	471