Advanced Excel focused on the Accounting Profession

Price:
R1518.00 (VAT Incl.)
Level:
Business Professionals
Sub-level:
Microsoft
Lecturer:
Probeta Training
Duration:
390 Minutes
Additionals:
   CPD Assessment
   Certificate

Lesson Outline


Overview:

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.

Course Content:

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.