finance information system help

profilebombshellri
topic_7._in_class_worksheet.xlsm
Home>Business & Finance homework help>finance information system help

Regression(A)

Date TGT Close Price TGT Monthly Return Risk Free Rate TGT Monthly Excess Return Market Monthly Excess Return
December-11 $ 44.97
January-12 $ 44.61 0.0000% 0.0000% 5.0500%
February-12 $ 50.06 0.0000% 0.0000% 4.4200%
March-12 $ 51.46 0.0000% 0.0000% 3.1100%
April-12 $ 51.17 0.0000% 0.0000% -0.8500%
May-12 $ 51.42 0.0100% -0.0100% -6.1900%
June-12 $ 51.67 0.0000% 0.0000% 3.8900%
July-12 $ 53.85 0.0000% 0.0000% 0.7900% Average Monthly Return_TGT Excess
August-12 $ 57.23 0.0100% -0.0100% 2.5500% Variance_TGT Excess
September-12 $ 56.68 0.0100% -0.0100% 2.7300% Standard Deviation_TGT Excess
October-12 $ 56.93 0.0100% -0.0100% -1.7600% Variance_Market Excess
November-12 $ 56.70 0.0100% -0.0100% 0.7800% Standard Deviation_Market Excess
December-12 $ 53.15 0.0100% -0.0100% 1.1800% Covariance_TGT and Market Excess
January-13 $ 54.26 0.0000% 0.0000% 5.5700% Sharpe Ratio_TGT
February-13 $ 56.87 0.0000% 0.0000% 1.2900% Beta_Calculation
March-13 $ 61.83 0.0000% 0.0000% 4.0300% Beta_Regression
April-13 $ 63.74 0.0000% 0.0000% 1.5500%
May-13 $ 63.11 0.0000% 0.0000% 2.8000% Formula:
June-13 $ 62.53 0.0000% 0.0000% -1.2000% Monthly Return= (Current Price/Previous Period Price)-1
July-13 $ 64.70 0.0000% 0.0000% 5.6500%
August-13 $ 57.85 0.0000% 0.0000% -2.7100% Geometric Average Return = (Ending price/Start Price)^(1/number of period)-1
September-13 $ 58.46 0.0000% 0.0000% 3.7700%
October-13 $ 59.20 0.0000% 0.0000% 4.1800% Beta = (Covariance_TGT and Market Excess)/(Variance_Market Excess)
November-13 $ 58.79 0.0000% 0.0000% 3.1200%
December-13 $ 58.19 0.0000% 0.0000% 2.8100% Sharpe Ratio= (Average Monthly Excess Return)/(Standard Deviation_TGT Excess)
January-14 $ 52.09 0.0000% 0.0000% -3.3200%
February-14 $ 57.95 0.0000% 0.0000% 4.6500%
March-14 $ 56.07 0.0000% 0.0000% 0.4300% Cost of Equity Calculation
April-14 $ 57.22 0.0000% 0.0000% -0.1900%
May-14 $ 52.99 0.0000% 0.0000% 2.0600% Risk free rate 2.34%
June-14 $ 54.10 0.0000% 0.0000% 2.6100% Market return 9.34%
July-14 $ 55.63 0.0000% 0.0000% -2.0400% rE, cost of equity
August-14 $ 56.58 0.0000% 0.0000% 4.2400%
September-14 $ 59.04 0.0000% 0.0000% -1.9700%
October-14 $ 58.23 0.0000% 0.0000% 2.5200%
November-14 $ 70.24 0.0000% 0.0000% 2.5500%
December-14 $ 72.05 0.0000% 0.0000% -0.0600%
January-15 $ 69.87 0.0000% 0.0000% -3.1100%
February-15 $ 73.42 0.0000% 0.0000% 6.1300%
March-15 $ 78.43 0.0000% 0.0000% -1.1200%
April-15 $ 75.33 0.0000% 0.0000% 0.5900%
May-15 $ 76.31 0.0000% 0.0000% 1.3600%
June-15 $ 78.53 0.0000% 0.0000% -1.5300%
July-15 $ 78.74 0.0000% 0.0000% 1.5400%
August-15 $ 75.29 0.0000% 0.0000% -6.0400%
September-15 $ 76.21 0.0000% 0.0000% -3.0800%
October-15 $ 74.78 0.0000% 0.0000% 7.7500%
November-15 $ 70.79 0.0000% 0.0000% 0.5600%
December-15 $ 70.90 0.0100% -0.0100% -2.1700%
January-16 $ 69.48 0.0100% -0.0100% -5.7700%
February-16 $ 75.89 0.0200% -0.0200% -0.0700%
March-16 $ 79.60 0.0200% -0.0200% 6.9600%
April-16 $ 76.91 0.0100% -0.0100% 0.9200%
May-16 $ 67.04 0.0100% -0.0100% 1.7800%
June-16 $ 68.06 0.0200% -0.0200% -0.0500%
July-16 $ 73.43 0.0200% -0.0200% 3.9500%
August-16 $ 68.96 0.0200% -0.0200% 0.5000%
September-16 $ 67.48 0.0200% -0.0200% 0.2500%
October-16 $ 67.53 0.0200% -0.0200% -2.0200%
November-16 $ 76.53 0.0100% -0.0100% 4.8600%
December-16 $ 71.57 0.0300% -0.0300% 0.0181

Data Download(B) (2)

Name of the company:
From Bloomberg_FA 2017 2018 2019 2020 2021
EPS (Earnings Per Share)
FCF (Free Cash Flow) Projection
E, market value of equity from Bloomberg_WACC
D, market value of debt from Bloomberg_WACC
rE, cost of equity from Regression (A)
rD, cost of debt from Bloomberg_WACC
TC, firm's tax rate from Bloomberg_WACC
WACC, weighted average cost of capital: WACC=rE*E/(E+D)+rD*(1-TC)*D/(E+D) Your Calculation
Recommended Ratio Analysis(From Bloomberg_RV)
Current Ratio Quick Ratio Cash Conversion Cycle Gross Profit Margin Operating Profit Margin Net Profit Margin ROA ROE Retention Ratio Debt/ Asset Ratio
Your company
Competitor 1
Competitor 2
Competitor 3

1

VALUING XYZ CORP. SHARES
Current dividend, D0 10
Dividend growth rate, g 7%
Cost of equity, rE 15%
Share value <--

Discounted Dividends Valuat.(C)

Discounted Dividends Approach
Perp
Relevant Valuation Item 0 1 2 3 4 5 6
2016 2017 2018 2019 2020 2021 2022
EPS (Earnings Per Share) 0.05 0.17 0.28 0.34 0.41
DPS (Dividends Per Share)
PV Factor
PV of Annual Dividends
Total PV of Annual Dividends
PV of Terminal Value
Projected Equity Price
Long Term Growth Rate
Observed Share Price $10.00 From Yahoo Finance 1.0% 1.5% 2.0% 2.5% 3.0%
Cost of Equity (From Regression Analysis) 5.00% From Regression (A) 3.0%
Long Term Growth Rate (g) 2.00% From your research 4.0%
Dividend Payout Ratio 50% From Yahoo Finance Cost of Equity 5.0%
6.0%
7.0%
Currently
LL Overvalued by >10%
Currently
UL Undervalued by >10%

2

VALUING ARNOLD CORP
2010 FCF (base year) 2,000,000
Future FCF growth rate 8%
WACC 15%
End-2010 debt 10,000,000
End-2010 cash 1,000,000
Number of shares outstanding 1,000,000
Enterprise value <--
Add cash <--
Subtract debt <--
Value of equity <--
Share value <--

Disc Free Cash Flow Valuat.(D

Discounted Free Cash Flow
0 1 2 3 4 5 Perp
2016 2017 2018 2019 2020 2021 2022
Annual FCF 254 271 269 267 278
PV Factor
PV of Annual Free Cash Flows
Total PV of Annual FCF
PV of FCF Terminal Value
Enterprise Value
Cash and Marketable Securities 10 From Balance Sheet, Yahoo Finance
Market Value Debt & Preferred Stock 3,467 From Data Download(B)
Market Value of Equity Long Term Growth Rate
Shares Outstanding 130 From Yahoo Finance 1.0% 1.5% 2.0% 2.5% 3.0%
Projected Equity Price 2.0%
3.0%
Oberved Share Price (11/1/2014) $22.07 From Yahoo Finance WACC 4.0%
5.0%
WACC(BT) 4.00% From Data Download(B) 6.0%
Long Term Growth Rate 2.00%
Currently
LL Overvalued by >10%
Currently
UL Undervalued by >10%