Question

hi chapter 12 Q3: here is the question:

1. modify the spreadsheet shown in Fig 12.9 to include the cash flows in this account. If the company deposits $3m 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?

how do i calculate beginning balance from month two knowing month one is $2,500,000

Answer 1

Let,

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

The aim is to modify the data of Hungry Dawg Restaurants and calculate the probability that the account will have insufficient funds to pay claims during the year.

Initial Condition		Assumption:
Number of Covered Employees	18,533	Increasing	2% per month
Average Claim for Employees	$250	Increasing	1% per month
Amount contributed over Employee	$125	Constant

To implement the formula to generate a random number of employees covered by the health plan.

Use the MS Excel function: PsiUniform ()

The change in the number of employees from one month to next can vary between a 2% increase and 1% increase.

In order to obtain total claims use the formula:

Total Claims = Number of Employees * Average claim per employees * Seasonal factor

In order to obtain Company Cost use the formula:

Company Cost = Total Claims - Employee Contribution

In order to obtain ending balance use the formula:

Ending Balance = Beginning Balance + Company Contribution - Company Cost

Use MS Excel function, PsiOutput:

Month	Beginning	Number of	Employee	Average claim	Seasonal	Total	Company	Company	Ending
	Balance	Employees	Contribution	per Employee	Factor	Claims	Cost	Contribution	Balance
1	$2,500,000	18904	$2,363,000	$252.50	0.8	$4,773,260	$2,410,260	$3,000,000	$3,089,740
2	$3,981,178	19282	$2,410,250	$255.03	0.85	$4,917,488	$2,507,238	$3,000,000	$4,473,940
3	$5,220,205	19667	$2,458,375	$257.58	0.87	$5,065,826	$2,607,451	$3,000,000	$5,612,754
4	$6,171,191	20061	$2,507,625	$260.15	0.92	$5,218,869	$2,711,244	$3,000,000	$6,459,947
5	$6,797,751	20462	$2,557,750	$262.75	0.93	$5,376,391	$2,818,641	$3,000,000	$6,979,110
6	$7,374,922	20871	$2,608,875	$265.38	1.15	$5,538,746	$2,929,871	$3,000,000	$7,445,051
7	$6,343,449	21289	$2,661,125	$268.03	1.2	$5,706,091	$3,044,966	$3,000,000	$6,298,483
8	$5,018,492	21714	$2,714,250	$270.71	1.18	$5,878,197	$3,163,947	$3,000,000	$4,854,546
9	$3,868,958	22149	$2,768,625	$273.42	1.03	$6,055,980	$3,287,355	$3,000,000	$3,581,603
10	$3,528,882	22592	$2,824,000	$276.16	0.95	$6,239,007	$3,415,007	$3,000,000	$3,113,875
11	$3,486,815	23043	$2,880,375	$278.92	0.98	$6,427,154	$3,546,779	$3,000,000	$2,940,036
12	$3,036,043	23504	$2,938,000	$281.71	1.14	$6,621,312	$3,683,312	$3,000,000	$2,352,731
							$36,126,071
							#Negative Months		0
							Insufficient funds		0
							P(Insufficient funds)		0.3246
							5% chance of insufficient funds		34,319,767

From the above output, there are 32.46% of the probability have insufficient funds to pay claims during the year.

From the above output, if they want their only be a 5% chance of having insufficient funds then the amount approximately $34,400,000.