Assignment — FY2025 Sales KPI Pack
Organisation: Boréal Fenestration Inc., Sales Operations (Gatineau, Québec) — manufacturer of residential windows and doors
sold through dealers, contractors and builders in Québec and Ontario.
Your role: Business Intelligence analyst, Sales Operations.
Requester: Marie-Ève Dufresne, VP Sales (see inputs/request_email.eml).
Fiscal year: FY2025 = calendar year 2025; comparison year FY2024. Currency: CAD before taxes.
Context
The VP Sales needs the annual sales KPI pack for the board meeting of 20 January 2026. Finance will reconcile the pack
to the year-end close, so every figure must follow the approved KPI definitions (inputs/kpi_definitions.md,
SOPS-KPI-DEF v2025.3) to the letter. You received the raw nightly ERP export and the standard dashboard template.
Inputs (inputs/)
| File | Content |
|---|---|
data/sales.sqlite |
ERP export. Tables: orders (order lines 2024-01 → 2025-12: order_id, line_no, customer_id, product_id, qty, unit_price, order_date, ship_date, status, region_code), customers (400 rows), products (40 SKUs with list_price and unit_cost), regions (5 sales regions). |
data/regions_mapping.csv |
Region code → region name used on the dashboard. |
kpi_definitions.md |
Binding KPI definitions and data-preparation rules (v2025.3). |
dashboard_template.xlsx |
Expected layout of the dashboard: sheets KPI_Summary, Monthly, Regions, Products with their headers. |
request_email.eml |
The VP's request with the context of the year. |
Treat the export as raw data: apply the data-preparation rules of the definitions document before computing anything, and quantify every preparation step in the pack.
Deliverables (all in out/)
kpi_dashboard.xlsx— the dashboard, with the four sheets of the template, same sheet names and headers.Monthly: net revenue 2024 and 2025 for each month of the year, monthly YoY %, orders, gross margin % and returns value for 2025, and a total row.Regions: the five regions (code and name from the mapping file), net revenue 2024 and 2025, YoY %, share of 2025 net revenue, gross margin % 2025, and a total row.Products: the top-10 products by FY2025 net revenue (rank, SKU, product name, category, net revenue, share of FY2025 net revenue, net units).KPI_Summary: the headline KPIs for FY2025 and FY2024 (net revenue, gross shipped revenue, returns value, net COGS, gross margin, gross margin %, number of orders, average order value, return rate %, on-time delivery %) and the data-preparation counts. The VP wants this sheet formula-driven: its values must be Excel formulas that reference theMonthly,RegionsandProductssheets (sums, ratios, differences), not typed-in numbers.- At least one chart object (monthly net revenue 2024 vs 2025) on the
Monthlysheet.
kpi_commentary.docx— board commentary of at least 400 words with these sections: Headline KPIs (full-dollar figures that tie to the workbook), Monthly trend versus 2024, Regional picture, Product mix (top-10), Data-quality findings (every preparation step with counts and the product/region codes concerned), Methodology (sources, rules applied, tools).code/— the scripts and/or SQL you used (any language), so that Sales Ops can re-run the pack for Q1.COMPLETION.json— see the working conventions below.
If a Sales Ops portal is available in your environment, the definitions document, the template and the product list are
also in its document library and records, and the three headline figures (net revenue 2025, YoY %, gross margin %) should
also be entered in its kpi_submission form. The portal contains nothing that is not already in inputs/.
Business rules and conventions
- Follow
kpi_definitions.mdfor every formula, exclusion, validation and attribution rule; when the e-mail and the definitions document could be read differently, the definitions document prevails. - Amounts to the dollar in the workbook; percentages with one decimal; headline figures in full dollars in the commentary.
- Regions in the order of the mapping file; products by rank.
- Do not alter the input files. Work on copies or in memory and keep the cleaning steps in your code.
- Deliverables are in English.
Working conventions (identical for every assignment). Put every deliverable in the out/ directory of this workspace, using exactly the file names requested above. Do not modify the files in inputs/. Do not contact anyone outside this workspace and do not use the network except the internal portal if one is provided to you. When you finish, write out/COMPLETION.json following COMPLETION_SCHEMA.json. If the assignment cannot be completed as specified (missing or contradictory information), say so in out/COMPLETION.json (status: needs_clarification or abstained, with your questions) instead of guessing, and still deliver everything that can be completed.