A Beginner’s Guide to Financial Modeling and Valuation Projection Models

Important business decisions relating to project finance, like whether to raise money, whether a particular investment is to be done, or if the merger or acquisition (M&A) should take place, can be taken with the help of financial modeling. Financial models are an important part of the job for anyone intending a career in financial planning and analysis, corporate development, equity research, investment banking, commercial banking, etc.

The following are some good practices for structuring a financial model:

• Use graphs, charts, and tables for summarizing information.
• Don't link the existing Excel spreadsheet to another Excel workbook unless it is essential. If it is unavoidable, then clear information about the links existing should be given.
• Use iterative calculations rather than circular references as far as possible.
• Use built-in error checks without a “plug”.
• Use the cell comments function to describe assumptions and calculations (Shift + F2).
• Use a clear header and subheads to distinguish the sections.
• Clearly separate the assumptions from the rest of the model.
• Use color-coding to distinguish between the inputs and the formulas.
• Use one worksheet structure with grouping.
• Build a standalone 3 statement model which links the income statement, balance sheet, and cash flow statement into one connected financial model, instead of a multi worksheet approach. It will be easier to navigate as one doesn't have to switch between the tabs. Also, the single worksheet approach is more organized with the use of grouping cells and there is less risk of unlinking the formulas.
• Pull forward or repeat information to understand the logic of the model. For example from the income statement, pull forward Earnings Before Interest, Tax, Depreciation, and Amortization to the cash flow valuation section.

Three Statement Model

When three financial statements, i.e., the income statement, balance sheet, and cash flow statement, are linked dynamically with Excel formulas, then it is a three-statement model. It is an integrated model where knowledge of finance, accounting and excel skills are required. Historical financial information is to be included in excel. 

Initial Public Offering (IPO)

An IPO model is built in Excel to value the business before going public by the investment bankers. “IPO Discount” is included in the IPO model for ensuring the success of trading the stocks in the secondary market. The Ratios of similar public companies are looked upon to derive the value of another business in conjunction with an assumption about how much investors will be willing to pay for the company.

Discounted Cash Flow Model (DCF)

DCF model is simply a forecast of the company's free cash flow, i.e., the cash which is available to both debt and equity investors, discounted back to today’s value, which is called the Net Present Value (NPV). 

In simple words, the DCF model takes the cash flows, makes some adjustments, and uses the XNPV function in Excel to discount them. XNPV function is more precise as the user has to select a discount rate, a series of cash flows which can either be negative cash flow (in case of outgoing payments) or positive cash flow (in case of income) and a series of corresponding dates for each cash flow. The DCF model is used in equity research which is a division of investment banking. 

From a career perspective, equity research is quite demanding and there is a lot of competition for the position of equity research analyst and equity research associate. The compensation is solid and the work is cerebral. The average salary of an equity research analyst and equity research associate is $100K.

Merger Model 

Also known as M&A, it is a more advanced model, used to evaluate dilution/accretion of a merger or acquisition. In investment banking and corporate development, the M&A model is very commonly used. The objective of such an analysis is to assess the impact of an acquisition on the acquirers' expected future ‘Earning Per Share’ (EPS). 

Consolidation Model 

It includes multiple business units (projects with their cost and revenues) in one single model. The first worksheet is a summary or a consolidation worksheet. Here all the projects’ financial metrics are summarized. The next step is to insert numbers into the summary tables by using formulas. There are very few inputs in the consolidation model, but it involves various data sources and the calculations are much more in-depth.

Budget Model

It focuses more on the income statement, to get the budget together for the coming years. Identification of financial goals, calculation of total income, entering of all the cash, cheque, online bank transactions, credit card transactions into the excel spreadsheet, identifying the areas of expenses reduction, comparison of incomes and expenses are a part of the budget model. This model is used by professionals for analysis and financial planning.

Sum of Parts Valuation Model (SOTP)

This model is built by taking several DCF models and adding them together. It estimates the value of each business segment separately and they are added together to find out the total enterprise value. Then the net debt is deducted from the enterprise value to calculate the equity value. 

Leveraged Buyout Model (LBO)

This is prepared mainly to know the enterprise value by the financial buyer that acquires it. It is built in Excel to evaluate an LBO transaction which will help the investors to realize a high possible ‘Internal Rate of Return’ (IRR), where a business is acquired using a large amount of debt. Key credit metrics in an LBO model include interest coverage ratio, EBITDA, debt service coverage ratio, and fixed charge coverage ratio.

Option Pricing Model 

It is a calculator built into excel. Binomial Tree and Black Scholes are the two types of Option Pricing models. Less subjective criteria and more mathematical formulas are used in this model.

• Research Consultant: They build financial models across multiple sectors by leveraging the SMART (digitizing financial research and spreading workflows) platform. The research consultant has to undertake the migration of existing client analyst models into the SMART compliant format for future updates. In such a career, exposure to report writing i.e., initiation/earnings report knowledge is required.
• Equity Research Analyst: or data crunchers, are research analysts who gather, analyze and improve profitability and efficiency for the companies. Now to excel in this job knowledge of excel, accounting skills which should be more than just numbers, valuation techniques, financial modeling skills, and most importantly, writing proficiency is needed.
• Associate Analyst: They are individuals who are in charge of setting up reporting systems, collecting data that affect business decisions, preparing reports for detailed findings and solutions to be recommended.  For becoming an analyst, a good understanding of databases such as Bloomberg, FactSet, CapitalIQ, Thompson, Reuters, etc is required.
• Online Financial Modeling Tutor: The tutor should be able to teach basics to advance excel, especially different formulas and functions like V-lookup, X-lookup, T,F, Z - tests, shortcuts of excel, index, goal, solver, data validation, graph chart , countifs etc. They teach how to develop financial plans, how to analyze investment proposals, how to apply valuation methods appropriate to ventures at different development stages and the difference between traditional and social investors/ entrepreneurs.
• Investment Banker: Investment banker responsibilities include raising capital, valuations to execute finance deals, suggest better investment opportunities. Sound judgment and quantitative skills, and familiarity with underwriting procedures are a must to become a successful investment banker. Knowledge of regulations, grasp of current market developments, outstanding oral and written communication ability, and having a valid license - Series 7, 66, professional certification - clearing CFA levels becomes an asset.
• Valuation Experts: They work with business owners to help them obtain an objective estimate of the company's value, particularly in the case of the sale of a business. Analyzing the capital structure and prospects of future earnings is also a part of the daily routine of the professional valuer. The valuer has to obtain certification which requires testing, which can be completed online or in the classroom. The valuation experts are also required to participate in training programs.
• Private Equity Analyst: Private equity analysts have to raise money from private companies, banks, and high net worth individuals. They have to predict the return on investment of a company and also determine its best use. Knowledge of financial accounting is a must for a Private Equity Analyst.
• Corporate Development Analyst: Demonstration of leadership and clear vision in determining & driving strategy decisions. financial modeling, valuation analysis, and merger consequences modeling, preparation of board and other briefing papers on topics relating to M & A, research & analysis of industry trends, capital markets activity, and competitive dynamics. To prepare and deliver presentations to the executives on the board of directors.

While each company has its own strict requirements, here are some valuable skills that all finance modeling job applicants should possess.

• Finance Analytics
• Cash Flow Analysis
• Financial Management
• Financial Planning
• Financial Modeling
• Advanced Excel
• Corporate Finance
• Understanding of financial statements

Even though the basic qualification required to do Financial Modeling is an MBA in the finance domain, just an academic curriculum may not be sufficient enough to understand the nitty-gritties of financial modeling.

An academic qualification may not always be enough to crack the interviews in this sector. Therefore, it is advisable to take up short-term courses for understanding financial modeling. 

COVID-19 has changed the pattern of learning and acquiring knowledge. Many online courses and certification programs are available to improve your skills for doing financial modeling in a more professional manner. Some of the recommended courses are: 

• Building a 3 Statement Financial Model – a certification course offered by Corporate Finance Institute (CFI)
• Reading financial Statements - a free course offered by CFI
• FP&A Monthly Cash Flow Forecast Modeling – a certification course offered by CFI
• Beginner to Pro in Excel:
  • Skillshare's Financial Modeling for Beginners in Excel
  • Simpliv's Quick Excel for Financial Modeling
• Coursera's Finance & Quantitative Modeling for Analysts Specialization
• Valuation with WACC:
  • Skillshare's Introduction to Company Valuation
  • elearnmarkets' Comprehensive Financial Modelling & Valuation

There are various job opportunities available in the market. After doing a course in financial modeling, practicing at regular intervals is also important to get practical experience.