1 / 32

Excel from an Audit Perspective

Excel from an Audit Perspective. Excel 2007. Patricia McCarthy www.CPASelfstudy.com. Areas of Discussion. Quick overview of Excel 2007 New Features Shortcuts Using Excel Fraud Audit Financial Records Audit Questions. Just How Large is Excel 2007?. 1,084,576 rows 16,384 columns

alicia
Download Presentation

Excel from an Audit Perspective

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. Excel from an Audit Perspective Excel 2007 Institute of Internal Auditors – Indianapolis Chapter Patricia McCarthywww.CPASelfstudy.com

  2. www.CPASelfStudy.com Areas of Discussion • Quick overview of Excel 2007 New Features • Shortcuts • Using Excel • Fraud Audit • Financial Records Audit • Questions

  3. www.CPASelfStudy.com Just How Large is Excel 2007? • 1,084,576 rows • 16,384 columns • The last column is XFD • 64 Nested Ifs instead of 7!

  4. www.CPASelfStudy.com Introducing the Ribbon • The menu bar is gone • Ribbon • Comprised of 7 tabs • Each tab is subdivided into 7 groups

  5. www.CPASelfStudy.com

  6. www.CPASelfStudy.com Names Workbook level or sheet level Name Manager – displays while doing a calculation

  7. www.CPASelfStudy.com Where are the Open and Save Commands? Alt+F opens the Office Button icon Press Alt to display shortcut keys

  8. www.CPASelfStudy.com Any Printing Changes? Where the heck is Print Preview? –

  9. www.CPASelfStudy.com What is a QAT? • Quick Access Toolbar

  10. Customize the QAT www.CPASelfStudy.com

  11. www.CPASelfStudy.com

  12. www.CPASelfStudy.com Excel Options >Popular

  13. www.CPASelfStudy.com Excel Options > Advanced

  14. www.CPASelfStudy.com Enhanced Features of Excel 2007

  15. www.CPASelfStudy.com Filtering and Sorting

  16. www.CPASelfStudy.com Sorting

  17. www.CPASelfStudy.com Conditional Formatting – Visual Effect Data Bars

  18. www.CPASelfStudy.com Conditional Formatting Highlight Cell Rules

  19. www.CPASelfStudy.com Top/Bottom Rules

  20. www.CPASelfStudy.com Alternate Shading= Legibility =mod(row(),2)=0

  21. www.CPASelfStudy.com New Functions • IFERROR • The Plurals

  22. www.CPASelfStudy.com =IFERROR(Value,Value_if_error) • New Logical Function • Tired of those pesky #N/A, DIV/0! • Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula I.E. =IFERROR((C2-B2)/B2, “ “ )

  23. www.CPASelfStudy.com The Plurals • SUMIFS • COUNTIFs • AVERAGEIFs • AVERAGEIF

  24. www.CPASelfStudy.com The Plurals • SUMIFs • Extends capabilities of SUMIF() by allowing the addition of multiple range/criteria pairs. ... • Adds all numbers in a range of cells, based on given criteria • 127 Different Criteria! • COUNTIFs and AVERAGEIFs • Summarize invoices by vendor • Count journal entries made by day • # invoices processed • Summarize credit memos

  25. www.CPASelfStudy.com Total Sales for Starbucks -if product is from Tanzania

  26. www.CPASelfStudy.com Remove Duplicates • Data>Remove Duplicates • Be careful though • You can also use conditional formatting to display duplicates

  27. www.CPASelfStudy.com CTRL +T = Table • Table • Data automatically selected • Filter drop-downs appliedDefault Table format • Contextual tool bar • Enter a formula once and Excel will copy it to all rows • Pivot Table, Total the Row, Table Styles, Convert to Range Home>Format as Table – on Styles Group

  28. www.CPASelfStudy.com “New”/”Visible” Features • Analysis ToolPak now in core function list • Workdays(), NetWorkdays(), EOMONTH() • Lookup Wizard

  29. www.CPASelfStudy.com Document Inspector • The Document Inspector enables you to quickly find and remove sensitive and hidden information in your documents. Useful but not perfect To access: Office Icon>Prepare

  30. Questions? www.CPASelfStudy.com www.cpaselfstudy.com

  31. www.CPASelfStudy.com

  32. www.CPASelfStudy.com How to Find www.CPASelfstudy.com http://excel-diva.blogspot.com @excel_diva patricia@cpaselfstudy.com

More Related