Book Your Seat Today!

Kindly advise me your company detail and our consultant will contact you soonest!

Course Objectives

  • Concepts of data science
  • Perform ETL (extract, transform, loop) process using Excel
  • Using statistical methods to perform descriptive analysis
  • Construct predictive models for different data applications
  • Visualize the data using graphical techniques
  • Develop dashboard using ActiveX Control and VBA Pivot Chart

Description

This three days course helps business professional to discover/identify business growth opportunities and hidden business information which include customers buying preference, products selling pattern, external environment factors and other internal business factors through analysing current business data without heavily investing in expensive data analytics software.

Target Audience

This is suggested for those who wish to use Excel for data science tasks.

Training Outline

We will use one of the following case studies to show the application of data analytics.

  • Customer relation management (CRM)
  • Market performance analysis
  • Sale trend analysis
  • Any other relevant applications
Introduction to Data Analytics
  • What is data analysis and analytics?
  • Data science process and skill sets
  • Analytics hierarchy and types
  • Basic domains use analytics
  • About big data
  • The CRISP-DM model
Getting start with Developer, Add-Ins and VBA
  • Enabling Developer tab and Macros
  • Download Add-Ins for Excel
  • Introducing Visual Basic for Applications (VBA)
ETL Processes
  • Data sources and types
    • Variables and data sources
    • Data collection methods (registration, questionnaires, interviews, direct observations, reporting)
    • Linkage among variables, sources and methods
    • Structured and unstructured data
    • Ways to record data (table, images, charts, text)
  • Extracting and integrating various data into single worksheet
    • Merge worksheets of active workbook
    • Merge worksheets from different workbooks
    • Extracting data from web (record macro)
    • Extracting data from text
  •  Data warehousing and cleaning
    • Pivot and de-pivot table
    • Consolidate data in multiple worksheets
    • Group, ungroup and subgroup data
    • Updating observations
    • Remove duplicates
    • Identify and treatment of outliers
    • Identify and impute missing values
    • Normalization of data
Descriptive Analytics
  • Descriptive statistics, frequency table and cross tabulation
  • Relationship and correlation analysis
Predictive Modeling
  • Unsupervised data segmentation using k-means clustering and decision tree
  • Supervised learning using logistic regression and neural network for categorical outcome
  • Prediction of continuous outcome using multiple regression
  • Trend analysis using time series models
Visualization of Data
    • Some good practices in designing charts
      • What is a good graphical representation?
      • Pros and cons of various charts
      • Selection of charts based on purposes and designs
    • Constructing Excel build-in charts
      • Bar chart and histogram
      • Line chart
      • Scatter plot
      • Area chart
      • Doughnut diagram
      • Bubble chart
      • Radar chart
      • Create compound charts
Building dashboard
  • Controls and ActiveX Controls
    • Command button
    • Combo box
    • Check box
    • List box
    • Text box
    • Scroll bar
    • Spin button
    • Option button
    • Toggle button
    • Data validation
  • Building dashboard for data visualization
    • Principles to make good dashboard
    • Develop your own dashboard using ActiveX Controls and VBA Pivot Chart

Prerequisite

Experience in using MS Excel.