This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Financial Modeling
What are the components of a model? Ben J. Sopranzetti, Ph.D. 1 Parts of a model – getting started Balance Sheet Income Statement Statement of Cash Flows Ben J. Sopranzetti, Ph.D. 2 Two kinds of models Forecasting Model Transaction Model Ben J. Sopranzetti, Ph.D. 3 Forecasting Model Includes key operating assumptions how the firm will grow its revenues and operating cash flows under various scenarios Price increases, growth in unit volume, and cost cuts are key drivers By changing key operating assumptions, we can test for sensitivities Ben J. Sopranzetti, Ph.D. 4 Transaction Model Starts with a forecasting model and then analyzes the impact of a transaction Sample transactions (also called "deals") include initial public offerings, debt refinancings, mergers and acquisitions. What is the impact of different deal structures? Ben J. Sopranzetti, Ph.D. 5 The guts of a model Cover Page Operating Assumptions Working Capital Assumptions Income Statement Balance Sheet Cash Flow Statement Debt Schedule Valuation Analysis Operating Lookup Capital Structure Lookup Ben J. Sopranzetti, Ph.D. 6 Keep things clean and tidy
Remember, you might have to revisit the model six months from now. Ben J. Sopranzetti, Ph.D. 7 The Cover Page Summarizes model’s key information in one place Ben J. Sopranzetti, Ph.D. 8 What are the key components? Sources and Uses Table Summary Valuation Summary Financial Results Ben J. Sopranzetti, Ph.D. 9 Sources and Uses Table Demonstrates from where a business plans to get financing (the "Sources") and what it plans to do with the it (the "Uses"). Ben J. Sopranzetti, Ph.D. 10 Summary Valuation What is the value of the business And… How much of that value belongs to shareholders. Ben J. Sopranzetti, Ph.D. 11 Summary Financial Results Summarizes the projected revenue and cash flow growth of the company Provides a comparison with historical results. Also… provides information on the company's creditworthiness. Ben J. Sopranzetti, Ph.D. 12 Operating Assumptions Garbage in → Garbage out Your output is only as good as the quality of your assumptions What are the critical assumptions? – Sales, profit margin, growth, etc… What are the engines driving cash flows?
Ben J. Sopranzetti, Ph.D. 13 Operating Assumptions Historical results are often used as a starting point for a forecast. Economies (diseconomies) of scale Are historical values good predictors of the future? Use your common sense.
Ben J. Sopranzetti, Ph.D. 14 Working Capital Page Forecast working capital items such as inventory, accounts payable, and accounts receivable. Would you expect this to change with sales? Ben J. Sopranzetti, Ph.D. 15 The Income Statement P&L (profit and loss) statement, shows all the revenue and expenses of the business. Did the firm make any money? The Operating Assumptions drive the Income Statement Ben J. Sopranzetti, Ph.D. 16 The Balance Sheet What do we have and how did we pay for it? It tells us how much "stuff" we have (Assets) and how much of the "stuff" is owed to other parties (Liabilities). The remainder belongs to the equity holders. Driven in part by the working capital page
Ben J. Sopranzetti, Ph.D. 17 Statement of Cash Flows It starts with the accountant's record of profits from the Income Statement and reconciles to the bottom line change in Debt and Cash Ties together the Income Statement and Balance Sheet Ben J. Sopranzetti, Ph.D. 18 The Debt Schedule The business generates a certain amount of cash. How much of the cash should be used to repay debt? How much should be put in the bank? Ben J. Sopranzetti, Ph.D. 19 Valuation Analysis Drives the Summary Valuation results on the Cover Page The Valuation Analysis calculates the cash flow and determines a business value based on these cash flow forecasts. Usually this is a indirect DCF (unlevered free cash flow) analysis Ben J. Sopranzetti, Ph.D. 20 Operating Lookup This section of a financial model allows a user the flexibility to plan for and run various scenarios by simply changing a value in one cell. This value is called a "trigger“ Vary the revenue growth and profitability (COGS) and CAPEX forecasts
Ben J. Sopranzetti, Ph.D. 21 Capital Structure Lookup Just like the Operating Lookup allows the user to vary the revenue and profit forecasts The Capital Structure Lookup creates the capability to analyze the impact of various financing alternatives Ben J. Sopranzetti, Ph.D. 22 ...
View Full Document
- Fall '08