NORMAL DISTRIBUTION: STANDARDIZING RETURNS FROM MUTUAL FUNDS

 NORMAL DISTRIBUTION: STANDARDIZING RETURNS FROM MUTUAL FUNDS

The annual returns for 30 mutual funds appear in your Google classroom or on my github with file name Standardizing Finished.xlsx. Find and interpret the Z-value of these returns.

Objective:

To use Excel and Tableau to standardize annual returns of various mutual funds.

The Excel Data:

Fund	Annual return
A	0.007
B	0.080
C	0.082
D	0.123
E	0.022
F	0.054
G	0.109
H	0.097
I	0.047
J	0.021
K	0.111
L	0.180
M	0.157
N	0.134
O	0.140
P	0.107
Q	0.193
R	0.156
S	0.095
T	0.039
U	0.034
V	0.064
W	0.071
X	0.079
Y	0.088
Z	0.077
AB	0.125
AC	0.094
AD	0.078
AE	0.066

Solution for Excel:

The 30 annual returns appear below. Their mean and standard deviation are calculated in cells B4 and B5 with the AVERAGE and STDEV.S functions. The corresponding Z-values are calculated in Column C by entering the formula

=(B8-Mean)/Stdev

In cell C8 and copying it down column C.  the Z values range from a low of -1.80 to a high of 2.19. Meaning, the return for stock A is about 1.80 standard deviations below the mean, whereas the return for stock O is 1.0528 standard deviation above the mean.

 Solution for Tableau.

Step 1. Upload the Standardizing Finished.xlsx in your Tableau Desktop.

Step 2. Drag “Funds” to Columns pill and “Annual Return” to Rows pill and make sure it is AVG.  

Step 3. Drag Reference Line to the Canvas and make sure you choose Value. Label your worksheet as “Mean”.

Step 4. Create a Calculated Field because the window average is used (which is a table calculation) this means that the Profit has to be aggregated, hence SUM and in the Table Calculation you should choose Annual Return.

(SUM([Annual return])-WINDOW_AVG(SUM([Annual return])))/WINDOW_STDEV(SUM([Annual return]))

 

Step 5. Once you have created the calculated field in Tableau, you can drag the field onto labels, or the tooltips to see the calculated z-score for all of the data points. You can also drag the calculated field onto color and set a diverging color scheme to differentiate the positive z-scores from the negative z-scores and to see the distance from the mean (i.e. the gradient). Label your worksheet as Z scores

Step 6. Duplicate your Z-Score Worksheet. Swap the Rows and columns. In the Label put the alignment at the Right then make your chart in descending order.

Step 7. On the column pill click the arrow in the AVG (Annual Return) and choose Compute Using Fund. Duplicate your worksheet.

Step 8. Create a Box and Whisker Plot using “Show Me”.

Z values range from a low of -1.80 to a high of 2.19. Meaning, the return for stock A is about 1.80 standard deviations below the mean, whereas the return for stock O is 1.0528 standard deviation above the mean.

Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)