Question

Using the following data, prepare a cash budget for Plains Medical Center for Fiscal 2018. You can assume that the Medical Center started the fiscal year with $7,264,871 in cash on its balance sheet.

Once you compile that budget consider the following issues:

Assume the Medical Center wishes to keep a minimum cash balance each month that equals one month of salary expense plus one month of salary and other expenses. In addition, they are required to keep one year's worth of debt service payments in cash on hand each month. So your minimum cash balance must be the sum of those salary/expense items noted here, plus one year of debt payments. What is the minimum cash balance that Plains Medical Center should keep each month based on these assumptions?

Look at your cash budget calculations. Are there any months where the cash balance in that month falls below that minimum? If so, what management actions can you take to address this issue? Be specific!

Plains Medical Center
Cash Budgeting Data
Fiscal Year 2018 (7/1/17-6/30/18)
Estimated Patient Billings (at expected collection amount)
July	$7,219,178
August	7,219,178
September	6,986,301
October	7,219,178
November	6,986,301
December	7,219,178
January	7,219,178
February	6,520,548
March	7,219,178
April	6,986,301
May	7,219,178
June	6,986,301
Total	$85,000,000
50% of billings go to government payers, 40% to commercial payers, and 10% are self-pay accounts
The historic timing of collections looks like this:
		Government		Commercial	Self-Pay
Month of service		15%		10%	2%
Month following service		50%		45%	10%
Second month following service		15%		15%	10%
Third month following service		10%		15%	10%
Fourth month following service		5%		10%	8%
Uncollectable		5%		5%	60%
Total		100%		100%	100%
You can assume that billings increased by 5% each month from year to year, so you can use  March 2017/1.05 as a part of estimating collections for July 2018
Salaries for each month are estimated at $2,250,000 + 20% of that month's billings.
* Payments for Salaries are 50% of the current month estimate + 50% of the prior month's estimate
* Hourly employees are given a 5% bonus at Christmas (paid during December) totaling 10% of estimated salaries for December
Supply purchases are estimated at 17.5% of the billings in that month
* Payments for supplies are made in total in the following month
Other operating expense purchases are estimated at 12.5% of the billings in that month
* Payments for other expenses are made in total in the following month
The hospital was constructed using $18,000,000 in debt, payable in monthly installments over 25 years at 6% annual interest
It recognizes $445,000 in straight line depreciation expense each month. Depreciation is not funded.
The Board of Directors has approved the following Capital Expenditure budget for Fiscal 2018:
Item		Cost		Projected Purchase Date
Lab Chemistry Analyzer		$250,000		9/30/17
Replace CT Scanner			1,050,000	11/30/17
Replace two Anesthesia Machines			775,000	4/30/18
Total			$2,075,000

Answer 1

	July	August	September	October	November	December	January	February	March	April	May	June	Total
Cash Receipts:
Government Payers	3211682	3345194	3377676	3362296	3394178	3359246	3405822	3365068	3248630	3359347	3335959	3376712	40141710
Commercial Payers	2530962	2643072	2686500	2687619	2720000	2687397	2720000	2701370	2608219	2692055	2659452	2692055	32028700
Self-Pay	258548	270754	279494	283688	285973	284110	284575	285041	279918	281315	279452	280384	3353252
Cash Payments
Salaries	3659459	3693836	3670548	3670548	3670548	3670548	3693836	3623973	3623973	3670548	3670548	3670548	43988910
Supply Purchase	1203196	1263356	1263356	1222603	1263356	1222603	1263356	1263356	1141096	1263356	1222603	1263356	14855593
Other Operating Expenses	859426	902397	902397	873288	902397	873288	902397	902397	815069	902397	873288	902397	10611138
Bonus on Christmas						369384
Capital Expenditure			250000		1050000					775000
Loan Repayment	6854	6854	6854	6854	6854	6854	6854	6854	6854	6854	6854	6854	82248
Total Cash Receipt	272257	392577	250514	560311	(493005)	188077	543954	554899	549776	(285539)	501571	505995	3541388
Opening Balance	7264871	7537128	7929705	8180219	8740530	8247526	8435603	8979557	9534456	10084233	9798694	10300264	10806259
Closing Balance	7537128	7929705	8180219	8740530	8247526	8435603	8979557	9534456	10084233	9798694	10300264	10806259	14347648
Patient Billings	7219178	7219178	6986301	7219178	6986301	7219178	7219178	6520548	7219178	6986301	7219178	6986301	84999998
Government Payers	3609589	3609589	3493151	3609589	3493151	3609589	3609589	3260274	3609589	3493151	3609589	3493151	42499999
Commercial Payers	2887671	2887671	2794520	2887671	2794520	2887671	2887671	2608219	2887671	2794520	2887671	2794520	33999999
Self Pay	721918	721918	698630	721918	698630	721918	721918	652055	721918	698630	721918	698630	8500000
Salaries for the current month	3693836	3693836	3647260	3693836	3647260	3693836	3693836	3554110	3693836	3647260	3693836	3647260	44000000
Supply Purchases for the month	1263356	1263356	1222603	1263356	1222603	1263356	1263356	1141096	1263356	1222603	1263356	1222603	14875000
Other Operating Expenses	902397	902397	873288	902397	873288	902397	902397	815069	902397	873288	902397	873288	10625000
Calculation of previous months revenue	March	April	May	June
Patient Billings	5939236	6236197	6548007	6875408
Government Payer	2969618	3118099	3274004	3437704
Commercial Payer	2375694	2494479	2619203	2750163
Self pay	593924	623620	654801	687541
Salaries	3437847	3497239	3559601	3625082
Supply Purchase	1039366	1091335	1145901	1203196
Other Operating Expenses	742404	779525	818501	859426
Minimum Cash Requirement
Salaries	3693836	3693836	3647260	3693836	3647260	3693836	3693836	3554110	3693836	3647260	3693836	3647260	44000000
Other Operating Expenses	902397	902397	873288	902397	873288	902397	902397	815069	902397	873288	902397	873288	10625000
Loan Repayment of 1 year	82248	82248	82248	82248	82248	82248	82248	82248	82248	82248	82248	82248	986976
Total	4678481	4678481	4602796	4678481	4602796	4678481	4678481	4451426	4678481	4602796	4678481	4602796	55611975

Cash receipts have been calculated as the sum of the various payments received from government payers, commercial payers and self-payers including the percentages given in the questions received for the previous months.

As we can see above that the closing cash balance exceeds the minimum cash requirements. So there is no need to take any action. Moreover, since the company has more than sufficient cash so it can invest the extra cash for short term and earn extra income.