Using the following data, prepare a cash budget for Plains Medical Center for Fi
ID: 2436488 • Letter: U
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
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
Explanation / Answer
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.
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 55611975Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.