Term 3 · Module 4 of 4

Business Applications

Spreadsheets for Business Decisions

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 applied business scenario is 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

ModuleFocusKey Tools & Techniques
1Data capture & organisation (sales, purchases, inventory)Basic sheets, ordering decisions
2Sales analyticsPivot tables, dashboards, trend visualisation
3Advanced 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

  1. The Trial Balance (TB) – what it is, how to read it, why it matters.
  2. Build a P&L from a Trial Balance – map TB data into a structured statement.
  3. Analyse percentages, margins, and key ratios within the P&L.
  4. Interpret cost breakdowns and income trends to extract insights.
  5. 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 TypeScalabilityComplexityMargin
Standard fixed unitsHigh (predictable sizes)LowLower (competitive)
Custom wooden workLow (bespoke)HighHigher
Add-on servicesMedium (bundled)MediumHighest

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:

  • 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

Ledger names and percentages indicate multiple billing patterns:

Product LineStage 1Stage 2Stage 3Stage 4
Fixed wooden furniture5%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).

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) Gross Margin=Net Revenue−Direct Costs\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) CM1=Gross Margin−Direct Marketing Expenses\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) CM2=CM1−Indirect Operating Expenses\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 EBITDA=CM2−SG&A\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

Why Layer? – Diagnostic Power

If this margin is low……the likely problem is
Gross MarginProduction/material costs too high
CM1Marketing inefficient (too much spend for too little revenue)
CM2Support/indirect teams overstaffed or underutilised
EBITDACore business model itself is unhealthy

Industry Variations

IndustryRevenue SourcesDirect CostsMarketing FocusKey Customizations
SaaS (Software)Monthly subscriptions, usage feesCloud servers, hosting, customer supportPaid campaigns, trials, affiliate fees (often large)Gross margin typically very high; CM1 may be skipped. Recurring profit from core tech operations is isolated.
D2C FashionOnline/offline sales, product categories (shirts, shoes, etc.)Manufacturing, warehousing, deliveryFacebook, Instagram, influencers, email targetingCM1 is crucial (marketing game). Profitability is tracked by collection or category.
Furniture & Civil ServicesDesign fees, advances, post-delivery milestonesMaterial, labor, transportGoogle/Face ads, sales incentivesAll 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

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), each mapped to a sub‑head under Net Revenue.

Ledger ExampleMapped To
5% booking amount (woodwork)Woodwork Booking
Later cost, civil bookingCivil Booking
Other revenue (scrap sales)Other Revenue
Finished modular/furnitureFixed Units Revenue

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

Gross Revenue=Net Revenue×(1+GST rate)\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 CategoryLedger Examples
Material – Wood + FixedHardwares, laminates, plywood, outsourced interior work
Direct Costs – CivilFall ceiling, civil electricity, plumbing, painting, final cleaning
Installation & DeliveryCarpenter costs, inward/outward logistics
Factory CostsFactory operators, security, electricity, generator, warehouse
Rework & WastageSeparate head to track month‑on‑month reduction
Other Direct CostsFitting charges, outsourced activity

Gross Margin=Net Revenue−Total Direct Costs\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.

ChannelLedger Examples
OnlineFacebook, Google, Bing ads, website costs, organic channels
OfflineCall centre, lead purchase, agent/influencer commissions, direct events
ReferralClient referral fees (tracked separately from agent referrals)

Contribution Margin 1=Gross Margin−Direct Marketing Expenses\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)

Contribution Margin 2=Contribution Margin 1−Project Costs\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 HeadExamples
Office & ShowroomRent, maintenance, parking, repairs
Central Team SalariesHR, management, admin, technology, support teams
Consultancy & ProfessionalLegal, recruitment, marketing consultants
TechnologySoftware subscriptions, asset/equipment rentals
General AdminStationery, broadband, travel, staff welfare
Central BrandingBillboard with logo only (non‑lead‑generating)

EBITDA=Contribution Margin 2−Total Central Costs\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)

HeadDerivationPurpose
Gross Revenue (GMV)Net Revenue + GSTTotal invoice value
GST–Tax collected, not company income
Net RevenueSum of all revenue headsTrue top line
Direct CostsSum of direct material, factory, installation, etc.Cost of goods/services sold
Gross MarginNet Revenue – Direct CostsProfit after direct costs
Indirect Costs (direct marketing, branding)Sum of marketing, branding, referralCustomer acquisition & brand
Contribution Margin 1 (CM1)Gross Margin – Indirect CostsProfit after acquisition costs
Project Costs (design, sales, project team)Sum of team costs tied to projectsDelivery‑related overhead
Contribution Margin 2 (CM2)CM1 – Project CostsProfit before central overhead
Central Costs (office, salaries, consulting, tech)Sum of all corporate overheadFixed operational expenses
EBITDACM2 – Central CostsOperating 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 Costs29%Largest cost, slightly high – negotiate vendors, standardise
Factory Costs7.5%Operational, room for optimisation
Installation4.6%Reasonable but reducible (fixed installation)
Other Direct (rework/wastage)2.5%Monitor for leakage
Civil Costs5%Very low relative to 21% revenue → high margin
Direct Marketing (total)10.13%Heavy online (7.91%) – dependency on paid ads
Offline Marketing1%Underutilised channel
Call Center2.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

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

  1. Boost demand for high‑margin product lines (fixed units) via festivals, campaigns, bundles – increase bookings (currently 1‑5%).
  2. Reduce material costs – negotiate fixed‑price vendor contracts, standardise SKUs, bulk procure.
  3. Optimise marketing – reduce online dependency, increase offline/referral (low cost), measure CAC by channel.
  4. Improve design efficiency – fewer designers needed for standard products; automate or repurpose.
  5. 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

AreaKey metricsData source / who to ask
MarketingLeads by channel (Google, Facebook, referral, offline), conversion per channel, cost per lead, cost per conversion, average order value (AOV), campaign ROASMarketing team; tools like Facebook Ads Manager, Google Ads, HubSpot, Zoho CRM, internal Excel trackers
InventoryRaw material inflow/outflow, stock aging, unsold materials, monthly consumption vs. plan, vendor delivery delaysPurchasing team, factory/warehouse
Project & DeliveryProjects 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 SuccessNPS scores, cycle time, bottleneck analysisCustomer success team
Conversion FunnelLead count → site visits → designs sent → bookings → handovers, stage-wise drop-offs, conversion time per leadSales team, CRM team, designer heads
Sales ForecastingLead pipeline by city/product/channel, quotation status, probability per deal, month-on‑month forecast vs. actualSales managers, CRM (Zoho, Salesforce etc.)
Cash Flow & Balance SheetCash in hand, vendor payment delays, receivables, asset purchases, loan repaymentsFinance/accounts, CFO
HR & ProductivityDepartment-wise headcount, cost vs. output, attrition rate, absenteeism, per-person ROIHR 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).

SourceTotal Leads% of Total LeadsConversionsConversion Rate% of Total Conversions
Offline~4,800*~21%~4569.5%~4%
Bing~2,000*~9%~40020%~3%
Referral~1,000*~4%~50050%~4%
Google AdWords~5,500*~24%~1,10020%~9%
Facebook~11,700*~51%~8007%~7%
Total23,000100%3,00013%100%

Values are approximate. Facebook generates 51% of leads but only 7% convert; Google and referrals have higher conversion rates.

Average Order Value (AOV) by Source

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

SourceTotal Signup Value (₹)ConversionsAOV (₹)
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

SourceLeadsConversionsConversion RateAOV (₹)Total Cost (₹)Cost per Conversion (₹)
Offline4,8004569.5%60,000(branding + share)high
Bing2,00040020%30,000144,000360
Referral1,00050050%60,000(cost from P&L + share)~200
Google5,5001,10020%30,000(40L + share)~370
Facebook11,7008007%30,000(40L + share)~500

Exact direct-cost figures for Offline, Referral, Google, and Facebook are not provided. The table illustrates 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

MetricValue
Conversion rate21.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 conversions39% (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

MetricValue
Conversion rate~7% (low)
Cost per leadVery low (high volume: ≈11,000 leads)
Cost per conversionHigher than Google
AOVSlightly higher than Google
Cost as % of AOV>12% (higher than Google)
Share of total conversions32%

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

MetricValue
Conversion rate50% (highest of all)
Cost per lead~₹300 (very low)
Cost per conversionLowest (≈1% of AOV)
AOVHighest among all channels
Cost as % of AOV~1%
Share of conversions11% (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

MetricValue
Conversion rate~10% (good; better than Facebook but below Google)
Cost per lead~₹300
Cost per conversion~₹3,700 (similar to Google)
AOVHigh (second to referral)
Cost as % of AOVReasonable (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

MetricValue
Conversion rateVery low volume, data noisy
Cost per leadLow (low competition)
Cost per conversionLow
AOVAverage
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

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:

MetricFormulaWhat it reveals
Cost per Lead (CPL)Total marketing spendNumber of leads\frac{\text{Total marketing spend}}{\text{Number of leads}}Efficiency of top‑of‑funnel acquisition
Cost per Convert (CPC)Total marketing spendNumber of conversions\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?”