Learn the best ways to use Microsoft Excel for data analysis, reporting and modeling for decision making using real-world business scenarios. Designed for supervisors, managers or team members who need to summarize, report and analyze data, this course will present helpful tools to evaluate investments and financial decisions, ensure accurate data entry by creating a list of values, summarize data using histograms and PivotTables, estimate business variables using trend curves and present the data. 

 

Prerequisites

Excel Intermediate Level

 

Objectives

The aim of this course is to give delegates a thorough grounding in Excel Advanced functions, Data Analysis Tools, Reporting and Business Modelling. It uses multiple exercises to ensure that delegates can practice the key techniques that are taught and also ask any questions that they may have.

Our aim is that you leave the course as a confident user of Excel and can start automating your spreadsheets as soon as your return to your office.

 

Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

 

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.

Each delegate will be given a digital courseware manual to refer to after the course.

 

Course Outline

 

Advanced Functions

  • Name Manager, Naming Ranges and Using them in Formulas
  • Formula Auditing
  • SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
  • INDEX & MATCH Lookup functions

 

What-If

  • Scenarios and Scenario management
  • Goal Seek

 

Advanced Data Analysis & Presentation

  • Using Sparklines
  • Create and manage PivotTables
  • Filters in PivotTables
  • Creating and manging Slicers
  • Create and manage PivotCharts
  • Manipulating options in existing PivotCharts 
  • Multiple PivotTables on same WorkSheet
  • Grouping Data to analyse data by Year/Quarter/Month
  • Building a BI Dashboard

 

Trend Analysis

  • Using Scatter Charts and Trend Analysis

 

Automating processes with Macros

  • Recording and Running Basic Macros
  • Saving Work with Macros

Prerequisites

A very good knowledge of Excel, including a working knowledge of PivotTables.

 

Objectives

At the end of the course, delegates will be able to use Power BI as a Self-Service BI tool. Users will learn Power Query for importing and transforming data from a multitude of data sources, building data models in Power Pivot and building Pivot Tables reading from multiple tables and finally creating interactive visualisations in Power BI Desktop or Power BI for o365.

 

Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

 

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training.

Each delegate will be given a digital courseware manual to refer to, both during and after the course.

 

Course Outline

Introduction to Business Intelligence (BI)

  • The definition of BI
  • Why is BI important?
  • Preview a BI stack

 

Choosing the Right BI Tools

  • Differentiate between business groups that consume BI
  • Understand the typical progression of BI
  • Determine the best BI tools for your needs

 

Using Power Query to gather data from various sources

  • Importing data in excel from various data sources (online, web search, spreadsheets, various database sources, Facebook, Microsoft exchange and others)

 

First Steps with PowerPivot

  • Working with Classic Excel PivotTables
  • Working with PivotTables in PowerPivot
  • Importing, Querying Data and Creating a Data Model
  • Creating PivotTables from multiple data sources

 

PowerPivot at Work

  • Using the PivotTable to Produce Reports
  • Formatting Numbers
  • Adding Calculated Columns
  • Adding Measures
  • Adding More Tables
  • Refreshing Data
  • Using Slicers
  • Extending the Data Model relationships using Excel, PowerPivot and DAX



This course is open for all professionals with different backgrounds and across varied industries. The objective of this programme is to inspire and empower professionals to apply the necessary data skills to address the fourth industrial revolution challenge.


Outline

Module 1 - Data Governance

Module 2 - Data Gathering

Module 3 - Data Modelling

Module 4 - Data Analytics and Visualisation

 

Prerequisites

Good mathematical, computational, and analytical skills are required. Minimum of MQF Level 5 and/or 2 years working experience in a private or public organisation / set-up.

 

Contact hours

Each module is split over a number of shorter sessions amounting to 5 contact hours per module

 

Assessment

All learning outcomes of all the 4 modules will be assessed through an assignment carried out in the trainees’ own time with the aim of assessing the participants cognitive development and application of knowledge to a typical workplace scenario. The assignment will be in the form of a case study.



 



Prerequisites

A foundation in basic Excel techniques is advised.

 

Objectives

By the end of this course candidates will master the techniques of using the right excel tools to solve problems more efficiently. Also new features in the current Excel version will be highlighted.

Topics covered include Conditional Formatting wizard, Flash Fill, Name Manager, various functions (string, maths, logical, lookup), pivot tables, slicers and pivot charts to build a BI dashboard.

Having practiced all of these new skills extensively during the course you will be able to hit the ground running on your return to the office and should be able to complete your Excel work far more proficiently.

 

Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

 

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.

Each delegate will be given a digital courseware manual to refer to after the course.

 

Course Outline

Apply Fill Tools, Conditional Formatting and Filtering

  • Using advanced Fill Series options
  • Flash Fill
  • Apply advanced conditional formatting and filtering
  • Creating filters
  • Managing conditional formatting rules
  • Conditional Formatting with Sorting & Filtering

 

Working with Data

  • Getting External Data
  • Restricting Cell Entries to Certain Data Types
  • Data Validation Tools
  • Removing Duplicate Rows from a Worksheet
  • Sorting Data on a Single & Multiple Criteria
  • Sorting Data Using Cell Attributes
  • Using AutoFilter
  • Filtering Data Using Cell Attributes
  • Removing Duplicates in a Table

 

Using Formulas

  • Apply functions in formulas
  • Functions Wizard
  • Absolute vs Relative Referencing
  • Using SUM, COUNT, SUBTOTAL
  • Using nested functions
  • Using the IF, AND, and OR functions
  • Using the VLOOKUP function
  • Using the NOW and TODAY functions
  • Using text manipulation functions like LEFT, RIGHT, MID, TRIM, CONCATENATE

 

Data Analysis & and Manipulation using PivotTables & PivotCharts

  • Create and manage PivotTables
  • Modifying field selections and options
  • Filters in PivotTables
  • Creating slicers
  • Create and manage PivotCharts
  • Manipulating options in existing PivotCharts 
  • Multiple PivotTables on same Sheet
  • Applying styles to PivotCharts
  • Building a BI Dashboard



Course Description

Data has been dubbed nowadays by many as being the new oil. Similarly to oil, data needs to be refined to extract the hidden value that it holds.  However, many organisations in today’s market still do not realise the value of the voluminous amounts of data that they are storing. Therefore, this course has been designed to provide an understanding in how organisations can go about analysing their data using innovative techniques in order to derive valuable insights. Such insights will support them in making more effective data driven decisions thus enabling them to remain competitive and at the forefront in today’s business environment.

 

Course Outline

  • What exactly is Data Analytics?
  • Why is Data Analytics so important in today’s business world?
  • Different types of analytical capabilities within organisation.
  • Different types of data and technologies to capture business data.
  • Data Analytics techniques and tools.
  • The journey to becoming a data-driven business.

 

Who should attend?

This course is designed to have broad appeal across many types of industries.

Executives and Managers who are looking to explore and gain an understanding in how to leverage data analytics within their organizations to drive effective and efficient decision-making will greatly benefit from attending this course.

 

Learning Outcomes

This course provides trainees with a valued understanding of what is data analytics is exactly and how the organisations they work in can leverage data analytics to help drive business decisions.

 

Course Duration

Course duration is 4 hours.


 



Prerequisites

This Excel VBA training requires knowledge of Excel up to Advanced level. No prior knowledge of Excel macros or Visual Basic for Applications (VBA) is required.

 

Objectives

The aim of this course is to give delegates a thorough grounding in Excel VBA. It uses multiple exercises to ensure that delegates can practice the key techniques that are taught and also ask any questions that they may have.

Rest assured that you will leave the course as a confident, albeit inexperienced, Excel programmer and can start automating your spreadsheets as soon as your return to your office.


Certifications

Attending delegates will be presented with a Certificate of Attendance upon completion of training.

 

Training Methodology

We believe that learning is most effective when presented in a relevant context so that the skills, strategy, and knowledge are meaningful to our delegates and can be applied directly in the training. Training sessions are conducted as hands-on workshops.

Each delegate will be given a digital courseware manual to refer to after the course.

 

Course Outline

Introduction to Excel VBA

  • Creating a Macro
  • Running the Macro
  • Examining the code behind the macro

Introducing the Visual Basic Editor (VBE)

  • Opening the Visual Basic Editor
  • Recognizing Parts of the Visual Basic Editor
  • Menus – View, Insert, Format, Debug, Run & Tools Menu

Exploring Visual Basic for Applications

  • Objects and Collections
  • Workbook and Worksheet Object
  • Range Object and Cells Property
  • Methods and Property
  • Assigning Object Variables and Using Named Argument

Declaring Variables, Constants and Data Types

  • Declaring Variables
  • Defining Data Types
  • Defining Constants
  • Assigning Values to Variables
  • Handling Strings
  • Handling Dates and Times
  • Handling Variants and Data Type Conversions

Modules and Procedures

  • Modules and Procedures and Their Scope 
  • Calling Sub Procedures and Function Procedures 
  • Understanding Variable Scope and Lifetimes
  • Exercises

Writing Your First VBA Function in Excel

  • About User Defined Functions
  • A Function to Calculate the Area of a Rectangle
  • A Function to Calculate Fuel Consumption
  • Accessing Your Custom Functions

Decision Structures

  • IF ... Then Statement
  • IF ... Then ... Else
  • Exercise : A Function to Calculate the Area of a Rectangle – (IF)
  • Select Case
  • Exercise : A Function That Gives the Name of the Day

Loop Structures

  • For ... Next 
  • For ... Next Loop With Step
  • Do While ... Loop
  • Do Until ... Loop 
  • Do ... Loop While 
  • Exercises 0..8

Complex Exercises

  • Adding Up the Coloured Numbers 



Microsoft Excel for Accountants is a training program that provides a practical, hands-on training on spreadsheet features and functions, intended to enhance the trainee’s efficiency at the workplace.

The course complements roles and responsibilities related to accounting, finance and budgeting functions.

 Course Outline

  • Creating formulas & operators
  • Relative & absolute cell referencing & handling error messages
  • Leveraging accounting and finance functions
  • Advanced data sorting
  • Importing delimated text file (CSV files)
  • Logical functions (IF function)
  • Mathematical functions (SUMIF & ROUND)
  • Statistical functions (COUNTIF)
  • Text functions: LEFT, RIGHT, MID, TRIM, CONCATENATE
  • LOOKUP functions: VLOOKUP, HLOOKUP
  • Formatting and modifying charts
  • Creating and using a pivot table
  • Automatically/manually grouping data & renaming groups
  • Macro development
  • Financial modelling through Excel
  • Using finance and accounting templates

 

Prerequisites

Basic knowledge of MS Office tools is essential.

 

Who should attend?

The MS Excel for Accountants is the ideal course for trainees working in accounts teams, as well as people having a finance related role.

 

Learning Outcomes

This course provides trainees with a good grounding in MS Excel techniques that can be used to selectively manipulate accounting and financial datasets.


This course is split into 4 modules

Course Outline

  • Python Basics

    • Install Python
    • Starting with Programming and Python Basics
    • Data Types, variables and Operators
    • Lists and Strings

     

    Python Advanced

    • Functions
    • Conditional Statements
    • Loops
    • Object-Oriented Programming (OOP) Concepts
    • Creating Classes
    • Packages in Python

     

    Running Python Scripts in Power BI for Data Analytics

    • Install required Python Packages – Pandas, Numpy and scikit-learn
    • Creating Python scripts to apply Machine Learning Analytics on the data
    • Setting-Up Python Scripting in Power BI
    • Creating Advanced Analysis & Visualisations using Python

     


This course is ideal for those that have no clue about Course Descriptions

  • 2 years experience in accounting
  • Diploma in nuclear energy

This is a certification at MQF Lev 20

  • 30 contact hours
  • Assignment
  • Test
  • Reading and research time

Preparation prior to course

    List of reading material:
  • Book by Charlene Borg
  • Article by Bernice Carabott
  • Podcast by Marika
22 May 2021
1000 – 1200hrs
2 hours
Professional
The Hub or Blended (part online part classroom)
Eur300