I need this done within 24hours please

profileskah
sampledividenstock.xls
Home>Business & Finance homework help>I need this done within 24hours please

Data Input Sheet

STOCK VALUATION MODEL - Dividend Discount Model - DDM)
(Dividends & Repurchases)
ENTER DATA IN YELLOW-CODED CELLS
NAME OF COMPANY McCormick Inc. (MKC) OUTPUT BELOW & IN SUBSEQUENT WORKSHEETS
BE VERY CAREFUL WITH THE DECIMAL POINTS
LAST YEAR 2015 Year
NUMBER OF SHARES OUTSTANDING (Mil.) 115,370.00 Millions of Shares WATCH DECIMAL POINT
MARKET PRICE OF STOCK / SHARE $94.04 Dollars Per Share
NET INCOME AFTER TAXES $401,600.00 Millions of Dollars
TOTAL SALES $4,296,300.00 Millions of Dollars
BOOK VALUE OF EQUITY $1,670,200.00 Millions of Dollars
RISK FREE RATE (10 Yr. T- Bond) 2.00% Annual Percentage Rate
DIVIDENDS PAID (TOTAL) $204,900.00 Millions of Dollars (Find on Statement of Cash Flows)
STOCK REPURCHASES (TOTAL) $112,700.00 Millions of Dollars (Find on Statement of Cash Flows)
REQUIRED INFORMATION FOR CALCULATION OF REQUIRED RATE OF RETURN
BETA OF STOCK 0.59
MARKET RISK PREMIUM 5.50% Expected Market Risk Premium % Should be Between 5% & 6%
REQUIRED INFORMATION FOR VARIABLE GROWTH RATE VALUATIONS Terminal
2016 2017 2018 2019 Terminal
EXPECTED GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 3.80% 3.50% 3.25% 3.00% 2.50% The Terminal Growth Rate Should
Not Exceed 2% to 3%
OUTPUTS AND VALUATIONS
COMPUTER-GENERATED CALCULATIONS
EARNINGS PER SHARE $3.48
LAST CASH DIVIDEND & STOCK REPURCHASE PAYMENT PER SHARE $2.75
EXPECTED ANNUAL GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 3.18% Geometric Mean of Annual Rates
REQUIRED RATE OF RETURN (k) 5.25% CAPM
REQUIRED RATE OF RETURN (k) 6.20% Dividend Discount Model
REQUIRED RATE OF RETURN (k) 5.72% Average of CAPM and Dividend Discount Model
SUSTAINABLE RATE OF GROWTH 5.03% (ROE * (1-Payout Ratio))
COMPUTER-GENERATED CALCULATIONS
STOCK VALUE - CONSTANT GROWTH MODEL $87.58
STOCK VALUE - VARIABLE GROWTH MODEL $90.55
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 $2.75
PRICE OF STOCK $94.04
EXPECTED GROWTH RATE (DIVIDENDS & STOCK REPURCHASES) 3.18%
OUTPUT
ESTIMATED REQUIRED RATE OF RETURN (k) 6.20%

Stock Price - Constant Growth

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

Stock Price - Variable Growth

PRICE OF STOCK - VARIABLE GROWTH MODEL
INPUTS
LAST YEAR (e.g., 1996) 2015 2016 2017 2018 2019 2020
LAST CASH & STOCK REPURCHASE PAYMENT PER SHARE $2.75
EXPECTED ANNUAL GROWTH RATE (DIVIDENDS + STOCK REPURCHASES) 3.80% 3.50% 3.25% 3.00% 2.50%
REQUIRED RATE OF RETURN 5.72%
(ROR or k)
FORMULA
P0 = Present Value of Dividends Plus Present Value of Future Stock Price
OUTPUT
2016 2017 2018 2019 2020
DIVIDENDS $2.86 $2.96 $3.05 $3.15 $3.22
FUTURE PRICE $100.07
NON-DISCOUNTED CASH FLOWS $2.86 $2.96 $3.05 $103.21
PRESENT VALUE $90.55

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