General Hospital Financial Model
General Hospital Financial Model consists of a financial model related to the start up and operations of a general hospital' business.
The model generates:
1) Three financial statements (profit & loss, balance sheet and cash flow)
2) Valuation using free cash flows and feasibility metrics
3) KPIs and metrics relevant to hospitals
4) Various charts
5) Detailed revenue breakdown per clinic
6) Detailed costs breakdown per clinic
7) Executive Summary tab which aggregates the most important metrics of the model.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the manual tab you can feed the general information for the model such as: model name, responsible, timeline of the model and date and currency conventions.
Additionally, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.
There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents, and checks).
There are 7 main tabs where the user input is needed: "Manual", "Revenues", "Direct Expenses", and "Direct Expenses per Clinic", "Indirect Expenses", "Other" and "Weighted Average Cost of Capital". The user needs to fill the yellow cells in each of these tabs, all the remaining tabs are calculated automatically.
On the Revenues tab, the user sets the number of rooms, and the number of beds per room, the percentage of active beds, and the room pricing, then the user fills the Outpatient admissions, and the percentage admitted as emergencies and appointments which are further split into consultations, diagnostic exams, or both, and finally the percentage which is converted to inpatients. Then the user inputs the average pricing for each consultation and exam as well as the average percentage split per outpatient for both emergencies and appointments.
Moving to the Inpatients the user sets the inpatient admissions, and the percentage of those hospitalized or both operated and hospitalized. Next the user sets for the hospitalized patients the length of stay for each clinic, as well as the percentage split each clinic, and of each room type. While for the operated and hospitalized patients the user needs to set the surgeries split, the length of stay, as well as the prices per operation. The operations are further split into various levels of operations' importance. Additionally the consumables and drugs per inpatient are set.
The remaining tab consist of pharmacy revenues calculated as a percentage of inpatients and outpatients, and price per drug, ambulatory services, food and beverages, parking, and other rented spaces in the building.
In the next tab the user fills the maximum operations that can be handled per year on average per doctor, and the number of surgeons employed, as well as their yearly salaries and their percentage bonus from operations performed. Similar inputs are needed for doctors (non-surgeons) and part time (on call doctors) such as headcount, salary and bonus if any. The user also needs to set the number of nurses and paramedics per bed, headcount, and salaries.
Following the user sets the cost of special materials per operations, for hospitalizations, and diagnostic exams. Similar inputs are needed for medical material expenses. Next the user sets the direct expenses for the ancilliary revenues such as pharmacy, ambulatory services, food and beverages, parking and other rentals, other materials, and other direct expenses.
In the Direct Expenses per Clinic the user sets the split of surgeons and other doctors per clinic. Moving to the indirect expenses tab, the user sets the headcount and salaries for the hospital administration staff, as well as other non salary costs. Next the user inputs some operating expenses related to the hospital operations such as facility related costs, utilities, travel and public relations, rent costs, and other outsourced costs.
In the Other tab, all the remaining assumptions are aggregated such as tax assumptions and inflation assumptions, investment costs, capital expenditures, depreciation rates, working capital assumptions such as inventory, receivables, and payables, and finally debt financing, overdraft facility and equity injections. In the remaining tab, the user can set the cost of debt and the cost of equity which results in the weighted average cost of capital.
All the remaining tabs are automatically calculated from the assumptions that the user has set. The resulting tabs are the three financial statements such as the profit and loss, balance sheet and cash flow on a yearly basis, the valuation of the business and its feasibility metrics (such as Net Present Value, Internal Rate of Return, Payback and Discounted Payback Period, Profitability Index, Sensitivity Analysis), various KPIs and ratios (such as business and financial ratios , costs breakdown, valuation metrics), all the charts are in the "Graphs" tab (Business and Profit and Loss Charts, Balance Sheet Graphs, and Cash Flow and Valuation Charts). Additionally, the most important financial and business metrics are aggregated in the Executive Summary tab.
Finally, the checks tab where the most critical checks are aggregated. Whenever you see an error message in any page, you should consult this page to see where the error is coming from.
