1 / 26

Introduction to Use of Pivot Tables

Introduction to Use of Pivot Tables. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com. What do pivot tables do? Guide to creating pivot table (easier than Dummies’ version). Sample uses for fraud detection:

gray-gould
Download Presentation

Introduction to Use of Pivot Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Use of Pivot Tables 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  2. What do pivot tables do? Guide to creating pivot table (easier than Dummies’ version). Sample uses for fraud detection: journal entries, accounts payable Where to go for more information 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  3. Pivot Tables are a powerful Excel tool that allows you to: SUMMARIZE DATA DISPLAY THE SUMMARIZED DATA ANY WAY YOU WANT TO ANSWER YOUR QUESTIONS. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  4. Spreadsheets Flat 2 dimensional Un-summarized The crushing amount of detail hides patterns 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  5. EXAMPLE OF MIND NUMBING SPREADSHEET (hypothetical data about conference attendees) 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  6. A Pivot Table Summarizes & Displays Patterns in this Data At a glance, this pivot table shows that about 2/3s of attendees had attended previously. The group with the highest percentage of new attendees is private industry. This allows us to explore why the conference was more successful in bringing in new attendees in this group and to try to use this success in bringing in new attendees from other groups. This is a simple illustration of how pivot table highlight differences for norm—which can be good or bad. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  7. The Same Date Sliced Differently to Ask Why Attendees Came This summary of why attendees came allows you to develop theories about why so many new attendees working in the Public Sector came. They came for “Specific Content. “ In contrast those working in Government came because they needed “Cont. Ed Hours”, while those doing Outsourced Work came for Marketing opportunities. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  8. Pivot Tables Are Great Because The data is summarized and easy to understand. Just like the Veg-O-Matic –> It slices!!! It dices!!! 3 Dimensional view of data -- Tabulates of numerical & categorical variables Pivot tables show you data patterns and anomalies. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  9. How to Create a Pivot Table The single most difficult aspect of creating a pivot table (if you have never done one before) is figuring out where Microsoft hid the Pivot Table Wizard in your version. In older versions (with drop down menu), it is on the Data tab. In newer versions, it is on the Insert table. Hint: When all else fails, perform a search for Microsoft Excel Pivot Table 20XX (your version: 2000, 2007, 2010 etc.) The Microsoft pictures are worth a thousand words. The keyboard command is Alt D + P 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  10. Step by Step How To Get the data you want to analyze. If it is not already in Excel, export it from your accounting program. Make sure your data is clean, i.e., free from gaps, carriage returns, quotation makes. Active Data makes this easier to accomplish on bigger files. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  11. 4. Click here.  10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  12. 5. Verify that the Wizard selected the correct data range. If it did not, there may be an unnoticed gap in the data. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  13. 6. Check at the variables you wish to examine in the larger upper box. 7. Drag the label you wish displayed as a column from the small lower left box (row labels) to the small upper right box (column labels). 8. Drag the variable to be tallied to the lower right box (sum values). With right click you can change from sum to count, average, etc. Hint: To get the format box back, put your cursor in the Pivot Table. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  14. Fraud Detection Use pivot tables to analyze: Journal entries Account Payables Payroll entries 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  15. Journal Entry Analysis Include the following elements in your data capture: Who: User ID, Approver ID What: Debit & Credit Amounts, Explanation/Description When: Entry Date and Effective Date Where: Journal Entry #, Account #, Cost Center # How: Manual or Automated 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  16. You can examine anything you would look at manually. In using a pivot table, sum by account, month, user, etc. to get the pattern. The low hanging fruit to examine includes: Entries in which the User ID and the Approver ID are the same Entries made by non-authorized personnel Entries to unusual accounts Entries made on unusual dates or times Entries with anomalous effective dates 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  17. Illustration of Journal Entry Analysis (Inappropriate approvals) In Combination with Demonstration of “Slicer” to Filter Results 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  18. From the workbook of journal entries, select “user”, “approver” and “date.” Drag the entries to the desired column, row, and value area. With your cursor in the pivot table so the pivot table tools are displayed, click on “Insert Slicer” From the drop down menu, select the variable you wish to filter by. In this example, “approver” was selected. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  19. By selecting the blue bar labeled Garrad, the slicer filter show just the approvals he made. It can be seen he appoved his a JE for he made. Greta also approved a JE she made. Self Approval of JE & Demo of Slicer Tool Continued 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  20. Illustration of an Accounts Payable Analysis In Combination with a Demonstration of Import of a Text File 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  21. From the Data Tab, select Import text, browse to text file & select file. The Wizard opens. Click “next” in all screens unless you need to change the delimiters in step 2. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  22. This Pivot Table was created by selecting Vendor and Check Amount. Vendor Count was added by dragging Vendor down to the Sum area. The Check amount is sorted from largest to smallest. Accounts Payable Illustration 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  23. Clicking on a box in the “Sum of Checks” on previous screen gives the detail for the entries. This is the Vendor with the largest total. The Power of Drill Down 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  24. Another Drill Down This is the drill down for a Vendor with a large total but only one check. This detail might make you say Hmm. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  25. Summary of this Segment Pivot tables display and summarize. They allow you to filter and drill down. The displays are mostly limited by your imagination. Wizards will get you there. Pivot Tables are especially useful in analyzing Journal Entries, Accounts Payable, and Payroll. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

  26. Best Advice Go Play When you get stuck, google the terms you are stuck on and you will find a You Tube video or a Microsoft lesson or a blog post that explains exactly what you want to know. Mastery comes from practice alternating with training. After you gain basic mastery through play, go for more training. 10940 Wilshire Blvd. Suite 1600 PMB 580 Los Angeles, CA 90024 Office 424.222.7770 www.ForensicAccountingAndValuation.com

More Related