1 / 28

Financial Statement Modeling & Spreadsheet Engineering

Financial Statement Modeling & Spreadsheet Engineering. “Training in spreadsheet modeling improves both the efficiency and effectiveness with which analysts use spreadsheets” Steve Powell, Dartmouth College. Understand the Decision. What is the problem? What questions must be answered?

pascha
Download Presentation

Financial Statement Modeling & Spreadsheet Engineering

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Financial Statement Modeling & Spreadsheet Engineering “Training in spreadsheet modeling improves both the efficiency and effectiveness with which analysts use spreadsheets” Steve Powell, Dartmouth College

  2. Understand the Decision • What is the problem? • What questions must be answered? • What is the time horizon of the problem? • What kind of output information is needed? • Who will use the information? • What level of detail is necessary?

  3. Structural Inputs of the Model • Define the equations and relationships that make up the model • What are the decision variables and what are the linkages between the decisions and the goal of the model? • What are the constraints? • Definitions based on accounting rules • Economic, industry, resource and technological limits • What happens at the end of the planning horizon?

  4. Data Inputs • Internal data that describes the current state of the system • Firm’s financial statements • Resources and capacities • External data that describes the firm’s operating environment • Economic conditions such as interest rate, costs, stock prices

  5. Financial Analysis • Assess the strengths and weaknesses of the firm’s current condition • Ratio analysis • Break-even analysis • Operating and financial leverage analysis • Generate pro forma financial statements to identify strategies to improve the condition and assess future risks

  6. Growth and the Need for Financing • Growth frequently associated with cash • Faster growth  more profits  more cash • This is incorrect! • The problem • Growth and profits do not equal cash flow • Growth is not an end in itself • Needs to be managed • Firms often fail because they do not plan for growth

  7. Cash Flow is Needed to Support Sales • Cash is the life blood of organizations • Funds receivables and inventory • Buys productive assets • Pays investors fair returns BUT • Cash needed to support growing sales can exceed cash flow • Firms fail when Needs of expansion Overwhelm Resources for expansion

  8. Flow of a Sales-Driven Model

  9. Modeling Sales Forecasts • Simple Method: Assume average or different growth rate for each period • Develop detail forecast by product, business unit, customer or geographic market and aggregate forecasts • Forecast the unit price and volume • Forecast size of market and estimate market share • Forecast costs and apply a margin assumption to derive sales

  10. Financial Planning Model Assumptions • Items that are functional relationships of sales • Percent of sales • Operating expenses • Working capital and fixed assets • Items that are functional relationships of other financial statement items • Interest, depreciation, dividends, marketable securities, cash flows • Items that involve policy decisions (e.g. long-term debt, equity, capital expenditures)

  11. Generating the Income Statement • Expenses in period t • Related to asset and liability balances at the end of period t – 1 • Interest Expensetbased on Debtt-1 • Depreciationt based on Gross Fixed Assetst-1 Or • Related to Sales in period t • COGSt = COGS/Sales * Salest

  12. Balance Sheet Sector • Track financial state of firm through time • Balance sheet equations go a long way towards ensuring this • Results in one period (t – 1) become inputs to next period (t) • EXAMPLE • Debtt= Debtt-1 + Debt Issuedt – Debt Repaidt • Feed into income statement • Interest Expenset = Debtt-1 * Interest Rate

  13. Investment ModuleCalculates Required New Investment • Compile changes in assets • Left side of balance sheet • Current Assets • Including Cash Balance • Not including discretionary items (e.g. marketable securities) • Long-term assets • Net Fixed Assets • Goodwill • Add cost of maintaining production • Replacement of depreciated assets • Output information to Financing Module

  14. Three Sources for Financing New Investment • Spontaneous • Increase in liabilities that increase naturally with sales • Internal equity • Current year's retained earnings • External capital markets • New debt or equity

  15. Sustainable Growth in the Rapid Growth Phase • The "it takes money to make money" phase • Increased sales require more assets of all types • Internal sources don’t generate enough cash • Retained earnings • Increase in spontaneous liabilities • Need to raise capital externally • New question: How fast can firm grow without altering its current capital structure?

  16. The “Plug” • The balance-sheet item which will “close” or balance the balance sheet model so that Total Assets will always equal Total Liabilities & Equity • Models the assumption of how the firm finances itself • Examples are cash & marketable securities, debt, debt & equity • See Plugs.xlsx

  17. Plan Your Layout • On paper, map out the different sections of your model and their planned locations within the workbook • Consider how • readable the model layout will be for the user • easy it will be to modify the assumptions • easy it will be to extend the model to include more time periods or new products.

  18. Modules • Data Inputs • Assumptions • Decision Variables • Internal Data and Data Structures • Outputs • Pro-forma statements • Other structural relationships • Documentation

  19. Layout Strategies • Modules on different worksheets • Stair-step format in same worksheet • For each strategy, • Consider how difficult it will be to insert or delete a row or column • Consider how much linking needs to be done between different modules • Consider linking a data input to the computation area or output for later reference

  20. Range Names • Use range names to create an overview of a worksheet in the model layout. • Highlight the cell or range you want to name, select Insert>Name>Define (2003) or Formulas>Define Name (2007) • Use View>Zoom where the Zoom factor is set to less than 40% to see overview • To apply name to all relevant formulas in spreadsheet, Insert>Name>Apply (2003) or Formulas>Define Name>Apply Name (2007)

  21. Other Layout Considerations • Gaps between logical areas improve readability • Isolate blocks of similar formulas for safe and convenient copying • Use styles, fonts and colors with consistent meanings

  22. Data Input Guidelines • Isolate constants into their own cells • Do not use constants in formulas unless it is a number like 1 or 100 being used in percentage calculations • Don’t have multiple instances of a number that is used as a decision variable or assumption • Organize decision variables and model parameter/assumptions logically into separate physical areas

  23. To Reduce Formula Errors • Use relative and absolute cell addressing where appropriate (Use [F4] to modify $ in formulas!) • Set up common structures in different parts of the model (e.g. same row labels for different worksheets) • Use formulae for a cell which are consistent in their relationship to adjacent cells • Minimize linking formulas across worksheets • Split complex formulas into multiple intermediary formulas

  24. Circular References • Created when expenses in period t refer to assets and liabilities in period t, resulting in modified values for the plug which then impact the income statement and the Addition to Retained Earnings • Iterative process is required to produce a consistent and correct final set of financial statements • To solve, use Tool Options Calculation Iteration (2003) or MS Office Button>Excel Options> Formulas> Iteration (2007) • Not all circularities are convergent. Make sure to verify accuracy!

  25. Verify Formula Reliability • Use Excel’s Auditing Toolbar • Tools>Auditing> (2003) • Formulas>Formula Auditing (2007) • Use [Ctrl]+` to toggle between formulas and calculations • Use Conditional Formatting to flag suspicious results • Format>Conditional Formatting (2003) • Home>Conditional Formatting (2007)

  26. Calculating a Plug with the Investment and Financing Modules • Investment module • Required New Investment • Financing module • Required External Financing • Spontaneous Financing • Internal Equity Financing • See Logic for Debt as Secondary Plug Excel file

  27. Testing the Model • Questions: • Does it balance under extreme conditions? • Does it make sense? • Extreme growth • Set growth rate to -50% and 50% • Does it still balance? • Do debt or equity go negative? • What happens to excess cash flow? • High Interest • What happens when Net Income is negative?

  28. Document the Spreadsheet • Use range names to make individual formulas more readable and module layouts clearer • Use comments to document assumptions and modifications in a formula’s logic as well as sources of data • Use textboxes to describe portions of the model’s logic and assumptions as well as instructions for use • Create a worksheet to describe the purpose of the model and document changes

More Related