Digital Vidya 11 years of excellence

Data Analytics Master Course

with Excel, PowerBI

Online Instructor-Led Training Program

online meeting
i

5

Courses

3

Capstone Projects

50+

Live Class Hours

60+

Assignments Hours

Why do Data Analytics Master Specialisation?

This data analytics master program is curated for people who own data as part of their functional roles or are in the roles of enabling data-based decision-making.

The key capabilities that the participants will acquire by doing this data analytics course are:

  • Getting proficient with the fundamental of working with data using the basic and advanced features of Microsoft Excel.
  • Becoming a story-teller with data using PowerBI for powerful visualization.
  • Acquiring skills of a Statistician to be make correct data interpretations
  • Knowing how data stores in databases and getting them into analytical tools 

To understand more about the Data Science and this Specialisation

Data Analytics Curriculum

This specialization course has 5 Courses

Data and Statistics

  • Elements, Variables, and Observations
  • Scales of Measurement
  • Categorical and Quantitative Data
  • Cross-Sectional and Time Series Data
  • Descriptive Statistics
  • Statistical Inference

Descriptive Statistics: Tabular and Graphical

  • Summarizing Categorical Data
  • Summarizing Quantitative Data
  • Crosstabulations and Scatter Diagrams

Descriptive Statistics: Numerical Measures

  • Measures of Location
  • Measures of Variability
  • Measures of Distribution Shape, Relative Location, and Detecting Outliers
  • Box Plot
  • Measures of Association Between Two Variables

Introduction to Probability

  • Experiments, Counting Rules, and Assigning Probabilities
  • Events and Their Probabilities
  • Complement of an Event
  • Addition Law
  • Independent Events
  • Multiplication Law
  • Baye’s theorem

Discrete Probability Distributions

  • Random Variables
  • Discrete Probability Distributions
  • Expected Value and Variance
  • Binomial Probability Distribution
  • Poisson Probability Distribution

Continuous Probability Distributions

  • Uniform Probability Distribution
  • Normal Curve
  • Standard Normal Probability Distribution
  • Computing Probabilities for Any Normal Probability Distribution

Sampling and Sampling Distributions

  • Simple random sample and its importance
  • Difference between descriptive and inferential statistics
  • Sampling distribution
  • Mean and standard deviation
  • Central Limit Theorem and its importance
  • Mean and standard deviation for the sampling distribution of the sample proportion
  • Sampling distributions of sample variances

Interval Estimation

  • Point estimate and confidence interval estimate
  • Construct and interpret confidence interval estimate
  • Form and interpret confidence interval estimate
  • Confidence Intervals for the Population Mean, μ
  • Confidence Intervals for the Population Proportion,  (large samples)

Confidence Interval

  • Point estimate and confidence interval estimate
  • Construct and interpret confidence interval estimate
  • Form and interpret confidence interval estimate
  • Confidence Intervals for the Population Mean, μ
  • Confidence Intervals for the Population Proportion,  (large samples)

Hypothesis Tests

  • Developing Null and Alternative Hypotheses
  • Type I and Type II Errors
  • Population Mean: Known
  • Population Mean: Unknown

Inference About Means and Proportions with Two Populations

  • Inferences About the Difference Between Two Population Means
  • Inferences About a Population Variance
  • Inferences About Two Population Variances

Simple Linear Regression

  • Simple Linear Regression Model
  • Regression Model and Regression Equation
  • Estimated Regression Equation
  • Least Squares Method
  • Coefficient of Determination
  • Correlation Coefficient
  • Model Assumptions
  • Testing for Significance
  • Using the Estimated Regression Equation for Estimation and Prediction
  • Residual Analysis: Validating Model Assumptions
  • Residual Analysis: Outliers and Influential Observations

Multiple Regression

  • Multiple Regression Model
  • Least Squares Method
  • Multiple Coefficient of Determination
  • Model Assumptions
  • Testing for Significance
  • Categorical Independent Variables
  • Residual Analysis

Model building in Regression

  • Regression model-building methodology
  • Dummy variables for categorical variables with more than two categories
  • Dummy variables usage in experimental design models
  • Lagged values of the dependent variable is regressors
  • Specification bias and multicollinearity
  • Heteroscedasticity and autocorrelation

Nonparametric Methods

  • Sign Test
  • Wilcoxon Signed-Rank Test
  • Mann-Whitney-Wilcoxon Test
  • Kruskal-Wallis Test
  • Rank Correlation

Tests of Goodness of Fit and Independence

  • Goodness of Fit Test: A Multinomial Population
  • Test of Independence

Anova

  • An Introduction to Analysis of Variance
  • Analysis of Variance:  Testing for the Equality of k  Population Means
  • Multiple Comparison Procedures
  • An Introduction to Experimental Design
  • Completely Randomized Designs
  • Randomized Block Design

Introduction to Analytics Techniques

  • Overview of the Analytics Techniques
  • Business Analytics
  • Business Intelligence
  • Industry Examples

Fundamentals of Excel

Introduction
  • Cell Referencing
  • Freeze Panes
  • Sum Function
Useful Functions
  • Counting functions
  • Summing functions
  • Averaging functions
  • Rounding functions
  • Sumproduct
Sorting & Filtering
  • Multi-level Sort
  • Custom Filter
Function on Filter
  • Subtotal function
  • Remove Duplicates

Data Validation

  • How to Use in-built options
  • Custom Data Validations with Formulas
Data Cleaning

Text Concatenate

Date functions
  • Date Functions Splitting
  • Creating Dates

Working with Conditions

  • Conditional Formatting Basics
  • Custom Conditional Formatting with Formulas
Logical Operations
  • IF, AND, OR, Nested IF
Database Functions

DSUM, DCOUNT, DAVERAGE, DMAX, DMIN

Data Manipulation using Advanced Excel

  • Fetching & Comparing Data Sets
  • $ Referencing
  • Vlookup & Hlookup
  • Named Ranges Name Box
Advanced Lookup Functions

Match & Index

  • Indirect
  • Offset
  • Choose
  • Dependant Dropdowns Dependent Data Validation Lists
Excel Tables Capabilities & Why to use
Protection Password Protection Sheet level

Data Analysis & Visualization using Advanced Excel

  • What-if-Analysis Goal Seek
  • Data Table
  • Scenario Manager
  • Solver Plug-in Solver Plug-in
  • Grouping & Subtotal Grouping
  • Subtotal feature
  • Pivot Tables “Dimensions & Measures
  • Multi-layer Pivot Table
  • Summarize Values by
  • Show Values as
  • Grouping”
  • Charts “Various Charts
  • Pivot Charts
  • Combo Charts
  • Sparklines
Introduction to Array Functions

Basics, Rows, Columns, Large, Small

Debugging Formulas
  • How to debug
  • Precedents, Dependents
  • Watch Window

Introduction to VBA Macros

  • What are macros
  • How to write a basic VBA code
  • Recording Macros
  • Editing recorded macros
  • Finding VBA codes on the internet

Introduction to Statistics

  • Statistics. Samples and Population
  • Probabability
  • Random Variables
  • Dataset Distributions

Descriptive Statistics

  • Mean
  • Median
  • Mode
  • Standard Deviation
  • Variance

Inferential Statistics

  • Inferring the relations and patterns in datasets.
  • Correlation
  • Analysis of Variance
  • Analysis of CoVariance

Applied Statistics – Some popular applications

  • Trendline Analysis
  • Factor Analysis
  • Principal Component Analysis
  • Assets Portfolio Analysis

Introduction to MS SQL

  • Relational database concepts
  • Data types
  • Tables

SQL Queries & Views

  • Queries Select, Insert, Update, Delete
  • Views Creating & Updating

Power BI – Introduction

  • Calculated Columns
  • Measures
Excel Tables
  • Why use Tables
  • Referencing Syntax
  • Formulas
  • Named Ranges
  • Dashboard using Pivot Tables, Slicers, Timeline
Power BI Components
  • Old v/s new Technologies
  • Power BI Desktop
  • Power BI Service
  • Power BI Mobile
Importing Data
  • From Excel
  • From Access, Folder, other sources
  • Clean & Transform
  • Data Query Editor
  • Advanced Methods
  • Pivoted data / Unpivoted Data
Modeling
  • Relationships
  • Calculated Columns & Measures
  • Calculated Tables
  • Time-based Data

Power BI – Building Dashboards, Manipulating data using DAX, Importing Data from multiple sources

DAX Functions
  • Introduction to DAX
  • Data types
  • Calculated Columns & Measures
  • DAX Functions
  • Relating Excel functions with DAX functions
  • Variables
  • Tables Referencing
  • Filtering
Visualizations
  • Create simple visualizations
  • Combination charts
  • Slicers
  • Map Visualizations
  • Tables & Matrix
  • Scatter Charts
  • Waterfall & Funnel Charts
  • Gauges & Single Number
  • Cards
  • Styling the visualizations
  • Categories with no data
Introduction to Power BI Service
  • Sign up for Power BI Service
  • Datasets
  • Reports
  • Dashboards

Publish from Power BI desktop to Service

Import an Excel table in Power BI Service directly

Import from data other Services
  • Mailchimp
  • Google Analytics
  • QuickBooks
Dashboards 
  • Pinning the required reports from across reports
  • Ask questions on your data
  • Focus mode
  • Edit tile details
Power BI Mobile
  • Installing the App
  • Viewing Dashboards
  • Viewing Reports
  • Sharing

Why learn Macros

  • Repetitive tasks
  • Automation
  • User form-based entry

Recording Macros

  • Activating the Developer Tab
  • What is a macro
  • Recording a macro
  • Running a recorded macro
  • Basic editing a recorded macro
  • Saving a Macro Enabled Workbook

Introduction to the VB Editor  

  • Various Windows
  • Personal macro WB
  • Writing a basic code
  • Color coding
  • Errors

Basic Coding

VBA Basics
  • Cells and Ranges
  • Variables
  • Objects
  • Worksheets/Workbooks
  • With
  • Message Boxes

Absolute & Relative References

  • Basics of Offset
  • Using Relative Referencing
  • Coding with Offset

Loops & Events

  • If Then Else
  • Select Case
  • For Next
  • Do While
  • Workbook open
  • Change

Userform Based Application

Basics
  • Creating a form
  • Form controls
Programming
  • Different Fields
  • Submit
  • Porting the data to Excel sheets
  • Cancel
Protection
  • Restrict Delete
  • Protect Sheet options
  • Password based Delete

User Defined Functions

Basics
  • Searching on the internet
  • Using a ready-made UDF
Programming
  • Creating your own UDF’s
  • Steps in writing code for it

Introduction to BI

  • Connecting to Data
  • Getting Started with Data
  • Managing Extracts
  • Saving and Publishing Data Sources
  • Data Prep with Text and Excel Files
  • Join Types with Union
  • Cross-database Joins
  • Data Blending

Visual Analytics

  • Drill Down and Hierarchies
  • Sorting
  • Grouping
  • Additional Ways to Group
  • Creating Sets
  • Working with Sets
  • Parameters
  • Formatting
  • The Formatting Pane
  • Tooltips
  • Trend Lines
  • Reference Lines
  • Forecasting
  • Clustering

Calculations

  • Getting Started with Calculations

Dashboards and Stories

  • Getting Started with Dashboards and Stories
  • Building a Dashboard
  • Dashboard Objects
  • Dashboard Formatting
  • Dashboard Interactivity Using Actions
  • Story Points

Database Basics: Concepts and need of a database

  • What is a database?
  • What is SQL?
  • Database Learn Data Modeling
  • What is Normalization? 1NF, 2NF, 3NF & BCNF

SQL Fundamental: Selecting and Filtering Data

  • MySQL Installation
  • MySQL Create Database & MySQL Data Types
  • MySQL SELECT Statement
  • MySQL WHERE Clause with- AND, OR, IN, NOT IN

SQL Fundamental: Updating Data

  • MySQL query INSERT INTO Table
  • MySQL UPDATE & DELETE Query
  • Sorting in MySQL ORDER BY, DESC and ASC

SQL: Data Aggregation and Functions

  • MySQL GROUP BY and HAVING Clause 
  • MySQL Wildcards : Like, NOT Like, Escape, ( % ), ( _ )
  • MYSQL Regular Expressions (REGEXP)
  • MySQL Functions
  • MySQL Aggregate Functions: SUM, AVG, MAX, MIN COUNT, DISTINCT
  • MySQL IS NULL & IS NOT NULL
  • MySQL AUTO_INCREMENT
  • MYSQL – ALTER, DROP, RENAME, MODIFYMySQL LIMIT & OFFSET

SQL: Complex Query Building

  • MySQL SubQuery
  • MySQL INNER, OUTER, LEFT, RIGHT, CROSS
  • MySQL UNION – Complete

SQL: Query Optimization

  • Views in MySQL: Create, Join & Drop
  • MySQL INDEXES – Create, Drop & Add Index

 

100+ Hours of Hands on Assignments

Hands-on Assignments

Modules Assignments

Note: The following list is not comprehensive. We add/edit assignments based on feedback from both industry experts and participants.

Data Cleaning and Validation

The assignment consists of a dataset of Office Products alongside its region-wise sales data. The learner is asked to work with Excel to perform data validation, calculations, and cleaning

  • Data summarisation for several sales based questions
  • Data analysis using filters and sorting to understand the sales performance
  • Cleaning data and cells by using functions like Trim, Upper, Lower, Proper
  • Data Validation rules to maintain integrity of the product data
  • Working with DATE format and calculate things like delivery date, payment due date

Data Manipulation with Advanced Excel

The assignment consists of a dataset of HR employee data. The learner is asked to work with data using the database functions to perform analysis on data.

  • Use of Vlookup, Hlookup
  • Working with Named Ranges
  • Working with the Math functions
  • Advanced Lookup Functions
Excel Tables and Data Analysis

This assignment will make the learner practice the concepts that will be very handy and effective for analyzing data is a powerful skill that helps you make better decisions. Some of the tasks in this assignment are to work as a sales leader to deep down on the teams performance:

  • Sheet protection to allow only addition of fields
  • Auto count data fields
  • Creating sub-totals based on different product categories and rages
  • How to generate maximum profit using product combinations
  • Perform analysis to calculate the  sales for a  specified profit goal
  • Calculate on profit (or loss) based on selling price
Data Visualization

This assignment focuses on helping learners create visual representation of data by creating:

  • Pivot tables for different types of groups
  • Sparkling Charts for monthly data
  • Calculating things like Top 3 and Bottom 3 sales using Array functions
Statistics

The assignments focus on making learners practice the fundamentals of applied Statistics for topics like

  • Calculating Probability
  • Working on Descriptive Statistics
  • Calculating a Trendline Equation
  • Calculating the coefficient of determination or R Squared Value
  • Finding Coorelation between series
SQL

The assignments under SQL enable the learners to practice end to end scenario of reading Sales owner and sales order tables and then do a series of data retrieval, and filtering steps. This practice exercise enables the learner to become confident in using SQL for the basic data filtering and analysis.

  • Fetching records
  • Filtering
  • Joining multiple tables
  • Creating views for specific scenarios
  • Display data based on conditions based on timeline
Power BI

Power BI assignment will enable a learner to work on an interesting trading dataset consisting of scrips, and client holding data.

  • The tasks will include a coverage of typical tasks used for Analytics like
  • Understanding relationships in data
  • Calculating new data
  • Creating multiple set of visualisations
  • Creating filters
  • Creating map data
  • Doing analysis 
  • Create role-wise Dashboards for the business leaders in the organization

3 Projects

Capstone Projects

Every participant is mandated to solve one Capstone Project for Certification. The learner is encouraged to solve all available projects to sharpen the skills across several domains.

Risk Assessment of Credit Card Customers

Duration: 2 Weeks

Project Description:

This Capstone project is a very real and interesting problem from the Finance Industry. The given dataset is a list of Credit Card customers, with their billing and payment history of 6 months. You will be working and analysing credit card customers’ data in terms of risk assessment. You will be able to identify what kind of customers are risky, what kind are not, which one’s should be blacklisted, etc

Key Takeaway:

The project will enable you to put your analytical technique and tooling skills to use in a real-world business scenario. The learner will be able to see how the various capabilities gained through the learning help to analyze a customer data, and use the information to bring out insights into the business for decision-making, in this case, help business to do a risk assessment.

    Computer Rental Business

    Duration: 2 Weeks

    Project Description:

    Large Corporations, Small Business & Freelancers are in need of computers/laptops for a short duration. This computer rental company gives out the devices at a fraction monthly cost of the actual product value. This Capstone project is seeing the power of running such a business efficiently using the tools most accessible to companies – Microsoft Excel, MS SQL, PowerBI. The business is looking to optimise its operations, efficiency, and business.

    Key Takeaway:

    Somebody apt at data-based decision-making, here is the opportunity to work on a complete business scenario and enabling with several key insights for the business. The data analyst is being asked to build the structure, create validation rules, use several formulas to keep a tab on daily operation status and finally generate reports, creating powerful visualisations.

      Indian Premiere League(IPL) Data Analysis

      Duration: 2 Weeks

      Project Description:

      This Capstone project will help you analyze the data from the world of Cricket – Indian Premier League for several years. The approach to this project is to think, define, design, and work your way to do the kind of analysis one sees by the Sports Journalists.

      Key Takeaway:

      The learner is asked to use different features and functions to arrive at answers and also the optimal method to get to the quickest and best way to find the solution. Also, considering the popularity of this data domain, the learners are encouraged to go beyond the questions and look for further analysis based on interest as they watch the game.

        For further information on Internship

        Data Analytics Trainings Schedule

        Live Classes

        3 hrs per week

        }

        Duration

        16 Weeks

        j

        Assignments

        5 hrs per week

        Upcoming batches starting on

        23 Oct, 2019 (Sunday)

        10 AM -1:30 PM (IST)

        23 Oct, 2019 (Sunday)

        10 AM -1:30 PM (IST)

        23 Oct, 2019 (Sunday)

        10 AM -1:30 PM (IST)

        If you do not like the training, take 100% refund within 15 days of training!

        Data Analytics Tools

        You will become comfortable with the tools widely used in the industry by Data Analysts.

        icon service 2

        Tool: Microsoft Excel

        Microsoft Excel continues to be the most widely used tool for data analysts. The learners will learn basic to advanced features of Excel to perform data crunching, data manipulation validation, analysis, statistical analysis, and reporting.

        icon service 2

        Database: MS SQL

        MS SQL is Microsoft’s database. The learners will learn the fundamentals of SQL, enabling them to be confident in their knowledge on how data is stored and retrieved with ease.

        icon service 2

        Tool: PowerBI

        Power BI is a collection of software services, apps, and connectors that work together to turn unrelated sources of data into coherent, visually immersive, and interactive insights. The learners will learn to use the tool for its effective use for story-telling with data.

        icon service 2

        Tool: Tableau

        Tableau is the analytics platform that disrupted the world of business intelligence. Learners will be introduced to this tool and its capability to visualize data and work with dashboards.

        100+ Hrs of Instructor-led Online Classes

        Why Learn Online?

        If you are serious about mastering Digital Marketing, forget Classroom Training.

        Experience it yourself through a one-to-one Live Demo session!

        We take 100s of demo sessions every day!

        Best Trainers

        Learn directly from 10+ Digital Marketing experts with up to 18+ years of Experience

        Attend from Anywhere

        You can attend live sessions from anywhere in the world including on Mobile.

        No Need to Travel

        Save upto 120 hours of precious time, which would otherwise get wasted in travel.

        Interactive & Practical

        100+ hours of assignments ensure that you are learning Digital Marketing hands-on.

        Class Recordings

        Even if you miss a live session, you can learn through the recording, which stays with you forever.

        Lifetime Updates

        Digital Marketing is dynamic. You will have access to the revised content for the lifetime. 

        Industry Experts as Trainers

        Trainers

        Get Trained by the Experts with extensive experience with the tools for analytics.

        Rushabh

        Rushabh Shah

        13+ Years

        Mandar Mulay

        Mandar Mulay
        19+ Years

        Jeet Shah

        Jeet Shah
        5+ Years

        Dilnoor

        Dilnoor Singh
        8+ Years

        Certifications

        Facebook marketing certi website image

        Data Analytics Master Certificate

        How to get this Certificate?

        On successful completion of all assignments and 1 Capstone Project, the participant will get a Certificate issued by Digital Vidya.

        Facebook marketing certi website image

        Data Analytics Master Certificate

        How to get this Certificate?

        On successful passing of the VSkills examination, the participant will get a Certificate issued by VSkills.

        For further information on Certifications

        What do our Customers say about us?

         

        An excellent package to understand for people who are uninitiated to this domain. Comprehensive.

        Nishant Bhushan

        Product & Pricing Head

        The best part of the course was the use of basic programs which can be used easily in our day to day business activities, this made it more interesting and competent.

        Vishakha Kadam

        Data Analyst

        Had a great experience learning with Digital Vidya. Great trainers, hence great sessions! Support team is also very helpful and had the issues resolved at the earliest.

        Aishwary Deshmukh

        Student

        The trainer was very knowledgeable and was patient enough to answer all the queries and run us through the exercises in the class.

        Jayasuryan C

        Senior Project Manager

        A task that was taking a day to complete, is now done in only 3 hours with Digital Vidya: Excel and Power BI course.  Rushabh Sir is indeed a great teacher!

        Vasuda Beekarry

        Analyst

        The Course was a great learning experience especially coming from a non-technical background, good guidance in solving all queries with help of trainers.

        Prasiddhi

        Student

        Data Analytics Course FAQs

        Who is the Data Analytics course for?

        This course is structured and curated for people from all functional areas like HR, Marketing, Sales, Supply Chain, Customer Satisfaction, Logistics, Accounting & Finance, Operations, Trading, Administration, Purchase & Production, Manufacturing, Research & Development

        The curriculum allows learners to enhance their analytical skills and bring higher efficiency into their roles, function, and business by moving to data-based decision making.

        What will I do if I miss my Instructor-Led Online class?

        You will be getting access to the recording of every class for your own revision and practice. In case you miss a class, it is important that you go through the topics using the  recording prior to the next class, so that you can follow through the next session effectively. Also, you will be required to submit the assignment on time even for the missed class.

        What should you expect after completing Data Analytics Course?

        This is a comprehensive course that will help you gain an in-depth understanding of end to end data analytics. You’ll build the foundation of statistics, SQL, Data Validation, Data Cleaning, Data Crunching, Statistical Analysis, Charts, Visualisation using both PowerBI and Tableau. With this knowledge you are equipped to handle your data and use it as a tool for decision making, efficiency and business growth, enabling you to propel your own career.

        What is the placement support after this course?

        This course should primarily be to upskill yourself in your functional role. Digital Vidya does not provide any placement support post this course completion.

        Getting Started is Easy?

        w

        Discuss with a Career Advisor

        Not sure, what to learn and how it will help you?

        Attend a Demo Session

        Not sure about online classes?

        k

        Application

        Check the available dates