Company Policy Examples - Excel

Document Sample
Company Policy Examples - Excel Powered By Docstoc

ClaimsStress.xls: Stress Analysis
This example shows how you might model the uncertainty involved in payment of insurance claims. To model this properly,
you must account for the uncertainty in both the total number of claims and the dollar amounts of each claim made.
Cell D29 contains an @RISK distribution function which determines the total number of claims. Each of the cells in the range
D33:AK33 contain another distribution to determine the payment amount for each claim. In each of these cells, an IF statement
is used to compare the "number" of the claim (in the row above) to the total number of claims. Only those claims with numbers
less than or equal to the value in D29 will return a positive value. The others will all return zero. The simulation output in cell D36
is the total payment amount.
Try setting the "Standard Recalc" option on the "Sampling" tab of the @RISK simulation settings dialog to "Monte-Carlo" and then
repeatedly press the recalc key (F9). Watch how the number of non-zero rows and the the total claim amount change.
Stress Analysis:
Suppose that the company is required by law to have enough money on hand to pay all the claims with the probability of 95%,
and that it can only set aside $2000 for the purposes of this particular insurance product. On the other hand, a simulation
of the model shows that the 95th percentile of the Total Payment Amount is around $2700. Assume further that the
company can purchase from a larger company an insurance policy against the number of claims being in the top decile
of the distribution in D29. The policy under consideration specifies that if the number of claims falls within the top decile,
the larger company will satisfy all the claims. The smaller company can model the situation with the policy in place by using
Stress Analysis to stress the distribution in D29 from the 0th to 90th percentile. With the modified distribution the 95th percentile
of the Total Payment Amount is reduced to around $1650. If the policy costs up to $350, the smaller company can
purchase it and keep $1650 on hand to comply with the law.
Would the larger company be willing to sell the policy for under $350? There is a 10% probability that it will be required
to make payments under the policy. The payments can be analyzed using the same model and stressing the distribution in D12
from the 90th to 100th percentile. This analysis shows the mean payment to be around $2800. Since there is only
a 10% probability that claims will need to be satisfied, the mean cost to the larger company is around $280. Hence,
it does not seem unreasonable for the larger company to sell the policy for $350.

No. of Claims                         #NAME?

Claim#                                    1              2              3              4                5               6
$Amount                                #NAME?         #NAME?         #NAME?         #NAME?           #NAME?          #NAME?

Total Payment Amount                  #NAME?

                                                               Page 1

cells in the range
ls, an IF statement
 ims with numbers
on output in cell D36

onte-Carlo" and then

obability of 95%,

 the 95th percentile

 distribution in D12

                        7        8        9       10       11       12       13       14       15
                     #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?

                                                             Page 2

  16       17       18       19       20       21       22       23       24
#NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?

                                        Page 3

  25       26       27       28       29       30       31       32       33
#NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?   #NAME?

                                        Page 4


         Page 5

Description: Company Policy Examples document sample