Solution to Microsoft Excel: a Self Assessment Exercise 1

Solution to Microsoft Excel: A Self Assessment Exercise[1]

The easiest way to start is to open up the assignment file in Word, and then copy and paste the data into an Excel spreadsheet. Alternatively, you could enter the data by hand.

1. Save your workbook under the name Self_Test_Data.xls
2. Compute a new column of the differences between the O’Brian and S&P returns.
3. Save your workbook under the name Self_Test_Anyl.xls (Use the Save As option.)
4. Use the appropriate Excel functions to compute the average, maximum and minimum of all three columns of numbers

After these steps are completed, your spreadsheet ought to look something like this:

1. Use the Excel Chart Wizard to create a time series plot of the O’Brian and S&P returns.

The Chart Wizard is a great tool, but you need to play with it a little and learn how to make it do what you want. First, select the data you want to appear in the chart:

Then, start the Wizard by selecting Insert—Chart:

In this case, we want a line graph, so we choose the chart type "Line":

As we click "Next" and proceed through the Wizard, we get a preview of what the chart will look like. So far it isn't very encouraging:

So far, the chart includes a line for "Year", which we don't want. The lines for O'Brian and the S&P are way down near the horizontal axis.

To get rid of the "Year" line, click on "Series":

This is a little tricky. We want to get rid of the "Year" line in the graph, but we want "Year" to appear on the horizontal axis in our chart. So we

(a)copy the cell reference for "Year" (where it says: ='Raw Data'!$A$2:$A$11)[2],

(b)click "Remove" to get rid of the unwanted line, and then

(c)paste the cell reference for "Year" into the "Category (X) axis labels" box for one of the other two variables.

Clicking "Next" leads us to a dialog box where we can write the titles for our chart:

Finally, we are offered some choices about where to place the chart. Sometimes it's nice to have the chart in the same worksheet as the data, but I have found that it is easier to use the chart in other programs (e.g. Word, PowerPoint) if we save it in a separate worksheet:

The chart will come out looking something like the one on the left below. It is recommended that you learn how to manipulate the elements of your charts, such as was done to produce the "improved" version on the right.

The view was changed to "Sized with Window".

The window was re-scaled to a pleasing shape.

The chart border was changed from gray to black, and the chart background was changed from gray to white.

The main title was changed to 14-point type, the axis titles were changed to 12-point type, and the scale markings along the axes were changed to 10-point type.

The legend was moved into the body of the chart, and the size of the chart expanded in all four directions.

The colors of the lines were changed, the lines were thickened, and the data point markers were changed from the Excel defaults to solid circles.

After working through the Excel Chart Wizard, you can copy the chart and paste it into Word. There are several ways to do this, but these are the steps used here:

Click on the chart so that eight little black squares appear around the outside of the chart:

Copy the chart using CTR+C. Then, switching to Word, click on Edit—Paste Special:

I chose "Picture" as the format, and unchecked the "Float over text" option.

1. Copy your data to another worksheet in your workbook.
2. Sort the copied data by increasing magnitude of the difference in returns.
3. Re-sort the data back into original order.

To sort by the magnitude of the difference, you need to add a new column that gives the absolute value of the difference. The Excel function "ABS" will do that for you. Here's what it looks like after you've sorted by the difference:

1. Use the Excel Chart Wizard to create a scatter plot of the O’Brian returns versus the S&P returns.

The scatter plot chart is made using most of the same techniques as the line chart. Here are two versions:

1. Use the Histogram Tool (in the Data Analysis Toolpak) to create a frequency histogram of the O’Brian returns.

Excel has many features that are useful for statistics. The default histogram feature is not one of them. To make a useful histogram requires bypassing most of the defaults and doing it yourself.

Here's how we made this histogram:

Set up a column with upper limits for the ranges into which you want to divide your data (these ranges are sometimes called "bins" or "buckets"). If you don't do this yourself, Excel will use some bizarre algorithm to come up with its own ranges, and they are usually confusing.

Select the data you want to use for the histogram. Then click on Tools — Data Analysis — Histogram, and fill in the boxes. The input range is the set of data you want plotted. The bin range is the set of cells containing your pre-determined range boundaries. The output range is where you want the data for your histogram to appear (these data will not be the raw data, but rather a count of how many observations appear in each of your ranges). There is not much point in asking Excel for chart output, so we leave that box unchecked:

Clicking on "OK" yields output like this:

To make a useful chart, we need to re-arrange this output as shown here:

The reason for doing this is that the "Bin" column isn't really a good set of labels for the horizontal axis in our histogram chart. These are the upper boundaries of the ranges we will use, and Excel will place the labels in the center of the range on the chart. This is misleading to people reading the chart, so we make up our own labels as shown above in column E.

Then we can go through the Chart Wizard, making a "column" chart, and using the data in F5:F13 for the values and E5:E13 for the labels.

Once again, we need to make a few changes to the format of the chart. Here, we selected the bars and decreased the gap between them to zero. We also got rid of the legend; a legend isn't of any use in a chart when there is only one variable.

1. Save your workbook.
2. Print your results and close your workbook.
3. Which investment looks superior, O’Brian or the S&P Index?

The decision as to which is superior is somewhat subjective. On one level, the two investments don't seem that much different from each other (look at the first chart again). When the S&P has a good year, O'Brian usually has a good year; when the S&P has a bad year, O'Brian usually has a bad year. This "moving together" behavior can be quantified using a statistic called "correlation"; we will deal with correlation later in this course.

The O'Brian fund does have a higher average return than the S&P (note the average of 20.9% vs. 11.1%), which suggests that the investor who wants to maximize his or her expected return would prefer O'Brian to the S&P.

But O'Brian also seems a little riskier (the S&P had negative returns twice in 10 years; O'Brian had negative returns three times).

One way to look at risk is to study the range of returns for each of the two investments. The range is calculated by subtracting the minimum from the maximum. For O'Brian the range is 51% - (-5%) = 56%; for the S&P the range is 27% - (-7%) = 34%. An investor who is interested in minimizing risk would prefer the S&P over the O'Brian. In this course we will use other measures of risk, primarily a statistic called the standard deviation.

Ultimately, this exercise illustrates both the potential usefulness of statistics as well as its limitations: In this example, the investor still needs to make a choice between higher returns and higher risk. Charts and statistical measures of risk and return can help us understand the differences between two possible courses of action, but they can't replace judgement.

Managerial Statistics1Prof. Juran

[1]This solution is based on "Microsoft Excel: A Self-Assessment Exercise", by Professor Peter Kolesar, Columbia Business School. This solution was written by David Juran (7/00).

[2] Note: As you increase your proficiency with Excel, it is a good idea to learn what these cell references are saying and how to edit them. The "Raw Data" here is a reference to the name given to the worksheet with the data; yours might say something different, like "Sheet1".