Financial Statement Modeling & Spreadsheet Engineering

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