Probability and distribution Problem

maxg1990

New Member
Joined
Jun 21, 2014
Messages
4
I have to formulate this in excel and was wondering if anyone could help me with the formulas.

It has also determined that the number of repair claims filed each week is a random variable
which can take values 1, 2, 4, 5, 6, 7, 8, 9. The probability of having 1 or 9 claims in a week is
equal. The probability that there are 2, 6, 7 or 8 claims in a week is twice the probability of
there being a single claim. The probability that there are 4 claims in a week is twice the
probability that there are 2 claims in a week. The probability that there are 5 claims in a week is
6 times the probability that there is a single claim in a week. The dollar amount per claim fits a
normal distribution with a mean claim amount of $2000 and a standard deviation of $400.

In addition to repair claims, the company also receives claims for cars that have been “totaled”
and cannot be repaired. There is a 20% chance in any week of receiving this type of claim. The
claims for “totaled” cars cost has a uniform distribution, in the range $10000 to $35000. Not all
repair claims are legitimate: 1% of the repair claims filed are rejected. Of the “totaled” claims
filed, 0.5% of them are rejected.

I'm not sure how to make the formulas for paragraph 1. And then I don't know how to incorporate the probability into the random uniform distribution and how to incorporate the reject variable.

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
O​
P​
Q​
R​
1​
Claims​
Likelihood​
Prob​
2​
1​
1​
5%​
Q2: =P2/$P$12
3​
2​
2​
10%​
4​
3​
0​
0%​
5​
4​
4​
20%​
6​
5​
6​
30%​
7​
6​
2​
10%​
8​
7​
2​
10%​
9​
8​
2​
10%​
10​
9​
1​
5%​
11​
12​
20​
 

maxg1990

New Member
Joined
Jun 21, 2014
Messages
4
O​
P​
Q​
R​
1​
Claims​
Likelihood​
Prob​
2​
1​
1​
5%​
Q2: =P2/$P$12
3​
2​
2​
10%​
4​
3​
0​
0%​
5​
4​
4​
20%​
6​
5​
6​
30%​
7​
6​
2​
10%​
8​
7​
2​
10%​
9​
8​
2​
10%​
10​
9​
1​
5%​
11​
12​
20​

<tbody>
</tbody>

Ok thank you for the quick reply? Do I need to make a cumulative probability now? How would I incorporate the 'totaled' car claims. The question asks:


  1. Create a spreadsheet model of the total claims cost incurred by the company for 100 weeks. Calculate the average payout per week. If the company decides to keep $20,000 cash on hand to pay claims, in how many of the weeks is this amount of money insufficient to pay the claims?


 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
We don't do homework, but will help if you have a narrow and specific question. Flail at it for a while.
 

maxg1990

New Member
Joined
Jun 21, 2014
Messages
4
Ok I understand. How do you involve the "totalled" claims to these probabilities ??
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,716
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top