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



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

Related Questions in business category