dividend calculation

Objective Inputs needed Output1. To compare how much a firm has returned to its stockholder historically (up to 10 years) with how much it could have returned. 2. To provide an assessment of project quality (ROE compared to cost of equity) and stock price performance over the period. 3. To provide forecasts of how much cash the firm will have available for stock buybacks in the future For historical analysis: a. Net Income b. Depreciation, amortization and other non-cash charges c. Capital expenditures: Please include acquisitions as part of capital expenditures d. Non-cash working capital changes In entering these numbers, please make sure that you get the signs right (check the comment box on each of these inputs) e. Dividends: Only cash dividends should be shown here (ignore stock dividends) f. Stock Buybacks: Include the cash flow associated with stock buybacks. For project assessment and stock price performance analysis a. Beta: You should really use an average beta over the historical period, but go ahead and use your current beta if you do not have this. b. Book Value of Equity: To compute return on equity. c. Return on the stock: This is the total return you would have made as an investor: It includes price appreciation + dividend yield each year d. Riskfree rate: The one-year government security rate at the start of each year (use the T.Bill rate) e. Return on Stock Market: This is the total return on the stock market each year (You can get the last two from the worksheet that is part of this spreadsheet that reports historical data on both) For forecasts a. Expected growth rates in net income, dividends, depreciation, capital expenditures and revenues b. Working capital as a percent of revenues c. Debt as a percent of reinvestment, looking forward. As a default, you can use your historical average. Historical Analysis 1. FCFE and Cash Returned each year for the historical period 2. Returns on equity, the stock and your required return each year for the historical period 3. Averages of both over the entire period Forecasts 1. Forecasted FCFE for next 5 years 2. Forecasted dividends for next 5 years 3. Cash available each year for stock buybacks for next 5 years. Analysis of Dividend Policyhow much it could have returned. performance over the period. on each of these inputs) your current beta if you do not have this. price appreciation + dividend yield each year data on both)How many years of historical data do you have available? 10 (number of years) Use less years, if you do not have the data. Enter the following data for the years for which you have data (starting with earliest year) Year Net Income Depreciation & Amort Capital Spending Chg in Non-Cash WC Net Debt Issued 1 $1,385.00 $678.00 $1,586.00 ($1,470.00) $15.00 2 $1,567.00 $826.00 $1,850.00 $1,055.00 $993.00 3 $552.00 $961.00 $3,657.00 ($886.00) $482.00 4 $1,244.00 $953.00 $1,317.00 $515.00 $837.00 5 $856.00 $1,142.00 $795.00 $910.00 ($21.00) 6 $393.00 $1,033.00 $629.00 $951.00 $6.00 7 $1,818.00 $1,241.00 $971.00 ($514.00) ($109.00) 8 ($178.00) $1,354.00 $1,391.00 $205.00 ($635.00) 9 $1,120.00 $1,517.00 $2,874.00 $84.00 $118.00 10 $2,309.00 $1,645.00 $1,236.00 ($2,627.00) ($239.00) Enter the dollar dividends paid and equity repurchases for each year of historical data (staring with earliest year): (Equity repurchases are in Year Dividends (in $)Equity Repurchases (in $) 1 $328.00 $156.00 2 $343.00 $127.00 3 $340.00 $109.00 4 $340.00 $0.00 5 $340.00 $0.00 6 $342.00 $0.00 7 $480.00 $718.00 8 $557.00 $141.00 9 $564.00 $1,397.00 10 $537.00 $2,937.00 The last set of inputs to this analysis relate to project choice and performance: Enter the beta for the equity of this firm: 0.95 Enter the following data relating to performance (starting with earliest year): (Check worksheet for riskfree & market rates) Year BV: Equity Returns on stockRiskfree rate Return on market1 $6,131.00 22.45% 4.91% 31.86% 2 $6,973.00 43.33% 5.16% 28.34% 3 $8,093.00 26.99% 4.39% 20.89% 4 $8,056.00 -17.64% 5.37% -9.03% 5 $8,983.00 25.20% 5.73% -11.85% 6 $9,700.00 46.81% 1.80% -21.98% 7 $9,898.00 51.64% 1.80% 28.41% 8 $13,502.00 -9.73% 2.18% 10.70% 9 $12,953.00 -8.76% 4.31% 4.85% 10 $12,316.00 20.20% 4.88% 15.63% Use these inputs if you want to make projection of FCFE for the future Expected growth in Revenues over next 5 years = 7.20% Expected growth in Net Income over next 5 years = 7.20% Expected growth in capital expenditures in next 5 years = 7.20% Expected growth in depreciation in next 5 years = 7.20% Enter non-cash working capital as a percent of revenues 8.13% Enter revenues from most recent year = $57,993 Enter net income from most recent year or normalized value = $2,309 Enter capital expenditures from most recent year or normalized value =$1,236 Enter depreciation in most recent year = $1,645 Enter dividends paid in the most recent year = $537 Do you want to use the debt ratio from your historical analysis? Yes If yes, the debt ratio used will be 45.52% If no, enter the debt to capital ratio to use in forecast 25% Expected growth in dividends 5%with earliest year): (Equity repurchases are in the statement of cash flows) for riskfree & market rates)Dividend Policy 26 1 2 3 4 5 6 7 8 Net Income $1,385.00 $1,567.00 $552.00 $1,244.00 $856.00 $393.00 $1,818.00 ($178.00) -(Cap. Exp -Depr) $908.00 $1,024.00 $2,696.00 $364.00 ($347.00) ($404.00) ($270.00) $37.00 -∂ Working Capital ($1,470.00) $1,055.00 ($886.00) $515.00 $910.00 $951.00 ($514.00) $205.00 + Net Debt Issued $15.00 $993.00 $482.00 $837.00 ($21.00) $6.00 ($109.00) ($635.00) = Free CF to Equity $1,962.00 $481.00 ($776.00) $1,202.00 $272.00 ($148.00) $2,493.00 ($1,055.00) Dividends $328.00 $343.00 $340.00 $340.00 $340.00 $342.00 $480.00 $557.00 + Equity Repurchases $156.00 $127.00 $109.00 $718.00 $141.00 = Cash to Stockholders $484.00 $470.00 $449.00 $340.00 $340.00 $342.00 $1,198.00 $698.00 Dividend Ratios Payout Ratio 23.68% 21.89% 61.59% 27.33% 39.72% 87.02% 26.40% -312.92% Cash Paid as % of FCFE 24.67% 97.71% -57.86% 28.29% 125.00% -231.08% 48.05% -66.16% Performance Ratios 1. Accounting Measure ROE 22.59% 22.47% 6.82% 15.44% 9.53% 4.05% 18.37% -1.32% Required rate of return 30.51% 27.18% 20.06% -8.31% -10.97% -20.79% 27.08% 10.28% ROE -Cost of Equity -7.92% -4.71% -13.24% 23.75% 20.50% 24.84% -8.71% -11.60% 2. Stock Performance Measure Returns on stock 22.45% 43.33% 26.99% -17.64% 25.20% 46.81% 51.64% -9.73% Required rate of return 30.51% 27.18% 20.06% -8.31% -10.97% -20.79% 27.08% 10.28% Analysis of Past Dividends Page 26Dividend Policy 27 Jensen's alpha -8.06% 16.15% 6.93% -9.33% 36.17% 67.60% 24.56% -20.01% Index 1 1 1 1 1 1 1 1 Summary of calculations Average Standard Deviation Maximum Minimum Free CF to Equity $933.40 $1,854.48 $5,106.00 ($1,055.00) Dividends $417.10 $103.49 $564.00 $328.00 Dividends+Repurchases $975.60 $1,017.82 $3,474.00 $340.00 Dividend Payout Ratio 37.69% Cash Paid as % of FCFE 104.52% ROE 11.45% Return on Stock 20.05% Required Return 9.49% ROE -Required return 1.96% Actual -Required Return 10.56% Page 27Dividend Policy 28 9 10 $1,120.00 $2,309.00 $1,357.00 ($409.00) $84.00 ($2,627.00) $118.00 ($239.00) ($203.00) $5,106.00 $564.00 $537.00 $1,397.00 $2,937.00 $1,961.00 $3,474.00 50.36% 23.26% -966.01% 68.04% 8.65% 18.75% 4.82% 15.09% 3.83% 3.66% -8.76% 20.20% 4.82% 15.09% Page 28Dividend Policy 29 -13.58% 5.12% 1 1 Page 29FORECASTED FCFE AND DIVIDENDS Debt Ratio used in forecasting FCFE = 45.52%Forecasted FCFE 1 2 3 4 5 Net Income $2,475 $2,653 $2,845 $3,049 $3,269 -(Cap Ex -Deprec'n) (1 -DR) ($239) ($256) ($275) ($294) ($315) -Change in Working Capital (1 -DR) $185 $198 $213 $228 $244 FCFE $2,529 $2,711 $2,906 $3,116 $3,340 Expected Dividends $564 $592 $622 $653 $685 Cash available for stock buybacks $1,965 $2,119 $2,285 $2,463 $2,655 Revenues $62,168 $66,645 $71,443 $76,587 $82,101 Non-cash WC $339 $364 $390 $418 $448DIVIDENDSYear Stocks T.Bills 1928 43.81% 3.08% 1929 -8.30% 3.16% 1930 -25.12% 4.55% 1931 -43.84% 2.31% 1932 -8.64% 1.07% 1933 49.98% 0.96% 1934 -1.19% 0.30% 1935 46.74% 0.23% 1936 31.94% 0.15% 1937 -35.34% 0.12% 1938 29.28% 0.11% 1939 -1.10% 0.03% 1940 -10.67% 0.04% 1941 -12.77% 0.02% 1942 19.17% 0.33% 1943 25.06% 0.38% 1944 19.03% 0.38% 1945 35.82% 0.38% 1946 -8.43% 0.38% 1947 5.20% 0.38% 1948 5.70% 0.95% 1949 18.30% 1.16% 1950 30.81% 1.10% 1951 23.68% 1.34% 1952 18.15% 1.73% 1953 -1.21% 2.09% 1954 52.56% 1.60% 1955 32.60% 1.15% 1956 7.44% 2.54% 1957 -10.46% 3.21% 1958 43.72% 3.04% 1959 12.06% 2.77% 1960 0.34% 4.49%1968 10.81% 4.97% 1969 -8.24% 5.96% 1970 3.56% 7.82% 1971 14.22% 4.87% 1972 18.76% 4.01% 1973 -14.31% 5.07% 1974 -25.90% 7.45% 1975 37.00% 7.15% 1976 23.83% 5.44% 1977 -6.98% 4.35% 1978 6.51% 6.07% 1979 18.52% 9.08% 1980 31.74% 12.04% 1981 -4.70% 15.49% 1982 20.42% 10.85% 1983 22.34% 7.94% 1984 6.15% 9.00% 1985 31.24% 8.06% 1986 18.49% 7.10% 1987 5.81% 5.53% 1988 16.54% 5.77% 1989 31.48% 8.07% 1990 -3.06% 7.63% 1991 30.23% 6.74% 1992 7.49% 4.07% 1993 9.97% 3.22% 1994 1.33% 3.06% 1995 37.20% 5.60% 1996 23.82% 5.14% 1997 31.86% 4.91% 1998 28.34% 5.16% 1999 20.89% 4.39% 2000 -9.03% 5.37% 2001 -11.85% 5.73%