Building a 3 Statement Financial Model in Excel

1 Building a 3 Statement Financial Modelin is a Financial Model ? A Financial Model is a tool used to forecast a business Financial performance into the future based on historical data and do we build Financial models?For anyone pursuing a career in corporate development, investment banking, FP&A, equity research, commercial banking, or other areas of corporate finance, Building Financial models is part of the daily DecisionsCompany performance, strategic planningProject FinanceWhether to invest in a projectCorporate TransactionsMergers & acquisitions, capital raisingInvestment DecisionsValuation, equity research, portfolio of Financial modelsFinancial ModelsThree Statement ModelDCF ModelMerger Model (M&A)Initial Public Offering (IPO) ModelLeveraged Buyout (LBO) ModelSum of the Parts ModelBudget ModelForecasting ModelOption Pricing ModelConsolidation of Financial modelingThree Statement ModelDCF AnalysisScenario AnalysisSensitivity AnalysisM&A AnalysisLBO AnalysisCapital RaisingIncome Statement , balance sheet, cash flow statementDiscounted cash flow analysis to value a businessEstimate changes in the value of a business in different possible scenariosEvaluate how sensitive an investment is to changes in driversEvaluate the attractiveness of potential merger, acquisition or divestitureAnalyze the pro forma impact of raising debt or equityDetermine how much leverage can be used to purchase a Modeling Best structure for Model buildingGood models clearly separate inputs, processing, and Clearly identified Should only ever be entered once Processing should be transparent Broken down into simple steps Easy to follow Quickly best practices1.

2 Clarify What problem is the Model meant to solve? Who is the end user? What are users supposed to do with the Model ? best practices1. Clarify2. Simplify What is the minimum number of inputs and outputs to build a useful Model ? best practices1. Clarify2. Simplify3. Plan Plan how inputs and outputs will be laid out Keep all inputs in one best practices1. Clarify2. Simplify3. Plan4. Integrity Consider using Excel tools such as: Data validation and Conditional formatting best practices1. Clarify2. Simplify3. Plan4. Integrity5. Model Testing Use test data to ensure the Model works as best practices1. Clarify2. Simplify3. Plan4. Integrity5. Model : complex vs. simple modelsComplex ModelsSimpleModels High detail Precise Hard to Model Prone to error Basic Easy to follow Lack of precision Overly simplifiedBest ModelsKeep things as simple as possible while providing enough detail for decision inputsInputsObjectivesAchieving objectives Accurate Reasonable data ranges Easy to use Easy to understand Easy to update data Enter each data once Use colorto differentiate inputs and outputs Use data validation & conditional formatting Use processingInputsProcessingOutputsDo you try to put all your processing calculations into as few cells as possible?

3 Do you hide your processing cells or worksheets? processingProcessingObjectivesAchieving objectives Easy to maintain Accurate processing Transparency Break down complex calculations Use comments and annotations Use formatting Calculate final figures which will go onto the output outputsOutputsObjectivesAchieving objectives Provide key results to aid decision-making Easy to understand Unambiguous Make outputs modular Consider creating a summary section with only the most important key Model structure and layoutSingle forecasting frameworkAssumptions & driversIncome statementBalance sheetCash flow statementSupporting schedulesHistorical ratios and figures which drive the forecastSummarizes the company s profit and lossDisplays the company s assets, liabilities and shareholders equityReports the cash generated and spent by a companyBreaks down longer calculations such as PP&E and debt forecasting approachHistoricalForecastAssumptions & driversIncome statementBalance sheetCash flow statementSupporting modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers1 Historical modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers2 Assumptions and drivers1 Historical modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA2 Assumptions and drivers1 Historical modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA4 Forecast working capital2

4 Assumptions and drivers1 Historical modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA4 Forecast working capital5 Forecast capital assets (PP&E, capex, depreciation, etc.)2 Assumptions and drivers1 Historical modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA4 Forecast working capital5 Forecast capital assets (PP&E, capex, depreciation, etc.)6 Forecast capital structure2 Assumptions and drivers1 Historical modeling stepsIncome StatementBalance SheetCash Flow Statement3 Forecast revenues down to EBITDA4 Forecast working capital5 Forecast capital assets (PP&E, capex, depreciation, etc.)6 Forecast capital structureSupporting SchedulesComplete cash flow statement72 Assumptions and driversAssumptions and Drivers1 Historical Setup and caseYour boss has just emailed you about about something the executive team would like to look at ASAPYou need to create a Financial forecast for a business, with limited informationYou only have a set of historical Financial statements and some guidance from the company s management team, as well as a template Model from a colleagueYou must link the historical Financial statements and create a well built 5-year forecast as fast as dataIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers1 Historical , drivers and forecasting methodsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers2 Assumptions and drivers1 Historical methodsTop-Down Analysis Start with total addressable market (TAM)

5 Work down from there based on market share and segments until arriving at revenueBottom-Up Analysis Start with most basic drivers of the business (units) Build up the analysis all the way to revenueRegression Analysis Analyze the relationship between revenue and other factors using the regression analysis in ExcelYear-over-Year Growth Rate Most basic form of forecasting Calculate the year-over-year change in Revenues Down To modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA2 Assumptions and drivers1 Historical operating revenues and profitsIncome Statement Revenues Direct operating cost Indirect operating cost Depreciation and amortization Cost of debt TaxesEBITDANet revenuesComplexModelsSimpleModelsQuick and simple Use historic figures and trends to predict future growth ( last year plus 5%)First principles Retail (bottom up) forecast # of stores, size, and derive revenue per sq.

6 Ft. Telco(top down) Forecast market size and use current market share and competitor analysis to predict revenue gross margin and SG&A expensesRevenue100%Cost of goods sold80%Gross margin20%SG&A5%Operating margin15%Use historic figures or trends to forecast future marginsGross margin20%Revenue100%Cost of goods sold(80%)Gross margin20% gross margin and SG&A expensesLabor + materials + Inflation %Consider factors such as economies of scale and learning effectsComplex ModelsSimpleModels Based on a margin Easy to modelCost of goods sold80% Based on inputs Per gross margin and SG&A expensesRevenue100%Cost of goods sold80%Gross margin20%SG&A(5%)Operating margins15%Indirect5% or $xxForecasted as a percentage of revenue or as a fixed cost (plus an inflator)Often includes marketing, sales, general and administrative Working Capital and PP& modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA4 Forecast working capital2 Assumptions and drivers1 Historical data Accounts receivable Inventories Accounts Financial statementsHaving forecast the revenues and costs of an operation, the next step is to considerthe working capital required to generate & Shareholders EquityCurrent assetsCurrent liabilitiesCashAccounts payableAccounts receivableOther current liabilitiesInventoryLong term liabilitiesNon-current assetsShareholders equityOperating (non-current)

7 AssetsCommon sharesRetained earningsAccounts receivableInventoryAccounts payableOther current complexityLow complexityForecasting working capitalModerate approach Quick and simple approach Receivable days Inventory days Payable days Historical trends A % of sales based on trendsDetailed approach Account/client detail Inventory management capital equationsReceivable daysPayable daysInventory capital equationsAccounts receivable days=Forecast accounts receivable=Receivable daysAccounts receivableSalesX365 Receivable days365 XSalesPayable daysInventory daysPayable daysInventory capital equationsReceivable daysPayable daysInventory daysReceivable daysInventory daysAccounts payable days=Forecast accounts payable=Accounts payableCost of salesX365 Payable days365 XCost of capital equationsReceivable daysPayable daysInventory daysReceivable daysPayable daysInventory days=Forecast inventory=InventoryCost of salesX365 Inventory days365 XCost of modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA4 Forecast working capital5 Forecast non-current capital assets2 Assumptions and drivers1 Historical data PP&E Capex Depreciation IntangiblesAssetsLiabilities & Shareholders EquityCurrent assetsCurrent liabilitiesCashAccounts payableAccounts receivableOther current liabilitiesInventoryLong term liabilitiesNon-current assetsShareholders equityOperating (non-current) assetsCommon sharesRetained Financial statementsOperating (non-current) assets / PP& property, plant and equipment (PP&E)First principles approach Forecast property, plant, and equipment requirement directly ( store expansion) Forecast depreciation/amortization based on stated depreciation/amortization policies.

8 If deprecation policies are not available, divide gross assets by the depreciation expense to get average asset life. Quick and simple approach Forecast depreciation & amortization as a percentage of opening PP&E balance or percentage of revenue Forecast PP&E balance based on a capital asset turnover ratioHigh complexityLow property, plant and equipment (PP&E)Capital Asset Turnover RatioSales / PP&E (end of period)or Sales / PP&E (average) Capital modeling stepsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and Drivers3 Forecast revenues down to EBITDA4 Forecast working capital5 Forecast capital assets (PP&E, capex, depreciation, etc.)6 Forecast capital structure2 Assumptions and drivers1 Historical Financial statementsThe financing structure affects both the balance sheet and the income Statement ( interest)AssetsLiabilities & Shareholders EquityCurrent assetsCurrent liabilitiesCashAccounts payableAccounts receivableIncome taxes payableInventoryNon-current liabilitiesLong term debtNon-current assetsShareholders equityOperating (non-current) assetsCommon sharesRetained earningsCashLong term debtCommon sharesRetained Financial statementsOther Current LiabilitiesLong term LiabilitiesCommon SharesRetained to modeling capital structure (debt/equity)Do we want to Model the status quo, or do we want to Model a different capital structure?

9 ?Debt & Equity $ Values Held ConstantDebt/Equity x Ratio Held ConstantDebt/Equity Change Over Time Based on Cash the capital structureWhat should be the split between equity and debt financing?Leverage ratiosDebtEquityCoverage ratiosEBITI nterest Expense Financial covenants dictate maximum leverage and minimum coverage Consider management s willingness to take on debt Use the company s current access to the debt and equity capital vs non compoundinginterestIs the debt (and interest expense) compounding or not??IF YEST here will be a circular reference in the modelIF NOThere will not be a circular reference in the referencesOpening Debt Balance+ Interest= Closing Debt Balance+/- Cash Flow modeling stepsIncome StatementBalance SheetCash Flow Statement3 Forecast revenues down to EBITDA4 Forecast working capital5 Forecast capital assets (PP&E, capex, depreciation, etc.)6 Forecast capital structureSupporting SchedulesComplete cash flow statement72 Assumptions and driversAssumptions and Drivers1 Historical Financial statementsOperating activities ( revenues, operating expenses)Investing activities ( sale/purchase of assets)Financing activities ( issuing shares, raising debt)Net Cash MovementA cash flow forecast can be derived from the balance sheet and income flows from operating activitiesNet income100 Depreciation20 Other non-cash items-Trade and other receivables(10)Inventory(20)Trade and other payables4515 Cash from operating activities135 From incomestatementFrom balancesheetChanges in operating assets and flows from investing activitiesCapital expenditures (additions to PP&E)(120)Proceeds from disposals of fixed assets10 Payments for investment in businesses0 Proceeds from disposals of businesses0 From specific fixed asset forecastsCash from investing activities(110)

10 Flows from financing activitiesIssuance of common stock100 Dividends paid in the year(5)Increase/(decrease) in long-term debt15 Increase/(decrease) in short-term debt(10)From balance sheet and supporting schedulesCash from financing and techniquesError Messages and AlertsSanity Checks (Assumptions and Drivers)Trace Precedents and DependentsExcel SettingsGoToSpecialView thoughts A Financial Model is a toolthatrelies on a set of modular approach to Building modelsIncome StatementBalance SheetCash Flow StatementSupporting SchedulesAssumptions and DriversDiscounted Cash Flow (DCF) AnalysisSensitivity AnalysisLBO or M& models, sensitivity, M&A, LBO, and moreThree Statement ModelDCF AnalysisScenario AnalysisSensitivity AnalysisM&A AnalysisLBO AnalysisCapital RaisingBusi