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).