Written By Team CFI

Financial model a tool to forecast financials for the future periods (i.e., revenues, expenses, profitability, and cash flow how, among others) based on past historical financials of the organization.

Screenshot 2022 09 29 At 4.13.11 PM

The company’s forecasted financials provide direction to the management and sets forth the target financials to achieve in future (i.e. business development teams will work accordingly to achieve forecasted revenue, operations teams will aim to limit their expenses based on forecasted expenses and thereby achieving desired profitability for the company owners.)Excel spreadsheets are used to generate most financial models. The three-statement model, which includes income statement, balance sheet, cash flow, and supporting schedules, is one of the simplest types of Financial Model.

Why Financial Modeling as a skill is so important?

Image001 1

A financial model gives strategic direction to the company’s future. It helps organizations understand their primary revenue drivers, cost drivers, working capital situation, liquidity situation, free cash flow availability, and thereby providing possible worth of the company.

Business also frequently modifies models to suit their own requirements as business situations and sales targets keeps changing. Models often produce outcomes for a quarter, month, or year because dynamic nature of businesses.Its always important for organizations to consider building a customized financial model and represent themselves using numbers rather than just using some prebuilt templates and standard assumptions.

Areas where financial models are used

Image002

Financial models are used for various situations such as:
  • Company valuation
  • Merger and acquisitions
  • Expansion of business
  • Raising funds
  • Budgeting and forecasting
  • Credit Analysis
  • Equity Investments

People who create financial models

Image003

  • Investment banking Analyst
  • Private equity Analyst
  • Risk Analyst
  • Data Analyst
  • Credit Analyst
  • Fund Managers
  • Equity research analyst
  • Portfolio managers

How can you learn financial modeling?

Image004

The most effective way to master financial modelling is by practicing. The process takes time to become an expert when it comes to developing a financial model, and you can master it by repeating the process across a few different parameters to get a hang of it.

Research reports on equity is a great method to get your feet wet, as it will give you something to compare your outcomes with.

It’s also crucial to develop an established foundation of understanding by attending professionally led financial modelling courses like the one we offer.

How to make a 3 statement Financial Model

No. 1: Assumptions and Drivers

Screenshot 2022 09 29 At 4.11.37 PM

In this sheet, we put all assumptions / inputs based on management or analyst expectations. These assumptions will be related to revenue growth, cost contribution and drivers, capex, working capital days expectations, dividend, among others.

In this sheet we will also consider economic scenarios and that can be categorized as best, base or worst to name a few.

In most models, we also have supporting schedules which is additional from Assumptions and Drivers. There schedules provides detailed analysis on certain things like debt movements, equity changes, cash surplus use and crunch, among others.

No. 2: The Income Statement

Screenshot 2022 09 29 At 4.10.10 PM

In this statement, we calculate the forecasted revenues, costs items and finally profits. We also perform margin analysis (gross, ebitda, ebit and net) to understand how margin changes at each level.

One of the most important key takeaways is how revenues are growing and how much of it is resulting to profitability. Management gets a clear idea about how each costs heads are decreasing the profits and how can they possibly reduce the costs to improve margins.

Start-ups gets to know how much of marketing expenses they are making and how it is hitting the profitability.

Standard items of the statements are:

  • RevenueCOGS=Gross Profit
  • Gross ProfitSG&A = EBITDA
  • EBITDAD&A = EBIT
  • EBITInterest Exp = EBT
  • EBTTax = Net Income

No. 3: The Balance Sheet

Screenshot 2022 09 29 At 4.08.21 PM

The balance sheet is a overview of company’s financial positions and balances of assets, equity, and liabilities.

Balance sheet includes Current and non-current assets, current and non-current liabilities, and equity.

Assets = Liability + Equity

We project balance sheet items using supporting schedules like D&A schedule, working cap schedule, debt, cash and equity schedule, among others. All the assumptions are mentioned in “Assumptions and Drivers” sheet and then these assumptions are linked to supporting schedule to project Balance Sheet numbers.

No. 4: The Cash Flow Statement

One of the most important parts of financial modelling is preparation of cash flow statement.

Screenshot 2022 09 29 At 4.06.17 PM

Cash flow statement has three sections namely

  • Cash Flow from Operating Activities: We start with Net Income then add back depreciation, adjust for changes in non-cash working capital, and then we have the cash from operations.
  • Cash Flow from Investing Activities: In the section we put investing line items such as capital expenditures, portfolio level investments, and disposal of old assets, among others
  • Cash Flow from Financing activities: In this section we put raising and repayment of debt and equity, dividend payments, repurchases, among others.

No. 5: Company Valuation and Sensitivity Analysis

We then use all 3 projected statements to calculate free cash flow (i.e. EBIT(1-tax)+D&A-Capex-working capital changes), discount all future cash flows using cost of capital and sum them up to find intrinsic value of company.

Screenshot 2022 09 29 At 4.01.38 PM

Once we get the intrinsic value, we can then perform sensitivity analysis by changing most imp assumptions of DCF (i.e. cash flows perpetual growth rate and cost of capital) as these two numbers are extremely difficult to calculate in reality and management wants to see possible valuation changes when we change these two variables.

 

Financial modeling best practices
  • Simple and Clear: A model should state assumptions clearly. It’s a good practice to state assumptions in a single sheet so end user can tweak those, if required, to suit their needs. Moreover, the use of unnecessary and complex formulas is not advisable.
  • Transferrable: A model should have all required comments and notes in the sheets to make it transferrable. In other words, other members of the team should be able use it and interpret it than just the creator himself.
  • Consistent: All the formulas, calculations and color-coding should be consistent throughout the model and one formulas / calculation should not be done in different ways in different sheets. Moreover, it’s a good practice to keep the starting cell same in each sheet.
  • Dynamic: A FinancialModel should be dynamic enough so future assumptions and major changes can be incorporated, easily.
  • Flexible: It’s a good practice to make the model in a module wise manner, so if user wants to add or remove a module, he /she should be able to do it.

In the next blog, we will discuss the types of financial models’ usage.

Types of Financial Models

  • 3 Statement model
  • Discounted cashflow
  • Comparative company analysis (Trading Comps)
  • Comparable transaction analysis (CompAcqs)
  • Merger and acquisition model
  • Leveraged buyout
  • Credit Model