Power tools: 2002 audit software usage survey; three avid users talk about how they use top-rated software products to automate their most important tasks. Plus, the results from the IIA's annual poll of members' application preferences are revealed

Internal Auditor, August, 2002 by Christy Chapman

In addition, we use the counting feature in Excel, which again is a basic function that probably every user knows how to do. But it is extremely useful, because it can show the number of instances of occurrence, rather than just providing the total numerical value of those occurrences. Excel also enables us to perform various types of statistical calculations, such as determining the mean and standard deviation, which helps us identify data that falls outside the norm and may require additional investigation.

We also use Excel's many helpful logic features quite extensively. For instance, suppose, as part of a payroll audit, we need to review pension and annuity withholdings, which can vary quite a bit depending on the plan and employee classification. The "if" function is useful for verifying whether the withholdings have been performed correctly and are within certain cutoffs. The user can set up a database table and have Excel "look up" information in that table, and make decisions based on it. For example, if the employee classification is Z, the table will show that the amount to be withheld should be X percent, and Excel can determine whether the correct amount is being withheld from that employee's paycheck.

CONNECTING INFORMATION

The ability to link computations between spreadsheets is another big time-saver for us. Suppose that, on worksheet A, the user totals a column of data and arrives at a bottom line number that is subsequently used in worksheet B. Instead of replicating the entire worksheet A in worksheet B, we can link that bottom line number to worksheet B. Then, when worksheet A is altered in any way, worksheet B will be updated automatically. This feature is extremely helpful when analyzing data to consider various scenarios. Anytime the user looks at a different scenario and changes a value, all of the worksheets linked to that value are updated.

A situation where Excel proved particularly useful for scenario modeling involved our food and nutrition department. In a hospital environment, food service must be provided 24 hours a day, seven days a week. As a result, food and nutrition management routinely had to schedule overtime for its employees. Because we are a union shop, there were many premiums attached to this practice, including paying time and a half for overtime and double time for holidays. Using Excel, I was able to prove that it was more cost-effective to hire several additional full-time employees for the extra duty rather than continuing to use existing staff or hiring temporary, per diem workers.

INTER-APPLICATION COMPATIBILITY

Excel also enables us to transfer all of our data among other office-suite applications. In the old days, we would write reports in our word-processing program and then attach a worksheet to the hard copy or include it as a separate file within an e-mail. Now I find it much easier to just cut and paste. We can take a snapshot of a table in Excel and paste it directly into a Microsoft Word document, and it looks great. The software provides various options for cutting and pasting, and they are all quite simple to perform. We can actually link the sheet, or import it as a bitmap image. We can make the worksheet data float with the text, or anchor it in place.