SPB Git

Repository Public

NumericTests.swift 30.2 KB 445 lines swift

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
Files main
Menu

Theme