Current model and calculation rules

This page documents calculations in the existing reporting code so its outputs remain interpretable. It does not prescribe the MSP CRM exploration. See the implementation reference for source locations and access.

The report answers where invoiced contribution comes from, how much recorded labour each commercial service consumes, and why that contribution differs from the business profit or loss. It does not forecast revenue from sales pipeline probabilities.

Use this page for the business meaning and the business-result bridge for the calculation of each row in Bridge. The model reference documents the source tables, relationships, filters and every Power BI measure.

Reporting period and accounting basis

Monthly activity begins on 1 July 2026. Posted Xero invoices and credits follow document date, excluding GST; labour follows work date. Complete calendar months are selected initially. Partial months are explicit and do not imply the books are closed. Business P&L and staff reconciliation retain this monthly basis.

By service accumulates captured invoices and available work through the selected month for the July-onward cohort. It includes explicitly linked earlier invoices and work available from January 2025, without moving revenue between months. Earlier invoice history is selective; archived work, older credits and historical rates remain completeness limitations. Historical hours without a reviewed effective rate are shown as uncosted, and excluded from the known contribution subtotal. No complete lifetime margin percentage is asserted.

The detail separates original planned labour from actual people and work. Original expectations remain unconfirmed where intended staffing or a reviewed planning rate is missing. Current delivery status is shown with observation evidence separately from the financial cutoff. See the service performance process for baseline and closure review rules.

Commercial services

From the September 2026 release, the July/August capture uses the CRM Commercial Services identity and Service Invoice Lines attribution. The browser links services and actual lines back to CRM. Xero remains the financial authority: quantities, rates, discounts, description, tax basis, document date, currency and account code are compared on each refresh. Missing capture rows or captured rows absent from posted Xero documents stop refresh. A payment-status change alone does not change revenue. Later months retain the existing mapping rules below; this release does not migrate new invoices on a schedule. Disable config/commercial_services.json's enabled flag to return reporting to the legacy identity rules without deleting CRM evidence.

CRM service IDs replace legacy report IDs where the mapping is unique. Split legacy scopes are not assigned labour arbitrarily. Existing timesheet scopes still determine recorded work; presales/scoping adds labour cost without requiring revenue and each recorded time row is counted once.

Monthly billing shows observed component quantities and revenue across selected months, retaining original Products and lines. Reviewed component mappings can bridge Product replacements. A blank is absence of a matching billed row, not proof of a cancelled service or missing invoice. Seat validity remains governed by legacy MSP billing dates. The service detail combines monthly rows and accumulated selected-period totals.

The fallback identities for uncaptured months and unmatched work are:

Service	Revenue identity	Labour identity
MSP contract	CRM Invoice's MSP_Plan	Plan timesheet scope, then reviewed project fallback
Existing project or recurring Deal	CRM Invoice's Commercial_Deal, or reviewed reporting assignment	Delivery Model scope
Reporting-only service	Reviewed invoice/line assignment	Reviewed project assignment where established
Unassigned labour	No revenue invented	Original project, task, person and work date retained
Business-level labour	No contract allocation	Internal operations/strategy project

A reporting-only service is a report identity; it is not a new CRM Deal. No new Deals are created by this workflow. A generic revenue account category is a provisional service until its scope is reviewed. Same-customer identity alone is not evidence that two scopes belong together.

Reviewed assignments live in config/commercial_margin.json. Each carries a reason and a stable source ID. They can identify an existing Deal, a reporting service, an exact product, a contact alias or a project fallback. Contact aliases do not change CRM Company fields. A reviewed assignment that disagrees with a single CRM service link blocks refresh. An Invoice with both an MSP Plan and a Commercial Deal is retained under an unreviewed reporting category with the mapping basis Conflicting CRM attribution; neither link is chosen silently.

Non-MSP recurring Delivery Models are included. Recurring Delivery Models already represented by an MSP Plan are excluded from the same labour mapping. One-time onboarding/scoping models under the parent Deal remain separate from the MSP contract. More-specific CRM timesheet scopes take precedence over a reviewed fallback for otherwise-unmatched project time.

Service contribution

Posted invoice revenue, less revenue credit notes, ex GST
  - identified product costs
  - recorded hours × each person’s work-date cost rate
  = known contribution before missing and shared costs

Both billable and nonbillable recorded delivery time use the same person-specific rate. Employee rates use actual annual salary divided by contracted annual hours; configured contractors use their hourly estimate. Unmatched staff or dates use an explicit $80/hour fallback and require review. Unmatched time is retained and costed; it never becomes free simply because a service link is missing. Internal operations, administration and centrally recorded delivery remain at business level pending an agreed usage allocation. Recorded hours are not staff capacity, and the gap to payroll is not called idle time.

Product matching uses reviewed product IDs, exact CRM invoice-subform product links, exact MSP Invoice Line links, specific Xero item codes and unambiguous product labels. A generic Xero 003 code does not establish a licence product. CRM subforms identify products; Xero always supplies actual quantities and revenue, even if the CRM quantity is stale.

Licences and hardware without a reviewed Quote match use the current Product Cost_Price × Xero quantity. Reviewed Hardware uses the matched Quote row Cost_Price × invoiced quantity, regardless of Quote status. Generic Xero item 003 does not override that match. The detail view shows matched hardware stock margin separately from setup revenue; setup cost and margin are excluded. Home retains revenue only, with labour and full margin deferred. Recorded hours that would map to these deferred scopes remain in staffing and the business bridge outside client service contribution. These are current-cost management estimates, not historical purchase costs. Missing or zero cost prices remain unknown unless an explicit cost adjustment has been reviewed. There is no automatic 85%/90% revenue-cost fallback in this report. Setup/service lines outside the explicitly excluded Hardware scope use recorded labour. MSP service components whose cost belongs to shared delivery remain in the business cost pool. Hardware or external subscriptions posted to a service account still need product costing.

Sales credits reduce revenue in their credit-note month. The reviewed capture assigns credits using original-sale evidence. Outside that capture, a credit allocation can suggest a service when all allocated invoices agree, but the attribution is flagged for review. A sales credit does not prove a supplier cost refund: no product cost reversal is assumed. A reviewed cost adjustment can record the confirmed cost effect.

The report displays known contribution even when some costs are missing, with an explicit warning. A modelled contribution percentage is suppressed when product costs or invoice attribution are incomplete, or when revenue has no recorded labour in the current selection. Power BI checks selected totals, so recorded work in one service or month can mask missing work in another. A displayed percentage does not certify complete coverage. Missing time can still overstate any estimate. Open a service to inspect the invoice, product-match basis, actual quantity and labour detail.

Staff salary and time comparison

A private JSON file supplies explicit Zoho Projects user IDs, employment dates and dated salary or contractor-rate periods. The small display IDs in the Projects users screen are not the extracted API IDs. CRM user IDs can help verify identity, but costing uses the Projects user ID on each timelog. Name similarity alone never selects a salary.

Employee hourly cost = actual annual salary / (26 × fortnightly paid hours)
Recorded labour cost = recorded minutes / 60 × work-date hourly cost
Salary-only monthly budget = annual salary / 12
Nominal monthly paid hours = fortnightly paid hours × 26 / 12
Salary not attributed = salary-only budget − recorded employee salary cost
Hours gap = nominal paid hours − recorded salary-costed employee hours

An explicitly approved costing_annual_salary can replace the annual salary for the service hourly rate only. Its reason is recorded in the private file, and labour detail labels the rate as a costing assumption. The actual annual salary still determines the monthly budget and salary attributed to recorded time, so an assumption does not manufacture extra payroll or distort that gap. Recorded cost can therefore differ from salary attributed to time.

Annual salary is the actual amount paid, including for part-time employees; it is not prorated again. The first version excludes super and all loaded-cost uplifts. Chargeable percentages and revenue targets do not affect the rate. Monthly budgets/hours are prorated by calendar days for partial months, employment starts/ends and rate changes. They are monthly equivalents, not actual payslip paid hours or a working-day calendar. Money is rounded to cents per row, so totals may differ by a few cents from annual totals divided by 12.

The Staff cost reconciliation view includes configured employees even when they have no time recorded. Each person's recorded hours split into client, central and unassigned work. Contractor time uses its configured hourly rate; contractors have no salary budget or paid-hours gap. Missing rate coverage is retained at $80/hour with a review flag, never at zero cost.

Positive salary/hour gaps mean the budget is not explained by recorded work; negative gaps mean recorded work exceeds the monthly equivalent. Neither is proof of idle time or overtime. Leave, missing timesheets and differences in calendar working days can explain them. The unallocated salary is a diagnostic figure, not another expense added to the business bridge.

The whole-business comparison is Xero payroll minus salary-only budget. It can include super, posting timing, staff coverage and payroll adjustments. Contractor P&L expenses are shown separately. This does not reconcile individual payslips: the source report has no employee payroll transactions. Actual Xero expenses replace all recorded labour estimates in the bridge, so changing a staff rate cannot change the reported Xero profit.

See private staff-cost maintenance for the JSON format, updating salaries and server setup. Keep salaries out of CRM, the repository and the image; report access must remain restricted to people authorised to see staff costs.

Labour capacity

The MSP Plan's expected monthly support hours are imported as service metadata. They are not used in the calculation below.

Affordable labour hours = (invoiced revenue - identified product cost) / $80
Labour headroom = affordable hours - recorded hours

This remains a $80/hour comparison scenario, independent of the configured staff rates used for contribution. It measures affordable hours before shared infrastructure, overhead and profit. It is not a staffing recommendation or a profitable capacity target. Capacity is withheld when product costs are incomplete. Monthly averages divide the selected totals by the selected calendar months; they are billed averages, not contractual recurring revenue or annualised run rates. Inspect billing timing before using them for restructuring decisions.

Business profit or loss

The business view uses Xero's standard-layout accrual Profit and Loss report for each calendar month (standard_layout=True, payments_only=False). The partial month ends on the snapshot end date. It reads account rows, excluding section subtotals, and uses Xero's Net Profit row as the control total.

Business-result bridge

Bridge contains 11 rows per month. They are calculated by the backend before Power BI imports them; they are not 11 independent DAX measures.

Column	Meaning
month_start	First day of the reporting month, not the date an expense was posted.
step	The calculation described below.
step_order	Display order, from 0 to 10. step sorts by this column.
profit_effect	Signed AUD effect on the bridge: positive increases the running result; negative reduces it. It is not a cumulative balance.

All sums below are for one month across the whole business. Client/service slicers do not change these rows. Let R be all included invoice revenue net of sales credits, P all identified product costs, L all recorded labour cost at the configured work-date rates, C central labour cost, and U unassigned labour cost.

Order	Exact step label	Calculation of profit_effect	Source and interpretation
0	Known client service contribution	Sum of all Contribution[known_contribution] + C + U	Equivalent to R − P − (L − C − U). Starts before central/unassigned labour; missing product costs and missing time can overstate this figure. It includes reporting-only and unreviewed service revenue.
1	Central operations labour	−C	Sum Contribution[labour_cost] for services whose service_type is Business-level labour, with the sign reversed. Classification comes from reviewed project mappings, not a percentage of client revenue.
2	Unassigned labour	−U	Sum Contribution[labour_cost] for Unassigned labour services, with the sign reversed. This time has no resolved CRM scope or reviewed project assignment.
3	Add back estimated product costs	+P	Sum Contribution[product_cost] across every service. Removes the product estimate already deducted in step 0. This is not a refund or additional income.
4	Add back estimated labour costs	+L	Sum Contribution[labour_cost] across every service, including client, central and unassigned labour. Removes all salary-derived, contractor and fallback costs deducted in steps 0–2.
5	Other revenue and accounting adjustments	Business[xero_revenue] − R	Xero P&L Revenue plus Other income, less included invoice revenue. A reconciliation difference, not an invented balancing journal or a list of individually identified adjustments.
6	Supplier and other direct costs	Negative sum of Accounts[amount] in this cost group	Remaining Xero Cost of Sales accounts after the explicit payroll, contractor and shared-cost rules below. These are actual accounting costs, not CRM Product estimates.
7	Payroll	Negative sum of Accounts[amount] in Payroll	Actual P&L accounts selected by payroll_account_codes. Replaces the labour estimate at business level; salary-derived service costs are reversed before charging this actual business expense.
8	Contractors	Negative sum of Accounts[amount] in Contractors	Actual P&L accounts selected by contractor_account_codes. The source is accounting postings, not an inferred contractor timesheet rate.
9	Shared infrastructure and tools	Negative sum of Accounts[amount] in this cost group	Actual P&L accounts selected by shared_infrastructure_account_codes. Kept at business level pending an agreed usage allocation.
10	Other overhead	Negative sum of Accounts[amount] in Other overhead	Remaining Xero Expenses accounts after the explicit rules below.

The arithmetic explains why costs are added back:

After steps 0–2: R − P − L
After steps 3–4: R
After step 5:   Xero P&L revenue + other income
After steps 6–10: Xero P&L income − actual P&L expenses = business net profit/loss

The estimated product and labour costs cancel in the business result. Improving a service's product match or labour attribution can change its contribution without changing Xero net profit. Actual payroll is charged once, not added on top of recorded labour estimates. A negative expense posting reverses the usual sign and therefore increases profit in the bridge.

How Xero accounts enter cost groups

Accounts retains account_id, account_code, account_name, cost_group, amount and profit_effect for each month. amount is the signed amount shown in Xero's P&L; normal income and expense rows are both positive there. profit_effect keeps income's sign and reverses expenses' sign.

Classification uses the P&L section first, then exact account-code matches in the deployed configuration. It does not infer a group from an account name. Rules run in this order; the first matching rule wins:

Priority	Rule	Group / checked-in configuration
1	P&L section contains Income	Revenue, or Other income when the section also contains Other. These feed Business[xero_revenue].
2	Expense code appears in payroll_account_codes	Payroll: 308, 401, 478, 485.
3	Expense code appears in contractor_account_codes	Contractors: 301.3, 302.3, 438.
4	Expense code appears in shared_infrastructure_account_codes	Shared infrastructure and tools: 417, 472, 480, 486.
5	Remaining section contains Cost of Sales	Supplier and other direct costs.
6	Remaining section contains Expenses	Other overhead.

These lists live in config/commercial_margin.json. The server can load another file through COMMERCIAL_MARGIN_CONFIG; inspect that file when auditing a deployed result. Reclassifying an account between expense groups changes the breakdown, not total profit. It does not allocate that cost to a contract. An unrecognised P&L section blocks the snapshot rather than being omitted.

Tracing a bridge amount

1. Select one month so the accounting period is clear.
2. For steps 0–4, inspect Contribution, joined to Services on service_id. Filter service_type for central or unassigned work. Use Invoices and Labour for the underlying document lines and project/task/person/date rows.
3. For step 5, compare Business[xero_revenue], invoice_revenue and revenue_difference; inspect income rows in Accounts and included revenue in Invoices. The report exposes the net difference, but does not yet link each journal or adjustment behind it. Invoice lines posted outside a revenue account retain their document_amount, with zero included revenue.
4. For steps 6–10, filter Accounts[cost_group] to the exact group and total amount; reverse its sign to reproduce the bridge row. Use the account codes and the same monthly accrual P&L in Xero for supporting transactions. Supplier-bill and payroll transaction detail is not imported into this model.
5. Sum all 11 profit_effect values and compare with Business[net_profit]. Both the account-to-P&L check and bridge-to-P&L check reject a discrepancy greater than $0.02 per month before publishing the snapshot. Monetary output is rounded to cents; labour calculations retain fractional hours.

The implementation is in app/commercial_margin/engine.py (build_contribution_snapshot, business/bridge section) and app/commercial_margin/finance.py (pnl_accounts). Revenue differences and unallocated supplier costs remain visible; the model does not invent a contract allocation to eliminate them. Foreign-currency sales block refresh until an AUD conversion basis is implemented.

Reconciliation and CRM writes

The dashboard itself is read-only. The separate reviewed reconciliation command may create a CRM Invoice or link an existing unassigned Invoice to an existing Deal. It preserves Xero dates, quantities, prices, discounts and tax, disables CRM workflow triggers, and verifies the persisted service link. It refuses conflicting attribution, missing required products, changed source invoices and pre-July invoices. Reporting-only services never create CRM Deals. Xero is never modified, approved, sent or otherwise written by this workflow.

See Project Invoice Reconciliation for the operating process.

Refresh and artifacts

• Browser dashboard: /commercial-margin/dashboard using existing report Basic auth.
• Shared JSON source: /commercial-margin/contribution-report.
• Power BI project: pbi/commercial-margin/CommercialMargin.pbip.
• Calculation and read-only source collection: app/commercial_margin/.

Refresh builds a complete snapshot in the background under a cross-worker lock, avoiding long-running web-request timeouts. Browser and Power BI poll the 202 response while it builds. Completed snapshots are cached for 15 minutes; failed refreshes return an error rather than a partial or zero-filled result. An optional local snapshot can be used for Power BI review before deployment. The old CSV endpoints remain available for existing consumers but are not sources for the replacement dashboard.