File: 05-calculation-script.md

Lumenwire Audio: Kewpie's calculation script

Every KPI calculated on the sample data in one saved, re-runnable script. Every number in the Calculation Pack comes from its results.

"""Lumenwire Audio, Stage 2 (Kewpie): KPI calculations and root cause tests.
Sample data. Re-run:  python "02_calculations.py"   (from anywhere)
Reads ./data/*.csv, writes 02_results.json next to this script and prints a log.
Every number in the Calculation Pack comes from 02_results.json (see 02_build_pack.py).
"""
import json
import re
import warnings
from pathlib import Path

import numpy as np
import pandas as pd

warnings.filterwarnings("ignore")
HERE = Path(__file__).resolve().parent
D = HERE / "data"
R = {}  # results

AS_OF = pd.Period("2026-09", "M")          # latest complete month (data ends 30 Sep 2026)
AS_OF_DATE = pd.Timestamp("2026-09-30")
TARGET = 115_000_000
FY26_REV = 80_600_000
TARGET_MONTH = pd.Period("2028-06", "M")


def fy_of(p):  # period -> financial year (July to June)
    return p.year + (1 if p.month >= 7 else 0)


# ---------------------------------------------------------------- GL
gl = pd.read_csv(D / "gl_monthly.csv")
acc = pd.read_csv(D / "gl_accounts.csv")
gl["p"] = pd.PeriodIndex(gl.period, freq="M")
gl = gl.merge(acc[["account_code", "pl_group"]], on="account_code", how="left")
R["dq_gl_unmapped_rows"] = int(gl.pl_group.isna().sum())
R["dq_gl_dup_rows"] = int(gl.duplicated(["period", "account_code", "cost_centre"]).sum())

HW = [4000, 4050]
SW = [4100, 4110]
OTH = [4200, 4210]
REVACC = HW + SW + OTH


def monthly(codes):
    s = gl[gl.account_code.isin(codes)].groupby("p").amount_aud.sum() * -1
    return s.reindex(pd.period_range(gl.p.min(), AS_OF, freq="M")).fillna(0.0)


m_hw, m_sw, m_oth = monthly(HW), monthly(SW), monthly(OTH)
m_tot = m_hw + m_sw + m_oth
m_cos = gl[gl.pl_group == "cost_of_sales"].groupby("p").amount_aud.sum().reindex(m_tot.index).fillna(0.0)
r12 = lambda s: s.rolling(12).sum()

fy_rev = {}
for fy in range(2020, 2027):
    idx = [p for p in m_tot.index if fy_of(p) == fy]
    fy_rev[fy] = dict(total=m_tot[idx].sum(), hw=m_hw[idx].sum(), sw=m_sw[idx].sum(), oth=m_oth[idx].sum(),
                      cos=m_cos[idx].sum())
R["fy_rev"] = {str(k): v for k, v in fy_rev.items()}
R["recon_FY25_total"] = fy_rev[2025]["total"]
R["recon_FY26_total"] = fy_rev[2026]["total"]
R["recon_FY26_sw"] = fy_rev[2026]["sw"]
R["recon_FY26_sw_growth"] = fy_rev[2026]["sw"] / fy_rev[2025]["sw"] - 1
R["recon_gm_FY25"] = 1 - fy_rev[2025]["cos"] / fy_rev[2025]["total"]
R["recon_gm_FY26"] = 1 - fy_rev[2026]["cos"] / fy_rev[2026]["total"]

# ---------------------------------------------------------------- K1
r12_tot, r12_hw, r12_sw = r12(m_tot), r12(m_hw), r12(m_sw)


def path(p):
    k = (p - pd.Period("2026-06", "M")).n
    return FY26_REV + (TARGET - FY26_REV) * k / 24


k1_rows = []
for p in pd.period_range("2026-06", AS_OF, freq="M"):
    k1_rows.append(dict(month=str(p), r12=r12_tot[p], hw=r12_hw[p], sw=r12_sw[p], path=path(p),
                        gap=r12_tot[p] - path(p), gap_pct=r12_tot[p] / path(p) - 1))
R["k1_series"] = k1_rows
k1 = k1_rows[-1]
R["k1"] = dict(
    current=k1["r12"], hw=k1["hw"], sw=k1["sw"], path=k1["path"], gap=k1["gap"], gap_pct=k1["gap_pct"],
    months_remaining=(TARGET_MONTH - AS_OF).n,
)
R["k1"]["pace_needed_per_month"] = (TARGET - k1["r12"]) / R["k1"]["months_remaining"]
R["k1"]["run_rate_3m"] = (r12_tot[AS_OF] - r12_tot[AS_OF - 3]) / 3
R["k1"]["path_pace_per_month"] = (TARGET - FY26_REV) / 24
R["k1"]["peak_r12"] = float(r12_tot.max())
R["k1"]["peak_r12_month"] = str(r12_tot.idxmax())
# quarterly revenue by family (financial quarters)
qs = {}
for p in m_tot.index:
    if p < pd.Period("2024-07", "M"):
        continue
    q = f"FY{str(fy_of(p))[2:]}-Q{((p.month - 7) % 12) // 3 + 1}"
    d = qs.setdefault(q, dict(hw=0.0, sw=0.0, oth=0.0))
    d["hw"] += m_hw[p]; d["sw"] += m_sw[p]; d["oth"] += m_oth[p]
R["quarterly_rev"] = qs

# FY27 plan vs path / actual
plan = pd.read_csv(D / "board_plan_fy27.csv")
plan["p"] = pd.PeriodIndex(plan.month, freq="M")
R["plan_fy27_total"] = float(plan.plan_revenue_aud.sum())
R["path_at_jun27"] = path(pd.Period("2027-06", "M"))
act_q1 = m_tot[pd.period_range("2026-07", "2026-09", freq="M")].sum()
plan_q1 = plan[plan.p <= pd.Period("2026-09", "M")].plan_revenue_aud.sum()
R["fy27_q1_actual"] = float(act_q1)
R["fy27_q1_plan"] = float(plan_q1)
# plan = FY26 month x 1.15 ?  (test)
chk = []
for _, r in plan.iterrows():
    prev = r.p - 12
    fam = dict(hardware=m_hw, software=m_sw, other=m_oth)[r.product_family]
    chk.append(r.plan_revenue_aud / fam[prev])
R["plan_vs_prior_year_ratio_min_max"] = [float(min(chk)), float(max(chk))]
R["plan_sw_mar27"] = float(plan[(plan.month == "2027-03") & (plan.product_family == "software")].plan_revenue_aud.iloc[0])

# ---------------------------------------------------------------- licences (K3, K3.1)
lic = pd.read_csv(D / "software_licences.csv", parse_dates=["start_date", "end_date"])
lic["p"] = lic.start_date.dt.to_period("M")
lic["fy"] = lic.start_date.apply(lambda d: d.year + (1 if d.month >= 7 else 0))
cur = pd.period_range(AS_OF - 11, AS_OF, freq="M")
pri = pd.period_range(AS_OF - 23, AS_OF - 12, freq="M")
sw_cur, sw_pri = m_sw[cur].sum(), m_sw[pri].sum()
lic_cur = lic[lic.p.isin(cur)]
lic_pri = lic[lic.p.isin(pri)]
R["dq_lic_vs_gl_diff_FY26"] = float(lic[lic.fy == 2026].contract_value_aud.sum() - fy_rev[2026]["sw"])
R["dq_lic_vs_gl_diff_R12M"] = float(lic_cur.contract_value_aud.sum() - sw_cur)
big = lic.sort_values("contract_value_aud", ascending=False).iloc[0]
R["big_licence"] = dict(id=big.licence_id, customer=big.customer_name, oem=big.oem_id, value=float(big.contract_value_aud),
                        start=str(big.start_date.date()), type=big.licence_type, seats=int(big.seats_or_devices))
ex = big.contract_value_aud
R["k3"] = dict(cur=sw_cur, prior=sw_pri, growth=sw_cur / sw_pri - 1,
               cur_ex=sw_cur - ex, growth_ex=(sw_cur - ex) / sw_pri - 1,
               fy26=fy_rev[2026]["sw"], fy25=fy_rev[2025]["sw"], fy24=fy_rev[2024]["sw"],
               fy26_growth=fy_rev[2026]["sw"] / fy_rev[2025]["sw"] - 1,
               fy26_growth_ex=(fy_rev[2026]["sw"] - ex) / fy_rev[2025]["sw"] - 1,
               fy25_growth=fy_rev[2025]["sw"] / fy_rev[2024]["sw"] - 1,
               big_share_of_fy26=ex / fy_rev[2026]["sw"], big_share_of_r12=ex / sw_cur)
# months remaining to target date 2027-06 and pace
mrem = (pd.Period("2027-06", "M") - AS_OF).n
R["k3"]["months_remaining"] = mrem
# growth series for last 4 months (run rate of the growth %)
g_series = {}
for p in [AS_OF - 3, AS_OF - 2, AS_OF - 1, AS_OF]:
    g_series[str(p)] = float(r12(m_sw)[p] / r12(m_sw)[p - 12] - 1)
R["k3"]["growth_series"] = g_series
R["k3"]["run_rate_pts_per_month"] = (g_series[str(AS_OF)] - g_series[str(AS_OF - 3)]) / 3 * 100
R["k3"]["pace_needed_pts_per_month"] = (0.20 - g_series[str(AS_OF)]) / mrem * 100
# software split by type (perpetual vs subscription) and other licences
sw_perp = monthly([4100]); sw_sub = monthly([4110])
R["k3"]["perp_cur"], R["k3"]["perp_prior"] = sw_perp[cur].sum(), sw_perp[pri].sum()
R["k3"]["sub_cur"], R["k3"]["sub_prior"] = sw_sub[cur].sum(), sw_sub[pri].sum()
# ex-big: perpetual ex
R["k3"]["perp_cur_ex"] = R["k3"]["perp_cur"] - ex
# licence count / mean size
R["k3"]["lic_count_cur"], R["k3"]["lic_count_prior"] = int(len(lic_cur)), int(len(lic_pri))
R["k3"]["new_cust_median_value_cur"] = float(lic_cur[lic_cur.contract_value_aud < 1e6].contract_value_aud.median())
R["k3"]["new_cust_median_value_prior"] = float(lic_pri.contract_value_aud.median())
# bridge of software change (cur vs prior)
oth_cur = lic_cur[lic_cur.licence_id != big.licence_id]
R["k3"]["bridge"] = dict(
    prior=sw_pri,
    big_licence=ex,
    perpetual_other=float(oth_cur[oth_cur.licence_type == "perpetual"].contract_value_aud.sum() - lic_pri[lic_pri.licence_type == "perpetual"].contract_value_aud.sum()),
    sub_annual=float(oth_cur[oth_cur.licence_type == "subscription_annual"].contract_value_aud.sum() - lic_pri[lic_pri.licence_type == "subscription_annual"].contract_value_aud.sum()),
    sub_monthly=float(oth_cur[oth_cur.licence_type == "subscription_monthly"].contract_value_aud.sum() - lic_pri[lic_pri.licence_type == "subscription_monthly"].contract_value_aud.sum()),
    current=sw_cur)
R["k3"]["bridge_sum_check"] = sum(v for k, v in R["k3"]["bridge"].items() if k not in ("current",)) - sw_cur
# renewal quality of annual subscriptions ended in last 12 months (counter-metric)
sub = lic[lic.licence_type == "subscription_annual"].copy()
due = sub[(sub.end_date >= (AS_OF_DATE - pd.DateOffset(months=12))) & (sub.end_date <= AS_OF_DATE)]
R["k3"]["renewal_due_n"] = int(len(due))
R["k3"]["renewal_status_missing"] = int(due.renewal_status.isna().sum())
dd = due.dropna(subset=["renewal_status"])
R["k3"]["renewal_rate_value"] = float(dd[dd.renewal_status == "renewed"].contract_value_aud.sum() / dd.contract_value_aud.sum())
R["k3"]["renewal_rate_count"] = float((dd.renewal_status == "renewed").mean())
due_prior = sub[(sub.end_date >= (AS_OF_DATE - pd.DateOffset(months=24))) & (sub.end_date < (AS_OF_DATE - pd.DateOffset(months=12)))].dropna(subset=["renewal_status"])
R["k3"]["renewal_rate_count_prior"] = float((due_prior.renewal_status == "renewed").mean())
R["k3"]["pending_in_due"] = int((dd.renewal_status == "pending").sum())
R["dq_lic_enddate_missing_subs"] = int(sub.end_date.isna().sum())
R["dq_lic_oem_missing"] = int(lic.oem_id.isna().sum())
R["dq_lic_total"] = int(len(lic))

# K3.1 concentration (licences started in last 12 months)
c = lic_cur.contract_value_aud.sort_values(ascending=False)
R["k3_1"] = dict(total=float(c.sum()), largest=float(c.iloc[0]), largest_share=float(c.iloc[0] / c.sum()),
                 top5=float(c.head(5).sum()), top5_share=float(c.head(5).sum() / c.sum()),
                 largest_share_ex=0.0, n=int(len(c)))
c2 = c.iloc[1:]
R["k3_1"]["largest_share_ex"] = float(c2.iloc[0] / c2.sum())
R["k3_1"]["top5_share_ex"] = float(c2.head(5).sum() / c2.sum())
R["k3_1"]["largest_customer_is_oem"] = bool(pd.notna(big.oem_id))
# prior-year comparator
cp = lic_pri.contract_value_aud.sort_values(ascending=False)
R["k3_1"]["prior_largest_share"] = float(cp.iloc[0] / cp.sum())
R["k3_1"]["prior_top5_share"] = float(cp.head(5).sum() / cp.sum())
# same licence set by calendar of FY for trend
R["k3_1"]["by_fy_largest_share"] = {str(fy): float(g.contract_value_aud.max() / g.contract_value_aud.sum())
                                    for fy, g in lic.groupby("fy") if fy < 2027}
# Gary's 10% threshold test on the second largest
R["k3_1"]["second_largest"] = float(c.iloc[1])
# total of licences with zero seats
R["dq_lic_zero_seats"] = int((lic.seats_or_devices == 0).sum())

# ---------------------------------------------------------------- K3.2 gross margin
gm_cur = 1 - m_cos[cur].sum() / m_tot[cur].sum()
gm_pri = 1 - m_cos[pri].sum() / m_tot[pri].sum()
R["k3_2"] = dict(cur=float(gm_cur), prior=float(gm_pri), fy25=R["recon_gm_FY25"], fy26=R["recon_gm_FY26"],
                 cur_rev=float(m_tot[cur].sum()), cur_cos=float(m_cos[cur].sum()))
ex_rev = m_tot[cur].sum() - ex
R["k3_2"]["cur_ex_big"] = float(1 - m_cos[cur].sum() / ex_rev)  # big licence assumed ~zero cost of sales
R["k3_2"]["fy26_ex_big"] = float(1 - fy_rev[2026]["cos"] / (fy_rev[2026]["total"] - ex))
# hardware-only gross margin (cost of sales split: 5000-5030 hardware, 5100 software)
cos_hw_codes = [5000, 5010, 5020, 5030]
cos_hw = gl[gl.account_code.isin(cos_hw_codes)].groupby("p").amount_aud.sum().reindex(m_tot.index).fillna(0)
cos_sw = gl[gl.account_code == 5100].groupby("p").amount_aud.sum().reindex(m_tot.index).fillna(0)
R["k3_2"]["hw_gm_cur"] = float(1 - cos_hw[cur].sum() / (m_hw[cur].sum() + m_oth[cur].sum()))
R["k3_2"]["hw_gm_prior"] = float(1 - cos_hw[pri].sum() / (m_hw[pri].sum() + m_oth[pri].sum()))
R["k3_2"]["sw_gm_cur"] = float(1 - cos_sw[cur].sum() / m_sw[cur].sum())
R["k3_2"]["sw_share_cur"] = float(m_sw[cur].sum() / m_tot[cur].sum())
R["k3_2"]["sw_share_prior"] = float(m_sw[pri].sum() / m_tot[pri].sum())
# 5010 variance release (FY26 credit)
v5010 = gl[gl.account_code == 5010].groupby("p").amount_aud.sum().reindex(m_tot.index).fillna(0)
R["k3_2"]["var5010_cur"] = float(v5010[cur].sum())
R["k3_2"]["var5010_prior"] = float(v5010[pri].sum())
R["k3_2"]["target_fy27_board"] = 76.0
R["k3_2"]["r12_series"] = {str(p): float(1 - m_cos[p - 11:p].sum() / m_tot[p - 11:p].sum()) for p in [AS_OF - 3, AS_OF - 2, AS_OF - 1, AS_OF]}
R["k3_2"]["run_rate_pts_per_month"] = (R["k3_2"]["r12_series"][str(AS_OF)] - R["k3_2"]["r12_series"][str(AS_OF - 3)]) / 3 * 100

# ---------------------------------------------------------------- invoices (K1 RCA)
inv = pd.read_csv(D / "erp_invoice_lines.csv", parse_dates=["invoice_date"])
inv["p"] = inv.invoice_date.dt.to_period("M")
inv["fy"] = inv.invoice_date.apply(lambda d: d.year + (1 if d.month >= 7 else 0))
R["dq_inv_oem_missing"] = int(inv.oem_id.isna().sum())
R["dq_inv_rows"] = int(len(inv))
R["dq_inv_order_missing"] = int(inv.order_id.isna().sum())
R["dq_inv_order_missing_hw"] = int((inv.order_id.isna() & inv.product_family.isin(["chip", "module", "card"])).sum())
R["dq_inv_oem_missing_families"] = inv[inv.oem_id.isna()].product_family.value_counts().to_dict()
R["dq_inv_fx_missing_nonaud"] = int(((inv.fx_rate_used.isna()) & (inv.currency != "AUD")).sum())
R["dq_inv_fx_missing_nonaud_last24m"] = int(((inv.fx_rate_used.isna()) & (inv.currency != "AUD") & (inv.invoice_date >= "2024-10-01")).sum())
# reconcile invoices to GL by FY and family
inv_fam = inv.groupby(["fy", "product_family"]).net_amount_aud.sum().unstack().fillna(0)
rec = {}
for fy in range(2020, 2027):
    hw_i = inv_fam.loc[fy, ["chip", "module", "card", "rebate"]].sum()
    sw_i = inv_fam.loc[fy, ["software_perpetual", "software_subscription"]].sum()
    oth_i = inv_fam.loc[fy, ["dev_kit", "freight"]].sum()
    rec[str(fy)] = dict(hw_diff=float(hw_i - fy_rev[fy]["hw"]), sw_diff=float(sw_i - fy_rev[fy]["sw"]), oth_diff=float(oth_i - fy_rev[fy]["oth"]))
R["recon_invoice_vs_gl"] = rec
R["recon_max_abs_diff"] = max(abs(v) for r_ in rec.values() for v in r_.values())

hwi = inv[inv.product_family.isin(["chip", "module", "card"])]
# volume / price-mix bridge FY25 -> FY26 by SKU
sku = hwi.groupby(["fy", "sku"]).agg(q=("qty", "sum"), a=("net_amount_aud", "sum")).reset_index()
b = {}
for s_, g in sku.groupby("sku"):
    g = g.set_index("fy")
    q0, q1, a0, a1 = g.loc[2025, "q"], g.loc[2026, "q"], g.loc[2025, "a"], g.loc[2026, "a"]
    p0, p1 = a0 / q0, a1 / q1
    b[s_] = dict(vol=(q1 - q0) * p0, price=q1 * (p1 - p0), q0=int(q0), q1=int(q1))
vol = sum(v["vol"] for v in b.values()); prc = sum(v["price"] for v in b.values())
reb25 = inv_fam.loc[2025, "rebate"]; reb26 = inv_fam.loc[2026, "rebate"]
sw25, sw26 = fy_rev[2025]["sw"], fy_rev[2026]["sw"]
oth25, oth26 = fy_rev[2025]["oth"], fy_rev[2026]["oth"]
R["bridge_fy25_fy26"] = dict(
    fy25=fy_rev[2025]["total"], hw_volume=float(vol), hw_price_mix=float(prc), rebates=float(reb26 - reb25),
    sw_big_licence=-0.0 + float(ex), sw_other=float(sw26 - sw25 - ex), other=float(oth26 - oth25), fy26=fy_rev[2026]["total"])
bb = R["bridge_fy25_fy26"]
bb["check_unexplained"] = float(bb["fy26"] - (bb["fy25"] + bb["hw_volume"] + bb["hw_price_mix"] + bb["rebates"] + bb["sw_big_licence"] + bb["sw_other"] + bb["other"]))
bb["hw_units_fy25"] = int(sum(v["q0"] for v in b.values())); bb["hw_units_fy26"] = int(sum(v["q1"] for v in b.values()))
bb["hw_units_change_pct"] = bb["hw_units_fy26"] / bb["hw_units_fy25"] - 1
bb["hw_avg_price_change_pct"] = (hwi[hwi.fy == 2026].net_amount_aud.sum() / hwi[hwi.fy == 2026].qty.sum()) / (hwi[hwi.fy == 2025].net_amount_aud.sum() / hwi[hwi.fy == 2025].qty.sum()) - 1
# like-for-like list prices: mean unit price per SKU FY25 vs FY26 (price held?)
pr = hwi.groupby(["fy", "sku"]).apply(lambda g: g.net_amount_aud.sum() / g.qty.sum()).unstack()
R["sku_price_change_pct"] = {s_: float(pr.loc[2026, s_] / pr.loc[2025, s_] - 1) for s_ in pr.columns}
# FX test: re-price FY26 non-AUD lines at FY25 average rate
fx = inv[inv.currency != "AUD"].dropna(subset=["fx_rate_used"])
fx25 = fx[fx.fy == 2025].groupby("currency").fx_rate_used.mean()
l26 = hwi[(hwi.fy == 2026) & (hwi.currency != "AUD")].dropna(subset=["fx_rate_used"])
at_old = (l26.net_amount_aud / l26.fx_rate_used * l26.currency.map(fx25)).sum()
R["fx_effect_fy26_hw"] = float(l26.net_amount_aud.sum() - at_old)
R["fx_rates"] = {f"{c_}_{fy}": float(fx[(fx.currency == c_) & (fx.fy == fy)].fx_rate_used.mean()) for c_ in ["EUR", "USD"] for fy in (2025, 2026)}
# breadth: OEM, tier, region
oem = pd.read_csv(D / "oem_master.csv")
po = hwi.pivot_table(index="oem_id", columns="fy", values="net_amount_aud", aggfunc="sum").fillna(0)
R["oem_breadth"] = dict(
    oems_buying_fy25=int((po[2025] > 0).sum()), oems_buying_fy26=int((po[2026] > 0).sum()),
    lost_oems=int(((po[2025] > 0) & (po[2026] == 0)).sum()),
    oems_down=int((po[2026] < po[2025]).sum()), oems_total=int(((po[2025] > 0) | (po[2026] > 0)).sum()),
    median_oem_change_pct=float(((po[2026] / po[2025]).replace([np.inf], np.nan).dropna()).median() - 1),
    top10_share_fy25=float(po[2025].nlargest(10).sum() / po[2025].sum()),
    top10_share_fy26=float(po[2026].nlargest(10).sum() / po[2026].sum()),
    top1_oem=str(po[2026].idxmax()), top1_fy25=float(po.loc[po[2025].idxmax(), 2025]), top1_fy26=float(po.loc[po[2025].idxmax(), 2026]),
)
byt = po.join(oem.set_index("oem_id").tier).groupby("tier")[[2025, 2026]].sum()
R["by_tier"] = {t: float(r_[2026] / r_[2025] - 1) for t, r_ in byt.iterrows()}
byr = hwi.pivot_table(index="sales_region", columns="fy", values="net_amount_aud", aggfunc="sum")
R["by_region"] = {t: float(r_[2026] / r_[2025] - 1) for t, r_ in byr.iterrows()}
bys = {s_: float(v["q1"] / v["q0"] - 1) for s_, v in b.items()}
R["by_sku_units"] = bys

# quarter-end loading
gm_ = hwi.groupby("p").net_amount_aud.sum()
def qe_split(fy):
    ms = [p for p in gm_.index if p.year + (1 if p.month >= 7 else 0) == fy]
    qe = sum(gm_[p] for p in ms if p.month in (3, 6, 9, 12)); ot = sum(gm_[p] for p in ms if p.month not in (3, 6, 9, 12))
    return qe, ot
qe25, ot25 = qe_split(2025); qe26, ot26 = qe_split(2026)
R["quarter_end"] = dict(qe25=float(qe25), ot25=float(ot25), qe26=float(qe26), ot26=float(ot26),
                        qe_share25=float(qe25 / (qe25 + ot25)), qe_share26=float(qe26 / (qe26 + ot26)),
                        qe_avg_month25=float(qe25 / 4), ot_avg_month25=float(ot25 / 8), qe_avg_month26=float(qe26 / 4), ot_avg_month26=float(ot26 / 8),
                        rebate_rate25=float(-reb25 / hwi[hwi.fy == 2025].net_amount_aud.sum()), rebate_rate26=float(-reb26 / hwi[hwi.fy == 2026].net_amount_aud.sum()))
# months with rebates
rb = inv[inv.product_family == "rebate"]
R["quarter_end"]["rebate_only_in_quarter_end_months"] = bool(rb.invoice_date.dt.month.isin([3, 6, 9, 12]).all())

# ---------------------------------------------------------------- orders / lead times
so = pd.read_csv(D / "sales_orders.csv", parse_dates=["order_date"], low_memory=False)
so["p"] = so.order_date.dt.to_period("M")
R["dq_orders_leadtime_missing"] = int(so.quoted_lead_time_weeks.isna().sum())
R["dq_orders_rows"] = int(len(so))
ordq = so.groupby("p").qty_ordered.sum()
lt = so.groupby("p").quoted_lead_time_weeks.mean()
# --------------------------------------------------------------- device activations (K2)
act = pd.read_csv(D / "device_activations.csv")
R["dq_act_rows"] = int(len(act))
act["ts"] = pd.to_datetime(act.first_seen_ts.str[:19])
act = act.sort_values("ts")
R["dq_act_dup_serials"] = int(act.device_serial.duplicated().sum())
R["dq_act_dup_serials_last24m"] = int(act[(act.ts >= "2024-10-01")].device_serial.duplicated().sum())
R["dq_act_oem_missing"] = int(act.oem_id.isna().sum())
code2oem = act.dropna(subset=["oem_id"]).drop_duplicates("manufacturer_code").set_index("manufacturer_code").oem_id
act["oem_fixed"] = act.oem_id.fillna(act.manufacturer_code.map(code2oem))
R["dq_act_oem_missing_after_backfill"] = int(act.oem_fixed.isna().sum())
R["dq_act_beta_rows"] = int((act.firmware_version.str.contains("beta")).sum())
R["dq_act_beta_in_last24m"] = int(((act.firmware_version.str.contains("beta")) & (act.ts >= "2024-10-01")).sum())
R["dq_act_model_string_free_text"] = True
a1 = act.drop_duplicates("device_serial", keep="first").copy()
a1["p"] = a1.ts.dt.to_period("M")
SC = 10
am = a1.groupby("p").size().reindex(pd.period_range("2021-01", AS_OF, freq="M")).fillna(0) * SC
raw = act.assign(p=act.ts.dt.to_period("M")).groupby("p").size().reindex(am.index).fillna(0) * SC
a_cur, a_pri = am[cur].sum(), am[pri].sum()
a_pri2 = am[pd.period_range(AS_OF - 35, AS_OF - 24, freq="M")].sum()
R["k2"] = dict(cur=float(a_cur), prior=float(a_pri), growth=float(a_cur / a_pri - 1), prior2=float(a_pri2),
               prior_growth=float(a_pri / a_pri2 - 1), raw_cur=float(raw[cur].sum()), raw_growth=float(raw[cur].sum() / raw[pri].sum() - 1),
               months_remaining=(pd.Period("2027-06", "M") - AS_OF).n)
gs = {str(p): float(r12(am)[p] / r12(am)[p - 12] - 1) for p in [AS_OF - 3, AS_OF - 2, AS_OF - 1, AS_OF]}
R["k2"]["growth_series"] = gs
R["k2"]["run_rate_pts_per_month"] = (gs[str(AS_OF)] - gs[str(AS_OF - 3)]) / 3 * 100
R["k2"]["pace_needed_pts_per_month"] = (0.05 - gs[str(AS_OF)]) / R["k2"]["months_remaining"] * 100
R["k2"]["fy_activations"] = {str(fy): float(am[[p for p in am.index if fy_of(p) == fy]].sum()) for fy in range(2022, 2027)}
R["k2"]["dup_rate_cur"] = float(1 - a1[a1.p.isin(cur)].shape[0] / act[act.ts.dt.to_period("M").isin(cur)].shape[0])
# by SKU group and country top changes
a1["grp"] = a1.module_sku.map(lambda s_: "chip" if s_.startswith("LW-C") else ("module" if s_.startswith("LW-M") else "card"))
sg = a1.groupby(["grp", "p"]).size().unstack(0).fillna(0) * SC
R["k2"]["by_group_growth"] = {g_: float(sg.loc[cur, g_].sum() / sg.loc[pri, g_].sum() - 1) for g_ in sg.columns}
cg = a1.groupby(["country_from_ip", "p"]).size().unstack(0).fillna(0) * SC
cgrow = (cg.loc[cur].sum() / cg.loc[pri].sum() - 1).dropna()
R["k2"]["country_growth_range"] = [float(cgrow.min()), float(cgrow.max())]
R["k2"]["countries_growing"] = int((cgrow > 0).sum()); R["k2"]["countries_total"] = int(len(cgrow))
top_c = cg.loc[pri].sum().nlargest(8).index
R["k2"]["top_country_growth"] = {c_: float(cg.loc[cur, c_].sum() / cg.loc[pri, c_].sum() - 1) for c_ in top_c}
og = a1.dropna(subset=["oem_fixed"]).groupby(["oem_fixed", "p"]).size().unstack(0).fillna(0) * SC
ogr = (og.loc[cur].sum() / og.loc[pri].sum()).replace([np.inf, -np.inf], np.nan).dropna() - 1
R["k2"]["oems_growing"] = int((ogr > 0).sum()); R["k2"]["oems_total"] = int(len(ogr)); R["k2"]["oem_growth_median"] = float(ogr.median())
# the same by quarter in the FY (seasonality): last 8 quarters of activations
R["k2"]["quarterly"] = {f"FY{str(fy_of(p))[2:]}-Q{((p.month - 7) % 12) // 3 + 1}": 0 for p in am.index[-24:]}
for p in am.index[-24:]:
    R["k2"]["quarterly"][f"FY{str(fy_of(p))[2:]}-Q{((p.month - 7) % 12) // 3 + 1}"] += float(am[p])
# counter-metric: duplicate serial share
R["k2"]["dup_serial_share_all"] = float(R["dq_act_dup_serials"] / len(act))

# sell-in vs install
units_inv = hwi.groupby("p").qty.sum().reindex(am.index).fillna(0)
def fysum(s, fy): return float(s[[p for p in s.index if fy_of(p) == fy]].sum())
R["sellin_vs_installs"] = {str(fy): dict(invoiced=fysum(units_inv, fy), activated=fysum(am, fy), ratio=fysum(units_inv, fy) / fysum(am, fy)) for fy in (2023, 2024, 2025, 2026)}
R["sellin_vs_installs"]["R12M"] = dict(invoiced=float(units_inv[cur].sum()), activated=float(am[cur].sum()), ratio=float(units_inv[cur].sum() / am[cur].sum()))
R["sellin_vs_installs"]["R12M_prior"] = dict(invoiced=float(units_inv[pri].sum()), activated=float(am[pri].sum()), ratio=float(units_inv[pri].sum() / am[pri].sum()))
mr = (units_inv / am)
R["sellin_vs_installs"]["ratio_jan25_jun25_avg"] = float(units_inv[pd.period_range("2025-01", "2025-06", freq="M")].sum() / am[pd.period_range("2025-01", "2025-06", freq="M")].sum())
R["sellin_vs_installs"]["ratio_jul25_sep26_avg"] = float(units_inv[pd.period_range("2025-07", "2026-09", freq="M")].sum() / am[pd.period_range("2025-07", "2026-09", freq="M")].sum())
R["sellin_vs_installs"]["cum_excess_jul22_jun25"] = float(units_inv[pd.period_range("2022-07", "2025-06", freq="M")].sum() - am[pd.period_range("2022-07", "2025-06", freq="M")].sum())
R["sellin_vs_installs"]["ratio_fy22"] = fysum(units_inv, 2022) / fysum(am, 2022)
# timeline for the story: orders and lead time at key points
tl = {}
for lab, per in {"2022-06": "2022-06", "2023-03": "2023-03", "2023-09": "2023-09", "2023-12": "2023-12", "2024-03": "2024-03", "2024-09": "2024-09", "2025-06": "2025-06", "2025-09": "2025-09", "2026-09": "2026-09"}.items():
    pp = pd.Period(per, "M")
    tl[lab] = dict(ord_qty=float(ordq.get(pp, np.nan)), lead_wk=float(lt.get(pp, np.nan)), inv_qty=float(units_inv.get(pp, np.nan)), act=float(am.get(pp, np.nan)))
R["timeline"] = tl
R["orders_drought"] = dict(
    ord_qty_oct23_jun24=float(ordq[pd.period_range("2023-10", "2024-06", freq="M")].sum()),
    ord_qty_prev9m=float(ordq[pd.period_range("2023-01", "2023-09", freq="M")].sum()),
    lead_before=float(lt[pd.Period("2023-03", "M")]), lead_after=float(lt[pd.Period("2024-06", "M")]))
R["orders_drought"]["backlog_invoiced_jul24_jun25_vs_orders"] = float(units_inv[pd.period_range("2024-07", "2025-06", freq="M")].sum())

# ---------------------------------------------------------------- inventory (K2.1)
inb = pd.read_csv(D / "oem_inventory_reports.csv")
def norm(s):
    s = re.sub(r"[-\s]+", " ", s.strip().lower()).replace(".", "").replace(",", "")
    return re.sub(r"( co ltd| gmbh| inc| ltd| pty ltd)$", "", s)
inb["k"] = inb.oem_name.map(norm)
oem["k"] = oem.oem_name.map(norm)
mm = oem.drop_duplicates("k").set_index("k").oem_id
inb["oem_id"] = inb.k.map(mm)
R["dq_inv_reports_rows"] = int(len(inb))
R["dq_inv_reports_unmatched"] = int(inb.oem_id.isna().sum())
R["dq_inv_reports_raw_names"] = int(inb.oem_name.nunique())
R["dq_inv_reports_clean_names"] = int(inb.k.nunique())
R["dq_inv_reports_kinds"] = dict(reel=int(inb.sku_or_description.str.contains("reel").sum()), finished=int(inb.sku_or_description.str.contains("finished").sum()))
inb["units"] = np.where(inb.sku_or_description.str.contains("reel"), inb.units_on_hand * 500, inb.units_on_hand)
inb["kind"] = np.where(inb.sku_or_description.str.contains("finished"), "finished", "parts")
dupk = inb.duplicated(["k", "quarter", "sku_or_description"]).sum()
R["dq_inv_reports_dups"] = int(dupk)
qtot = inb.groupby("quarter").agg(units=("units", "sum"), oems=("k", "nunique"))
qparts = inb[inb.kind == "parts"].groupby("quarter").units.sum()
R["inv_by_quarter"] = {q: dict(units=float(r_.units), oems=int(r_.oems), parts=float(qparts.get(q, 0))) for q, r_ in qtot.iterrows()}
# like for like: OEMs reporting in both quarters
oq = {q: set(g.k) for q, g in inb.groupby("quarter")}
qs_ = sorted(oq)
lfl = {}
for a_, b_ in zip(qs_[:-1], qs_[1:]):
    both = oq[a_] & oq[b_]
    ua = inb[(inb.quarter == a_) & inb.k.isin(both)].units.sum(); ub = inb[(inb.quarter == b_) & inb.k.isin(both)].units.sum()
    lfl[b_] = dict(prev=a_, both=len(both), prev_units=float(ua), units=float(ub), change=float(ub / ua - 1))
R["inv_lfl"] = lfl
pk = max(R["inv_by_quarter"].items(), key=lambda kv: kv[1]["units"])
R["inv_peak"] = dict(quarter=pk[0], units=pk[1]["units"], oems=pk[1]["oems"])
# like-for-like peak to latest for OEMs reporting in both 2025-Q2 and 2026-Q3
both = oq["2025-Q2"] & oq["2026-Q3"]
u0 = inb[(inb.quarter == "2025-Q2") & inb.k.isin(both)].units.sum(); u1 = inb[(inb.quarter == "2026-Q3") & inb.k.isin(both)].units.sum()
R["inv_lfl_peak_to_latest"] = dict(oems=len(both), peak=float(u0), latest=float(u1), change=float(u1 / u0 - 1))
# months of cover: latest stock / monthly installs of those OEMs? (approx: all-OEM installs not matched) -> use total
R["inv_share_of_oems"] = dict(reporting_latest=int(R["inv_by_quarter"]["2026-Q3"]["oems"]), total_active=int(oem[oem.status == "active"].shape[0]))
# top-40 coverage: share of FY26 hardware invoices from the reporting OEMs
rep_ids = set(inb.oem_id.dropna())
R["inv_coverage_fy26_hw_share"] = float(po.loc[po.index.isin(rep_ids), 2026].sum() / po[2026].sum())
# stock vs units shipped to those OEMs (months of cover at latest quarter)
shipped_rep_q = hwi[hwi.oem_id.isin(rep_ids) & (hwi.p >= pd.Period("2026-07", "M"))].qty.sum()
R["inv_months_cover_latest"] = float(R["inv_by_quarter"]["2026-Q3"]["units"] / (shipped_rep_q / 3))
shipped_rep_pk = hwi[hwi.oem_id.isin(rep_ids) & (hwi.p >= pd.Period("2025-04", "M")) & (hwi.p <= pd.Period("2025-06", "M"))].qty.sum()
R["inv_months_cover_peak"] = float(R["inv_by_quarter"]["2025-Q2"]["units"] / (shipped_rep_pk / 3))
# are stock reports tied to the OEM drop-offs? names unmatched count
R["inv_notes_counts"] = inb.notes.fillna("none").value_counts().to_dict()

# ---------------------------------------------------------------- CRM (K4, K4.1, K4.2)
crm = pd.read_csv(D / "crm_opportunities.csv")
st = [c_ for c_ in crm.columns if c_.startswith("stage_entered")]
for s_ in st:
    crm[s_] = pd.to_datetime(crm[s_])
dwd, shd = crm.stage_entered_design_win, crm.stage_entered_shipping
R["dq_crm_rows"] = int(len(crm))
R["dq_crm_no_dates_at_all"] = int(crm[st].isna().all(axis=1).sum())
undated = crm[crm[st].isna().all(axis=1)]
R["dq_crm_undated_last_modified_max"] = str(undated.last_modified.max())
R["dq_crm_undated_by_stage"] = undated.stage.value_counts().to_dict()
passed_dw_stage = ["design_win", "pre_production", "shipping"]
R["dq_crm_undated_past_dw_stage"] = int(undated.stage.isin(passed_dw_stage).sum())
R["dq_crm_dated_dw"] = int(dwd.notna().sum())
R["dq_crm_ship_without_dw_date"] = int((shd.notna() & dwd.isna()).sum())
R["dq_crm_dw_without_design_in"] = int((dwd.notna() & crm.stage_entered_design_in.isna()).sum())
R["dq_crm_dw_without_design_in_after_2023q2"] = int((dwd.notna() & crm.stage_entered_design_in.isna() & (dwd >= "2023-07-01")).sum())
R["dq_crm_stage_dates_start"] = str(min(crm[st].min()).date())
R["dq_crm_missing_share_k4"] = float(R["dq_crm_undated_past_dw_stage"] / (R["dq_crm_undated_past_dw_stage"] + R["dq_crm_dated_dw"]))

cut = AS_OF_DATE - pd.DateOffset(months=18)
R["k4_cutoff"] = str(cut.date())
dw = crm[dwd.notna()].copy()
dw["q"] = dw.stage_entered_design_win.dt.to_period("Q")
dw["fq"] = dw.stage_entered_design_win.dt.to_period("Q-JUN")
dw["shipped"] = dw.stage_entered_shipping.notna()
dw["lost_after"] = dw.stage_entered_lost.notna()
dw["days"] = (dw.stage_entered_shipping - dw.stage_entered_design_win).dt.days
mature = dw[dw.stage_entered_design_win <= cut]
R["k4"] = dict(n=int(len(mature)), shipped=int(mature.shipped.sum()), rate=float(mature.shipped.mean()),
               lost=int(mature.lost_after.sum()), lost_rate=float(mature.lost_after.mean()),
               still_open=int((~mature.shipped & ~mature.lost_after).sum()),
               cohort_from=str(mature.stage_entered_design_win.min().date()), cohort_to=str(mature.stage_entered_design_win.max().date()))
# shipped within 18m of DW for same mature cohort
R["k4"]["shipped_within_18m"] = int((mature.days <= 548).sum()); R["k4"]["rate_within_18m"] = float((mature.days <= 548).sum() / len(mature))
R["k4"]["rate_within_24m_cohort"] = None
m24 = dw[dw.stage_entered_design_win <= (AS_OF_DATE - pd.DateOffset(months=24))]
R["k4"]["rate_within_24m_cohort"] = float((m24.days <= 730).sum() / len(m24))
R["k4"]["n24"] = int(len(m24))
# stricter reading: gary's draft counts shipped without a DW date? separate. Also sensitivity: undated count in
R["k4"]["sens_include_undated_stage_based"] = float(
    (mature.shipped.sum() + (undated.stage == "shipping").sum()) /
    (len(mature) + undated.stage.isin(passed_dw_stage).sum()))
R["k4"]["months_remaining"] = (pd.Period("2027-06", "M") - AS_OF).n
# cohort table by FY quarter
rows = []
for q, g in dw.groupby("fq"):
    row = dict(q=str(q), n=int(len(g)), shipped=int(g.shipped.sum()), lost=int(g.lost_after.sum()),
               age_months=float(((AS_OF_DATE - g.stage_entered_design_win.mean()).days) / 30.44))
    for lab, d_ in (("s12", 365), ("s18", 548)):
        eligible = g[g.stage_entered_design_win <= AS_OF_DATE - pd.Timedelta(days=d_)]
        row[lab] = float((eligible.days <= d_).sum() / len(eligible)) if len(eligible) == len(g) else None
    row["pp12"] = None
    ge = g[g.stage_entered_design_win <= AS_OF_DATE - pd.Timedelta(days=365)]
    if len(ge) == len(g):
        row["pp12"] = float(((ge.stage_entered_pre_production - ge.stage_entered_design_win).dt.days <= 365).sum() / len(ge))
        row["lost12"] = float(((ge.stage_entered_lost - ge.stage_entered_design_win).dt.days <= 365).sum() / len(ge))
    else:
        row["lost12"] = None
    row["rev_median"] = float(g.est_annual_revenue_aud.median())
    row["last_week_share"] = float((g.stage_entered_design_win >= (g.stage_entered_design_win.dt.to_period("Q").dt.end_time.dt.normalize() - pd.Timedelta(days=6))).mean())
    row["no_design_in"] = float(g.stage_entered_design_in.isna().mean())
    rows.append(row)
R["k4_cohorts"] = rows
R["k4_fy_dw"] = {}
for fy in (2023, 2024, 2025, 2026):
    R["k4_fy_dw"][str(fy)] = int(((dwd >= f"{fy-1}-07-01") & (dwd < f"{fy}-07-01")).sum())
R["k4_fy_dw"]["FY27_Q1"] = int(((dwd >= "2026-07-01") & (dwd <= "2026-09-30")).sum())
# bonus test: cohorts before vs after bonus start (FY25 = 1 Jul 2024), equal age (12 months) so no censoring
bonus = pd.Timestamp("2024-07-01")
elig12 = dw[dw.stage_entered_design_win <= AS_OF_DATE - pd.Timedelta(days=365)]
elig18 = dw[dw.stage_entered_design_win <= AS_OF_DATE - pd.Timedelta(days=548)]
def split(df, days):
    pre = df[df.stage_entered_design_win < bonus]; post = df[df.stage_entered_design_win >= bonus]
    f = lambda g: float((g.days <= days).sum() / len(g)) if len(g) else None
    return dict(pre_n=int(len(pre)), post_n=int(len(post)), pre=f(pre), post=f(post))
R["k4_bonus_test"] = dict(ship12=split(elig12, 365), ship18=split(elig18, 548))
# first-half-of-year only to keep pre/post fair seasonally: pre = Jan23-Jun24 (18m) vs post = Jul24-Sep25
# lost and pre-production progression within 12m, pre vs post
def prog(df, col, days):
    pre = df[df.stage_entered_design_win < bonus]; post = df[df.stage_entered_design_win >= bonus]
    f = lambda g: float(((g[col] - g.stage_entered_design_win).dt.days <= days).sum() / len(g))
    return dict(pre=f(pre), post=f(post))
R["k4_bonus_test"]["preprod12"] = prog(elig12, "stage_entered_pre_production", 365)
R["k4_bonus_test"]["lost12"] = prog(elig12, "stage_entered_lost", 365)
R["k4_bonus_test"]["dw_per_quarter_pre"] = float(len(dw[(dw.stage_entered_design_win < bonus) & (dw.stage_entered_design_win >= "2023-01-01")]) / 6)
R["k4_bonus_test"]["dw_per_quarter_post"] = float(len(dw[(dw.stage_entered_design_win >= bonus)]) / (len(pd.period_range("2024Q3", "2026Q3", freq="Q"))))
R["k4_bonus_test"]["est_rev_median_pre"] = float(dw[dw.stage_entered_design_win < bonus].est_annual_revenue_aud.median())
R["k4_bonus_test"]["est_rev_median_post"] = float(dw[dw.stage_entered_design_win >= bonus].est_annual_revenue_aud.median())
R["k4_bonus_test"]["last_week_share_pre"] = float((dw[dw.stage_entered_design_win < bonus].stage_entered_design_win >= (dw[dw.stage_entered_design_win < bonus].stage_entered_design_win.dt.to_period("Q").dt.end_time.dt.normalize() - pd.Timedelta(days=6))).mean())
post_ = dw[dw.stage_entered_design_win >= bonus]
R["k4_bonus_test"]["last_week_share_post"] = float((post_.stage_entered_design_win >= (post_.stage_entered_design_win.dt.to_period("Q").dt.end_time.dt.normalize() - pd.Timedelta(days=6))).mean())
R["k4_bonus_test"]["last_week_expected"] = 7 / 91
R["k4_bonus_payout_estimate_fy26"] = float(R["k4_fy_dw"]["2026"] * 400)
R["k4_bonus_payout_lost_after_dw_fy26_cohort"] = int(dw[(dw.stage_entered_design_win >= "2025-07-01") & (dw.stage_entered_design_win < "2026-07-01")].lost_after.sum()) * 400
R["k4_bonus_dw_fy26_not_shipped"] = int(((dw.stage_entered_design_win >= "2025-07-01") & (dw.stage_entered_design_win < "2026-07-01") & ~dw.shipped).sum())
# spread: owners / region / tier  (mature cohort)
oemt = oem.set_index("oem_id").tier
mature = mature.assign(tier=mature.oem_id.map(oemt))
R["k4_spread"] = dict(
    owner_min=float(mature.groupby("owner").shipped.mean().min()), owner_max=float(mature.groupby("owner").shipped.mean().max()),
    owners=int(mature.owner.nunique()),
    region={k: float(v) for k, v in mature.groupby("region").shipped.mean().items()},
    tier={k: float(v) for k, v in mature.groupby("tier").shipped.mean().items()})
og_ = mature.groupby("owner").agg(n=("shipped", "size"), r=("shipped", "mean"))
og_ = og_[og_.n >= 50]
R["k4_spread"]["owner_min"] = float(og_.r.min()); R["k4_spread"]["owner_max"] = float(og_.r.max()); R["k4_spread"]["owner_n_min"] = int(og_.n.min())
# OEM health: do design-win OEMs still order? OEM level cross-check of shipped opps (invoices after ship date)
last_inv = hwi.groupby("oem_id").invoice_date.max()
sh = mature[mature.shipped]
R["k4_invoice_crosscheck"] = dict(
    shipped_opps=int(len(sh)),
    oem_invoiced_after_ship=int((sh.apply(lambda r_: last_inv.get(r_.oem_id, pd.NaT) >= r_.stage_entered_shipping, axis=1)).sum()))
# K4.1: months to ship by DW cohort, only cohorts with >=18 months age; also show censoring
dw["months"] = dw.days / 30.44
mm_ = dw[dw.shipped & (dw.stage_entered_design_win <= cut)]
R["k4_1"] = dict(median=float(mm_.months.median()), p25=float(mm_.months.quantile(.25)), p75=float(mm_.months.quantile(.75)), n=int(len(mm_)),
                 all_shipped_median=float(dw[dw.shipped].months.median()), all_n=int(dw.shipped.sum()))
byc = []
for q, g in dw[dw.stage_entered_design_win <= cut].groupby("fq"):
    s_ = g[g.shipped]
    byc.append(dict(q=str(q), n_ship=int(len(s_)), median=float(s_.months.median()) if len(s_) else None))
R["k4_1"]["by_cohort"] = byc
cohort_series = [b_["median"] for b_ in byc if b_["median"] is not None]
R["k4_1"]["run_rate_months_per_cohort"] = float((cohort_series[-1] - cohort_series[-4]) / 3)
# K4.2 design wins per FY quarter
dwq = dw.groupby("fq").size()
R["k4_2"] = {str(k): int(v) for k, v in dwq.items()}
R["k4_2_fy26"] = R["k4_fy_dw"]["2026"]; R["k4_2_fy25"] = R["k4_fy_dw"]["2025"]
R["k4_2_run_rate_per_quarter"] = float((dwq.iloc[-2] - dwq.iloc[-5]) / 3)

# ---------------------------------------------------------------- forecasts (K5)
fc = pd.read_csv(D / "forecast_snapshots.csv", parse_dates=["snapshot_date"])
fc["aud"] = np.where(fc.amount_unit.str.contains("thousand"), fc.forecast_amount * 1000, fc.forecast_amount)
R["dq_fc_rows"] = int(len(fc))
R["dq_fc_thousands_rows"] = int(fc.amount_unit.str.contains("thousand").sum())
R["dq_fc_thousands_quarters"] = sorted(fc[fc.amount_unit.str.contains("thousand")].forecast_quarter.unique().tolist())
def qstart(q):
    fy = 2000 + int(q[2:4]); n = int(q[-1])
    return pd.Timestamp(year=fy - 1 if n <= 2 else fy, month={1: 7, 2: 10, 3: 1, 4: 4}[n], day=1)
def qmonths(q):
    s_ = qstart(q); return pd.period_range(s_.to_period("M"), periods=3, freq="M")
fc["qstart"] = fc.forecast_quarter.map(qstart)
fam_series = dict(hardware=m_hw, software=m_sw, other=m_oth)
rows = []
for (src, q, fam), g in fc.groupby(["source", "forecast_quarter", "product_family"]):
    qs_start = qstart(q)
    g2 = g[g.snapshot_date <= qs_start]
    if g2.empty:
        continue
    snap = g2.sort_values("snapshot_date").iloc[-1]
    ms = qmonths(q)
    if ms[-1] > AS_OF:
        continue
    actual = fam_series[fam][ms].sum()
    rows.append(dict(source=src, q=q, fam=fam, snap=str(snap.snapshot_date.date()), forecast=float(snap.aud), actual=float(actual),
                     days_before=int((qs_start - snap.snapshot_date).days)))
f = pd.DataFrame(rows)
tot = f.groupby(["source", "q"]).agg(forecast=("forecast", "sum"), actual=("actual", "sum"), n=("fam", "size")).reset_index()
tot = tot[tot.n == 3]
tot["err"] = (tot.forecast - tot.actual) / tot.actual
tot["abs_err"] = tot.err.abs()
qorder = sorted(tot.q.unique(), key=lambda s_: (s_[2:4], s_[-1]))
R["k5_quarters"] = {s_: {r_.q: dict(forecast=float(r_.forecast), actual=float(r_.actual), err=float(r_.err)) for r_ in tot[tot.source == s_].itertuples()} for s_ in ["finance_run_rate", "sales_crm"]}
R["k5_order"] = qorder
# missing snapshots
exp_ = {(s_, q) for s_ in ["finance_run_rate", "sales_crm"] for q in qorder}
have = {(r_.source, r_.q) for r_ in tot.itertuples()}
R["k5_missing_source_quarters"] = sorted([f"{s_} {q}" for s_, q in exp_ - have])
# also fc quarters with a complete set missing family
R["dq_fc_late_snapshots"] = fc[(fc.snapshot_date > fc.qstart)].groupby(["source", "forecast_quarter"]).size().reset_index().to_dict("records")
R["dq_fc_late_snapshots"] = [dict(source=a_["source"], q=a_["forecast_quarter"]) for a_ in R["dq_fc_late_snapshots"]]
R["dq_fc_snapshot_days_before_start"] = [int(f.days_before.min()), int(f.days_before.max())]
last4 = qorder[-4:]
R["k5_last4"] = last4
R["k5"] = {}
for s_ in ["finance_run_rate", "sales_crm"]:
    t = tot[tot.source == s_].set_index("q")
    R["k5"][s_] = dict(latest_q=qorder[-1], latest_err=float(t.loc[qorder[-1], "err"]) if qorder[-1] in t.index else None,
                       latest_abs=float(t.loc[qorder[-1], "abs_err"]) if qorder[-1] in t.index else None,
                       mape_last4=float(t.loc[[q for q in last4 if q in t.index], "abs_err"].mean()),
                       mape_all=float(t.abs_err.mean()), n_all=int(len(t)),
                       within5=int((t.abs_err <= 0.05).sum()), within10=int((t.abs_err <= 0.10).sum()),
                       bias_all=float(t.err.mean()), bias_last4=float(t.loc[[q for q in last4 if q in t.index], "err"].mean()),
                       worst_q=str(t.abs_err.idxmax()), worst_err=float(t.loc[t.abs_err.idxmax(), "err"]))
# the unit trap
nofix = fc.copy(); nofix["raw"] = nofix.forecast_amount
raw_tot = nofix[nofix.source == "finance_run_rate"].groupby("forecast_quarter").raw.sum()
R["k5_unit_trap"] = dict(fy24q1_raw=float(raw_tot["FY24-Q1"]), fy24q1_fixed=float(tot[(tot.source == "finance_run_rate") & (tot.q == "FY24-Q1")].forecast.iloc[0]))
# error by family, finance & sales, mean abs (all quarters)
fam_err = f.assign(abs_err=lambda d: ((d.forecast - d.actual) / d.actual).abs(), err=lambda d: (d.forecast - d.actual) / d.actual)
R["k5_family"] = {s_: {fam: dict(mape=float(g.abs_err.mean()), bias=float(g.err.mean())) for fam, g in d.groupby("fam")} for s_, d in fam_err.groupby("source")}
# last 4 quarters by family
fl = fam_err[fam_err.q.isin(last4)]
R["k5_family_last4"] = {s_: {fam: dict(mape=float(g.abs_err.mean()), bias=float(g.err.mean())) for fam, g in d.groupby("fam")} for s_, d in fl.groupby("source")}
# naive tests: is Finance = last quarter's actual (run rate)? compare error of forecast vs prior-quarter actual
def prev_q_actual(q, fam):
    n = int(q[-1]); fy = int(q[2:4])
    pq = f"FY{fy}-Q{n-1}" if n > 1 else f"FY{fy-1}-Q4"
    ms = qmonths(pq); return fam_series[fam][ms].sum()
fin = f[f.source == "finance_run_rate"].copy()
fin["prev_actual"] = [prev_q_actual(r_.q, r_.fam) for r_ in fin.itertuples()]
fin["f_vs_prev"] = (fin.forecast - fin.prev_actual) / fin.prev_actual
fin["naive_err"] = (fin.prev_actual - fin.actual) / fin.actual
fin["err"] = (fin.forecast - fin.actual) / fin.actual
# totals by quarter
ft = fin.groupby("q").agg(forecast=("forecast", "sum"), prev=("prev_actual", "sum"), actual=("actual", "sum"))
ft["f_vs_prev"] = ft.forecast / ft.prev - 1
ft["naive_err"] = ft.prev / ft.actual - 1
ft["err"] = ft.forecast / ft.actual - 1
R["k5_finance_vs_naive"] = {q: dict(f_vs_prev=float(r_.f_vs_prev), naive_err=float(r_.naive_err), err=float(r_.err)) for q, r_ in ft.iterrows()}
R["k5_naive"] = dict(finance_mape_all=float(ft.err.abs().mean()), naive_mape_all=float(ft.naive_err.abs().mean()),
                     median_abs_gap_to_prev=float(ft.f_vs_prev.abs().median()),
                     corr=float(np.corrcoef(ft.forecast, ft.prev)[0, 1]),
                     finance_mape_ex_break=float(ft.drop(index=[q for q in ["FY26-Q1", "FY27-Q1"] if q in ft.index]).err.abs().mean()),
                     naive_mape_ex_break=float(ft.drop(index=[q for q in ["FY26-Q1", "FY27-Q1"] if q in ft.index]).naive_err.abs().mean()))
# Software lumpiness: finance and sales software error vs size of top licence in quarter
swq = f[f.fam == "software"].copy(); swq["err"] = (swq.forecast - swq.actual) / swq.actual
R["k5_software_miss_q"] = {f"{r_.source} {r_.q}": float(r_.err) for r_ in swq.itertuples() if abs(r_.err) > 0.4}
# the Mar-26 quarter (FY26-Q3 Jan-Mar 26 includes Corvane). Actual FY26-Q3 software:
R["k5_fy26q3_software_actual"] = float(m_sw[qmonths("FY26-Q3")].sum())
R["k5_fy26q3_software_actual_ex"] = float(m_sw[qmonths("FY26-Q3")].sum() - ex)
for src in ("finance_run_rate", "sales_crm"):
    r_ = f[(f.source == src) & (f.q == "FY26-Q3") & (f.fam == "software")].iloc[0]
    R.setdefault("k5_fy26q3_software", {})[src] = dict(forecast=float(r_.forecast), err=float((r_.forecast - r_.actual) / r_.actual), err_ex=float((r_.forecast - (r_.actual - ex)) / (r_.actual - ex)))
# Structural break: Jul 2025 hardware halved
R["k5_break"] = {src: dict(forecast=float(f[(f.source == src) & (f.q == "FY26-Q1") & (f.fam == "hardware")].forecast.iloc[0]),
                           actual=float(f[(f.source == src) & (f.q == "FY26-Q1") & (f.fam == "hardware")].actual.iloc[0])) for src in ("finance_run_rate", "sales_crm")}
R["k5_fy27q1_hw"] = {src: dict(forecast=float(f[(f.source == src) & (f.q == "FY27-Q1") & (f.fam == "hardware")].forecast.iloc[0]),
                               actual=float(f[(f.source == src) & (f.q == "FY27-Q1") & (f.fam == "hardware")].actual.iloc[0])) for src in ("finance_run_rate", "sales_crm")}
# rolling-year forecast-vs-actual scale for Finance FY27-Q1 hardware relative to prior-year same quarter
R["k5_fin_fy27q1_hw_vs_actual_prev_q"] = float(m_hw[qmonths("FY26-Q4")].sum())
# Sales vs Finance disagreement per quarter (difference as % of actual)
sf = f.pivot_table(index=["q", "fam"], columns="source", values="forecast").dropna()
sfq = sf.groupby("q").sum()
act_q = f.drop_duplicates(["q", "fam"]).groupby("q").actual.sum()
R["k5_disagreement"] = {q: float((sfq.loc[q, "sales_crm"] - sfq.loc[q, "finance_run_rate"]) / act_q[q]) for q in sfq.index}
R["k5_disagreement_median_abs"] = float(np.median([abs(v) for v in R["k5_disagreement"].values()]))
R["k5_target_checks"] = dict(latest_q=qorder[-1], months_remaining=(pd.Period("2027-06", "M") - AS_OF).n)
# best of the two (blended) as an option
bl = tot.pivot(index="q", columns="source", values="forecast")
bl = bl.dropna()
ac = tot.drop_duplicates("q").set_index("q").actual
R["k5_blend_mape"] = float((((bl.mean(axis=1) - ac.loc[bl.index]) / ac.loc[bl.index]).abs()).mean())
R["k5_blend_quarters"] = int(len(bl))
R["k5_sales_mape_same_quarters"] = float((((bl.sales_crm - ac.loc[bl.index]) / ac.loc[bl.index]).abs()).mean())
R["k5_finance_mape_same_quarters"] = float((((bl.finance_run_rate - ac.loc[bl.index]) / ac.loc[bl.index]).abs()).mean())

# ---------------------------------------------------------------- headcount / incentive context (reference)
inc = pd.read_csv(D / "sales_incentive_plans.csv")
R["incentive"] = inc[inc.component == "Design wins"].iloc[0].to_dict()

# ---------------------------------------------------------------- targets sanity (K1 and board targets)
R["board_targets"] = pd.read_csv(D / "board_targets.csv").to_dict("records")

# --------------------------------------------------------------- heartbeat: not used (coverage check only)
hb = pd.read_csv(D / "device_heartbeat_monthly.csv", usecols=["device_serial", "month"])
R["dq_heartbeat_rows"] = int(len(hb))
R["dq_heartbeat_devices"] = int(hb.device_serial.nunique())
R["dq_heartbeat_first_month"] = str(hb.month.min())

# ================================================================ ADDITIONAL TESTS (second pass)
# --- K1: "nothing changes" illustration and plan checks
jun27 = pd.period_range("2026-10", "2027-06", freq="M")
same_as_last_year = sum(m_tot[p - 12] for p in jun27)
flat_ex_big = m_tot[pd.period_range("2026-07", "2026-09", freq="M")].sum() + same_as_last_year - ex
R["k1_flat_jun27"] = dict(value=float(flat_ex_big), with_big=float(flat_ex_big + ex), path=float(R["path_at_jun27"]),
                          gap_to_path=float(flat_ex_big - R["path_at_jun27"]))
R["plan_corvane_repeat"] = float(ex * 1.15)
R["plan_ex_corvane_repeat"] = float(R["plan_fy27_total"] - ex * 1.15)
R["k1_hw_q_vs_prior_year"] = {"FY27-Q1": float(qs["FY27-Q1"]["hw"] / qs["FY26-Q1"]["hw"] - 1),
                              "FY26-Q4": float(qs["FY26-Q4"]["hw"] / qs["FY25-Q4"]["hw"] - 1)}
R["k1_hw_q_vs_prev_q"] = {"FY26-Q1": float(qs["FY26-Q1"]["hw"] / qs["FY25-Q4"]["hw"] - 1),
                          "FY27-Q1": float(qs["FY27-Q1"]["hw"] / qs["FY26-Q4"]["hw"] - 1)}
R["hw_q_series"] = {q: float(v["hw"]) for q, v in qs.items()}

# --- K1 RCA: split of hardware volume change into installs (end demand) and the sell-in gap
av25 = hwi[hwi.fy == 2025].net_amount_aud.sum() / hwi[hwi.fy == 2025].qty.sum()
d_units = R["bridge_fy25_fy26"]["hw_units_fy26"] - R["bridge_fy25_fy26"]["hw_units_fy25"]
d_inst = R["k2"]["fy_activations"]["2026"] - R["k2"]["fy_activations"]["2025"]
R["hw_volume_split"] = dict(units_change=float(d_units), installs_change=float(d_inst), gap_change=float(d_units - d_inst),
                            avg_price_fy25=float(av25), installs_effect=float(d_inst * av25), gap_effect=float((d_units - d_inst) * av25),
                            total_effect_at_avg_price=float(d_units * av25), sku_level_volume_effect=R["bridge_fy25_fy26"]["hw_volume"])
R["hw_volume_split"]["installs_share_of_units_fall"] = float(d_inst / d_units)

# --- K2: year-on-year by quarter and month (the turn)
qa = a1.assign(q=a1.p.dt.asfreq("Q-JUN")).groupby("q").size()
qy = (qa / qa.shift(4) - 1).dropna()
R["k2_quarter_yoy"] = {f"FY{str(q.qyear)[2:]}-Q{q.quarter}": float(v) for q, v in qy.items() if q >= pd.Period("2025Q1", "Q-JUN")}
ma = a1.groupby("p").size()
my = (ma / ma.shift(12) - 1).dropna()
R["k2_month_yoy_2025_07_to_2026_06_mean"] = float(my[pd.period_range("2025-07", "2026-06", freq="M")].mean())
R["k2_month_yoy_2024_07_to_2025_06_mean"] = float(my[pd.period_range("2024-07", "2025-06", freq="M")].mean())
R["k2_latest_q_units"] = float(am[pd.period_range("2026-07", "2026-09", freq="M")].sum())
R["k2_latest_q_prior_units"] = float(am[pd.period_range("2025-07", "2025-09", freq="M")].sum())
R["k2_latest_q_yoy_exact"] = R["k2_latest_q_units"] / R["k2_latest_q_prior_units"] - 1
dip_cur = pd.period_range("2025-07", "2026-06", freq="M"); dip_pri = pd.period_range("2024-07", "2025-06", freq="M")
cgd = (cg.loc[dip_cur].sum() / cg.loc[dip_pri].sum() - 1)
R["k2_dip_breadth"] = dict(countries_down=int((cgd < 0).sum()), countries=int(len(cgd)), min=float(cgd.min()), max=float(cgd.max()),
                           groups={g_: float(sg.loc[dip_cur, g_].sum() / sg.loc[dip_pri, g_].sum() - 1) for g_ in sg.columns})
latest_q = pd.period_range("2026-07", "2026-09", freq="M"); prior_q = pd.period_range("2025-07", "2025-09", freq="M")
cgl = (cg.loc[latest_q].sum() / cg.loc[prior_q].sum() - 1)
R["k2_latest_q_breadth"] = dict(countries_up=int((cgl > 0).sum()), countries=int(len(cgl)))
fwm = a1.groupby(["p", "firmware_version"]).size().unstack().fillna(0)
R["k2_firmware_first_month"] = {v: str(fwm.index[(fwm[v] > 0).argmax()]) for v in fwm.columns}
R["k2_dup_share_by_period"] = dict(
    dip=float(1 - a1[a1.p.isin(dip_cur)].shape[0] / act[act.ts.dt.to_period("M").isin(dip_cur)].shape[0]),
    before=float(1 - a1[a1.p.isin(dip_pri)].shape[0] / act[act.ts.dt.to_period("M").isin(dip_pri)].shape[0]))
yq = a1.assign(fy=a1.p.apply(fy_of), qn=a1.p.apply(lambda p: ((p.month - 7) % 12) // 3 + 1)).groupby(["fy", "qn"]).size().unstack()
R["k2_quarter_share_of_year"] = {str(fy): [float(x) for x in (r_ / r_.sum()).round(4)] for fy, r_ in yq.loc[2023:2025].iterrows()}
hbm = pd.read_csv(D / "device_heartbeat_monthly.csv", usecols=["device_serial", "month"])
hbc = hbm.groupby("month").device_serial.nunique()
R["k2_heartbeat_devices_seen"] = {m_: int(hbc[m_]) for m_ in ["2025-06-01", "2025-07-01", "2026-06-01", "2026-07-01", "2026-09-01"] if m_ in hbc.index}

# --- K3: perpetual licences ex the big one
pc = lic_cur[(lic_cur.licence_type == "perpetual") & (lic_cur.licence_id != big.licence_id)]
pp = lic_pri[lic_pri.licence_type == "perpetual"]
R["k3_perp"] = dict(cur_n=int(len(pc)), prior_n=int(len(pp)), cur_value=float(pc.contract_value_aud.sum()), prior_value=float(pp.contract_value_aud.sum()),
                    cur_median=float(pc.contract_value_aud.median()), prior_median=float(pp.contract_value_aud.median()),
                    cur_over_500k=int((pc.contract_value_aud > 500_000).sum()), prior_over_500k=int((pp.contract_value_aud > 500_000).sum()),
                    cur_over_500k_value=float(pc[pc.contract_value_aud > 500_000].contract_value_aud.sum()),
                    prior_over_500k_value=float(pp[pp.contract_value_aud > 500_000].contract_value_aud.sum()),
                    small_cur=float(pc[pc.contract_value_aud <= 500_000].contract_value_aud.sum()),
                    small_prior=float(pp[pp.contract_value_aud <= 500_000].contract_value_aud.sum()))
dec = dd[dd.renewal_status.isin(["renewed", "lapsed"])]
R["k3"]["renewal_decided_n"] = int(len(dec))
R["k3"]["renewal_rate_decided"] = float((dec.renewal_status == "renewed").mean())
decp = due_prior[due_prior.renewal_status.isin(["renewed", "lapsed"])]
R["k3"]["renewal_rate_decided_prior"] = float((decp.renewal_status == "renewed").mean())
R["k3"]["renewal_decided_prior_n"] = int(len(decp))
R["big_oem_hw"] = dict(fy25=float(po.loc[big.oem_id, 2025]), fy26=float(po.loc[big.oem_id, 2026]),
                       change=float(po.loc[big.oem_id, 2026] / po.loc[big.oem_id, 2025] - 1),
                       rank_fy25=int(po[2025].rank(ascending=False)[big.oem_id]))
R["k3"]["lic_vs_gl_r12m_diff"] = float(lic_cur.contract_value_aud.sum() - sw_cur)

# --- K4: how many old design wins are neither shipped nor lost
old36 = dw[dw.stage_entered_design_win <= AS_OF_DATE - pd.DateOffset(months=36)]
R["k4_old36"] = dict(n=int(len(old36)), shipped=float(old36.shipped.mean()), lost=float(old36.lost_after.mean()),
                     open=float((~old36.shipped & ~old36.lost_after).mean()))
c23 = dw[(dw.stage_entered_design_win < "2023-07-01")]
R["k4_ship_curve_2023h1"] = {str(m_): float((c23.days <= m_ * 30.44).sum() / len(c23)) for m_ in (6, 12, 18, 24, 30, 36, 42)}
R["k4_ship_curve_2023h1_n"] = int(len(c23))

# --- K5: year-ago test and the A$ error by family
def year_ago_actual(q, fam):
    fy = int(q[2:4]); n = int(q[-1]); yq_ = f"FY{fy-1}-Q{n}"
    return fam_series[fam][qmonths(yq_)].sum()
fin["yago"] = [year_ago_actual(r_.q, r_.fam) for r_ in fin.itertuples()]
ftt = fin.groupby("q").agg(forecast=("forecast", "sum"), yago=("yago", "sum"), actual=("actual", "sum"))
ftt["f_vs_yago"] = ftt.forecast / ftt.yago - 1
ftt["yago_err"] = ftt.yago / ftt.actual - 1
R["k5_finance_vs_yearago"] = {q: dict(f_vs_yago=float(r_.f_vs_yago), yago_err=float(r_.yago_err)) for q, r_ in ftt.iterrows()}
R["k5_yearago"] = dict(corr=float(np.corrcoef(ftt.forecast, ftt.yago)[0, 1]), yago_mape=float(ftt.yago_err.abs().mean()),
                       finance_mape=float((ftt.forecast / ftt.actual - 1).abs().mean()),
                       median_gap_to_yago=float(ftt.f_vs_yago.abs().median()))
f["err_aud"] = f.forecast - f.actual
bysrc = f[f.q.isin(last4)].groupby(["source", "fam"]).err_aud.sum().unstack().fillna(0)
R["k5_aud_error_last4"] = {s_: {fam: float(v) for fam, v in r_.items()} for s_, r_ in bysrc.iterrows()}
R["k5_aud_actual_last4"] = float(f[(f.source == "finance_run_rate") & f.q.isin(last4)].actual.sum())
for src in ("finance_run_rate", "sales_crm"):
    t = tot[tot.source == src].set_index("q")
    pre = [q for q in t.index if q < "FY26-Q1"]; post = [q for q in t.index if q >= "FY26-Q1"]
    R["k5"][src]["mape_before_break"] = float(t.loc[pre, "abs_err"].mean()); R["k5"][src]["mape_after_break"] = float(t.loc[post, "abs_err"].mean())
    R["k5"][src]["bias_before_break"] = float(t.loc[pre, "err"].mean()); R["k5"][src]["bias_after_break"] = float(t.loc[post, "err"].mean())
    R["k5"][src]["n_over"] = int((t.err > 0).sum()); R["k5"][src]["n_quarters"] = int(len(t))


# ================================================================ THIRD PASS: pace-needed inputs
# --- K4 as of each of the last 4 quarter-ends (same 18-month rule, shipping known by that date)
def k4_asof(d):
    d = pd.Timestamp(d)
    coh = dw[dw.stage_entered_design_win <= d - pd.DateOffset(months=18)]
    shipped = (coh.stage_entered_shipping <= d).sum()
    return float(shipped / len(coh)), int(len(coh))
R["k4_asof"] = {d: dict(zip(("rate", "n"), k4_asof(d))) for d in ["2025-12-31", "2026-03-31", "2026-06-30", "2026-09-30"]}
R["k4_run_rate_pts_per_month"] = (R["k4_asof"]["2026-09-30"]["rate"] - R["k4_asof"]["2025-12-31"]["rate"]) / 9 * 100
R["k4_pace_needed_pts_per_month"] = (0.80 - R["k4_asof"]["2026-09-30"]["rate"]) / R["k4"]["months_remaining"] * 100

# --- K3: what FY27 software needs for +20%, in dollars
need = fy_rev[2026]["sw"] * 1.20
q1_sw = float(m_sw[pd.period_range("2026-07", "2026-09", freq="M")].sum())
rest = (need - q1_sw) / 9
exbig_fy26_monthly = (fy_rev[2026]["sw"] - ex) / 12
R["k3_fy27_need"] = dict(need_fy27=float(need), q1_actual=q1_sw, rest_per_month=float(rest), fy26_ex_big_per_month=float(exbig_fy26_monthly),
                         ex_big_trailing_per_month=float((sw_cur - ex) / 12))
sw_flat = q1_sw + sum(m_sw[p - 12] for p in pd.period_range("2026-10", "2027-06", freq="M")) - ex
R["k3_flat_jun27"] = dict(value=float(sw_flat), growth=float(sw_flat / fy_rev[2026]["sw"] - 1))

# --- K2: pace in customers
R["k2_pace_note"] = dict(target_units_jun27=float(a_pri * 0 + R["k2"]["prior"] * 1.05))

# --- K5: absolute error run rate and plan cross-check
for src in ("finance_run_rate", "sales_crm"):
    t = tot[tot.source == src].set_index("q").abs_err
    last = t.iloc[-1]; three_back = t.iloc[-4] if len(t) >= 4 else np.nan
    R["k5"][src]["abs_err_latest"] = float(last)
    R["k5"][src]["run_rate_pts_per_quarter"] = float((last - three_back) / 3 * 100)
    R["k5"][src]["pace_needed_pts_per_quarter"] = float((0.05 - last) / 3 * 100)
    R["k5"][src]["abs_err_series_last4"] = {q: float(v) for q, v in t.iloc[-4:].items()}
plan_q1_hw = float(plan[(plan.month <= "2026-09") & (plan.product_family == "hardware")].plan_revenue_aud.sum())
R["k5_plan_vs_forecast_q1"] = dict(plan_hw=plan_q1_hw, finance_forecast_hw=R["k5_fy27q1_hw"]["finance_run_rate"]["forecast"],
                                   sales_forecast_hw=R["k5_fy27q1_hw"]["sales_crm"]["forecast"], actual_hw=R["k5_fy27q1_hw"]["finance_run_rate"]["actual"],
                                   plan_set="2026-06-15", forecast_dated="2026-06-26")
plan_q1_all = float(plan[plan.month <= "2026-09"].plan_revenue_aud.sum())
R["k5_plan_q1_all"] = plan_q1_all
R["k5_plan_q1_err"] = float(plan_q1_all / act_q1 - 1)

# --- challenge-note facts
R["gary_hw_codes_check"] = dict(
    plan_other_codes_match=bool(abs(plan[plan.product_family == "other"].plan_revenue_aud.sum() / fy_rev[2026]["oth"] - 1.15) < 0.001),
    other_fy26=float(fy_rev[2026]["oth"]), dev_kits_fy26=float(-gl[(gl.account_code == 4200) & (gl.p.map(fy_of) == 2026)].amount_aud.sum()),
    freight_fy26=float(-gl[(gl.account_code == 4210) & (gl.p.map(fy_of) == 2026)].amount_aud.sum()))
R["fy27_plan_vs_path_gap"] = float(R["plan_fy27_total"] - R["path_at_jun27"])
R["fy27_plan_vs_path_gap_pct"] = float(R["plan_fy27_total"] / R["path_at_jun27"] - 1)
R["board_target_fy27_gm"] = 76.0

# ================================================================ FOURTH PASS: loose ends
# orders against installs, by phase
def avg_m(s, a_, b_):
    return float(s[pd.period_range(a_, b_, freq="M")].mean())
R["orders_vs_installs"] = dict(
    shortage_2022_07_to_2023_09=dict(orders=avg_m(ordq, "2022-07", "2023-09"), installs=avg_m(am, "2022-07", "2023-09"), invoiced=avg_m(units_inv, "2022-07", "2023-09")),
    drought_2023_10_to_2024_06=dict(orders=avg_m(ordq, "2023-10", "2024-06"), installs=avg_m(am, "2023-10", "2024-06"), invoiced=avg_m(units_inv, "2023-10", "2024-06")),
    rebuy_2024_07_to_2025_04=dict(orders=avg_m(ordq, "2024-07", "2025-04"), installs=avg_m(am, "2024-07", "2025-04"), invoiced=avg_m(units_inv, "2024-07", "2025-04")),
    reset_2025_05_to_2026_09=dict(orders=avg_m(ordq, "2025-05", "2026-09"), installs=avg_m(am, "2025-05", "2026-09"), invoiced=avg_m(units_inv, "2025-05", "2026-09")),
)
# ledger software revenue vs licence table, month by month
lic_m = lic.groupby("p").contract_value_aud.sum().reindex(m_sw.index).fillna(0)
dm = (lic_m - m_sw).abs()
R["lic_vs_gl_monthly"] = dict(max_abs_diff=float(dm.max()), months_over_1pct=int(((dm / m_sw) > 0.01).sum()), months=int(len(dm)))
# K2.1 headline numbers
iq = R["inv_by_quarter"]
R["k2_1"] = dict(latest_q="2026-Q3", latest_units=iq["2026-Q3"]["units"], latest_oems=iq["2026-Q3"]["oems"],
                 latest_parts=iq["2026-Q3"]["parts"], peak_q=R["inv_peak"]["quarter"], peak_units=R["inv_peak"]["units"], peak_oems=R["inv_peak"]["oems"],
                 lfl_latest=R["inv_lfl"]["2026-Q3"], lfl_prev=R["inv_lfl"]["2026-Q2"], lfl_prev2=R["inv_lfl"]["2026-Q1"],
                 raw_sum_no_reel_fix_latest=float(inb[inb.quarter == "2026-Q3"].units_on_hand.sum()))
R["k2_1"]["run_rate_lfl_per_quarter"] = float(np.mean([R["inv_lfl"][q]["change"] for q in ("2026-Q1", "2026-Q2", "2026-Q3")]))
# counter-metric baselines
R["counter"] = dict(
    k1_gm=R["k3_2"]["cur"], k1_rebate_rate_fy26=R["quarter_end"]["rebate_rate26"],
    k2_dup=R["k2"]["dup_rate_cur"], k3_renewal=R["k3"]["renewal_rate_decided"],
    k4_lost=R["k4"]["lost_rate"], k5_bias_fin=R["k5"]["finance_run_rate"]["bias_last4"], k5_bias_sales=R["k5"]["sales_crm"]["bias_last4"])

# ================================================================ v1.1 PASS (Business Requirements v1.1)
# --- K3 v1.1: growth leaving out any single licence worth more than A$1m (threshold to agree with the CFO)
LIM = 1_000_000
def k3_ex(end):
    cw = pd.period_range(end - 11, end, freq="M"); pw = pd.period_range(end - 23, end - 12, freq="M")
    exc = lic[(lic.contract_value_aud > LIM) & lic.p.isin(cw)].contract_value_aud.sum()
    exp_ = lic[(lic.contract_value_aud > LIM) & lic.p.isin(pw)].contract_value_aud.sum()
    c_, p_ = m_sw[cw].sum() - exc, m_sw[pw].sum() - exp_
    return float(c_ / p_ - 1), float(exc), float(exp_), float(c_), float(p_)
ser = {str(p): k3_ex(p) for p in [AS_OF - 3, AS_OF - 2, AS_OF - 1, AS_OF]}
g_, exc_, exp_, c_, p_ = ser[str(AS_OF)]
R["k3_v11"] = dict(growth_ex=g_, excluded_cur=exc_, excluded_prior=exp_, cur=c_, prior=p_, limit=LIM,
                   n_over_limit_cur=int(((lic.contract_value_aud > LIM) & lic.p.isin(cur)).sum()),
                   n_over_limit_prior=int(((lic.contract_value_aud > LIM) & lic.p.isin(pri)).sum()),
                   series={k: v[0] for k, v in ser.items()}, months_remaining=mrem)
R["k3_v11"]["run_rate_pts_per_month"] = (ser[str(AS_OF)][0] - ser[str(AS_OF - 3)][0]) / 3 * 100
R["k3_v11"]["pace_needed_pts_per_month"] = (0 - g_) / mrem * 100
R["k3_v11"]["fy26_ex"] = float((fy_rev[2026]["sw"] - lic[(lic.contract_value_aud > LIM) & (lic.fy == 2026)].contract_value_aud.sum()) /
                               (fy_rev[2025]["sw"] - lic[(lic.contract_value_aud > LIM) & (lic.fy == 2025)].contract_value_aud.sum()) - 1)

# --- K2.1 v1.1: months of stock cover at the reporting OEMs, divisor = units invoiced to the same OEMs
inb_ids = inb.dropna(subset=["oem_id"])
def q_months(qs):
    y, n = int(qs[:4]), int(qs[-1]); return pd.period_range(f"{y}-{(n-1)*3+1:02d}", periods=3, freq="M")
hw_oem_m = hwi.groupby(["oem_id", "p"]).qty.sum()
cover = {}
for q, g in inb_ids.groupby("quarter"):
    ids = set(g.oem_id); ms = q_months(q)
    if ms[-1] > AS_OF: continue
    stock = float(g.units.sum())
    inv_u = float(hwi[hwi.oem_id.isin(ids) & hwi.p.isin(ms)].qty.sum()) / 3
    a_u = float(a1[a1.oem_fixed.isin(ids) & a1.p.isin(ms)].shape[0] * SC) / 3
    cover[q] = dict(stock=stock, oems=len(ids), invoiced_per_month=inv_u, activated_per_month=a_u,
                    months_invoiced=stock / inv_u if inv_u else None, months_activated=stock / a_u if a_u else None)
R["k2_1_cover"] = cover
last3 = ["2026-Q1", "2026-Q2", "2026-Q3"]
R["k2_1_cover_summary"] = dict(
    latest=cover["2026-Q3"], peak_q=max(cover, key=lambda q: cover[q]["months_invoiced"] or 0),
    run_rate_per_quarter=float((cover["2026-Q3"]["months_invoiced"] - cover["2025-Q4"]["months_invoiced"]) / 3),
    pace_needed_per_quarter=float((6 - cover["2026-Q3"]["months_invoiced"]) / 3),
    quarters_remaining=3,
    activation_share_oem_known=float(1 - R["dq_act_oem_missing_after_backfill"] / R["dq_act_rows"]),
    activated_vs_invoiced_ratio_latest=float(cover["2026-Q3"]["months_activated"] / cover["2026-Q3"]["months_invoiced"]))
R["k2_1_cover_summary"]["peak"] = cover[R["k2_1_cover_summary"]["peak_q"]]
# --- K4 v1.1 pace to 35%
R["k4_pace_needed_pts_per_month_v11"] = (0.35 - R["k4"]["rate"]) / R["k4"]["months_remaining"] * 100
# --- K5 v1.1 pace to 10%
for src in ("finance_run_rate", "sales_crm"):
    R["k5"][src]["pace_needed_pts_per_quarter_v11"] = (0.10 - R["k5"][src]["abs_err_latest"]) / 3 * 100
# --- K3.2 v1.1 pace to 76%
R["k3_2"]["pace_needed_pts_per_month_v11"] = (0.76 - R["k3_2"]["cur"]) * 100 / 9


def conv(o):
    if isinstance(o, (np.integer,)): return int(o)
    if isinstance(o, (np.floating,)): return float(o)
    if isinstance(o, (np.bool_,)): return bool(o)
    if isinstance(o, (pd.Period, pd.Timestamp)): return str(o)
    raise TypeError(type(o))


(HERE / "02_results.json").write_text(json.dumps(R, indent=1, default=conv), encoding="utf-8")
print(json.dumps(R, indent=1, default=conv))