You are the manager of an Oncology Unit. It is now September 2017 and you are finalizing your budget proposal for FY 2018. Finance and Senior management have approved the volume projections
business
Description
| A# 6: Costs and Budget Quiz: Questions 11 & 12 | ||||||||||||||||
| Budget Work Sheet | ||||||||||||||||
| You are the manager of an Oncology Unit. It is now September 2017 and you are finalizing your budget proposal for FY 2018. Finance and Senior management have approved the volume projections. You now need to complete the operating expense budget using the data and assumptions provided. USE EXCEL FORMULAS IN EACH CELL TO SHOW YOUR CALCULATIONS. The spreadsheet formulas are worth a total of 1 point (0.25 each) for formulas for calculating a) productive hours, b) paid hours, c) inflation impact, and d) summing rows when needed. The spreadsheet has been formatted to round to whole numbers ( i.e., answers need no decimals) except for your FTE line. Carry that calculation out to 1 decimal point. USE Weekdays in month numbers provided on Row 37 to calculate Monthly FTE Hours. | ||||||||||||||||
| Labor Targets : | Non Labor Assumptions: | Inflation for FY 2018 is projected to be 3% | ||||||||||||||
| HPPD = 12 | ||||||||||||||||
| Non Productive Time = 12% | Non labor cost per unit for 2017 are projected to end the year as follows: | |||||||||||||||
| 103.106 | Patient Supplies | 24.35 | ||||||||||||||
| Salary Data | 103.107 | Medical supplies | 4.14 | |||||||||||||
| Fixed | Nurse Manager | $42.04 / hr. | Merit increase of 4% will occur 1 April | 103.108 | Linen | 9.33 | ||||||||||
| All Variable | Average variable rate | $27.09/hr. | Merit increase of 3% and will occur in July for non-management staff | 103.109 | Pharmacy | 4.66 | ||||||||||
| 2018 Budget ONCOLOGY | ||||||||||||||||
| Dept: 007812 | ||||||||||||||||
| Workload Units | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sept | Oct | Nov | Dec | Total | |||
| Patient Days | 619 | 633 | 574 | 553 | 536 | 594 | 555 | 535 | 614 | 563 | 567 | 537 | 6880 | |||
| Labor Expenses | ||||||||||||||||
| 102.100 | Fixed Pay (Manager) | |||||||||||||||
| 102.110 | Variable Pay | |||||||||||||||
| Total Labor Expenses | ||||||||||||||||
| Non-Labor Expenses | ||||||||||||||||
| 103.106 | Patient Supplies | |||||||||||||||
| 103.107 | Medical supplies | |||||||||||||||
| 103.108 | Linen | |||||||||||||||
| 103.109 | Pharmacy | |||||||||||||||
| Total Non Labor Expenses | ||||||||||||||||
| TOTAL EXPENSES | ||||||||||||||||
| FTE Budget | Productive Hours | |||||||||||||||
| Paid Hours | ||||||||||||||||
| (Carry this result out to 1 decimal point) | Total FTEs | Hint: P36 Annualized (P35/P38 ) | ||||||||||||||
| Weekdays in Month | 23 | 20 | 22 | 21 | 20 | 21 | 22 | 23 | 20 | 23 | 22 | 21 | ||||
| Calculate | Monthly FTE Hours (Hint: Weekdays in month * 8 hours) | 2080 | ||||||||||||||
