How to Use Excel for Auditing

By Drew Nelson

i BananaStock/BananaStock/Getty Images

Excel has many useful features for auditors. The ability to put data into a spreadsheet and perform different tests and analysis makes Excel a powerful audit tool. It is simple to use and readily available to most auditors. Excel can perform many types of analytical tests including: Benford’s law, horizontal and vertical analytics, ratio analysis, regression analysis, statistics, stratification and aging. It can also perform many types of data analysis, including: append and merge, cross tabulate and pivot table, detect duplicates, and gaps, extract and filter, join and relate, sample, sort, summarize and subtotal (Reference No. 1).

Step 1

First obtain the data from the entity being audited. This may involve simply having access to the accounting system or it may involve downloading data for testing. Every audit involves running tests on accounting transactions. While Excel is limited to 65,536 rows, data sets can often be summarized down to a "spreadsheet size," allowing Excel to be used for analysis purposes (Reference No. 1). A good example of this is ratio analysis of financial statements.

Step 2

Create statistical samples from the data. Sampling allows you to perform tests on groups of data that are representative of the data population. For example, you can select a sample of paid invoices to test that represents the entire population of the paid invoices. If there are problems with the sample this is an indication of problems with the entire population and you can expand you testing as needed.

Step 3

Export your sample into an Excel spreadsheet. In excel you can run a variety of tests on your sample and set up templates to be used on other similar audits. Once you have your templates set up it is easy to use the same templates again with new data, you don’t have to reinvent the wheel each time.

Step 4

Run the tests on your sample data. There are two types of audit software tests, those that are analytical in nature and those that are focused on applying analysis to the actual detailed data. Analytical Tests are evaluations of relationships between different components of financial and non-financial data to assess whether account balances appear reasonable, for example ratio analysis. Transaction testing is an example of detailed data analysis. The results of analytical and detailed testing can be recorded in the Excel spreadsheets with the data to which it applies.

Step 5

Form conclusions from your testing and analysis and reference your Excel spreadsheets as documentation. Where problems or findings are identified reference the data on the Excel spreadsheets to copies of the detail that document the problems or findings. Excel spreadsheets can be used as lead sheets to organize your work papers with supporting detail lined up behind each spreadsheet.

References

Writer Bio