Perform Foresnic Auditing of Excel files to ensure that calculation are error free & not manipulated with hidden tools
Are your spreadsheets important?
What you’ll learn
- Protect against formula and operational errors.
- Be able to demonstrate management of material spreadsheet risks.
- Reduce compliance costs for business in regulated sectors.
- Design and implement robust internal controls over spreadsheet based financial reporting.
Course Content
- Introduction and requirements for the course –> 2 lectures • 13min.
- Get started with Excel –> 3 lectures • 19min.
- Features that will speed up your review and auditing process –> 5 lectures • 1hr 1min.
- Inquire tool add in to perform through analysis of any Excel file automatically –> 7 lectures • 54min.
- Formula Auditing –> 7 lectures • 55min.
- Pivot Tables –> 11 lectures • 1hr 28min.
- Overall governance around spreadsheets –> 3 lectures • 16min.
- Bonus Lecture –> 1 lecture • 1min.
Requirements
Are your spreadsheets important?
If yes, then this course is MUST for you. It describes good practices that all spreadsheet users should follow.
Use this course to learn how to avoid the most common errors and to make future development easier. If you are a software tester or a manager of end user-computing, it gives you techniques in checking spreadsheets for accuracy and soundness. If you are an auditor looking for evidence of fraud, such as deliberately concealed data or functionality, you will also benefit from knowing the many ways in which data can be hidden or calculation methods subverted.
This course was created in response to experssions of concerns by managers or auditors about risk to the business from a pervasive dependence on spreadsheets. This course can be considered as a guide to developing high quality spreadsheets, steps to perform for auditing or reviewing spreadsheets created by someone else to ensure accuracy. This course is of interest to anyone who relies on data analysis performed in spreadsheets or models built in it for decision making. The techniques described include areas such as ensuring that the objectives of the models are clear, defining the calculations, good design practice, testing and understanding and presenting the results from spreadsheet.
From this course your will learn how to:
- Increase efficiency by avoiding rework
- Discover Powerful formula auditing techniques
- Foil attempts to conceal data and formulas from you
- Reduce worry about costly and embarrassing mistakes
- Create spreadsheets faster by avoiding wasted time from lack of specification
- Present results with more confidence knowing that you have checked for errors.
In this course we will first understand various frequently used keyboard shortcuts. I have provided a cheat of all keyboard shortcut combination available in excel. You can simply take its printout and stick it on your workstation.
Then we will have a look at various inbuilt feature available in Excel which are underutilized. These features can be extremely useful when we are reviewing or auditing or even building any complex models inside Excel file. Some of the features are like watch window, comparing same file side by side on single monitor, options under go window special, how to select cells based on their formatting and various printing setting like keep row heading on each page or printing cells comments, etc.
Then next section is fully dedicated to a new add-in called as Inquire Add-in. If you are reviewer or auditor then you MUST learn how to use this add-in. Let me repeat you must learn this. This add-in will help you analyze your entire workbook for formulas, errors, hidden sheets, macros and many more. This will help you get pictorial presentation of relationship between workbooks, worksheets and individual cells. Further, if you want to compare multiple version of files then this add-in will do it for you at cell level even for formatting changes and also changes to macro. Further, if you are struggling with excess formatting in workbook then it can help you clean that. So this section is almost more than one hour long which gives detailed explanation of various feature of this add-in. I can assure you that you would not get so much explanation about this add-in even at Microsoft site also. So this is one of the highlight of this course.
Now moving on auditing the formulas. Most of the people are aware about blue arrows displayed using formula auditing toolbar but unaware about the approaches to be followed to understand complex formulas. There have been many instances when formula entered had parentheses at wrong places and hence results were incorrect. I will explain you the order of operations followed by excel while calculating the results. We will also look at various referencing available in Excel like relative / absolute / structured and circular referencing. You must have a solid understanding of these referencing if you want to audit any formulas.
In the next section we will look at pivot tables and various settings. Pivot Table are one of the USP of Excel because it gives flexibility to users to perform the data analysis very quickly. With recent versions of Excel there have been lot of changes made to this pivot table. Current Excel now contains data models and power pivot. In this section I will provide a brief introduction about these new tools. Further I will show you various unexplored setting related to filtering, sorting and grouping data inside the pivot tables. Also, if you believe that the grand totals displayed in Pivot Table are always correct then I will show you settings through which this can be incorrect. Many fraudsters have used this setting to overstate or understate certain items used in financial statements. I also have provided solutions to FAQ on pivot table.
Now in next section titled overall governance around spreadsheets I will show various design level controls should be in place and implemented to avoid errors from spreadsheets. I will provide you a copy of sample spreadsheet policy which can be used to implement relevant procedures at your organisation. I will also show how can you create a inventory of files stored in a folder and also create a index sheet within excel file using Power Query. I have two other full fledged courses on Power Query.
So currently these are areas I am covering as part of this course. In future I will also update more videos to this course and you do not have to pay anything extra for it. So don’t wait and enroll into this course immediately.