Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetTemplates/Tests/SheetTemplatesTests/NumericTests.swift10// Module : SheetTemplatesTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90import SheetEngine91@testable import SheetTemplates9293/// Key outputs of every template against values computed independently (numpy / scipy, Generators/gen_expected.py),94/// and propagation of edited inputs. Both locales are checked: the numbers must not depend on the language.95@Suite struct Numeric {96 static let locales = [english, french]9798 @Test func expectationsAreLoaded() { #expect(Expected.all.count == 15, "run Generators/gen_expected.py") }99100 @Test(arguments: locales) func personalBudget(_ l: Locale) {101 let (wb, e) = fresh("personal-budget", l)102 #expect(close(wb.number("Total_Income"), X.d("personal-budget.total_income")))103 #expect(close(wb.number("Total_Expenses"), X.d("personal-budget.total_expenses")))104 #expect(close(wb.number("Net_Savings"), X.d("personal-budget.net_savings")))105 #expect(close(wb.number("Savings_Rate"), X.d("personal-budget.savings_rate")))106 #expect(allClose(wb.nums(0, "C10:C19"), X.a("personal-budget.category_actual")))107 #expect(allClose(wb.nums(0, "B10:B19"), X.a("personal-budget.category_plan")))108 #expect(close(wb.num(0, "F20"), 1)) // shares add up to 100 %109 // propagation: 1,000 more rent in January110 let before = wb.number("Total_Expenses"), income = wb.number("Total_Income")111 e.setCell(1, "C12", .number(wb.num(1, "C12") + 1000))112 #expect(close(wb.number("Total_Expenses"), before + 1000))113 #expect(close(wb.number("Savings_Rate"), (income - before - 1000) / income))114 #expect(close(wb.num(0, "D10"), 1000)) // housing variance: was on plan115 #expect(errorCells(wb).isEmpty)116 }117118 @Test(arguments: locales) func businessBudget(_ l: Locale) {119 let (wb, e) = fresh("business-budget", l)120 #expect(close(wb.number("YTD_Budget"), X.d("business-budget.ytd_budget")))121 #expect(close(wb.number("YTD_Actual"), X.d("business-budget.ytd_actual")))122 #expect(close(wb.number("YTD_Variance"), X.d("business-budget.ytd_variance")))123 #expect(allClose(wb.nums(0, "C11:C16"), X.a("business-budget.ytd_actual_by_department")))124 #expect(close(wb.num(0, "F17"), X.d("business-budget.year_budget")))125 #expect(allClose(wb.nums(0, "B21:E26"), X.m("business-budget.quarter_variance_pct").flatMap { $0 }))126 #expect(close(wb.num(1, "R13"), X.d("business-budget.year_budget"))) // Budget sheet: year total127 e.setInput("Months_Closed", .number(6))128 #expect(close(wb.number("YTD_Actual"), X.d("business-budget.ytd_actual_6")))129 #expect(close(wb.number("YTD_Budget"), X.d("business-budget.ytd_budget_6")))130 #expect(wb.nums(3, "L7:Q12").allSatisfy { $0 == 0 }) // variance sheet: months 7+ are not closed131 #expect(errorCells(wb).isEmpty)132 }133134 @Test(arguments: locales) func financialStatements(_ l: Locale) {135 let (wb, e) = fresh("financial-statements", l)136 #expect(allClose(wb.nums(1, "C17:G17"), X.a("financial-statements.net_income")))137 #expect(allClose(wb.nums(3, "C20:G20"), X.a("financial-statements.closing_cash")))138 #expect(allClose(wb.nums(2, "C12:G12"), X.a("financial-statements.total_assets")))139 #expect(allClose(wb.nums(3, "C12:G12"), X.a("financial-statements.cfo")))140 #expect(allClose(wb.nums(2, "C7:G7"), wb.nums(3, "C20:G20"))) // balance-sheet cash is the cash-flow closing cash141 for check in ["Check_Opening", "Check_Balance", "Check_Cash", "Check_Equity"] { #expect(abs(wb.number(check)) < 1e-6, "\(check)") }142 #expect(allClose(wb.nums(2, "B12:G12"), wb.nums(2, "B22:G22"), rel: 1e-12)) // assets = liabilities + equity, opening included143 #expect(X.d("financial-statements.max_imbalance") < 1e-9)144 // propagation: three assumptions change, the statements still balance and match the independent model145 e.setInput("Revenue_Growth", .number(0.15)); e.setInput("Tax_Rate", .number(0.30)); e.setInput("AR_Days", .number(70))146 #expect(allClose(wb.nums(1, "C17:G17"), X.a("financial-statements.alt_net_income")))147 #expect(allClose(wb.nums(3, "C20:G20"), X.a("financial-statements.alt_closing_cash")))148 #expect(allClose(wb.nums(2, "C12:G12"), X.a("financial-statements.alt_total_assets")))149 #expect(allClose(wb.nums(2, "B12:G12"), wb.nums(2, "B22:G22"), rel: 1e-12))150 #expect(abs(wb.number("Check_Balance")) < 1e-6 && abs(wb.number("Check_Cash")) < 1e-6)151 #expect(wb.texts(0, "F19:F22").allSatisfy { $0 == "OK" })152 // an opening balance sheet that does not balance is reported153 e.setInput("Open_Cash", .number(500))154 #expect(wb.cell(0, "F19").textValue != "OK" && wb.cell(0, "F20").textValue != "OK")155 #expect(errorCells(wb).isEmpty)156 }157158 @Test(arguments: locales) func cashFlowForecast(_ l: Locale) {159 let (wb, e) = fresh("cash-flow-forecast", l)160 let closing = X.a("cash-flow-forecast.closing")161 #expect(allClose(wb.nums(0, "B26:N26"), closing))162 #expect(close(wb.number("Lowest_Balance"), X.d("cash-flow-forecast.lowest")))163 #expect(close(wb.number("Weeks_Below_Min"), X.d("cash-flow-forecast.weeks_below")))164 #expect(wb.number("Weeks_Below_Min") >= 1, "the sample must demonstrate the alert")165 #expect(close(wb.number("Ending_Cash"), X.d("cash-flow-forecast.ending")))166 #expect(close(wb.num(0, "O26"), X.d("cash-flow-forecast.ending"))) // opening + total net flow ties to the last closing balance167 #expect(close(wb.num(0, "O15"), X.d("cash-flow-forecast.total_receipts")))168 #expect(allClose(wb.nums(0, "C11:N11"), Array(closing.dropLast()))) // each opening balance is the previous closing balance169 let low = Int(X.d("cash-flow-forecast.lowest_week"))170 #expect(close(wb.num(0, "G6"), wb.num(0, "B10") + Double(7 * (low - 1)))) // date of the low point171 e.setInput("Opening_Cash", .number(96_000))172 #expect(allClose(wb.nums(0, "B26:N26"), closing.map { $0 + 10_000 }))173 e.setInput("Min_Cash", .number(1_000_000))174 #expect(wb.number("Weeks_Below_Min") == 13)175 #expect(wb.texts(0, "B28:N28").allSatisfy { $0 == (l == french ? "BAS" : "LOW") })176 #expect(errorCells(wb).isEmpty)177 }178179 @Test(arguments: locales) func mortgage(_ l: Locale) {180 let (wb, e) = fresh("mortgage", l)181 #expect(close(wb.number("Loan_Amount"), X.d("mortgage.loan")))182 #expect(close(wb.number("Periodic_Rate"), X.d("mortgage.canadian.periodic_rate"), rel: 1e-12))183 #expect(close(wb.number("Payment"), X.d("mortgage.canadian.payment"), rel: 1e-11))184 #expect(close(wb.number("Total_Interest"), X.d("mortgage.canadian.total_interest"), rel: 1e-9))185 #expect(close(wb.number("Total_Paid"), X.d("mortgage.canadian.total_paid"), rel: 1e-9))186 #expect(wb.num(0, "E10") == 300) // payments actually made187 #expect(abs(wb.number("Final_Balance")) < 1e-6, "the schedule ends at a zero balance")188 #expect(abs(wb.num(1, "H305")) < 1e-6 && wb.cell(1, "H306") == .text("")) // payment 300 is the last row189 #expect(close(wb.num(0, "B18"), X.d("mortgage.canadian.first_interest")) && wb.num(0, "E19") == 0) // IPMT = schedule190 #expect(close(wb.num(0, "B18") + wb.num(0, "B19"), wb.number("Payment"))) // IPMT + PPMT = PMT191 // closed form of the Canadian convention, written out here as a third independent source192 let r = pow(1 + 0.0549 / 2, 2.0 / 12) - 1193 #expect(close(wb.number("Payment"), 420_000 * r / (1 - pow(1 + r, -300)), rel: 1e-12))194 // propagation195 e.setInput("Canadian_Mode", .text(l == french ? "Non" : "No"))196 #expect(close(wb.number("Payment"), X.d("mortgage.us.payment"), rel: 1e-11))197 #expect(close(wb.number("Total_Interest"), X.d("mortgage.us.total_interest"), rel: 1e-9))198 e.setInput("Canadian_Mode", .text(l == french ? "Oui" : "Yes"))199 e.setInput("Extra_Payment", .number(300))200 #expect(close(wb.number("Total_Interest"), X.d("mortgage.extra_300.total_interest"), rel: 1e-9))201 #expect(wb.num(0, "E10") == X.d("mortgage.extra_300.payments"))202 #expect(abs(wb.number("Final_Balance")) < 1e-6)203 #expect(wb.num(0, "E14") > 0) // interest saved204 e.setInput("Extra_Payment", .number(0)); e.setInput("Pay_Per_Year", .number(26))205 #expect(close(wb.number("Payment"), X.d("mortgage.biweekly.payment"), rel: 1e-11))206 #expect(wb.num(0, "E10") == 650 && abs(wb.number("Final_Balance")) < 1e-6)207 #expect(errorCells(wb).isEmpty)208 }209210 @Test(arguments: locales) func investmentAnalysis(_ l: Locale) {211 let (wb, e) = fresh("investment-analysis", l)212 for (name, key, tol) in [("NPV", "npv", 1e-10), ("IRR", "irr", 1e-8), ("MIRR", "mirr", 1e-10), ("Payback", "payback", 1e-10), ("PI", "pi", 1e-10), ("XNPV", "xnpv", 1e-10), ("XIRR", "xirr", 1e-7)] {213 #expect(close(wb.number("Result_\(name)"), X.d("investment-analysis.\(key)"), rel: tol), "\(name): \(wb.number("Result_\(name)")) vs \(X.d("investment-analysis.\(key)"))")214 }215 #expect(allClose(wb.nums(0, "B35:B45"), X.a("investment-analysis.sensitivity"), rel: 1e-10))216 #expect(close(wb.num(0, "G19"), wb.number("Result_NPV"))) // last cumulative present value = NPV217 let irr = wb.number("Result_IRR")218 e.setInput("Discount_Rate", .number(0.12))219 #expect(close(wb.number("Result_NPV"), X.d("investment-analysis.npv_at_12"), rel: 1e-10))220 #expect(close(wb.number("Result_NPV"), wb.num(0, "B41"), rel: 1e-12)) // the 12 % row of the sensitivity table221 #expect(wb.number("Result_IRR") == irr) // IRR does not depend on the discount rate222 e.setInput("Discount_Rate", .number(irr))223 #expect(abs(wb.number("Result_NPV")) < 1e-4, "NPV at the IRR is zero")224 e.setInput("Discount_Rate", .number(0.30))225 #expect(wb.number("Result_NPV") < 0 && wb.cell(0, "C31").textValue == (l == french ? "Rejeter" : "Reject"))226 #expect(errorCells(wb).isEmpty)227 }228229 @Test(arguments: locales) func dcfValuation(_ l: Locale) {230 let (wb, e) = fresh("dcf-valuation", l)231 for (name, key) in [("Cost_of_Equity", "cost_of_equity"), ("WACC", "wacc"), ("Enterprise_Value", "ev"), ("Equity_Value", "equity"), ("Value_Per_Share", "per_share"), ("Value_Per_Share_Exit", "per_share_exit")] {232 #expect(close(wb.number(name), X.d("dcf-valuation.\(key)"), rel: 1e-11), "\(name)")233 }234 #expect(close(wb.num(1, "G17"), X.d("dcf-valuation.fcf5"), rel: 1e-11))235 #expect(close(wb.num(2, "B13"), X.d("dcf-valuation.pv_fcf"), rel: 1e-11))236 let grid = X.m("dcf-valuation.grid")237 #expect(allClose(wb.nums(2, "C30:G34"), grid.flatMap { $0 }, rel: 1e-10))238 #expect(close(wb.num(2, "E32"), wb.number("Value_Per_Share"), rel: 1e-12)) // centre of the grid239 let g = wb.nums(2, "C30:G34")240 for r in 0..<5 { for c in 0..<5 {241 if r > 0 { #expect(g[r * 5 + c] < g[(r - 1) * 5 + c], "value falls when the WACC rises") }242 if c > 0 { #expect(g[r * 5 + c] > g[r * 5 + c - 1], "value rises with terminal growth") }243 } }244 // propagation: a riskier share (higher beta) raises the WACC and lowers the value, monotonically245 var last = wb.number("Value_Per_Share")246 for beta in [1.25, 1.35, 1.5] {247 e.setInput("Beta", .number(beta))248 #expect(wb.number("Value_Per_Share") < last); last = wb.number("Value_Per_Share")249 }250 #expect(close(wb.number("WACC"), X.d("dcf-valuation.wacc_beta_1_5"), rel: 1e-11))251 #expect(close(wb.number("Value_Per_Share"), X.d("dcf-valuation.per_share_beta_1_5"), rel: 1e-10))252 #expect(close(wb.num(2, "E32"), wb.number("Value_Per_Share"), rel: 1e-12))253 e.setInput("Shares_Out", .number(96))254 #expect(close(wb.number("Value_Per_Share"), X.d("dcf-valuation.per_share_beta_1_5") / 2, rel: 1e-10))255 #expect(errorCells(wb).isEmpty)256 }257258 @Test(arguments: locales) func projectPlan(_ l: Locale) {259 let (wb, e) = fresh("project-plan", l)260 #expect(wb.nums(0, "E12:E23") == X.a("project-plan.starts")) // numpy business-day calendar261 #expect(wb.nums(0, "G12:G23") == X.a("project-plan.ends"))262 #expect(wb.number("Project_Finish") == X.d("project-plan.finish"))263 #expect(wb.number("Tasks_Late") == X.d("project-plan.late") && wb.num(0, "C8") == X.d("project-plan.done"))264 #expect(close(wb.number("Project_Progress"), X.d("project-plan.progress")))265 let words = l == french ? ["done": "Terminée", "late": "En retard", "progress": "En cours", "todo": "À venir"] : ["done": "Done", "late": "Late", "progress": "In progress", "todo": "Not started"]266 #expect(wb.texts(0, "I12:I23") == X.s("project-plan.states").map { words[$0]! })267 #expect(wb.number("Tasks_Late") >= 1, "the sample must show a late task")268 // the spilled FILTER lists exactly the late tasks269 let lateNames = zip(wb.texts(0, "B12:B23"), wb.texts(0, "I12:I23")).filter { $0.1 == words["late"] }.map(\.0)270 #expect(Array(wb.texts(0, "B27:B38").prefix(lateNames.count)) == lateNames)271 // weekends and holidays are never start or end dates272 for d in wb.nums(0, "E12:E23") + wb.nums(0, "G12:G23") { #expect((2...6).contains(DateSerial.weekday(d))) }273 // propagation: a longer first task pushes the whole chain; a far status date makes every unfinished task late274 let finish = wb.number("Project_Finish")275 e.setCell(0, "F12", .number(10))276 #expect(wb.number("Project_Finish") > finish)277 #expect(wb.num(0, "E13") > X.a("project-plan.starts")[1])278 e.setInput("Status_Date", .number(DateSerial.serial(year: 2029, month: 1, day: 1)))279 #expect(wb.number("Tasks_Late") == 12 - X.d("project-plan.done"))280 #expect(errorCells(wb).isEmpty)281 }282283 @Test(arguments: locales) func inventory(_ l: Locale) {284 let (wb, e) = fresh("inventory", l)285 #expect(wb.nums(1, "G6:G21") == X.a("inventory.received") && wb.nums(1, "H6:H21") == X.a("inventory.issued"))286 #expect(wb.nums(1, "I6:I21") == X.a("inventory.on_hand"))287 #expect(X.d("inventory.min_on_hand") >= 0, "the sample never shows a negative stock")288 #expect(close(wb.number("Stock_Value"), X.d("inventory.stock_value")))289 #expect(wb.number("Units_On_Hand") == X.d("inventory.units") && wb.number("Items_To_Reorder") == X.d("inventory.to_reorder"))290 #expect(wb.number("Items_To_Reorder") >= 1)291 #expect(allClose(wb.nums(0, "D10:D13"), X.a("inventory.value_by_category")))292 #expect(close(wb.num(0, "D14"), wb.number("Stock_Value")))293 // lookup card (XLOOKUP)294 e.setInput("Lookup_SKU", .text("EL-2001"))295 #expect(wb.num(0, "H14") == X.a("inventory.on_hand")[4] && wb.num(0, "H13") == 2.95)296 // propagation: a new issue of 10 boxes of HW-1001 in the first free row of the log297 let value = wb.number("Stock_Value")298 e.setCell(2, "B40", .text("HW-1001")); e.setCell(2, "D40", .text(l == french ? "Sortie" : "Out")); e.setCell(2, "E40", .number(10))299 #expect(wb.num(1, "I6") == X.a("inventory.on_hand")[0] - 10)300 #expect(close(wb.number("Stock_Value"), value - 64))301 #expect(close(wb.num(2, "G40"), -64))302 e.setCell(2, "B41", .text("ZZ-0000"))303 #expect(wb.cell(2, "C41") == .text("?")) // unknown SKU is flagged, not an error304 #expect(errorCells(wb).isEmpty)305 }306307 @Test(arguments: locales) func salesTracking(_ l: Locale) {308 let (wb, e) = fresh("sales-tracking", l)309 #expect(close(wb.number("Total_Revenue"), X.d("sales-tracking.revenue")) && wb.number("Sales_Count") == X.d("sales-tracking.count"))310 #expect(close(wb.number("Average_Sale"), X.d("sales-tracking.average")) && wb.num(0, "I6") == X.d("sales-tracking.units"))311 #expect(wb.nums(0, "B10:B21") == X.a("sales-tracking.by_month")) // SORT(UNIQUE(month keys)) is chronological312 #expect(wb.texts(0, "A10:A21") == (1...12).map { String(format: "2026-%02d", $0) })313 #expect(wb.nums(0, "F10:F14").sorted() == X.a("sales-tracking.by_rep").sorted())314 #expect(wb.nums(0, "K10:K15").sorted() == X.a("sales-tracking.by_product").sorted())315 #expect(wb.nums(0, "F26:F28") == X.a("sales-tracking.top") && wb.cell(0, "F29").isBlank) // top 3, sorted316 #expect(close(wb.nums(0, "G10:G14").reduce(0, +), 1))317 e.setInput("Top_N", .number(5))318 #expect(wb.nums(0, "F26:F30") == X.a("sales-tracking.top5"))319 // propagation: one more unit on the first sale320 let revenue = wb.number("Total_Revenue"), price = wb.num(1, "F6")321 e.setCell(1, "E6", .number(wb.num(1, "E6") + 1))322 #expect(close(wb.number("Total_Revenue"), revenue + price))323 #expect(close(wb.num(0, "B10"), X.a("sales-tracking.by_month")[0] + price))324 #expect(errorCells(wb).isEmpty)325 }326327 @Test(arguments: locales) func researchDataset(_ l: Locale) {328 let (wb, e) = fresh("research-dataset", l)329 #expect(wb.number("N_Participants") == X.d("research-dataset.n") && wb.number("N_Complete") == X.d("research-dataset.complete"))330 #expect(wb.number("N_Missing") == X.d("research-dataset.missing"))331 #expect(close(wb.number("Mean_Change"), X.d("research-dataset.mean_change")))332 #expect(allClose(wb.nums(0, "F10:F11"), X.a("research-dataset.mean_change_by_group")))333 #expect(allClose(wb.nums(0, "D10:D11"), X.a("research-dataset.mean_pre_by_group")) && allClose(wb.nums(0, "C10:C11"), X.a("research-dataset.mean_age_by_group")))334 #expect(wb.nums(0, "B10:B11") == X.a("research-dataset.n_by_group"))335 #expect(wb.nums(0, "I10:K11") == X.m("research-dataset.group_site").flatMap { $0 })336 #expect(wb.nums(2, "E9:E9") + wb.nums(2, "E11:E13") == X.a("research-dataset.valid")) // codebook: age, pre, post, change337 #expect(wb.num(0, "L12") == 48)338 // propagation: one more missing post score339 e.set(sheet: 1, "G6", .blank); e.recalculate()340 #expect(wb.number("N_Missing") == X.d("research-dataset.missing") + 1 && wb.number("N_Complete") == X.d("research-dataset.complete") - 1)341 #expect(wb.num(2, "E13") == X.a("research-dataset.valid")[3] - 1 && wb.cell(1, "H6") == .text(""))342 #expect(errorCells(wb).isEmpty)343 }344345 @Test(arguments: locales) func statisticalAnalysis(_ l: Locale) {346 let (wb, e) = fresh("statistical-analysis", l)347 let k = "statistical-analysis."348 for (name, key, tol) in [("Mean_A", "mean_a", 1e-12), ("Mean_B", "mean_b", 1e-12), ("SD_A", "sd_a", 1e-12), ("CI_Lower_A", "ci_lower_a", 1e-10), ("CI_Upper_A", "ci_upper_a", 1e-10),349 ("T_Pooled", "t_pooled", 1e-11), ("P_Pooled", "p_pooled", 1e-9), ("T_Welch", "t_welch", 1e-11), ("DF_Welch", "df_welch", 1e-11), ("P_Welch", "p_welch", 1e-9),350 ("Corr_R", "r", 1e-12), ("Corr_P", "p_r", 1e-7), ("Corr_Slope", "slope", 1e-11)] {351 #expect(close(wb.number(name), X.d(k + key), rel: tol, abs: 0), "\(name): \(wb.number(name)) vs scipy \(X.d(k + key))")352 }353 #expect(close(wb.num(0, "C12"), X.d(k + "sd_b"), rel: 1e-12) && close(wb.num(0, "B11"), X.d(k + "median_a")))354 #expect(close(wb.num(0, "B15"), X.d(k + "q1_a"), rel: 1e-12) && close(wb.num(0, "C16"), X.d(k + "q3_b"), rel: 1e-12))355 #expect(close(wb.num(0, "B18"), X.d(k + "skew_a"), rel: 1e-10) && close(wb.num(0, "C19"), X.d(k + "kurt_b"), rel: 1e-10))356 #expect(close(wb.num(0, "F13"), wb.num(0, "F14"), rel: 1e-9)) // T.DIST.2T(t, df) = T.TEST(…, 2, 2)357 #expect(close(wb.num(0, "F34"), X.d(k + "intercept"), rel: 1e-11))358 #expect(wb.nums(0, "I9:I17") == X.a(k + "freq_a") && wb.nums(0, "J9:J17") == X.a(k + "freq_b")) // FREQUENCY, overflow class included359 #expect(wb.num(0, "I18") == 24 && wb.num(0, "J18") == 24)360 // propagation: a stricter level widens the interval; shifting sample B moves its mean and the test361 let lower = wb.number("CI_Lower_A"), p = wb.number("P_Welch")362 e.setInput("Alpha", .number(0.01))363 #expect(wb.number("CI_Lower_A") < lower && wb.number("P_Welch") == p)364 for r in 6...29 { let a = "C\(r)"; e.set(sheet: 1, a, .value(.number(wb.num(1, a) + 6))) }365 e.recalculate()366 #expect(close(wb.number("Mean_B"), X.d(k + "mean_b") + 6) && close(wb.num(0, "C12"), X.d(k + "sd_b"), rel: 1e-10))367 #expect(wb.number("P_Welch") < 0.001 && wb.cell(0, "F18").textValue == (l == french ? "Rejeter H0" : "Reject H0"))368 #expect(errorCells(wb).isEmpty)369 }370371 @Test(arguments: locales) func regressionReport(_ l: Locale) {372 let (wb, e) = fresh("regression-report", l)373 let s = "regression-report.simple.", m = "regression-report.multiple."374 #expect(allClose(wb.nums(0, "B9:B10"), X.a(s + "beta"), rel: 1e-10) && allClose(wb.nums(0, "C9:C10"), X.a(s + "se"), rel: 1e-9))375 #expect(close(wb.number("Simple_R2"), X.d(s + "r2"), rel: 1e-10) && close(wb.num(0, "F16"), X.d(s + "f"), rel: 1e-9) && close(wb.num(0, "F15"), X.d(s + "see"), rel: 1e-10))376 #expect(close(wb.num(0, "B13"), wb.number("Simple_Slope"), rel: 1e-10) && close(wb.num(0, "B14"), wb.number("Simple_Intercept"), rel: 1e-10)) // SLOPE / INTERCEPT agree with LINEST377 #expect(close(wb.num(0, "B15"), wb.number("Simple_R2"), rel: 1e-10) && close(wb.num(0, "B16"), wb.num(0, "F15"), rel: 1e-10)) // RSQ, STEYX378 #expect(allClose(wb.nums(0, "B21:B24"), X.a(m + "beta"), rel: 1e-9), "coefficients vs numpy.linalg.lstsq")379 #expect(allClose(wb.nums(0, "C21:C24"), X.a(m + "se"), rel: 1e-8) && allClose(wb.nums(0, "D21:D24"), X.a(m + "t"), rel: 1e-8))380 #expect(allClose(wb.nums(0, "E21:E24"), X.a(m + "p"), rel: 1e-6, abs: 1e-14))381 #expect(allClose(wb.nums(0, "F21:G24"), X.m(m + "ci95").flatMap { $0 }, rel: 1e-8))382 #expect(close(wb.number("Multi_R2"), X.d(m + "r2"), rel: 1e-10) && close(wb.num(0, "F28"), X.d(m + "adj_r2"), rel: 1e-10))383 #expect(close(wb.number("Multi_SE"), X.d(m + "see"), rel: 1e-9) && close(wb.number("Multi_F"), X.d(m + "f"), rel: 1e-8) && wb.num(0, "F31") == X.d(m + "df"))384 #expect(close(wb.num(0, "F32"), X.d(m + "p_f"), rel: 1e-6))385 #expect(close(wb.num(0, "B28"), X.d(m + "ssr"), rel: 1e-9) && close(wb.num(0, "B29"), X.d(m + "sse"), rel: 1e-9))386 #expect(close(wb.num(0, "B30"), wb.num(0, "B31"), rel: 1e-10) && close(wb.num(0, "B32"), wb.num(0, "B29"), rel: 1e-9)) // SSR + SSE = DEVSQ(y); Σ residuals² = SSE387 #expect(close(wb.num(0, "B33"), X.d(m + "dw"), rel: 1e-8))388 #expect(allClose(wb.nums(1, "G6:G41"), X.a(m + "residuals"), rel: 1e-7, abs: 1e-8))389 #expect(abs(wb.nums(1, "G6:G41").reduce(0, +)) < 1e-8) // residuals sum to zero390 e.setCell(1, "B6", .number(wb.num(1, "B6") + 40))391 #expect(allClose(wb.nums(0, "B21:B24"), X.a("regression-report.multiple_y0_plus_40.beta"), rel: 1e-9))392 #expect(close(wb.number("Multi_R2"), X.d("regression-report.multiple_y0_plus_40.r2"), rel: 1e-10))393 #expect(errorCells(wb).isEmpty)394 }395396 @Test(arguments: locales) func dashboard(_ l: Locale) {397 let (wb, e) = fresh("dashboard", l)398 func check(_ key: String) {399 let k = "dashboard.\(key)."400 #expect(wb.number("Period_Row") == X.d(k + "row") && wb.number("KPI_Revenue") == X.d(k + "revenue") && wb.number("KPI_Orders") == X.d(k + "orders"))401 #expect(close(wb.number("KPI_Margin"), X.d(k + "margin")) && wb.number("KPI_Active") == X.d(k + "active"))402 #expect(close(wb.number("KPI_Revenue_Change"), X.d(k + "revenue_change")))403 #expect(close(wb.number("YTD_Revenue"), X.d(k + "ytd_revenue")) && close(wb.number("YTD_Margin"), X.d(k + "ytd_margin")) && wb.num(0, "H13") == X.d(k + "ytd_new"))404 let six = X.a(k + "last6_revenue")405 #expect(Array(wb.nums(0, "B17:B22").suffix(six.count)) == six, "\(key): trailing months")406 #expect(wb.cell(0, "A22").textValue == wb.string("Period"))407 }408 check("selected")409 for period in ["2025-03", "2026-12"] { e.setInput("Period", .text(period)); check(period); #expect(errorCells(wb).isEmpty) }410 #expect(wb.cell(0, "A17") != .text(""))411 e.setInput("Period", .text("2025-03"))412 #expect(wb.texts(0, "A17:A19") == ["", "", ""] && wb.cell(0, "A20").textValue == "2025-01") // fewer than six months of history413 e.setInput("Period", .text("2025-01"))414 #expect(wb.cell(0, "B9") == .text("") && errorCells(wb).isEmpty) // no previous month415 }416417 @Test(arguments: locales) func gradebook(_ l: Locale) {418 let (wb, e) = fresh("gradebook", l)419 #expect(allClose(wb.nums(0, "J9:J28"), X.a("gradebook.weighted"), rel: 1e-12) && allClose(wb.nums(0, "K9:K28"), X.a("gradebook.final"), rel: 1e-12))420 #expect(wb.texts(0, "L9:L28") == X.s("gradebook.letters") && wb.nums(0, "M9:M28") == X.a("gradebook.rank"))421 #expect(close(wb.number("Class_Mean"), X.d("gradebook.mean")) && close(wb.number("Class_Median"), X.d("gradebook.median")) && close(wb.number("Class_SD"), X.d("gradebook.sd")))422 #expect(wb.number("Class_At_Risk") == X.d("gradebook.at_risk") && wb.number("Class_At_Risk") >= 1)423 #expect(wb.string("Class_Top") == Sample.Gradebook.students[Int(X.d("gradebook.top"))])424 #expect(close(wb.number("Weights_Total"), 1) && wb.cell(0, "K7").textValue == "OK")425 #expect(wb.nums(2, "F7:F18").reduce(0, +) == 20) // every student has a letter of the scale426 // exemption: student 4 (row 12) is exempt from Quiz 2 — the other five weights are rescaled427 let row = Sample.Gradebook.scores[3], a = Sample.Gradebook.assessments428 var num = 0.0, den = 0.0429 for (j, x) in row.enumerated() { if case .score(let v) = x { num += v / a[j].max * a[j].weight; den += a[j].weight } }430 #expect(close(den, 0.95) && close(wb.num(0, "J12"), num / den))431 // propagation: late penalty, a perfect final exam, a new exemption432 let before = wb.num(0, "K19") // student 11: three late days433 e.setInput("Late_Penalty", .number(0.05))434 #expect(close(wb.num(0, "K19"), before - 3 * 0.04))435 let final1 = wb.num(0, "K9"), old = wb.num(0, "H9")436 e.setCell(0, "H9", .number(100))437 #expect(close(wb.num(0, "K9"), final1 + 0.35 * (100 - old) / 100))438 e.setCell(0, "H9", .text("EX"))439 #expect(close(wb.num(0, "J9") * 0.65, (final1 - 0.35 * old / 100))) // weighted over the remaining 65 %440 e.setCell(0, "H7", .number(0.30))441 #expect(wb.cell(0, "K7").textValue != "OK") // weights no longer total 100 %442 #expect(errorCells(wb).isEmpty)443 }444}445