Financial modeling is one of the most powerful skills of modern finance. It is used extensively by investment bankers and other finance professionals. Financial modeling is a highly useful skill for professionals and students involved in the CFA program as well as for holders of other finance and accounting designations - CPA, CA, ACA, CMA and CGA.
Financial modeling in Excel is one of the most versatile and powerful finance skills today. This skill is often a sought-after add-on to well-known financial designations such as CFA, CPA, CA, CMA and CGA. In a nutshell financial modeling is a process of building a multi-year forecast of a company's financial statements: income statement, balance sheet and statement of cash flows. The projected time period varies from one model to the next, the norm being 5 to 10 years.
Why is financial modeling so important? It is used in a variety of finance applications such as investment banking - initial public offerings (IPO), secondary financings, mergers and acquisitions (M&A); corporate banking; private equity; venture capital; equity research; corporate strategic planning and budgeting; and numerous other important applications. Below are just a few financial modeling application examples:
An investment banker builds a financial model of a mobile telephony software company that is going through an IPO process. The main outputs of the model will be metrics used in valuation: unlevered free cash flows (UFCF), earnings and net debt calculations. The financial model will be used in discounted cash flow (DCF) valuation. DCF, together with comparable trading and transactions valuation will be used in the company's ultimate valuation. The end goal of this modeling process will be to value the per-share offering price of the company's shares once they are listed on the stock exchange.
A credit-focused financial model is being built by the commercial lending unit of a major bank. This is a part of processing a large commercial loan application filed by a manufacturing company which is looking to expand its operations. The model's emphasis is on the debt servicing ability of the company in question. The most important outputs that the commercial bankers will look at are debt to equity ratio, interest coverage and fixed charge coverage ratios.
An equity analyst builds a financial model of a company that his firm decided to initiate coverage on. The focus of the model is on DCF valuation and unlevered free cash flows generated by the company. Based on the model's results the analyst will issue buy/sell/hold recommendations on the stock based on the relationship of his target stock price and the current market stock price.
A private equity firm is considering a 50% acquisition of an early stage pharmaceutical company that needs capital for sustaining its research and development (R&D) program. The private equity firm sees value and significant upside in this situation given the target firm's pending patent applications. The purpose for building the financial model is to determine the price at which the private equity firm is willing to purchase the 50% stake, given the hurdle IRR (internal rate of return) rate of 35%.
A pulp and paper company's CFO prepares a detailed multi-year budget of the company. She uses Excel financial modeling techniques to achieve her goal. The model will contain a 5-year projection of the company's income statement, balance sheet and cash flow statement and help the company assess future financing, staffing and operational needs. The multi-year budget will be submitted to the company CEO for review.
The financial modeling process is as much an art as it is a science. Solid financial modeling training through seminars and courses is a must for people seeking careers in many finance areas. These skills are further honed and advanced through the real-life work experience of building financial models.
The financial modeling process begins with gathering information. The analyst must become intimately familiar with the company he models, its industry and competitive landscape, its plans and prospects, and the strength of the company's management. Crucial pieces of information are the company's past financial reports, management interviews, conference call transcripts, research analyst reports, and industry publications. It must be noted that this information gathering exercise is much more challenging when modeling a private company as opposed to a public company. Private company information can often only be obtained through direct access to the company insiders.
An typical Excel financial model will consist of the following parts:
Assumptions. These are the model's inputs. Assumptions are based on the company's historical information as well as its future plans and current market trends.
Historical and projected financial statements - income statement, balance sheet, cash flow statement. Projections are based on historical performance and model assumptions.
Supporting schedules including working capital schedule, capital expenditures (CAPEX) schedule, debt schedule, and tax schedule.
The model's outputs depend on the primary purpose for building the model. In many cases modellers focus on earnings, unlevered free cash flows, capital structure and debt capacity.
Scenario and sensitivity analyses are often incorporated into the models, including scenario managers, data tables and charts.
Financial models often serve as foundation for more detailed further analysis such as valuation, M&A merger modelling (accretion/dilution analysis), LBO analysis and Monte Carlo simulations.
So what does it take to be a good financial modeller? Accounting and finance knowledge is compulsory. In-depth understanding of financial statements and relationships between line items of the income statement, balance sheet and the cash flow statement is an absolute must. Microsoft Excel proficiency is another prerequisite. A good modeller not only knows Excel functions, tools and formats, but also is quick and efficient in using Excel's numerous keyboard shortcuts. Sometimes it takes years of Excel modeling to become truly proficient at this task.
Financial Modelling Group is an international firm specializing in finance training. Our acclaimed financial modeling and valuation seminars helped numerous current and aspiring professionals in corporate finance, M&A, private equity, portfolio management, strategy consulting, and other industries achieve their professional goals. Young professional involved in the CFA program benefited from our financial modeling courses, alongside professional accountants holding CPA, CA, ACA, CMA and CGA designations. Visit our web site at www.finmodelling.com to learn more about our firm.
Alan Augustinovich, CFA
Partner, Financial Modelling Group.
Corporate valuations - Introduction.
The purpose of corporate valuation is to assign value to companies or their distinctive parts, e.g. divisions. Traditionally valuations were mostly performed by investment bankers in M&A and IPO transactions. In recent years the applicability of valuation assignments has been expanding. Part of the reason is the adoption of International Financial Reporting Standards ("IFRS") around the world. IFRS requires preparation of valuation analyses for asset and goodwill impairment. Valuation work is also used extensively in selling and buying businesses, capital raising, investment decisions and litigation support. Specialized skills are required to be able to perform valuation work. Financial Modelling Group teaches valuable financial modelling and valuation skills (www.finmodelling.com).
Corporate valuations were traditionally the domain of investment bankers. Establishing a fair value of a business is at the heart of advice given to clients in mergers and acquisition transactions ("M&A"). Since each side in the M&A transaction would have an investment banking advisor, the final transaction price was determined through negotiations for which the starting point was the independent valuation work performed by both advisors.
In recent years the role of valuation assignments has been increasing. Valuation work is performed for corporate clients for a variety of purposes:
§ Formal valuation and fairness opinions (reports assessing what is the fair value of a given company or division)
§ Analysis for the merger and acquisition transactions (one of the key outputs of the financial model is the value per share which can be paid for a target company)
§ Compliance valuations (as required by regulators)
§ Investment analysis for the venture capital and private equity investors (valuation analysis allows to determine if a given investment provides a sufficient rate of return)
§ Restructuring valuation analyses (the creditors of a financially struggling company determine their consent to the restructuring measures based on specialized financial analysis)
§ Reports required by the IFRS:
§ intangible asset valuations and purchase price allocations
§ goodwill impairment analyses (output of the test determines if no write down is necessary)
§ Reports supporting litigation and dispute resolution
§ Analysis prepared for the Court opinions and expert testimony
To be able to perform valuation of a given company a number of criteria has to be met. Valuation professional needs to:
Have a solid understanding of the business of the company. This includes understanding of:
The markets and their major trends, the competition, the main customers
Competitive advantages, the management experience, technology used, existence of patents, trademarks etc.
Economic factors affecting the main markets of the Company products. At the very least projected GDP levels and projected demand for the main products and services delivered by the Company need to be considered.
Possess sophisticated theoretical valuation and financial modelling knowledge. Main items of consideration are listed below:
The appropriate discount rate (in particular what is the appropriate "Market Premium" for a given market)
Use of the Discounted Cash Flow ("DCF") analysis versus the Comparable Transaction and Comparable Trading analyses
§ Despite of all its shortcomings DCF analysis remains the basic valuation tool
§ Typically at least 2 valuation methods are used, although when the stock markets are depressed (e.g. fall 2008) the Comparable Trading methodology can produce misleading results
o Financial model of the company needs to be built. Typically 5 to 10 year forecast period is used. The longer the forecast period the more meaningful discussion can take place regarding EBIT, EBITDA and Net Income margins as well as the year to year changes in revenue and cost structure. Longer forecast period also translates into less weight assigned to the Terminal Value. On the other hand longer forecast period translates into more uncertainty regarding the figures.
o Financial modelling expertise. Comprehensive, dynamic, multi year financial models of companies are among most complex outputs in finance. Often financial models encompass hundreds of thousands of numbers and formulas and they test the limits of excel. Solid financial modelling skills are essential to be successful at valuation assignments. If you are interested in financial modelling and corporate valuations it is a good idea to take a financial modelling and valuation course.
Financial Modeling Group - www.finmodelling.com - teaches valuable financial modelling and valuation skills. We offer public and in house seminars as well as practical self-study courses which allow participants to learn career enhancing finance skills at their own pace. Finance professionals (CFA, CPA, CA, CMA and CGA) and business students have benefited from our seminars. Our most popular 2 day financial modelling and valuation seminar provides 14 hours of verifiable learning for continuing education purposes of professional accountants. For some students the knowledge of financial modelling and valuation concepts gained at our seminars has translated into securing a dream investment banking job.
Financial Modelling Fundamentals.
Financial modelling is a versatile financial discipline. Financial models are ubiquitous in the modern financial marketplace, and are used in multiple Investment Banking, Corporate Finance, Private Equity, Budgeting and other applications. Financial Modelling Group is a world-wide provider of financial modelling and valuation training.
Many outsiders often ask a question: what constitutes a financial model? The answer is not as simple as some may imagine. Because of the variety of intended uses, the definition of a financial model can only be a rather broad one. Simply put, a financial model is a spreadsheet (most commonly in Excel) created for the purpose of financial analysis of companies, projects, portfolios and other subjects. Financial models are used in Investment Banking and Corporate Finance fields, as well as Commercial Banking, Portfolio Management and Venture Capital / Private Equity applications. Different types of financial models exist:
Risk analysis models - used to analyze different types of risk
Trading models - used in portfolio management and sales/trading functions
Portfolio allocation models - determine asset type and other allocations within a portfolio.
But the most commonly used type of a financial model, and the core of the Financial Modelling Group's courses, is the financial statements projection model. Financial Modelling Group's flagship Financial Modelling in Excel and Valuation course focuses specifically on building a financial statement forecast model and later derive at a company's valuation using the model's outputs. Financial statements projection model forecasts the company's future financial results and consists of:
Cash Flow Statement
Supporting schedules - CAPEX Schedule, Debt Schedule, Working Capital and other schedules.
The financial projections model is an essential building block for valuation and investment decision making analysis. Subsequent valuation models such as the Discounted Cash Flow (DCF) models, Comparable Trading and Comparable Transaction analysis models, LBO (Leveraged Buyout) models, and Mergers and Acquisitions (M&A) models build on the financial statements projection model.
The level of detail of a given model depends on its intended use. For example, if the model's purpose is to analyze your company's tax situation, then building a detailed tax schedule into your model will suit that purpose. In another example, if your company has a complicated debt structure with layers of senior and junior debt, adding a complex debt schedule outlining all debt segments and determining repayment order will be warranted. Finally, if you want to analyze the CAPEX program of your company , you need to build a more comprehensive CAPEX schedule to analyze different CAPEX inputs and their dynamics over time.
When building a financial statements projection model an analyst creates financial statements of a company that reflect its historical financial performance (usually 1-3 years), and forecasts the company's financial performance over a certain period of time (usually 3 to 10 years). The forecast period can be monthly, quarterly or yearly depending on the requirements. The modeller focuses on the three main financial statements: Income Statement, Balance Sheet and the Statement of Cash Flows.
The Income Statement would typically have the following line items: Sales Revenue; Cost of Goods Sold (COGS); Sales, General and Administrative Expenses (SG&A); Research and Development Expenses (R&D); Earnings Before Interest, Taxes, Depreciation and Amortization (EBITDA); Depreciation and Amortization Expense (D&A); Interest Expense; Earnings Before Taxes (EBT); Income Tax Expense; Net Profit (Net Income).
The Balance Sheet in the financial modelling context will consist of the following line items: Current Items (Cash, Investments, Accounts Receivable, Deferred Taxes, Prepaid Expenses, Inventory); Fixed Assets - mainly Property, Plant and Equipment (PP&E) net of Accumulated Depreciation; Current Liabilities (Short-term Borrowings, Current Portion of Long-term Debt, Accounts Payable, Accrued Expenses); Long-term Liabilities - mostly Long-term Debt and Pensions; Shareholders' Equity - typically consisting of Common Stock, Treasury Stock and Retained Earnings.
The Cash Flow Statement acts as an indicator of sources and uses of cash. In a typical model it consists of the three main parts: Cash Flows from Operating Activities, Cash Flows From Financing Activities and Cash Flows From Investing Activities. Every year-to-year change in the model's Balance Sheet is reflected on the Cash Flow Statement.
Financial Modelling Group Inc. is an international financial training company. Its courses are highly acclaimed by holders of various financial designations such as CFA, CPA, ACA, CMA and others. The company's offices are located in Toronto, New York and London. Please visit our web site www.finmodelling.com for an upcoming financial modelling and valuation seminars near you.
Alan Augustinovich, CFA
Partner, Financial Modelling Group.
Discounted Cash Flow Analysis Fundamentals.
Discounted Cash Flow ("DCF") Analysis is one of the most popular valuation methodologies. Its advantages include ability to customize the financial model to a given situation, to specifically account for a level of risk in a given country and industry (through the discount rate wacc) and to run sensitivity analysis which makes the valuation discussion more meaningful. One of the key advantages is the almost total independence of the DCF method of the prevailing capital markets conditions. The main weakness of the DCF Analysis is its high sensitivity to the chosen assumptions (e.g. growth of the Free Cash Flow in the Terminal Year), as well as the fact that a detailed financial model needs to be built which is effort intensive.
Financial Modelling Group teaches valuable, versatile and career enhancing financial modelling and valuation seminars in all major financial centres around the world (www.finmodelling.com).
DCF Analysis is one of the most popular valuation methodologies in use today.
DCF Analysis offers several advantages over other valuation methodologies:
Advantage nr. 1: Ability to customize the financial model which is the basis for the valuation to any given business situation.
DCF Analysis is most universal and can be used to value any business, provided a reasonable financial forecast can be created.
Knowledge of financial modelling techniques allows to customise the financial model for any business venture and business development scenario. As such, using the DCF Analysis we can value any business no matter how unique the business model is. This is in sharp contrast to the Comparable Trading Analysis which can be used to value a given business only if comparable public companies do exist and if financial forecasts are available for these comparable companies.
Advantage nr. 2: Ability to select an appropriate discount rate wacc which specifically addresses a unique circumstances of a given capital market
Since the applicable discount rate wacc is individually calculated for each DCF valuation analysis, a high level of flexibility exists to capture the reality of a given capital market. Wacc calculation includes the following main factors:
a) risk free rate of interest of a given capital market (e.g. T-Bills in the USA)
b) risk premium commended by equity investors over risk free investments in a given capital market
c) Beta of a given business (Beta measures how sensitive the equity return of a given company is in relation to the market return (e.g. return of S&P 500 index) Beta = 0 means that the returns are not correlated at all (Beta can be negative) Beta = 1 means that the return of a company is 100% correlated with the return of the market.
Advantage nr. 3: Ability to run sensitivity analysis
Sensitivity analysis is the analysis which tests how sensitive the outcome of the valuation is to the changing values of the key inputs and assmptions. Since valuation is more of an art than science, sensitivity analysis is highly useful in negotiating and agreeing what the value of a given company is, as it allows to position the valuation as a range of values, rather than a single value.
Most popular inputs which are being tested in the sensitivity analysis for a company being valued include:
Growth rate of revenues, EBITDA, EBIT and Net Income margins, Level of corporate synergies (esp. In M&A transactions), Working capital assumptions, CAPEX levels, Obtaining new debt, paying faster existing debt, Obtaining funds through an IPO or an SPO, Wacc inputs (risk free rate, risk premium, Beta), The growth rate g of Free Cash Flow after the Terminal year.
Advantage nr. 4: Ability to perform valuation analysis which is almost totally independent from the prevailing capital market conditions
Since DCF Analysis considers business model of a company being valued in great detail and only based on specific business metrics, it is possible to capture the "true" value of a company being valued with reference only to its specific situation and without regard for the overall capital market conditions.
This is very important as sometimes the capital markets are influenced heavily by prevailing macroeconomic conditions, e.g. during recession years valuation of companies fall in general. This way some good companies are "punished" by the markets without good reason. This is one of the most important limitations of Comparable Transaction methodology.
Main disadvantages of the DCF Analysis include:
Disadventage nr. 1: High dependence on the chosen assumptions
Outcome of the DCF Analysis is highly dependent on several key assumptions. Some of the most important assumptions where judgement is exercised include the wacc components, the Free Cash Flow growth rate "g" after the Terminal Year, the revenue growth rate, the EBIT, EBITDA and Net Income margin assumptions and the Working Capital assumptions. This limitation can be addressed by running sensitivity analysis.
Disadventage nr. 2: The necessity to prepare a financial model
Unfortunately DCF Analysis requires preparation of a comprehensive financial model of a company being valued. It is an intensive and labour consuming process which requires a considerable technical expertise.
In summary it seems that advantages of the DCF methodology outweigh the disadvantages, and DCF Analysis will remain in the foreseeable future one of the most important and universal company valuation methodologies.
DCF Analysis is extensively used for:
Company valuations for the Merger and Acquisition transactions
Investment analysis for the venture capital and private equity investors
Restructuring valuation analyses
Specialized reports required by the IFRS (intangible asset valuations and purchase price allocations; goodwill impairment analysis)
Reports supporting litigation and dispute resolution (e.g. income loss reports)
Analysis prepared for the legal opinions and expert testimony used in the court trails
Financial Modelling Group Inc. ("FMG") is an international financial training company. FMG teaches valuable and versatile financial modelling and company valuation skills. We offer public and in house seminars as well as practical self-study courses which allow participants to learn career enhancing finance skills at their own pace. Finance professionals (CFA, CPA, CA, CMA and CGA) and business students have benefited from our seminars. The FMG's offices are located in Toronto, New York and London. Please visit our web site www.finmodelling.com for an upcoming financial modelling and valuation seminars near you.
Our most popular 2 day financial modelling and valuation seminar provides 13 hours of verifiable learning for continuing education purposes for professional accountants.
Gathering historical information is an important starting point in the financial modelling process. Public companies disclose their financial information in their annual and quarterly reports. Conference call scripts, media articles as well as analyst research reports also provide useful insight into the company's financial health and current trends.
Every financial modelling exercise begins with the first important step - gathering historical financial information about the company that is being modelled. This is very important since you need to get a financial insight into the company quickly and effectively. As a rule, much more information is available on public companies than on private ones. A typical list of information that can be usually obtained on a public company includes the company's latest annual report (10K in USA), quarterly report (10Q in the USA), other relevant securities regulator filings (SEC in the USA), latest applicable company press releases and conference call transcripts, any available analyst research reports, EPS consensus estimates, industry publications, newspaper, magazine and industry journal articles.
Where to find public company filings? Thankfully, this task is among the easiest in the historical information gathering process. Public company filings are available free of charge on internet accessible databases such as www.sec.gov (USA) or www.sedar.com (Canada). Company web site is an great starting point when searching for all kinds of information about a particular company. Company competitors' and industry web sites should also be researched for useful information about the industry in which the company operates. Company and industry data is also available from paid-for subscription services such as FactSet, Edgar, Bloomberg, Capital IQ and others.
What does a company's annual report (10K) consist of? Publicly traded companies are required to submit periodic filings to the capital markets authorities, e.g. Securities and Exchange Commission (SEC) in USA. Company annual results are filled in the form called 10K. 10K includes the following key information: Balance sheet, Income statement, Statement of cash flows, Notes to financial statements, Management's discussion and analysis (MD&A), Additional important financial information. 10K's are filed within 75 days after the end of the fiscal year, 60 days if the company is large (market capitalization > $700m).
The latest quarterly report (or 10K in the USA) is needed only if one exists subsequent to the latest annual report. 10Q's are due 40 days after the close of the fiscal quarter. 10Q's include financial statements and other data. 10K's are much more detailed reports that 10Q's, they contain much more specific financial, operating and industry information. 10Q's do not provide detailed footnotes, stock options and debt schedules that 10K's provide. 10K's provide a more extensive Management Discussion and Analysis (MD&A) section with insightful management analysis that sometimes may provide guidance into the future. 10K's are audited by an independent auditor, while 10Q's are not. This can result in less reliable information contained in the 10Q's.
Although the 10K and the 10Q are by far the most important SEC filed documents, other forms sometimes deserve a look.
8K - reporting of materially significant event for a company (acquisition, merger, divestiture, bankruptcy, etc.).
S1 and S2 - filed by a private company that intends to go public.
S3 - filed to register of stock or debt securities by public companies (over a year subsequent to IPO or debt placement).
S4 - when two existing public companies merge and new securities are issued.
3, 4, 5, 13D, 13G - forms that disclose buying and selling of securities held by company insiders or large shareholders (>5%).
Press releases and conference calls can provide useful insight into the company's financial affairs. Companies usually issue press releases ahead of official filings (such as 10K and 10Q). In these press releases they disclose some financial and operating information that will be later found in the official filings. Although press releases are much less detailed than the 10K and 10Q, they are still useful in providing guidance on certain topics. Conference calls are often held by the announcing company in conjunction with the press release. During the conference call, management usually provides insights into the company's performance as well as some forward guidance. Press releases can be obtained from the reporting company's web site, from the filed 8K forms and from free and subscription-based financial news services (Yahoo finance, Reuters, Bloomberg).
Finally, analysts' equity research reports provide a "second pair of eyes" view on the company. These reports are often unavailable to the general public. They are an inside product of investment banking firms' equity research departments and are usually reserved for the firms' sales and trading department clients. However, if you can get your hands on an analyst report study it in detail to get the "zoomed-in" view of the company. Research analyst often cover companies in one single industry, they know the companies much better than you and therefore their product is highly valuable to any financial modeller. Equity research analysts usually focus on certain companies and industries and are in business of providing regular research product to their firms' clients. A typical research report would incorporate the analyst's own financial model of the company, along with financial projections based on management's guidance and analyst's own opinion, analysis and foresight. Equity research reports provide analytical information about the company's historical results, and important issues faced by the company or industry. These reports are used frequently by investment bankers and others to get to know the industry and the company quickly and effectively. Equity research reports are difficult to obtain as research houses protect their proprietary product. Financial information agencies such as Bloomberg and Thomson provide equity research reports on a subscription basis.
Financial Modelling Group Inc. is an international financial training company. Our courses are highly acclaimed by our numerous students worldwide, including holders of various financial designations such as CFA, CPA, ACA, CMA and others. The company's offices are located in Toronto, New York and London. Please visit our web site www.finmodelling.com for an upcoming financial modelling and valuation seminars near you.