# with Excel, PowerBI

Online Instructor-Led Training Program

i

Courses

#### 3

Capstone Projects

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
• 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
• 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

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

Match & Index

• Indirect
• Offset
• Choose
• Dependant Dropdowns Dependent Data Validation Lists

#### 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
• 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
• Datasets
• Reports
• Dashboards

Publish from Power BI desktop to Service

Import an Excel table in Power BI Service directly

• Mailchimp
• QuickBooks
##### Dashboards
• Pinning the required reports from across reports
• Focus mode
• Edit tile details
##### Power BI Mobile
• Installing the App
• Viewing Dashboards
• Viewing Reports
• Sharing

#### Why learn Macros

• 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

#### User Defined Functions

##### Basics
• Searching on the internet
##### Programming
• 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
• 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
##### 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

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.

### 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

}

16 Weeks

j

#### Assignments

5 hrs per week

#### 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.

### 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.

### 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.

### 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.

### 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.

Industry Experts as Trainers

## Trainers

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

Rushabh Shah

13+ Years

Mandar Mulay
19+ Years

Jeet Shah
5+ Years

Dilnoor Singh
8+ Years

## Certifications

#### 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.

#### 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

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.

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.

w