Budgeting Expense and Revenue Case Study It is January of 2014. The SC Department of Health and Environmental Control Public Health Region 4 operates 8
Budgeting Expense and Revenue Case Study
It is January of 2014. The SC Department of Health and Environmental Control Public Health Region 4 operates 8 public health department clinics and 6 Home Health operations in addition to other public health functions. During the last two years their clinical revenue has dropped. The clinic revenue makes up about 25% of the total funds used to pay for region operations. About 25% of the total district funds are state funds and the remaining funds are from various federal programs.
The Region Director has asked you, the Region Administrator to give him an update on the financial condition of the region and a plan to solve any of the financial problems you identify. As you start to look you realize that time is not on your side. It is currently in the beginning of January which is midway through the current fiscal year and you have to have a balanced budget by the end of the year or your evaluation will not be good.
As you review the current financial statements several things become clear. Your earned revenue budget is over budget. Your total budgeted expenses are greater than your total revenue as well. Since half the year is already gone, and you use the July through June fiscal year for budgeting and expenditures, you know that you only have about half the year to enact changes to bring the budget into balance by the end of the year. This includes cash and budgeted amounts. That also means that you have already spent a large part of the years budget so any changes you make will have to be much bigger than if you had made the changes at the beginning of the year.
There are a number of things you know about the expense budget. The classified FTEs are permanent full time equivalent positions. There are 228 filled positions and 16 vacant positions. The average salary for the staff positions is $43,000. Since there are some positions vacant, if you decide that they do not need to be filled, you may not need all of the funding in that category. You also may have some funds that were budgeted for positions that has not been spent. Region managers are currently working to fill all of the vacant positions. There are also 58 temporary employees working in various jobs throughout the region and in the various programs. The region has never done a Reduction in Force Plan, which is needed if you decide you need to terminate the full time employees. If one is needed, it will take at least 2 months to develop and implement one. Temporary staff can be eliminated more quickly without a reduction in force plan, with about a weeks delay.
There is a sense that some of the employees may not be performing the level of services that they should be performing which would cause the revenue for the earned services to be less that they should be generating, but you have not been able to identify the amount they should be generating. 73 of the filled permanent positions and 39 of the temporary positions should be providing at least some level of earned services that can be billed.
The Medicaid revenue can be used in the program that generated it, which does not include WIC, which is 100% federally funded. WIC funds and other federal funds can only be expended in the programs the funds where they were granted. For the earned funds, this restriction will most likely not be a problem.
It takes about a month for the region to record all of the operating costs, so there may be at least one whole month of expenses that are not recorded yet.
For the operating category, Travel is for mileage and meals reimbursement for staff who are required to travel for the agency. Child Health Home visits, for example. Some staff trips are necessary to go to various trainings that are required and routine meetings at the Region office and the Central Office which is in Columbia. About 78% of the travel is for services with the remaining going to meetings and trainings.
Some of the operating funds, like Contractual, fixed charges, case services and equipment, dont come in at a certain amount per month. Contractual is paid as the contracts are reimbursed. They usually have at least a 4 month delay before they hit the system and the reports. Fixed charges are usually paid once, at the beginning of the year. Supplies are used at about the same amount per month, but go into inventory. At this point you have about 4 months of supplies built up into inventory.
Case Services are for health care services for the region, they are also delayed before they hit the system and usually show up about 3 months after the services are done.
In trying to get started, you identify several steps you think you should take. They include:
- Get an accurate estimate of the actual revenue you will have to pay the bills for this fiscal year.
- Get an accurate estimate of the actual expense you expect to pay. It seems like the budget may be overestimating or underestimating some items in the budget.
- Figure out what the difference is between the actual revenue and the actual expenses, which will tell you the amount of the actual deficit.
- Figure out what steps you will need to take to balance the budget by the end of the fiscal year.
The Regional Director expects you to produce a one page summary that can be shared with the Region Management Team that lays out your plan to balance the budget. Include any appendixes that will help explain your projections and the actions you plan to take.
B32 X Fix A B Revenue Budget Projections 2014 C D E 3 $ 4,316,210 4. State Funds 5 6 7 Federal Funds 8 WIC 9 Family Planning 10 MCH Block Grant 11 Immunizations 12 Total 13 14 15 16 Clinic Earned Funds 17 Medicaid 18 Private Pay 19 Other 20 Total $ 4,785,632 $ 2,365,485 $ 768,956 $ 465,960 $ 8,386,033 $ 3,265,425 $ 789,546 $ 54,695 $ 4,109,666 21 22 23 Total Revenue $16,811,909 24 25 D31 X V fi B =+C31+B31 C A D E F G H I J Region 4 Medicaid Revenue 534031 Projection January 2014 -Nm7 D + Reimb 6 Mos Avg 5 Mos Projected $456,185 Projected Acct Receivable $245,226 receptable26 Future Revenue $701,411 $701,411 Gross Less Revenue Match $684,016 $ (68,402) $ YTD Cash 583,139 Net Rev To Region $ 1,198,753 0.9752 7 Program Code 8 Family Planning 9 10 Children' Health 11 Child Health Home Visit 12 Child Health @ Risk 13 Child Health 14 Child Health Initiative 15 EPSDT 16 Immunization 17 Immunization Admin 18 19 CRS/BabyNet 20 BabyNet 21 CRS Nurse $159,045 $25,215 $75,625 $32,500 $108,215 $52,635 $12,500 $45,265 $12,452 $27,857 $12,365 $37,895 $19,870 $3,895 $204,310 $37,667 $103,482 $44,865 $146,110 $72,505 $16,395 0.96244 0.96244 0.96244 0.96244 0.96244 0.96244 0.96244 $196,636 $ $36,252 $ $99,595 $ $43,180 $ $140,622 $ $69,782 $ $15,779 $ (59,555) $ (10,980) $ (30,164) $ (13,078) $ (42,590) $ (21,135) $ (4,779) $ 27,250 24,654 9,562 45,215 16,871 24,556 26,584 $ $ $ $ $ $ $ 164,331 49,927 78,993 75,317 114,903 73,203 37,584 $69,852 $35,684 $35,642 $12,785 $105,494 $48,469 0.96267 0.96267 $101,556 $ $46,660 $ (30,758) $ (14,132) $ 3,654 $ 6,549 $ 74,452 39,077 22 $167,896 $27,985 $36,256 $58,759 $8,753 $9,856 $226,655 $36,738 $46,112 0.95365 0.95365 0.95365 $216,150 $ $35,035 $ $43,975$ (65,465) $ (10,611) $ (13,319) $ 85,478 $ 56,914 $ 55,142 $ 236,162 81,338 85,798 23 Miscellaneous 24 General Clinic 25 Lab 26 Lab CLIA 27 28 Preventive Health 29 HIV 30 STD 31 TB 32 $35,252 $37,595 $23,654 $1,453 $1,857) $1,654 $36,705 $39,452 $25,308 0.98523 0.98523 0.98523 $36,163 $ $38,869 $ $24,934 $ (10,953) $ (11,772) $ (7,552) $ 8,457 $ 6,594 $ 6,489 $ 33,667 33,691 23,871 r fir =+C31+B31 - B C $27,985 $8,753 $36,256 $9,856 D $36,738 $46,112 E 0.95365 0.95365 F $35,035 $ $43,975 $ G (10,611) $ (13,319) $ H 56,914 $ 55,142 $ I 81,338 85,798 D31 - X 25 Lab 26 Lab CLIA 27 28 Preventive Health 29 HIV 30 STD 31 TB 32 33 Family Support 34 FSS Family Planning 35 FSS Child Health 36 FSS CRS 37 FSS Preventive Health 38 FSS STD/HIV 39 FSS TB $35,252 $37,595 $23,654 $1,453 $1,857 $1,654 $36,705 $39,452 $25,308 0.98523 0.98523 0.98523 $36,163 $ $38,869 $ $24,934 $ (10,953) $ (11,772) $ (7,552) $ 8,457 $ 6,594 $ 6,489 $ 33,667 33,691 23,871 $53,674 $27,845 $26,985 $3,465 $6,789 $5,481 $1,847 $1,958 $1,654 $1,543 $2,854 $987 $55,521 $29,803 $28,639 $5,008 $9,643 $6,468 0.8325 0.8325 0.8325 0.8325 0.8325 0.8325 $46,221 $ $24,811 $ $23,842 $ $4,169 $ $8,028 $ $5,385 $ (13,999) $ (7,515) $ (7,221) $ (1,263) $ (2,431) $ (1,631) $ 18,749$ 6,459 $ 5,564 $ 4,875 $ 2,365 $ 2,465 $ 50,971 23,755 22,185 7,781 7,961 6,219 40 41 Totals 42 $1,480,333 $546,427 $2,026,760 $1,941,660 $ (449,304) $ 1,027,585 $ 2,519,940 43 Projected Revenue Carry Forward Cash Balance Cash Transfers Available to Budget $ 2,519,940 $ (453,169) $ 65,234 $ 2,132,005 Current Budget Projected Surplus (Deficit) $ 3,265,425 $ (1,133,420) J14 x A fx Expense Budget 1-Jan-14 $ $ $ $ $ $ Budget YTD Expenses Current Balance 10,227,451 $ 4,909,176 $ 5,318,275 1,425,896 $ 706,654 $ 719,242 3,265 $ 3,452 $ (187) 3,529,446 $ 1,698,134 $ 1,831,311 664,427 $ 320,299 $ 344,128 15,850,484 $ 7,637,716 $ 8,212,769 4 Personnel 5 Classified FTE's 6 Temporary Staff 7 Overtime 8 Fringe Benefits 9 Indirect Cost 10 Subtotal Personnel 11 12 Operating 13 Travel 14 Contractual 15 Supplies 16 Fixed Charges 17 Case Services 18 Equipment 19 Other 20 Subtotal Operating 21 22 Total Expense Budget 23 110,042 224,115 119,402 $ $ $ $ $ $ $ $ 235,694 $ 248,965 $ 475,987 $ 32,654 $ 569,878 $ 135,987 $ 24,856 $ 1,724,021 $ 125,652 $ 24,850 $ 356,585 $ 32,654 $ 165,485 $ 125,326 $ 12,654 $ 843,206 $ 404,393 10,661 12,202 880,815 $ 17,574,505 $ 8,480,922 $ 9,093,584 24 B32 X Fix A B Revenue Budget Projections 2014 C D E 3 $ 4,316,210 4. State Funds 5 6 7 Federal Funds 8 WIC 9 Family Planning 10 MCH Block Grant 11 Immunizations 12 Total 13 14 15 16 Clinic Earned Funds 17 Medicaid 18 Private Pay 19 Other 20 Total $ 4,785,632 $ 2,365,485 $ 768,956 $ 465,960 $ 8,386,033 $ 3,265,425 $ 789,546 $ 54,695 $ 4,109,666 21 22 23 Total Revenue $16,811,909 24 25 D31 X V fi B =+C31+B31 C A D E F G H I J Region 4 Medicaid Revenue 534031 Projection January 2014 -Nm7 D + Reimb 6 Mos Avg 5 Mos Projected $456,185 Projected Acct Receivable $245,226 receptable26 Future Revenue $701,411 $701,411 Gross Less Revenue Match $684,016 $ (68,402) $ YTD Cash 583,139 Net Rev To Region $ 1,198,753 0.9752 7 Program Code 8 Family Planning 9 10 Children' Health 11 Child Health Home Visit 12 Child Health @ Risk 13 Child Health 14 Child Health Initiative 15 EPSDT 16 Immunization 17 Immunization Admin 18 19 CRS/BabyNet 20 BabyNet 21 CRS Nurse $159,045 $25,215 $75,625 $32,500 $108,215 $52,635 $12,500 $45,265 $12,452 $27,857 $12,365 $37,895 $19,870 $3,895 $204,310 $37,667 $103,482 $44,865 $146,110 $72,505 $16,395 0.96244 0.96244 0.96244 0.96244 0.96244 0.96244 0.96244 $196,636 $ $36,252 $ $99,595 $ $43,180 $ $140,622 $ $69,782 $ $15,779 $ (59,555) $ (10,980) $ (30,164) $ (13,078) $ (42,590) $ (21,135) $ (4,779) $ 27,250 24,654 9,562 45,215 16,871 24,556 26,584 $ $ $ $ $ $ $ 164,331 49,927 78,993 75,317 114,903 73,203 37,584 $69,852 $35,684 $35,642 $12,785 $105,494 $48,469 0.96267 0.96267 $101,556 $ $46,660 $ (30,758) $ (14,132) $ 3,654 $ 6,549 $ 74,452 39,077 22 $167,896 $27,985 $36,256 $58,759 $8,753 $9,856 $226,655 $36,738 $46,112 0.95365 0.95365 0.95365 $216,150 $ $35,035 $ $43,975$ (65,465) $ (10,611) $ (13,319) $ 85,478 $ 56,914 $ 55,142 $ 236,162 81,338 85,798 23 Miscellaneous 24 General Clinic 25 Lab 26 Lab CLIA 27 28 Preventive Health 29 HIV 30 STD 31 TB 32 $35,252 $37,595 $23,654 $1,453 $1,857) $1,654 $36,705 $39,452 $25,308 0.98523 0.98523 0.98523 $36,163 $ $38,869 $ $24,934 $ (10,953) $ (11,772) $ (7,552) $ 8,457 $ 6,594 $ 6,489 $ 33,667 33,691 23,871 r fir =+C31+B31 - B C $27,985 $8,753 $36,256 $9,856 D $36,738 $46,112 E 0.95365 0.95365 F $35,035 $ $43,975 $ G (10,611) $ (13,319) $ H 56,914 $ 55,142 $ I 81,338 85,798 D31 - X 25 Lab 26 Lab CLIA 27 28 Preventive Health 29 HIV 30 STD 31 TB 32 33 Family Support 34 FSS Family Planning 35 FSS Child Health 36 FSS CRS 37 FSS Preventive Health 38 FSS STD/HIV 39 FSS TB $35,252 $37,595 $23,654 $1,453 $1,857 $1,654 $36,705 $39,452 $25,308 0.98523 0.98523 0.98523 $36,163 $ $38,869 $ $24,934 $ (10,953) $ (11,772) $ (7,552) $ 8,457 $ 6,594 $ 6,489 $ 33,667 33,691 23,871 $53,674 $27,845 $26,985 $3,465 $6,789 $5,481 $1,847 $1,958 $1,654 $1,543 $2,854 $987 $55,521 $29,803 $28,639 $5,008 $9,643 $6,468 0.8325 0.8325 0.8325 0.8325 0.8325 0.8325 $46,221 $ $24,811 $ $23,842 $ $4,169 $ $8,028 $ $5,385 $ (13,999) $ (7,515) $ (7,221) $ (1,263) $ (2,431) $ (1,631) $ 18,749$ 6,459 $ 5,564 $ 4,875 $ 2,365 $ 2,465 $ 50,971 23,755 22,185 7,781 7,961 6,219 40 41 Totals 42 $1,480,333 $546,427 $2,026,760 $1,941,660 $ (449,304) $ 1,027,585 $ 2,519,940 43 Projected Revenue Carry Forward Cash Balance Cash Transfers Available to Budget $ 2,519,940 $ (453,169) $ 65,234 $ 2,132,005 Current Budget Projected Surplus (Deficit) $ 3,265,425 $ (1,133,420) J14 x A fx Expense Budget 1-Jan-14 $ $ $ $ $ $ Budget YTD Expenses Current Balance 10,227,451 $ 4,909,176 $ 5,318,275 1,425,896 $ 706,654 $ 719,242 3,265 $ 3,452 $ (187) 3,529,446 $ 1,698,134 $ 1,831,311 664,427 $ 320,299 $ 344,128 15,850,484 $ 7,637,716 $ 8,212,769 4 Personnel 5 Classified FTE's 6 Temporary Staff 7 Overtime 8 Fringe Benefits 9 Indirect Cost 10 Subtotal Personnel 11 12 Operating 13 Travel 14 Contractual 15 Supplies 16 Fixed Charges 17 Case Services 18 Equipment 19 Other 20 Subtotal Operating 21 22 Total Expense Budget 23 110,042 224,115 119,402 $ $ $ $ $ $ $ $ 235,694 $ 248,965 $ 475,987 $ 32,654 $ 569,878 $ 135,987 $ 24,856 $ 1,724,021 $ 125,652 $ 24,850 $ 356,585 $ 32,654 $ 165,485 $ 125,326 $ 12,654 $ 843,206 $ 404,393 10,661 12,202 880,815 $ 17,574,505 $ 8,480,922 $ 9,093,584 24
