How to generate statistical analysis using Excel

Create a histogram
If you would like to group the students’ results and know the number in each group, you can do that with a histogram. In cell I19 type ‘Bins’. In the range I20 to I23 enter reasonable groupings. For example, if the student’s totals go from 202 down to 132 then you could enter 120, 150, 180, 210.

On the Data menu (or under the Data tab in Excel 2007), choose Data Analysis and then Histogram. The Input range, using the present example, is H19:H34. The Bin range is I19:I23. Check the Labels box, New worksheet, Pareto, Cumulative Percentage, and OK.

As the outcome will be displayed on a new worksheet, it’s easier to adjust the formatting and not mess up the column widths on the existing worksheet. The histogram shows that five of the 15 students, or 33 per cent, scored between 120 and 150; four, between 150 and 180 (a cumulative percentage of 60 per cent); and six between 180 and 210.

A few explanatory notes might be of interest. A cell reference such as A1 is called a relative reference as it can change if copied and pasted elsewhere. A reference such as $A$1 is called an absolute reference and won’t change.

If the reference is $A1 then the column reference won’t change but the row reference will. You can quickly change A1 to $A$1 by selecting it in the Formula Bar and pressing F4. This function key will cycle through the combinations: $A$1, A$1, $A1 and back to A1. Knowing this enables you to create formulas which can be dragged along rows and down columns to get the results you intend.

For example, when you entered =$K3*L$2 in cell L3 it meant that when dragged to other cells the column in the first reference and the row in the second could change. This meant that all 24 cells in the range from L3 to Q6 would receive the correct formula.

Taking stock
Newspaper listings for popular stocks will usually give the latest price at press time, the high and low price for the year, the Yield, and the Price Earnings ratio.

A useful daily snapshot, but a spreadsheet can be more helpful.

The important statistic in financial analysis is the trend of a company’s performance. If you extract the results from a company’s annual reports for the past five years and compare them on a spreadsheet the trend becomes clear.

An investor’s yield can be calculated by entering the total of dividends paid by the company in a worksheet cell, and the amount paid for shares in the company (say, 100 shares at £10 a share) in a second cell. In a third cell divide the dividends by the stock investment and format the cell as a percentage.

The price-earnings ratio is the price of a share of the stock divided by after-tax earnings per share. This differs from the yield because the management will invest some of the profits in research and development, or expansion of facilities and only give a fraction of the profits to their stockholders.

Enter in a cell the aggregate value of the company’s stock, known as the market capitalisation (MCAP). In a second cell, enter the last recorded annual profits or after-tax earnings. In a third cell, divide the MCAP by the earnings.