This content was specifically designed for use and free distribution on
A community of professionals helping you to maximize the benefits of audit software
Using Excel as an Audit Software
By Richard B. Lanza, CPA, PMP
Using Excel as an Audit Software
© 2003 - Richard B. Lanza
2
TABLE OF CONTENTS
COPYRIGHT PAGE
3
DOCUMENT PURPOSE / CALL FOR FEEDBACK
4
LIMITATIONS OF EXCEL AS AN AUDIT SOFTWARE
5
AUDIT SOFTWARE TESTS
6
1. Analytical Tests
6
1.1 Horizontal Analysis
6
1.2 Vertical Analysis
7
1.3 Ratios
7
1.4 Trend analysis
8
1.5 Performance Measures
8
1.6 Statistics
8
1.7 Stratifications
10
1.8 Aging
11
1.9 Benford’s Law
11
1.10 Regression
12
1.11 Monte Carlo simulation
13
2. Data Management/Analysis Reports
14
2.1 Append/Merge
15
2.2 Calculated Fields/Functions
16
2.3 Cross Tabulate
17
2.4 Duplicates
20
2.5 Extract/Filter
21
2.6 Export
23
2.7 Gaps
23
2.8 Index / Sort
23
2.9 Join / Relate
24
2.11 Summarize
28
ABOUT THE AUTHOR
29
Using Excel as an Audit Software
© 2003 - Richard B. Lanza
3
Copyright Page
© 2003 - Richard B. Lanza
No part of this publication may be reproduced in any form without permission in writing from Richard B.
Lanza.
Limitation of Liability / Disclaimer of Warranty
The author, Richard B. Lanza, has used his best efforts in preparing this publication and is not responsible
for any errors or omissions. He makes no representations or warranties with respect to the accuracy or
completeness of the contents of this document and specifically disclaim any implied warranties of
merchantability or fitness for any particular purpose, and shall in no event be liable for any loss of profit
or any other financial or commercial damage, including, but not limited to, special, incidental,
consequential, or other damages.
Microsoft Excel is the trademark of Microsoft. All other trademarks are the property of their respective
owners.
Using Excel as an Audit Software
© 2003