This assignment is designed to help you build an annual operating budget, and to
ID: 2795121 • Letter: T
Question
This assignment is designed to help you build an annual operating budget, and to practice and/or improve your Excel skills. When totaling rows and columns in your budget, be sure to allow Excel to do the math for you (use formulas or the Auto Sum feature); do not use a calculator to obtain the amount and then type the number into the budget. Please note that this assignment does not pertain to the health care business that you are creating in this class.
Develop a budget for a small department within a hospital or other organization that delivers health care, such as a rehabilitation facility, nursing home or home health care agency. Select ONE department or division within that organization for which you can develop a 12-month operating budget. For example, you might prepare a budget for the Ultrasound Division of the Radiology Department; it is not necessary to prepare a budget for the entire department, only Ultrasound.
Download the Department Budget Template Excel file. Six revenue categories have been inserted along with the percentage that each payer represents of the total revenue. You may NOT change any of the revenue sources. Eleven (11) operating expense categories have been inserted for you. You MAY add or delete expense categories as you see fit.
Prepare a 12-month annual budget by doing the following:
Insert the months of July through December for the budget.
Assume that the annual incoming revenue for this department is $500,000. Allocate the $500,000 across the 12 months as you like; this can be consistent for each month or some months could have more revenue coming in and some can have less revenue, but the total annual must be $500,000.
Insert amounts of money for all operating expense items that you choose to have in the budget for all 12 months.
Total vertically each month of revenue.
Total vertically each month of expenses.
Total each line item horizontally to obtain the total annual amount.
Insert any comments (assumptions) that you wish. For example, for “Salaries”, you may wish to add a comment that there is one physician, one nurse, one technician and one receptionist in the department.
Be sure to budget to incoming revenue. In other words, total expenses for all categories for all 12 months must NOT exceed $500,000 or you would be “over budget”.
Financial Manager Interview Assignment
On this page:
Prepare a short write-up regarding your final budget. What categories did you find the most challenging to budget for?
Assume that the Chief Financial Officer has informed you that next year, there will only be $450,000 of incoming revenue. Discuss how you will handle this. What operating expense categories could be reduced in total cost or could be eliminated altogether?
Department Name January February March April May June Total Annual Comments Revenue: Medicare (40%) Medicaid (15%) HMO Revenue (20%) PPO Revenue (15%) TRICARE (5%) Cash (5%) Total Revenue Operating Expenses: Salaries Contract Labor Fringe Benefits Employee Training Dues and Subscriptions Supplies and Pharmaceuticals Depreciation Expense Housekeeping Expense Malpractice Expense Utilities Expense Miscellaneous Operating Expenses Total ExpensesExplanation / Answer
January February March April May June July August September October November December Total Annual Comments Revenue: 39647 41638 40263 44362 40263 47863 42687 44263 40103 41527 38742 38642 500000 based on demand Medicare (40%) 15,859 16,655 16,105 17,745 16,105 19,145 17,075 17,705 16,041 16,611 15,497 15,457 200000 Medicaid (15%) 5,947 6,246 6,039 6,654 6,039 7,179 6,403 6,639 6,015 6,229 5,811 5,796 75000 HMO Revenue (20%) 7,929 8,328 8,053 8,872 8,053 9,573 8,537 8,853 8,021 8,305 7,748 7,728 100000 PPO Revenue (15%) 5,947 6,246 6,039 6,654 6,039 7,179 6,403 6,639 6,015 6,229 5,811 5,796 75000 TRICARE (5%) 1,982 2,082 2,013 2,218 2,013 2,393 2,134 2,213 2,005 2,076 1,937 1,932 25000 Cash (5%) 1,982 2,082 2,013 2,218 2,013 2,393 2,134 2,213 2,005 2,076 1,937 1,932 25000 Total Revenue 39647 41638 40263 44362 40263 47863 42687 44263 40103 41527 38742 38642 500000 Operating Expenses: 27752.9 29146.6 28184.1 31053.4 28184.1 33504.1 29880.9 30984.1 28072.1 29068.9 27119.4 27049.4 350000 Salaries 9150 9150 9150 9150 9150 9150 9150 9150 9150 9150 9150 9150 109800 salaries fixed Contract Labor 3330.348 3497.592 3382.092 3726.408 3382.092 4020.492 3585.708 3718.092 3368.652 3488.268 3254.328 3245.928 42000 labour variable Fringe Benefits 1942.703 2040.262 1972.887 2173.738 1972.887 2345.287 2091.663 2168.887 1965.047 2034.823 1898.358 1893.458 24500 Employee Training 2220.232 2331.728 2254.728 2484.272 2254.728 2680.328 2390.472 2478.728 2245.768 2325.512 2169.552 2163.952 28000 Dues and Subscriptions 1665.174 1748.796 1691.046 1863.204 1691.046 2010.246 1792.854 1859.046 1684.326 1744.134 1627.164 1622.964 21000 Supplies and Pharmaceuticals 4162.935 4371.99 4227.615 4658.01 4227.615 5025.615 4482.135 4647.615 4210.815 4360.335 4067.91 4057.41 52500 Depreciation Expense 1560 1560 1560 1560 1560 1560 1560 1560 1560 1560 1560 1560 18720 deprecaition exp fixed Housekeeping Expense 1387.645 1457.33 1409.205 1552.67 1409.205 1675.205 1494.045 1549.205 1403.605 1453.445 1355.97 1352.47 17500 variable cost Malpractice Expense 1110.116 1165.864 1127.364 1242.136 1127.364 1340.164 1195.236 1239.364 1122.884 1162.756 1084.776 1081.976 14000 Utilities Expense 1110.116 1165.864 1127.364 1242.136 1127.364 1340.164 1195.236 1239.364 1122.884 1162.756 1084.776 1081.976 14000 Miscellaneous Operating Expenses 2206.631 1138.074 1970.899 4452.126 3247.699 2356.599 587.951 1373.799 5626.019 3985.471 5158.566 3589.866 35693.7 general expenses Total Expenses 29845.9 29627.5 29873.2 34104.7 31150 33504.1 29525.3 30984.1 33460 32427.5 32411.4 30800 377713.7
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.