Video summary

Data Driven Decision Making | AICTE | BharatCares | Masterclass 5

Main summary

Key takeaways

Educational

Main ideas / concepts covered

1) Internship admin + session expectations

  • Internship “Masterclass 5” is the final masterclass in a 6-week Big Data and Business Management academic internship (sponsored by IBM / AICTE / BharatCares).
  • Agenda for the session:
    • Why data visualization matters
    • Principles of effective visual design
    • How to choose the right charts/visualizations
    • How to communicate insights through data

Attendance and process rules

  • Domain cannot be changed (stays big data and business management).
  • Use the same email ID throughout the internship. If any alternative email was used, report it during attendance.
  • Offer letters will be/are being sent via email (check spam if needed).
  • Attendance is mandatory at the end of each masterclass.
  • Recordings/resources are provided in a resource document; questions go through a query form.

Participation

  • The internship can be done individually or as a team.

2) Excel methodology: finishing a multi-task data-cleaning + analysis workflow

A fictional dataset is incrementally transformed through tasks, demonstrating Excel skills—especially VLOOKUP, pivot tables, slicers, and statistical formulas.

Task 16: Fetch employee name, department, total sales (using VLOOKUP)

  • Uses VLOOKUP on employee ID to fetch:
    • Employee first name and last name
    • Department
    • Total sales
  • Key implementation ideas:
    • Column index correctness in VLOOKUP:
      • First name is in the 2nd column relative to employee ID → use index 2
      • Last name is in the 3rd column relative to employee ID → use index 3
    • Handling errors:
      • Use the 0/0 approach to reduce visible Excel errors (related to returning NA vs suppressing errors).
  • Common pitfalls covered:
    • Fixing “reference does not exist” by selecting correct lookup ranges/columns.
    • Preventing references from “sliding” when dragging:
      • Use $ absolute references to lock key ranges so VLOOKUP searches the same dataset range even when copied down.
  • Reducing repeated work:
    • Prefer copy-paste + controlled absolute/relative references so each cell pulls the correct field.

Task 17: Pivot table—average salary by department

  • Insert pivot table, then:
    • Put Department in Rows
    • Put Salary in Values
    • Change aggregation from default (Sum) to Average
  • Notes:
    • Quick validation via counts/averages.

Task 18: Pivot table—total sales by region

  • Similar pivot table flow:
    • Ensure at least two rows/columns of source data are selected for pivot creation.
  • Use:
    • Region/Location in Rows
    • Total Sales in Values (keep default Sum)

Task 19: Add slicers for dashboard filtering (department + region)

Two slicer approaches are shown:

1) Slicers on a table - Convert a dataset into a formatted Excel Table (Format as Table). - Use Insert Slicer (from Table Design): - Add slicer for Department - Add slicer for Region/Location - Behavior: - Selecting values filters the table visually (Tableau-style filtering).

2) Multi-slicer interaction demo - Selecting “Channel sales” then “Bangalore” narrows results further.

3) Financial/statistical calculations in Excel (compound/loan style tasks)

The session shifts from visualization prep to quantitative Excel formula tasks.

Monthly loan repayment (PMT)

  • Uses PMT to compute monthly repayment:
    • Principle / Loan amount
    • Interest rate / 100 adjustment
    • Term converted to months (years * 12)
    • Sign convention (negative principle input)
  • Interpretation:
    • Example demonstrates monthly repayment for a loan scenario.

Future value and Present value

  • Future value depends on:
    • Remaining loan age (time left)
    • Monthly payment and interest rate
    • Output trends toward zero when the loan age completes
  • Present value emphasis:
    • Same loan amount vs present value value might align in specific setups
    • Key teaching: parameters change depending on whether you compute present vs future values.

Median, Mode, Standard Deviation

  • Add columns for:
    • Median salary using MEDIAN()
    • Mode salary using MODE() (highest frequency)
    • Standard deviation of sales using STDEV... (shown as STDEVBS in the demo)
  • Insight interpretation:
    • If median ≈ mode, data may be concentrated
    • Standard deviation indicates how scattered sales values are.

4) Building the dashboard: Tableau workflow + dashboard design principles

The session transitions from Excel into Tableau to create a final dashboard project.

Data preparation (cleaning for dashboard readiness)

  • Starts with raw government-style census data (example from India census tables).
  • Cleanup checklist (conceptual):
    • Remove unused columns (IDs/codes not needed)
    • Unmerge merged cells (ensure clean row/column structure)
    • Fix headers into separate columns
    • Remove “Total India” aggregate rows that would double-count during aggregation
    • Use filtering for clean categories (state/district/subdistrict labels; rural/urban categories)
    • Remove unwanted characters/substring artifacts (e.g., “and”, “uninhabited/add” variants)
      • Uses text cleaning and Text to Columns via delimiter splitting
    • Trim and clean hidden whitespace characters
    • Convert stored text numbers into numeric types (convert to number)
    • Optionally fill missing values (not available / zero / mean/median/mode)

Tableau dashboard construction principles

  • Prioritize ease of viewing data over making something “beautiful but hard to use.”
  • Use multiple elements thoughtfully:
    • Bar chart, line chart, pie chart, maps, KPI cards/text highlights
  • Demonstrations:
    • Correct geographic role assignment for mapping:
      • Set fields like Name to a geographic role (e.g., State)
    • Why too-granular geography can clutter maps:
      • States vs districts vs cities (thousands of points)
    • Use filters and “Use as filter” for interactivity.

Example dashboard components (shown during demo)

  • Category vs households (bar)
  • Male vs female population by state (line chart)
  • Population distribution using pie chart (average population by location levels)
  • Map visualization:
    • Color, labels, and measures like population per area
  • KPI cards:
    • Area, population, number of households, number of towns, etc.
  • Adds images and formatting:
    • Insert image objects (download from web, add to dashboard)
    • Adjust colors and dashboard styling
  • Publish and share:
    • Save to Tableau Public
    • Generate/share a link and embed code

5) Concept note and final presentation: purpose + required contents

A major part of the later section explains what to write and what to include for submissions.

Concept note (what it is and how to structure it)

  • Purpose:
    • Impress at “first glance” and increase selection chances for a deeper pitch.
    • Used like a gatekeeper before a detailed presentation (Shark Tank analogy).
  • Length guideline:
    • Max 2–3 pages (often mostly text).
  • Core components:
    • Introduction
    • Problem statement
    • Objectives
    • Description of the dataset
    • Key performance indicators / proposed dashboard features
      • Examples: interactive maps, bar charts, pie charts, plus filters
    • Expected outcomes / conclusions
    • Dashboard link (important for validity)
  • Formatting advice:
    • Mostly text to fit more info on limited pages
    • Images optional but may increase page count

Final presentation (what to include)

  • Cover similar items in more detailed slide format:
    • Title, intro, problem, objectives
    • Dataset description and variables
    • Proposed dashboard features
    • Visualizations and how interactivity/filtering works
    • Live demonstration link (optional in the example, but link should be included)
  • Must include the Tableau Public dashboard link for validity.

6) Submission + upload workflow (platform guidance)

Admin guidance is provided for offer letters, certificates, and dashboard submission uploads.

Learning plan + course completion certificates

  • Certificates are retrieved from the learning plan page using:
    • “Actions” → View completion certificate / New completion certificate
  • Save/download:
    • Screenshot or print to PDF for upload.

Uploading certificates

  • Upload in the dashboard platform:
    • Team member roles vary (team leader has access to certain uploads).
  • Certificate upload validity depends on:
    • Matching the certificate track name with the logged-in person’s account/learning plan
    • Correct ownership (uploading another person’s certificate can fail)

Concept note and final deliverable submission permissions

  • Upload is only enabled for team leaders for:
    • Concept note
    • Final deliverable
  • Team members submit through their team leader.
  • Upload methods:
    • Upload a file directly (PDF/PPT/DOC) or paste a Google Drive/Docs/Slides link

Project timeline

  • Start projects immediately.
  • Target evaluation date: 20th (project reviews by IBM experts).
  • Project Q&A session scheduled on 16th.
  • Pitch night planned for early/mid August depending on criteria.

Speakers / sources featured (as named in subtitles)

  1. Mr. Kartik Huda (main presenter / instructor; data analytics professional)
  2. Arti (host / coordinator; announcements, wrap-up, dashboard + upload guidance)
  3. IBM (referenced as sponsor and evaluators; not a personal speaker)
  4. AICTE (referenced; not a personal speaker)
  5. BharatCares (referenced; not a personal speaker)
  6. Tableau (tool; not a personal speaker)
  7. Excel / Microsoft Excel (tool; not a personal speaker)
  8. WhatsApp group / “resource document” / “query form” (platform resources; not personal speakers)

Original video