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
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.