Healthcare Financial Analysis
Steps 1. Create a spreadsheet L that evaluates, and clearly illustrates, your analysis using the following inputs. ° 6 000 0 0 0 ° Projected revenue from the physicians Projected revenue from the physicians’ assistants Projected total revenue Projected cost of the physician payroll Projected cost of the physician assistant payroll Projected cost of the nursing payroll Projected cost of the Medical Assistants” payroll Given debt service of $400,000, operating costs of 40% of total payroll, the total projected expense of operating the clinic, including all payroll costs Projected profit/loss for this proposed clinic 2. Create a second spreadsheet that should include all of the above with your recommendations being inputted as appropriate. 3. Submit your spreadsheets by uploading them by the due date. Grading View the grading rubric. HPA 390W Excel Original Spreadsheet Final Spreadsheet With Recommendations ile Rubric Criteria 25t0>200 pts. 20t0>15.0 pts. Full Marks Partial Credit All 10 columns are correct. 25t0>200 pts. 20t0 >15.0 pts Full Marks Partial Credit 8 out of 10 columns are correct. 8 out of 10 columns are correct. 15 to >10.0 pts Partial Credit 6 out of 10 columns are correct. 15 to 10.0 pts Partial Credi 6 out of 10 columns are correct. 10t0>5.0pts Partial Credit 4 out of 10 columns are correct. 10t0 >5.0pts Partial Credit 4 out of 10 columns are correct. 5t0>00pts Partial Credit 2 out of 10 columns are correct. 51t0>00pts Partial Credit 2 out of 10 columns are correct. Pts opts No Marks 25pts opts No Marks 25pts Total Points: 50 Modified Business Plan Overview At the conclusion of this semester, you will submit both a written and financial analysis as well as a modified business plan presentation using the data from the provided scenario. 1. Financial Analysis - You will create a spreadsheet that evaluates and clearly illustrates your analysis using the identified inputs and a second spreadsheet that includes your recommendations. 2. Written Analysis - Compose your written modified business plan analysis using the provided components and formatting. 3. Presentation - In addition to the Written Analysis assignment and Financial Analysis Excel assignment, students will create a PowerPoint presentation to present to their internship preceptor Michele Lei. Scenario Details For this project, you will assume the role of an intern at Nittany Lion Health System (NLHS). Your preceptor, Michele Lei, has noted that you have excellent analytical critical thinking and Excel skills listed on your resume. To fully take advantage of your experience and ability, she has asked you to prepare a statement of projected revenues and expenses for a proposed project currently under consideration. Nittany Lion Health System (NLHS) NLMS, like many hospitals, owns a number of primary care physician practices. To take advantage of some possible economies of scale, NLHS is considering the consolidation of its outpatient practices and relocating them to a single building close to the hospital. Building The building under consideration is a 20,000-square-foot existing facility and will be more than adequate to accommodate the physician practices. At a rental cost of $20 per square foot, the total annual rent will be $400,000. (This rent payment is considered a “fixed cost”) Professional Staffing Physicians « There will be three (3) physicians located in the facility on a daily basis. © On average, each physician will see approximately 15 patients per day, with each patient generating an average of $110 per visit. « The base physician's salary is $185,000, with an additional 35% for taxes and benefits. + The physicians are exempt employees, and they all just renewed a 5-year employment contract. Physician Assistants « Additionally, there will be two (2) Physician Assistants (PAs) in the facility. o The PAs are averaging ten (10) patients per day, and according to CMS rules, they can only bill at 85% of the physician's fee. « The PAs salary is $80,000, with an additional 30% for taxes and benefits. « The PAs are exempt employees, and they all just renewed a 5-year employment contract. Additional Professional Staff « In addition to the above patient care providers, there will be one (1) nurse (LPN), and two (2) Medical Assistants (MA) in the facility to assist the doctors and the PAs. © Because NLHS cannot bill for non-provider staff, their salaries are part of the overhead for operating the clinic. « The LPN salary is $35,000, and the 2 MA salaries are $21,500 each. Both the LPN and the MA also have an additional 30% for taxes and benefits. Facility Expenses The facility is open 235 days/year and has a general operating cost (electricity, water and sewer, medical waste disposal, and supplies) that averages 40% of the total payroll, including taxes and benefits. + Said another way, this cost is 40% of everything NLHS will spend on the payroll in this project. + Note: operating costs are typically considered “variable costs” however for the purposes of this exercise, they are constant as described here. Question That Needs Answering The question NLHS needs to answer is, how much revenue must the clinic produce to cover the operating cost of the clinic, including the facility fees. For this, you must prepare an Excel spreadsheet to assist Michele with her report to the hospital CEO to determine if the project is viable.