Please provide all formulas needed to recreate the problem

SMG Modeling Exercise Instructions The purpose of this exercise is to understand your fundamentals of modeling, accounting, and proper formatting. What we will be evaluating: Financial Modeling, Excel Formatting, Excel Formulas, detail provided in additional assumptions, thought process, attention to detail, accounting policy, and final conclusions. Your model should flow clearly and be easily legible to someone who is unfamiliar with with assumptions below. We should be able to easily follow your logic. 1- Recreate from a blank spreadsheet a 3 year Income Statement projection (monthly projections for year 1 and annual for years 2 and 3) 2- Use the assumptions below to create the projections for the physician practice 3- Make sure to clearly show any additional assumptions you make 4- Your model We've recruited an orthopedic surgeon to join the St. Anne's market. The physician, a joint surgeon, will start March 1st, Year 1 The joint surgeon will operate 3 days a week (4 surgeries per day) and will be in the office 2 days a week (sees 30 patients per day) Use the chart below to calculate gross patient service revenue (GPSR); bad debt is 3% of GPSR and contractual allowance is 55% of GPSR . wRVU / patient 8.0 GPSR / WRVU $160 Surgical Cases Office Visits 1.5 $80 Total volume grows 10% in year 2 and another 10% in year 3 The physician's salary will be $375K in year 1 and is flat for 3 years. MD fringe benefits are 15 % of salary. The physician will earn incentive bonus of $62 per WRVU over 8500 WRVUs annually. We will need to hire a medical secretary ($15 /hour) and a medical assistant ($20 /hr) to support the physician in his practice Staff salary growth is 2% every year and fringe benefits are 25% Rent of $70K and utilities of $10K per year are split with another physician. There is rent escalation of 2% in year 2 and another 2% in year 3 See below for break-out of other expenses Page 1 YoY% Change 0% 8% 0% Billing Fees 6.5% of Net Revenue Malpractice $30k/year Medical Supplies $2 / office WRVUS Purchased Services $1.5 / office WRVUS Other Expenses $.50 / office wRVUS Overhead 7% of total expenses 0% 0% 0% One time expenses in year 1 are $10K for marketing, a $20K sign-on bonus and $5k for software licenses Upfront capital costs will be $500K to fix the space and buy equipment 4- If the hospital margin is $9,000 on average for a joint surgery, should we or shouldn't we hire this physician? SMG Modeling Exercise Instructions The purpose of this exercise is to understand your fundamentals of modeling, accounting, and proper formatting. What we will be evaluating: Financial Modeling, Excel Formatting, Excel Formulas, detail provided in additional assumptions, thought process, attention to detail, accounting policy, and final conclusions. Your model should flow clearly and be easily legible to someone who is unfamiliar with with assumptions below. We should be able to easily follow your logic. 1- Recreate from a blank spreadsheet a 3 year Income Statement projection (monthly projections for year 1 and annual for years 2 and 3) 2- Use the assumptions below to create the projections for the physician practice 3- Make sure to clearly show any additional assumptions you make 4- Your model We've recruited an orthopedic surgeon to join the St. Anne's market. The physician, a joint surgeon, will start March 1st, Year 1 The joint surgeon will operate 3 days a week (4 surgeries per day) and will be in the office 2 days a week (sees 30 patients per day) Use the chart below to calculate gross patient service revenue (GPSR); bad debt is 3% of GPSR and contractual allowance is 55% of GPSR . wRVU / patient 8.0 GPSR / WRVU $160 Surgical Cases Office Visits 1.5 $80 Total volume grows 10% in year 2 and another 10% in year 3 The physician's salary will be $375K in year 1 and is flat for 3 years. MD fringe benefits are 15 % of salary. The physician will earn incentive bonus of $62 per WRVU over 8500 WRVUs annually. We will need to hire a medical secretary ($15 /hour) and a medical assistant ($20 /hr) to support the physician in his practice Staff salary growth is 2% every year and fringe benefits are 25% Rent of $70K and utilities of $10K per year are split with another physician. There is rent escalation of 2% in year 2 and another 2% in year 3 See below for break-out of other expenses Page 1 YoY% Change 0% 8% 0% Billing Fees 6.5% of Net Revenue Malpractice $30k/year Medical Supplies $2 / office WRVUS Purchased Services $1.5 / office WRVUS Other Expenses $.50 / office wRVUS Overhead 7% of total expenses 0% 0% 0% One time expenses in year 1 are $10K for marketing, a $20K sign-on bonus and $5k for software licenses Upfront capital costs will be $500K to fix the space and buy equipment 4- If the hospital margin is $9,000 on average for a joint surgery, should we or shouldn't we hire this physician