Question

Year

               Technology          Energy

2000:          -24.31            30.47

2001:          -38.55           -12.49

2002:          -36.89           -11.61

2003:           68.59            27.84

2004:         -9.98            35.94

2005:          17.81             70.70

2006:          3.79               -2.12

2007:          -3.13              29.30

2008:        -42.51           -48.25

2009:        79.03              40.13

2010:        45.03           34.25

2011:         -12.21          -8.76

what I have to find. There are 2 data FUNDS in the file: Technology and Energy.

1) For each fund, compute using Excel: the Average, Median, Mode, the first percentile, the third percentile.

2) Graph the Box-Plot for each fund (called Box and Whisker in Excel).

3) For each fund, compute using Excel: the Range, Mean Absolute Deviation, the variance, the standard deviation and the coefficient of variation.

4) Using Excel, compute the Sharpe-Ratio for each fund using the risk free return of 3%. 5) Using Excel, compute the correlation between both funds.

Answer 1

1) For each fund, compute using Excel: the Average, Median, Mode, the first percentile, the third percentile.

Average formula: "AVERAGE(B2:B13)"

MEdian Formula: "=MEDIAN(B2:B13)"

Mode Formula: "=MODE.MULT(B2:B13)"

Per Excel Help for the MODE function:
"If the data set contains no duplicate data points, MODE returns the #N/A error value"

Percentile calculation procedure:

In excel go to Data--> Data analysis --> choose Rank and Percentile to get the output

First Quartile formula to find min,max,Q1,Medain,Q3 and maximum value needs to be prepared in excel.

Quartile formula"

Min: QUARTILE.INC($B$2:$B$13,0)

Q1: QUARTILE.INC($B$2:$B$13,1)

Median: QUARTILE.INC($B$2:$B$13,2)

Q3: QUARTILE.INC($B$2:$B$13,3)

Max: QUARTILE.INC($B$2:$B$13,4)

Insert chart --> choose insert statistic chart and select --> box and whisker

3) For each fund, compute using Excel: the Range, Mean Absolute Deviation, the variance, the standard deviation and the coefficient of variation.

Range is calcluation max of the range or minimum of the range

RAnge: MAX(C2:C13)

MEan deviation: =AVEDEV(B2:B13)

Variance: =VAR(B2:B13)

SD: =STDEV(B2:B13)

MEan: =AVERAGE(B2:B13)

Coeffi: SD/AVerage

4) Using Excel, compute the Sharpe-Ratio for each fund using the risk free return of 3%. 5) Using Excel, compute the correlation between both funds.

Sharpe Ratio(p) = (Expected return(p)-Risk free rate)/Standard deviation(p)

First find lenght of the range with formula

=IFERROR(LN(B3/B2),0) in column C

Expected result is average of length

Results are highlighted in yellow colour

Correlation between two funds are with exel formula

=CORREL(Arrary1,Array2)

=CORREL(B2:B13,B18:B29)

answer = 0.379761