1 / 77

Financial Analysis, Planning and Forecasting Theory and Application

Financial Analysis, Planning and Forecasting Theory and Application. Chapter 23. Simultaneous-Equation Models for Financial Planning. By Alice C. Lee San Francisco State University John C. Lee J.P. Morgan Chase Cheng F. Lee Rutgers University. Outline. 23.1 Introduction

yorick
Download Presentation

Financial Analysis, Planning and Forecasting Theory and Application

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 Analysis, Planning and ForecastingTheory and Application Chapter 23 Simultaneous-Equation Models for Financial Planning By Alice C. Lee San Francisco State University John C. Lee J.P. Morgan Chase Cheng F. Lee Rutgers University

  2. Outline • 23.1 Introduction • 23.2 Warren and Shelton model • 23.3 Eastman Kodak as a case study • 23.4 Francis and Rowell (FR) model • 23.5 Summary • Appendix 23A. Procedure of Using Microsoft Excel to Run FINPLAN Program • Appendix 23B. Program of FINPLAN With An Example

  3. 23.1 Introduction

  4. 23.2 Warren and Shelton model

  5. 23.2 Warren and Shelton model TABLE 23.1 The Warren and Shelton Model (Cont.) III. Financing the desired level of assets

  6. 23.2 Warren and Shelton model TABLE 23.1 The Warren and Shelton Model (Cont.)

  7. 23.2 Warren and Shelton model

  8. 23.2 Warren and Shelton model TABLE 23.2 List of unknowns and list of parameters provided by management (Cont.)

  9. 23.2 Warren and Shelton model TABLE 23.3 FINPLAN input format (Cont.)

  10. Balance Sheet 23.2 Warren and Shelton model TABLE 23.3 (Cont.) Historical or Base-Period input:

  11. 23.2 Warren and Shelton model TABLE 23.3 (Cont.) Historical or Base-Period input: Balance Sheet

  12. Income Statement 23.2 Warren and Shelton model TABLE 23.3 (Cont.) Historical or Base-Period input:

  13. 23.2 Warren and Shelton model Income Statement TABLE 23.3 (Cont.) Historical or Base-Period input:

  14. Statement of Cash Flows 23.2 Warren and Shelton model TABLE 23.3 (Cont.)

  15. Retained Earnings Statement 23.2 Warren and Shelton model TABLE 23.3 (Cont.)

  16. Retained Earnings Statement 23.2 Warren and Shelton model TABLE 23.3 (Cont.) The above data of financial statements is downloaded from the COMPUSTAT dataset. @NA represents data is not available.

  17. 23.3 Eastman Kodak as a case study • Data sources and parameter estimations • Procedure for calculating WS model

  18. 23.3 Eastman Kodak as a case study

  19. 23.3 Eastman Kodak as a case study Procedure for Calculating WS Model By using the data above, we are able to calculate the unknown variables below: (1) Salest = Salest-1(1 + GCALSt) = 11,703.7  1.0726 = 12,553.38 (2) EBITt = REBITt-1 Salest = 0.1967  12,553.38 = 2,469.25 (3) CAt = RCAt-1 Salest = 0.1368 12,553.38 = 1,717.30

  20. 23.3 Eastman Kodak as a case study (4) FAt = RFAt-1 Salest = 0.6805  12,553.38 = 8,542.58 (5) At = CAt + FAt = 1,717.30 + 8,542.58 = 10,259.88 (6) CLt = RCLt-1 Salest = 0.1698  12,553.38 = 2,131.56 (7) NFt = (At– CLt– PFDSKt) – (Lt-1– LRt) – St-1– Rt-1– bt{(1 – Tt)[EBITt– it-1(Lt-1– LRt)] – PFDIVt} = (10,259.88 – 2,131.56 – 0) - (4,880.60 – 553.70) – 716.1 – 9,006.20 – 0.6054{(1-0.3524)(2,469.25 - 0.0631(4,880.60 – 553.70) – 0} = -6,781.92

  21. 23.3 Eastman Kodak as a case study (12) itLt = i0(L0– LRt) + ietNLt = 0.0631(4,880.60 – 553.70) + 0.0631NLt = 273.03 + 0.0631NLt (8) NFt + bt(1-T)[iNLt + ULtNLt] = NLt + NSt -6,781.92 + 0.6054(1 - 0.3524)(0.0631NLt + 0.03NLt) = NLt + NSt -6,781.92 + 0.0365NLt = NLt + NSt (a) NSt +0.9635NLt = -6,781.92 (9) Lt = Lt-1– LRt + NLt (b) Lt = 4,880.60 – 553.70 + NLt Lt– NLt = 4,326.90 (10) St = St-1 + NSt (c) -NSt + St = 716.1 (11) Rt = Rt-1 + bt{(1 – Tt)[EBITt– itLt– ULtNLt] – PFDIVt} = 9,006.20 + 0.6054{0.6476[2,469.25 – itLt - 0.03NLt]}

  22. 23.3 Eastman Kodak as a case study Substitute (12) into (11) Rt = 9,006.20 + 0.6054{0.6476[2,469.25 – (273.027 + 0.0631NLt) - 0.03NLt]} = 9,006.20 + 861.04 - 0.0357NLt (d) Rt = 9,867.24 - 0.0357NLt (13) Lt = (St + Rt)Kt Lt = 1.2446St + 1.2446Rt (e) Lt– 1.2446St– 1.2446Rt = 0 (b) – (e) = (f) 0 = (Lt– NLt– 4,326.90) – (Lt - 1.2446St - 1.2446Rt) 4,326.90= 1.2446St + 1.2446Rt– NLt (f) – 1.2446(c) = (g) 4,326.9-4,880.7= (1.2446St - 1.2446Rt– NLt ) – 1.2446(-NSt + St ) 3,435.64= 1.2446NSt - NLt + 1.2446Rt

  23. 23.3 Eastman Kodak as a case study (g) – 1.2446(d) = (h) 3,435.64 - 12,280.77 = (1.2446NSt– NLt + 1.2446Rt ) – 1.2446(.0494NLt + Rt ) - 8,845.13 = 1.2446NSt– 1.06148NLt (h) – 1.2446(a) = (i) 1.2446NSt– 1.06148NLt– 1.2446(NSt + 0.9505NLt ) = - 8,845.13 + 8,440.78 NLt = 180.15 Substitute NLt in (a) NSt + 0.9635(180.15) = -6,781.92 NSt = -6,955.49

  24. 23.3 Eastman Kodak as a case study Substitute NLt in (b) Lt = 4,880.60 – 553.70 + 180.15 = 4,507.05 Substitute NSt in (c) 10,159.65 + St = 716.10 St = -6,239.39 Substitute NLt in (d) Rt = 9,867.24 - 0.0357(180.15) Rt = 9,860.81 Substitute NLtLt in (12)… it(4,505.76) = 273.027 +0.0357(180.15) it =0.0631

  25. 23.3 Eastman Kodak as a case study (14) EAFCDt = (1 – Tt)(EBITt– itLt– ULtNLt)- PFDIVt = 0.6476[2,469.25 – (0.0631)(4,505.76) - 0.0631(178.86)] = 1,407.55 (15) CMDIVt = (1 – bt)EAFCDt = 0.3946(1,407.55) = 555.42 (16) NUMCSt = X1 = NUMCSt-1 + NEWCSt X1 = 461.10 + NEWCSt (17) NEWCSt = X2 = NSt / (1 – Ust) Pt X2 = - 6,955.49 / (1 - 0.1016)Pt (18) Pt = X3 = mtEPSt X3 = 23.70(EPSt)

  26. 23.3 Eastman Kodak as a case study (19) EPSt = X4 = EAFCDt / NUMCSt X4 = 1,407.55 / NUMCSt (20) DPSt = X5 = CMDIVt/ NUMCSt X5 = 555.42 / NUMCSt (A) = For (18) and (19) we obtain X3 = 23.70(1,407.55) / NUMCSt =33,452.55/X1 Substitute (A) into Equation (17) to calculate (B) (B) = -6,955.49 / (1-0.1016)(23.70)(1,407.55) / X1 (B) = 0.2321X1

  27. 23.3 Eastman Kodak as a case study Substitute (B) into Equation (16) to calculate (C) (C) = X1 = 461.10 - 0.2321X1 (C) = X1 = 374.24 Substitute (C) into (B)… (B) = 0.3231X1 (B) = 116.92 From Equation (19) and (20) we obtain X4, X5 and X3 X4 = 1,411.5 / 374.24 = 3.76 X5 = 555.42 / 344.24 = 1.48 X3 = 23.70(3.76) = 89.11

  28. 23.3 Eastman Kodak as a case study The results of the above calculations allow us to forecast the following information regarding Anheuser-Busch in the 2000 fiscal year ($ in thousands, except for per share data): • Sales = $12,553.38 • Current Assets = $1,717.30 • Fixed Assets = $8,542.58 • Total Assets = $10,259.88 • Current Payables = $2,131.56 • Needed Funds = ($6,781.92) • Earnings Before Interest and Taxes = $2,469.25 • New Debt = $180.15 • New Stock = ($6,955.49) • Total Debt = $4,507.05 • Common Stock = ($6,239.39) • Retained Earnings $9,858.34 • Interest Rate on Debt = 6.31% • Earnings Available for Common Dividends = $1,407.55 • Common Dividends = $555.42 • Number of Common Shares Outstanding = 374.24 • New Common Shares Issued = 86.86 • Price per Share = $89.11 • Earnings per Share = $3.76 • Dividends per Share = $1.48

  29. 23.3 Eastman Kodak as a case study TABLE 23.4 The Comparison of Financial Forecast of Anheuser-Busch Companies, Inc.: Hand Calculation and FinPlan Forecasting.

  30. 23.3 Eastman Kodak as a case study TABLE 23.4 The Comparison of Financial Forecast of Anheuser-Busch Companies, Inc.: Hand Calculation and FinPlan Forecasting. (Cont.)

  31. 23.3 Eastman Kodak as a case study TABLE 23.5 FINPLAN input

  32. 23.3 Eastman Kodak as a case study TABLE 23.6 Pro forma balance sheet of Anheuser-Busch Companies, Inc.: 2000-2003

  33. 23.3 Eastman Kodak as a case study TABLE 23.7 Pro forma income statement of Anheuser-Busch Companies, Inc.: 2000-2003

  34. 23.3 Eastman Kodak as a case study TABLE 23.8 Results of sensitivity analysis

  35. 23.4 Francis and Rowell (FR) model • The FR model specification • A brief discussion of FR’s empirical results

  36. 23.4 Francis and Rowell (FR) model TABLE 23.9 List of variables for FR model.

  37. 23.4 Francis and Rowell (FR) model TABLE 23.9 List of variables for FR model. (Cont.)

  38. 23.4 Francis and Rowell (FR) model TABLE 23.9 List of variables for FR model. (Cont.)

  39. 23.4 Francis and Rowell (FR) model TABLE 23.9 List of variables for FR model. (Cont.)

  40. 23.4 Francis and Rowell (FR) model TABLE 23.10 List of equations for FR Model.

  41. 23.4 Francis and Rowell (FR) model TABLE 23.10 List of equations for FR Model. (Cont.)

  42. 23.4 Francis and Rowell (FR) model TABLE 23.11 Sector interdependence

  43. 23.4 Francis and Rowell (FR) model TABLE 23.12Variable interdependence within sector seven

  44. 23.4 Francis and Rowell (FR) model TABLE 23.13 Transformation of industry sales moments to company NIAT and EBIY moments

  45. 23.4 Francis and Rowell (FR) model TABLE 23.13 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)

  46. (Cont.) 23.4 Francis and Rowell (FR) model TABLE 23.13 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)

  47. (Cont.) 23.4 Francis and Rowell (FR) model TABLE 23.13 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)

  48. (Cont.) 23.4 Francis and Rowell (FR) model TABLE 23.13 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)

  49. 23.4 Francis and Rowell (FR) model

  50. 23.5 Summary Two simultaneous-equation financial planning models are discussed in detail in this chapter. There are 20 equations and 20 un­knowns in the WS model. Annual financial data from Anheuser-Busch Co. are used to show how the WS model can be used to perform financial analysis and planning. A computer program of the WS model is presented in Appendix B. The FR model is a generalized WS financial-planning model. There are 36 equation and 36 unknown in the FR model. The two simultaneous-equation financial-planning models discussed in this chapter are an alternative to Carleton's linear-programming mode­l, to perform financial analysis, planning, and forecasting.

More Related