Business Data Analytics with Excel

Who Should Attend?

Anyone using EXCEL for ANALYTICS

Duration: 3 Days
Training Date: 24 – 25 September 2020

This 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 analyzing current business data without heavily investing in expensive data analytics software.

  • Understand 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
  1. 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
  2. Get Started with Developer, Add-Ins and VBA
    • Enabling Developer Tab and Macros
    • Download Add-Ins for Excel
    • Introducing Visual Basic for Applications (VBA)
Module 1: Extract, Transform and Load (ETL) with Excel
  1. Data Sources and Types
    • Variables and Data Sources
    • Data Collection Methods
    • 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
Module 2: Data Analytics using Excel
  1. Descriptive Analytics
    • Descriptive Statistics, Frequency Table and Cross Tabulation
    • Relationship and Correlation Analysis
  2. Predictive Modeling
    • Unsupervised Data Segmentation using k-Means Clustering and Decision Tree
    • Supervised Learning using Logistic Regression and Neural Network for Classification
    • Prediction of Continuous Outcome using Multiple Regression
    • Trend Analysis using Time Series Models
Module 3: Visualization and Building Dashboard
  1. 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 Design
  2. Constructing Excel Build-In Charts
    • Bar Chart and Histrogram
    • Line Chart
    • Scatter Plot
    • Area Chart
    • Doughnut Diagram
    • Bubble Chart
    • Radar Chart
    • Create Compound Charts
    • Extracting Data from Web (Record Macro)
    • Extracting Data from Text
  3. 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
  4. Building Dashboards: Controls and ActiveX Controls
    • Command Button
    • Combo Box
    • Check Box
    • List Box
    • Text Box
    • Scroll Box
    • Spin Button
    • Option Button
    • Toggle Button
    • Data Validation
  5. Building Dashboards for Data Visualization
    • Principles to Produce an Effective Dashboard
    • Develop Your Own Dashboard using ActiveX Controls and VBA Pivot Chart

Register Now

Drop us your entry if you are interested to join this course.