Financial Modeling Tutorials
- Financial Modeling Basics
This article discusses the following –
- What is a Financial Modeling?
- Financial Model Uses
- Who builds Financial Models?
- Learn Financial Modeling
- Financial Modeling Examples
- Build a Financial Model
- Financial Model Tips
- Best Practices
What is Financial Modeling?
Financial Modeling Definition –
Financial Modeling is an abstract numerical scenario of a real-world financial situation used to ascertain the future financial performance by making projections. The user can manipulate the inputs to maintain the quality of a financial model, which will result in accuracy and dependency one can have on the outputs.
- Financial modeling is either building a model from scratch or work on maintaining the existing model by implementing newly available data to it. As you can notice all the above financial situations are of a complex and volatile nature. A financial model helps the user to gain an in-depth understanding of all the components of the complex scenario.
- In Investment Banking, Financial Modeling is used to forecast potential future financial performance of a company by making relevant assumptions of how the firm or a specific project is expected to perform in the forthcoming years, for instance how much cash flow a project is expected to produce within 5 years from its initiation.
It is easily possible to work on individual different parts of the model without affecting the whole structure and avoiding any huge blunders. It is useful when the inputs are of a volatile nature and are subject to change with newly available data. So there is a certain flexibility one can have with the structure when working on Financial Modeling as long as they are accurate, of course!
Though it sounds complex, it can be learned by steady practice and the appropriate know how.
What is Financial Model used for?
Financial modeling can be done for various situations; for e.g. valuation of a company, valuation of an asset, pricing strategies, restructuring situations (merger & acquisition), etc.
Below are the areas in which Financial modeling is generally used for –
Who builds the Financial Models?
Financial Models are build by the following –
Majorly financial modeling is used for determining reasonable forecasts, prices for markets/products, asset or enterprise valuation (Discounted Cash Flow Analysis, Relative Valuation), share price of companies, synergies, effects of merger/acquisition on the companies, Leverage Buy-out (LBO), corporate finance models, option pricing, etc.
How can you learn Financial Modeling?
There are various ways in which you can learn financial modeling.
- Learn Financial Modeling in Excel (Basic) – This is a step by step tutorial on Financial Modeling. Here you will learn to prepare a financial model of Colgate.
- Financial Modeling Course (Advanced) – This is an advanced tutorial on Financial Modeling. You will learn sector modeling of Banking, Petrochemical, Real Estate, Capital Goods, Telecommunication and more.
Financial Modeling Examples
There are various financial modeling examples differing in type and complexity as the situation demands. Financial models are widely used for valuation, sensitivity analysis, and comparative analysis. There are other uses of financial modeling as well, like risk prediction, pricing strategy, effects of synergies, etc. Different examples of financial models cater to their own set of specialties, requirements, and users.
Following are some of the Financial Modeling examples that are widely used in the Finance Industry:
Example #1 – Full Blown Three Statement Financial Modeling:
- This type of financial model represents the complete financial scenario of a company and projections. This is the most standard and in-depth form of a financial model.
- As the name suggests the model is a structure of all the three financial statements (Income Statement, Balance Sheet and Cash Flow Statement) of a company interlinked together.
- There are also schedules supporting the data. (Depreciation schedule, debt schedule, working capital calculation schedule, etc.).
- The interconnectivity of this model sets it apart, which allows the user to tweak the inputs wherever and whenever required which then immediately reflects the changes on the entire model.
- This feature helps us to get a thorough understanding of all the components in a model and its effects thereof.
- Important uses of this model are for forecasting and understanding trends with the given set of inputs.
- Historically the model can stretch back as long as the conception of the company and forecasts can stretch up to 2-3 years depending requirement.
Example #2 Discounted Cash Flow (DCF) Model:
- The most widely used method of valuation in the finance industry is the Discounted Cash Flow analysis method which uses the concept of Time Value of Money.
- The concept working behind this method says that the value of the company is the net present value (NPV) of the sum of the future cash flows generated by the company discounted back today.
- The discounting of the projected future cash flows is done by the discounting factor. One rather important mechanic in this method is deriving the ‘discounting factor’. Even the slightest error in the calculation of discounting factor can lead to enormous amounts of change in the results obtained.
- Usually, the Weighted Average Cost of Capital (WACC) of a company is used as the discounting factor to discount the future cash flows.
- DCF helps to identify whether a company’s stock is undervalued or overvalued. This proves to be a rather important decision making factor in case of investment scenarios.
- In simplicity, it helps to determine the attractiveness of an investment opportunity. If the NPV of the sum of future cash flows is greater than its current value then the opportunity is profitable or else it is an unprofitable deal.
- The reliability of a DCF model is strong as it is calculated on the base of Free Cash Flow, thus eliminating all the factors of expenses and only focusing on the freely available cash to the company.
- As DCF involves the projection of future cash flows it is usually suited for working on financials of big organizations, where the growth rates and financials have a steady trend.
Example #3 Leveraged Buyout (LBO) Model:
- In a leveraged buyout deal a company acquires other company by using borrowed money (debt) to meet the acquisition costs. Then the cash flows from the assets and operations of the acquired company are used to pay off the debt and its charges.
- Hence, LBO is termed as a very hostile/aggressive way of acquisition as the target company is not taken under the sanctioning process of the deal.
- Usually cash-rich Private Equity firms are seen to be engaged in LBO’s. They acquire the company with a combination of Debt & Equity (where a majority is of debt, almost above 75%) and sell off after gaining substantial profit after few years (3-5 years)
- So the purpose of an LBO model is to determine the amount of profit that can be generated from such kind of a deal.
- As there are multiple ways a debt can be raised each having specific interest payments, these models have higher levels of complexity.
- Following are steps that goes into making a LBO model;
- Calculation of purchase price based on forward trading multiple on EBITDA
- Weightage of debt and equity funding for acquisition
- Building projected income statement and calculate EBITDA
- Calculation of cumulative FCF during the total tenure of LBO
- Calculating Ending exit values and Returns through IRR.
Example #4 Merger & Acquisition (M&A) model:
- The M&A model helps to figure out the effect of merger or acquisition on the earnings per share of the newly formed company after the completion of the restructuring and how it compares with the existing EPS.
- If the EPS increases altogether then the transaction is said to be “accretive”, and if the EPS decreases than the current EPS the transaction is said to be “dilutive”.
- The complexity of the model varies with the type and size of operations of the companies in question.
- These models are generally used by Investment Banking, corporate financing companies.
- Following are steps that go into making an M&A model;
- Valuing Target & Acquirer as standalone firms
- Valuing Target & Acquirer with synergies
- Working out an Initial offer for the target firm
- Determining combined firms ability to finance transaction
- Adjust cash/debt according to the ability to finance the transaction
- Calculating EPS by combining Net income and figuring out an accretive/dilutive situation.
Example #5 Sum-of-the-parts (SOTP) financial model:
- Valuing of huge conglomerates becomes difficult to value the company as a whole with one single valuation method.
- So, valuation for the different segments is carried out separately by suitable valuation methods for each segment.
- Once all the segments are valued separately, the sum of valuations are added together to get the valuation of the conglomerate as a whole.
- Hence, it is called “Sum-of-the-parts” valuation method.
- Usually, SOTP is suitable in the case of a spin-off, mergers, Equity carve-outs, etc.
Example #6 Comparative Company Analysis model:
- Analysts while working on a comparative valuation analysis of a company looking for other similar companies that are equal in terms of size, operations and basically the peer group companies.
- By looking at the numbers of its peers, we get a ballpark figure of the valuation of the company.
- It works on the assumption that similar companies will have similar EV/EBITDA and other valuation multiples.
- It is the most basic form of valuation done by analysts in their firms.
Example #7 – Comparable Transaction Analysis Model
Transaction multiples Model is a method where we look at the past Merger & Acquisition (M&A) transactions and value a comparable company using precedents. The steps involved are as follows –
- Step 1 – Identify the Transaction
- Step 2 – Identify the right transaction multiples
- Step 3 – Calculate the Transaction Multiple Valuation
Prerequisites to Learning Financial Modeling
Building a Financial model will only be fruitful when it is giving out results which are accurate and dependable. To achieve efficiency in preparing a model, one should have a required set of basic skills. Let’s see what those skills are:
#1 Understanding of Accounting Concepts:
Building a Financial model is a pure financial document which uses financial numbers from a company or market. There are certain accounting rules and concepts that are constant in the financial industry over the world, e.g. US GAAP, IFRS (International Financial Reporting Standards), etc. These rules help in maintaining the consistency of presentation of financial facts and events. Understanding these rules and concepts are of extreme importance to maintain accuracy and quality while preparing to build a financial model in excel.
Our main focus in Accounting is also to identify and predict the accounting malpractices by companies. These are normally hidden away. You can see the confessions in Satyam Fraud Case
#2 Excel Skills:
The basic financial modeling in excel where is where a model is prepared is an application like MS Excel. A Financial Model excel involves a wide range of complex calculations spread over multiple tabs which are interlinked to show their relationships with each other. Having an in-depth working knowledge of excel like formulas, keyboard shortcuts, presentation varieties, VBA Macros, etc. are a must while preparing a financial model excel. Keeping knowledge of these skills gives the analyst an edge in his working skills over others.
#3 Interlinking of Financial Model Statements:
A 3 statement financial modeling needs to be interlinked together. The interlinking allows key numbers in the model to flow from one statement to the other, thus completing the inter-relationship between them and showing us the complete picture of the financial situation of the company. Example of interlinking: 1) Net change in cash (from Cash Flow Statement) must be linked to Cash in Balance Sheet. 2) Net Income from Income statement should be linked to Retained Earnings in Statement of Stock Holder’s Equity.
#4 Forecast financial model:
The skill of forecasting financial modeling is important because usually, the purpose of a financial model excel is to arrive at an understanding of the future scenario of any financial situation. Forecasting is both an art and a science. Using the reasonable assumptions while predicting the numbers will give an analyst a close enough idea of how attractive the investment or company will be in the coming period. Good forecasting skills increase the dependability of a model.
Financial modeling is full of minute details, numbers and complex formulas. A financial model is used by different groups like operational managers, management, clients. These people will not be able to decipher any meaning from the model if the model is looking messy and hard to understand. Hence, keeping the model simple in presentation and at the same time rich in details is of great importance.
How do you build a Financial Model:
Financial Modeling is easy as well as complex. If you look at the Financial Model you will find it complex, however, financial modeling a sum total of smaller and simple modules. The key here is to prepare each smaller modules and interconnect each other to prepare the final financial model.
You can refer to this step by step guide on Financial Modeling in Excel for detailed learning.
You can see below various Financial Modeling Schedules / Modules –
Please note the following –
- The core modules are the Income Statement, Balance Sheet, and Cash Flows.
- The additional modules are the depreciation schedule, working capital schedule, intangibles schedule, shareholder’s equity schedule, other long-term items schedule, debt schedule etc.
- The additional schedules are linked to the core statements upon their completion
A full-scale financial modeling is a lengthy and complicated process and hence disastrous to go wrong. It is advisable to follow a planned path while working on a financial model in order to maintain accuracy and avoid getting confused and lost in it. Following are the logical steps to follow:
- Quick review of Company Financial Statements: A quick review of the company financial statements (10K, 10Q, Annual reports, etc.) will give the analyst an overview of the company, as in, the industry of the company, segments, history of the company, revenue drivers, capital structure, etc. This helps in planning the structure of financial modeling by setting a guide path, which can be referred to from time to time as we progress.
- Historical Numbers: Once a fair idea is generated about the company and the types of financial models to be prepared it is advisable to start with inputting Historical data. Past Financial Statements of the company can be found on the company website. Data from as long as conception of the company is available. Usually past 3 years data is added to the historical side which is called as actual numbers. Color code the cells, so that historical and formulas can be quickly identified separately.
- Ratios and Growth rates: Once the historical numbers are added the analyst can proceed with calculating the required Financial ratios (Gross Profit Ratio, Net Profit Ratio, etc.) and growth rates (YoY, QoQ, etc.). These ratios help in identifying a trend for high level strategizing and also forecasting.
- Forecasting: Next step after historical and ratios is implementing projections and forecasting. It is usually done for 3 to 5 years. Line items like Revenue are usually projected on Growth rates. Whereas cost items like COGS, R&D, Selling General & Admin exp. Etc. are projected on the base of revenue margin (% of sales). Analyst should be careful while making the assumptions and should consider the trends of market.
- Interlinking of Statements: For the model to reflect the flow from one statement to other, it is imperative that they should be linked together dynamically and accurately. If done correctly the model should balance out all the statements thus giving it a finalized outlook.
Tips for creating a seamless Financial Model:
Following are some tips for creating a seamless and dependable financial model:
- Planning & Outlining: Before you rush into putting the historical numbers and start with your model, always begin with planning the whole project outline. Decide a timeline, the extent of the years of historical numbers, projection years, read about the industry and the company. Do an in-depth run of the recent Annual report or the situation at hand. This helps in giving you a steady head start.
- Quality: As you proceed through the complex process of modeling, do not forget about maintaining quality of the same. At the start it may look an easy task, but once the model gets chunky and complicated it becomes difficult for an analyst to maintain their nerves about it. Be patient and work with confidence. Take breaks if required. There goes a saying that “Trash in-Trash out”. It means if you are putting the wrong data, you will get wrong results.
- Presentation: The amount of efforts you are putting in for financial modeling will only be fruitful when it can be used and understood by others easily. Color coding, font size, sectioning, names of line items, etc. are all included under presentation. These may sound very basic, but combined effects of all these makes an enormous difference in the lookout of the model.
- Assumptions: What we project in financial modeling is only as good as the assumptions we are basing it on. If the assumptions are awry and lacking reasonable base the projections will be useless considering the inaccuracy. Setting assumptions should have a realistic thinking and reasonability in it. It should go with the industry standards and general market scenario. They shouldn’t be too pessimistic or too optimistic.
- Accuracy Checks: As the model flows longer and longer, with multiple sections and parts, it becomes difficult for the analyst to keep a check on the accuracy of the whole. So, it is important to add Accuracy Checks wherever necessary and possible. It helps in keeping the modeling process under constant quality check and avoids huge blunders at the end.
Financial Modeling Best Practices
- Flexibility: Every financial model should be flexible in its scope and adaptable in every situation (as contingency is a natural part of any business or industry). Flexibility of a financial model depends on how easy it is to modify the model whenever and wherever it would be necessary.
- Appropriate: Financial models shouldn’t be cluttered with excessive details. While producing a financial model, the financial modeler always should understand what financial model is, i.e. a good representation of reality.
- Structure: The logical integrity of a financial model is of utter importance. As the author of the model may change, the structure should be rigorous and integrity should be kept at the forefront.
- Transparent: Financial modeling should be such and based on such formulas which can be easily understood by other financial modelers and non-modelers.
COLGATE BALANCE SHEET HISTORICAL DATA
Also, note the color standards popularly used in Financial Modeling –
- Blue – Use this color for any constant that is used in the model.
- Black – Use Black color for any formulas used in the Financial Model
- Green – Green color is used for any cross-references from different sheets.
This has been a guide to What is Financial Modeling. Here we discuss uses of financial models, financial modeling examples (DCF, LBO, M&A, SOTP, Comps, Transaction Model), prerequisites to learning financial modeling, how to build a financial model, financial modeling tips and best practices. You can learn more about Financial Modeling from the following –