1 / 29

Financial Statement Modeling

Financial Statement Modeling. MGT 4850 Spring 2008 University of Lethbridge. Topics. Pro forma Financial Statement Firm valuation and its securities Credit analyses (how much financing will be needed) What if scenarios Mathematical structure

chamomile-green
Download Presentation

Financial Statement Modeling

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 MGT 4850 Spring 2008 University of Lethbridge

  2. Topics • Pro forma Financial Statement • Firm valuation and its securities • Credit analyses (how much financing will be needed) • What if scenarios • Mathematical structure • Simultaneous linear equations predict both the balance sheets and the income statements

  3. How Financial Statements Work • Models are sales driven • Functional relationships and policy decisions

  4. The “Plug” • Balance sheet item that “closes” the model • The firm sells no additional stock, doesnot raise or retire debt, therefore financing comes from the cash and marketable securities

  5. Projecting Next Year Inc. St.

  6. Projecting Next Year Bal. Sh.

  7. Recalculating the statements • Go to Tools/Options/Calculation click iteration

  8. Extending the model to more years • Copy and paste the columns

  9. Free Cash Flows

  10. Reconciling the Cash Balances • Consolidated statement of cash flows explains the increase in the cash account in the balance sheet as a function of the cash flows from the firm’s operating, investing and financing activities

  11. Cash and marketable securities

  12. Using the FCF to Value the firm and its Equity

  13. Enterprise Value of the Firm • The value of the firm’s debt, convertible securities and equity or PV of the firm’s future anticipated cash flows. • Example: WACC=20%, FCF projection for 5 years plus terminal value. • Terminal Value – after 5 years the cash flows will grow at 10% • FCF5*(1+growth)/(WACC-growth)

  14. Terminal Value • Terminal Value=Year5 book value of debt + Equity (assumes book value correctly predicts market value) • Terminal Value=(Enterprise market/book ratio)*(Year5 book value of debt + Equity) • Terminal Value=P/E *Year5 profits +Year5 book value of debt • Terminal Value=EBITDA ratio *Year5 anticipated EBITDA

  15. Cash and Marketable Securities in the Valuation • Add in initial (year 0) cash and mkt. securities (assumptions) • They are not needed to produce the FCF • They are “surpluses” that could be drawn down or paid out to the shareholders without affecting future performance • Can be treated as negative debt

  16. Half-Year Discounting • Cash flows occur smoothly throughout the year

  17. Sensitivity Analysis • What is the effect of the sales growth rate on the equity value?

  18. Sensitivity Analysis • Effect on equity valuation of the sales growth and the WACC

  19. Debt as a “Plug” • Increased sales growth, current and fixed assets requirements, dividend payouts → needs of more financing (p. 73)

  20. Proforma balance sheet Cash and marketable securities turning negative p.73

  21. DEBT test • Current assets + Net Fixed assets>Current Liabilities + Last year’s debt + Stock + accumulated retained earnings – we need to increase debt to finance the firm’s productive activities • Current assets + Net Fixed assets<Current Liabilities + Last year’s debt + Stock + accumulated retained earnings – there is no need to increase debt (cash and marketable securities are positive)

  22. No Negative Cash p.74

  23. Incorporating a Target Debt/Equity Ratio

  24. Project Finance • Borrowing money to finance a project may entail: • No dividends should be paid out until the debt is paid off • No new equity issue • Pay back the debt over a specific period • A new project set in year 0 • Firm assets -2,200; current liab. =100; debt=1,000 and equity = 1,100 • Debt will be paid in equal installments over 5 years

  25. Debt Repayment Schedule p.77 • Repayment reflected in debt balances

  26. Constant Fixed Assets p.78

  27. Credit Analysis p.79

  28. Return on Equity • Book Value of the firm at year 5 is: Profit/NI5+Accumulated Retained Earnings+Stock • If we decrease the initial equity investment we can increase the return on equity (leverage effect)

  29. ROE and Initial Equity Investment

More Related