I need to know how to solve the question on the tab marked solve. I am using an excel addin called risk solver.
Hungry Dawg Restaurants
| Intial condition |
|
|
Assumptions |
|
|
|
|
| Number of covered employees |
18,533 |
Max Decrease |
3% |
Max increase |
7% |
Uniform |
| Average Claims per employee |
$ 250.00 |
Monthly increase |
1% |
Std Dev |
$ 3.00 |
Normal |
| Amount contributed per employee |
$ 125.00 |
Constant |
|
|
|
|
Refer to the Hungry Dawg Restaurant example presented in this chapter. Health claim costs actually tend to be seasonal, with higher levels of claims occurring during the summer months (when kids are out of school and more likely to injure themselves) and during December (when people schedule elective procedures before the next year's deductible must be paid). The following table summarizes the seasonal adjustment factors that apply to RNGs for average claims in the Hungry Dawg problem. For instance, the average claim for month 6 should be multiplied by 115%, and claims for month 1 should be multiplied by 80%.
Month 1 2 3 4 5 6 7 8 9 10 11 12
Seasonal Factor 0.80 0.85 0.87 0.92 0.93 1.15 1.20 1.18 1.03 0.95 0.98 1.14
Suppose the company maintains an account from which it pays health insurance claims. Assume there is $2.5 million in the account at the beginning of month 1. Each month, employee contributions are deposited into this account and claims are paid from the account.
a. Modify the spreadsheet to include the cash flows in this account. If the company deposits $3 million in this account every month, what is the probability that the account will have insufficient funds to pay claims at some point during the year? (Hint: You can use the COUNTIF( ) function to count the number of months in a year in which the ending balance in the account is below 0.)
b. If the company wants to deposit an equal amount of money in this account each month, what should this amount be if the company wants there to only be a 5% chance of having insufficient funds?