Calculation Rules

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

The browser forecasts revenue and contribution from confirmed, mapped commercial services. One-off projects cover the whole contract; recurring services cover the current financial year. Recorded invoice/work activity has its own Performance tab and service overview. The separate Power BI business report explains invoiced contribution and the accounting result. Neither view weights unsigned 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

The browser review shows services marked Confirmed in CRM Review_State for the snapshot period beginning 1 July 2026, including the current partial month. Supporting historical revenue is issued Xero revenue net of credits, excluding GST, by document date. Commercial services opens the selected-period service overview. Performance presents monthly actual contribution and delivery reserves. Forecast and each service Summary show configured revenue, delivery cost, contribution and margin, with recurring FY and whole-contract horizons distinguished. Recorded activity dates and the forecast salary-allocation switch have separate scopes. Service Role on the Commercial Service supplies the browser category; it does not change Product or line costing. See pages and filters.

Confirmed services can combine a Consulting Product (003) line with purchased Products, linked through Commercial_Service, independently of Plan presence. Explicit Labour Budget Type uses Expected Labour Hours; legacy hourly lines with blank type use Quantity as their planned hours. A fixed work budget is not multiplied by months or inferred from the actual invoice quantity or selling price. Ten days of 7.6 hours are entered as 76 hours. The full budget is shown alongside issued revenue and recorded work for the displayed reporting period. The headline forecast uses configured budgets without requiring pre-history delivery costs.

Future signed Consulting work retains its whole-contract forecast budget before its start date; its delivery reserve stays zero until that date. The one-off Consulting budget remains included when Active is unchecked; that billing flag does not erase agreed project effort. Multiple Consulting lines keep their configured forecast budgets; recorded activity still requires an explicit work allocation.

The browser review uses these expected and incurred cost rules:

Product	Expected cost	Actual cost
Seat	Configured seats × monthly Seat level hours × period fraction × $50/hour	Recorded service hours × the standard $50/hour estimate
One-off Consulting (003)	Expected Labour Hours, or legacy hourly Quantity, × labour tier rate; explicit pending hours leave expected cost unknown	Recorded service hours × the line's labour tier rate, with blank selecting Tier 1; staff costs are ignored
Recurring internal Consulting (003)	Configured monthly hours × applicable month fraction × labour tier rate	Same recorded-work rule as one-off Consulting
External Consulting (CONSULTING-EXTERNAL)	Planned hours × explicit supplier hourly rate	All recorded hours × supplier hourly rate, including overruns
Discount (MSP-DISCOUNT, MSP-NFP-DISCOUNT)	Zero: price adjustment only	Zero: price adjustment only
Other Product, cost price present	Signed billed quantity × current Product cost price	Same as expected
Other Product, cost price missing	90% of signed net billed revenue	Same as expected

An explicit zero cost price stays zero. Blank non-labour Product prices use the fallback even for infrastructure, devices or Products classified as labour/service. Product credits retain their signed cost effect. The two explicit discount Product codes reduce revenue without creating costs or reversing labour, even when their Product cost price is blank or populated. This classification uses the Product code, not a negative amount or the word "discount" in a description. A configured non-labour component with no billed activity has zero cost in this billed-margin comparison; this is not a future purchasing forecast.

A service without labour Products can use the same Product-cost rule when it has configured lines, every configured/billed Product is classified as Purchased product/licence or is an explicit discount Product, and the scope has no recorded labour. Consulting and Seat Products cannot enter this path. Unknown classifications or recorded work leave the service unmodelled until its delivery allocation is resolved. No labour coverage warning is raised for a purchased-only sale. Expected and actual purchase costs use the same signed billed quantities and current Product prices.

For ARACY, one service contains a laptop line and a setup labour line. Laptop quantity two at AUD 2,499, with current Product cost AUD 2,347, produces AUD 304 contribution on AUD 4,998 revenue. Setup adds AUD 599 revenue; its labour budget is three hours at Tier 1 ($50/hour). The configured forecast shows AUD 5,597 revenue and AUD 903 contribution, or AUD 753 after the optional staff allocation.

Consulting lines can select Labour_Budget_Type and supply a separate Expected_Labour_Hours. Fixed budgets use total hours; recurring allowances use monthly hours and override service Arrangement. Selling Quantity and price are independent: one AUD 599 fee does not imply one hour. A fixed line with an explicit type and blank hours has pending expected/projected cost and margin. Recorded incurred costs remain available when work exists; with no budget or recorded work, labour cost stays unknown. Unknown costs propagate into service and monthly totals without obscuring known purchased components. Legacy lines with blank type retain their quantity/Arrangement convention.

Signed-work financial forecast

The dashboard renders the existing Commercial Service and service lines. It has no forecast input form and does not use local revenue overrides, opening balances or remaining-work estimates. Its service-line table makes each total traceable.

Revenue is configured Quantity × Unit Price Override, falling back to Product Unit Price. Invoice totals and invoice timing do not change this expectation. One-off services cover the whole configured sale. Recurring services cover the Australian financial year containing the report cutoff, currently 1 July 2026– 30 June 2027. Current monthly quantities and prices are prorated by inclusive calendar days and service/line dates. This projects current configuration; it does not reconstruct previous pricing or add unsigned sales. Monthly recurrence is the supported convention.

• Purchases: configured quantities × Product cost. Missing Product cost keeps the existing, labelled 90% of selling value estimate. Explicit zero stays zero; discount Products have no cost. Invoice-only items belong to supporting activity and do not change the configured forecast.
• External Consulting: configured budget hours × the service line's positive External_Hourly_Cost. A recorded overrun increases the fee. Recurring work retains each month's allowance, including months without recorded work; overruns are additional and do not consume future allowances. Recorded hours can be assigned automatically only when the service has one labour line. Multiple labour lines retain their separate configured budgets without guessing the ownership of recorded work. Missing work history never blocks the budget forecast.
• Salaried delivery: configured Consulting hours × the existing tier rate; Seat quantities × level hours × $50/hour. These are optional delivery allocations, not the fixed payroll bill. Sold quantities and explicit labour hours are separate.

Include estimated salaried delivery cost defaults on and persists in the URL. Off: contribution = revenue − purchases − external fees. On: also subtract the salaried allocation. External fees and revenue stay the same. Fixed payroll, overheads and Xero reconciliation are outside this forecast.

Missing configured prices, rates or hours remain visible as gaps for the affected cost type. Missing salary allocation does not hide known direct contribution. Portfolio percentages use total contribution divided by total revenue. The portfolio covers confirmed, mapped services; this does not establish that every signed deal has been captured. Each row identifies whole-contract or FY scope.

Older saved report catalogues omitted selling fields that their raw CRM collection already contained. Rendering restores the catalogue from that same saved collection, including while a source refresh is pending. CRM changes still require source refresh.

Implementation: app/commercial_margin/financial_forecast.py, commercial_services/labour_products.py; tests: tests/test_financial_forecast.py, tests/js/commercial_margin_dashboard.cjs.

Monthly service activity

Each service exposes monthly activity for every calendar month intersecting the snapshot period. Invoice and credit revenue and their billed Product costs use document date; labour uses work date and the same costing rule as the service total. Cost-only and empty months remain present, and a truncated first or last month is labelled partial. The report does not spread invoice amounts by service coverage or reconstruct historical Product prices.

Monthly rows contain revenue, recorded hours, labour cost, other cost, incurred cost, actual contribution, reserve movement and contribution after reserve. Each row also has its Product components and included invoice items. Configured-charge ownership takes precedence over historical Product identity, as in the service total. Selecting a month filters these details locally without a source refresh. Native invoice references link to the CRM Invoice when available.

Reviewed historical invoice items can use an explicit reporting-only link to their current configured service line. The link is limited to the exact invoice and item, verifies service ownership and both Product identities, and rejects a conflicting existing charge link. It is applied before costing in the snapshot position and monthly activity. An old support Product linked to a Seat line contributes revenue to that Seat component; it does not also receive a purchased Product cost estimate. The configured seat budget and recorded labour allocation are counted once. Source Product names, billed quantities, prices and dates remain unchanged in invoice evidence. These links do not edit CRM or Xero.

Line budget positions display the configured service-line name with the Product underneath when exactly one configured line owns the Product component. Multiple lines sharing a Product retain the Product heading and their existing combined budget. Implementation: app/commercial_margin/review_attribution.py, config/commercial_services.json (invoice_charge_assignments); verification: tests/test_review_charge_attribution.py.

The dashboard and every service detail offer a cumulative view from the selected range start. A selected month includes activity from that start through the month. The default range is financial YTD through the latest complete month available, with a labelled partial-month fallback when no full month exists. Custom ranges and single months use saved monthly rows; YTD presets require history covering the beginning of the year. The percentage divides cumulative contribution after reserve by cumulative revenue; it does not average monthly percentages. The month-on-month comparison uses the latest two complete months in the selected range and shows the change in contribution after reserve and the percentage-point change in rate. If either month has incomplete labour costs, the comparison flags it and omits the percentage-point change. Zero revenue has no margin percentage.

Recurring expected hours and costs use current configured quantities and dated charge proration. Monthly Seat work uses the same report-period allowance weights as its service's Product components. Multiple configured lines for one Product remain grouped together; this is not separate work attribution to each line. Rounded labour totals are allocated across components by largest remainder, and monthly display rounding preserves each component's period total. Equal remainders go to the later row. This keeps both aggregation directions consistent without creating negative labour from a rounding residual.

One-off Consulting has incurred and cumulative monthly costs, with its budget position shown separately at the report cutoff. The fixed budget and remaining hours are not recomputed when the user selects a month. Monthly expected labour cost is unavailable for that fixed scope; the component table points to its scope budget. Recorded work after closeout remains in its work month through the report cutoff. Month-end reserves and their movements are derived from dated work, opening balances and completion, then carried into the selected range.

Unknown labour cost stays unknown in the affected month and in cumulative totals that include it; a later complete month can retain its own known costs. A month without recorded work can legitimately have invoiced revenue. Zero recorded hours alone does not raise a coverage warning or suppress a margin percentage. When all incurred costs are zero and revenue is positive, the monthly activity contribution before provision is 100%. The headline for Project, Onboarding and Recurring agreement services also includes delivery provisions or a labelled planned estimate. Missing costs still leave complete margins unavailable. Unmodelled services retain monthly revenue and hours with unavailable costs, rather than zero-cost margins.

Recurring Consulting allowance

A Consulting Product 003 line with Labour_Budget_Type = Recurring allowance uses Expected Labour Hours as hours per calendar month. A legacy line with blank type uses Quantity when the service Arrangement is Recurring. For example, 23 hours at Tier 2 ($80/hour) gives $1,840 expected labour cost per full month. Invoice quantities and selling prices do not set these hours. This monthly convention is specific to the single-line reporting pilot; other allowance intervals and mixed labour components require explicit line rules.

Prorate expected hours and cost by inclusive calendar days within the report and line start/end dates. Lifecycle = Ended with a service End_Date also limits the allowance. The line's Active billing flag does not erase agreed Consulting hours. Recorded work remains in its work month, including work after the allowance ends. Actual costs use recorded hours at the line's tier rate; a blank tier selects Tier 1. Staff rates and captured labour costs are ignored. Missing monthly quantity leaves expected hours and cost unavailable while actual costing continues; explicit zero stays zero.

For Project, Onboarding and Recurring agreement roles, undelivered monthly hours carry forward within the available report history. Accrue each full applicable monthly budget, including an open month, and consume owed hours as work is delivered. A line end stops new accrual but retains hours still owed. Dated service closeout releases unused hours. An overrun is charged immediately and does not preconsume a future allowance. Missing hours keep reserves unavailable, including at month-end. A partial history beginning mid-month cannot invent an opening balance. These are obligations within saved history, not a reconstruction before July 2026.

A reviewed private allowance_policy = expire_monthly explicitly selects monthly expiry for an agreement whose unused hours expire. Other roles, including MSP, retain monthly expiry: closed months have no reserve; an open month holds its remaining full-month budget until month/line end or dated completion. Expected hours in the activity components remain prorated period comparisons; the carried remaining-hours balance is shown separately. External-fee forecasts also consume carried hours before identifying overruns. Zero work is not a cost-coverage gap, but it does not remove an owed delivery provision.

One-off labour budget and margin

An explicit Fixed budget Consulting line, or a legacy one-off hourly line, uses a fixed budget when planned hours are known. While the work is open and recorded hours are at or below that budget, the line margin position holds the full expected labour cost. Remaining hours are max(budget hours − recorded hours, 0). Once recorded hours exceed the budget, the baseline reserve is zero; a dated remaining-work estimate reserves any further expected delivery. A completed service also uses incurred labour cost and releases unused hours. Other Product costs are added once under the rules above.

For 76 hours at Tier 2 ($80/hour), the budget is $6,080. At 45.6 recorded hours while open, 30.4 hours remain and the line margin position still uses $6,080. At 83.6 recorded hours it uses $6,688 at Tier 2. Completing at 45.6 hours releases 30.4 unused hours and uses $3,648. Both planned and recorded hours use the line tier rate; worker-rate differences do not affect these dashboard costs.

The pilot's explicit closeout convention is Commercial Service Lifecycle = Ended with End Date on or before the report cutoff. An ended service without a date retains its budget and displays an instruction to set that date. A future end date, an end date on an active service, Cancelled, line Active, and Review_State do not release budget. Reopening the service restores the open budget rule. Linked Zoho Project completion is not read for this calculation.

The supporting delivery-reserve calculation combines captured work with any saved opening costs. This does not set the headline service-line forecast. Work recorded after closeout remains included through the report cutoff. The full-business contribution calculation keeps using incurred costs. A line selecting Recurring allowance uses the monthly allowance instead. Arrangement selects that convention only when Labour Budget Type is blank.

Delivery reserve and service forecast

For Project, Onboarding and Recurring agreement Service Roles, the browser's Actual contribution includes delivery still owed. Incurred facts remain available separately:

• Contribution before provision = issued revenue net of credits − incurred costs.
• Reserve movement = closing delivery reserve − opening delivery reserve.
• Headline contribution = revenue − incurred costs − reserve movement.

The list's delivery cost includes that movement, with incurred costs and the adjustment identified below it. Overview totals, sorting, Performance trends, monthly/cumulative tables and Activity use the same headline basis. Other roles retain incurred contribution. Forecast's salary toggle does not change activity.

If an open fixed project has incomplete opening history but a complete configured plan, the headline uses Planned margin · delivery position incomplete. The configured whole-contract cost/revenue ratio is applied to cumulative recorded revenue, floored at zero revenue; known cumulative incurred costs are the minimum cost. Monthly changes in that cost produce additive period figures. This estimates the planned margin on the billed revenue, without inserting a whole-contract margin beside period revenue or inventing opening hours/reserves. The adjustment absorbs later recorded costs until that allowance is exhausted; known overruns reduce contribution. Missing incurred costs, missing selling or cost budgets, or completed projects with unknown opening history remain unavailable. The opening-position warning stays visible on planned estimates.

An increase is a cost; a decrease releases cost already reserved. Period totals sum movements, while opening and closing reserves remain boundary balances. Selecting August or YTD carries the reserve from before that selection. A fixed budget is charged once, when it enters the available history, rather than in every month. A project already underway needs an opening balance; its original budget is not charged again in July. An unknown opening makes the affected movement and adjusted margin unavailable. A labelled planned estimate may supply the headline as described above; incurred figures remain available separately.

The supporting cost-to-complete fields retain the existing calculation:

Cost to complete = actual cost, including saved opening costs + closing reserve

Saved legacy management inputs can still affect this reserve calculation. A dated remaining-work estimate is consumed by later recorded work and billed purchases; an open fixed project retains at least its unused original budget. Dated service completion releases unused reserves, while later recorded costs remain included. The retained management endpoint writes only private local records and rejects concurrent edits. The dashboard no longer presents that input form or uses these values to determine its headline forecast. Missing opening balances affect historical reserve movements, not the configured revenue or expected margin.

The reserve is a recalculated management view, not an immutable period-close ledger or statutory provision. Reporting posts no accounting entries.

Labour allowances and rates

Monthly Seat labour expectations are L1: 15 minutes (0.25 hours), L2: 30 minutes (0.5 hours), L3: 1.5 hours per seat per month. L3 support is unlimited; its 1.5 hours is an indicative expectation for planning and reporting, not a support limit. Recorded work is included even when it exceeds that expectation. Charge start/end dates constrain the budget, with inclusive calendar-day proration in each month. Inactive charges without an end date do not add budget. Confirmed services remain readable before the first charge starts or after the last allowance ends. Their configured Seat identity still costs recorded work at the MSP rate; dates outside the allowance contribute no expected hours. Current configured quantities are applied over that scope; this is a current configuration scenario, not a reconstruction of historical seat-count changes. The old fixed Plan support hours and separate website labour budget are not used.

MSP expected labour uses $50/hour for every Seat line. Its labour tier has no effect, including when the field contains an unrecognised value. The Seat L1/L2/L3 level determines hours independently of the Consulting labour tier.

Internal Consulting uses the shared service line's optional Expected_Labour_Tier picklist. The code mapping in commercial_services/labour_tiers.py sets AUD hourly costs: Tier 1: $50, Tier 2: $80, Tier 3: $95, Tier 4: $110. Blank or -None- uses Tier 1; other unrecognised values fail cost validation. CRM stores the tier only, and the legacy currency field is not read. Selling prices and Product purchase costs are separate inputs. Editing the tier takes effect after source refresh; editing the code mapping affects recalculated periods, including historical periods. Dated tier costs are not retained.

MSP Seat labour uses the standard $50/hour estimate for both planned and recorded work. Private worker rates, captured labour costs, missing staff-rate coverage and Consulting tiers do not change this estimate. The Activity detail labels this basis explicitly. Internal Consulting uses its line tier for both expected and actual costs: planned hours × tier rate and recorded hours × the same tier rate. Resolved staff rates, captured labour costs and staff fallback flags do not affect Consulting costs. Tier pricing is the normal basis and does not generate a fallback warning. With no recorded work, actual labour is zero. Changes to the staff mix do not change Consulting costs. External Consulting uses its explicit supplier hourly rate for both planned and recorded work, regardless of tier or staff rate. Recorded MSP hours remain costed at $50 even when the source snapshot has no staff cost for them. These costs remain estimates, not supplier-bill or payroll reconciliation.

For MSP, all labour attributed to the service is counted once against Seat. If there are multiple Seat tiers, actual work is split by their expected-hour weights (equally when all weights are zero), with rounding reconciled to the service total. The native item's configured-charge lookup takes precedence over its historical Product identity. A historical internal Consulting item follows the sole External Consulting line in its already-attributed service; services with mixed labour lines require an explicit charge link. Other items use the resolved invoice Product. Missing Product metadata, a missing Seat, or a charge linked to another Plan prevents a misleading complete report. Billing configuration takes precedence over agreement text. No per-customer cost exceptions or CRM changes are required to apply these rules. For a service with one Consulting line, its recorded work belongs to that line once. Consulting Product purchase cost is not added to labour. Other invoice Products retain the billed Product-cost rules; credits and discounts reduce revenue without changing the planned hours. Multiple Consulting lines or mixed labour components need explicit work allocation and are not guessed by Product name, selling rate or expected-hour weights. Confirmation controls inclusion independently of Active/Ended lifecycle. The initial confirmed services are Success Works and Sharps; names and IDs are read from CRM. Source refresh validates confirmation before expensive collection. Saved-data recalculation retains the last fetched scope and source timestamp. See refresh controls.

The review has a separate persistent cache and selects complete customer documents by service captures, linked MSP invoices, customer identity and reviewed assignments. Shared documents retain every line and their original subtotal checks. This browser dashboard is a draft proof of concept for modelling and experimentation. Changed archived fingerprints are warnings using current Xero amounts; missing captures for selected services, ownership conflicts and invalid totals still block refresh. Unrelated customers' history does not block it.

The retained contribution API starts on 1 July 2026. Posted Xero invoices and credits follow document date, excluding GST; labour follows work date. Partial months are explicit and do not imply the books are closed. Business P&L and staff reconciliation retain this monthly basis.

The exploratory cumulative historical-position and original-baseline extension is retired from live collection. Its unreviewed terms, budgets and selective pre-July evidence remain archived. Live outputs do not assert lifetime margin or an approved original project budget. The hourly Consulting pilot stores its current expectation on the shared line, as described in the commercial service design.

Commercial services

Commercial Services supplies the service identity. Existing MSP Invoice Lines are shared configured lines with optional Plan and Commercial Service relationships. Native Invoices have a Commercial Service header lookup; conflicts with item, Plan, Deal or reviewed source ownership stop refresh. A header link is used only for a single-service invoice.

New invoices supply attribution directly from fully linked native Invoiced Items, checked against Xero and their configured service lines. They do not need an archived capture or a migration-list entry. Every issued line must appear exactly once, and the native invoice dates, tax and totals must reconcile to Xero. Unknown delivery coverage can remain blank; revenue follows the invoice date.

The six migrated Success Works and Sharps invoices also supply attribution from native Invoiced Items, retaining required reviewed coverage. Other historical invoice/credit ownership, Products, Quote-row cost references and coverage are preserved in config/commercial_service_attribution.json. This immutable migration bridge contains source fingerprints instead of copied financial values. Source quantities, rates, discounts, descriptions, tax basis, date, currency and account code must match for the full-business contribution export. The draft browser allows a changed fingerprint on the same document/line identity, uses current Xero values and retains a warning. Missing posted source rows and mismatched customer ownership still block both paths. A payment status change alone does not change revenue. Archived mappings open the Xero source rather than a deleted custom line.

The collector does not read Service Invoice Lines, Service Terms, Service Labour Budgets or Service Source Links. Reviewed historical Deal, billing-alias and work-date ownership is retained in the bridge so July-onward attribution remains available. Legacy work IDs there are migration keys, not fields on new service lines. New invoice and line relationships belong in retained CRM modules; automatic rolling capture and new Xero invoice generation are not enabled.

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.

Draft dashboard totals with incomplete costs

Commercial services summary cards and Performance totals sum known monthly amounts even when another service or month has unknown costs. Recorded revenue is retained. Cost and contribution headlines are labelled as partial estimates: unknown costs are excluded, so contribution may be overstated. Margin percentages remain withheld where costs are incomplete. A metric with no known amounts stays unavailable; opening/closing reserves and reserve movement keep their completeness checks. Service rows and detail views retain their unavailable values and review flags. Monthly, cumulative and comparison views use the same draft aggregation. This does not change source records, costing assumptions or forecast totals.