Financial Modelling

Business Financial Modelling Overview

Financial modelling is used to assess an organisation’s previous or historical financial position, to forecast the business performance in the future or benchmark an organisation with its peers/competitors. A financial modelling tool can be built in Microsoft Excel to estimate a corporation’s financial performance in the coming years. The model is designed taking into account parameters such as assumption about a company’s future performance based on historical data, and the process involves preparing a balance sheet, cash flow statement, and an income statement, along with supporting schedules (also referred to as a 3 statement prototype). Post the basis analysis, advanced models such as sensitivity analysis, leverage-buyout (LBO), discounted cash flow analysis (DCF model), and mergers and acquisitions (M&A) can be built further.

Financial model and its use!

The data computed through a financial model is used for making the correct business choice and assessing the finances, both outside and inside of an organisation. Within an organisation, officials use financial models to arrive at the following decisions:

1. Budgeting and estimating (forecasting for the years in the future)
2. Business valuation
3. Capital allocation (prioritising the projects and their respective investments)
4. Expanding the business organically (business expansion strategy such as entering new markets, opening new stores etc.)
5. Generating capital (equity or debt)
6. Making acquisitions (assets or businesses)
7. Divesting or selling assets and business units

A financial model must include the below mentioned sections:

1. Assumptions and drivers
2. Balance sheet
3. Charts and graphs
4. Cash flow statement
5. Income statement
6. Sensitivity analysis
7. Supporting schedules
8. Valuation

Building a financial model in Microsoft Excel

In order to build a financial model, various sections need to be analysed before tying it all together. Below mentioned is a step-by-step analysis from where an analyst should begin and ultimately associate all the sections:

1. Historical outcomes and conventions

   Each financial model begins with an organisation’s historical outcomes. An analyst typically starts with creating a financial model by capturing financial data for three to five years into an Excel workbook. Next step is to make the assumptions, based on historical data and other economic parameters, and compute fixed and variable cost, gross margins, revenue growth rate, accounts payable days, and inventory days. Basis the assumptions, the values/figures can be computed

2. Preparing an income statement

   With the estimate conventions in place, an analyst can compute certain elements of an income statement such as cost of goods sold (COGS), revenue, operating expenses, gross profit, and earnings before interest, tax, depreciation and amortization (EBITDA). Value for amortization, depreciation, interest, and taxes can be computed later

3. Preparing a balance sheet

   After the assumptions for an income statement are made, an analyst can begin preparing the balance sheet. An analyst can start by computing inventory and accounts receivable, which are together functions of cost of goods sold (COGS) and revenue as well as the accounts receivable days and inventory day assumptions. Next, make entry for accounts payable as it is a function of accounts payable days and cost of goods sold (COGS)

4. Preparing the supporting schedules

   Before finalising the balance sheet and income statement, an analyst should prepare a schedule for capital assets like Plant, Property & Equipment (PP&E) as well as for interest and debt. The PP&E schedule will extract information from the historical data, add capital expenditures and deduct depreciation. The debt schedule will extract information from the historical data, add growth in debt and deduct repayments. Interest will be computed on the average debt balance

5. Finalise the balance sheet and income statement

   Data from the supportive schedules help to finalise the balance sheet and income statement. In an excel workbook, for the income statement, associate depreciation to the Plant, Property & Equipment schedule and also link interest with the debt schedule. From here an analyst can compute earnings before tax (EBT), net income and taxation amount. On the balance sheet, link the closing Plant, Property & Equipment balance and closing debt amount from the schedules. Stakeholder’s equity can be finalised by adding net income and capital raised to last year’s closing balance, and deducting dividends or shares repurchased

6. Preparing the cash flow statement

   Once an analyst has finalised the balance sheet and income statement, he/she can prepare the cash flow report with the reconciliation technique. Begin with net income, adding back depreciation and alter for variations in non-cash working capital, which provides the number in cash from operations. Cash used in financing is a function of capital expenditures in the Plant, Property & Equipment plan and cash from financing is arrived from the assumptions that were put down while raising equity and debt

7. Execute the Discounted cash flow (DCF) analysis

   Once an analyst has prepared the three statement prototype, he/she can compute free cash flow and execute the business assessment process. The free cash flow of a corporate is discounted back to the present day at the company’s cost of capital (required rate of return or opportunity cost). Our company provides a full suite of step-by-step instructions along with examples and templates

8. Perform sensitivity analysis and scenarios

   Once the valuation section and Discounted cash flow analysis is complete, an analyst must integrate scenarios and sensitivity analysis into the model. The reason behind this analysis is to decide how much business, in value terms, will be impacted by variations in fundamental assumptions. This is very valuable for measuring the risk of an asset or for business development purpose (For example, will an organisation be required to raise money if sales volume decreases by a certain percentage?)

9. Prepare graphs and charts

   Graphical representation of the analysis given stakeholders a clear picture of the projected figures. The most beneficial way to demonstrate the results of a financial model are using charts and graphs. Most senior manager/directors/stakeholders don’t have time to go-through the complete working model, hence charts and graphs are very effective

10. Audit the model and stress testing

    Once the financial model is complete, an analyst should begin stress-testing risky circumstances to understand if the model performs as expected. It’s also imperative to verify if the auditing tools and the Excel formulas are all functioning correctly

Uses of Financial Modelling in Financial analysis

• Investment Banking: Financial Modelling is an elementary tool for all Investment Bankers. The tool can be used to assess an organisation by estimating its financials and revenues. With the estimation as a base, an investment banker can then recommend a seller or buyer on procurement of funds or investments
• Equity Research: Financial Modelling helps analysts in examining an organisation’s financial calculations, competitor’s estimates and other changing aspects to control the risk and put an educated guess on the return from such investments
• Credit Rating: Financial Modelling empowers Credit analysts to convert historical data such as unpaid debts into growth estimates. These estimates, then, regulate the degree and nature of numerous risks which is then organised to provide an organisation with a rating
• Project Finance: Financial Modelling aids an analyst to assess the financial feasibility of a project. It is also the main tool used to prepare a funding plan using appropriate equity and debt mechanisms
• Mergers & Acquisitions: Financial Modelling enables an organisation to assess the value of assets or value of the other company. Hence, it provides them clarity on decisions pertaining to mergers and acquisitions through data forecasting, performing competitors’ analysis, and preparing debt schedules
• Corporate Finance: Financial Modelling can be used by organisations to evaluate their own projects and finance. It helps an organisation to create a funding plans for corporate assignments

DNS Accountants and Financial Modelling

Finance specialists typically work with huge volumes of financial data and the key to effectively managing such data is the capability to bring together and structure it profoundly, for the senior management to build strategies and decision making. The next step in the study of financial figures typically results in an evaluation exercise.

Certain scenarios where our team can help with valuation are:

• Determining tax obligations
• Funding from external sources
• Forecasting and decision making
• Resolving shareholders’ disputes
• Selling of business
• Resolving corporate restructuring

About the author

Sumit Agarwal
Sumit Agarwal (ACMA ACA India), the Managing partner of dns accountants is a highly respected accountant with expertise in helping owner-managed businesses.