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.

None