The course is combination of Advanced Excel, Excel Macro’s(VBA), SQL, Statistics Essentials, and Tableau. This training will help to work as an Data Analytics. The course content mentioned on this page is for begineers to expert.

MS Excel –Advanced Analytics Using Excel Total Duration: 8 hours

Introduction to Excel

• Navigating Worksheets
• Formatting and Editing Worksheet Data
• Grouping Data, Subtotals and Data Validation

Working with Formulas and Functions

• Referencing Functions
• Filter, advance filter
• Conditional formatting

Logical functions using Excel

• AND, IF, NOT, OR, TRUE, FALSE
• CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, EXACT etc

Analyzing Data with Excel

• Consolidating Worksheets (VLOOKUP/HLOOKUP, INDEX, MATCH, etc)
• Using Pivot Tables

Statistical and Mathematical functions

• Sum, Count, Sumifs, Countifs
• Rank, quartile, decile, percentile
• Standard deviation, correlation, mean, median

Data Prep & Reduction techniques

• Need for data preparation
• Outlier treatment
• Flat-liners treatment
• Missing values treatment

Text and Date Functions

Data Validation

Workbook and Worksheet Protection

• Worksheets Security
• Shared Workbook
• Track Changes

Creating Charts and Graphics

• Simple charts
• Pie charts

Pivot Table and Charts

• Pivot Table Creation
• Analysing Pivot Table from different – 2 styles
• Pivot Creation from multiple sources
• Slicers

Data Connections

• External Data Connections
• Data Object Model
• Update reports from external sources

Data Analytics Using Excel

• Basic statistics; descriptive and summary
• Frequency, crosstabs
• Best in class evaluation
• Performance benchmarking
• Correlation analysis
• Key drivers analysis
• Association analysis
• Rules based Segmentation
• Interpretation of results

MS Excel – Dashboarding Total Duration: 6 hours

Overview of Dash boarding

• What is dash board & Excel dash board.
• Principles of great dashboard design
• Common mistakes dashboard design
• Selecting the correct chart to display data
• Adding icons and images to dashboards
• Effective use of colour and logos
• Making dashboards dynamic

Deciding On Dashboards

• Deciding On Dashboards
• Reports, Dashboards And Scorecards
• Selecting Measures And Metrics

Creating your Excel Dashboards

• • Dashboard Do’s and Don’ts
  • Data Layout Creating Dynamic Dashboards
  • Merging and Consolidating Data Using Shapes to make Charts more attractive
  • Using Alerts to draw attention to
    • Interactive Charts
    • Charts tips and tricks

Adding Form Controls

• • Why use Form controls
  • Linking charts to Form controls
  • Add dashboard interactivity via controls
    • Dropdown lists
    • Check boxes, Spin button, Radio button

Advanced Power Charting Techniques

• Thermometer Chart
• Doughnut Chart
• Pareto chart
• Population Pyramid Chart
• Waterfall Chart

Interactive Charts

Charts with Combo box, Spin Button, Radio Buttons and Scroll bar

• Bullet-proofing your Dashboards
• Protect dashboards by locking cells
• Password Protection, Restricting incorrect data entry with data validations
• Using worksheet protection to prevent entry
• Protecting your dashboard files using “Read Only” and password protection

Charting (Pivot Charts & Normal Charts)

• • • Pivot Charts & Visual Dash boarding Techniques
    • How to select the Right Charts for your Data
      • Overview of chart types and their suitability to dashboards
      • Bar, Line, Bubble charts
  • Advance charting
    • Combining different chart types within one plot area
    • Creating user defined charts
    • Saving and using chart templates
• Creating and customizing Charts
• New Features of Excel – sparklines etc.
• Design principles for including charts in dashboards

Practical Dashboard Creation: Hands-on Dashboard Creation

• Complete Management Dashboard for Sales & Services
• Creating a Sales Dashboard
• Creating a Services Dashboard
• Creating a HR Dashboard
• Best Practices in Dashboard Design

MS Excel – VBA Total Duration: 16 hours

Introducing VBA

• What is Logic ?
• What is VBA ?
• Introduction to Macro Reacordings, IDE

How VBA Works

• Working In the Visual Basic Editor
• Introducing the Excel Object Model
• Using the Excel Macro Recorder
• VBA Sub and Function Procedures

Key Components of Programming language

• • Essential VBA Language Elements
    • Keywords & Syntax
    • Programming statements
    • Variables & Data types
    • Comments
    • Operators
• Working with Range Objects
  A look at some commonly used code snippets

User Defined Functions

Programming constructs in VBA

• • The With The With -End with Block
  • Control Structures
  • Looping Structures
    • For Loop
    • For Each
    • Do While

Functions & Procedures in VBA – Modularizing your programs

• Worksheet & workbook functions
• Automatic Procedures and Events

Objects & Memory Management in VBA

• The NEW and SET Key words
• Destroying Objects – The Nothing Keyword

Communicating with Your Users

• Simple Dialog Boxes
• User Form Basics
• Using User Form Controls
• Accessing Your Macros through the User Interface

Arrays

• Single Dimensional Arrays
• Multi Dimensional Arrays

Error Handling

• Default Error Type
• Ignoring Errors
• Controlling Errors
• Controlling accessibility of your code – Access specifiers.
• Code Reusability – Adding references and components to your code

Access Database and Retrieve Data from Access to Excel

• Create a New Table and Database in MS Access
• Load the Data into Access Database
• ADO(Active Database Objects) Concept

Case Study:

Sales Data Consolidation from multiple sources into single workbook.

MS Excel – Tableau  Total Duration: 16 hours

Getting Started

• Introduction to Tableau & Architecture
• My Tableau Repository
• Connecting to Data sources
• Understanding the Tableau workspace
• Dimensions and Measures
• Data Types & Default Properties
• Tour of Shelves & Marks Card
• Building basic views
• Saving and Sharing your work-overview

Data Manipulation

• Sorting
• Trend lines
• Reference Lines
• Forecasting
• Filters
• Context filters
• Sets
• In/Out Sets
• Combined Sets
• Grouping
• Bins/Histograms
• Drilling up/down – drill through
• Hierarchies/View data
• Actions (across sheets)

Basic Data Analysis

• Date Aggregations and Date parts
• Cross tab & Tabular charts
• Totals & Subtotals
• Bar Charts & Stacked Bars
• Line Graphs with Date & Without Date
• Tree maps
• Scatter Plots
• Individual Axes, Dual Axes & Combination chart
• Parts of Views

Maps

• Explain latitude and longitude
• Default location/Edit locations
• Symbol Map & Filled Map
• Custom Geo Coding

Calculated Fields

• Working with aggregate versus disaggregate data
• Explain – #Number of Rows
• Basic Functions (String, Date, Numbers)
• Usage of Logical conditions
• Explain scope and direction of table calculations
• Percent of Total, Running / Cumulative calculations

Parameters

• Create What-If analysis
• Using Parameters in
• Calculated fields
• Bins
• Reference Lines
• Filters/Sets
• Display Options (Dynamic Dimension/Measure Selection)

Building and Customizing Dashboards

• Combining multiple visualizations into a dashboard (overview)
• Making your worksheet interactive by using actions
• Filter/URL /Highlight
• Options in Formatting your Visualization
• Working with Labels and Annotations
• Effective Use of Titles and Captions

Building and Customizing Dashboards

• Multiple Table Join, Data Blending
• Difference between joining and blending data, and when we should do each
• Working with the Data Engine / Extracts, Custom SQL
• Toggle between to Direct Connection and Extracts
• Options in Formatting your Visualization
• Working with Labels and Annotations
• Effective Use of Titles and Captions

DATABASE CONCEPT’S Total Duration: 24 hours

The Fundamentals – Objectives

• Describe the seven main Access 2016 objects
• Start Access and open a database
• Understand the Access Program Screen
• Add, Edit and Delete Records in Table View
• Open and run a query
• Open and run a report
• Close down and exit Access

RDMS Concept

• Schema –Meta Data –ER Diagram
• Looking at an example Database design
• Data Integrity Constraints & types of Relationships
• Basic concepts – Queries, Data types & NULL Values, Operators and Comments in SQL
• Rest of the story – Joins, Indexes, Functions & Views
• Creating Relational Databases – Objectives
• Understand table relationships
• Create relationships between tables
• Understand referential integrity
• Implement and test referential integrity
• Understand relationship types
• Finding, filtering and formatting data – Objectives
• Find and replace information
• Sort records
• Filter records
• Adjust and re-arrange rows and columns
• Freeze fields
• Hide columns

Working with tables and fields – Objectives

• Understand field properties
• Index a field
• Change a field’s data type
• Apply formats to number and text fields
• Set default values and apply field validation
• Create input masks
• Create and modify lookup fields
• Create value lists

Creating Queries

• Creating Lookup queries
• Queries using GUI
• Queries using multiple tables

Creating reports

• Single-column reports
• Tabular reports
• Mailing label reports
• Creating summary reports
• Creating groups / totals reports

Introduction to SQL

• A quick Introduction of SQL Management System
• Utilizing the Object Explorer

Data Manipulation

• Basic Select statement
• Additional components – Where, Group By, Order by & Having clauses

DDL Commands

• Creating, Modifying & Deleting Tables
• Create Table & Create Index statements
• Insert, Update & Delete statements
• Drop & Truncate statements – Uses & Differences
• Alter Table & alter Column statements

Optimize your work

• Sub-queries vs. Temp Tables vs. Joins
• Stored Procedures
• Optimizing for Composite keys & Non-numeric Primary keys

What is the eligibility criterion to enroll in our courses?

Our courses can be pursued by individuals from multiple educational i.e. from begineers & professional backgrounds both can pursue the program. Analytics is a techno functional area wherein one needs to understand the business process and apply predictive modeling for better business understanding. Today, the kind of data we are generating is been used in every field. Professionals can also look at restarting their career in Analytics given the kind opportunities available.

Can you share more course details like duration of session and the class schedules? Could you also share details on the mode of instruction-is it virtual or classroom training?

Our program has been specifically designed for beginneer who wants to become working professionals as well as fresher and is available over weekends in regular mode. Each session is of 2 to 3 hours each. The module is available in online training only. The training program is available worldwide.

In case of online training, is it pre recorded?

The training is live in nature and generally we put maximum 6 candidates on each class. There are no pre- recorded sessions. It is extremely important for us that all the professionals receive real time training for better learning & development.

Is it certified? / Do you provide certifications?

Yes. There is an evaluation process one has to undergo become a Data Analytic Certification. The certification is well recognized in the industry and accepted all over. The program is in fact a result of rigorous industry feedback.

Why BSAI Academy? / Why should I enroll in your program?

It is industry oriented training. The program is a result of collaboration of subject matter experts & industry professionals. The program focuses on help you build your analytics & intelligence tools knowledge right from the basic conceptual parts to advanced application based hands on learning. The entire training is to make you working professional and includes case studies as well. It is an end to end solution.

What if I miss a session?

We provide recorded video of your class through which you can follow your missing class by watching video file.

What if I have doubts post a particular session?

We expect the tutees/students to write to us with the necessary doubts and schedule a session based on mutual availably. You can give us a CALL at +9779869302993 OR email at info@bsaiacademy.com

What if I want to attend an entire session again?

You can. Revisiting a entire session is chargeable and depends up on the ongoing schedule and availability of our instructor/tutors.

What is the training methodology? Is it conceptual / hands on? Are there any projects / case studies?

The aim over here is to have industry ready professionals. The training program is a combination of conceptual understanding of the basics and hands on understanding of advanced concepts. The entire training is lab based and is delivered via projects & case studies from multiple business domains / verticals to give you an overall exposure in to the analytics industry.

What are the career and job prospects after completion of this certification ?

According to IDC1, “The big data and analytics market will reach $125 billion worldwide in 2015. Further on, IDC also predicts, “Clearly IoT (Internet of Things) analytics will be hot, with a five-year CAGR of 30%.The above statistics clearly highlight the vast potential this industry has and this directly means that industry ready data analytics professionals will be needed to take on this mandate. In terms of the availability of jobs, IDC sees a clear shortage of industry ready professionals in this high growth area. For e.g. IDC found that in the U.S. alone there will be 181,000 deep analytics roles in 2018 and 5x that many positions requiring related skills in data management and interpretation. This sheer demand will require skilled and well trained professionals who can make successful global careers.

All of the above indicators support the vast potential this industry has and our program will surely help you getting potential career track. One of the key highlights includes the opportunities available to people who are looking to switch their careers and wish to remain relevant in a fast paced industry. This career path has immense opportunities for newbie and professional both.

Will my previous experience be accounted?

Yes, the industry is hiring professionals with relevant experience in a particular domain / field with expertise in to Predictive Modeling / Analytics techniques and concepts and knowledge of BI tools such as SAS / R. The industry expects these professionals to be able to run these concepts over these tools while leveraging on their experience & expertise. You can start as a fresher but if you have a analytic experience then it will surely hepl you to get a role according to your experience level.

Which are the companies recruiting in to Analytics?

There are almost 700 plus companies recruiting in to Analytics in India. Organizations such as Accenture, IBM, Infosys, Genpact, MuSigna, WNS, SAS, Fractal Analytics, ZS Associates, Inductis, KIE Square, HDFC Bank, ICICI Bank, HSBC Analytics, Axis Bank, Shoppers Stop, Snap Deal, Flipkart, Myntra, Cipla, GSK, Ranbaxy, Vodafone, Airtel etc.

Do I need to have an IT / Computer Science / Engineering background to do the program?

No. The training module is developed to help you learn right from the basics to advanced knowledge. Having an IT / Computer Science / Engineering background can be an added advantage to help your grasp faster. However the program doesn’t require you to have such knowledge.

Do I need to have an Economics / Mathematics / Statistics background to do the program?

No. The training module is developed to help you learn right from the basics to advanced knowledge. We start right from mean, media, mode etc. Having an Economics / Mathematics / Statistics background can be an added advantage to help your grasp faster. However the program doesn’t require you to have such knowledge.

Do I need to do all the modules? Is the program sufficient for me to pursue a career in to Analytics?

Yes. The program is a result of collaboration of subject matter experts & industry professionals based on industry feedback and research. A combination of all these modules is suggested for you to make a successful & highly rewarding career in this highly growth oriented sector. The program is more than sufficient to help you get that break and pursue your dream career.

Do you provide with the software?

No, this cannot be provided as we need to comply with the various corporate policies governing restricted use of all software. We are not authorized to distribute the same.There is provision for students to follow our tutor/instructor for enviroment setup. Our tutor/instructor will guide you on this.

Is flexi payment option available?

Yes, there is provision to pay in installments.

Are there any discounts?

At current there is no discount but we might bring Special Offer on the course. Visit our website frequently.

Is it feasible to attend a demo session before enrollment?

Unfortunately participation in a live class without enrollment is not possible. However, We can provide you the sample class recording and it would give you a clear insight about how are the classes conducted, quality of instructors and the level of interaction in the class.