KPIs

The ReturnPro dashboard computes financial and volume KPIs across the income statement and Master P&L. This page documents the formulas the code actually uses, the Solution7 conventions for sourcing data, and the dimensional constraints (sign multipliers, dim maintenance) you need to keep clean each close.

Production KPIs

The dashboard surfaces these as primary KPIs, computed at every level of the program hierarchy:

KPI	What it is
Sales	Revenue category from the income statement.
Supply Chain Services	Revenue category from the income statement.
Total Revenues	Sum of revenue lines.
COGS	Cost of Goods Sold line item from the income statement (not a pre-computed ratio).
Total Operating Expenses	Sum of opex lines.
Gross Margin	Total Revenues − Total Operating Expenses, rendered at every hierarchy level.
Processed Units	Volume KPI sourced from R1 volume uploads (account codes 130-132).

Gross Margin formula

Gross Margin %
  = (Total Revenues − Adjusted Total OpEx) / Total Revenues

where
  Adjusted Total OpEx = Total Operating Expenses − Facility Costs (Allocated)

This is what the code computes:

• dashboard-returnpro/app/reports/income-statement/page.tsx:159-162
• dashboard-returnpro/app/master-pl/page.tsx:114-124

Previous docs were wrong

Older versions of this page documented Gross Margin as (Revenue − COGs) / Revenue. That formula is not what the code computes. The actual formula uses Total Revenues minus the full Adjusted Total OpEx (which is operating expenses minus the allocated facility costs), not just COGS.

A legacy dim_kpi row labeled "Gross Margin" exists as a sum-bucket that double-counts revenue against expenses. Both the Income Statement and the Master P&L render the corrected formula above; ignore the legacy dim_kpi row when reasoning about the dashboard's actual output.

Volume KPIs

Processed Units comes from R1 volume uploads via optimal finance upload-r1 (see CLI Commands). Each program produces three insert sets per upload run:

• Unit — TRGID count.
• Pallet — LocationID count.
• Qty — allocation-based count.

Account codes used as volume buckets: 130-132 (checked-in), 116-118 (order closed), 160-162 (ops complete).

Solution7 canonical formula

NetSuite cells in the monthly close template must use the canonical Solution7 formula:

=-NSGLAPBAL.LOCKED(
    "ReturnPro (Consolidated)",
    <acct>,
    <period>,
    <period>,
    "Program ID",
    <programid>
  )

The leading minus sign flips NetSuite's sign convention so revenue lands positive and expenses land negative for downstream sign_multiplier math (see below). Validation on the March 2026 close: dollar accuracy jumped from 5.8% to 94.7% after fixing three defects plus the sign multipliers.

Three defects to check for every close

Solution7 formula defects

1. NSGLADBAL typo — wrong function (an extra D). Cells with this typo pull no data; whole accounts go missing without an error.
2. "New Program" filter dimension — using the wrong filter dimension drops 75-90% of rows. The correct filter dimension name is "Program ID".
3. IF(TRUE, hardcoded, NSGLAPBAL(...)) wrappers — frozen cells. The IF(TRUE, ...) wrapper around hardcoded values causes Excel to never evaluate the live formula, so the cell forever returns whatever was hardcoded the day the wrapper was added.

All three of these masquerade as "the data is just sparse" until someone audits a known-good month and the variance becomes obvious. Run optimal finance audit --months YYYY-MM and optimal finance diagnose --months YYYY-MM after each upload.

Sign-multiplier convention

dim_account.sign_multiplier is the single source of truth for how each account's raw value should be flipped before aggregation:

Account family	sign_multiplier
Revenue	+1
Refunds	−1
Cost of Goods / Operating Expenses	−1

Confirmed from the March 2026 close fix-up: the dollar accuracy gap traced primarily to wrong sign_multiplier values in dim_account. The data-driven correction script lives at ~/.openclaw/workspace/optimal-cli/scripts/fix-signs-from-audit.ts.

Dim maintenance per close

The CLI's optimal finance sync-dims command is deactivate-only — it will not add new dimension rows or reactivate stale ones. Each monthly close requires two manual passes:

1. Upsert any new IS GL codes into dim_account (with the correct sign_multiplier).
2. Reactivate any NetSuite-export programids currently stuck inactive in dim_program_id.

Reference scripts (one-off, not part of the CLI surface):

• ~/.openclaw/workspace/optimal-cli/scripts/march-dim-fix.ts — dim expansion.
• ~/.openclaw/workspace/optimal-cli/scripts/fix-signs-from-audit.ts — data-driven sign correction.

Filtering

KPIs can be filtered by:

• Program — single program or multi-select.
• Period — month range or monthly-vs-annual toggle.
• Scenario — comparing actuals against saved budget scenarios. Scenarios are managed via the FP&A Forecast interface and the optimal scenario CLI; program-specific scenarios are picked from the scenario selector on the dashboard.

Planned KPIs (not shipped)

The four ratio KPIs below were proposed but never built into the dashboard. They are listed here so future work has a clear "to do" anchor; they are not currently visible in the UI and optimal finance kpis does not emit them.

KPI	Proposed formula	Status
COGs %	Cost of Goods Sold / Revenue	Not implemented as a pre-computed KPI. COGS is available as a line item; ratio is not.
Commission %	Commission Expense / Revenue	Not implemented. No Commission Expense metric is surfaced.
Check-In $/Unit	Check-In Costs / Total Units	Not implemented. Volume data exists; per-unit ratio does not.
Refurb $/Unit	Refurbishment Costs / Total Units	Not implemented. No refurbishment-cost metric exists.

Open question

Should these four planned KPIs ship as part of the next dashboard pass, or be removed from this page entirely? Surfaced to the user in the writer report.

Annual override fix

Programs that only have override entries in the income statement (no base GL rows) used to disappear in the annual roll-up. The annual view now scans both base GL rows and override entries, so every program with financial data appears in the annual summary regardless of how its rows were entered.