Assessment for future performance outlookFinancial Modelling
Financial Modelling is a tool used to forecast a picture of a company’s future performance based on historical performance or start-up nature of company. Financial Modelling includes detailed company-specific models which are used for decision making and financial analysis.
The term “Financial Modelling” encompasses anything related to finance on excel. A financial model is a living and breathing calculation with multiple historical financial values, mixed with prospective inputs to get you to an answer. Using excel you could create a financial budget, forecasting a project cost, developing an option pricing model or simply valuing a company, among nearly anything else.
It is all considered “financial modelling” and it all requires the user to identify the project, scope out inputs and calculations necessary to reach the conclusion and build the model.
It is a mathematical model of different aspects of the financial health of a given company and made on in excel so it’s easily possible to analyse impact of assumptions or change in value of variables hence giving it more flexibility. Financial modelling highlights:
- An organization is in need of funds (debt or equity)
- How business reacts to different financial /market situations
- In which company to make investments
- Company has analysed and defined risk
- Company had any change that caused loss of customers
- Strategic and Business Plans finding strengths and weaknesses
- Value to analyse Firms, IPOs
A good financial model should
- Be relatively simple
- Focus on key cash flow drivers
- Clearly convey assumptions and conclusions
- Evaluate Risks
Financial models can be used by different entities for different objectives. Investment bankers use models for transactions involving capital structure or ownership, accountants and valuation advisors use financial models for valuation projections, credit analysts use financial models to determine ability to repay debt, buy or sell side research analysts use financial models to determine a buy or sell rating on a particular security and top management uses financial models to determine internal budgets and projections for various reasons.
The 10 most common financial models are
#1 Three Statement Model
The 3 statement model is the most basic setup for financial modelling. As the name implies, in this model the three statements (income statement, balance sheet, and cash flow) are all dynamically linked with formulas in Excel. The objective is to set it up so all the accounts are connected, and a set of assumptions can drive changes in the entire model. It’s important to know how to link the 3 financial statements, which requires a solid foundation of accounting, finance, and Excel skills.
#2 Discounted Cash Flow (DCF) Model
The DCF model builds on the 3 statement model to value a company based on the Net Present Value (NPV) of the business’ future cash flow. The DCF model takes the cash flows from the 3 statement model, makes some adjustments where necessary, and then uses the XNPV function in Excel to discount them back to today at the company’s Weighted Average Cost of Capital (WACC).
These types of financial models are used in equity research and other areas of the capital markets.
#3 Merger Model (M&A)
The M&A model is a more advanced model used to evaluate the pro forma accretion/dilution of a merger or acquisition. It’s common to use a single tab model for each company, where the consolidation of Company A + Company B = Merged Co. The level of complexity can vary widely and is most commonly used in investment banking and/or corporate development.
#4 Initial Public Offering (IPO) Model
Investment bankers and corporate development professionals will also build IPO models in Excel to value their business in advance of going public. These models involve looking at comparable company analysis in conjunction with an assumption about how much investors would be willing to pay for the company in question. The valuation in an IPO model includes “an IPO discount” to ensure the stock trades well in the secondary market.
#5 Leveraged Buyout (LBO) Model
A leveraged buyout transaction typically requires modelling complicated debt schedules and is an advanced form of financial modelling. An LBO is often one of the most detailed and challenging of all types of financial models as the many layers of financing create circular references and require cash flow waterfalls. These types of models are not very common outside of private equity or investment banking.
#6 Sum of the Parts Model
This type of model is built by taking several DCF models and adding them together. Next, any additional components of the business that might not be suitable for a DCF analysis (i.e. marketable securities, which would be valued based on the market) are added to that value of the business. So, for example, you would sum up (hence “Sum of the Parts”) the value of business unit A, business unit B, and investments C, minus liabilities D to arrive at the Net Asset Value for the company.
#7 Consolidation Model
This type of model includes multiple business units added into one single model. Typically each business unit is its own tab, with a consolidation tab that simply sums up the other business units. This is similar to a Sum of the Parts exercise where Division A and Division B are added together and a new, consolidated worksheet is created.
#8 Budget Model
This is used to model finance for professionals in financial planning & analysis (FP&A) to get the budget together for the coming year(s). Budget models are typically designed to be based on monthly or quarterly figures and focus heavily on the income statement.
#9 Forecasting Model
This type is also used in financial planning and analysis (FP&A) to build a forecast that compares to the budget model. Sometimes the budget and forecast models are one combined workbook and sometimes they are totally separate.
#10 Option Pricing Model
The two main types of models are binomial tree and Black-Scholes. These models are based purely on mathematical models rather than subjective criteria and therefore are more or less a straightforward calculator built into Excel.
What is the Financial Modelling Process?
Financial modelling is an iterative process and follows these standard steps:
- Enter three to five years of historical financial information from the three financial statements
- Analyse the historical performance
- Generate assumptions about future performance
- Use the assumptions to forecast and link the income statement, balance sheet, and cash flow statement
- Perform discounted cash flow analysis
- Perform sensitivity analysis
- Audit and stress test the model
What are the three financial statements?
The three financial statements are:
- The Income Statement
- The Balance Sheet
- The Cash Flow Statement
These three core statements are intricately linked to each other and this guide will explain how they all fit together. By following the steps below you’ll be able to connect the three statements on your own.
Overview of the three financial statements:
#1 Income statement
Often, the first place an investor or analyst will look is the income statement. The income statement shows the performance of the business throughout each period, displaying sales revenue at the very top. The statement then deducts the cost of goods sold (COGS) to find gross profit. From there, the gross profit is affected by other operating expenses and income, depending on the nature of the business, to reach net income at the bottom – “the bottom line” for the business.
- Shows the revenues and expenses of a business
- Expressed over a period of time (i.e., 1 year, 1 quarter, Year-to-Date, etc.)
- Uses accounting principles such as matching and accruals to represent figures (not presented on a cash basis)
- Used to assess profitability
#2 Balance sheet
The balance sheet displays the company’s assets, liabilities, and shareholders’ equity. As commonly known, assets must equal liabilities plus equity. The asset section begins with cash and equivalents, which should equal the balance found at the end of the cash flow statement. The balance sheet then displays the changes in each major account. Net income from the income statement flows into the balance sheet as a change in retained earnings (adjusted for payment of dividends).
- Shows the financial position of a business
- Expressed as a “snapshot” or financial picture of the company at a specified point in time (i.e., as of December 12, 2017)
- Has three sections: assets, liabilities, and shareholders’ equity
- Assets = Liabilities + Shareholders Equity
#3 Cash flow statement
The cash flow statement then takes net income and adjusts it for any non-cash expenses. Then, using changes in the balance sheet, usage and receipt of cash is found. The cash flow statement displays the change in cash per period, as well as the beginning balance and ending balance of cash.
- Shows the increases and decreases in cash
- Expressed over a period of time, an accounting period (i.e., 1 year, 1 quarter, Year-to-Date, etc.)
- Undoes all accounting principles to show pure cash movements
- Has three sections: cash from operations, cash used in investing, and cash from financing
- Shows the net change in the cash balance from start to end of the period
How are these 3 core statements used in financial modelling?
As explained above, each of the three financial statements has an interplay of information. Financial models use the trends in the relationship of information within these statements, as well as the trend between periods in historical data to forecast future performance.
The preparation and presentation of this information can become quite complicated. In general, however, the following steps are followed to create a financial model.
- Line-items for each of the core statements are set up. This provides the overall format and skeleton that the financial model will follow
- Historical numbers are placed in each of the line-items
- At this point, the creator of the model will often check to make sure that each of the core statements reconciles with data in the other. For example, the ending balance of cash calculated in the cash flow statement must equal the cash account in the balance sheet
- An assumptions section is prepared within the sheet to analyse the trend in each line-item of the core statements between periods
- Assumptions from existing historical data are then used to create forecasted assumptions for the same line items
- The forecasted section of each core statement will use the forecasted assumptions to populate values for each line item. Since the analyst or user has analysed past trends in creating the forecasted assumptions, the populated values should follow historical trends
- Supporting schedules are used to calculate more complex line items. For example, the debt schedule is used to calculate interest expense and the balance of debt items. The depreciation and amortization schedule is used to calculate depreciation expense and the balance of long-term fixed assets. These values will flow into the three main statements
What is Sensitivity Analysis?
Sensitivity Analysis is a tool used in financial modelling to analyse how the different values of a set of independent variables affect a specific dependent variable under certain specific conditions. In general, Sensitivity Analysis is used in a wide range of fields, ranging from biology and geography to economics and engineering.
It is especially useful in the study and analysis of a “Black Box Processes” where the output is an opaque function of several inputs. An opaque function or process is one which for some reason can’t be studied and analysed. For example, climate models in geography are usually very complex. As a result, the exact relationship between the inputs and outputs are not well understood.
A Financial Sensitivity Analysis, also known as a What-If analysis or a What-If simulation exercise, is most commonly used by financial analysts to predict the outcome of a specific action when performed under certain conditions.
Financial Sensitivity Analysis is done within defined boundaries that are determined by the set of independent (input) variables.
For example, Sensitivity Analysis can be used to study the effect of a change in interest rates on bond prices if the interest rates increased by 1%. The “What-If” question would be: “What would happen to the price of a bond if interest rates went up by 1%?”. This question is answered with sensitivity analysis.
Sensitivity Analysis Example
John is in charge of sales for Holiday Co that sells Christmas decorations at a shopping mall. John knows that the holiday season is approaching and that the mall will be crowded. He wants to find out whether an increase in customer traffic at the mall will raise the total sales revenue of Holiday Co and if so, by how much.
The average price of a packet of Christmas decorations is $20 and during the previous year’s holiday season, Holiday Co sold 500 packs of Christmas decorations, resulting in total sales worth $10,000.
After carrying out a Financial Sensitivity Analysis, John determines that a 10% increase in customer traffic at the mall results in a 7% increase in the number of sales.
Using this information, John can predict how much money company XYZ will generate if customer traffic increases by 20%, 40%, or 100%.
Based on John’s Financial Sensitivity Analysis, these will result in an increase in revenue by 14%, 28%, and 70%, respectively.
Presenting of Financial Model
The one who made the model, knows the model best and is most qualified to talk about it. When asked to communicate the results of the financial model as a formal presentation to a board or senior management one needs to decide how to communicate it in a clear and concise way. Many find that presenting financial models that have taken weeks to build into a ten-minute presentation, challenging.
If you simply copy and paste a chart into Word or PowerPoint, the links to the underlying data in Excel, will be maintained. This is fine if you’re planning to make changes, but it can make the file size very large, and could also lead to your accidentally sending confidential information unintentionally embedded into another document.
To avoid this, you need to paste the chart as a picture. Copy the chart and then use Paste Special in the destination document to paste the chart as a picture or JPG.
In this kind of environment where you need to convey lots of information, having the summary tables or charts on a PowerPoint slide behind you while you’re speaking will be helpful. It can also help to take the focus off you if you’re a little bit nervous.
Your audience is probably not interested in seeing the workings of the model, but they might like to see live and changing scenarios. If the model is built correctly, you’ll be able to make a single change to the input assumptions, and the audience will be able to see the effects of these changing scenarios in real time.
Ensure to test all possible inputs in advance. Having a system glitch during a live analysis is a credibility killer.
Whether presenting in Excel or PowerPoint slides, follow these basic rules of making financial presentations:
- Display one key message at a time. Do not crowd screen with too much detail or convey too much at once
- Use white spaces in gridlines. Gridlines create clutter and many in the audience will switch off
- Give a more detailed report after presentation. Show a high-level summary on screen
- Ensure font is big and clear on projection. Test in advance. Sometimes colours wash out, making text difficult
- If showing the model on screen, increase zoom. Remember you will only show a small portion of the screen
- Avoid jumping around. Your audience isn’t as familiar with the model as you are. They’ll need time to digest
- Use charts and graphics to display instead of text and numbers.
Be prepared for questions regarding output, inputs, assumptions and workings of the model. Ensure that you can defend the assumptions used or the way they are calculated. The model output is as good as assumptions.