Course Overview

There is no doubt that 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.

Course Methodology

This is a hands-on course with about 20% on design and structure and 80% uses MS Excel as a commanding tool to perform routine and periodic tasks. Individuals will be required to complete exercises, case studies, and projects daily.

Course Objectives

By completely attending this course, participants will be able to:
  • Design dynamic reporting models using different modelling techniques. Perform What-If-Analysis.
  • Integrate Excel with many different file types such as access, web, text, SQL, and other databases. Reports will update automatically.
  • Perform repetitive tasks and generate reports efficiently by recording, running and editing.

Target Audience

Business, finance and accounting professionals, senior and junior accountants, business analysts, research professionals, marketing and sales, administrative staff, supervisors, general business professionals.

Target Competencies

  • Modeling Techniques
  • Integration and Linking
  • Macros and Automation
Note:
This course requires the use of laptops with Excel 2010/2013/2016 installed. On any courses conducted in the UAE, VIF will provide laptops to all delegates for the duration of the course. For courses outside the UAE, delegates must bring their own laptops with Windows-based Excel 2010+ fully installed.

Course Outline

Modeling and Integration Techniques

  • Performance 'what-if' using spinner
  • Check box data modeling with 'if' function
  • Option box data modeling with 'if' function
  • List box data modeling with 'CHOOSE' function
  • Linking Excel with text files
  • Linking Excel with databases (Access)
  • Linking Excel with multiple Excel files and SQL
  • Linking Excel with internet
  • Scenario manager

Introduction to Learning the Ultimate Tool in Excel: ‘Macros’

  • Macro basics
  • Planning a macro
  • Designing your control board
  • Recording macro
  • Testing macro
  • Editing macro
  • Macro workshops
  • Advanced filter with macro

Tips and Tricks in Excel

  • Conditional formatting
  • Recover unsaved workbooks
  • New functions
  • Protect all formula cells
  • 40 important shortcuts
  • Controlled list
  • Fill in a flash
  • Sort and filter by color
  • Data entry form
  • Custom list
  • Camera tool
  • Text to speech
Location & Dates
Call us at +971 4 430 8394