Lecture Notes: Data Analysis and Optimization (in Hindi)

Project Overview

• Project Name: Crunchy Corner Project
• Topics Covered: Advanced data analysis, Power BI, business insight, optimization, budgeting
• Reference: Previous 99.5 hours video on sales data analysis
• Utilized clean sales or CRM data
• Techniques: Line Chart, Bar Chart, Pie Chart, Column Chart
• Goal: Understanding how actual industry work is done using Power BI
• Transition from basic developer to business consultant

Prerequisites

• Watch 99.5-hour video (Basic to Intermediate Level)
• Recommended for understanding basic concepts before advancing

Goals and Approach

• Solving real problems for Crunchy Corner client (fast food restaurant chain)
• Focus on optimization and budgeting
• Understand business, prepare data, create advanced analysis

Data Preparation

1. Data Understanding: Crunchy Corner

   • One of the largest fast-food chains in India
   • Data from 2020 to Q1 2024
   • Divided into Quarters and Months

2. Data Structuring: Key components

   • Columns: Year, Quarter, Month, Cluster Head, SKU Code, Category, Sub-category, Channel, State, Volume, Sales, Profit, etc.
   • Key Heads: Bilal, Dheeraj, etc.
   • Categories: Cakes, Fries, Fresh, Protein Pack, etc.

3. Data Manipulation and Cleaning: SQL, Excel Operations

   • Ensure data cleanliness and unique representation
   • Filter and sort data correctly
   • Handling missing values

4. Dimension and Fact Tables: Necessary for analysis

   • Created dimension tables for Product, Channel, Location, etc.
   • Linked with fact tables for proper data modeling

Data Analysis and Insights

1. Basic Metrics Calculation

   • Metrics: Net Revenue, Gross Profit, EBIT, PAT, Volume Metric
   • Entity relationships for Cluster Heads, Channels, and Locations

2. Relational Data Modeling

   • One-to-Many Relationships: Essential for accurate insights
   • Star Schema: Keep central table as Dimension Date, link others
   • Linked data such as unique SKUs to dimensions

Financial Performance Analysis

1. Objective: Provide financial performance overview, historical performance, trends, and optimizations
2. Performance Indicators:
   • Total Number of unique SKUs Sold
   • Total Revenue (Monthly, Quarterly, Yearly)
   • Comparisons using Card Visual, Line Charts, and Bar Charts
   • Monthly Performance Trends (Overall Sales, Gross Profit, EBIT, PAT)
3. Category and Channel Analysis
   • Revenue split by Category and Channel
   • Visuals: Pie chart or Donut chart for clarity
4. Volume and Gross Profit Trends
   • Trend analysis of Key Metrics

Optimization for Business Improvement

1. Objective: Optimize business processes for better profitability
2. Tools and Techniques: Pareto Analysis, Quadrants Analysis
   • Focus on top contributing SKUs
   • Simplify insights using Tree Map and Bubble Chart
3. Price-Volume-Mix (PVM) Analysis
   • Determine effect on revenue
   • Calculate using SQL scripts & Power BI formulas
   • Decompose changes into price, volume, and mix impacts

Budgeting and Variance Analysis

1. Objective: Compare budgeted vs actual performance
2. Methods and Analysis: PVM, Revenue variance, and Gross Profit variance
3. Key Formulas and Measures
   • Revenue Variance = (Actual - Budget) / Budget
   • GP Variance = Actual GP% - Budget GP%
4. Advanced Analytics Techniques
   • Condition Formatting: Change visuals based on variance conditions
   • Implementation: Using dynamic parameters for flexibility

Final Notes

• Objective: To transform from data handler to insightful business consultant
• Action Items: Practice the provided concepts, explore additional functions, and apply them in real-case scenarios
• Study Tips: Revisit basics, leverage Power BI’s advanced features, and stay updated with new analytics trends

---

Recommended Reading and Practice:

• Advanced Power BI books and resources to deepen understanding.
• Explore financial statements and their analysis.
• Practical projects applying similar concepts.

---