ABOUT COURSE 

It is a fact, Excel is the accountant's, finance and business professional's best friend! You and I know how overwhelmed we are with the abundance of unstructured data. Monthly, quarterly and annual closing are immensely data driven and require moving and exporting data from 'ERPs' and databases to Excel. This hands-on course will advance your data massaging, modeling, integration and automation skills to new levels. You will also master normalization and massaging of noisy data, preparation of reports, analysis and reconciliation.

This course is Excel based allowing you to develop an exclusive level of expertise and adding immediate value to your job and company.

Course Methodology

20% of the course is design and structure focused. 80% uses MS Excel as a powerful tool to perform daily, monthly and periodic tasks. Groups and individuals will be required to complete exercises, case studies, and projects on daily basis.

Objective

• Use some of the functions explained on the Intermediate level of this course
• Navigate confidently in Excel
• Use absolute cell references (e.g =$A$1)
• Create and use nested functions, or at least have an understanding of the benefits of nesting functions togethe

Who should this course

 the course material includes advanced features of Excel and participants will gain the maximum benefit from this course if they have already attended the ‘Data Analysis with Excel – Intermediate’ course and / or are already competent spreadsheet users. This course is designed for users who use Excel on a regular basis, and are looking to further their knowledge in producing more robust spreadsheets.

Outline:

• DAY 1  Advanced listing techniques
  • Tricks in creating dependent lists
  • Dynamic selection tips
  • Dynamic extraction of unique values
• Facilitating calculations
  • Dynamic and expandable named ranges
  • Multi-nested functions / expandable referencing
  • Structured table references / table nomenclature
  • Aggregate calculation using the ‘wildcard’ technique
• DAY 2  Key functions
  • DSUM / SUMPRODUCT / SUMIFS (and building conditions)
  • Advanced uses of: OFFSET / CHOOSE / INDEX / MATCH
  • ROWS
  • INDIRECT
• DAY 3   Further advanced techniques for summarising and presenting data
  • Mini Pivot Table reports
  • Fast calculations with Data Tables
  • Data modelling with Scenario Manager
  • Histogram and Pareto Charts
• DAY 4  Excel dashboard (extracts)
  • Creative charting techniques
  • Building a visual analysis
  • Tips and tricks in custom formatting
• DAY 5  Bonus VBA exercises
  • Apply simple automation to speed up processes

Certificates

A Certificate of Completion will be issued to those who attend & successfully complete the programme.

Schedule

  08:30 – 10:15 First Session

 10:15 – 10:30 Coffee Break

10:30 – 12:15 Second Session

 12:15 – 12:30 Coffee Break

12:30 – 14:00 Third Session

 14:00 – 15:00 Lunch

Training Methodology:

This interactive training course includes the following training methodologies as a percentage of the total tuition hours:

• 30% Lectures, Concepts, Role Play
• 30% Workshops & Work Presentations, Techniques
• 20% Based on Case Studies & Practical Exercises
• 20% Videos, Software & General Discussions
• Pre and Post Test

Fees

 The Fee for the seminar, including instruction materials, documentation, lunch, coffee/tea breaks & snack is: