Using Excel to Prevent and Detect Fraud-Part 1
Registration Fee – $49
June 24, 2021 – 2:00 pm – 4:00 pm – (2.0 CPE)
Level: Basic – Basic/Intermediate
Prerequisites: A good understanding of Excel basics
Advance Preparation: None
Field of Study: Fraud/Technical
Presented by: Jim Colville, CPA Retired, CFE
Handout Materials: All files and notes available for download. See below
Description:
This is the first of four sessions on Using Excel to Detect Fraud. In keeping with our goal of not taking you away from your work all day, we have found the two hour sessions work better in the attendee’s schedule. Additionally, the concepts are presented from standpoint of fraud detection and prevention first, and the Excel tools to accomplish this second. As a result it is considered a Fraud topic qualifying for Fraud CPE as well as Technical CPE.
Excel’s robust list of features have most all the tools needed for preventing or detecting fraud. The real issue is which ones to use and when and which ones give the best results. This session will layout different fraud scenarios and Excel approach and features to tackle the issue. Certain features will be presented as generic Excel topic and then how to use them in the fraud challenge. Its one thing to find fraud or other anomalies but providing a winning forensic accounting / expert witness report is another. Exercises will show techniques from the spreadsheet analysis to the final report. Addition technical techniques will be presented to accomplish Zero Time Reporting; once the data is entered, imported or otherwise updated, the report is done.
- An overview of fraud detection and prevention. Key concepts, with and without Excel, and various approached to the many scenarios we are faced with
- Examine data using data visualization tools and techniques
- Transform the visualization results into range or tables for further analysis or printing
- Tools and techniques to isolate duplicates. This can be duplicate names or companies, duplicate records, disguised duplicates or other deceptive data
- Tools and techniques to determine unique data
- Normalizing and cleansing data to ensure good data for analysis
- Comparing uncomparable data (such as last four digits to the full number or data with voluminous errors)
- Techniques in protecting sensitive data in your spreadsheets
- Many of the Excel features presented you may already know but they will be enhanced for fraud examples and presented at the advanced or creative level
- Working with files with known fraud to determine the extent of the fraud or that fraud was not present
- Techniques to make your data ready for a report. Zero Time Reporting tools will be presented
- Techniques and ideas to help you with enhancing your skeptical mindset
The files used in presenting these topics will be available for download. The files will contain instructions on how to use the concept which includes the presenter’s notes. The files will remain available online for about two weeks.
Jim Colville, Bio
Jim began working with spreadsheets with Lotus 1-2-3, v1a and then with Excel v3, and soon thereafter v4. He learned very early the power of Excel and how to be super productive in solving problems, creating business solutions and enhancing reporting in his CPA practice.
Jim’s experience comes from working as CPA and CFE (Certified Fraud Examiner) in international CPA firms as well as his own practice. He has recently retired as a CPA, keeping the CFE active, and is preparing and presenting education course, currently webinars and live, once COVID is behind us.
His experience with detecting fraud using spreadsheets began within a week of purchasing Lotus 1-2-3 (a long time ago) and has been ongoing ever since. Many of his fraud and forensic accounting projects have been high profile.
Seeing the business opportunity in educating others in using Excel he began his own PC training company teaching over 20,000 users over a period about four years. The training evolved into a consulting company offering specific solutions to companies and corporations by combining training combined with project challenges which resulted in powerful solutions. A by-product of this is Jim’s Zero Time Reporting, once the data is entered (or imported) the report is done.
Jim has authored well over Excel 500 courses, both as training materials and solution based Excel products for companies, corporations and nonprofit organizations. Many of these later became the foundation of web based articles and solutions under the the name of Info-Stat.com . With the saturation of Internet based Excel websites and Jim’s commitment to his CPA business the Info-Stat website was allowed to sunset. With his retirement from his CPA practice in 2019, Jim has revitalized his web-based activities in by writing articles, creating Excel files that offer solutions, revitalizing Info-Stat.com and more importantly, educational webinars through AEI. Excel has changed dramatically over the years and the new course and webinar offerings significantly modernize the Excel materials including, but not limited to, business and artificial intelligence.
Of major significance is the evolution of Jim’s education activities from a local San Diego PC training company to a national and world wide based company with Internet based products, both website and webinar offerings.