# Sales KPI definitions — Boréal Fenestration Inc.

**Document:** SOPS-KPI-DEF, version 2025.3 (approved by Finance and Sales Operations, 2025-11-14)
**Scope:** fiscal year 2025 (FY2025 = 2025-01-01 to 2025-12-31, calendar year), with FY2024 as the comparison period.
**Currency:** Canadian dollars, before GST/QST/HST. All figures are computed from the ERP order-line export (`sales.sqlite`,
table `orders`) joined to `products` (list prices and unit costs) and to the regional mapping (`regions_mapping.csv`).

These definitions are binding for every KPI pack. When a figure in a pack differs from a figure in another report, the
figure computed with these definitions prevails.

## 1. Data preparation rules

1.1 **Order-line identity.** One row of `orders` is one order line. A line is uniquely identified by the pair
`(order_id, line_no)`. The ERP export routine is known to repeat rows when it is run while the nightly job is still
writing; if the export contains more than one row with the same `(order_id, line_no)`, exactly one occurrence is kept
and the number of removed rows is reported in the pack.

1.2 **Status.** `status` takes the values `shipped`, `returned` and `cancelled`. A return is recorded by the ERP as its
own order line with `status = 'returned'` (a credit line with a positive quantity, carrying the `order_date` of the
original order); the original shipped line keeps `status = 'shipped'`. Cancelled lines were never invoiced: they are
**excluded from every KPI** (revenue, margin, orders, average order value, returns and delivery).

1.3 **Line value and unit price validation.** Line value = `qty × unit_price`. The `unit_price` of a line must lie between
50 % and 150 % of the product's `list_price` (table `products`). A line outside this range is a data-entry error: the line
is kept, its `unit_price` is replaced by the product's `list_price`, and the pack reports the number of lines and the
product(s) affected. Normal commercial discounts (0–12 %) and the January 2025 price-list increase (+3 %) are within the
accepted range and must not be altered.

1.4 **Period attribution.** Every line, including return (credit) lines, is attributed to the month and year of its
`order_date`.

1.5 **Regions.** Each line carries `region_code` (three upper-case letters). Region names come from `regions_mapping.csv`;
codes must be normalised to upper case before joining so that every line maps to exactly one named region. A pack with
lines in an "unknown" region is not acceptable.

1.6 **Dates.** `ship_date` must be on or after `order_date`. A shipped line whose `ship_date` is earlier than its
`order_date` is impossible (interface error): it is **kept in revenue and margin** but **excluded from the on-time delivery
metric** (numerator and denominator), and the number of such lines is reported in the pack.

## 2. KPI formulas

| # | KPI | Definition |
|---|---|---|
| K1 | Gross shipped revenue | Σ line value over lines with `status = 'shipped'` |
| K2 | Returns value | Σ line value over lines with `status = 'returned'` |
| K3 | **Net revenue** | K1 − K2 |
| K4 | Net COGS | Σ `qty × unit_cost` over shipped lines − Σ `qty × unit_cost` over returned lines (unit_cost from `products`) |
| K5 | Gross margin | K3 − K4 |
| K6 | **Gross margin %** | K5 ÷ K3 × 100 |
| K7 | Number of orders | count of distinct `order_id` having at least one shipped line in the period |
| K8 | **Average order value (AOV)** | K3 ÷ K7 |
| K9 | **Return rate %** | K2 ÷ K1 × 100 |
| K10 | **On-time delivery %** | among shipped lines (`status = 'shipped'`) with a valid `ship_date` (rule 1.6), share with `ship_date − order_date ≤ 10` calendar days, × 100 |
| K11 | **Year-over-year (YoY) %** | (net revenue 2025 ÷ net revenue 2024 − 1) × 100, computed for the full year and for each calendar month (January 2025 vs January 2024, etc.) |
| K12 | **Top-10 products** | the ten products with the highest net revenue (K3) in FY2025, ranked in descending order, with each product's share of FY2025 net revenue |

K7, K8, K9 and K10 are reported for FY2025 (FY2024 values are welcome as comparison). K1–K6 and K11 are reported for
FY2025 and FY2024, and K3 and K11 month by month.

## 3. Presentation conventions

- Currency amounts to the dollar (no cents) in the dashboard; percentages with one decimal.
- The commentary quotes headline figures in full dollars (for example 12,345,678 CAD), never rounded to millions, so that
  Finance can tie them to the dashboard.
- Regions are presented in the order of `regions_mapping.csv`; products by rank.
- Every data-preparation action (rules 1.1, 1.3, 1.5, 1.6) is quantified in the pack: how many rows or lines, which
  product(s), which region code(s).