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 companys 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 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 companys ultimate valuation. The end goal of this modeling process will be to value the per-share offering price of the companys 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 models 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 models 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 firms 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 companys 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 companys 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 companys management. Crucial pieces of information are the companys 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 models inputs. Assumptions are based on the companys 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 models 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 Excels numerous keyboard shortcuts. Sometimes it takes years of Excel modeling to become truly proficient at this task.
