1 / 46

Improving Internal Controls Using Excel 2010 New Features

Presented March 10 , 2011 to the Litigation Services Committee of the NYSSCPA. Improving Internal Controls Using Excel 2010 New Features. Marc Engel, CPA, CISA, CFE Director, CFO Consulting Partners LLC mengel@cfoconsultingpartners.com 973-953-8569 . Key Discussion Points.

tal
Download Presentation

Improving Internal Controls Using Excel 2010 New Features

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. Presented March 10, 2011 to the Litigation Services Committee of the NYSSCPA Improving Internal Controls Using Excel 2010 New Features Marc Engel, CPA, CISA, CFEDirector, CFO Consulting Partners LLCmengel@cfoconsultingpartners.com 973-953-8569

  2. Key Discussion Points • (1)__What is the additional internal controls value of Excel 2010 to my organization? • Deeper levels of analysis – A Billion (!!) rows in a single file. (On my puny computer?? LOL) • New types of reports using Slicers • Easily show trend analysis using Sparklines, •  (2)__ Pivot Tables and PowerPivot tables • (3)__New info features: “Backstage”

  3. Introduction: XL 2010 Helpful Features Auto Filter improvements Conditional Formatting Standard Pivot Tables and Charts

  4. Filters further improved over XL 07:Now with Search Box in the drop down filter

  5. Auto Filters in XL 2007 Were greatly improved. Checkboxes by each item in the field. Allow multiple selections with great specificity. Sort / filter by Color: If your spreadsheet is color coded you could hone in on trouble quickly. Especially using conditional formatting. Filters drop down Detects if the field is numeric, date or text and gives those options. On the main dialog, so no need to go to custom; it takes you there with the right options.

  6. Custom Auto filter Dialog box • STILL!! No change from ‘03. Still limited to 2 criteria.

  7. Conditional formatting – Huge Improvements from ‘03 • Multiple conditions – No limit (‘03: only 3) • Increased types of formatting rules • Format only Duplicate (or unique) values • Note: the new filters permit filtering by color so combining these two features permits easy isolation of duplicates. • Format only top or bottom rank • Assign icons (traffic lights, arrows, flags) that appear in the cell. Then filter by icon.

  8. Conditional formatting – editing the Formatting Rule - dialog box Select the type of rule you will use. Note the duplicate values option.

  9. XL 2010 Conditional Formatting Data Bars

  10. XL 2007 Conditional FormattingData Bars Had data bars but not gradient data bars

  11. XL 07 Pivot Table Improvements Pivot table field list – easier to use than the “wizard” of 2003. You check the fields you Want in the table, drag them Into the box for column label, Row label, sum values.

  12. XL 07 Pivot Table Improvements Plus and minus buttons for fields. Here, clicking the minus next to 2006 will eliminate all individual months from view.

  13. XL 07 Pivot Table Improvements Plus and minus buttons for fields. After clicking the minus next to 2006 it has changed to a Plus sign. Clicking it again will restore all individual months to the view.

  14. XL 07 Pivot Table Improvements If only it could give be a flat file pivot table!!! Checking off customer name added the field to the column label.

  15. XL 10 New Features New feature: Sparklines – In-Line charts Pivot Table of aged receivables

  16. Pivot Table ImprovementsFrom XL 07 Filters work just as in auto filters. You can check off the items you are looking for. The filter Symbol is Activated.

  17. Pivot Table ImprovementsNew to XL 10 Typing “ac” immediately filtered the Pivot table and the filter checklist on the fly!! Filters have integrated search box

  18. Pivot Table ImprovementsNew to XL 10 Here is the full drop down before applying filter. Note: drag handle to expand the box.

  19. Pivot Table before applying filter SPARKLINES SHOW TREND

  20. Pivot Tables Filtering also filters the chart and sparkline

  21. Pivot Table NON Improvements KEY POINT: Date grouping ONLY WORKS when EVERY SINGLE row has a valid date. Reminder: when summarizing by date NO NON-DATES ARE ALLOWED IN THE FIELD AND NO BLANKS. YOU WILL NOT GET AN ERROR MESSAGE ABOUT MISSING DATES, BUT THE SUMMARY WILL NOT WORK.

  22. You are now ready for PowerPivots • What is PowerPivot? • An intermediary between Excel and databases that reside in your firm’s IT. • Why use it? • It can handle huge datasets that Excel cannot. • It is designed to link with SQL databases, and other platforms, for easy download. Thus your data can be real time, and you analyze it without IT support (other than linking).

  23. Accessing Power Pivot The Client version is free. Download it off the internet. WWW.powerpivot.com You need to run the right version 32 bit or 64 bit, compatible with your V. of Excel. See below for the PowerPivot menu

  24. Accessing Power Pivot Clicking that menu brings you to the PowerPivotMenu. Click PowerPivot Window Launch

  25. Data Source Options

  26. Import Completed Note: No row and column references Note: record number at bottom of screen

  27. To summarize by year add years column Formulas are called “Measures” in PowerPivot. NOTE: Only 1 formula per column!! It affects the whole column! PowerPivot pivot tables do not permit grouping, (Worse than regular pivot tables) Solution: Add a column for Year, Month

  28. Here is the pivot table from the Powerpivot window

  29. The linked table Original Excel file This is the original linked table IN EXCEL. Tables can be linked externally.

  30. The Linked Table in PowerPivot New tab in the Powerpivot window Linking the table brings it into a new tab in PowerPivot

  31. Powerpivot window before adding “Division Head” Linked field

  32. With field added from linked table Improved!! How simple the formula is vs. VLOOKUP

  33. Four Tables from the PowerPivot window

  34. Benford Analysis KEY CONCEPT: The PowerPivot Pivot Table has all the fields from the FOUR tables!!!!!

  35. Slicers: the new way to analyze pivot tables With all buttons selected 2 slicers for 2 different factors. More can be added as needed.

  36. Slicers Activated Hold the shift and control keys to show only the data you need to see. Note that the same series of slicers controls both the table and chart. 11]

  37. Here the month slicer filters for Nov & Dec sales although it is not even a category on the Pivot Table.

  38. Format the slicers with Slicer Tools Options

  39. Other New Features New Backstage view

  40. Excel 2007 – Office button- removed aaaaaaaa

  41. IMPROVED: Pinned items get sorted to the top of the list. Recent places can also be pinned. XL 10 uses the “File” tab for the “backstage” features: More recent files, their locations, and recent places

  42. See even more versions

  43. Forms Command

  44. Forms in XL 2010 More Fields available vs. XL07

  45. Forms command is accessed as per below

  46. Questions? Contact me at: mengel@cfoconsultingpartners.com Marc.engelcpa@gmail.com 973-953-8569

More Related