Finc 351 and 440 Week 4

profilebarkersbaseball
homework_9.8.14_ddm_stock_valuation_models_0914.xls
Home>Business & Finance homework help>Finc 351 and 440 Week 4

Data Input Sheet

STOCK VALUATION MODELS - DDM (Dividends & Repurchases)
ENTER DATA IN YELLOW-CODED CELLS
NAME OF COMPANY Sample Company OUTPUT IN SUBSEQUENT WORKSHEETS
LAST YEAR XXXX Year
NUMBER OF SHARES OUTSTANDING (Mil.) 1,848.96 Millions of Shares
MARKET PRICE OF STOCK / SHARE $27.92 Dollars Per Share $51.68 Price of Stock
NET INCOME AFTER TAXES $7,237.00 Millions of Dollars Expected in
TOTAL SALES $162,558.00 Millions of Dollars 5 years
BOOK VALUE OF EQUITY $27,537.00 Millions of Dollars
RISK FREE RATE (T- Bill) 4.92% Annual Percentage Rate
DIVIDENDS PAID (TOTAL) $2,290.00 Millions of Dollars
STOCK REPURCHASES (TOTAL) $371.00 Millions of Dollars
REQUIRED INFORMATION FOR CALCULATION OF REQUIRED RATE OF RETURN
BETA OF STOCK 0.95
MARKET RISK PREMIUM 1.00% Expected Market Risk Premium %
REQUIRED INFORMATION FOR VARIABLE GROWTH RATE VALUATIONS
EXPECTED GROWTH RATE FOR EARNINGS 7.0000% 6.0000% 5.0000% 5.0000% 5.0000%
EXPECTED GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 5.0000% 5.0000% 5.0000% 5.0000% 5.0000%
COMPUTER-GENERATED CALCULATIONS
EARNINGS PER SHARE $3.91
LAST CASH DIVIDEND & STOCK REPURCHASE PAYMENT PER SHARE $1.44
EXPECTED ANNUAL GROWTH RATE FOR EARNINGS 5.55% Geometric Mean of Annual Rates
EXPECTED ANNUAL GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 5.00% Geometric Mean of Annual Rates
REQUIRED RATE OF RETURN (k) 5.87% CAPM
REQUIRED RATE OF RETURN (k) 10.41% Dividend Discount Model
REQUIRED RATE OF RETURN (k) 8.14% Average of CAPM and Dividend Discount Model
EXPECTED EARNINGS PER SHARE IN 5 YEARS $5.14
SUSTAINABLE RATE OF GROWTH 16.62% (ROE * (1-Payout Ratio))
COMPUTER-GENERATED CALCULATIONS
STOCK VALUE - ZERO GROWTH MODEL $17.68 Not applicable for most stocks
STOCK VALUE - CONSTANT GROWTH MODEL $48.11
STOCK VALUE - VARIABLE GROWTH MODEL $48.11
STOCK VALUE - DIVIDEND & EARNINGS MODEL $41.54
Must be positive values in each cell
All Data Required
Required Data

Stock Expected Return

EXPECTED RETURN COMMON STOCK
INPUTS FORMULA
k = ( D1 / P0 ) + g
LAST DIVIDEND & STOCK REPURCHASE PER SHARE $1.44
PRICE OF STOCK $27.92
EXPECTED GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 5.00%
OUTPUT
ESTIMATED REQUIRED RATE OF RETURN (k) 10.41%
5% 10.2%
1% 6.2%
2% 7.2%
3% 8.2%
4% 9.2%
5% 10.2%
6% 11.2%
7% 12.2%
8% 13.2%
9% 14.2%
10% 15.2%
11% 16.2%
12% 17.2%
13% 18.2%
14% 19.2%
15% 20.2%
16% 21.2%
17% 22.2%
18% 23.2%
19% 24.2%
20% 25.2%

Stock Expected Return
&A
Page &P
GROWTH RATE
EXPECTED RETURN
EXPECTED RETURNS AT DIFFERENT GROWTH RATES

Stock Price - Zero Growth

PRICE OF COMMON STOCK - ZERO GROWTH MODEL
INPUTS FORMULA
LAST DIVIDEND & STOCK REPURCHASE PER SHARE $1.44 P0 = D0 / k
REQUIRED RATE OF RETURN (ROR) 8.14%
(Average of CAPM and Dividend Discount Model)
OUTPUT
PRICE OF STOCK $17.68

Stock Price - Constant Growth

PRICE OF COMMON STOCK - CONSTANT GROWTH MODEL
INPUTS FORMULA
P0 = D1 / ( k - g )
LAST CASH/STOCK REPURCHASE PAYMENT PER SHARE $1.44
REQUIRED RATE OF RETURN (ROR) 8.14%
EXPECTED ANNUAL GROWTH RATE 5.00%
(Dividends + Stock Repurchases) k must > g
OUTPUT
PRICE OF STOCK $48.11

Stock Price - Variable Growth

PRICE OF STOCK - VARIABLE GROWTH MODEL
INPUTS
LAST YEAR (e.g., 1996) XXXX 0 0 0 0 0
LAST CASH & STOCK REPURCHASE PAYMENT PER SHARE $1.44
EXPECTED ANNUAL GROWTH RATE (DIVIDENDS + STOCK REPURCHASES) 5.00% 5.00% 5.00% 5.00% 5.00%
REQUIRED RATE OF RETURN 8.14%
(ROR or k)
FORMULA
P0 = Present Value of Dividends Plus Present Value of Future Stock Price
OUTPUT
0 0 0 0 0
DIVIDENDS $1.51 $1.59 $1.67 $1.75 $1.84
FUTURE PRICE $58.47
NON-DISCOUNTED CASH FLOWS $1.51 $1.59 $1.67 $60.22
PRESENT VALUE $48.11

Stock Price - Div and Earnings

PRICE OF STOCK - DIVIDEND AND EARNINGS APPROACH
INPUTS
PRESENT STOCK PRICE $27.92
LAST CASH & STOCK REPURCHASE PAYMENT PER SHARE $1.44
EXPECTED ANNUAL GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 5.00% For 5 Years
ESTIMATED STOCK PRICE $51.68 In 5 Years
REQUIRED RATE OF RETURN (ROR) 8.14%
FORMULA
P0 = Present Value of Dividends Plus Present Value of Future Stock Price
OUTPUT
XXXX 0 0 0 0
$1.51 $1.59 $1.67 $1.75 $1.84 CASH + STOCK REPURCHASE PMT PER SHARE
$51.68 FUTURE VALUE OF STOCK
$1.40 $1.36 $1.32 $1.28 $36.19 PRESENT VALUES
PRICE OF STOCK $41.54

Sheet8
&A
Page &P

Sheet9
&A
Page &P

Sheet10
&A
Page &P

Sheet11
&A
Page &P

Sheet12
&A
Page &P

Sheet13
&A
Page &P

Sheet14
&A
Page &P

Sheet15
&A
Page &P

Sheet16
&A
Page &P