Attendance at this seminar will secure 6.5 hour/s verifiable CPD points including other professional bodies (SAICA, SAIBA, ACCA, IACSA, IRBA & etc)
NERISSA SINGH
nerissa@probetatraining.co.za
Microsoft Excel is the foremost spreadsheet application used in any and every department within an organisation, be it in audit, accounting, taxation or financial management.
Many secondary applications interface with Microsoft Excel and so an advanced knowledge of Microsoft Excel will streamline many tasks relating to data editing, data extraction, data filtering / sorting, data searching and data presentation - which will empower decision making.
ProBeta Training has designed a practical one-day course for accounting professionals with intermediate experience to enable them to take a step up in analysing data.
As an accounting professional as well as auditor, it is essential to seek new ways of maximising the functionality of Microsoft Excel to be in the best position to make informed financial decisions - let ProBeta Training help your accounting professionals.
Create powerful audit and exception reports, by making use of ODBC, Microsoft Query and advanced excel techniques.
Advantages of using Advanced Excel techniques:
• Working much faster with larger records
• Detects areas of interest for further review
• Data can be manipulated without changing anything in the original data
• Analysing the whole population not just an example
• Imports unlimited records (MS Office 2016)
• Easily filters data on user - specified criteria which can then be extracted to smaller manageable files for others to use
Examples of what can be achieved by using these techniques:
• Easily summarise classify information e.g. total sales per area or product per month
• Search for specific relevant data e.g. search for all the cash transactions in a ledger
• Find duplicated transactions e.g. duplicated supplier invoices
• Find gaps in sequence e.g. test for completeness on character numbers quickly find missing data and
• Predictable patterns using Benford's Law
• Statistics and sampling
During this course we will show you how to create the following audit and exception reports:
• Setting up the ODBC Drivers
• Importing data from Pastel
• Creating queries using the Query wizard
• Moving around in large worksheets by using the hot keys
• Sorting a database
• Using the IF/AND functions
• The COUNTIF function
• VLOOKUP function
• Date and time functions
• Filtering of data
• Create unique lists
• Create a pivot table
Quick explanation of the technique
ODBC is a tool that can access databases created by other applications e.g. Pastel. To import data from Pastel into Excel you need a suitable ODBC driver. The ODBC driver needs to know which data you are interested in. Rather than provide all this information each time to access the data, you do this once by defining a Data Source Name template. The DSN can then be re-used whenever you want to access the data again. You can then submit commands to the ODBC driver and it will send you the data and display the result in a spreadsheet format.
In this course we will use Excel to create a database, ODBC to define the connection to the database and Microsoft Query to provide a user interface through which to query the data.
After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.
• Delegates that make use of Microsoft Excel as part of their daily work duties
in the following financial sectors, namely auditing, accounting, taxation and
financial management.
• Delegates that need to extract and sort through data and turn it into
information for decision-making purposes.
The hosting organization is responsible for the following:
• Own computers (PC or laptop) with Excel loaded onto the machine
• Internet Connection (Wi-Fi or 3G) with sufficient data for the day
• USB headset (microphone, speakers) for each delegate
For engagement activities and online tools, other devices such as the iPad and iPhone are not recommended.
Please ensure that the pre-course work has been completed prior to attending the course.
Please ensure that you are logged in 15 minutes prior to the start of the session.
Delegates must please make use of their own computers with the latest version of Pastel and Excel already loaded. Please install Microsoft Query on your computer before the start of the course. All other materials and stationery will be provided.
