Repository Public
1#!/opt/homebrew/bin/python32# =============================================================================3# SP BOUCHER | SOFTWARE & RESEARCH4# ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5# =============================================================================6#7# PROJECT METADATA8# -----------------------------------------------------------------------------9# Project : ORIX Sheet10# File : Packages/SheetTemplates/Tests/SheetTemplatesTests/Generators/gen_expected.py11# Description : Independent expectations for the ORIX Sheet templates.12# Module : SheetTemplatesTests13# Repository : https://git.spboucher.ai/orix-sheet14# Created : 2026-11-2115# Last Modified : 2026-11-2116#17# =============================================================================18# AUTHOR & PROFESSIONAL AFFILIATION19# =============================================================================20#21# Author : Simon-Pierre Boucher22# Position : Professor of Finance23# Institution : Université du Québec en Outaouais (UQO)24# Department : Département des sciences administratives25# Organization : SP Boucher26#27# Website : https://spboucher.ai28# Git : https://git.spboucher.ai29#30# =============================================================================31# OFFICIAL CONTACT DIRECTORY32# =============================================================================33#34# General : info@spboucher.ai35# Professional : contact@spboucher.ai36# Direct : simon@spboucher.ai37# Research : research@spboucher.ai38# Development : dev@spboucher.ai39# Support : support@spboucher.ai40# Security/Admin : admin@spboucher.ai41# Notifications : notifications@spboucher.ai42#43# Preferred Contact: support@spboucher.ai44#45# =============================================================================46# COPYRIGHT & INTELLECTUAL PROPERTY47# =============================================================================48#49# Copyright (c) 202650# Simon-Pierre Boucher51#52# All rights reserved where applicable.53#54# This file is governed by the applicable project license,55# copyright notices, institutional agreements and relevant law.56#57# Unless expressly authorized by the applicable license or58# rights holder, copying, redistribution, sublicensing,59# publication and commercial exploitation are not permitted.60#61# Existing third-party rights and notices remain unaffected.62#63# =============================================================================64# CONFIDENTIALITY & SECURITY65# =============================================================================66#67# Classification : Public68# Distribution : Public repository; use is governed by the project license69# Security Contact : admin@spboucher.ai70#71# Access to this source does not independently grant permission72# to redistribute or disclose confidential materials.73#74# Do not embed credentials, private keys, access tokens,75# certificates or confidential personal information.76#77# =============================================================================78# LICENSE & MAINTENANCE79# =============================================================================80#81# License : None specified (no license file in this repository)82# Maintainer : Simon-Pierre Boucher83# Technical Contact: dev@spboucher.ai84#85# =============================================================================86# END OF SP BOUCHER SOURCE HEADER87# =============================================================================8889"""Independent expectations for the ORIX Sheet templates.9091Reads inputs.json (written by the test `exportGeneratorInputs`, see GeneratorInputs.swift) and recomputes the key92outputs of every template with numpy / scipy and plain closed forms -- never with a spreadsheet engine. The result,93Fixtures/expected.json, is what NumericTests.swift compares the calculated workbooks against.9495 ORIX_TEMPLATES_INPUTS=Tests/SheetTemplatesTests/Generators/inputs.json swift test --filter exportGeneratorInputs96 /opt/homebrew/bin/python3 Tests/SheetTemplatesTests/Generators/gen_expected.py97"""98import json, math, os, datetime as dt99import numpy as np100from scipy import stats, optimize101102here = os.path.dirname(os.path.abspath(__file__))103I = json.load(open(os.path.join(here, "inputs.json")))104E = {}105106def serial(y, m, d):107 """Excel 1900-system serial of a date after 1 March 1900."""108 return (dt.date(y, m, d) - dt.date(1899, 12, 30)).days109110# ---------------------------------------------------------------- personal budget111x = I["personal-budget"]112income = float(np.sum(x["income"])); expenses = float(np.sum(x["expenses"]))113E["personal-budget"] = {114 "total_income": income, "total_expenses": expenses, "net_savings": income - expenses, "savings_rate": (income - expenses) / income,115 "category_actual": [float(sum(r)) for r in x["expenses"]], "category_plan": [12 * p for p in x["plan"]],116 "overspent_months": int(sum(1 for r, p in zip(x["expenses"], x["plan"]) for v in r if v > p)),117}118119# ---------------------------------------------------------------- business budget120x = I["business-budget"]; B = np.array(x["budget"]); A = np.array(x["actual"]); n = x["months_closed"]121E["business-budget"] = {122 "ytd_budget": float(B[:, :n].sum()), "ytd_actual": float(A[:, :n].sum()), "ytd_variance": float(A[:, :n].sum() - B[:, :n].sum()),123 "ytd_actual_by_department": A[:, :n].sum(axis=1).tolist(), "year_budget": float(B.sum()),124 "quarter_variance_pct": [[float(A[d, q * 3:q * 3 + 3].sum() / B[d, q * 3:q * 3 + 3].sum() - 1) for q in range(4)] for d in range(B.shape[0])],125 "ytd_actual_6": float(A[:, :6].sum()), "ytd_budget_6": float(B[:, :6].sum()),126}127128# ---------------------------------------------------------------- financial statements129def statements(x):130 cash, ar, inv, ppe, ap, debt, cap, re = (x[k] for k in ("cash0", "ar0", "inv0", "ppe0", "ap0", "debt0", "capital0", "retained0"))131 out = {"net_income": [], "cash": [], "total_assets": [], "imbalance": [], "cfo": []}132 rev = x["revenue1"]133 for y in range(x["years"]):134 if y > 0: rev *= 1 + x["growth"]135 cogs = rev * x["cogs"]; opex = rev * x["opex"]; dep = ppe * x["dep"]; interest = debt * x["interest"]136 ebt = rev - cogs - opex - dep - interest137 ni = ebt - max(0.0, ebt) * x["tax"]138 ar1 = rev * x["ar_days"] / 365; inv1 = cogs * x["inv_days"] / 365; ap1 = cogs * x["ap_days"] / 365139 debt1 = max(0.0, debt - x["repayment"]); div = max(0.0, ni) * x["payout"]140 cfo = ni + dep - (ar1 - ar) - (inv1 - inv) + (ap1 - ap)141 cash = cash + cfo - x["capex"] + (debt1 - debt) - div142 ppe = ppe + x["capex"] - dep; re = re + ni - div143 ar, inv, ap, debt = ar1, inv1, ap1, debt1144 assets = cash + ar + inv + ppe145 out["net_income"].append(ni); out["cash"].append(cash); out["total_assets"].append(assets); out["cfo"].append(cfo)146 out["imbalance"].append(assets - (ap + debt + cap + re))147 return out148x = I["financial-statements"]149s = statements(x)150alt = dict(x); alt["growth"] = 0.15; alt["tax"] = 0.30; alt["ar_days"] = 70.0151s2 = statements(alt)152E["financial-statements"] = {"net_income": s["net_income"], "closing_cash": s["cash"], "total_assets": s["total_assets"], "cfo": s["cfo"],153 "max_imbalance": float(max(abs(v) for v in s["imbalance"])),154 "alt_net_income": s2["net_income"], "alt_closing_cash": s2["cash"], "alt_total_assets": s2["total_assets"]}155156# ---------------------------------------------------------------- cash-flow forecast157x = I["cash-flow-forecast"]158net = np.sum(x["receipts"], axis=0) - np.sum(x["payments"], axis=0)159closing = x["opening"] + np.cumsum(net)160E["cash-flow-forecast"] = {"closing": closing.tolist(), "lowest": float(closing.min()), "lowest_week": int(closing.argmin()) + 1,161 "weeks_below": int((closing < x["minimum"]).sum()), "ending": float(closing[-1]), "total_receipts": float(np.sum(x["receipts"]))}162163# ---------------------------------------------------------------- mortgage164def mortgage(principal, rate, years, per_year, canadian=True, extra=0.0):165 r = (1 + rate / 2) ** (2 / per_year) - 1 if canadian else rate / per_year166 n = int(round(years * per_year))167 pmt = principal * r / (1 - (1 + r) ** -n)168 bal, interest, count, paid = principal, 0.0, 0, 0.0169 for _ in range(n):170 if bal <= 0.005: break171 i = bal * r172 p = min(pmt, bal + i)173 e = min(extra, bal - (p - i))174 bal -= (p - i) + e; interest += i; count += 1; paid += p + e175 return {"periodic_rate": r, "payment": pmt, "total_interest": interest, "payments": count, "final_balance": bal, "total_paid": paid, "first_interest": principal * r}176x = I["mortgage"]; P = x["price"] * (1 - x["down"])177E["mortgage"] = {"loan": P, "canadian": mortgage(P, x["rate"], x["years"], x["per_year"]), "us": mortgage(P, x["rate"], x["years"], x["per_year"], canadian=False),178 "extra_300": mortgage(P, x["rate"], x["years"], x["per_year"], extra=300.0), "biweekly": mortgage(P, x["rate"], x["years"], 26)}179180# ---------------------------------------------------------------- investment analysis181x = I["investment-analysis"]; cf = np.array(x["flows"]); t = np.arange(len(cf))182npv = lambda r: float(np.sum(cf / (1 + r) ** t))183days = np.array([serial(*d) for d in x["dates"]], dtype=float)184xnpv = lambda r: float(np.sum(cf / (1 + r) ** ((days - days[0]) / 365)))185pos = np.where(cf > 0, cf, 0); neg = np.where(cf < 0, cf, 0); n = len(cf) - 1186mirr = (-(np.sum(pos * (1 + x["reinvest"]) ** (n - t))) / np.sum(neg / (1 + x["finance"]) ** t)) ** (1 / n) - 1187cum = np.cumsum(cf); k = int(np.argmax(cum >= 0))188E["investment-analysis"] = {189 "npv": npv(x["discount"]), "irr": optimize.brentq(npv, -0.9, 5, xtol=1e-14), "mirr": float(mirr), "payback": (k - 1) + (-cum[k - 1]) / cf[k],190 "pi": (npv(x["discount"]) - cf[0]) / -cf[0], "xnpv": xnpv(x["discount"]), "xirr": optimize.brentq(xnpv, -0.9, 5, xtol=1e-14),191 "sensitivity": [npv(r) for r in x["sensitivity"]], "npv_at_12": npv(0.12),192}193194# ---------------------------------------------------------------- DCF valuation195def dcf(x, wacc=None, g=None):196 ke = x["rf"] + x["beta"] * x["premium"]197 w = x["wd"] * x["kd"] * (1 - x["tax"]) + (1 - x["wd"]) * ke if wacc is None else wacc198 g = x["g"] if g is None else g199 rev, pv, fcf, ebitda = x["base"], 0.0, 0.0, 0.0200 for i in range(5):201 prev = rev; rev = rev * (1 + x["growth"][i]); ebitda = rev * x["margin"][i]; da = rev * x["da"]202 ebit = ebitda - da; nopat = ebit - max(0.0, ebit) * x["tax"]203 fcf = nopat + da - rev * x["capex"] - (rev - prev) * x["nwc"]204 pv += fcf / (1 + w) ** (i + 1)205 tv_g = fcf * (1 + g) / (w - g); tv_m = ebitda * x["multiple"]206 ev_g = pv + tv_g / (1 + w) ** 5; ev_m = pv + tv_m / (1 + w) ** 5207 return {"cost_of_equity": ke, "wacc": w, "fcf5": fcf, "pv_fcf": pv, "ev": ev_g, "equity": ev_g - x["debt"] + x["cash"],208 "per_share": (ev_g - x["debt"] + x["cash"]) / x["shares"], "per_share_exit": (ev_m - x["debt"] + x["cash"]) / x["shares"]}209x = I["dcf-valuation"]; base = dcf(x)210E["dcf-valuation"] = dict(base)211E["dcf-valuation"]["grid"] = [[dcf(x, base["wacc"] + dw, x["g"] + dg)["per_share"] for dg in x["steps"]] for dw in x["steps"]]212hi = dict(x); hi["beta"] = 1.5213E["dcf-valuation"]["per_share_beta_1_5"] = dcf(hi)["per_share"]; E["dcf-valuation"]["wacc_beta_1_5"] = dcf(hi)["wacc"]214215# ---------------------------------------------------------------- project plan (numpy business-day calendar)216x = I["project-plan"]217hol = [dt.date(*h) for h in x["holidays"]]218cal = np.busdaycalendar(holidays=hol)219start0 = np.busday_offset(dt.date(*x["start"]), 0, roll="forward", busdaycal=cal)220status = np.datetime64(dt.date(*x["status"]))221starts, ends, states = [], [], []222for p, d, done in zip(x["pred"], x["days"], x["done"]):223 s0 = start0 if p == 0 else np.busday_offset(ends[p - 1], 1, roll="forward", busdaycal=cal)224 e0 = np.busday_offset(s0, d - 1, roll="forward", busdaycal=cal)225 starts.append(s0); ends.append(e0)226 states.append("done" if done >= 1 else "late" if status > e0 else "progress" if status >= s0 else "todo")227to_serial = lambda d: int((d.astype("datetime64[D]") - np.datetime64("1899-12-30")).astype(int))228E["project-plan"] = {"starts": [to_serial(d) for d in starts], "ends": [to_serial(d) for d in ends], "finish": max(to_serial(d) for d in ends),229 "states": states, "late": states.count("late"), "done": states.count("done"),230 "progress": float(np.dot(x["days"], x["done"]) / np.sum(x["days"]))}231232# ---------------------------------------------------------------- inventory233x = I["inventory"]; n = len(x["cost"])234rec = np.zeros(n); iss = np.zeros(n)235for item, inc, qty in zip(x["mv_item"], x["mv_in"], x["mv_qty"]):236 (rec if inc else iss)[item] += qty237on_hand = np.array(x["opening"]) + rec - iss238value = on_hand * np.array(x["cost"])239E["inventory"] = {"received": rec.tolist(), "issued": iss.tolist(), "on_hand": on_hand.tolist(), "stock_value": float(value.sum()), "units": float(on_hand.sum()),240 "to_reorder": int((on_hand <= np.array(x["point"])).sum()), "min_on_hand": float(on_hand.min()),241 "value_by_category": [float(value[np.array(x["cat"]) == c].sum()) for c in range(x["categories"])]}242243# ---------------------------------------------------------------- sales tracking244x = I["sales-tracking"]245amount = np.array(x["units"]) * np.array([x["price"][p] for p in x["product"]])246by_product = [float(amount[np.array(x["product"]) == p].sum()) for p in range(len(x["price"]))]247E["sales-tracking"] = {"revenue": float(amount.sum()), "count": len(amount), "average": float(amount.mean()), "units": float(np.sum(x["units"])),248 "by_month": [float(amount[np.array(x["month"]) == m].sum()) for m in range(1, 13)],249 "by_rep": [float(amount[np.array(x["rep"]) == r].sum()) for r in range(5)], "by_product": by_product,250 "top": sorted(by_product, reverse=True)[:x["top"]], "top5": sorted(by_product, reverse=True)[:5]}251252# ---------------------------------------------------------------- research dataset253x = I["research-dataset"]254nan = lambda v: np.array([np.nan if e is None else e for e in v], dtype=float)255pre, post, age = nan(x["pre"]), nan(x["post"]), nan(x["age"]); grp = np.array(x["group"]); site = np.array(x["site"])256change = post - pre257E["research-dataset"] = {258 "n": len(grp), "missing": int(np.isnan(pre).sum() + np.isnan(post).sum() + np.isnan(age).sum()),259 "complete": int((~(np.isnan(pre) | np.isnan(post) | np.isnan(age))).sum()), "mean_change": float(np.nanmean(change)),260 "mean_change_by_group": [float(np.nanmean(change[grp == k])) for k in (0, 1)], "mean_pre_by_group": [float(np.nanmean(pre[grp == k])) for k in (0, 1)],261 "mean_age_by_group": [float(np.nanmean(age[grp == k])) for k in (0, 1)], "n_by_group": [int((grp == k).sum()) for k in (0, 1)],262 "group_site": [[int(((grp == k) & (site == j)).sum()) for j in range(3)] for k in (0, 1)],263 "valid": [int((~np.isnan(v)).sum()) for v in (age, pre, post, change)],264}265266# ---------------------------------------------------------------- statistical analysis (scipy)267x = I["statistical-analysis"]; a = np.array(x["a"]); b = np.array(x["b"]); h = np.array(x["hours"]); sc = np.array(x["score"])268ci = stats.t.interval(1 - x["alpha"], len(a) - 1, loc=a.mean(), scale=stats.sem(a))269pooled = stats.ttest_ind(a, b, equal_var=True); welch = stats.ttest_ind(a, b, equal_var=False)270r, p_r = stats.pearsonr(h, sc); lin = stats.linregress(h, sc)271edges = np.array(x["bins"])272freq = lambda v: [int(((v > lo) & (v <= hi)).sum()) for lo, hi in zip(np.r_[-np.inf, edges], np.r_[edges, np.inf])]273E["statistical-analysis"] = {274 "mean_a": float(a.mean()), "mean_b": float(b.mean()), "sd_a": float(a.std(ddof=1)), "sd_b": float(b.std(ddof=1)), "median_a": float(np.median(a)),275 "q1_a": float(np.quantile(a, 0.25)), "q3_b": float(np.quantile(b, 0.75)), "skew_a": float(stats.skew(a, bias=False)), "kurt_b": float(stats.kurtosis(b, bias=False)),276 "ci_lower_a": float(ci[0]), "ci_upper_a": float(ci[1]), "t_pooled": float(pooled.statistic), "p_pooled": float(pooled.pvalue),277 "t_welch": float(welch.statistic), "df_welch": float(welch.df), "p_welch": float(welch.pvalue),278 "r": float(r), "p_r": float(p_r), "slope": float(lin.slope), "intercept": float(lin.intercept), "freq_a": freq(a), "freq_b": freq(b),279}280281# ---------------------------------------------------------------- regression report (numpy.linalg.lstsq)282def ols(y, X):283 Xd = np.column_stack([np.ones(len(y)), X])284 beta, *_ = np.linalg.lstsq(Xd, y, rcond=None)285 res = y - Xd @ beta; n, k = Xd.shape286 sse = float(res @ res); sst = float(((y - y.mean()) ** 2).sum()); s2 = sse / (n - k)287 se = np.sqrt(np.diag(s2 * np.linalg.inv(Xd.T @ Xd))); tvals = beta / se288 f = ((sst - sse) / (k - 1)) / s2289 return {"beta": beta.tolist(), "se": se.tolist(), "t": tvals.tolist(), "p": (2 * stats.t.sf(np.abs(tvals), n - k)).tolist(), "r2": 1 - sse / sst,290 "adj_r2": 1 - (sse / (n - k)) / (sst / (n - 1)), "see": math.sqrt(s2), "f": float(f), "p_f": float(stats.f.sf(f, k - 1, n - k)), "sse": sse, "ssr": sst - sse,291 "df": n - k, "dw": float((np.diff(res) ** 2).sum() / sse), "residuals": res.tolist(),292 "ci95": [[float(bv - stats.t.ppf(0.975, n - k) * s), float(bv + stats.t.ppf(0.975, n - k) * s)] for bv, s in zip(beta, se)]}293x = I["regression-report"]; y = np.array(x["y"]); X = np.array(x["x"])294E["regression-report"] = {"simple": ols(y, X[:, :1]), "multiple": ols(y, X)}295y2 = y.copy(); y2[0] += 40296E["regression-report"]["multiple_y0_plus_40"] = ols(y2, X)297298# ---------------------------------------------------------------- dashboard299x = I["dashboard"]; rev = np.array(x["revenue"]); cost = np.array(x["cost"]); i = x["key"].index(x["period"])300j0 = x["key"].index(x["period"][:4] + "-01")301def dash(i):302 j0 = x["key"].index(x["key"][i][:4] + "-01")303 return {"row": i + 1, "revenue": float(rev[i]), "margin": float((rev[i] - cost[i]) / rev[i]), "orders": x["orders"][i], "active": x["active"][i],304 "revenue_change": float(rev[i] / rev[i - 1] - 1), "ytd_revenue": float(rev[j0:i + 1].sum()), "ytd_margin": float((rev - cost)[j0:i + 1].sum()),305 "ytd_new": float(np.sum(x["new"][j0:i + 1])), "last6_revenue": rev[max(0, i - 5):i + 1].tolist()}306E["dashboard"] = {"selected": dash(i), "2025-03": dash(x["key"].index("2025-03")), "2026-12": dash(x["key"].index("2026-12"))}307308# ---------------------------------------------------------------- gradebook309x = I["gradebook"]; mx = np.array(x["max"]); w = np.array(x["weight"])310weighted, final = [], []311for row, late in zip(x["scores"], x["late"]):312 num = sum((v / m) * wt for v, m, wt in zip(row, mx, w) if isinstance(v, (int, float)))313 den = sum(wt for v, wt in zip(row, w) if v != "EX")314 weighted.append(num / den); final.append(round(max(0.0, num / den - late * x["penalty"]), 6))315final = np.array(final)316letter = lambda f: [l for m, l in zip(x["scale_min"], x["scale_letter"]) if f >= m][-1]317E["gradebook"] = {"weighted": weighted, "final": final.tolist(), "letters": [letter(f) for f in final], "rank": [int((final > f).sum()) + 1 for f in final],318 "mean": float(final.mean()), "median": float(np.median(final)), "sd": float(final.std(ddof=1)), "at_risk": int((final < x["at_risk"]).sum()),319 "top": int(final.argmax())}320321out = os.path.join(here, "..", "Fixtures", "expected.json")322json.dump(E, open(out, "w"), indent=1, sort_keys=True)323print("wrote", os.path.normpath(out), "-", sum(len(v) for v in E.values()), "keys")324