Instruction for Assignment to be posted on Canvas. Word Document and
Excel to be attached.
Assignment 1: Operating Budget
In Assignment 1, you will be provided an excel spreadsheet
with 9 month’s of actual data for a free-standing Radiology
Facility. Your assignment is to create the next year’s budget in excel
based on the assumptions provided. You will be provided with volume,
reimbursement and expense assumptions. In addition, you will be provided
with a capital asset to incorporate the impact into your operating budget.
All of the instructions are in the attached Word file:
Assignment 1 – Budget
Instructions 2018.doc
The attached spreadsheet (Assignment 1 –
Elizabethtown Radiology 2018 Budget.xls)
has a tab for your final budget and several tabs for your work papers.
Your work papers are supporting documents you would need to provide for any
budget that show what key assumptions were used to develop the budget.
Submit Assignment 1, the completed excel spreadsheet, by the due date.
All cells shaded in light blue and red should be complete per the
instructions.
If you have no experience with Excel, this assignment will be
much easier if you take the Excel lessons identified in the Pre-Week 1 Module.
Creating formulas will be critical to your ability to complete this assignment.
In the excel spreadsheet, there are formulas that are protected
so you don’t accidently type over them. You will not be able to add or delete
rows or columns. If you do not use formulas to arrive at the numbers I won’t be
able to provide feedback for any incorrect numbers.
Assignment 1
Elizabethtown Radiology Operating Budget
Overview:
You have graduated from Elizabethtown College
and are immediately hired as the Administrative Director (AD) of a
free-standing Radiology facility. The
previous AD left in September of 2017 so the financial statements are
complete through the end of September, 2017 and no budget for 2018 was
completed. The physician-owner, Dr. Malcolm Practice, has tasked you with
developing the 2018 operating budget.
Dr. Practice has determined that there are specific initiatives he
would like implemented in 2018 and requests that you reflect these
initiatives in the 2018 budget including:
·
The
elimination of one FTE of contract labor and
·
The
purchase of a 3-D ultrasound system for $100,000 on July 1, 2018.
The fiscal year end for the facility is the 12
months ending December 31. Dr.
Practice is pretty sharp with numbers and has given you the following
assumptions to use when developing the 2018 budget. Please complete the 2018
budget (filling in all light blue
shaded cells) based on the assumptions
below. Red shaded cells on the Final
Budget Tab are figures that
will automatically populate when you complete the support schedules in the
other three tabs. Ensure that all cells in columns G and H of the Final Budget Tab are complete. If you
choose to copy some formulas into these columns, you can ignore the loss of
the blue shading as long as you are sure the formula is producing the correct
number or calculation. While your
current detail for 2017 shows each service line (X-ray, CT Scan, MRI and
Ultrasound) provided by the facility, your 2018 budget is for all service
lines combined.
Using
the Template
It is recommended that you follow the
instructions in the order they are presented. Also, to ensure the integrity
of your numbers, you will:
Not be able to type over
formulas
Not insert or delete rows or
columns
See cells with comments to help
you along; a red triangle in the corner of a cell indicates the cell has
a comment. Hover or click on the cell to see the comment.
Step 1: Calculate the year ending 2017
balances
To determine your base-line, the projected
ending numbers for 2017, annualize
the 9 months of actual results because you, as the new AD, do not have actual
data for the last three months of 2017.
Step 2: Calculate the 2018 budget
It is common practice to show supporting work
papers to support a completed budget.
For this reason, you have extra excel tabs for the volume of
procedures, the physician fee calculations, as well as the Revenue &
Deductions to support the 2018 numbers that belong in the Final Budget Tab. Figures calculated in your supporting worksheets
will populate the appropriate cells in your Final Budget Tab so
you must complete the blue shaded fields in the three blue tabs / work
papers.
Volume & Revenue Assumptions
1) Volume – In the Volumes Tab,
calculate budgeted volumes first since revenues will be driven by volume. In
row 9, annualize the volumes by service line in the blue shaded cells. In row
10, assume a 3% increase in all service lines except Ultrasound
Services. Due to the new equipment
noted above, a 3% increase is expected due to normal growth and an additional
2% growth is expected when the new 3-D Ultrasound is installed on July 1,
2018. Remember that anything initiated half way through the year will not
have any impact for the first six months; therefore only half the additional
growth should be added to the normal growth rate.
Calculate Steps 2 through 5 in Tab 2. Revenues
& Deductions
2) Gross Charges – Click on Tab 2. Revenues & Deductions.
You will see that your 2017 estimated ending revenues have been broken
out by payer class. In your 2018
revenue calculation, you should also see the impact of the budgeted volume
increase for 2018 that you keyed into the Volumes Tab.
Dr. Practice has asked you to budget a 4% charge
increase in January 1, 2018. (All service lines are expected to grow at the
same rate you already calculated and no payer mix change is expected in 2018).
3) Contractual allowances (C/A) would
typically increase by 4% due to the charge increase but in healthcare, charge
increases and reimbursement increases rarely coincide with each other. In this
case most of the facility’s payers have contracted rates that change on
January 1, 2018. Per the instructions in the Revenues & Deductions Tab,
you will need to determine how the C/A for each payer is impacted by the
combination of the facility’s charge increase versus the payer’s rate
increase. Follow the instructions by payer at the bottom of the Revenues & Deductions Tab.
4) Contractual allowances can also
increase due to budgeted volumes, however not many insurers utilize an
individual facility’s charges in their payment formulas. You conduct a three-month
look-back and calculate that cash collections are averaging only 35% of gross
charges because your 2017 C/A percentage is 65%. You question Dr. Practice on why charges
are so high. He responds that all of
the commercial and managed care payer contracts have a clause that states
they will pay the lower of the charge or the negotiated contract rate so he
keeps the charges high to ensure he is getting maximum reimbursement. (Due to
the high charge rates, the facility offers a high discount for self-pay
patients of 50% off charges to stay competitive).
You will complete your entire revenue budget by filling in the blue
shaded cells in the Volumes Tab and
the Revenues & Deductions Tab.
5) Bad Debt is averaging 2.9% of gross
charges in 2017 but Dr. Practice is expecting you to improve on this
percentage because of your training in revenue cycle management. He asks you to set a goal for 2018 of 2.1%
of gross charges for budget purposes. Enter the bad debt percentage as a
negative figure since they are reductions to revenues.
Expense Assumptions:
All of the expenses
can be calculated in the Final Budget Tab, except the Physician Fees. Please
use the Physician Fee Tab for this budget figure.
6) Salaries and Wages – All employees
receive their annual increase of 3% on July 1, 2018. Remember that anything
initiated half way through the year will not have any impact for the first
six months.
Since you are adding a new 3D Ultrasound
machine on July 1, 2018, you review the Worked Hours per Procedure for all
service lines. After comparing your average worked hours per MRI of 2.47 hours
per test to national standards, as well as some local facilities, you
determine that without sacrificing quality you can reduce this to 1.5 hours
per test. You discuss this with Dr. Practice and propose that the MRI techs
be cross-trained to perform the 3D Ultrasound procedures. You agree with Dr.
Practice that during the first six months of 2018, you will conduct site
visits to top-notch facilities to determine how they are completing MRI
procedures at 1.5 hours per test and you agree to bring these Best Practice
procedures back to Elizabethtown Radiology and implement them on July 1, 2018.
This allows your facility to add the new service without adding new labor
costs. This will bring down your overall Worked
Hours per Procedure to 1.43 for 2018.
7) Benefits – Employee benefits
averaged 27% of salaries in 2017 but you are expecting a significant increase
in the cost of health insurance. As a small employer you have little
negotiating power with local insurers. You calculate the premiums and when
added to your benefits package, the new percentage for 2018 averages 31% of
salaries for 2018.
8) Contract Labor – You and Dr.
Practice agree that the cost of using a full-time contractor at $40 per hour
for a Diagnostic X-Ray Tech is too high and you have calculated there are
enough work hours (see Contract FTEs under Key Indicators – Diagnostic X-Ray)
to hire a full-time Tech in Diagnostic X-Ray to replace the contractor. For the 2018 budget, you decide to
eliminate one FTE (or 2,080 hours) from your contract labor expense at $40
per hour. To ensure you budget for the change correctly, you add a new full-time
Radiology Tech at $14.75 per hour for 2,080 hours to your salary expense.
The new Tech is not entitled to the July 1
increase until he/she’s been employed for at least one year per company
policy.
9) Supplies – The facility records
medical supplies and office supplies in the same general ledger account so
you can’t easily differentiate between medical and other supplies. You recommend to Dr. Practice that these be
broken into two accounts for actual reporting in 2018 and promise to budget
them separately in 2019. Dr. Practice
says that’s a great idea and he’s glad he hired an Elizabethtown College
Graduate to run his business. Since
you know medical supplies will grow in usage by at least 3% over the prior
year, specifically radiology film, you decide to budget for an average of 5.4%
increase over 2017 to cover medical and office supplies expense combined.
10) Physician Fees – Elizabethtown
Radiology does not employ Radiologists.
Instead, the facility has a contract with a teleradiology service in
Florida who read the films remotely and documents the results in a shared
software system. The contract is a
three-year fixed rate contract that commits the facility to paying for the
films to be read for $25 per X-ray, $50 per CT Scan, $56 per MRI and $36 per
Ultrasound. Dr. Practice threatened to
exercise the 90-day cancellation clause in the contract because he’s losing
money on the diagnostic X-ray service (see the negative contribution margin
for this line of service). To keep his
business, the teleradiology company agreed to reduce the cost of reading Diagnostic
X-rays from $25 to $20 per test. Your
analysis shows that switching teleradiology services would have cost more
money than he’s saving because it would have required purchasing all new
software. Dr. Practice states that you
are correct, but the Florida company doesn’t know he was never planning to change
services; he was threatening to change services as a negotiating tactic. Dr.
Practice is a real shark. All other radiology fees remain the same for 2018.
In Tab 3. Physician Fees, enter
the 2018 rates by service line.
11) Purchased Services, Medical Director,
Repairs & Maintenance, Utilities and Other Operating Expenses -You
anticipate no significant changes in these expenses except for the
Medical Director so you budget a 4.6% increase over 2017 due to the budgeted
volume increase plus inflation and you ask Dr. Practice what to use for his
annual increase. The Medical Director, Dr. Practice, says he’s going to need
a 5% increase for 2018. Must be nice
to set your own salary.
12) Billing Fees – The facility
outsources all billing to a clearinghouse.
The clearinghouse provides the facility with software to enter all
claims data and then electronically bills all insurers on behalf of
Elizabethtown Radiology. The
clearinghouse charges 14.2% of net revenues in 2017 but notified you that
their fee is increasing to 14.5% in 2018.
13) Marketing – Dr. Practice doesn’t
believe his marketing company is generating any business. They help with name recognition for the
facility but they have no understanding that all of his patients come from
referrals and they have no way to impact these referrals. He asks you to cap
the 2018 budget at $10,000 while you and he plan a new strategy next year.
Marketing expenses are one of the common areas to quickly cut expenses when
necessary.
14) Property Taxes and Insurance – You
look at the General Ledger to find out how to split these two expenses and
find that by year-end, your estimated ending balance of $106,667 for 2017 is
accurate and consists of $28,000 in property tax and $78,667 in general
liability and malpractice insurance.
Based on Governor Wolf’s recent comments on raising taxes, you read up
on his latest legislative proposal and decide to budget a 5% increase in
property taxes. You then review the
past 3 years of historical increases in Insurance expense and determine that
the historical annual increase has been 3.3% per year. Therefore, you budget
a 5% increase for property taxes and a 3.3% increase for insurance in the 2018
budget. You immediately create a new GL account so you can track these
expenses separately in 2018.
15) Depreciation – You check the fixed
asset ledger and find that no assets will become fully depreciated in 2018
and you don’t expect to retire any assets that are still depreciating so your
annualized baseline for 2017 of $139,279 is a good starting point. The new 3-D Ultrasound machine you plan to
purchase will cost $92,000 and $8,000 for installation. The facility’s policy for depreciating
equipment is to follow GAAP and use 5-year straight-line depreciation. The new depreciation doesn’t impact the expense
until July 1, 2018, when the new 3D
equipment is put into operation. You
double-check your Accounting text book and read that straight-line
depreciation is calculated by taking the cost of the equipment plus any
related installation and delivery costs and spreading the cost evenly over
the life of the asset. Depreciation
does not begin until the asset is placed in service so the expense can be
properly matched against the revenues related to the new equipment. In
accounting, this is called the Matching Concept.
16) Rent – You check the lease and see
the lease doesn’t expire until 2021, but the landlord reserves the right to
raise the CAM (Common Area Maintenance) fees by 1.5% per year. To be
conservative, you budget the entire 1.5% increase over the 2017 estimated
ending expense.
Step 3: Review the Key Performance Indicators
(KPI)
A reasonable manager in a healthcare facility
knows that any budget or forecast can be inaccurate if the underlying assumptions
are not realistic. An ideal way to
ensure the reader understands the budget figures is to provide key indicators
and support schedules that provide the reader important assumptions and allow
the budget to be compared to the current year. In this case, the key indicators are in the
Final Budget Tab and some key assumptions are in the other excel tabs.
Besides, Dr. Practice is a smart guy but has no training in forecasting and
you want to bring that extra level of skill to the budget presentation by
providing key indicators he can use to understand the new budget.
You used the general ledger or trial balance
to break out the service lines for the 2017 figures and you used the
Facility’s Time and Attendance system to obtain the productive and
non-productive (vacation, sick and holiday) hours. You included labor hours in your KPI
because labor is typically the single highest controllable expense in a
healthcare organization. For expenses
that were considered administrative expenses and recorded in the
Administration cost center, you used the revenues generated by each service
line to allocate the overhead administrative expenses recorded in the
Administration cost center. For
example, when Dr. Practice asks how you spread his salary (Medical Director
Expense) to the lines of service, you tell him that it’s common practice to
use revenues as the basis for the allocation.
You show him the spreadsheet formulas and he sees that you took his
salary, then multiplied his salary times the percentage of revenue of each
service line to total revenues. For
example, revenues from CT Scans represent 31% of total revenues so you
allocated 31% of his salary to that service line. Dr. Practice tells you
“that’s brilliant!” You
don’t tell him there are more sophisticated ways of allocating cost but you
were pressed for time.
A) Review and ensure all KPI for 2017 and 2018
are complete and to ensure they are reasonable in comparison to the 2017 KPI
and that the changes you made are properly reflected in the KPI – this is an
excellent way to check your work. An
unusual change that can’t be explained could easily mean that something is
incorrect in your budget numbers. For
example, Average Salaries Including Contract Labor in Line 54 should have
decreased because you replaced one full-time contract laborer with a
full-time employee at a lower rate.
B) Once you complete your review and everything appears
reasonable, submit your completed spreadsheet by the due date.
