BUDGET DEVELOPMENT AND ANALYSIS
PROJECT
The budget narrative should be seven pages.
Be sure to give me excel worksheets.
The Assignment
You are the Budget Director for Alamo Hospice a mid-size hospice organization. You will develop a comprehensive budget and create the associated performance story for Next Year that describes the results the organization is planning to produce. Your budget should support your organizational goals and objectives. You will develop schedules that will enable you to present the budget to the Board of Directors for Approval. You will be given a set of data for the estimated Current Year (an extrapolated annualized amount), as well as historical data from 1 Year Ago (Actual data) and 2 Years Ago (Actual Data). You will develop the budgeted financial and statistical information for the Next Year that utilizes the historical information as a basis. The accompanying Excel spreadsheet will provide you a template for development of the key information. You will be asked to develop and defend assumptions related to the development of the budget.
PLEASE INCLUDE THE EXCEL WORKSHEET WHEN YOU TURN IN YOUR PROJECT.
Using this document as a guide, begin filling out the assumptions section of the Excel spreadsheet. You will be asked to create some of your own assumptions regarding volumes and rates of increase in revenues and expenses. Don’t forget, budgeting is an iterative process, put your best number in and move on, you can always come back and adjust it.
a.Most budget processes start with making estimates of volume. Read the narrative in this document about volume and enter your estimates into the assumptions tab on the Excel spreadsheet. Your volume numbers will drive your financial performance.
b.Read the narrative in this document about Revenue and enter your estimates in the assumptions tab on the Excel spreadsheet. You will be asked to estimate some payment rates. Make your estimates based on historical trends and be prepared to “defend” your estimate in your paper.
c.Read the narrative in this document about Expenses and enter your estimates in the assumptions tab on the Excel spreadsheet. You will be asked to estimate some rates of increase in labor and benefit costs.
d.Read the narrative in this document about Capital costs and enter your estimates in the assumptions tab on the Excel worksheet. You will be asked to estimate the interest rate on a loan.
e.Evaluate the results on your income statement against your objectives. Make adjustments to your assumptions that are necessary to produce the income statement that meets your Board’s objectives. Discuss these results and your assumptions in your paper.
2.Draft your narrative that demonstrates to the Board, how your budget was constructed, the significant assumptions, and performance management is prepared to deliver.
3.Print the schedules you want to include as support for your narrative (you can reference these in your narrative if you wish).
4.Finalize your presentation and make sure your budget meets the objectives outlined by the Board.
Alamo Hospice
Alamo Hospice is the premier Hospice program in the state. It is 2 times larger than the next largest hospice. There are five more hospice providers in its service area. Alamo is a 501(c)3 not for profit, governed by a volunteer Board of Directors. The Directors are comprised of representatives from the community, few of which have financial backgrounds.
The Board has established 4 key goals for the organization for Next Year:
1. Provide State of the Art service to patients
2. Achieve improvements in productivity of at least 2%
3. Produce at least a 5% Net Margin from Operations
4. Grow the number of patients admitted by 6%
Alamo Operates with two care teams. One team is the Home Care (HC) Team. This team provides services to those patients who reside at home. The other team is the Long Term Care (LTC) Team. The LTC team provides services to patients who reside in Nursing Homes or Assisted Living Facilities. The administrative staff consists of management, billing, accounting, clerical and medical records functions.
Alamo has a sophisticated “Bottom-Up” budget process that creates a unified and comprehensive set of financial targets for the organization. Each manager is accountable to deliver their budget and has a significant voice in the development of the overall organizational goals and strategies. For the purposes of this presentation, we will assume that the product you deliver was developed in conjunction with the key management and other personnel of Alamo.
1. Volume
Two years ago, Alamo Hospice admitted 720 patients. Each patient was enrolled in Hospice on average for approximately 67 days (Average Length-of-Stay or ALOS), with Alamo providing approximately 48,000 patient days of service. The average daily census has been growing steadily for the last few years with an emphasis on patients residing in Nursing Homes and Assisted Living Facilities. Key Volume Statistics are as follows:
ADMISSIONS 2 Years Ago 1 Year Ago Current Year
Total 720 750 790
Home Care % 60.0% 57.0% 56.0%
Long Term Care % 40.0% 43.0% 44.0%
AVERAGE LENGTH-OF-STAY 2 Years Ago 1 Year Ago Current Year
Home Care 66.0 67.0 68.0
Long Term Care 68.0 70.0 72.0
PATIENT DAYS 2 Years Ago 1 Year Ago Current Year
Home Care 28,512 28,676 30,056
Long Term Care 19,584 22,540 25,056
Total 48,096 51,216 55,112
AVERAGE DAILY CENSUS 2 Years Ago 1 Year Ago Current Year
Home Care 78.1 78.6 82.3
Long Term Care 53.7 61.8 68.6
Total 131.8 140.3 151.0
2. Revenue
a) Hospice Gross Revenue: Hospice revenue is derived primarily from Medicare. All pay types pay Alamo on a “Per-Diem” basis or per patient day. Alamo tracks its revenue by Pay Source. Management does not expect the mix of Payers to change Next Year. The mix of Admissions by Pay Source is as follows:
ADMITS BY PAY SOURCE 2 Years Ago 1 Year Ago Current Year
Medicare 80.0% 80.0% 80.0%
Medicaid 3.0% 3.0% 3.0%
Managed Care 7.0% 7.0% 7.0%
Commercial 7.0% 7.0% 7.0%
Charity 1.0% 1.0% 1.0%
Self Pay 2.0% 2.0% 2.0%
Total 100.0% 100.0% 100.0%
“Charges” represent the Gross Charge used to compute “Gross Revenue” by Alamo for 1 day of service at the designated Level of Care. Management expects charges to increase Next Year.
CHARGES BY LEVEL OF CARE 2 Years Ago 1 Year Ago Current Year
Routine $ 175.00 $ 178.00 $ 180.00
Inpatient $ 760.00 $ 762.00 $ 764.00
Respite $ 182.00 $ 185.00 $ 187.00
Continuous $ 980.00 $ 985.00 $ 990.00
b) Hospice Payment: Medicare and Medicaid compute the amount paid by using the National Rate for each level of care and applying a wage index for the location of the Hospice to the portion of the rate designated as the Labor Portion. The non-labor portion is then added back to the wage-adjusted labor portion to compute the total payment. For the purposes of this project, we will assume that the Medicare & Medicaid payment rates are the same.
NATIONAL RATES 2 Years Ago 1 Year Ago Current Year Next Year
Routine $ 137.25 $ 140.05 $ 142.91 $ 145.77
Inpatient $ 610.57 $ 623.03 $ 635.74 $ 648.45
Respite $ 141.97 $ 144.87 $ 147.83 $ 150.79
Continuous $ 801.07 $ 817.42 $ 834.10 $ 850.78
LABOR PORTION 2 Years Ago 1 Year Ago Current Year Next Year
Routine 68.71% 68.71% 68.71% 68.71%
Inpatient 64.01% 64.01% 64.01% 64.01%
Respite 54.13% 54.13% 54.13% 54.13%
Continuous 68.71% 68.71% 68.71% 68.71%
The Wage Index for the previous two years was 1.1358. The current year and next year’s wage index is 1.1429.
NET PAYMENTS BY PAY SOURCE AND LEVEL OF CARE
MANAGED CARE 2 Years Ago 1 Year Ago Current Year
Routine $ 135.00 $ 138.00 $ 141.00
Inpatient $ 650.00 $ 663.00 $ 676.00
Respite $ 132.00 $ 135.00 $ 138.00
Continuous $ 800.00 $ 816.00 $ 832.00
COMMERCIAL
Routine $ 137.00 $ 140.00 $ 143.00
Inpatient $ 655.00 $ 668.00 $ 681.00
Respite $ 140.00 $ 143.00 $ 146.00
Continuous $ 820.00 $ 836.00 $ 853.00
The contracts for Managed Care payers and Commercial Payers include increases of 2.0% and 2.5% respectively Next Year.
Alamo sees patients without regard to their ability to pay. It tracks and enrolls those patients that meet its Charity Care guidelines as “Charity Care”. No payment is expected for Charity Care patients. Patients without insurance are responsible for payment themselves. These patients are referred to as being “Self Pay” patients. The nature of these patients financial condition results in low collection rates. Self Pay collections have averaged 8% of the charges. For the purposes of this project, amounts not collected from Self Pay patients will be considered a “Deduction from Revenue” instead of “Bad Debt.” Bad Debts are generally recognized as Expense on the Income Statement.
The difference between the Gross Charges billed by Hospice and the Net Payment expected to be collected is shown on the Income Statement as Deductions from Revenue.
c) Nursing Home Pass Thru – When a Hospice patient who resides in a nursing home is entitled to both Medicaid and Medicare (called “Dually Eligible”), the nursing home no longer bills the state Medicaid program for that patient’s long-term care. Instead, the nursing home bills and receives payment from the hospice. The hospice then bills the Medicaid agency for the patient’s room and board. The state Medicaid agency must pay the hospice at least 95% of what Medicaid would have paid the Nursing Home. Alamo had 35% of its Long Term Care patient days dually eligible in one of three nursing homes. The percent of Long Term Care patient days that are dually eligible is as follows:
% of LTC Patients Dually Eligible 2 Years Ago 1 Year Ago Current Year
Percent of LTC Patient Days 35.0% 36.0% 38.0%
The distributions of dually eligible days are as follows:
Distribution of Patient Days by NH 2 Years Ago 1 Year Ago Current Year
Nursing Home 1 25.0% 28.0% 35.0%
Nursing Home 2 40.0% 41.0% 43.0%
Nursing Home 3 35.0% 31.0% 22.0%
Total 100.0% 100.0% 100.0%
The Costs per day billed by the nursing homes to Hospice are as follows:
Nursing Home Cost per Day 2 Years Ago 1 Year Ago Current Year
Nursing Home 1 $ 170.83 $ 173.25 $ 176.10
Nursing Home 2 $ 130.30 $ 129.40 $ 135.20
Nursing Home 3 $ 155.75 $ 158.60 $ 163.25
The state Medicaid Agency pays the Hospice 95% of the billed Nursing Home Charges.
3. Expenses
a) Labor Hours – Clinical services at hospice are delivered primarily by four disciplines; Registered Nurses (RN), Medical Social Workers (MSW), Home Care Aides (HCA) and Pastoral Counselors. Productivity is expressed as the number of patients per Full Time Equivalent (FTE). Productivity for the Long Term Care team is higher than that of the Home Care Team because the patients are concentrated in facilities, thus reducing travel time between patients.
Number of Patients per FTE 2 Years Ago 1 Year Ago Current Year
Home Care
RN 9.8 9.7 9.9
MSW 21.5 21.6 21.7
HCA 5.0 5.0 5.0
Pastoral 50.5 50.6 51.0
Long Term Care
RN 11.5 11.6 12.0
MSW 25.0 25.1 25.2
HCA 6.0 6.0 6.0
Pastoral 55.0 55.0 55.0
Administrative
Management 42.0 42.0 42.5
Clinical Management 30.0 31.0 32.0
Clerical 4.2 4.2 4.3
One Full Time Equivalent (FTE) = 2,080 paid hours.
b) Labor Costs – Labor Costs are expressed as a rate of increase over the base hourly rate established 2 years ago. Management expects the rates to increase Next Year. The rates for each discipline are as follows:
Average Hourly Labor Rate 2 Years Ago 1 Year Ago Current Year
Home Care
RN $ 32.50 3.00% 2.50%
MSW $ 28.00 2.00% 1.75%
HCA $ 14.00 2.00% 1.75%
Pastoral $ 23.00 2.00% 1.75%
Long Term Care
RN $ 32.50 3.00% 2.50%
MSW $ 28.00 2.00% 1.75%
HCA $ 14.00 2.00% 1.75%
Pastoral $ 23.00 2.00% 1.75%
Administrative
Management $ 52.00 2.00% 2.00%
Clinical Management $ 40.00 2.00% 1.75%
Clerical $ 17.00 2.00% 1.75%
c) Benefit Costs – Benefits are expressed as a percentage of paid dollars. Management expects the cost of health insurance to increase Next Year. The Benefit costs percentages are as follows:
Benefits as % of Paid Salaries 2 Years Ago 1 Year Ago Current Year
FICA 7.65% 7.65% 7.65%
Health Insurance 8.50% 9.50% 9.75%
Worker’s Comp 0.20% 0.20% 0.20%
Pension 4.00% 4.50% 4.50%
d) Variable Costs – Variable costs are shown as “Cost per Patient Day” for the respective Care Teams (see Chapter 7 in the McLean text). Administrative Costs are shown as “Cost per Patient Day for all hospice patient days. Many of the variable costs are locked in by contract and some reflect changes in economic conditions. Management generally expects these costs to rise between 2% and 6%.
Variable Costs per Patient Day 2 Years Ago 1 Year Ago Current Year
Home Care
Contract Labor
Physical Therapy $ 1.10 $ 1.12 $ 1.14
Dietitian $ 0.20 $ 0.20 $ 0.21
Contract Services
Durable Medical Equipment $ 6.50 $ 6.50 $ 6.50
Pharmaceuticals $ 12.00 $ 12.24 $ 12.48
Inpatient Services $ 630.00 $ 643.00 $ 659.00
Respite Services $ 137.00 $ 140.00 $ 143.00
Supplies & Other
Medical Supplies $ 2.35 $ 2.40 $ 2.44
Office Supplies $ 1.20 $ 1.21 $ 1.22
Mileage $ 2.50 $ 2.50 $ 2.50
Long Term Care
Contract Labor
Physical Therapy $ 0.20 $ 0.20 $ 0.20
Dietitian $ – $ – $ –
Contract Services
Durable Medical Equipment $ 4.50 $ 4.50 $ 4.50
Pharmaceuticals $ 12.00 $ 12.00 $ 12.00
Inpatient Services $ 630.00 $ 643.00 $ 659.00
Respite Services $ 137.00 $ 140.00 $ 143.00
Supplies & Other
Medical Supplies $ 6.50 $ 6.53 $ 6.80
Office Supplies $ 1.20 $ 1.20 $ 1.20
Mileage $ 1.39 $ 1.45 $ 1.51
Administrative
Supplies & Other
Office Supplies $ 0.45 $ 0.46 $ 0.49
e) Fixed Costs – Fixed Costs are shown as the base year (2 Years Ago) and the annual increase incurred and anticipated each year.
Fixed Costs (Base Yr x % Inc) 2 Years Ago 1 Year Ago Current Year Next Year
Occupancy
Rent $ 360,000 2.50% 2.50% 2.50%
Utilities $ 21,600 2.50% 2.50% 2.50%
Telephone $ 144,000 2.00% 2.00% 2.00%
Professional Services
Consulting $ 20,000 3.00% 3.00% 3.00%
Medical Director $ 36,000 3.50% 3.50% 3.50%
Other
Meetings & Travel $ 40,000 2.00% 1.00% 2.00%
Dues and Fees $ 1,500 2.00% 2.00% 2.00%
License & Accreditation $ 4,000 2.00% 2.00% 2.00%
Postage & Mail $ 3,000 2.00% 2.00% 2.00%
Education & Training $ 12,000 3.00% 2.00% 2.00%
4. Capital
a) New Equipment Purchases – Alamo depreciates items whose cost is $1,000 or more. Capital purchases (including the computer system planned to be acquired Next Year) are as follows:
Capital Acquisitions 2 Years Ago 1 Year Ago Current Year Next Year
Beginning Balance $ 168,000 $ 168,000 $ 170,000 $ 172,700
Add: New Equipment – 2,000 2,700 148,350
Total $ 168,000 $ 170,000 $ 172,700 $ 321,050
b) Depreciation on Current Equipment – Based on equipment purchased above, depreciation is scheduled as follows (NOTE: Next Year Depreciation below does NOT include Depreciation related to the purchase of the computer system):
Equipment Depreciation 2 Years Ago 1 Year Ago Current Year Next Year
Equipment – Existing
Fixed Equipment $ 12,000 $ 11,500 $ 11,000 $ 10,500
Moveable Equipment 21,600 22,140 22,694 23,261
Total Equipment $ 33,600 $ 33,640 $ 33,694 $ 33,761
c) Major Capital Acquisition – A new computer system will be acquired, installed and up and running by the first of the year. The computer system is expected to improve the quality, service and productivity of the hospice. The acquisition costs will be $145,000 and the useful life is expected to be 5 years. The purchase will be financed by a loan from a local bank for the full value of the acquisition at a rate of 5.5% over a 5 year period. Alamo will pay monthly payments of principle and interest over the 5 year period. This is the first debt for Alamo.
5. Balance Sheet
a) Ending Balances – Balances reflect use of Key Financial Ratios and Income Statement for the respective periods.
Ending Balances 2 Years Ago 1 Year Ago Current Year
Cash $1,821,612 $1,799,712 $2,114,520
Accounts Receivable $1,348,406 $1,481,367 $1,652,092
Equipment $ 168,000 $ 170,000 $ 172,700
Accumulated Depreciation $ 83,600 $ 117,600 $ 151,740
Accounts Payable $ 584,800 $ 678,912 $ 782,568
Net Assets $2,376,905 $2,642,902 $2,993,339
b) Key Financial Ratios – Ratios are developed using information shown on Income Statement for the respective periods.
Financial Ratios 2 Years Ago 1 Year Ago Current Year Next Year
Days Cash on Hand 78.0 69.0 73.0 81.0
A/R Days (Net) 55.0 55.0 55.0
Days in A/P 25.0 26.0 27.0
6.Assignment Checklist
In developing your presentation to the Board consider the following:
1.Identify your key assumptions in developing the budget.
a.Identify the changes from year to year. What is driving the change (governmental policy, inflation, local market, etc).
2.Develop a narrative that describes the performance of the organization.
a.Include a 5 – 7 page double-spaced narrative that “explains” the budget and the results it produces to the Board of Directors. Write the document from the perspective of Management to the Board.
3.Create the appropriate schedules that detail the components of the budget. Be sure to include any significant variances you feel are appropriate and their cause. The suggested presentation components include:
a)Budget Narrative (5 – 7 pages double spaced)
b)Income Statement
c)Schedule of Volume
d)Schedule of Revenue
e)Schedule of Labor
f)Schedule of Expense
g)Schedule of Capital
4.Introduce any outside factors (market pressures, nursing shortage, recession, etc.) into your analysis as you see fit.
5.Ensure that your budget supports the organizations objectives and state that it does so in your narrative.
7.Grading
Your grade will be based on the following criteria:
1.Clarity and appearance of presentation and narrative – Is the presentation understandable and professional in its appearance?
2.Accuracy – are the computations correct based on the assumptions you used?
3.Completeness of Schedules – Are the appropriate schedules included that detail the key information needed by the Board?
4.Achievement of organizational goals – Does the budget presented achieve the goals established by the organization?
8.Excel Tips & Techniques
Before Printing, use print preview to make sure you are printing what you want to print. Print ranges and headers have been pre-set but may vary based on the printer being used.
Check the column widths. You sometime may see ### in the columns instead of numbers. Increase the column width to remove the hash marks.
Cells in Blue indicate user input. Cells in black indicate a formula should be present.
This spreadsheet was meant to be used interactively, that is, it was designed so that one could make changes in the assumptions and see the impact on each of the statements and schedules. When creating your formulas, reference the cells on the “Assumptions” tab.
Remember, you are telling a story to your Board about Management’s plans for the next year. Don’t be afraid to be creative and develop a great story! One final thought, PLEASE INCLUDE THE EXCEL WORKSHEET WHEN YOU TURN IN YOUR PROJECT! Whether you use the schedules or not, we need to see the underlying financial statements to determine your grade.
