520 likes | 538 Views
Financial Analysis, Planning and Forecasting Theory and Application. Chapter 24. Simultaneous-Equation Models for Financial Planning. By Cheng F. Lee Rutgers University, USA John Lee Center for PBBEF Research, USA. Outline. 24.1 Introduction 24.2 Warren and Shelton model
E N D
Financial Analysis, Planning and ForecastingTheory and Application Chapter 24 Simultaneous-Equation Models for Financial Planning By Cheng F. Lee Rutgers University, USA John Lee Center for PBBEF Research, USA
Outline • 24.1 Introduction • 24.2 Warren and Shelton model • 24.3 Johnson & Johnson (JNJ) as a case study • 24.4 Francis and Rowell (FR) model • 24.5 Feltham-Ohlson model for determining equity value • 24.6 Combined forecasting method to determine equity value • 24.7 Summary
24.2 Warren and Shelton model Table 24.1
24.2 Warren and Shelton model TABLE 24.1 The Warren and Shelton Model (Cont.) III. Financing the desired level of assets
24.2 Warren and Shelton model TABLE 24.1 The Warren and Shelton Model (Cont.)
24.2 Warren and Shelton model Table 24.2
24.2 Warren and Shelton model TABLE 24.2 List of unknowns and list of parameters provided by management (Cont.)
24.2 Warren and Shelton model TABLE 24.3 FINPLAN input format (Cont.)
Balance Sheet 24.2 Warren and Shelton model TABLE 24.3 (Cont.) Historical or Base-Period input:
24.2 Warren and Shelton model TABLE 24.3 (Cont.) Historical or Base-Period input: Balance Sheet
Income Statement 24.2 Warren and Shelton model TABLE 24.3 (Cont.) Historical or Base-Period input:
24.2 Warren and Shelton model Income Statement TABLE 24.3 (Cont.) Historical or Base-Period input:
Statement of Cash Flows 24.2 Warren and Shelton model TABLE 24.3 (Cont.)
Retained Earnings Statement 24.2 Warren and Shelton model TABLE 24.3 (Cont.)
Retained Earnings Statement 24.2 Warren and Shelton model TABLE 24.3 (Cont.) The above data of financial statements is downloaded from the COMPUSTAT dataset. @NA represents data is not available.
24.3 Johnson & Johnson (JNJ) as a case study • Data sources and parameter estimations • Procedure for calculating WS model
24.3 Johnson & Johnson (JNJ) 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) = 61897.0 0.71 = 43,946.87. (2) EBITt = REBITt-1Salest = 0.2710 43,946.87 = 11,909.60. (3) CAt = RCAt-1Salest = 0.6388 43,946.87 = 28,073.26
24.3 Johnson & Johnson (JNJ) as a case study (4) FAt = RFAt-1 Salest = 0.8909 43,946.87 = 39,152.27 (5) At = CAt + FAt = 28,073.26 + 39,152.27 = 67,225.53 (6) CLt = RCLt-1 Salest = 0.3109 43,946.87 = 13,663.08. (7) NFt = (At– CLt– PFDSKt) – (Lt-1– LRt) – St-1– Rt-1– bt{(1 – Tt)[EBITt– it-1(Lt-1– LRt)] – PFDIVt} = (67,225.53 – 13,663.08 – 0) - (8,223.0 – 219.0) – 3,120.0 – 67,248.0 – 0.5657 {(1-0.2215)(11,909.60 - 0.0671(8,223.0 – 219.0) – 0} = -29,817.99.
24.3 Johnson & Johnson (JNJ) as a case study (12) itLt = i0(L0– LRt) + ietNLt = 0.0671(8,223.0 – 219.0) + 0.0671NLt = 537.0684 + 0.0671NLt (8) NFt + bt(1-T)[iNLt + ULtNLt] = NLt + NSt -29817.99 + 0.5657(1 - 0.2215)x(0.0671NLt + 0.067NLt) = NLt + NSt -29817.99 + 0.0591NLt = NLt + NSt (a) NSt +0.9635NLt = -29,817.99 (9) Lt = Lt-1– LRt + NLt (b) Lt = 8,223.0 – 219.0 + NLt Lt– NLt = 8,004 (10) St = St-1 + NSt (c) -NSt + St = 3,120.0 (11) Rt = Rt-1 + bt{(1 – Tt)[EBITt– itLt– ULtNLt] – PFDIVt} = 67,248.0 + 0.5657{(1 - 0.2215) x [11,909.60 – itLt - 0.0671NLt]}
24.3 Johnson & Johnson (JNJ) as a case study Substitute (12) into (11) Rt = 67,248.0 + 0.5657 x {0.7785 x [11,909.60 – (537.0684 + 0.0671NLt) - 0.0671NLt]} = 67,248.0 + 5,008.4347 - 0.0591NLt (d) Rt = 72,256.435 - 0.0591NLt (13) Lt = (St + Rt)Kt Lt = 0.1625St + 0.1625Rt (e) Lt– 0.1625St– 0.1625Rt = 0 (b) – (e) = (f) 0 = (Lt– NLt– 4,326.90) – (Lt – 0.1625St – 0.1625Rt) 8,004 = 0.1625St + 0.1625Rt– NLt (f) – 0.1625(c) = (g) 7,497 – 507 = (0.1625St – 0.1625Rt– NLt ) – 0.1625(-NSt + St ) 7,497 = 0.1625NSt - NLt + 0.1625Rt
24.3 Johnson & Johnson (JNJ) as a case study (g) – 0.1625(d) = (h) 7,497 – 0.1625 x 72,256.435 = (0.1625NSt– NLt + 0.1625Rt ) – 0.1625(Rt + .0591NLt) - 4,244.67 = 0.1625NSt– 1.0096NLt (h) – 0.1625(a) = (i) 0.1625NSt– 1.0096NLt– 0.1625(NSt + 0.9409NLt ) = - 8,845.13 + 8,440.78 NLt = -600.7533/1.1625 = -516.777 Substitute NLt in (a) NSt + 0.9409(-516.777) = -29,817.99 NSt = -29,331.755
24.3 Johnson & Johnson (JNJ) as a case study Substitute NLt in (b) Lt = 8,223.0 – 219.0 – 516.777 = 7,487.223 Substitute NSt in (c) 29,331.755 + St = 3,120.0 St = -2611.755 Substitute NLt in (d) 72,256.43 = Rt + 0.0591(-516.777) Rt = 72,286.98 Substitute NLtLt in (12)… it(7,487.223) = 537.0684 + 0.0671(-516.777) it =0.0671
24.3 Johnson & Johnson (JNJ) as a case study (14) EAFCDt = (1 – Tt)(EBITt– itLt– ULtNLt)- PFDIVt = 0.7785[11,909.60 – (0.0671)(7,487.223) - 0.0671(-516.777)] = 8,907.51 (15) CMDIVt = (1 – bt)EAFCDt = 0.4343(8,907.51) = 3,868.53 (16) NUMCSt = X1 = NUMCSt-1 + NEWCSt X1 = 2754.3 + NEWCSt (17) NEWCSt = X2 = NSt / (1 – Ust) Pt X2 = - 29,331.755 / (1 - 0.1053)Pt (18) Pt = X3 = mtEPSt X3 = 14.5(EPSt)
24.3 Johnson & Johnson (JNJ) as a case study (19) EPSt = X4 = EAFCDt / NUMCSt X4 = 8,907.5075 / NUMCSt (20) DPSt = X5 = CMDIVt/ NUMCSt X5 = 3,868.53 / NUMCSt (A) = For (18) and (19) we obtain X3 = 14.5(8,907.51) / NUMCSt = 129,158.9/X1 Substitute (A) into Equation (24.17) to calculate (B) (B) = -29,331.755 / [(1-0.1053) x 129,158.9 / X1] (B) = -0.2538X1
24.3 Johnson & Johnson (JNJ) as a case study Substitute (B) into Equation (24.16) to calculate (C) (C) = X1 = 2754.3 - 0.2538X1 (C) = X1 = 2196.76 Substitute (C) into (B)… (B) = X2 = -0.2538 x 2196.76 (B) = X2 = 2196.76 From Equation (24.19) and (24.20) we obtain X4, X5 and X3 X4 = 8,907.5075 / 2196.76 = 4.0548 X5 = 3,868.53 / 2196.76 = 1.7610 X3 = 14.5(4.0548) = 58.79
24.3 Johnson & Johnson (JNJ) as a case study The results of the above calculations allow us to forecast the following information regarding JNJ in the 2010 fiscal year ($ in thousands, except for per share data): • Sales = $43,946.87 • Current Assets = $28,073.26 • Fixed Assets = $39,152.27 • Total Assets = $67,225.53 • Current Payables = $13,663.08 • Needed Funds = ($29,817.99) • Earnings Before Interest and Taxes = $11,909.60 • New Debt = $516.777 • New Stock = ($-29,331.755) • Total Debt = $7,487.223 • Common Stock = ($26,211.755) • Retained Earnings $72,286.98 • Interest Rate on Debt = 6.71% • Earnings Available for Common Dividends = $8,907.51 • Common Dividends = $3,868.53 • Number of Common Shares Outstanding = 2196.76 • New Common Shares Issued = (577.54) • Price per Share = $58.79 • Earnings per Share = $4.0548 • Dividends per Share = $1.7610
24.4 Francis and Rowell (FR) model • The FR model specification • A brief discussion of FR’s empirical results
24.4 Francis and Rowell (FR) model TABLE 24.9 List of variables for FR model.
24.4 Francis and Rowell (FR) model TABLE 24.9 List of variables for FR model. (Cont.)
24.4 Francis and Rowell (FR) model TABLE 24.9 List of variables for FR model. (Cont.)
24.4 Francis and Rowell (FR) model TABLE 24.9 List of variables for FR model. (Cont.)
24.4 Francis and Rowell (FR) model TABLE 24.10 List of equations for FR Model.
24.4 Francis and Rowell (FR) model TABLE 24.10 List of equations for FR Model. (Cont.)
24.4 Francis and Rowell (FR) model TABLE 24.11 Transformation of industry sales moments to company NIAT and EBIY moments
24.4 Francis and Rowell (FR) model TABLE 24.11 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)
(Cont.) 24.4 Francis and Rowell (FR) model TABLE 24.11 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)
(Cont.) 24.4 Francis and Rowell (FR) model TABLE 24.11 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)
(Cont.) 24.4 Francis and Rowell (FR) model TABLE 24.11 Transformation of industry sales moments to company NIAT and EBIY moments (Cont.)
24.4 Francis and Rowell (FR) model TABLE 24.12 Sector interdependence
24.4 Francis and Rowell (FR) model TABLE 24.13Variable interdependence within sector seven
24.5 Feltham-Ohlson model for determining equity value Operating Assets = Total Assets – Financial Assets Operating Liabilities = Preferred Shares + Total Liabilities – Financial Liabilities Financial Assets = Cash and Cash Equivalent + Investment and Advancements + Short-Term Investments Financial Liabilities = Long-Term debt + Debt in Current Liabilities + Notes Payable Net Operating Assets = Operating Assets – Operating Liabilities Net Financial Assets = Financial Assets – Financial Liabilities