Operating Company Financial Model (Excel XLSX)

Operating Company Model consists of an Excel model which allows the user to generate forecast financial statements based on historical financial statements of an already operating company. The model generates the three financial statements (profit and loss, balance sheet, and cash flow) and performs a valuation, along with graphs and ratios.

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 cells.

On the timing 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. Grey cells are restricted cells which you must not change.

There is also 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, and contents).

On the Historical FS tab, the user can fill the yellow input cells to match the historical financial statements of the operating company. The historical financial statements are set to balance out automatically based on the assumptions the user enters for the profit and loss, and balance sheet.

On the drivers' tab, the user can set the scenario to be used as well as the assumptions for the revenue growth, cost of goods sold, and interest rate for each scenario case. Additionally, the user can also modify the profit and loss drivers in the yellow cells such as Sales and General Administrative expenses growth, other operating expenses margins, and income tax rates. Regarding the balance sheet items the user can amend the working capital days (receivables, inventory, and payables), the prepaid expenses, the long-term investments and capital expenditures, the depreciation as a percentage of net book value. Finally, the user can change the debt drawdowns and repayments, as well as the current and non-current liabilities, and share capital increase.

Regarding the valuation assumptions, the user can modify the cost of equity and the growth rate to perpetuity. The debt-to-equity gearing is based on the last historical year, and the cost of debt is based on the after-tax interest rate in the latest historical financial statement.

Based on the historical financial statements tab and on the driver's tab, the forecast financial statements are generated in this tab. The profit and loss, balance sheet, and cash flow statements result based on the previous assumptions set by the user.

In the valuation tab, free cash flows to the firm is performed and the enterprise value and equity value are calculated. Furthermore, sensitivity is performed on both equity value and equity value per share by using the discount rate and the growth rate to perpetuity as sensitivity parameters. Finally, a simple multiples valuation is performed based on the Enterprise Value to Sales and on the Enterprise Value to EBITDA (a 5-year historical average is used from the operating company).

On the next tab, a set of ratios is calculated focusing on profitability and returns, financial strength and leverage, DuPont breakdown and earning power, liquidity, working capital operations, and valuation ratios.

Moving to the Graphs tab, a set of charts are presented such as revenues, cost of goods sold and operating expenses, gross profit, EBIT, EBITDA, net income and the respective margins, debt and equity, cash flows and cash balance, DCF, and Multiples, Free Cash Flows, and Equity Value sensitivity.

Finally, the checks tab where the most critical checks are aggregated. Whenever you see an error message on any page, you should consult this page to see where the error is coming from.