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:
- Budgeting and estimating (forecasting for the years in the future)
- Business valuation
- Capital allocation (prioritising the projects and their respective investments)
- Expanding the business organically (business expansion strategy such as entering new markets, opening new stores etc.)
- Generating capital (equity or debt)
- Making acquisitions (assets or businesses)
- Divesting or selling assets and business units
A financial model must include the below mentioned sections:
- Assumptions and drivers
- Balance sheet
- Charts and graphs
- Cash flow statement
- Income statement
- Sensitivity analysis
- Supporting schedules
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:
Historical outcomes and conventionsEach 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
Preparing an income statementWith 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
Preparing a balance sheetAfter 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)
Preparing the supporting schedulesBefore 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
Finalise the balance sheet and income statementData 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
Preparing the cash flow statementOnce 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
Execute the Discounted cash flow (DCF) analysisOnce 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
Perform sensitivity analysis and scenariosOnce 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?)
Prepare graphs and chartsGraphical 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
Audit the model and stress testingOnce 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
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:
Any questions? Schedule a call with one of our experts.
Whether you prefer to meet and speak over the internet, or if you prefer an in person conversation we can help you with your preference.
Stay up-to-date with the latest news affecting small businesses, get business tips and tax saving advice.