Skip to content

Reporting Assumptions

Last updated: 2026-09-05.

This page captures process-relevant reporting assumptions. These are not mutating business workflows, but they affect how process owners should interpret Power BI and exported report data.

Deliberate Limits

The assumptions below describe implemented reporting. The separate commercial services design direction records agreed foundations and unresolved design choices for service ownership of invoice lines and monthly expectations and actuals. It identifies future impacts on Deals, Timesheets and commercial contribution without changing the current attribution or calculation rules documented here.

CRM Overview Model

Current implemented assumptions:

  • CRM contacts, companies, deals, MSP plans, MSP seats, MSP plan history, and MSP seat history are loaded from middleware report endpoints.
  • Company is the primary slicer path for contacts, deals, and MSP plans.
  • MSP Seat visuals use denormalized contact details such as contact email and lead status rather than an active Power BI relationship from Contacts to MSP Seats.
  • Current MSP plan and current MSP seat exports are active-only backend exports. For current MSP active-seat counts, Billing_Status values Active and Onboarding are both included when the seat billing dates are in range and the Account has an active Service with MSP Seats Enabled and in-range billing dates. Seat exports include Account identity. Compatibility Service columns are derived only when one eligible Service is unambiguous. Each Account seat is counted once in company totals and seat history, even when several Services consume the roster. Existing duplicate seat records require review.
  • Historical MSP plan and seat tables are reconstructed at refresh time from CRM billing start/end date ranges. They are not filtered by the seat's current Billing_Status, because a seat that is now Ended can still be valid for a historical month.
  • MSP plan exports include PO_User_Count as a plan-level PO user count for comparison with active seat reporting.
  • MSP plan exports also expose Fallback_Monthly_Revenue, Expected_Support_Hours_per_Month, Timesheet_Scope_Type, Timesheet_Scope_ID, and Margin_Budget_Source. The CRM overview model does not infer invoice pricing from those planning fields.
  • Empty contact company or lead source values are normalized to blank/null-style values, and empty lead status becomes Uncategorised.

Process implication:

  • Historical MSP reporting depends on billing date ranges being maintained correctly in CRM.
  • PO_User_Count is a reporting comparison value. It does not currently drive MSP invoice generation or active seat eligibility.
  • Fallback_Monthly_Revenue is planning data for uncovered historical months, not invoice authority. Normal recurring revenue reporting still needs invoice-line data.
  • Seat lead status in visuals is copied from the linked Contact at export time; it should be read as reporting context, not as the source of seat billing eligibility.
  • Current MSP active-seat numbers include onboarding seats for operational visibility; MSP invoice generation has its own billing-eligibility rules.

Project Timesheets Model

Current implemented assumptions:

  • Current timelogs come from the live Delivery Model timelog report.
  • Archived timelogs come from a separate archived backfill report.
  • The final Power BI timelogs table appends current and archived rows.
  • Delivery Model status in Power BI is derived from Valid_Until: a date before the refresh date is reported as completed; blank, today, and future dates are reported as active.
  • Timelogs are assigned to Delivery Models by scope and log date. Valid_From is inclusive. For recurring timesheet reports, Valid_Until ends the monthly budget, while later logged work remains assigned to that model until a later-starting model takes over the same project and scope. One-time models retain their inclusive validity cutoff. CRM delivery alerts and other consumers retain strict validity matching.
  • Sequential recurring Delivery Models can share a project and scope. Adjacent validity periods preserve a continuous monthly comparison while applying each model's expected labour to its own months.
  • If same-scope validity periods overlap, the model with the latest Valid_From owns timelogs in the overlap.
  • Recurring monthly reports include every budget month and subsequent months with mapped timelogs. Later activity months have zero expected hours; empty post-budget months are omitted. Each budget month retains the full monthly expectation, including a partial final validity month.
  • Post-budget work increases actual hours and variance without increasing total expected hours. Monthly completion is undefined and displayed blank for a zero budget. Overall completion compares all actual hours with the fixed budget within the selected report dates.
  • Archived backfill should not be scheduled for routine refresh after a successful import; it should be refreshed manually after historical edits.
  • The Projects user export starts with active Zoho Projects portal users and retains historical-only users found in timelogs. User filtering should use the final users query and its canonical user_zuid key.
  • Delivery Model and mapped timelog exports expose normalized msp_plan_id and msp_plan_name when the Delivery Model is linked to a Managed Service Plan.

Process implication:

  • Historical project edits may require a cache reset and a manual Power BI refresh before they appear in reporting.
  • A gap after a recurring budget ends is reported as work at zero additional budget until a replacement starts. Review unintended gaps and overlaps; overlapping budgets still require correction even though timelogs resolve to a single owner.
  • Retainer review checks should include later activity months, the unchanged overall budget, and replacement-model ownership. See the staff procedure and the repository's pbi/project-timesheets/README.md for report refresh steps.
  • Active Projects users without timelogs remain visible for labour-rate setup; former users remain visible when historical timelogs still reference them.

Scorecard Excel Workbook

Current implemented assumptions:

  • The SharePoint-hosted scorecard workbook refreshes read-only middleware CSV routes protected by the backend Excel/Power BI Basic auth credentials.
  • The P&L report is calculated in Excel from the xero_profit_and_loss staging table. Xero remains the accounting source of truth.
  • On each refresh, the P&L query uses the end of the current month and returns that month plus the previous 35 months. New months enter the rolling window automatically; the oldest months leave it. The current month remains in progress until month-end, and the workbook still requires a successful refresh.
  • Lead reporting uses the leads staging table from /scorecard/leads.csv. The backend fetches Zoho CRM Leads with converted=both, because converted leads are not visible in the normal CRM Leads view.
  • New lead counts use created_month_start. Converted lead counts use converted_month_start where is_converted is true.
  • Deal movement reporting uses the deals staging table. Deal creation counts use created_time; won and lost counts use closing_date filtered by the CRM stage values used for won or lost statuses.
  • Excel for the web data source credentials should be treated as user-scoped to the Microsoft account that configured them. The Basic auth credential is a shared middleware report credential, not a personal Zoho or Xero login.
  • The published workbook should not contain old pivot-cache or range sources named list_leads_import or list_deals_import. Sales pivots should use the current leads and deals tables.

Process implication:

  • A lead can be counted in two different months: once when created and once when converted.
  • Converted-lead reporting depends on the middleware export, not on a CRM Leads view.
  • Workbook viewers may see already-loaded data without being able to refresh. Users who need to refresh need the scorecard Basic auth credentials.
  • If old imported ranges remain in pivots or formulas, SharePoint refresh can fail even when the backend CSV routes and credentials are valid.

Archived Timelog Backfill

Current implemented assumptions:

  • The archived backfill discovers Delivery Model projects, keeps archived projects, fetches logs through Zoho Projects REST APIs, and caches a generated CSV.
  • Archived project discovery is bulk and paginated. Remaining legacy REST calls are paused before the Zoho rolling request limit is reached.
  • The default historical range starts at 2024-01-01 and ends at the last day of the previous month.
  • Cache reset mutates generated cache files only. It does not change CRM, Projects, Xero, or Power BI model definitions.
  • If a generation lock is fresh, reset returns a conflict and leaves the existing job alone. Locks older than 30 minutes are treated as stale.
  • If Zoho throttles cache generation, reporting remains unavailable for that archived table until the upstream cooldown expires. Refresh attempts during the cooldown return the remaining wait time and do not start another job.

Commercial Contribution Dashboard

The dashboard under pbi/commercial-margin provides historical service contribution, staff time/salary diagnostics and reconciliation to Xero. See the implementation reference for source locations and access. These rules describe existing output and impose no requirements on the commercial services design. Legacy CSV consumers and the separate spreadsheet model below have their own contracts.

  • Report transactions and recorded labour from 1 July 2026 onward; select complete months by default and mark the current partial month explicitly.
  • Use posted Xero sales invoices and credits excluding GST, by document date. Use exact CRM Plan/Deal links or reviewed reporting-only service identities. The report and reconciliation batch create no Deals and write nothing to Xero.
  • Resolve licence/hardware products using reviewed IDs, CRM Invoice/Plan lines, specific codes and exact labels. Cost actual Xero quantities using current CRM Product cost prices. Missing/zero prices remain unknown; percentage-of- revenue cost fallbacks are not used.
  • Cost billable and nonbillable time using private dated staff rates: actual annual salary / contracted annual hours, salary only, or a configured contractor rate. An approved salary assumption may override service costing only; salary budget and attributed salary still use actual salary. Part-time annual salaries are actual amounts, not FTE. Unmatched rate coverage retains an explicit $80 fallback. Include unassigned time. One-time work remains separate from recurring MSP scopes; non-MSP recurring Delivery Models are included.
  • Compare salary-only monthly budgets with recorded employee time and actual business payroll. Keep salary data outside CRM and Git, with restricted report access. Missing time is not proof of idle capacity; salary gaps are diagnostic, not additional expenses.
  • Keep central operations and shared infrastructure at business level until a usage basis is agreed. Unassigned time remains visible and costed. Do not equate a timesheet/payroll difference with unused staff capacity.
  • Display known contribution before missing/shared costs. Withhold modelled percentages for missing product costs, unreviewed invoice scope or revenue without recorded labour in the selected totals. Aggregates can mask a service/month without time, so a displayed percentage does not prove complete coverage. Flag delivery without revenue for timing review.
  • Show the actual Xero accrual P&L separately. Reconcile client contribution, central/unassigned time and accounting adjustments to that result by replacing estimated costs with actual expenses, counting payroll once.
  • Power BI uses the authenticated, versioned contribution snapshot and publishes after invoice and P&L checks pass. Source failures do not become zero costs.
  • The browser uses a separate confirmed-service snapshot. It leads with whole- contract project forecasts plus current-FY recurring forecasts. External consultant fees use an explicit supplier hourly rate with uncapped hours; salaried delivery allocation is optional and defaults off. Forecast selling values and cost budgets come from Commercial Services and service lines, independently of invoice timing or manual forecast inputs. Missing delivery history does not block the configured expectation. Source refresh reads CRM, Projects and customer invoices, with no P&L reconciliation. Historical activity remains expandable detail, including all modelled costs.
  • The browser dashboard is a draft proof of concept for modelling experiments; figures and assumptions remain provisional. Historical fingerprint changes produce warnings while current Xero values supply the model. Missing source rows, conflicting ownership, native invoice mismatches and invalid document totals still block publication. Completed source collection survives calculation failure for recalculation, and unchanged failures require an explicit retry or model/configuration revision. The full-business export keeps strict checks.

The separate reconciliation command may reconstruct CRM Invoices and link them to existing Deals after reviewing invoice and product identities. It disables workflow triggers, rejects source changes/conflicts and reads back persisted links. Reporting-only services stay in report configuration.

See Current model and calculation rules and Project Invoice Reconciliation for calculation details and operating steps.

Commercial Margin Spreadsheet

Current implemented assumptions:

  • The workbook under pbi/previsional-budget is a working reporting model. It stores manual assumptions and cost inputs but does not create or update CRM, Projects, Delivery Models, timelogs, Zoho Billing, or Xero records.
  • Commercial margin is owned by a normalized Commercial Unit. V3 consolidates Deal-based non-MSP work with recurring MSP at one Managed Service Plan-month rather than a lifetime Deal value.
  • The implemented forecast scope is calendar 2026. The core roll-up uses Deal Closing_Date as the provisional reporting month. This is not revenue recognition for multi-month delivery.
  • The working workbook is the source of truth for manual inputs. Repository upgrades preserve the documented Assumptions, Product Costs, Labour Rates, Commercial Unit context, Margin Line inputs and raw Deal ranges, and write a new release instead of overwriting the input workbook.
  • Closed-won stages receive a 100% forecast weight, closed-lost stages receive 0%, and open stages use normalized CRM Probability.
  • The core roll-up seeds one placeholder Margin Line from each Deal amount. Placeholder lines must be replaced by actual product and labour components as data becomes available.
  • Product cost resolves from a line override, then Product_Costs, then an editable category fallback. Initial fallback gross margins are 15% for Hardware and 10% for Licences.
  • Hardware and Licence fallback rows are marked Estimated. Missing Plan, labour, product or linkage inputs are marked Incomplete.
  • Unresolved cost may be zero-filled to keep the roll-up visible only when the result is marked Incomplete. A displayed 100% margin on such a row is not a measured or approved margin.
  • Labour Rates are manual, effective-dated spreadsheet inputs. V3 generates a coverage roster from active Projects users and historical timelog users. Actual timelogs match exactly one active person rate by stable user ID and log date; an open-ended rate has no effective_until. The person rate takes precedence, a missing person rate can use the blended assumption as an Estimated fallback, and overlapping active periods remain Incomplete.
  • Delivery Model monthly expected hours use the blended budget rate. A Deal-keyed budget replaces manual expected labour for the matching Tech Project without double counting. MSP Plan-month revenue, product cost and expected labour are included in the core Summary; actual labour remains a separate reconciliation measure.
  • MSP line types license and subscription are treated as Licence, device as Hardware, base, seat and prorate as service, and discount and adjustment as no-separate-product-cost lines. Product_Costs category and cost entries take precedence. Unknown types remain Incomplete.
  • The read-only commercial-margin MSP export accepts an explicit start and end month and returns one row per applicable Plan-month invoice line. It reuses the MSP invoice-preview rules for billing dates, seat quantities, price overrides and Product defaults. revenue_ex_gst is the resolved invoice-line amount before GST.
  • subtotal_ex_gst, GST and total are Plan-month context repeated on every invoice-line row. Sum revenue_ex_gst for line-grain revenue; do not sum the repeated Plan totals.
  • The MSP export provides stable commercial_unit_key, msp_plan_month_key and line keys, retains Plans with setup issues as visible gap rows, and does not create or refresh CRM or Xero invoices.

Process implication:

  • The model is a 2026 commercial-margin prototype, not a complete P&L.
  • Users must review Estimated and Incomplete coverage before relying on Summary gross profit or margin.
  • A populated workbook must be upgraded from the latest accepted file. Rebuild from the original Deal snapshot would discard persistent inputs.
  • The backend exports provide MSP Plan-month revenue identity and Plan-linked Delivery Model/timelog identity. V3 supplies Power Query definitions and dedicated staging/calculation sheets; Excel still requires one-time query creation and credential setup before refresh.
  • Product/quote lines should eventually provide third-party cost.

References

  • CRM overview model: pbi/crm-overview/README.md
  • Project timesheets model: pbi/project-timesheets/README.md
  • Archived timelog backfill: docs/notes/2026-05-06_archived_timelog_backfill.md
  • Commercial margin spreadsheet: pbi/previsional-budget/README.md