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.
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
- 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?
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
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
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:
- Revenue–COGS=Gross Profit
- Gross Profit– SG&A = EBITDA
- EBITDA– D&A = EBIT
- EBIT– Interest Exp = EBT
- EBT– Tax = Net Income
No. 3: The Balance Sheet
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.
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.
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.
- 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