Skip to content

Margin Calculation Approach

Purpose

The commercial margin report answers one operational question:

For each client and month, what margin did we expect, what margin did we invoice, and how reliable is the comparison?

The first usable version is deliberately approximate. It reuses current CRM, Projects, and Xero records and makes every shortcut visible. It does not wait for perfect product matching, person-specific labour rates, or accounting revenue recognition.

Reporting shape

Client-month is the primary view. Commercial services sit below it so users can drill into the work that produced the result.

Commercial service Stable monthly key Expected revenue Actual revenue
Managed service MSP|{plan_id}|{YYYY-MM} Applicable MSP Invoice Lines Posted Xero sales invoice lines linked through the CRM invoice
Deal or project DEAL|{deal_id}|{YYYY-MM} Probability-weighted CRM Deal amount in its closing month Posted Xero sales invoice lines linked through the CRM invoice
Revenue without a service link CLIENT|{company_id}|UNALLOCATED|{YYYY-MM} None Posted Xero sales invoice lines mapped only to the client

A Deal remains useful sales context. A Managed Services Plan remains the contract identity for recurring service. Neither needs to become a new generic CRM Commercial Unit record for the report to work.

Source ownership

  • Xero is the source of actual invoiced revenue. The MVP reads posted ACCREC invoices in AUTHORISED or PAID status and emits one report row per invoice line.
  • CRM Account is the client identity. Xero_Contact_ID links the Xero contact back to the client.
  • CRM Invoice links a Xero invoice to an MSP Plan or Deal when that link is available.
  • CRM Product supplies Product_Category and Cost_Price when the Xero item code matches Product_Code.
  • MSP Invoice Lines supply expected recurring revenue at Plan-month-line grain.
  • Delivery Models supply expected labour hours. Project timelogs supply actual labour hours.

The report is read-only. It does not mirror or create invoices in CRM, update Products, or change MSP Plans.

Margin calculation

expected direct cost = expected product cost + expected labour cost
actual direct cost   = estimated actual product cost + actual labour cost

gross profit = revenue excluding GST - direct cost
gross margin = gross profit / revenue excluding GST

Expected Deal revenue uses the following provisional weight:

  • won: 100%;
  • lost: 0%;
  • open: CRM Probability divided by 100.

Deal Closing_Date is the provisional month. This is a forecast placement shortcut, not multi-month revenue recognition.

Expected Delivery Model hours and actual timelog hours use one editable BlendedHourlyCost parameter in the MVP. Person-specific effective rates can replace that shortcut later without changing the client/service/month shape.

Product-cost resolution

Product cost is resolved in this order:

  1. matching CRM Product Cost_Price for a Hardware or Licence line;
  2. Hardware estimate at 85% of revenue excluding GST;
  3. Licence estimate at 90% of revenue excluding GST;
  4. zero product cost for Services and Managed Services because labour is costed separately;
  5. zero-filled unknown cost, visibly marked Incomplete.

The Xero revenue account provides the day-one category fallback:

Xero account MVP margin category
200 Hardware Sales Hardware
211 Software Licenses Licence
201, 202, 202.1, 202.4, 203 Services
202.5, 202.6, 202.7 Managed Services
220 Sales Discounts - Not For Profit Discount

This is necessary because current Xero sales lines predominantly use generic item code 003, which does not identify a CRM Product.

Reliability labels

Label Meaning
Reconciled Client and commercial service are linked, and the invoice line maps to a CRM Product.
Estimated The service is linked, but category or cost uses an account-code or blended-rate shortcut.
Unallocated The Xero invoice maps to the client but not to a Deal or MSP Plan.
Incomplete The client or required category/cost identity is missing.

Margins remain visible for Estimated, Unallocated, and Incomplete rows so the report is useful immediately. Quality cards and the reconciliation page show the exposure that should not yet be treated as measured margin.

CRM structure

No new CRM module or blocking field migration is required for the MVP. The existing foundational fields are sufficient:

  • Account Xero_Contact_ID;
  • Invoice Xero_Invoice_ID, MSP_Plan, and Deal_Name__s;
  • Product Product_Code, Product_Category, Cost_Price, and Xero_Account_Code.

The first operational improvement is data completion, not schema creation: maintain Account Xero IDs, retain invoice-to-Plan/Deal links, and use meaningful Xero item codes for new product-led lines where practical.

MVP boundary

The first Power BI version excludes supplier bills, Xero credit notes, line-level historical purchase costs, accrual revenue recognition, person-specific labour rates, and automatic CRM invoice mirroring. Those are deliberate follow-on improvements. The report should first demonstrate where better linkage or costing materially changes the result.

The runnable project and its operating guide are in the repository at pbi/commercial-margin/README.md.