Value Proposition: Customer Profitability Analysis often fails for a simple reason: “Revenue is known” but the true cost is not visible (discounts, commissions, payment gateway fees, shipping, returns, customer service… etc.). The result is that you assess a “big” customer as profitable because the invoice margin looks good, while the Cost to Serve eats into profitability. This pack builds a practical accounting model that transforms your data into Customer Profitability and Product Profitability that is auditable: contribution margin, service cost allocation, channel profitability, and analysis of fees and discounts—with a link between revenue and COGS to the GL so that the results do not become “unsupported management analysis.”

In 20 Seconds: What Will You Get?

• Customer Profitability: Customer profitability after COGS and after Cost to Serve (not “invoice margin”).
• Product Profitability: Product/category margin with separation of the impact of discounts and returns.
• Profit Margin Analysis by Customer: Margin table (Gross → Net → Contribution) for each customer + Top/Bottom ranking.
• Cost to Serve: Allocation engine (shipping/fulfillment/delivery/collections/service) with clear auditable rules.
• Channel Profitability: Channel P&L (Retail / Online / B2B …) based on your data.
• Analysis of Fees and Discounts: Discount leakage + Fees impact (gateways/commissions/Marketplace) linked to sales.
• Control Tie‑out: Matching revenue and COGS (and sometimes fees) with GL/TB to ensure that the model is built on traceable numbers.

CTA related to outputs: you will receive Customer margin waterfall + Cost to Serve allocation + Channel P&L + GL Tie‑out ready for operation.

Suitable For

• Controller / FP&A: Operational profitability reports that can be linked to the TB instead of “separate management analysis.”
• Commercial Finance / Pricing: Repricing/renewal/negotiating discounts based on profitability after Cost to Serve.
• E‑commerce / Distribution: Environments where fees and discounts and returns make Net Revenue and Fees a critical factor.

Not Suitable For

• If your goal is complete Cost accounting (Standard costing/variance/WIP) — this is a customer/product profitability model, not a factory cost system.
• If you do not have a detailed level of sales data (Line level) or cannot extract COGS/fees — the results will become too estimative.

Without the Model / With the Model (Short Comparison)

Before Use: 5 Symptoms That Customer Profitability is “Misleading”

• The highest sales customers do not appear as the most profitable when accounting for returns/discounts/fees—but there is no model to prove it.
• Discounts are managed as a monthly total without measuring “Discount leakage” for each customer/channel.
• Shipping/fulfillment/returns costs are recorded as overhead without a Cost to Serve showing who consumes them.
• Commissions/platform fees/payment gateways affect Net, but there is no linking them to sales by channel.
• Any profitability report cannot be linked to GL/TB numbers, turning it into a discussion rather than a decision.

How is Customer Profitability Analysis Built Practically from Sales Data to Pricing Decision?

The model does not “explain profitability” but builds it step by step: (1) We aggregate sales at the invoice line level and arrive at Net Revenue after discounts and returns, (2) We link COGS at the same level or through logical Mapping, (3) We add Cost to Serve with documented allocation rules (Drivers) such as number of shipments, weight/size, number of returns, payment gateway transactions, customer service visits… then we produce Customer margin waterfall and Top/Bottom lists and channel profitability analysis—with a Tie‑out proving that the starting point number is correct.

Application Method (3 Steps)

Step 1: Preparation and Report Gathering

• Extract Sales invoice lines (Customer / SKU / Qty / Gross / Discounts / Net) + sales channel (if available).
• Extract Returns/Credit Notes and link them to the invoice/customer/item (to avoid mixing month returns with sales from another month without explanation).
• Extract COGS at the SKU level or at the category level (depending on your data availability) + any direct fees (payment gateways/Marketplace/commissions).
• Define Cost to Serve Pools: shipping/fulfillment/delivery/collections/service/support… and identify appropriate Drivers for each Pool.

Step 2: Reconciliations + Matches + JE Log

• Build Net Revenue: Gross − discounts − returns/allowances (keeping the impact of Analysis of Fees and Discounts as separate as possible).
• Link COGS to produce Product gross margin then Product Profitability (by SKU/Category).
• Run the Allocation engine for Cost to Serve: apply Drivers and generate “Allocated cost” at the customer/order/channel level.
• Output an “Exceptions List”: sales lines without SKU mapping, customers without channel, unlinked fees… (these are the data improvement points).
• Prepare an optional JE Log (if you decide that some fees/discounts need Reclass within GL for better presentation), with justification and references.

Step 3: Adjusted TB + Reports + Closing/Reporting Pack

• GL Tie‑out: linking total Net revenue and COGS (and sometimes fees) with TB for the period, with a record of variances (Timing/Mapping/Unposted).
• Output management reports: Customer margin waterfall + channel profitability + profit margin analysis by customer + Product contribution.
• Prepare Reporting pack: assumptions + drivers + tie‑out + snapshots + sign‑off (Prepared/Reviewed) to make the file deliverable internally.

Package Components (Clear Inventory)

1. Sales & Discounts Import
   • Practical Purpose: Import sales lines to establish Net Revenue and detail discounts.
   • When to Use: Monthly/Quarterly.
   • Output Evidence: Sales basis export for the period + mapping of fields.
2. Returns/CN Import
   • Practical Purpose: Link returns to sales to explain their impact on Net Revenue and customer profitability.
   • When to Use: Monthly (and weekly review if return volume is high).
   • Output Evidence: Returns register + linking to invoice/period.
3. COGS & Cost Mapping
   • Practical Purpose: Link the cost of goods sold to the product/category to produce Product gross margin.
   • When to Use: Monthly when COGS is available or when updating product costs.
   • Output Evidence: Mapping sheet + list of unlinked product exceptions.
4. Fees & Commissions Module
   • Practical Purpose: Load payment gateway/Marketplace/commission fees and link them to the channel/customer to form Net contribution.
   • When to Use: Monthly, especially in Online/Marketplace channels.
   • Output Evidence: Fees schedule by channel/customer + unexplained fee variances.
5. Cost to Serve Pools & Drivers
   • Practical Purpose: Define Cost to Serve Pools (Fulfillment/Delivery/Returns/CS/Collections) and select Drivers for allocation.
   • When to Use: Once at setup then reviewed quarterly or when operations change.
   • Output Evidence: Assumptions & drivers log + Approved sign‑off.
6. Allocation Engine
   • Practical Purpose: Apply Drivers to produce “Allocated cost” at the customer/channel/product level.
   • When to Use: Monthly/Quarterly after data loading.
   • Output Evidence: Allocation results + reconciliation to total Pool.
7. Customer Margin Waterfall
   • Practical Purpose: Turn numbers into an auditable story: Gross → Discounts → Returns → Net → COGS → Fees → Cost to Serve → Contribution.
   • When to Use: When reviewing customers/negotiating/renewing contracts.
   • Output Evidence: Customer profitability statement for each customer + Top/Bottom list.
8. Channel P&L
   • Practical Purpose: Aggregate profitability by channel/platform with isolation of fees, commissions, and returns.
   • When to Use: Monthly/Quarterly.
   • Output Evidence: Channel P&L + variance compared to previous period.
9. GL Tie‑out & Reporting Pack
   • Practical Purpose: Link total revenue and COGS (and depending on data: fees) with TB and document variances.
   • When to Use: When preparing the monthly/quarterly report or before presenting results to management/internal audit.
   • Output Evidence: Tie‑out notes + Evidence index + Sign‑off.

What Should Be Included in the Delivery?

• 01-Customer-Product-Profitability-Model.xlsx: The main model file (Data model + Output dashboards).
• 02-Sales-Import-Template.xlsx: Sales lines import template + Discounts fields + Channel fields.
• 03-Returns-CN-Import-Template.xlsx: Returns/CN template and linking to invoice/period.
• 04-COGS-Mapping-Template.xlsx: COGS mapping by SKU/Category + exceptions + rules.
• 05-Fees-Commissions-Import.xlsx: Payment gateway/Marketplace/commission fees + linking to channel/customer.
• 06-Cost-to-Serve-Pools-Drivers.xlsx: Definition of Pools + Drivers + measurement source + review/approval.
• 07-Allocation-Engine.xlsx: Cost to Serve allocation + reconciliation to total Pool.
• 08-Customer-Margin-Waterfall.xlsx: Detailed Waterfall + Top/Bottom customers + drilldowns.
• 09-Product-Profitability.xlsx: Product Profitability (SKU/Category) + margin/contribution.
• 10-Channel-PnL.xlsx: Channel profitability + fees/returns impact.
• 11-Exceptions-Data-Quality-Log.xlsx: Unlinked/missing data lines + Owner + Action.
• 12-GL-Tieout-Reconciliation.xlsx: Tie‑out for revenue and COGS (and depending on data: fees) with explanatory notes.
• 13-Assumptions-Memo.docx: Assumptions memo (Drivers/Allocation rules/Scope) + signature.
• 14-Runbook.pdf: Monthly/quarterly operational steps (Import → Allocate → Review → Tie‑out → Report pack).
• 15-Archiving-Map.docx: Archiving tree (Year/Month/Profitability/Inputs/Outputs/Tieout) + Naming convention.

After Implementation (Two Points Only)

• Operational Outcome for the Team: You will have a “cost-layered” profitability report that shows why a customer/channel/product is losing (excess discount? high fees? shipping/returns?) thus turning the discussion into action (repricing/modifying terms/minimum order/stopping offers).
• Control/Audit Outcome: Profitability results can be defended: clear data sources + documented allocation rules + reconciliation of Pool totals + Tie‑out for revenue/COGS with TB + Evidence index and Sign‑off.

FAQ — Questions Before Purchase

Is the model suitable for any ERP system?

Yes, as long as you can extract Sales invoice lines and COGS and (if available) fees/commissions and returns. The model works over any system via Exports.

Can profitability be done at the customer level only without product?

Yes. It can be run at the Customer/Channel level, but Product Profitability adds significant value if you have SKU/Category and COGS by product.

How is Cost to Serve calculated?

Through Cost Pools (shipping/fulfillment/returns/service/collections…) and identifying Drivers (number of shipments, weight, number of service tickets, number of returns, payment transactions…), then allocating them to customers/channels with documented rules.

Does it support separate analysis of discounts and fees?

Yes. The model separates discounts (Discounts) and fees (Fees/Commissions) from COGS so you can see the impact of each item on Contribution instead of aggregating them into one figure.

What is the minimum data required to get started?

Sales invoice lines (Gross/Net/Discount) + total COGS or by category/product. Having returns/fees/commissions improves the model but can be added gradually.

Does it produce numbers that can be linked to TB/GL?

Yes, via GL tie‑out for revenue and COGS (and depending on data availability: fees) with a record of variances (Timing/Mapping/Unposted) instead of ignoring them.

Is this an alternative to a BI tool or Dashboarding?

No. This is an auditable accounting/management model (Excel) with a Runbook and Tie‑out. The logic can later be transferred to BI after establishing the rules.

Is it suitable for e-commerce and multi-channel?

Yes, especially as it supports channel profitability and analysis of fees and discounts linked to the channel (Marketplace/Payment gateway/Delivery) if the data is available.

Ready to See Profitability “After Cost to Serve” and Link it to GL Numbers?

Outputs: Customer Profitability + Product Profitability + Cost to Serve + Channel P&L + GL Tie‑out Pack.