Introduction

This introductory course is suitable for a two-day live training course and a nine hour (3 sessions at 3 hours per session) online webinar style programme. The essence of the course is to provide delegates with an introductory to intermediate level review of the main topics under the general heading of data analysis.

The course content and practical exercises are centered around Microsoft Excel and a significant number of Excel functions and techniques will be reviewed during the course.

Training methodology

Whether the course is provided as live training or as an online experience, the training methodology is essentially the same:

The trainer will introduce each topic with a discussion and/or practical exercise and the delegates will then be provided with a follow up exercise to complete on their own. The final result will be shared with everyone either by confirmatory discussion during the session or by means of sharing the trainer’s completed Excel file.

All delegates will receive a full set of training notes and Excel files: both delegate and trainer versions. There will also be PowerPoint slides and other support materials that will form an integral part of the course.

Workshop prerequisites

Delegates should have a good working knowledge of Microsoft Excel: you don’t need to be an advanced user, just aware of and be confident in the use of basic Excel

Delegates should also be in possession of their laptops loaded with Microsoft 365/Excel 2016

You will also need to ensure that Power Query and Power Pivot are fully operational: you will receive a delegates’ pack in advance of the course and full instructions will be given then

Required Outcomes

After completing this workshop, the delegate should be able to:

• Identify data analysis situations and opportunities
• Select and use the appropriate analysis and modelling tool(s) in Excel
• Provide meaningful and readable data analysis reports for further translation into action plans

Schedules for the full workshop

3 Sessions of 3 hours each Singapore Time (Total of 9 hours)

17 August 2020, Monday at 9:00 AM - 12:NN

19 August 2020, Wednesday at 9:00 AM - 12:NN

21 August 2020, Friday at 9:00 AM - 12:NN

Course contents

Introduction

The Types of Data Analytics

• Data Mining

• Business Intelligence

• Statistical Analysis

• Descriptive statistics

• Inferential statistics

• Predictive Analytics

• Text Analytics

Review of The Process of Data Analytics… analyzing data, techniques for interpreting the results of such procedures, ways of planning the gathering of data to make its analysis easier, more precise or more accurate, and all the machinery and results of (mathematical) statistics which apply to analysing data.” John Tukey

Thus, data analysis is a process for obtaining large, unstructured data from various sources and converting it into information that is useful for:

• Answering questions

• Test hypotheses

• Decision making

• Disproving theories

Data Analytics with Excel

Importing Data into Excel

• Microsoft Access Database

• Web Pages

• CSV and Text Files

• SQL Server Table

• SQL Server Analysis Cube

• XML File

• Many others

o unstructured and structured

o may contain irrelevant information

Data Processing

Structuring

• Ranges and Tables

• Data Cleaning: Text Functions, Dates and Times

• Conditional Formatting

• Sorting and Filtering

• Subtotals with Ranges

Data Cleaning

• Incomplete

• Contain duplicates

• Contain errors

• Totals compared against reliable sources and thresholds

• Outlier detection

Data Analysis

• Data analysis techniques

• Data visualisation

• Data Validation

• Financial Analysis

• Working with Multiple Worksheets

• Formula Auditing

• Correlation

• Regression analysis

• Additional data cleaning

• Additional data collection

Quick Analysis Tool

Using Lookup Functions

• VLOOKUP

• HLOOKUP

• XLOOKUP

• LOOKUP

• INDEX

• MATCH

• INDIRECT

Inquire

More Advanced Data Analytics

• Data Consolidation

• What … If? Analysis

• Goal Seek

• SOLVER Add in

Exploring Data

• PivotTables

• Power Query

• PowerPivot: Data ModelHierarchies

Key Performance Indicators (KPIs)

Communication

Visualisation techniques

• tables

• charts

• dashboards

Mr. Duncan Williamson

Distinguished Trainer & International Consultant

Highlights: Duncan combines his extensive academic and training experience with significant manufacturing and commercial work. In addition to working as a teacher and trainer, Duncan has experience of working in a wide variety of settings, ranging from working as a management accountant in process industries in the UK to product cost consulting in several industries in Eastern Europe and the oil and gas industry in the Middle East. Duncan has worked in and around a number of oil and gas based companies over recent years.

He has published books, articles and case studies. His latest books Introduction to Power Pivot and Introduction to Power BI for Desktop to be published by www.bookboon.com

Countries of Work Experience: Bosnia and Herzegovina, Kyrgyzstan, Kazakhstan, Uzbekistan, Romania, United Kingdom, Afghanistan, Dubai, Abu Dhabi, Kuwait, Oman, Saudi Arabia, Switzerland, Hungary, Thailand, Singapore, Vietnam, Philippines, Malaysia, Pakistan, Holland, Sudan, Albania, Qatar, Mexico, Angola

Significant Areas of Expertise: cost and management accounting, financial accounting and bookkeeping, financial reporting under IFRS, financial modelling, spread sheet modelling, and product cost modelling.