Spreadsheets for Business Decisions — Notes

Business Applications

Introduction to Module 4

Why finance now?
Modules 1–3 built operational and marketing intelligence from spreadsheets. Module 4 closes the loop by adding finance — the language of business performance. The capstone task: act as a business analyst asked by the CFO to prepare a profit and loss statement (P&L) for the year and derive a management information system (MIS) report for investors and the CEO.

Recap of Modules 1–3

Module	Focus	Key Tools & Techniques
1	Data capture & organisation (sales, purchases, inventory)	Basic sheets, ordering decisions
2	Sales analytics	Pivot tables, dashboards, trend visualisation
3	Advanced marketing analytics (invoice line‑level data)	Cohort analysis, customer segmentation

Together they turned raw data into actionable intelligence for inventory/operations, sales, and marketing. The missing piece: finance.

What Module 4 Covers

The Trial Balance (TB) – what it is, how to read it, why it matters.
Build a P&L from a Trial Balance – map TB data into a structured statement.
Analyse percentages, margins, and key ratios within the P&L.
Interpret cost breakdowns and income trends to extract insights.
Cross‑compare marketing data (lead dataset) with the P&L to evaluate marketing efficiency and profitability.

End‑of‑module goal: Use Excel to generate business‑critical financial insights — not just know functions, but apply them to real‑world decision‑making.

Learning Outcome

By the end, you will be someone who can transform a trial balance into a P&L, benchmark metrics, and produce an investor‑ready MIS report. This makes you an indispensable asset in any organisation.

Key takeaways

Module 4 adds the finance function to the operational and marketing skills from prior modules.
Core artefacts: Trial Balance → Profit & Loss Statement → MIS Report.
Focus on percentages, margins, ratios, and cost breakdowns for financial insight.
Cross‑analysis with marketing data reveals the profitability of marketing spend.
The ultimate deliverable is a business‑critical financial insight that supports strategic decisions.

Business Context: A Furniture Startup

The company is a city-based furniture startup producing fixed wooden furniture (wardrobes, kitchen cabinets, beds, shelves) and custom wooden work. Customers order via showroom visits; orders are built in a factory, then delivered and installed. The model is semi-custom – fixed units allow mass production while offering customization of material, finish, and colour. Custom work (tailor-made shape/size) is more expensive and complex. Premium add-on services (civil work, electrical, painting) are high-margin and often bundled.

Billing is stage-wise, tied to project milestones: advance → after design → after manufacturing/delivery → after installation. This creates multiple revenue ledgers per order type.

Order Type	Scalability	Complexity	Margin
Standard fixed units	High (predictable sizes)	Low	Lower (competitive)
Custom wooden work	Low (bespoke)	High	Higher
Add-on services	Medium (bundled)	Medium	Highest

The Analyst’s Task: Build an MIS Report

You are a business analyst reporting to the CFO. Your job: create a Management Information System (MIS) report for investors – a summary of business performance across functions. This requires breaking down revenue and expenses from raw accounting data.

The Raw Material: The Trial Balance

The finance team provides a trial balance – a spreadsheet listing every ledger (a category where money is tracked, e.g., rent, salaries, revenue) with debit and credit columns. Each row is a ledger; there are ~120 rows for the year 2024–2025.

Key points about trial balances:

Revenue ledgers have a credit balance (money earned or borrowed).
Expense ledgers have a debit balance (money spent).
In practice, trial balances are split by month, city, or location; centralized costs (e.g., CEO salary) are allocated across branches. The given version is a consolidated annual trial balance for learning.

Understanding the Ledgers

Revenue Ledgers (Credit Balances)

Ledger names reveal business operations. Examples from the transcript:

Supply of projects – finished modular furniture (~₹6 crores) – fixed furniture (the core product).
Supply of projects – custom wooden work – custom furniture.
Supply of civil projects – add-on civil services.

These ledgers show stage-wise revenue collection (see below).

Expense Ledgers (Debit Balances)

Direct costs (variable): COGS (cost of goods sold – hardware, laminates, plywood), Painting charges, Site fitting costs, Civil & plumbing costs, Electrical costs.
Salaries (semi-fixed): broken by team – Design, Sales, Marketing, Customer, Technology, Finance, R&D.
Fixed overheads: Rent (office & showroom), Bank charges, Interest on bank OD (overdraft), Interest on loans (~₹7 lakhs).
Variable marketing: Facebook cost, Google cost.

Revenue Recognition: Stage-Wise Billing

The transcript reveals multiple billing patterns inferred from ledger names and percentages:

Product Line	Stage 1	Stage 2	Stage 3	Stage 4
Fixed wooden furniture	5%	15%	35%	45%
Civil add-on (variant 1)	5%	40%	5%	50%
Civil add-on (variant 2)	10%	40%	50%	–

Each stage corresponds to a project milestone (advance, after design, after manufacturing, after delivery/installation). The percentages sum to 100% per order.

Cost Structure Breakdown

Costs can be classified by behaviour:

Variable (direct with order volume): COGS, painting, site fitting, marketing (Facebook/Google).
Fixed (independent of volume): rent, salaries (most), interest, bank charges.
Semi-fixed: salaries (headcount-driven, not directly per order).

The trial balance includes both – a clue that the P&L must separate COGS from operating expenses (SG&A).

flowchart TD
    A[Walk to Finance Team] --> B[Receive Trial Balance]
    B --> C[Classify Ledgers: Debit vs Credit]
    C --> D{Revenue Ledger?}
    D -->|Yes| E[Group by revenue source]
    D -->|No| F[Group by cost type]
    E --> G[Build P&L: Revenue - Expenses]
    F --> G
    G --> H[Deliver MIS Report to CFO/Investors]

Exam tip: In a trial balance, revenue ledgers always have a credit balance; expense ledgers have a debit balance. When building a P&L, you subtract expenses (debits) from revenues (credits). Misclassifying a ledger’s sign will produce a wrong profit figure.

Key Takeaways

The P&L for this furniture startup must be assembled from a trial balance of ~120 ledgers.
Revenue is recognized in stages (5%, 15%, 35%, 45% for fixed furniture) – each stage is a separate ledger.
COGS and direct costs (variable) are separate from fixed overheads (rent, salaries, interest).
The trial balance reveals operations: standard vs custom vs add-on revenue, team salaries, and financing costs (overdraft interest).
The analyst must classify each ledger correctly and then group into P&L heads (revenue, COGS, operating expenses, finance costs).

What is a PNL?

A Profit & Loss (PNL) statement is a financial report card that shows revenue earned, costs incurred, and the resulting profit or loss. It is built in layers so that the source of profitability (or leakage) can be identified at each stage.

The Layered Structure

Each layer subtracts a specific category of costs, yielding a new margin that reveals how well the business is performing at that step.

1. Revenue

The top line: all income from core products or services.
Often split into gross revenue and net revenue (after taxes like GST).
For a furniture company: project billing, fixed unit billing, custom woodwork, civil projects.

2. Direct Costs (COGS)

Costs directly tied to producing and delivering what was sold (materials, labor, transport, inventory used).
Also called Cost of Goods Sold (COGS).

3. Gross Margin (also called Contribution Margin 0) $\text{Gross Margin} = \text{Net Revenue} - \text{Direct Costs}$

First layer of profit; indicates production and delivery efficiency.
High gross margin → efficient operations or low raw material costs.

4. Direct Marketing Expenses

Spending specifically to acquire customers for recorded sales: Google/Facebook ads, sales team incentives, offline campaigns.

5. Contribution Margin 1 (CM1) $\text{CM1} = \text{Gross Margin} - \text{Direct Marketing Expenses}$

Profit after production and direct sales effort. Reveals marketing efficiency.

6. Indirect Operating Expenses

Costs not tied to a single order: project team salaries, design team salaries, sales team salaries.

7. Contribution Margin 2 (CM2) $\text{CM2} = \text{CM1} - \text{Indirect Operating Expenses}$

Profit after removing as many assignable project costs as possible. Shows whether support teams are overstaffed or underutilized.

8. SG&A (Selling, General & Administrative Expenses)

Central costs not directly linked to projects: office rent, showroom rent, HR/finance/admin salaries, software subscriptions.

9. EBITDA $\text{EBITDA} = \text{CM2} - \text{SG&A}$

Earnings Before Interest, Taxes, Depreciation, and Amortization.
The headline profit number most investors care about; reflects core business health before financial and accounting adjustments.

Flow Diagram

flowchart LR
    A[Net Revenue] --> B[Minus Direct Costs]
    B --> C[Gross Margin]
    C --> D[Minus Direct Marketing]
    D --> E[CM1]
    E --> F[Minus Indirect Operating]
    F --> G[CM2]
    G --> H[Minus SG&A]
    H --> I[EBITDA]

Why Layer? – Diagnostic Power

If this margin is low…	…the likely problem is
Gross Margin	Production/material costs too high
CM1	Marketing inefficient (too much spend for too little revenue)
CM2	Support/indirect teams overstaffed or underutilised
EBITDA	Core business model itself is unhealthy

Industry Variations

Industry	Revenue Sources	Direct Costs	Marketing Focus	Key Customizations
SaaS (Software)	Monthly subscriptions, usage fees	Cloud servers, hosting, customer support	Paid campaigns, trials, affiliate fees (often large)	Gross margin typically very high; CM1 may be skipped. Recurring profit from core tech operations is isolated.
D2C Fashion	Online/offline sales, product categories (shirts, shoes, etc.)	Manufacturing, warehousing, delivery	Facebook, Instagram, influencers, email targeting	CM1 is crucial (marketing game). Profitability is tracked by collection or category.
Furniture & Civil Services	Design fees, advances, post-delivery milestones	Material, labor, transport	Google/Face ads, sales incentives	All CM layers used; granular tracking helps manage mixed standard/custom projects across geographies. Civil projects especially benefit.

Format Customisation

No universal PNL format – the CFO defines one tailored to the business model, cost structure, and reporting needs.
A simple business may stop at gross margin. Complex delivery models (heavy marketing, multiple project types) require multiple contribution margins.
For a startup, the format is built from the trial balance by grouping ledgers operationally.

Exam tip: The multi-layered PNL is a diagnostic tool. Investors look at EBITDA as the headline, but each intermediate margin pinpoints exactly where profitability leaks – production, marketing, operations, or overhead.

Key Takeaways

PNL layers: Revenue → Direct Costs → Gross Margin → (Direct Marketing → CM1) → (Indirect Operating → CM2) → SG&A → EBITDA.
Gross margin measures production efficiency; CM1 measures marketing ROI; CM2 measures indirect team utilisation; EBITDA measures core business health.
Industry customisation is essential – software, D2C, and services each emphasise different layers.
The goal of layering is to isolate the source of profit or loss, enabling targeted fixes.
For a startup without a predefined format, the PNL is derived directly from the trial balance.

PNL Analysis – Building a P&L from Ledgers

A P&L statement is built from the ground up by mapping raw ledger accounts (from a trial balance) into a structured hierarchy. The goal is to isolate performance at successive layers: Gross Margin, Contribution Margin 1, Contribution Margin 2, and EBITDA. Each layer strips away a specific category of cost, revealing how different parts of the business drive profitability.

The P&L Staircase

flowchart TD
    A[Gross Revenue] --> B[− GST]
    B --> C[Net Revenue]
    C --> D[− Direct Costs]
    D --> E[Gross Margin]
    E --> F[− Direct Marketing Expenses]
    F --> G[Contribution Margin 1]
    G --> H[− Project Costs]
    H --> I[Contribution Margin 2]
    I --> J[− Central Costs]
    J --> K[EBITDA]

Revenue: Net Revenue and Its Components

Net Revenue is the top-line after tax. It is built from credit ledger accounts (e.g., woodwork booking, woodwork revenue, civil booking, civil revenue, other revenue). The lecture maps each ledger to a sub‑head under Net Revenue.

Ledger Example	Mapped To
5% booking amount (woodwork)	Woodwork Booking
Later cost, civil booking	Civil Booking
Other revenue (scrap sales)	Other Revenue
Finished modular/furniture	Fixed Units Revenue

Net values are assumed (GST is 18%). Gross Revenue is derived by reversing GST:

$\text{Gross Revenue} = \text{Net Revenue} \times (1 + \text{GST rate})$

Direct Costs → Gross Margin

Direct costs are those directly attributable to delivering the revenue. They are split by project type (woodwork + fixed, civil) and include material, labour, installation, factory, and logistics.

Cost Category	Ledger Examples
Material – Wood + Fixed	Hardwares, laminates, plywood, outsourced interior work
Direct Costs – Civil	Fall ceiling, civil electricity, plumbing, painting, final cleaning
Installation & Delivery	Carpenter costs, inward/outward logistics
Factory Costs	Factory operators, security, electricity, generator, warehouse
Rework & Wastage	Separate head to track month‑on‑month reduction
Other Direct Costs	Fitting charges, outsourced activity

$\text{Gross Margin} = \text{Net Revenue} - \text{Total Direct Costs}$

Direct Marketing Expenses → Contribution Margin 1

Marketing expenses directly tied to generating leads are separated from general branding. They are grouped as Online, Offline, and Referral.

Channel	Ledger Examples
Online	Facebook, Google, Bing ads, website costs, organic channels
Offline	Call centre, lead purchase, agent/influencer commissions, direct events
Referral	Client referral fees (tracked separately from agent referrals)

$\text{Contribution Margin 1} = \text{Gross Margin} - \text{Direct Marketing Expenses}$

Project Costs → Contribution Margin 2

Project costs are the salaries and incentives of teams directly serving projects: sales, design, project delivery, and customer support. This layer reveals the utilisation of key resources.

Sales team salary & incentives
Design team salary & incentives
Project & delivery team salary & incentives
Site visit costs (design team expense)

$\text{Contribution Margin 2} = \text{Contribution Margin 1} - \text{Project Costs}$

Central Costs → EBITDA

Central costs are not directly traceable to individual projects or centres. They include office/showroom, central team salaries, consultants, technology, branding, and general administration.

Central Cost Head	Examples
Office & Showroom	Rent, maintenance, parking, repairs
Central Team Salaries	HR, management, admin, technology, support teams
Consultancy & Professional	Legal, recruitment, marketing consultants
Technology	Software subscriptions, asset/equipment rentals
General Admin	Stationery, broadband, travel, staff welfare
Central Branding	Billboard with logo only (non‑lead‑generating)

$\text{EBITDA} = \text{Contribution Margin 2} - \text{Total Central Costs}$

Exam tip: EBITDA excludes Interest, Tax, Depreciation, and Amortisation by definition. In this construction, interest is intentionally skipped to stay faithful to EBITDA. If a question asks “where would interest appear?”, answer: after EBITDA, before Net Profit.

Key Takeaways

The P&L is built by mapping every ledger from the trial balance into predefined heads, ensuring every cost is assigned to the correct layer.
Gross Margin = Net Revenue − Direct Costs (material, labour, factory, logistics).
Contribution Margin 1 strips out direct marketing (online/offline/referral) to show profitability after lead generation.
Contribution Margin 2 further removes project‑related salaries (sales, design, delivery).
EBITDA = Contribution Margin 2 − central costs (office, central teams, tech, admin).
The structure allows managers to isolate which layer is underperforming – e.g., low Contribution Margin 1 may indicate inefficient marketing spend.
Ledger mapping must be consistent; judgment calls (e.g., whether factory electricity is direct or central) need CFO alignment.

PNL Analysis: Building and Interpreting a Profit & Loss Statement

A profit and loss (P&L) statement summarises revenues, costs, and profitability over a period. Building it from raw ledger data forces a deep understanding of the business; interpreting it reveals where money is made or lost and where to intervene.

Building the P&L from Ledgers

Mapping ledgers to heads

Each ledger (e.g., “Fixed Unit Revenue”, “Wood Direct Cost”) is mapped to a P&L head (e.g., “Fixed Units”, “Direct Wooden Costs”).
This mapping converts a raw trial balance into a structured statement.

Aggregating values

Use SUMIF (or equivalent) to sum all credit ledgers mapped to a head (for revenues) or debit ledgers (for costs).
Net each head as credit‑minus‑debit (or vice versa) so revenues are positive and costs are negative.

P&L structure (from top to bottom)

Head	Derivation	Purpose
Gross Revenue (GMV)	Net Revenue + GST	Total invoice value
GST	–	Tax collected, not company income
Net Revenue	Sum of all revenue heads	True top line
Direct Costs	Sum of direct material, factory, installation, etc.	Cost of goods/services sold
Gross Margin	Net Revenue – Direct Costs	Profit after direct costs
Indirect Costs (direct marketing, branding)	Sum of marketing, branding, referral	Customer acquisition & brand
Contribution Margin 1 (CM1)	Gross Margin – Indirect Costs	Profit after acquisition costs
Project Costs (design, sales, project team)	Sum of team costs tied to projects	Delivery‑related overhead
Contribution Margin 2 (CM2)	CM1 – Project Costs	Profit before central overhead
Central Costs (office, salaries, consulting, tech)	Sum of all corporate overhead	Fixed operational expenses
EBITDA	CM2 – Central Costs	Operating profit before interest, tax, depreciation, amortisation

Exam tip: The P&L hierarchy isolates profitability at each layer. Gross margin shows unit‑level economics; EBITDA shows overall viability. Comparing % to net revenue across layers reveals where value is destroyed.

Benefits of the subtotal formula

Using SUBTOTAL(9, range) instead of SUM allows filtering without breaking totals – filtered rows are excluded automatically.
Enables cleaner aggregation when building the P&L in a spreadsheet.

Formatting conventions

Bold key P&L lines: Gross Revenue, Net Revenue, Direct Costs, Gross Margin, Contribution Margins, EBITDA.
Underline or box critical numbers for visual clarity.

Key takeaways – Building a P&L

Every P&L must be built from actual ledgers using a mapping step.
Structure layers: revenue → direct costs → indirect costs → project costs → central costs → EBITDA.
Use SUBTOTAL for filter‑friendly sums.
Always verify the meaning of each ledger with finance/ops teams (ledger names vary by company).

Interpreting the Numbers

Analysis converts numbers into business insights. All percentages are computed against Net Revenue (GST excluded) because tax is not controllable.

Revenue composition

Fixed units (catalog wooden furniture): 54% – strongest stream, reliable and repeatable.
Civil work: 21% – high margin but operationally complex (low standardisation).
Woodworking: 18.59% – strong pipeline (bookings 5%).
Miscellaneous: 0.16% – negligible (scrap sales, one‑offs).

Interpretation: The business is anchored by manufactured products; civil projects add margin but risk. Booking percentages (civil 1%, wood 5%) indicate future revenue visibility.

Cost analysis (% of Net Revenue)

Cost head	%	Insight
Direct Wooden Costs	29%	Largest cost, slightly high – negotiate vendors, standardise
Factory Costs	7.5%	Operational, room for optimisation
Installation	4.6%	Reasonable but reducible (fixed installation)
Other Direct (rework/wastage)	2.5%	Monitor for leakage
Civil Costs	5%	Very low relative to 21% revenue → high margin
Direct Marketing (total)	10.13%	Heavy online (7.91%) – dependency on paid ads
Offline Marketing	1%	Underutilised channel
Call Center	2.01%	Combined with offline could be boosted

Margin progression

Gross Margin: ~50% – healthy for a physical goods business.
CM1 (after marketing): ~40% (implied) – reasonable.
CM2 (after project costs): ~27‑30% – still healthy.
EBITDA: ~4.6‑5% – positive but thin.

Why is EBITDA so thin?

Design cost: 6% – surprisingly high given that 54% of revenue is from standardised fixed units. Could be reduced by 3% through efficiency or software.
Central team salaries: 11% – high for a fixed‑cost‑heavy business.
Office expenses: ~5.5% (combined) – premium location, potentially trim.

Sensitivity to revenue drop

flowchart LR
  A[Revenue drops to 9 Cr] --> B[EBITDA becomes -30% loss]
  A2[Revenue stays 12 Cr] --> B2[EBITDA +4.6%]
  A3[Revenue rises to 15 Cr] --> B3[EBITDA significantly higher]

At 12 Cr net revenue, the business barely breaks even. If revenue falls to 9 Cr, fixed costs cause a 30% loss. The break‑even is around 11‑11.5 Cr. This fragility is the key strategic risk.

Actionable recommendations

Boost demand for high‑margin product lines (fixed units) via festivals, campaigns, bundles – increase bookings (currently 1‑5%).
Reduce material costs – negotiate fixed‑price vendor contracts, standardise SKUs, bulk procure.
Optimise marketing – reduce online dependency, increase offline/referral (low cost), measure CAC by channel.
Improve design efficiency – fewer designers needed for standard products; automate or repurpose.
Cut central overhead – renegotiate office lease, reduce consulting/tech if not essential.

Exam tip: When analysing a P&L, always ask: “What would happen if revenue fell 20%?” The answer reveals the company’s operating leverage – high fixed costs amplify losses. This is the most testable insight from the analysis.

Key takeaways – Interpreting the P&L

Revenue mix reveals which product lines are dependable vs. risky.
Cost percentages should be benchmarked – a 29% material cost is high; 5% civil cost is excellent.
Gross margin ~50% is okay, but EBITDA <5% is fragile.
A small revenue decline turns EBITDA negative; the business needs a revenue buffer or cost reduction.
Every number tells a story: connect data to strategy to decide where to double down or cut.

MIS: Building a 360-Degree Business Dashboard

A Management Information System (MIS) is a centralized report that gives stakeholders (CFOs, founders, investors, operations heads) a 360° view of the business. A P&L is only one lens. A strong MIS combines multiple functional area metrics so leadership can spot trends, course-correct quickly, and capitalise on opportunities. Each metric tracked — from marketing cost per lead to project timelines to team efficiency — has a ripple effect; together they form a control panel.

Components of a Full MIS

Area	Key metrics	Data source / who to ask
Marketing	Leads by channel (Google, Facebook, referral, offline), conversion per channel, cost per lead, cost per conversion, average order value (AOV), campaign ROAS	Marketing team; tools like Facebook Ads Manager, Google Ads, HubSpot, Zoho CRM, internal Excel trackers
Inventory	Raw material inflow/outflow, stock aging, unsold materials, monthly consumption vs. plan, vendor delivery delays	Purchasing team, factory/warehouse
Project & Delivery	Projects booked vs. delivered, average order-to-completion time, project delays beyond agreed timelines, SLA compliance (civil, fixed, custom furniture)	Project management team, site execution head
Customer Success	NPS scores, cycle time, bottleneck analysis	Customer success team
Conversion Funnel	Lead count → site visits → designs sent → bookings → handovers, stage-wise drop-offs, conversion time per lead	Sales team, CRM team, designer heads
Sales Forecasting	Lead pipeline by city/product/channel, quotation status, probability per deal, month-on‑month forecast vs. actual	Sales managers, CRM (Zoho, Salesforce etc.)
Cash Flow & Balance Sheet	Cash in hand, vendor payment delays, receivables, asset purchases, loan repayments	Finance/accounts, CFO
HR & Productivity	Department-wise headcount, cost vs. output, attrition rate, absenteeism, per-person ROI	HR head, operations coordinator

Exam tip: An MIS is not just a collection of reports — it is an operating system that ties every department’s metrics together. The power comes when you correlate metrics with actual cost data from the P&L.

Lead Analytics: A Worked Example

To make MIS actionable, we must connect metrics to real costs. Below is a step‑by‑step analysis of lead data extracted from a marketing team’s dump.

Raw Data Structure

The marketing team provided a table with fields:

Lead ID (unique)
Source (Online – Bing, FB, Google; Offline; Referral)
Convert (1 = signed up, 0 = not converted)
Type (Fixed, Custom, Civil, or combinations)
Signup Order Value (₹ at time of booking)

Missing data identified: signup date, conversion date, demographic data (city, age). These would enable deeper analysis (e.g., average time to convert, regional performance, customer segment targeting).

Pivot Analysis – Conversion Rates by Source

Using a pivot table, we compute conversion rate (converted leads / total leads per source) and lead share (percentage of total leads from each source).

Source	Total Leads	% of Total Leads	Conversions	Conversion Rate	% of Total Conversions
Offline	~4,800*	~21%	~456	9.5%	~4%
Bing	~2,000*	~9%	~400	20%	~3%
Referral	~1,000*	~4%	~500	50%	~4%
Google AdWords	~5,500*	~24%	~1,100	20%	~9%
Facebook	~11,700*	~51%	~800	7%	~7%
Total	23,000	100%	3,000	13%	100%

*Values approximated from transcript context. Facebook generates 51% of leads but only 7% convert; Google and Referral have high conversion rates.

Average Order Value (AOV) by Source

Compute AOV as total signup order value ÷ number of conversions.

Source	Total Signup Value (₹)	Conversions	AOV (₹)
Offline	(high)	~456	~60,000
Bing	(moderate)	~400	~30,000
Referral	(highest)	~500	~60,000
Google AdWords	(moderate)	~1,100	~30,000
Facebook	(low)	~800	~30,000

Referral and Offline show high AOV; Facebook/Bing/Google moderate.

Cost Allocation to Marketing Channels

We now pull actual cost data from the P&L and allocate shared conversion costs (e.g., call centre cost ₹10 lakh) proportionally to leads received.

Cost items from P&L:

Bing: ₹54,000
Facebook: (approx. ₹40–45 lakh, specific value not given)
Google AdWords: ₹40–45 lakh
Referral: value from P&L (assume given)
Offline: sum of branding + direct offline + allocated portion of ₹10 lakh conversion cost

Allocation method:
For each source, total cost = (source direct cost) + (lead share × ₹10 lakh).

Example for Bing:
Lead share = Bing leads / total leads ≈ 9% → allocated conversion cost = 9% × ₹10 lakh = ₹90,000.
Total Bing cost = ₹54,000 + ₹90,000 = ₹144,000.

Repeat for each source.

Metrics Summary

Source	Leads	Conversions	Conversion Rate	AOV (₹)	Total Cost (₹)	Cost per Conversion (₹)
Offline	4,800	456	9.5%	60,000	(branding + share)	high
Bing	2,000	400	20%	30,000	144,000	360
Referral	1,000	500	50%	60,000	(cost from P&L + share)	~200
Google	5,500	1,100	20%	30,000	(40L + share)	~370
Facebook	11,700	800	7%	30,000	(40L + share)	~500

Exact figures for offline, referral, Google, Facebook direct costs are not given in the transcript; the example demonstrates the method. Facebook has the highest cost per conversion and lowest conversion rate.

Key Insights from the Analysis

Facebook generates the most leads (51%) but the worst conversion rate (7%) and relatively low AOV. It burns money relative to results.
Referral and Offline yield high AOV (₹60,000) but low lead volume.
Google AdWords and Bing have moderate performance with decent conversion rates (20%).
Cost per conversion is lowest for Referral and highest for Facebook, suggesting reallocation of budget may improve ROI.

Exam tip: Always bring cost data into conversion analysis. A high conversion rate means little if the channel’s cost per conversion is unsustainable. This is the “ripple effect” – marketing metrics meet P&L numbers.

Key Takeaways

An MIS extends beyond a P&L to cover marketing, inventory, operations, customer success, sales forecasting, cash flow, and HR.
Each department must provide raw data (Excel dumps, CRM exports) for central consolidation.
Lead analytics should combine conversion rates, AOV, and cost per conversion (including allocated shared costs like call centres).
The example shows that Facebook’s high lead volume masks poor conversion and high cost; data drives budget reallocation decisions.
Missing data (dates, demographics) limits analysis – always push teams to track more granular fields.

P&L Analysis for Marketing Channel Performance

A P&L analysis combined with marketing channel data transforms vague spend into sharp decision points. By cross-referencing lead data, conversion metrics, and actual booked P&L costs, managers can identify which channels win, which leak money, and what levers to pull next month.

Key Metrics for Channel Evaluation

Each channel is assessed using the following metrics, derived from total cost, leads, conversions, and average order value (AOV):

Cost per lead (CPL) = total channel cost / total leads
Cost per conversion (CPC) = total channel cost / number of conversions
Conversion rate = conversions / leads × 100%
Average order value (AOV) = total revenue from channel / conversions
Cost as % of AOV = (CPC / AOV) × 100% — measures profitability of acquisition

Exam tip: Cost as % of AOV is the most telling metric for channel viability. A channel becomes borderline unviable when this percentage exceeds ~10–12%, unless volumes are strategic.

Channel-by-Channel Analysis

The following data comes from a real P&L across digital channels (Google, Facebook, Referral, Offline, Bing). All costs in ₹.

Google Ads

Metric	Value
Conversion rate	21.55% (highest among digital)
Cost per lead	₹857 (highest)
Cost per conversion	~₹3,700
AOV	~₹31 (lowest)
Cost as % of AOV	~12%
Share of total conversions	39% (40% of all)

Insights:
Google users are high-intent — they search actively, leading to high conversion rates. Despite high CPL, cost per conversion is lower than Facebook’s. However, AOV is the lowest, possibly because Google leads research aggressively for best price.
Action: Retain Google but optimize for higher AOV (e.g., retargeting, premium keywords).

Facebook Ads

Metric	Value
Conversion rate	~7% (low)
Cost per lead	Very low (high volume: ≈11,000 leads)
Cost per conversion	Higher than Google
AOV	Slightly higher than Google
Cost as % of AOV	>12% (higher than Google)
Share of total conversions	32%

Insights:
Facebook users are low-intent — they sign up while scrolling but convert poorly. High lead volume masks high conversion cost. The cost as % of AOV is borderline unviable unless conversion improves.
Action: Double conversion rate by refining targeting (age, region, purchase behavior, lookalikes), changing creatives, and narrowing to high-intent audiences.

Referral

Metric	Value
Conversion rate	50% (highest of all)
Cost per lead	~₹300 (very low)
Cost per conversion	Lowest (≈1% of AOV)
AOV	Highest among all channels
Cost as % of AOV	~1%
Share of conversions	11% (from only 3% of total leads)

Insights:
Referral leads are gold — word-of-mouth trust drives ultra-high conversion and AOV. Yet only ~300 referrals from 3,000 customers.
Action: Aggressively scale referral schemes: incentives, loyalty programs, ambassador clubs, community building. Doubling referrals could cut total marketing cost by half and boost EBITDA from 4% to 8%.

Offline

Metric	Value
Conversion rate	~10% (good; better than Facebook but below Google)
Cost per lead	~₹300
Cost per conversion	~₹3,700 (similar to Google)
AOV	High (second to referral)
Cost as % of AOV	Reasonable (due to high AOV)

Insights:
Offline pulls in higher-ticket customers but volume is moderate. Risk of fake leads from third-party sources (e.g., architects giving leads to multiple firms).
Action: Focus on high-value zones/premium segments; test incentive-led purchases (pay only if converted); improve conversion rate to 20% (Google level).

Bing

Metric	Value
Conversion rate	Very low volume, data noisy
Cost per lead	Low (low competition)
Cost per conversion	Low
AOV	Average
Share of conversions	~1%

Insights:
Bing is an oddity — efficient but cannot scale. Minimal impact on total business.
Action: Explore if targeting can be expanded; if not, deprioritize.

Cross-Channel Decisions

flowchart TD
    A[Analyze channel metrics] --> B{Share of conversions > 20%?}
    B -->|Yes| C{Is cost as % of AOV > 12%?}
    C -->|Yes| D[Fix channel: improve conversion rate or targeting]
    C -->|No| E[Scale channel: increase budget while maintaining efficiency]
    B -->|No| F{Is conversion rate > 15% and AOV high?}
    F -->|Yes| G[Double down: referral-like channels]
    F -->|No| H[Optimize or deprioritize: test before scaling]

Key strategic actions derived from the analysis:

Scale referral aggressively — it is the most ROI-positive channel.
Retain Google but optimize for higher AOV.
Fix Facebook — poor conversion makes it too expensive; improve targeting and creatives.
Focus offline on high-ticket segments; improve lead quality.
Treat Bing as a low-volume experiment; don't expect significant scale.

Exam tip: The narrative of an MIS is not a data dump — it cross-references data from different sources to tell where the business is winning and where it is leaking money. A P&L with channel-level metrics is the first step toward building a decision-grade MIS.

Key Takeaways

Cost per conversion and cost as % of AOV are the most critical profitability metrics for each channel.
Referral leads have the highest conversion rate, highest AOV, and lowest acquisition cost — scale them.
Google delivers high-intent traffic but low AOV; optimize for value, not just volume.
Facebook is expensive due to low conversion; fix targeting before scaling.
Offline works for high-ticket customers; verify lead authenticity and improve conversion.
Bing is efficient but cannot scale; deprioritize if expansion fails.
A well-structured MIS turns raw data into targeted decision levers for the next quarter.

Module 4 Business Applications – Summary Notes

This module builds the analytical backbone of a business: the Profit & Loss (P&L) statement and the Management Information System (MIS). The goal is to transform raw accounting data into actionable intelligence for operations, marketing, finance, and strategy.

1. Building the P&L from a Trial Balance

The process starts with the trial balance from the accounting team — a list of every ledger account with its debit/credit balance. The task is to structure that flat list into a layered P&L that reveals true profitability.

Step 1 – Map ledgers line by line to business‑meaningful categories (e.g., revenue, cost of goods sold, operating expenses). Each ledger must be assigned to the correct P&L section.
Step 2 – Create layers (gross profit, EBITDA, net profit) by grouping mapped ledgers and inserting intermediate subtotals.
Step 3 – Use formulas (e.g., SUMIF, VLOOKUP, or structured references) to automate the aggregation so the P&L updates automatically when the trial balance is refreshed.

Exam tip: The mapping step is the most error‑prone. A single ledger misclassified (e.g., marketing cost under “admin expenses”) distorts gross margin and operating profit. Always cross‑check ledger descriptions with the business context.

2. Analyzing the P&L

Beyond building the P&L, the module teaches how to read it to identify gaps, inefficiencies, and opportunities.

Analyze marketing spends – Drill into specific expense lines to see how costs relate to revenue.
Cross‑track with MIS – The P&L is one slice; the MIS integrates data from other teams (e.g., sales, customer support) to explain why the numbers move.

3. Marketing Efficiency Metrics (Funnel Metrics)

By pulling data from other teams, you calculate strategic funnel metrics that link spending to outcomes:

Metric	Formula	What it reveals
Cost per Lead (CPL)	$\frac{\text{Total marketing spend}}{\text{Number of leads}}$	Efficiency of top‑of‑funnel acquisition
Cost per Convert (CPC)	$\frac{\text{Total marketing spend}}{\text{Number of conversions}}$	Efficiency of converting leads to customers

These numbers feed directly into decisions: where to increase spend, which channels to cut, and how to improve unit economics.

4. Putting It All Together – The MIS

The MIS (Management Information System) is a cross‑functional dashboard that combines the P&L with operational and marketing data. It allows you to:

Track business efficiency in real time.
Ask the right questions (e.g., “Why did cost per lead rise while conversion rate dropped?”).
Uncover hidden gaps between marketing spend and revenue growth.

Unlike a simple Excel sheet, the MIS built in this module is designed to be dynamic, automated (via formulas and possibly App Scripts), and reusable across reporting periods.

Key takeaways

Start with the trial balance → map ledgers → layer the P&L → automate with formulas.
The true value is in cross‑tracking: P&L + operational data = MIS.
CPL and CPC are core funnel metrics computed from marketing spend and conversion data.
Analytical thinking means moving from “what is the number?” to “what does the number mean for the business?”

Clustering and Cohort Analysis

Introduction to Module 3

Module 3 shifts from descriptive analytics (pivot tables, dashboards, logical segmentation) to strategic analytics — understanding why something happened, who it happened to, and what is likely to happen next. This is where data meets marketing strategy.

Four core techniques are introduced:

Technique	Purpose	Key Question It Answers
Cohort analysis	Track customer retention over time	Are we retaining customers? Where do they drop off?
Customer lifetime value (CLTV)	Estimate long-term revenue potential per customer	Is it worth spending X to acquire a user?
RFM analysis (Recency, Frequency, Monetary)	Classify customers by buying behaviour	Who are our loyal customers? Who is at risk?
K-Means clustering	Build unbiased, behavioural-based customer segments	What groups emerge naturally from similarity patterns?

Module Flow

flowchart LR
    A[Cohort Analysis<br/>Visualise retention & churn] --> B[Customer Lifetime Value<br/>Calculate & interpret CLTV]
    B --> C[RFM Segmentation<br/>Rank customers by rules]
    C --> D[K-Means Clustering<br/>Machine finds groups]
    D --> E[Apply to real customer data]

Each technique builds on the previous: cohort analysis provides retention baselines → CLTV puts a dollar value on that retention → RFM adds a behavioural ranking → K-Means automates segmentation without guesswork.

Exam tip: The progression from rule-based segmentation (RFM) to statistical clustering (K-Means) is a classic conceptual bridge. Understand that RFM requires manual thresholds, while K-Means uses distance metrics to form groups objectively.

Key takeaways

Module 3 moves from what happened to why, who, and what next.
Four techniques: cohort analysis, CLTV, RFM, K-Means clustering.
Cohort analysis tracks retention over time; CLTV values a customer lifetime.
RFM ranks by recency, frequency, monetary value; K-Means finds groups mathematically.
All methods are applied to real customer data — theory is immediately operational.

What is Cohort Analysis?

A cohort is a group of users who share a common characteristic, most often the month they signed up or made their first purchase. Cohort analysis tracks how each cohort behaves over time, answering questions like:

How many users remain after their first month?
Are customers from January more loyal than those from March?
Is retention improving over time?

It is widely used in:

Domain	Application
E-commerce	Measuring repeat purchases
SaaS	Tracking subscriber retention
Mobile apps	Analyzing user drop-off
EdTech	Monitoring batch engagement

The core idea: for each cohort, count how many users return in each subsequent period and build a table showing retention across time.

The Data: Understanding the Invoice Dataset

A real transactional dataset (≈400,000 rows) with columns:

Column	Description
Invoice	Transaction/order number
StockCode	Product code
Description	Item description
Quantity	Units purchased
Price	Price per unit
Customer ID	Unique customer
Country	Customer’s country
InvoiceDate	Date of transaction

Important structure: An invoice can contain multiple line items (same Invoice number, same Customer ID, multiple rows). Raw data is at the invoice line-item level, not summarized per order.

Data cleaning essentials (always verify):

✅ All quantities are positive – delete rows with negative quantity (returns/cancellations).
✅ All prices are positive – remove zero or negative rows.
✅ Remove test product codes or customer IDs (e.g., "test").
✅ Ensure InvoiceDate is in correct date format (Excel may auto-group).

Exam tip: In any real dataset, cleaning is non-negotiable. Cohort results are only as reliable as the underlying data.

Building the Base Table: From Line Items to Customer Orders

Step 1 – Aggregating Line Items into Invoices

Create a pivot table from the clean data:

Rows: Customer ID, then Invoice (and optionally InvoiceDate, Country)
Values: Sum of Price (or Count of line items to get order size)

This collapses multiple line items into one row per invoice, giving a customer-level purchase history.

Step 2 – Extracting Signup Date (First Transaction per Customer)

Sort the pivot table by Customer ID and then InvoiceDate (oldest first). The first row for each customer is their signup date (cohort anchor). Use VLOOKUP (exact match) to copy this date into a new column for every row of that customer. Because we sorted ascending, VLOOKUP returns the first – i.e., earliest – date.

Step 3 – Encoding Month Numbers

Since the data spans two years (Jan 2009 – Dec 2010), create a continuous month number for each date:

Data Insights and Dashboard Creation

Introduction to Module 2

This module transitions from storing and structuring data (Module 1) to understanding, visualizing, and telling a story with data. The goal: turn raw data into business insights through interactive dashboards.

What you will learn

Topic	Purpose
Pivot tables & pivot charts	Foundational building blocks for interactive dashboards
Sales dashboard	Built using real data from a vegetable delivery app
Cluster‑wise sales analysis	Identify top‑performing regions
Customer behavior & demand patterns	Understand what drives purchases
Return volumes	Spot products with high return rates
Interactive filters & slicers	Let managers drill down dynamically
Professional dashboard	Present clearly to management or clients

The core philosophy: Dashboards for decisions

A dashboard is not a collection of charts — it is a tool that helps a manager answer specific business questions. Every element on the dashboard should serve a decision.

Dashboards are not about creating charts. They're about decisions.

Decision questions a good dashboard answers

A well‑designed dashboard enables quick answers to questions like:

Business question	Dashboard insight it surfaces
Is sales going up?	Sales trend over time
Which region is underperforming?	Cluster‑wise sales comparison
Which product has the highest returns?	Return volume by product
Which customer should we focus on?	Customer behavior / demand patterns

How the module unfolds

Pivot tables & pivot charts – understand the mechanics.
Build a sales dashboard using a vegetable‑delivery app dataset.
Analyze cluster‑wise sales – uncover top and weak regions.
Examine customer behavior – demand patterns and return volumes.
Compile everything into a clean, professional dashboard.

Exam tip: Memorise the four sample business questions above — they are typical of what a dashboard should answer. Expect a question that asks you to match a dashboard element to a decision.

Key takeaways

This module moves from data storage (Module 1) to data insight via visualisation.
Pivot tables and pivot charts are the core tools for interactive dashboards.
Dashboards are decision‑centric, not chart‑centric.
Real example used: a vegetable delivery app (sales, cluster, returns, customer behaviour).
By the end, you can build a professional dashboard in Excel or Google Sheets with filters and slicers.

Pivot Tables - I

A pivot table is a spreadsheet feature that automatically reorganises and summarises selected columns and rows from a raw data table into a compact, flexible report. It does not change the original data — it creates a new summary view. Technically, a pivot table is a form of cross tabulation; it gets its name because you can pivot (rotate) the summary around different variables. With drag-and-drop fields, you can instantly group thousands of rows by product, city, date, customer, etc., and switch between monthly totals, category breakdowns, or top-selling SKUs.

Main Components

A pivot table is built from four core areas:

Component	Function	Example from transcript
Rows	Variables placed here become row labels (one row per unique value)	Product (Potato, Tomato)
Columns	Variables placed here become column headers (one column per unique value)	City (Bangalore, Chennai, Mumbai)
Values	The numeric field(s) to be aggregated (sum, count, average, max, etc.)	Quantity – summed by default
Filters	Restrict the entire pivot table to a subset of data (e.g., date range, city)	Optional – not used in the example

Definition (from Wiki): “A pivot table is a table of values which are aggregations of groups of individual values from a more extensible table.” — it takes one detailed table and produces a summary view.

Pivot Charts

Once a pivot table is ready, you can build a chart on that pivot data – a pivot chart. This is a dynamic visual representation of the pivot table. In short: pivot tables summarise; pivot charts visualise.

Worked Example: Vegetable Sales Data

A small dataset of vegetable sales across three cities contains: Date, City, Product (Potato/Tomato), Quantity, Sale Price, and Total Value.

Steps to create a pivot table (Excel):

Select the dataset → Insert → Pivot Table.
Choose location (existing sheet or new worksheet).
The PivotTable Fields pane appears with all column headers.
Drag Product into Rows, City into Columns, and Quantity into Values.

Result (first orientation – product rows, city columns):

Product	Bangalore	Chennai	Mumbai	Grand Total
Potato	25	35	50	100
Tomato	(implicit, not shown)

By swapping rows and columns (or adding filters), you can instantly re-orient the summary — e.g., city as rows, product as columns — to view total sales per city.

Modifying calculations: By default, pivot tables sum numeric values. You can change the aggregation by opening Value Field Settings and choosing Count, Average, Max, Min, etc.

Exam tip: The Value Field Settings menu is where you switch between aggregation functions (Sum, Average, Count, Max, Min). Forgetting this is the most common reason a pivot table gives unexpected numbers.

Key Takeaways

A pivot table summarises large datasets without altering the raw data — it creates a new aggregated view.
The four main components: Rows, Columns, Values, Filters.
Drag fields into these areas to instantly group and summarise.
Default aggregation for numeric fields is Sum; use Value Field Settings to change it.
A pivot chart is a dynamic chart linked to a pivot table – summary + visualisation.
Pivoting (swapping rows and columns) lets you quickly explore different perspectives of the same data.

Understanding the Data Before Pivoting

Real analysis starts with understanding what the data represents, its granularity, and how sheets relate.

Two sheets in the workbook:

SKU Sales Trends – daily SKU-level summary data (dates Jan–Mar 2018, ~700 rows). Columns: Date, SKU Name, SKU Total Orders, Vegetable Orders, Fruit Orders, Sale Price, Billed Tonnage, Return Tonnage.
Customer Level SKU – transaction-level data (Feb only, different SKUs, includes Customer ID, Locality, Quantity, etc.). Not directly related to the first sheet.

SKU = Stock Keeping Unit – the smallest inventory unit (e.g., tomatoes: Standard vs. Hybrid are two SKUs).

Key observation: The SKU Sales Trends table is already aggregated at a daily level. Values like “Total Orders” are attributes of the date, not the SKU. These values repeat across all SKUs for the same date. Therefore, when building a pivot, Sum would double-count – use Max, Min, or Average instead.

Decomposing Order Types Using Set Theory

From the daily summary:

$|U| = \text{Total Orders}$ (all orders)
$|V| = \text{Vegetable Orders}$ (orders with ≥1 vegetable)
$|F| = \text{Fruit Orders}$ (orders with ≥1 fruit)

These sets overlap (an order can contain both). To find exclusive categories:

\begin{aligned} |V \cap F| &= |V| + |F| - |U| \\ \text{Only Veg} &= |V| - |V \cap F| \\ \text{Only Fruit} &= |F| - |V \cap F| \end{aligned}

Example (one day): Total = 1324, Veg = 1289, Fruit = 702
$|V \cap F| = 1289 + 702 - 1324 = 667$
Only Veg = $1289 - 667 = 622$
Only Fruit = $702 - 667 = 35$

These derived columns are computed directly in Excel (or any analysis tool) and added to the source table.

Building the Pivot Table & Chart

Steps:

Select the data table (including new columns).
Insert Pivot Table, choose new worksheet, optionally check “Add this data to Data Model”.
Set Rows = Date (un-group from months to show days).
Add Values for: Total Orders, Only Veg, Only Fruit, Both. Change each to Max (or Min – identical per date).
Insert Pivot Chart (Line chart) from the pivot.
Add trend lines (linear) to key series.

Insight from the chart:

All categories grow over time.
The “Both Veg & Fruit” order count has a steeper slope than “Only Veg” or “Only Fruit”.
This suggests customers are increasingly placing orders that contain both food types — a cross-selling opportunity.

Exam tip: When source data is pre-aggregated (e.g., daily totals repeated per SKU), using Sum in a pivot table creates inflated values. Always check aggregation level and use Max/Min/Count if duplication exists.

Key Takeaways

Understand data granularity: SKU Sales Trends is daily-summary, not transactional; the second sheet is different and unrelated.
SKU = smallest inventory unit; classification (essentials, fruits, etc.) can be used in analysis.
Use set theory ( $|A \cup B| = |A| + |B| - |A \cap B|$ ) to decompose overlapping categories into exclusive counts.
Pivot tables on pre-aggregated data require Max (or Min, Avg) instead of Sum.
Pivot charts with trend lines reveal growth rates; steeper slope = faster growth segment.
The insight “Both Veg & Fruit orders are the main growth driver” informs cross-selling strategy.

Pivot Tables – Aggregating Percentages and Week-Level Summaries

Percentages standardize quantities so you can compare across time periods or categories without being misled by raw numbers. For example, raw daily order quantities fluctuate, but converting them to percentages of total orders reveals relative trends.

Adding Percentage Columns in a Pivot Table

From the raw order data (SKU-level), create calculated columns:

% Both Veggies & Fruits = (Both Veggie & Fruit Orders) / Total Orders
% Only Veggies = (Only Veggie Orders) / Total Orders
% Only Fruits = (Only Fruit Orders) / Total Orders

These three sum to 100% and make trends visible. In the example, over time:

% Both rose from 38% → 50%
% Only Veggies dropped from 60% → 47%
% Only Fruits grew from 0% → 3%

This is far clearer than raw counts.

The Granularity Trap: Why You Cannot Reuse Day-Level Percentages for Week-Level Summaries

Directly summing or averaging daily percentages across a week produces a meaningless metric. Percentages are computed relative to the denominator of that row. At a different granularity (e.g., week), the base changes (total orders across the week vs. per day).

❌ Wrong approach	✅ Correct approach
Create percentage columns at day level, then in a new pivot table sum/average those percentages per week	First aggregate raw numbers (total orders, veg orders, fruit orders) per week, then compute percentages from the weekly totals
Result: double-counting or mis-weighted proportions	Result: true weekly percentages

Correct Workflow for Week-Level Percentages

Base data – raw daily order table (with date)
Add a WeekNumber column (extracted from date using WEEKNUM())
First pivot table – aggregate raw counts by week:
- Rows: WeekNumber
- Values (all using Max because values are identical across rows within the same week):
  - Total Orders
  - Only Veggie Orders
  - Only Fruit Orders
  - Both Veggie & Fruit Orders
- Why Max? Each day's row already holds the daily total; we want the daily value itself, not a sum (which would sum across all days in the week incorrectly). Since it's the same value for each product within that day, Max (or Min or Average) returns the correct daily number. Then the pivot table's implicit sum across days gives the weekly total.
Second pivot table (on top of the first pivot table’s data) – compute percentages:
- Rows: WeekNumber
- Values:
  - Total Orders (as raw number)
  - Only Veggies (sum across weeks, but we will compute % later)
  - Only Fruits
  - Both Veggies & Fruits
- Add calculated columns:
  - % Both = Both / Total
  - % Only Veggies = Only Veggies / Total
  - % Only Fruits = Only Fruits / Total
Chart – insert a pivot chart from this table to visualize week-on-week percentage trends.

Exam tip: When shifting granularity (e.g., daily → weekly), never reuse percentage fields from the finer level. Always re-aggregate the raw counts at the new level and then compute percentages. The same principle applies for any ratio metric (averages, rates, etc.).

Why Max (or Min) Works for This Aggregation

In the first pivot table (daily data, one row per product per day), each day’s total order count is the same for every product row within that day. Using Max picks that single value (or Sum would multiply it by the number of products, giving a huge inflated number). The pivot table’s row label (WeekNumber) then groups all rows in that week, and the pivot table’s implicit sum of the daily Max values gives the correct weekly total.

If you instead used Sum on the raw daily number, you would sum across all products every day, double- and triple-counting the same daily total. Always check your aggregation function matches the data’s grain.

Key takeaways

Percentages standardize data for trend analysis across time.
Granularity mismatch – do not reuse percentage fields at a coarser level; re-aggregate raw numbers first.
Correct hierarchy: raw data → pivot with raw counts at desired grain → new pivot with percentages.
Use Max (or Min, Average) when a value is identical across rows within a day to avoid double-counting.
Generate GetPivotData should be turned off (PivotTable Analyze → Options → uncheck “Generate GetPivotData”) when referencing cells directly in the second pivot’s calculated columns.

Pivot Tables IV: Measures and Slicers

When you add a calculated field (a column with a formula) outside a pivot table – for example, a percentage column derived from the pivot’s values – you create a static copy. If you later filter the pivot, the external formula can break (e.g., division by zero when the denominator becomes empty). The better approach: embed calculations directly inside the pivot using measures.

Drawback of External Calculations

External formulas reference specific cells; when the pivot reshapes (filtering, adding fields), those references may produce errors (e.g., #DIV/0!).
To avoid errors, you could copy the pivot as values (static), but then you lose the ability to dynamically filter or update.

Solution: Measures in Pivot Tables

A measure is a DAX (Data Analysis Expressions) formula stored inside the pivot. It recalculates automatically as the pivot changes – no errors, no stale links.

How to Add a Measure

Right-click anywhere within the pivot table.
Select Add Measure (or Add a Measure).
Give the measure a name (e.g., % Veggie & Fruit).
Write the formula using field references:
= SUM( 'Max'[Veggie] ) + SUM( 'Max'[Fruit] ) divided by SUM( 'Max'[Total Orders] ) (or whatever aggregate is needed).
Click OK – the measure appears in the Values area.

Example: Percentage Measures

From the transcript, three measures were created:

Measure Name	Formula	Purpose
`% Veggie & Fruit`	`(SUM of Max[Veggie] + SUM of Max[Fruit]) / SUM of Max[Total Orders]`	Combined percentage of veggie + fruit orders
`% Veggies Only`	`SUM of Max[Veggie] / SUM of Max[Total Orders]`	Percentage of veggie orders
`% Fruits Only`	`SUM of Max[Fruit] / SUM of Max[Total Orders]`	Percentage of fruit orders

These measures can then be added to the Values area alongside or instead of raw counts. Filtering the pivot now works seamlessly – no divide‑by‑zero errors because the formula respects the current filter context.

Building Charts with Measures

After adding measures, create a PivotChart from the same pivot table (Insert > PivotChart, choose a line chart).
Remove unwanted series (e.g., raw sums) and keep only the measure fields.
For week-by-week analysis, place Week Number in the Rows area, not in Values.

The result: a clean line chart showing, for example, the weekly percentage distribution of veggie and fruit orders.

Exam tip: When you need to filter a pivot but also show normalized values (percentages), always use measures rather than calculated columns outside the pivot. Measures respect the filter context and prevent #DIV/0! errors.

Using Slicers for Interactive Filtering

A slicer provides a visual filter (buttons) that connects to one or more pivot tables/charts.

Insert a slicer from the PivotTable Analyze tab > Insert Slicer.
Choose the field to filter on (e.g., Week Number).
The slicer can be placed on a dashboard sheet.

Limitation: One Slicer per Pivot Table

A slicer can only filter pivot tables that share the same source pivot table (or the same underlying data model). If two charts come from different pivot tables (e.g., one uses Week Number, another uses Date), a single slicer cannot control both simultaneously.

In the transcript, two charts were created: one from a pivot that uses Week Number (measures), another from a pivot that uses Date (raw data). Trying to link the same slicer to both failed because they were not based on the same pivot.
To make a slicer work across multiple charts, ensure they are all derived from the same pivot table.

Practical Example: Adding Revenue Analysis

Using the same sales dataset, you can create another pivot:

Insert a new pivot table (existing worksheet).
Place Week Number in Rows, SKU Classification in Columns, and Sum of Revenue in Values.
Insert a line PivotChart to view weekly revenue trends per product category.
Optionally, add other measures like Average Sale Price or Bulk Tonnage (be careful to use the correct aggregation – e.g., AVERAGE for price).

Note: When adding multiple measures from different fields, ensure you are using the correct source pivot table to avoid mixing aggregations inconsistently. In the transcript, one attempt to add Average Price accidentally referenced a different pivot; always double‑check the source.

Key Takeaways

External calculations on pivot values break when the pivot is filtered – use measures instead.
Measures are DAX formulas stored inside the pivot; they automatically adapt to filters.
To build a chart with percentages, create a measure (e.g., % Veggies = SUM(Veggies)/SUM(Total)) and use it in a PivotChart.
Slicers provide interactive filtering but only work across charts that share the same pivot table.
To avoid filtering issues, design all charts from one pivot table or use a common data model (Power Pivot) for cross‑chart slicers.

Pivot Tables for Dashboard Creation

A dashboard pulls together multiple visualisations that share a common data source and allows interactive filtering through slicers. The key is to build a master pivot table that contains all the necessary fields (dates, categories, metrics) at the lowest granularity, then create derived pivot charts that each show a different metric. A single slicer can then filter all charts simultaneously.

Building a Master Pivot Table

Start with the raw data table. Insert a new pivot table and add rows or columns to define the granularity – the level at which each metric will be shown. A good granularity for time‑series analysis is Date (or Week Number) combined with SKU Classification (or SKU Name).

Field	Role	Example
Date	Row (daily)	2025-01-07
Week Number	Row (derived from date)	Week 1
SKU Classification	Row (category)	Fruits
Sale Price	Value – use Max (single value per day)	45.00
Revenue	Value – use Sum (aggregated per day)	12,500
Built Tonnage	Value – use Sum	2,300
Return Tonnage	Value – use Max (same as sum at lowest level)	76

Why Max for Sale Price? At the lowest level (one date + one SKU) there is only one price per day. Using Max avoids summing duplicate entries that may appear from the raw data. For aggregated totals (e.g., revenue, tonnage) use Sum.

After adding fields, tidy the pivot table layout:

Design tab → Subtotals → Off
Grand Totals → Off
Report Layout → Show in Tabular Form
Repeat All Item Labels

This gives a clean, row‑by‑row table where every date and category appears explicitly.

Creating Linked Charts

From the master pivot table, create three separate pivot charts (line charts are typical for trends), each using the same master table as its data source. The row fields (Date, Week, SKU Classification) stay the same; only the value field changes.

Chart	Value Field	Example Interpretation
Tonnage Trend	Sum of Built Tonnage	Volume moving through time
Price Trend	Max of Sale Price	Unit price evolution
Revenue Trend	Sum of Revenue	Total income – product of price × tonnage

To create a chart: select the master pivot table → Insert → Pivot Chart → Line. In the PivotChart Fields pane, remove the previous value field and add the desired one.

Adding Interactivity with a Slicer

Add one slicer (e.g., on SKU Classification) and connect it to all pivot tables that feed the charts.

Click on any pivot chart or table → Insert → Slicer → choose SKU Classification.
Right‑click the slicer → Report Connections and tick every pivot table (master table plus the three chart‑specific ones).
The slicer now filters all charts simultaneously.

flowchart LR
  A[Master Pivot Table] --> B[Tonnage Chart]
  A --> C[Price Chart]
  A --> D[Revenue Chart]
  E[SKU Classification Slicer] --> B
  E --> C
  E --> D

Arrange the three charts side‑by‑side on a dedicated dashboard sheet. The slicer sits next to them for easy filtering.

Example Dashboard Analysis

After filtering for Fruits, a manager observes:

Sale Price decreased week on week
Tonnage increased steadily then sharply dropped in week 9
Revenue rose initially, then crashed when tonnage fell

Such side‑by‑side comparison reveals cause‑effect relationships (e.g., price cuts may have driven volume until a disruption occurred). The same logic applies to other categories like Onion, Potato, Tomato.

Exam tip: The master pivot table approach avoids duplicating slicers and ensures all charts stay in sync. Remember to set numeric fields to the correct aggregation (Max for unique values, Sum for totals) at the chosen granularity.

Key takeaways

Build one master pivot table with all needed dimensions (date, week, SKU) and metrics (price, revenue, tonnage).
Use Max for fields that have a single value per row (e.g., sale price) and Sum for aggregated totals.
Create separate pivot charts for each metric, all sourced from the same master table.
Add a single slicer and link it to every pivot table in the dashboard.
Side‑by‑side charts enable instant comparison of trends and help identify relationships between price, volume, and revenue.

Dashboard Creation with Pivot Tables and Slicers

A dashboard consolidates multiple visualizations linked by a common filter. Pivot tables feed pivot charts, and slicers enable interactive filtering across all charts derived from the same base table.

Constructing a Master Pivot Table

To manage multiple views efficiently, create one master pivot table containing all necessary fields aggregated at the desired granularity (e.g., week level). From this master, generate separate smaller pivot tables for individual charts.

Steps:

Insert a pivot table from the raw data, adding it to the data model for advanced calculations.
Place fields: row labels (e.g., week number), values (e.g., max of total orders, max of percentage veggies, max of percentage fruits).
Set value field settings to appropriate aggregation – max for daily totals (to avoid double-counting when aggregating by week), average for percentages.
Remove subtotals and grand totals, apply a tabular layout for clarity.

Example fields in master pivot:

Row	Value 1	Value 2	Value 3
Week 1	Max(Total Orders)	Avg(% Veggies)	Avg(% Fruits)
Week 2	...	...	...

Creating Linked Charts from Sub-Pivots

Insert new pivot tables using the master pivot’s range as source. This ensures all sub-pivots share the same underlying data.

Sub-pivot 1: Week number → Max(Total Orders) – line chart for order volume trend.
Sub-pivot 2: Week number → Avg(% Veggies), Avg(% Fruits), Avg(% Both) – line chart for order mix distribution.

Exam tip: When using percentages, aggregate by average per week, not sum. Summing percentages across weeks is meaningless.

Adding Slicers and Linking Across Charts

Insert a slicer on a common field (e.g., week number). To link it to multiple pivot charts:

Right-click the slicer → Report Connections – select all charts that should respond.
All pivots must originate from the same base table (or the same master pivot) for the slicer to filter them simultaneously.

flowchart TD
    A[Raw Data Table] --> B[Master Pivot Table]
    B --> C[Sub-Pivot 1: Orders Trend]
    B --> D[Sub-Pivot 2: Order Mix]
    B --> E[Sub-Pivot 3: Price/Volume/Revenue]
    F[Slicer (Week)] --connects to--> C
    F --connects to--> D
    F --connects to--> E

Analyzing Price–Volume–Revenue Interactions

By combining slicers with pivot tables showing price, tonnage (quantity), and revenue, a manager can spot anomalies. Key observations from the lecture (applied to SKUs like Tomato, Potato, Onion, Fruits, Essentials):

SKU	Price Trend	Tonnage Trend	Revenue Outcome
Tomato	Slight drop (₹10→₹6→₹8)	Massively increased	Increased slightly
Potato	Stable (₹~same)	Increased	Increased
Onion	Dropped sharply (₹45→₹20)	Increased initially, later flat	Increased then decreased
Fruits	Dropped slightly (₹50→₹40)	Increased 4×	Increased ~3×
Essentials	Crashed (₹70→₹30)	Doubled	Languished (flat)

Lesson: Revenue $= \text{Price} \times \text{Quantity}$ . A price drop can offset volume gains, leading to flat or falling revenue. Slicers allow zooming into specific time windows to isolate such dynamics.

Key Takeaways

Build a master pivot at the desired aggregation level (e.g., week) to serve as a single source for multiple charts.
Use average for percentage fields when aggregating over time; use max to avoid summing daily totals.
Slicers filter all connected pivot charts simultaneously only if those pivots share the same base data range.
Price–volume–revenue analysis reveals whether revenue follows tonnage or gets eroded by price declines.
Dashboard interactivity enables managers to drill into specific periods (e.g., 4 weeks) and compare trends across SKUs or categories.

Pivot Tables – VII: Customer & Regional Segmentation

Customer-level data (sales, customer, SKU) with 50,000 rows can reveal powerful insights when summarized with PivotTables and simple formulas. The goal is to segment customers and regions without charts, using only PivotTable summaries and IFS logic.

Extracting Customer Metrics

Create a PivotTable with Customer ID in rows and the following measures (all as summed values unless noted):

Measure	PivotTable Field	Aggregation	Purpose
Total Build Value	Build Value	Sum	Revenue per customer
Return Quantity	Return QTY	Sum	Absolute returns
Build Quantity	Build QTY	Sum	Volume ordered
Unique SKUs	SKU ID	Distinct Count	Product variety per customer
Order Frequency	Date (Delivery Date)	Distinct Count	How many times they ordered in the month

These raw measures are then used to compute derived metrics:

Return % = Sum (Return QTY) / Sum (Build QTY)
Average Order Value (Ticket Size) = Sum (Build Value) / Count of Delivery Date

Exam tip: Adding a measure directly inside the PivotTable (e.g., using Value Field Settings > Show Values As or a calculated field) keeps the analysis dynamic. The transcript uses a separate column with =IFS(...) formulas referencing the PivotTable output.

Customer Segmentation with Formulas

Once the PivotTable is built, segment customers by adding helper columns with IFS (or nested IF) formulas.

Value Category (based on Total Build Value)

Condition	Category
> 200,000	Category 1
> 100,000	Category 2
> 50,000	Category 3
≤ 50,000	Category 4

=IFS( [@[Sum of Build Value]] > 200000, "Category 1",
      [@[Sum of Build Value]] > 100000, "Category 2",
      [@[Sum of Build Value]] > 50000, "Category 3",
      TRUE, "Category 4")

Handle blanks by converting to zero (e.g., with IF or IFERROR).

Return Nature (based on Return %)

Return %	Category
> 10%	High Return (bad)
> 4%	Medium Return
≤ 4%	Low Return (good)

Frequency (based on Distinct Order Count per month)

Days Ordered	Category
> 20	High Frequency
> 10	Medium Frequency
≤ 10	Low Frequency

Exam tip: The same logic can be extended to Average Order Value — high ticket size is desirable even if order count is low.

Regional Analysis (Same PivotTable, Different Row Field)

Replace Customer ID with Locality Cluster to see aggregated regional performance.

Metric	How to get it
Total Build Value per locality	Sum of Build Value
Return %	Sum(Return QTY)/Sum(Build QTY)
Number of orders	Count of Delivery Date (not distinct, as each row is a transaction)
Average Order Value	Sum(Build Value) / Count of DD

Sorting by Build Value (descending) immediately reveals top regions.
Example insight: JP Nagar and Nagarbhavi (south-west Bangalore) are most valuable; Yeswanthpur underperforms, possibly due to competition (APMCR).

flowchart LR
    A[PivotTable Row: Locality] --> B[Add Measures]
    B --> C[Sort by Total Build Value]
    C --> D{Top region?}
    D -->|JP Nagar| E[Highest revenue & order value]
    D -->|Yeswanthpur| F[Low revenue – investigate]

Key Takeaways

PivotTables alone (without charts) can yield actionable insights for managers — summary in 8 rows instead of 50,000.
Derived metrics (return %, average order value) are critical for segmentation.
Formulas (IFS) convert raw PivotTable outputs into meaningful customer tiers (value, return nature, frequency).
Regional analysis follows the same pattern: replace the row field and apply the same measures and sorting.
Segments drive action – high value + high frequency customers get offers; high return customers need investigation.
Always handle empty cells in PivotTable outputs (e.g., force 0 for blank build value).

Data Insights and Dashboard Creation

Module 2 transformed raw transaction logs into a live decision tool – a manager's cockpit that consolidates key metrics into a single interactive view. Instead of static spreadsheets, you now have a system that answers: Who are my most valuable customers? Which area returns the most? How do revenues shift with price and volume?

A manager's cockpit is a dashboard that consolidates key metrics into one interactive view, allowing on-demand drill-down and slicing.

What We Built

Pivot Tables – Clean, aggregated summaries from raw data (daily sales trends, SKU-level order patterns, geographic breakdowns).
Dynamic Pivot Charts – Visual representations of business movements (e.g., revenue trends, volume shifts) that update automatically when underlying data changes.
Interactive Dashboards – A collection of pivot charts bound together with slicers and filters so users can slice data instantly (e.g., filter by region, time period, product category).
Customer Segmentation – Logical rules and thresholds applied to segment customers (e.g., by geography or value). Segments are defined using decision logic – for example, high-value customers meet threshold conditions on revenue, frequency, or recency.

Components of the Dashboard

Component	Function	Example in Context
Pivot Table	Aggregate raw data into summaries	Daily sales totals, SKU-level order volume
Dynamic Pivot Chart	Visualize business movements	Line chart of revenue over time, bar chart of regional sales
Slicer / Filter	Build interactivity – slice data instantly	Select a region → all charts update to show that region
Dashboard	Single pane for key metrics	Displays customer segmentation, geographic performance, revenue & volume trends

Process: Raw Data to Dashboard

flowchart LR
  A[Raw Transaction Data] --> B[Pivot Tables]
  B --> C[Pivot Charts]
  C --> D[Dashboard with Slicers & Filters]
  D --> E{Insights}
  E --> F[Customer Segmentation<br>by Logic / Thresholds]
  E --> G[Geographic Performance<br>Most returns by area]
  E --> H[Revenue & Volume<br>Changes w.r.t. price & quantity]

Insights Unlocked

Most valuable customers – Who contributes the most revenue or frequency? Segments are revealed by logical thresholds.
Best-performing areas – Which geography brings the highest returns? Geographic segmentation isolates top regions.
Revenue vs. price and volume – How do revenues change when price moves or volume shifts? The dashboard surfaces trends and correlations.
Behavioral interpretation – Beyond numbers: identifying gaps, surfacing growth opportunities, and understanding why patterns occur.

Exam tip: Segmentation by geography and logic is covered here. Behavioral segmentation (RFM – recency, frequency, monetary) appears in Module 3. Do not confuse the two; RFM uses weighted scores, not simple thresholds.

Key Takeaways

Module 2 built a manager's cockpit from raw data using pivot tables, charts, slicers, and filters.
Customer segmentation relies on logical rules and thresholds (e.g., geography, value thresholds).
Dynamic dashboards answer: most valuable customers, best regions, revenue–volume–price relationships.
The focus is on structured business dashboards – not advanced analytics (that comes in Module 3 with CLV, cohort analysis, RFM, and clustering).

Intermediate Excel and Google Sheets with App Scripts

Introduction to ERP Systems and Spreadsheets

ERP (Enterprise Resource Planning) is the central nervous system of a business – it connects departments (sales, finance, operations, HR, inventory, procurement) so everyone works from a common set of data.

Why ERP?	What happens without it	With ERP
Department silos	Sales, inventory, finance each maintain separate records	Single source of truth
Data delays & errors	Reconciliation is manual, slow, error-prone	Real-time updates & automated checks
Poor decisions	Decisions based on stale or inconsistent info	Decisions backed by live, cross‑department data

Popular ERPs in India

Global: SAP, Oracle, NetSuite, Microsoft Dynamics
Indian: Tally, Zoho, Odoo, ERP Next
Large enterprises (Infosys, Tata Steel, Mahindra) use full‑fledged ERPs; many SMEs use modular systems (Tally, Zoho) or self‑built spreadsheets.

Core ERP Modules

Module	Purpose
Customer management (CRM)	Track interactions, leads, customer relationships
Inventory management	Monitor stock levels, movements, availability
Purchase & vendor management	Handle procurement cycles, purchase orders, vendor performance
Finance & accounting	Billing, payments, financial reporting, compliance
HR & payroll	Employee records, attendance, salary, benefits
Operations & production planning	Plan/track production workflows, allocate resources

The Real‑World Role of Spreadsheets

Even large enterprises rely on spreadsheets alongside ERPs. In 2025, over 71% of large enterprises globally still use Excel or Google Sheets for critical financial and operational analysis. In India, giants and startups alike use spreadsheets for:

Reporting & reconciliation
Quick analysis
Custom operations

Spreadsheets are the bridge between formal ERP systems and human flexibility.

What This Module Covers

We will treat spreadsheets as data capture and processing tools – like a mini‑ERP. The module builds toward creating your own mini‑ERP.

Linking sheets – VLOOKUP, INDEX+MATCH, dynamic referencing
Logical functions – IF, AND, OR for decision‑making in sheets
Delivery fee calculator – a real‑life logistics calculator
Google Forms – capture user input directly into sheets (field teams, customer feedback, order management)
Google Apps Script – automate repetitive tasks, link external APIs, turn static sheets into live business systems

Exam tip: Spreadsheets are not a crutch – they are a fundamental tool even in ERP‑heavy enterprises. Understanding how to use them as a mini‑ERP (linking tables, automating logic) is a skill tested in many business‑analyst interviews.

Key takeaways

ERP unifies departments with real‑time, shared data.
Common modules: CRM, inventory, procurement, finance, HR, production.
Spreadsheets are still used by >70% of large enterprises for critical work – they bridge ERP rigidity with human flexibility.
This module transforms spreadsheets into a mini‑ERP using lookup functions, logical formulas, Google Forms, and App Script.

Data Linking Across Sheets

Data linking turns separate spreadsheet tables into a connected system — a lightweight ERP. By linking master data (products, customers) to transaction sheets, you avoid manual lookups, reduce errors, and enable real‑time calculations.

Context: A wholesale vegetable delivery company operates in Bangalore, Chennai, and Mumbai. Orders come via phone, form, or app. The goal is to quickly calculate order costs and delivery fees using linked sheets.

Product Master: Structure and Auto‑Generation

A product master stores the core product catalogue. It’s a dedicated sheet with columns:

Column	Example Data
Product Code	P0001
Product Name	Tomato
Price (₹/kg)	50
On‑hand Quantity	200

Product Code is the primary key — a unique identifier that prevents errors from name variations (spacing, case). Always link tables using codes, not names.

Automatic Code Generation with Text Formulas

Instead of typing codes manually, use a formula that auto‑increments when a product row is added.

=IF(B2<>"", "P" & TEXT(ROW(B2)-1, "0000"), "")

How it works:

IF(B2<>"", … , "") — only generate a code if the product name (B2) is non‑empty.
ROW(B2) returns the current row number (2 in this example). Subtract 1 to start from 1.
TEXT(ROW(B2)-1, "0000") formats the number as a 4‑digit zero‑padded string (1 → "0001").
Concatenate "P" with the formatted number → "P0001".

Copy the formula down the column. When you insert or delete rows, the codes update automatically.

Exam tip: Always use a unique, system‑generated key (like P0001) instead of manual numbering. It survives row insertions and deletions.

Protecting Master Data

Prevent accidental edits to the product master by protecting the range.

Select the range containing the master data.
Go to Data > Protect ranges (or Review > Protect Sheet in Excel).
Set permissions so that only authorised users (e.g., you) can edit.

This keeps the master data clean and reliable for all linked sheets.

Customer Master: Structure and Email Uniqueness

A customer master stores customer details. Columns:

Column	Example
Customer ID	C0001
Name	Kavita Foster
City	Bangalore
Subscription Type	Premium
Email	kfoster@bender.com

Customer IDs are generated the same way as product codes, using "C" instead of "P":

=IF(B2<>"", "C" & TEXT(ROW(B2)-1, "0000"), "")

Enforcing Unique Emails with Data Validation

To prevent duplicate customer registrations, add a data validation rule on the email column.

Select the email range (e.g., E2:E).
Go to Data > Data validation.
Set Criteria to Custom formula is.
Enter the following formula:

=COUNTIF($E$2:E, E2) = 1

What it does:

COUNTIF($E$2:E, E2) counts how many times the email in the current cell (E2, E3, …) appears in the entire column (from E2 downwards).
The formula returns TRUE only when the count equals 1 (i.e., the email is unique).
If the user tries to enter a duplicate email, the validation rejects the input.

Referencing note: The $E$2:E uses a mixed reference — the start of the range is locked ($E$2), but the end is relative (E), so as the rule applies to each cell, the range stays anchored to the top while the value being checked moves.

Exam tip: In data validation custom formulas, use mixed references ($ for the start) to keep the lookup range fixed while checking each row’s value.

Key Takeaways

Linking sheets with unique codes (product code, customer ID) is the foundation of a spreadsheet‑based mini‑ERP.
Auto‑generate codes using IF, TEXT, and ROW to avoid manual errors and handle row insertions.
Protect master data ranges to prevent accidental edits.
Enforce data integrity with data validation — e.g., =COUNTIF($E$2:E, E2)=1 ensures unique emails.
Use mixed references ($E$2:E) to lock the start of a range while allowing the checked cell to move.

Data Linking using Spreadsheets – II

Building an order sheet with a calculation system. The goal: capture orders by selecting Customer ID and Product ID from validated lists, then automatically populate related fields (names, city, product name). This prevents manual entry errors and keeps data structured.

Data Validation for Dropdowns

Restrict inputs to valid IDs using dropdowns from a source range.

Select the column (e.g., Customer ID).
Data → Data validation → Add rule (Google Sheets) or Data Validation (Excel).
Choose Dropdown from a range, then point to the ID column in the master sheet.
Use absolute referencing ($A$1:$A$1000) so the range stays fixed when copied.
Remove the $ on the last row (e.g., $A$1:$A) to make the range "infinite" (Google Sheets).

Apply a second validation for Product ID similarly.

Exam tip: Always use absolute referencing ($) for the lookup range in data validation when you intend to copy the validation down. Without $, the range shifts and breaks.

VLOOKUP for Single‑Row Lookups

VLOOKUP searches the first column of a table, moves horizontally to a specified column, and returns the corresponding value.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: the ID to match (e.g., A2).
table_array: the master data range (absolute, e.g., $A$1:$E$1000).
col_index_num: the column number in the range (1‑based: 1 = first column, 2 = second, etc.).
range_lookup: FALSE for exact match (always use for IDs).

Example – fill Customer Name
=VLOOKUP(A2, CustomerMaster!$A$1:$E$1000, 2, FALSE)

A2 = chosen Customer ID.
CustomerMaster!$A$1:$E$1000 = master table (absolute).
2 = second column contains the name.

Copy down – the A2 reference changes (relative), but the table array stays fixed because of $.

Absolute vs. Relative Referencing

Referencing	Syntax	Behaviour when copied
Absolute	`$A$1`	Does not change – locks to that cell.
Relative	`A1`	Adjusts by row/column offset.
Mixed	`$A1` or `A$1`	Only one part fixed.

Shortcut: F4 (Windows) to toggle through absolute/relative modes.

Handling Missing Data – IFERROR

When no customer is selected, VLOOKUP returns #N/A. Wrap the formula in IFERROR to output a blank instead:

=IFERROR(VLOOKUP(...), "")

Apply the same pattern for city (col 3), product name, etc., referencing the appropriate master sheet and column index.

INDEX‑MATCH – Two‑Dimensional Lookups

INDEX‑MATCH is more flexible than VLOOKUP: it can look up values in any direction and handle two‑way lookups (row + column).

INDEX

=INDEX(reference, row_num, [column_num])
Returns the value at the intersection of a given row and column within reference.

MATCH

=MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of lookup_value in lookup_array (1‑based).
Use 0 for exact match.

Combined

Replace row_num and column_num in INDEX with MATCH functions to dynamically find the correct row and column.

Workflow:

flowchart LR
    A[Lookup Value] --> B[MATCH → row index]
    C[Column Header] --> D[MATCH → column index]
    B & D --> E[INDEX(data, row, col) → result]

Example – delivery fee based on city and weight slab

Weight Slab	BLR	CHN	MUN
<5 kg	30	40	50
5–10 kg	50	60	70
10–25 kg	100	120	140
25–100 kg	150	200	220
>100 kg	250	300	350

To get the fee for city "CHN" and slab "10–25 kg":

MATCH("CHN", city_header_row, 0) → column index 3.
MATCH("10–25 kg", weight_slab_column, 0) → row index 4.
INDEX(fee_table, row_index, col_index) → returns 120.

This works even if the lookup columns are not the leftmost (unlike VLOOKUP).

Key takeaways

Data validation restricts inputs; always use absolute ranges when copying.
VLOOKUP is simple for left‑to‑right lookups; set FALSE for exact match.
IFERROR cleans up #N/A when no input is given.
INDEX‑MATCH enables two‑dimensional lookups and is not limited to the first column.
Absolute referencing ($) is critical for ranges that must not shift; use F4 to toggle.
Automating lookups reduces manual entry errors (spelling, case, spacing).

Building a Delivery Fee Calculator

The goal is a calculator that takes city, quantity (kg), and product as inputs and outputs the final price (product cost + delivery fee). This mimics a mini-ERP system where data is linked across sheets.

Core components:

Data validation for city (BLR, CHN, MUM), product (from product master), and customer (from customer master).
Quantity slab – map a numeric quantity into a pre-defined weight range using IFS.
Pricing table – lookup delivery fee based on city and slab using INDEX-MATCH.
Product master – lookup price per kg using VLOOKUP.
Final formula:
$\text{final_price} = (\text{price_per_kg} \times \text{quantity}) + \text{delivery_fee}$

Using `IFS` for Quantity Slabs

IFS evaluates conditions sequentially – the first true condition returns its value.
No need to check both lower and upper bounds; the order guarantees the correct range.

Example:

=IFS(
  A1<5, "Less than 5 kg",
  A1<10, "5 to 10 kg",
  A1<25, "10 to 25 kg",
  A1<100, "25 to 100 kg",
  TRUE, "Above 100 kg"
)

If quantity = 10, condition A1<10 is false (since 10 is not less than 10), so it falls to A1<25 → "10 to 25 kg".
If quantity = 9.9, A1<10 is true → "5 to 10 kg".

Exam tip: IFS stops at the first true condition. Always place narrower ranges first. Use TRUE as a catch-all final condition.

INDEX-MATCH for Two-Way Lookup

To retrieve the delivery fee from a pricing table where rows = city and columns = quantity slab:

Row match: MATCH(city, city_range, 0)
Column match: MATCH(slab, slab_range, 0)
INDEX: INDEX(fee_range, row_num, col_num)

Worked example (from lecture data):

City	<5 kg	5–10 kg	10–25 kg	25–100 kg
BLR	...	...	...	...
CHN	...	...	120	...
MUM	...	...	140	...

Quantity = 23 kg → slab = "10 to 25 kg". City = CHN.
Row match on CHN → row 2; column match on "10 to 25 kg" → column 3.
INDEX returns 120.

Always use exact match (0 or FALSE) in MATCH for pricing lookups.

VLOOKUP for Product Price per kg

From the product master table (columns: Product Name, Price per kg):

=VLOOKUP(product_name, product_master, 2, FALSE)

Use product codes instead of names in production to avoid duplicates.

Adding Subscription Logic with IF, AND

Enhance the calculator: if the customer is free, use the standard delivery fee (from INDEX-MATCH). If premium, apply a flat rate per city regardless of weight.

Steps:

VLOOKUP customer → subscription type (free / premium).

Use IFS with AND:

=IFS(
  subscription_type = "free", standard_delivery_fee,
  AND(subscription_type = "premium", city = "BLR"), 30,
  AND(subscription_type = "premium", city = "CHN"), 50,
  AND(subscription_type = "premium", city = "MUM"), 70
)

The AND function returns TRUE only if all conditions are true.

Logical Function	Behavior
`AND(cond1, cond2, ...)`	TRUE if all conditions TRUE
`OR(cond1, cond2, ...)`	TRUE if any condition TRUE
`NOT(cond)`	Flips TRUE ↔ FALSE
`SWITCH(expr, case1, val1, case2, val2, ...)`	Match expression (Excel 2026+)

Final calculator structure (user inputs only yellow cells):

Customer (dropdown) → auto-populates city and subscription type via VLOOKUP.
Product (dropdown) → auto-populates price per kg via VLOOKUP.
Quantity (manual) → determines slab → delivery fee via INDEX-MATCH (then overridden by premium logic if needed).
Final price = price_per_kg * quantity + delivery_fee_final

Exam tip: Use nested IF or IFS for multi-condition branching. AND/OR reduce nesting and improve readability.

Key Takeaways

IFS evaluates conditions sequentially; use TRUE as a default.
INDEX-MATCH performs two-dimensional lookups (row + column) without restructuring data.
VLOOKUP is simpler for single-column lookups; prefer product codes over names.
Combine IF/IFS with AND to implement conditional business rules (e.g., premium flat rate by city).
Build a clean user interface: hide helper columns, mark input cells in yellow.
All formulas can reference data from other sheets, creating an interconnected system that simulates an ERP.

Capturing Data using Google Forms

Google Forms integrated with Google Sheets provides a structured way to capture data from external users (e.g., sales or inventory teams) directly into a spreadsheet. Rather than manually entering data into a sheet, a form enforces consistency, reduces errors, and automatically timestamps entries. This feature is unique to Google Sheets and is not available in Excel (which requires custom VBA forms).

Creating a form from within a sheet links the responses to a dedicated sheet tab; every new submission appends a row instantly.

Creating a form from Sheets

Go to Tools → Create a new form (or use the Forms menu in Sheets).
The form is automatically linked to a new sheet tab named “Form Responses 1”.
Rename the form and the response tab (e.g., “Inventory Purchase Form”).

Designing the form questions

Dropdown question – predefined choices (e.g., product names). Useful for standardised data.
Short answer – free text; apply a validation rule (e.g., number > 0) to enforce quality.
Timestamp – added automatically by the form (not a question).

Exam tip: Always validate numeric fields (number > 0) to prevent invalid entries. Forms with validation are significantly more reliable than raw sheet input.

Publishing and collecting responses

Click Publish → a responder link is generated.
Anyone with the link can fill the form; each submission appends a row to the linked sheet.

Automatic logging in the sheet

The response sheet logs: Timestamp, and each question’s answer as columns.
Data can be immediately used for analysis (e.g., inventory purchases linked to product master).

flowchart LR
  A[Team member fills form] --> B[Google Form]
  B --> C[Linked Sheet tab]
  C --> D[Inventory analysis]

Excel vs. Google Sheets

Feature	Google Sheets	Excel (desktop)
Native form integration	Built-in via Google Forms	No native equivalent – requires VBA UserForms or Power Apps
Automatic response logging	Automatically appends rows to sheet	Manual macro / Power Query needed
Real-time collaboration	Multiple users submit, instant update	No real-time

Key takeaways

Google Forms provide a controlled, structured input method for Sheets – ideal for teams.
Create a form from Tools → Create a new form; it auto-links to a response sheet.
Use dropdown for fixed options and short answer with validation for numeric fields.
Timestamps are captured automatically.
Forms are a Google Sheets-only advantage over desktop Excel.
The logged data (e.g., inventory purchases) can be linked to other sheets for further analysis (e.g., reorder level calculations).

Lead Time

Lead time is the time between placing an order and receiving the goods. It is a critical input for inventory planning: knowing how long replenishment takes determines when to reorder.

Average lead time: the typical number of days from order to delivery (e.g., tomatoes and potatoes usually arrive in 2 days, oranges in 5 days).
Max lead time: the longest possible delay (e.g., from an unreliable supplier). Used to manage risk – even if most orders arrive on time, a safety buffer is needed for late deliveries.

Both values are derived from historical data, supplier agreements, or experience. In the sample data:

Product	Average Lead Time (days)	Max Lead Time (days)
Tomato	2	(known from data)
Potato	2
Orange	5

Key takeaway: Lead time drives reorder timing; max lead time builds a risk cushion.

On-Hand Inventory

The quantity of each product currently in stock. Combined with lead time and expected sales, it determines when and how much to reorder.

If inventory is low and lead time is long, a reorder must be placed earlier.
On-hand inventory is a direct input for reorder point calculations.

Average Daily Sales

Average daily sales estimates how many units of a product are sold per day. This is used to forecast how much inventory will be consumed during the lead time.

Calculation method: Use a pivot table on the order master to first sum daily quantities per product, then average those daily totals.

Step 1: Build a pivot table that sums order quantities per product per day

Row fields: Product ID and Product Name (with repeat row labels), and Purchase Date.
Value field: Order Quantity, summarized by Sum.
Result: for each product and each date, the total kgs sold that day.

Common mistake: Directly averaging order quantities gives the average order size, not average daily sales. Summing daily totals first corrects for multiple orders on the same day.

Step 2: Create a second pivot table on the first pivot table to compute average and standard deviation

Row fields: Product ID and Product Name (filter blanks).
Value fields:
- Average of Sum of Order Quantity → average daily sales.
- Standard Deviation of Sum of Order Quantity → measure of daily fluctuation.

Data flow:

flowchart LR
    A[Order Master<br/>(raw transactions)] --> B[Pivot Table 1<br/>Sum of quantity per product per date]
    B --> C[Pivot Table 2<br/>Average and Std Dev of daily totals]
    C --> D[Average Daily Sales<br/>and Std Dev]

Formally:

Let $Q_{p,d}$ be the sum of order quantities for product $p$ on day $d$ (from Step 1). Then for $n$ days:

\text{Average Daily Sales}_p = \frac{1}{n}\sum_{d} Q_{p,d}

\text{Std Dev (sample)}_p = \sqrt{\frac{1}{n-1}\sum_{d} \left(Q_{p,d} - \text{Avg}_p\right)^2}

Exam tip: Always ask: "What does the average represent?" The pivot table's average of raw order quantities is not average daily sales – it's the average order size. You must aggregate by day first.

Standard Deviation of Daily Sales

Standard deviation captures the fluctuation (inconsistency) of daily sales. A higher standard deviation means sales vary more from day to day.

More fluctuation → need a larger buffer inventory (safety stock) to avoid stockouts on unexpectedly high sales days.
Higher average daily sales also require more inventory because items move faster.

Key relationship:

Average daily sales → base stock needed for lead time.
Standard deviation → safety stock to cover variability.

Key takeaway: Use both average and standard deviation of daily sales to set inventory levels – not just the average alone.

Key takeaways for Concepts in Spreadsheets – I

Lead time (average & max) drives reorder timing and risk management.
On-hand inventory is the starting point for reorder decisions.
Average daily sales must be computed as the average of daily sums, not of raw orders.
Standard deviation of daily sales quantifies demand variability and determines safety stock.
Pivot tables on pivot tables enable this two‑step aggregation.

Enhancing the Product Master with Sales Statistics

To compute inventory metrics, the product master table is extended with two derived fields from daily sales data:

Average daily orders – obtained via a pivot table (grouping by product key) and then pulled into the master with =VLOOKUP(product_key, pivot_table_range, column_index, FALSE).
Standard deviation of daily orders – same pivot approach, but selecting the STDEV.P (or STDEV.S) aggregation. The formula is copied across after anchoring the lookup range with absolute references (e.g., $A$1:$C$100).

Metric	Source	Calculation
Average daily orders	Pivot (AVERAGE)	`=VLOOKUP(key, pivots!A:C, 3, FALSE)`
Std dev of daily orders	Pivot (STDEV)	`=VLOOKUP(key, pivots!A:C, 4, FALSE)`

Jackfruit example: average daily order = 12, standard deviation is high because order quantities in the source data include 50 and 25 – orders fluctuate significantly.

Why Safety Stock Is Necessary

Inventory faces two independent sources of uncertainty:

Demand fluctuation – daily orders vary around the average.
Supply lead time fluctuation – the time between placing an order and receiving it is not constant (e.g., average 2 days, max 5 days).

Without a buffer, a spike in demand or a delay in supply causes a stockout – inability to fulfil orders.

Safety Stock: A Rudimentary Calculation

Safety stock is the minimum inventory kept to absorb worst‑case scenarios.

Approach 1: Simple range method (illustrative)

$\text{Safety Stock} = (\text{Max Lead Time} - \text{Avg Lead Time}) \times (\text{Max Daily Orders} - \text{Avg Daily Orders})$

This covers the case where both extremes happen simultaneously.

Approach 2: Using standard deviation (practical)

Since we have only the average and standard deviation of daily orders, we approximate the “max” as $\text{Avg} + 2\sigma$ (covering ~95% of demand if normally distributed). Then:

$\text{Safety Stock} = (\text{Max Lead Time} - \text{Avg Lead Time}) \times 2\sigma_{\text{daily orders}}$

Why 2σ? In a normal distribution, the average ± 2σ contains about 95% of all values. This corresponds to a 95% service level – you will have enough stock 95% of the time.

The transcript notes this is a rudimentary method; professional settings use joint distributions of lead time and demand variability.

Reorder Point (ROP)

The reorder point is the inventory level at which a new order should be placed. It consists of two parts:

Safety stock – always kept for worst‑case events.
Cycle stock – quantity needed to cover average demand during the average lead time.

$\text{Reorder Point} = \text{Safety Stock} + (\text{Average Daily Orders} \times \text{Average Lead Time})$

When physical stock falls to this level, a replenishment order is triggered.

Worked Example (Illustrative)

Assume:

Average daily orders = 12 units
Std dev of daily orders = 5 units
Average lead time = 2 days
Max lead time = 5 days

Safety stock (using 2σ method): $\text{Safety Stock} = (5 - 2) \times (2 \times 5) = 3 \times 10 = 30 \text{ units}$

Reorder point: $\text{ROP} = 30 + (12 \times 2) = 30 + 24 = 54 \text{ units}$

Interpretation: When on‑hand inventory reaches 54 units, place a new order. The 30 units of safety stock will only be used if demand is unusually high or lead time is extended.

Inventory Decision Logic

flowchart TD
    A[Monitor inventory] --> B{Stock ≤ Reorder Point?}
    B -->|No| A
    B -->|Yes| C[Place order for replenishment]
    C --> D[Wait during lead time]
    D --> E[Receive goods]
    E --> A

The safety stock ensures that during the lead time, even if demand surges or the shipment is delayed, the inventory does not hit zero.

Key takeaways

Use VLOOKUP and pivot tables to compute average and std dev of daily orders per product.
Safety stock = (max lead time – avg lead time) × 2 × std dev of daily orders (rudimentary, ~95% service level).
Reorder point = safety stock + (avg daily orders × avg lead time).
Safety stock is a buffer against demand variability and lead time variability; reorder point triggers timely replenishment without dipping into the buffer.
This is a simplified model; real‑world systems often use joint distributions and dynamic safety stock.

Inventory Management with Spreadsheet Functions

Inventory management answers two practical questions: when to order and how much to order. Holding too much stock ties up capital and incurs storage costs; holding too little risks stockouts. Spreadsheets provide tools to automate these decisions.

Economic Order Quantity (EOQ) – Concept

EOQ is a classic formula that balances ordering cost (delivery charges per order) and holding cost (storage, capital lockup). The optimal order quantity minimises total cost:

$\text{EOQ} = \sqrt{\frac{2DS}{H}}$

where $D$ = annual demand, $S$ = cost per order, $H$ = holding cost per unit per year.
The lecture mentions EOQ only as background; in practice, a simpler rule based on reorder point and on-hand inventory is used.

Reorder Point & Safety Stock

Reorder point – the inventory level that triggers a new order. It accounts for lead time and expected demand.
Safety stock – extra buffer to cover demand fluctuations.
The lecture assumes reorder point is given per product (e.g., 50 for Mosambi, 28 for Tomato Hybrid, 69 for another product).

Calculating On-Hand Inventory

On-hand inventory at any moment is the difference between total purchases and total sales (orders):

$\text{On Hand} = \sum \text{Purchases} - \sum \text{Sales}$

Using SUMIF, you sum quantities by matching a product ID.

Product ID	Product Name	Purchases (SUMIF from Purchase Master)	Sales (SUMIF from Order Master)	On Hand
P001	Tomato Hybrid	1000	72	928
…	…	…	…	…

Negative on-hand indicates sold more than purchased – a data error (theft, recording mistake, or missing purchase). This must be corrected.

Extracting Product IDs from Combined Strings

If product data is stored as a combined string (e.g., "Orange P001"), extract the ID for lookup:

LEFT – when ID has fixed length (e.g., 5 characters): =LEFT(A2,5)
MID + FIND – when ID position varies:
- Find the space: =FIND(" ", A2)
- Extract from that position+1: =MID(A2, FIND(" ", A2)+1, LEN(A2)-FIND(" ", A2))

Exam tip: Use LEFT when product codes are fixed-length; use MID+FIND when they are variable-length. Always test edge cases (missing strings).

Decision: How Much to Order

If on-hand inventory is below the reorder point, order enough to bring it up to the reorder point; otherwise order zero.