Financial excel

Need help with the following financial excel

sheet. Can you provide steps to follow please

 

  Outpatient Facility Projected Finance  

  Surgical Center  

   

Surgi Center Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10

Sq.Footage 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000

Rent Per Sq. Foot 25 26 28 29 30 32 34 35 37 39

Days in Operation 250 250 250 250 250 250 250 250 250 250

# of OR Rooms 5 5 5 6 6 6 7 7 7 7

Procedure Per Day 20 20 20 24 24 24 28 28 28 28

Annual Procedures 5,000 5,000 5,000 6,000 6,000 6,000 8,000 8,000 8,000 8,000

Cannibalized Procedures % 65.00% 65.00% 65.00% 55.00% 55.00% 55.00% 45.00% 45.00% 45.00% 45.00%

New Procedures % 35.00% 35.00% 35.00% 45.00% 45.00% 45.00% 55.00% 55.00% 55.00% 55.00%

Revenue Per Procedure $1,900.00 $1,957.00 $2,015.71 $2,076.18 $2,138.47 $2,202.62 $2,268.70 $2,336.76 $2,406.86 $2,479.07

   

FTE’s 20 20 20 22 22 22 25 25 25 25

Annual Hours 41,700 41,700 41,700 45,870 45,870 45,870 52,125 52,125 52,125 52,125

Average Hourly Rate $30.00 $30.60 $31.21 $31.84 $32.47 $33.12 $33.78 $34.46 $35.15 $35.85

Capital $10,000, 000 nequipment 10 year life  

VariableCost Per Procedure $200 $210 $221 $232 $243 $255 $268 $281 $295 $310

Fixed Costs $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000

Tax Rate 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00%

   

Annual Revenue Need help with all cells starting here to the end  

  Total from the data above  

  New $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

  Cannibalized $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

Expenses  

Rent  

Salaries  

Benefits 22% of Salaries  

Depreciation  

Variable Costs  

Fixed Costs  

General Expenses  

Total Expenses $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

Total Profit Loss from Unit Total Before Share with Partners and Taxes $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

Combined Federal and StateTaxes $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

Profit after Taxes $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

Loss Revenue from Main Campus $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Reduced Expenses Main Campus $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Net Effect Main Campus $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

Total Effect of Surgi Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

 

 

  Outpatient Facility Projected Finance  

  Radiology Center  

   

Surgi Center Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10

Sq.Footage 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000

Rent Per Sq. Foot 25 26 28 29 30 32 34 35 37 39

Days in Operation 250 250 250 250 250 250 250 250 250 250

   

Procedure Per Day 45 50 55 60 70 75 80 85 85 90

Annual Procedures 11250 12500 13750 15000 17500 18750 20000 21250 21250 22500

Cannibalized Procedures % 65.00% 65.00% 65.00% 55.00% 55.00% 55.00% 45.00% 45.00% 45.00% 45.00%

New Procedures % 35.00% 35.00% 35.00% 45.00% 45.00% 45.00% 55.00% 55.00% 55.00% 55.00%

Revenue Per Procedure $550.00 $566.50 $583.50 $601.00 $619.03 $637.60 $656.73 $676.43 $696.72 $717.63

   

FTE’s 25 25 25 26 26 26 26 26 26 26

Annual Hours 52125 52125 52125 54210 54210 54210 54210 54210 54210 54210

Average Hourly Rate $30.00 $30.60 $31.21 $31.84 $32.47 $33.12 $33.78 $34.46 $35.15 $35.85

Capital $12,500,000 nequipment 10 year life  

VariableCost Per Procedure $175.00 $183.75 $192.94 $202.58 $212.71 $223.35 $234.52 $246.24 $258.55 $271.48

Fixed Costs $750,000 $750,000 $750,000 $750,000 $750,000 $750,000 $750,000 $750,000 $750,000 $750,000

Tax Rate 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00%

   

Annual Revenue Need help with all cells starting from here  

  Total from the data above  

  New $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

  Cannibalized $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Expenses  

Rent  

Salaries  

Benefits 22% of Salaries  

Depreciation  

Variable Costs $100 per procedure  

Fixed Costs  

General Expenses  

Total Expenses $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Total Profit Loss from Unit Total Before Share with Partners $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Combined Federal and StateTaxes $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Profit after Taxes $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Loss Revenue from Main Campus $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Reduced Expenses Main Campus $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Net Effect Main Campus $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Total Effect of Radiology Center $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

  Outpatient Facility Projected Finance  

  Surgical Center  

   

Surgi Center Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10

Sq.Footage 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000

Rent Per Sq. Foot 25 26 28 29 30 32 34 35 37 39

Days in Operation 250 250 250 250 250 250 250 250 250 250

# of Exams Rooms 10 10 10 10 10 10 10 10 10 10

Procedure Per Day 125 125 125 150 150 150 200 200 200 200

Annual Procedures 31250 31250 31250 37500 37500 37500 50000 50000 50000 50000

Cannibalized Procedures % 65.00% 65.00% 65.00% 55.00% 55.00% 55.00% 45.00% 45.00% 45.00% 45.00%

New Procedures % 35.00% 35.00% 35.00% 45.00% 45.00% 45.00% 55.00% 55.00% 55.00% 55.00%

Revenue Per Procedure $175.00 $180.25 $185.66 $191.23 $196.96 $202.87 $208.96 $215.23 $221.68 $228.34

   

FTE’s 30 30 30 32 32 32 35 35 35 35

Annual Hours 62550 62550 62550 66720 66720 66720 72975 72975 72975 72975

Average Hourly Rate $30.00 $30.60 $31.21 $31.84 $32.47 $33.12 $33.78 $34.46 $35.15 $35.85

Capital $5,000, 000 nequipment 10 year life  

VariableCost Per Procedure $55.00 $57.75 $60.64 $63.67 $66.85 $70.20 $73.71 $77.39 $81.26 $85.32

Fixed Costs $500,000.00 $500,000.00 $500,000.00 $500,000.00 $500,000.00 $500,000.00 $500,000.00 $500,000.00 $500,000.00 $500,000.00

Tax Rate 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00% 30.00%

   

Annual Revenue  

  Total  

  New need help with  all cells starting here $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

  Cannibalized from the data above $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Expenses  

Rent  

Salaries  

Benefits 22% of Salaries  

Depreciation  

Variable Costs  

Fixed Costs  

General Expenses  

Total Expenses $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Total Profit Loss from Unit Total Before Share with Partners $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Combined Federal and StateTaxes $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Profit after Taxes $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Loss Revenue from Main Campus $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Reduced Expenses Main Campus $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Net Effect Main Campus $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

   

Total Effect of Urgent Care Center $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

  $0.00

$0.00

 

 

 

$0.00  

Need  help with Summary

 

Excess Revenue over Expenses  

  Outpatient Facility Only Before Split with Partners  

  Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10

Surgical Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Radiology Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Urgent Care Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

    Total $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

   

   

Including Effect on Main Facility  

   

Surgical Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Radiology Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

Urgent Care Center $0 $0 $0 $0 $0 $0 $0 $0 $0 $0

    Total