Question
Suppose you own an expensive car and purchase auto insurance. This insurance has a $1000 deduct- ible, so that if you have an accident and
Suppose you own an expensive car and purchase auto insurance. This insurance has a $1000 deduct- ible, so that if you have an accident and the damage is less than $1000, you pay for it out of your pocket. However, if the damage is greater than $1000, you pay the first $1000 and the insurance pays the rest. In the current year there is probability 0.025 that you will have an accident. If you have an accident, the damage amount is normally distributed with mean $3000 and standard deviation $750.
a.Use Excel to simulate the amount you have to pay for damages to your car. This should be a
one-line simulation, so run 5000 iterations by copying it down. Then find the average amount you pay, the standard deviation of the amounts you pay, and a 95% confidence interval for the average amount you pay. (Note that many of the amounts you pay will be 0 because you have no accidents.)
PLEASE SHOW THE EXCEL SHEET , SHOW ME VISUALS PLEASE
b. Continue the simulation in part a by creating a two- way data table, where the row input is the deducti- ble amount, varied from $500 to $2000 in multiples of $500. Now find the average amount you pay, the standard deviation of the amounts you pay, and a 95% confidence interval for the average amount you pay for each deductible amount.
c. Do you think it is reasonable to assume that dam- age amounts are normally distributed? What would you criticize about this assumption? What might you suggest instead?
\begin{tabular}{|l|l|r|} \hline & A & \multicolumn{1}{|c|}{ B } \\ \hline 1 & Deductible on auto insurance \\ \hline 2 & & \\ \hline 3 & Deductible & $1,000 \\ \hline 4 & & \\ \hline 5 & Prob of accident & 0.025 \\ \hline \end{tabular} 7 Damage amount, normally distributed Part b - data table \begin{tabular}{|l|l|r|} \hline & A & \multicolumn{1}{|c|}{ B } \\ \hline 1 & Deductible on auto insurance \\ \hline 2 & & \\ \hline 3 & Deductible & $1,000 \\ \hline 4 & & \\ \hline 5 & Prob of accident & 0.025 \\ \hline \end{tabular} 7 Damage amount, normally distributed Part b - data tableStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started