FINC 430 Week 2/3 Discussions and Projects

profilebarkersbaseball
rp1-2_1.xlsx
Home>Business & Finance homework help>FINC 430 Week 2/3 Discussions and Projects

RPPart1-Correlation-Study

Item Change % StdDev Enter IDX Return StdDev Industry Return % StdDev
Year Corp_C Corp_Cs SPY_R SPY_Rs XLY_R
wm: SP Sector Idx Return http://www.sectorspdr.com/sectorspdr/ & http://performance.morningstar.com/funds/etf/total-returns.action?t=XLY
2010 5.0 9.6 5.0 9.6 27.46 13.2
2011 11.7 11.7 5.99
2012 7.6 7.6 23.6
2013 32.3 32.3 42.74
2014 15.6 15.6 9.49
Correlation SPY_R XLY_R SPY_R
2010-2012 1.000 -0.975 -0.975 =CORREL(b3:b5,f3:f5)
2011-2013 1.000 0.793 0.793
2012-2014 1.000 0.725 0.725
Reported Value-> Correl(All) 1.000 0.529 0.529 =CORREL(b3:b7,f3:f7)
Avg(3) 1.000 0.181 0.181
StdDev 0 0.8177736216 0.8177736216
Median 1.000 0.725 0.725
y, x Corp_C Corp_C XLY_R

x,y plot

Corp_C2SPY_R 5 11.7 7.6 32.299999999999997 15 .6 5 11.7 7.6 32.299999999999997 15.6

x,y plot

Corp_C2XLY_R 5 11.7 7.6 32.299999999999997 15.6 27.46 5.99 23.6 42.74 9.49

x,y plot

XLY_R2SPY_R 27.46 5.99 23.6 42.74 9.49 5 11.7 7.6 32.299999999999997 15.6

QEP2-DDM-GrowthRate

Year Enter Financial Item Average StdDev Geomean Median Item Change StdDev Enter IDX Return StdDev 3yr Rolling Avg StdDev 3yr Weighted Roll Avg StdDev
Grow GROW_A GROW_S GROW_S+ GROW_S- GROW_G GROW_M GROW_C GROW_Cs SPY_R SPY_Rs GROW_3y GROW_3ys GROW_3ys+ GROW_3ys- GROW_3yw GROW_3ywM GROW_3yws* GROW_3yws+ GROW_3yws- Wgt Periods
2009 56 71.5 9.3 80.8 62.2 70.9 72.0 0.25 3
2010 71 71.5 9.3 80.8 62.2 70.9 72.0 4.7 14.8 5.0 0.1 0.25
2011 73 71.5 9.3 80.8 62.2 70.9 72.0 36.5 11.7 66.7 7.6 74.3 59.1 68.3 68.3 0.6 68.9 67.6 0.5
2012 65 71.5 9.3 80.8 62.2 70.9 72.0 -8.1 7.6 69.7 3.4 73.1 66.3 68.5 68.5 0.3 68.8 68.2
2013 79 71.5 9.3 80.8 62.2 70.9 72.0 5.6 32.3 72.3 5.7 78.1 66.6 74.0 74.0 0.5 74.5 73.5
2014 85 71.5 9.3 80.8 62.2 70.9 72.0 14.2 15.6 76.3 8.4 84.7 68.0 78.5 78.5 0.7 79.2 77.8
2015 -
Correlation Beta =Corr(UFCF_C,SPY_R) * (STDEV(UFCF_Cs)/STDEV(SPY_Rs))
SPY_R 0.778 =CORREL(I4:I6,K4:K6) 82.29
wm: What happens to beta if we calculate a moving standard deviation for each range?		 =D20*(J4/L4)
-0.062 -6.56
0.442 46.74
Corr(All) 0.039 =CORREL(I4:I8,K4:K8) Beta (All) 4.17 =D23*(J4/L4)
Avg(3) 0.386 Avg(3) 40.82
y, x GROW_C
*Weighted Moving Standard Deviation http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf Measures of Scale: Standard Deviation http://www.itl.nist.gov/div898/handbook/eda/section3/eda356.htm http://www.morningstar.com/InvGlossary/standard_deviation.aspx

A Avg

Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_A 2009 2010 2011 2012 2013 2014 71.5 71.5 71.5 71.5 71.5 71.5 GROW_S+ 80.840770846134703 80.840770846134703 80.840770846134703 80.8407708 46134703 80.840770846134703 80.840770846134703 GROW_S- 62.159229153865297 62.159229153865297 62.159229153865297 62.159229153865297 62.159229153865297 62.159229153865297 GROW_M 72 72 72 72 72 72

3y Roll

Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_3y 2009 2010 2011 2012 2013 2014 66.666666666666671 69.666666666666671 72.333333333333329 76.333333333333329 GROW_3ys+ 74.2 53204451160698 73.066013009061862 78.068216844695087 84.713203393317684 GROW_3ys- 59.080128882172644 66.267320324271481 66.59844982197157 67.953463273348973

3y Wgt Mov

Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_3ywM 2009 2010 2011 2012 2013 2014 68.25 68.5 74 78.5 GROW_3yws+ 68.866568122756931 68.802501532282889 74.477609253551833 79.161231483094838 GROW_3yws- 67.633431877243069 68.197498467717111 73.522390746448167 77.838768516905162

Linest

Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_3ywM 2009 2010 2011 2012 2013 2014 68.25 68.5 74 78.5

x,y plot

UFCF_C2SPY_R 4.7333333333333334 36.5 -8.125 5.6428571428571432 14.166666666666666 5 11.7 7.6 32.299999999999997 15.6

RPPart2-Beta-Study

Item Change % StdDev Enter IDX Return StdDev Industry Return % StdDev
Year Corp_C Corp_Cs SPY_R SPY_Rs XLY_R
wm: SP Sector Idx Return http://www.sectorspdr.com/sectorspdr/ & http://performance.morningstar.com/funds/etf/total-returns.action?t=XLY
2010 5.0 9.6 5.0 9.6 27.46 13.2
2011 11.7 11.7 5.99
2012 7.6 7.6 23.6
2013 32.3 32.3 42.74
2014 15.6 15.6 9.49
Corp_C2SPY_R Corp_C2XLY_R XLY_R2SPY_R
Correlation SPY_R XLY_R SPY_R Beta Total Market Firm Industry =Correl(Corp_C,SPY_R) * (STDEV(Corp_Cs)/STDEV(SPY_Rs))
2010-2012 1.000 -0.975 -0.975 =CORREL(b3:b5,f3:f5) - 1.00
wm: What happens to beta if we calculate a moving standard deviation for each range?		 -0.71
wm: What happens to beta if we calculate a moving standard deviation for each range?		 -1.34 =G11*(G3/E3)
2011-2013 1.000 0.793 0.793 - 1.00 0.58 1.09
2012-2014 1.000 0.725 0.725 - 1.00 0.53 1.00
Reported Value-> Correl(All) 1.000 0.529 0.529 =CORREL(b3:b7,f3:f7) Beta (All) 1.00 1.00 0.39 0.73 =G14*(G3/E3)
Avg(3) 1.000 0.181 0.181 Avg(3) - 1.00 0.13 0.25
StdDev 0 0.8177736216 0.8177736216 StdDev - 0.00 0.60 1.12
Median 1.000 0.725 0.725 Median - 1.00 0.53 1.00
y, x Corp_C Corp_C XLY_R

x,y plot

Corp_C2SPY_R 5 11.7 7.6 32.299999999999997 15.6 5 11.7 7.6 32.299999999999997 15.6

x,y plot

Corp_C2XLY_R 5 11.7 7.6 32.299999999999997 15.6 27.46 5.99 23.6 42.74 9.49

x,y plot

XLY_R2SPY_R 27.46 5.99 23.6 42.74 9.49 5 11.7 7.6 32.299999999999997 15.6