For the first year, the hospital anticipates serving 1,475 patients. The number of patients is expected to grow 7% each year. The average billing for each patient is $140,000, which will grow by 3.5% each year. However, because of managed care, the center collects only 25% of billings. Variable costs for supplies and drugs are calculated to be 10% of billings. Fixed costs for salaries, utilities, and so on, will amount to $22,000,000 in the first year and are expected to increase 4.5% each year. They want to calculate the net present value of profit over the next five years, assuming a discount rate of 8%.

1. Complete the model by populating the green cells with all the appropriate data values or formulas required for calculating the net present value. Be sure to use best practices for spreadsheet modeling.
2. Create a data table that evaluates the net present value by varying both:

• • the first-year volume from 1,400 to 1,600 patients in increments of 25 AND
  • the % of billings collected from 20% to 30% in increments of 1%.
• In your data table, the % of billings collected should serve as the row labels, and the first-year patient volume should serve as the column labels.

A B C D E F. G H Twin Peaks Surgery Center Data First year volume Billing per patient % of billings collected Variable costs First year fixed costs Discount rate Model Year 1 Year 2 Year 3 Year 4 Year 5 Annual growth Patients Billing growth Billing per patient Total billing Billings collected Variable costs Fixed cost growth Fixed costs Profit Net present value A B C D E F. G H Twin Peaks Surgery Center Data First year volume Billing per patient % of billings collected Variable costs First year fixed costs Discount rate Model Year 1 Year 2 Year 3 Year 4 Year 5 Annual growth Patients Billing growth Billing per patient Total billing Billings collected Variable costs Fixed cost growth Fixed costs Profit Net present value