It is January 2^nd and you are just finishing up your 3^rd month as the new Administrator of MedLab Medical Office, LLC. After spending your first few weeks getting settled and meeting with the providers and staff, you turned your attention to the practices financials. You had a very difficult time finding any budget or budget tracking information, which may be why the person who was in this position for the past two years was let go. Determined to put your financial skills to work, you scoured the office files and found a handwritten notebook titled Budget. In the notebook you found the following information for the current year:

• 24,000 patient visits per year
• Payer mix:
  • 58% Private insurance
  • 35% Medicare
  • 6% Medicaid
  • 1% Self-pay
• Average reimbursement per visit by payer:
  • $65 Private insurance
  • $53 Medicare
  • $32 Medicaid
  • $50 Self-pay
• Annual salaries:
  • Physicians: $380,000
  • Nurses: $145,000
  • Laboratory Technician: $75,000
  • Administrator: $80,000
  • Support Staff: $40,000
• Employee benefits rate: 26%
• Rent: $96,000/year
• Utilities: $12,000/year
• General overhead: $45,000/year
• Per patient costs:
  • Medical supplies: $7.00
  • Office supplies: $1.50

1. Using information you found in the notebook, develop a budget for the current year in an Excel file. Use formulas for calculations whenever possible. [10 points]

Since you got back from the holiday break, you have been analyzing actual data for last year. You found actual data for (now) last year:

• 17,886 patient visits per year
• Payer mix:
  • 56% Private insurance
  • 33.75% Medicare
  • 8.50% Medicaid
  • 1.75% Self-pay (after adjusting for patients who did not pay)
• Average reimbursement per visit by payer:
  • $63 Private insurance
  • $52 Medicare
  • $32 Medicaid
  • $24 Self-pay
• Annual salaries:
  • Physicians: $379,110
  • Nurses: $153,733
  • Laboratory Technician: $72,225
  • Administrator: $75,533
  • Support Staff: $40,000
• Employee benefits rate: 23.5%
• Rent: $102,683/year
• Utilities: $12,835/year
• General overhead: $45,000/year
• Per patient costs:
  • Medical supplies: $8.25
  • Office supplies: $2.25