Finance memos

profileyeo1992226
finance_excel_2.xls
Home>Business & Finance homework help>Finance memos

Student ver

Alex Ng
Telarus Case on the Cost of Capital
Total Return
Canada Preferred Shares P-Close Telus Close Dividends Dividend % Change
Canada Index % Return P-Close % Return % Return % Returns Per Share* Dividends
2003 3969.8 18 Color Key Input data
2004 3256.8 -0.180 19.25 0.069 0.126 0.057 1.02 Formula Calculation
2005 3512.4 0.078 22.88 0.189 0.255 0.066 1.1 0.078
2006 3350.4 -0.046 19.63 -0.142 -0.092 0.050 1.15 0.045
2007 4321.4 0.290 25.38 0.293 0.354 0.061 1.19 0.035
2008 4213.6 -0.025 24 -0.054 -0.006 0.048 1.23 0.034
2009 4713.5 0.119 25 0.042 0.095 0.053 1.27 0.033
2010 5927.03 0.257 29.65 0.186 0.238 0.052 1.31 0.031
2011 6699.44 0.130 44.5 0.501 0.546 0.046 1.35 0.031
2012 6485.94 -0.032 41.95 -0.057 -0.026 0.032 1.4 0.037
2013 8413.75 0.297 35.15 -0.162 -0.129 0.033 1.4 0.000
2014 8933.68 0.062 41.55 0.182 0.222 0.040 1.4 0.000
Average 0.086 29.904 0.095 0.144 0.049 1.256 0.032
Cost of Equity Dividend Growth Model Approach
P(0) 41.55
D(1) 1.4453417648 * Common Stock
Re 0.067 Using Average Dividend Return
*assuming we treat the common shares as same as preferred shares paying dividends
SML Approach
Re = Rf + B (Rm - Rf)
run regression on Excel, Canada Index Returns as X vs. Telus Total Returns as Y.
It is called LINEST function
0.3741618861 Beta
Risk-Free % Return
1-year 0.0275
5-year 0.045
Market Risk Premium 0.059
Re 0.0495652812 Average Re 0.058 Cost of Equity
Cost of Debt Cost of Preferred
Long-Term Debt You are calculating current dividend
Bonds 3,328,000,000 D Preferred Share Dividends 1.4
Preferred Cost of Preferred Stock 0.0336943442
Shares 70000000 P
Telus Bonds
Coupon Rate 0.11
Average Maturity 15
Prime Bank Rate 0.045
Bond Price (% of Par) 1.18
Yield to Maturity 0.0899082569 Cost of Debt
using YTM formula = coupon + (Face - Price)/maturity divided by (Face + Price)/2
WACC
Equity
Shares 4785000000 Long-Term Debt
Retained 1563000000 Bonds 3328000000 D
Earnings Preferred
Total Equity 6,348,000,000 E Shares 70000000 P
V(E+P+D) 9,746,000,000
Weights
E/V 0.6513441412
D/V 0.341473425
P/V 0.0071824338
Tax Rate 0.5
WACC 5.36% This is your required rate of return or discount rate
Capital Budget Investment
This looks like another stage for this case, you would need to pose this as a ??
Now you have the WACC which tells you how much 165$million costs you, what is the NPV?
Project Cost = 150 million
Float Bond (including flotation cost) = 1.04*150 = $156 million assume $$ already exists
After tax Cash Flows = $25 million per year, 14 years
Outlay 156
Cash Flow 30.25 this is an expected Cash Flow Calc. 0.5*29 + 0.5*31.5
Years 7
Total Cashflows 172.7700953975 present value of 7 years of cashflows
PVIF 1.4413246239
PVIFA 5.7114081123
NPV 16.7700953975 Is this a good project wrt NPV rule?
IRR 8.28% Is this a good project wrt IRR rule?
Year CF
0 -156
1 30.25 expected CF result
2 30.25
3 30.25
4 30.25
5 30.25
6 30.25
7 30.25
Alex Karlen-Ng: This red corner symbol shows hints for using Excel that you should try.
Windows User: Use dividend growth formula: Re = D1/P0 + g
Windows User: D0 is the most recent dividend paid.
Windows User: this is the SML formula
Windows User: LINEST will give you Beta. You just need to tell Excel which are the x and y values.
Windows User: MRP = average Canada Index return - risk free rate
Windows User: Input the SML formula here
Windows User: since both methods give you a valid Re, then take the average of them
Windows User: use the preferred yield formula = D /P0
Windows User: sum up all equity = shares plus retained earnings
Windows User: total all sources of capital
Windows User: calculate your capital weights by dividing by V
Windows User: Use the WACC formula
Windows User: to calculate expected CF, you multiply probability, P x the CF and sum them up.
Windows User: calculate by multiplying CF x PVIFA
alex: difference between total returns (F) - closing price returns € = dividend returns

Sheet2

Sheet3