Financial Analysis, Planning and Forecasting Theory and Application

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.