Testing for relationships between numeric data contained on an Excel spreadsheet is a simple audit test that can be performed in just minutes. An common example would be to test if there was a relationship between invoice amount, discount and sales tax. Assuming that most items were taxable, there should be a fairly strong relationship between the amount of the invoice and amount of sales tax. This audit exercise is based upon survey data results compiled by the National Science Foundation. The measures are socioeconomic infrastructure, technological infrastructure and productive capacity. To perform the audit procedure, follow these steps:
1. Download the Excel workbook and select the first sheet, which is named at0609.
2. Select the range $A$6:$E$39, and copy it to the clipboard (Ctrl-C or Edit|Copy).
3. Open EZ-R XL and click "Paste". If you don't have EZ-R XL, you can download a full working copy at no cost.
4. For each of the column headers, type over the description in order to provide a unique column name.
5. Double-click each of the cells in the "Selected" column next to the column names for which regression statistics are desired. (You will then see an "X" marked in the column named selected).
6. From the drop-down menu on the left, select "linear regression".
7. Click the "Run" bottom at the bottom of the dialog.
8. You should now see the linear regression results displayed for each pair of variables being tested. The color of the results displayed denotes the strength of the relationship - light green indicates a strong relationship, yellow a weak relationship and pink indicating little or no relationship.
9. Document the results in the work-papers, including the data source, coefficient of correlation and perhaps a print out of the chart.
Below is the end result that you should have obtained...
This analysis shows that the strongest linear correlation is between the productive capacity and the technological infrastructure, with a score of .89 (coefficient of correlation).
There is also a very similar procedure available which does not require the installation of software. This procedure can be run directly from the Internet. Details and a help file are available.
Note: the display created is html code (not a png graphic).
You can leave (anonymous) comments, if you wish, at our audit blog.
Should you need further detail results and statistics on this audit procedure, it is recommended that you use EZ-R XL for Windows, which works in a similar fashion.
There is also a web analytics version which can be run directly from the Internet using Excel or other data source (in tab separated value format). View Web Analytics details. All software is provided at no cost.