Herramientas de excel

This content was specifically designed for use and free distribution by

A community of professionals helping you to maximize the benefits of audit software

Using Excel as an Audit Software
By Richard B. Lanza, CPA/CITP, CFE, PMP www.auditsoftware.net

TABLE OF CONTENTS
COPYRIGHT PAGE DOCUMENT PURPOSE / CALL FOR FEEDBACK LIMITATIONS OF EXCEL AS AN AUDIT SOFTWARE AUDIT SOFTWARE TESTS 3 45 6 6 6 7 7 8 8 8 10 11 11 12 13 14 15 16 17 20 21 23 23 23 24 28 29

1. Analytical Tests
1.1 Horizontal Analysis 1.2 Vertical Analysis 1.3 Ratios 1.4 Trend analysis 1.5 Performance Measures 1.6 Statistics 1.7 Stratifications 1.8 Aging 1.9 Benford’s Law 1.10 Regression 1.11 Monte Carlo simulation

2. Data Management/Analysis Reports
2.1 Append/Merge 2.2 Calculated Fields/Functions 2.3 CrossTabulate 2.4 Duplicates 2.5 Extract/Filter 2.6 Export 2.7 Gaps 2.8 Index / Sort 2.9 Join / Relate 2.11 Summarize ABOUT THE AUTHOR

Using Excel as an Audit Software © 2006 - Richard B. Lanza

2

Copyright Page
© 2006 - 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 ofWarranty 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 beliable 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 © 2006 - Richard B. Lanza

3

Document Purpose / Call for Feedback
The purpose ofthis document is to assist auditors, fraud examiners, and management in implementing data analysis routines using Microsoft Excel. It is hoped that through the dissemination of this new information that more analysis will be done using audit software to prevent and proactively detect organizational inefficiency, ineffectiveness, and fraud. Please note that although written for auditors, these testsmay greatly assist the business community at large as it is currently estimated that there are 400 million users worldwide. This document is not expected to explain Microsoft Excel concepts at length but rather to provide guidance as to which of the product’s features can be used in an audit setting. For more extensive documentation on the use of Microsoft Excel, please see the Help feature in theproduct and/or the publications section of the www.microsoft.com website. With this document, users can no longer say that audit software is difficult. For now, even a tool as simple as a spreadsheet can and should be used as an audit software. Many readers have noted that this document showed them what is possible with audit software in analogous terms they could understand. With the “curtainexposed” users can now focus their time on the more critical activities of creatively dreaming up new tests and improving their business intelligence. For more information on the use of audit software, and countless ways of applying it to your business, please see www.auditsoftware.net. If you would like to provide feedback on the document, we welcome and encourage it as we plan to complete laterversions. Please provide your feedback via Email at questions@auditsoftware.net

Using Excel as an Audit Software © 2006 - Richard B. Lanza

4

Limitations of Excel as an Audit Software
Although Microsoft Excel has many powerful features and can take on many of the features of an audit software, it has its own set of limitations that are presented below: • Can only process 65,536 rows or...