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%.

Suppose that the company maintains an account from which it pays health insurance claims. Assume that 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 shown in Figure to include the cash flows in this account. If the company deposits $3 million into this account every month, what is the probability that the account will have insufficient funds to pay claims at some point during the year? Use 5000 replications.
b. If the company wants to deposit an equal amount of money in this account each month, what should this amount be if they want there to only be a 5% chance of having insufficient funds?

Month 2 3 45 6 78 9 10 12 Seasonal 0.80 0.85 0.87 0.92 .93 1.15 120 .18 1.03 0.95 0.98 1.14 Factor i129sts Competibility Madel-Mirusurt Escel EN, Home Insert Page Layout Formulas Dsta Renew View Add-Ins Cystal Ball - 4 Initial Conditions 5 Number of Covered Employees 18,533 Max Decrease 3.0% Max Increase 7% Uniform Distribution 6Average Claim per Employee 7 Amount Contributed per Employa $125 Constant $2.50-lthly increase, 1.0% Std Dev 53 Nonal Distribution 10 MonthE 18,904] $2.353.000 $252.50) $4,773,260 19,567$2,458,375 $257.50$5,065,826 20,061$2 507625 $260.15 $5 218,869 20,462$2557,750 $762.755,376,391 16 6 20,871 $2,609.875 $265.38 5,538,746 21,2892 661,125 $268.03$5,706,091 18 21,714| | 52,714,250 $270.71| 55,678,197 19 9 22,149 2,768,625 $27342 $6.055,980 20 10 225 2,824,000 $276.16 6,239,007 21 11 23,043 2.880.375$278.92 6.427.154 Total Company Cost 36,126,069 Forecast Cell Assumption Cell M Health Claims ModelConfidence Reacty Key Cell Formulas & CB Settings Cell Copied to CB Distribution: Uniform Minimum: -D5 (1-F5) Maximum: D5"11+H5) B13B22 (Using CB copy & paste) Minimum: =B11 *(1-SFS5) Maximum: B11(1+SHS5) D12:D22 (Using copy & paste) -D11 B11