SPB Git

Repository Public

AnalysisTests.swift 17.1 KB 283 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Tests/WhatIfTests/AnalysisTests.swift10// Module           : WhatIfTests11// 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 Statistics91import AnalyticsTestSupport92@testable import WhatIf9394@Suite struct TornadoTests {95    @Test func profitModelSensitivities() throws {96        for isolation in WhatIfIsolation.allCases {97            let m = Model()98            m.seedHistory()99            let inputs = [m.cell("B4", sheet: 1), m.cell("B1", sheet: 1), m.cell("B2", sheet: 1), m.cell("B3", sheet: 1)]100            let t = try expectUntouched(m, isolation, "tornado") {101                try Sensitivity.tornado(engine: m.engine, inputs: inputs, names: ["Fixed cost", "Price", "Volume", "Unit cost"],102                                        output: m.cell("B7", sheet: 1), percent: 0.10, isolation: isolation)103            }104            #expect(t.baseOutput == 2000 && t.percent == 0.10 && t.output == "Profit!B7")105            // profit = (p − c)·v − F: ±10 % → price ±2500, unit cost ∓1500, volume ±1000, fixed ∓800106            #expect(t.bars.map(\.input) == ["Price", "Unit cost", "Volume", "Fixed cost"])107            #expect(allClose(t.bars.map(\.swing), [5000, 3000, 2000, 1600], rel: 1e-12))108            #expect(allClose(t.bars.map(\.deltaLow), [-2500, 1500, -1000, 800], rel: 1e-12))109            #expect(allClose(t.bars.map(\.deltaHigh), [2500, -1500, 1000, -800], rel: 1e-12))110            #expect(allClose(t.bars.map(\.lowInput), [22.5, 13.5, 900, 7200], rel: 1e-13))111            #expect(allClose(t.bars.map(\.highInput), [27.5, 16.5, 1100, 8800], rel: 1e-13))112            // elasticity of a linear model at base = slope·base/output: price 25·1000/2000 = 12.5113            #expect(allClose(t.bars.map(\.elasticity), [12.5, -7.5, 5, -4], rel: 1e-12))114            #expect(t.table().count == 8 && t.table()[4][0] == .text("Price"))115        }116    }117118    @Test func zeroBasesDefaultsAndValidation() throws {119        let m = Model()120        m.set("H1", "0"); m.set("H2", "4"); m.set("H3", "=10*H1+H2^2")121        let plain = try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("H1"), m.cell("H2")], output: m.cell("H3"), percent: 0.5)122        #expect(plain.bars.map(\.input) == ["Loan!H2", "Loan!H1"])        // default names = cell labels123        #expect(plain.bars[0].swing == 36 - 4 && plain.bars[1].swing == 0 && plain.bars[1].elasticity.isNaN)124        let stepped = try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("H1"), m.cell("H2")], output: m.cell("H3"), percent: 0.5, zeroBaseStep: 3)125        #expect(stepped.bars.map(\.input) == ["Loan!H1", "Loan!H2"] && stepped.bars[0].swing == 60 && stepped.bars[0].lowInput == -3)126        #expect(throws: WhatIfError.self) { try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("H3")], output: m.cell("H3")) }      // formula input127        #expect(throws: WhatIfError.self) { try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("A1")], output: m.cell("H3")) }      // text input128        #expect(throws: WhatIfError.self) { try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("H1")], output: m.cell("H3"), percent: 0) }129        #expect(throws: WhatIfError.self) { try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("H1")], names: [], output: m.cell("H3")) }130        // negative base: low < high in input terms131        m.set("H2", "-4")132        let neg = try Sensitivity.tornado(engine: m.engine, inputs: [m.cell("H2")], output: m.cell("H3"), percent: 0.25)133        #expect(neg.bars[0].lowInput == -5 && neg.bars[0].highInput == -3 && neg.bars[0].outputAtLow == 25 && neg.bars[0].outputAtHigh == 9)134    }135}136137@Suite struct BreakEvenTests {138    @Test func linearProfitModel() throws {139        for isolation in WhatIfIsolation.allCases {140            let m = Model()141            m.seedHistory()142            // volume at which profit = 0: F / (p − c) = 8000 / 10143            let v = try expectUntouched(m, isolation, "break-even volume") {144                try BreakEven.find(engine: m.engine, changing: m.cell("B2", sheet: 1), output: m.cell("B7", sheet: 1), isolation: isolation)145            }146            #expect(v.found && v.points.count == 1 && isClose(v.points[0].input, 800, rel: 1e-10) && abs(v.points[0].residual) < 1e-8)147            // same question asked as revenue = total cost148            let rc = try expectUntouched(m, isolation, "revenue = cost") {149                try BreakEven.find(engine: m.engine, changing: m.cell("B2", sheet: 1), output: m.cell("B5", sheet: 1), equals: .cell(m.cell("B6", sheet: 1)), isolation: isolation)150            }151            #expect(isClose(rc.points.first?.input ?? .nan, 800, rel: 1e-10) && isClose(rc.points.first?.output ?? .nan, 20_000, rel: 1e-10))152            // break-even price at the current volume: c + F/v = 23; and a target profit of 5000 → 28153            let p = try BreakEven.find(engine: m.engine, changing: m.cell("B1", sheet: 1), output: m.cell("B7", sheet: 1), isolation: isolation)154            #expect(isClose(p.points.first?.input ?? .nan, 23, rel: 1e-10))155            let p5 = try BreakEven.find(engine: m.engine, changing: m.cell("B1", sheet: 1), output: m.cell("B7", sheet: 1), equals: .value(5000), isolation: isolation)156            #expect(isClose(p5.points.first?.input ?? .nan, 28, rel: 1e-10) && isClose(p5.points.first?.output ?? .nan, 5000, rel: 1e-9))157            #expect(v.table().count == 4)158        }159    }160161    @Test func scanFindsEveryCrossing() throws {162        for isolation in WhatIfIsolation.allCases {163            let m = Model()164            // profit with capacity costs: −(q − 10)(q − 40) → break-evens at 10 and 40165            m.set("H1", "20"); m.set("H2", "=-(H1-10)*(H1-40)")166            m.seedHistory()167            let r = try expectUntouched(m, isolation, "scan") {168                try BreakEven.find(engine: m.engine, changing: m.cell("H1"), output: m.cell("H2"), searchRange: 0...100, steps: 37, isolation: isolation)169            }170            #expect(r.points.count == 2 && r.message == "2 break-even points found.")171            #expect(isClose(r.points[0].input, 10, rel: 1e-10) && isClose(r.points[1].input, 40, rel: 1e-10))172            // roots that fall exactly on grid points are reported once each173            let grid = try BreakEven.find(engine: m.engine, changing: m.cell("H1"), output: m.cell("H2"), searchRange: 0...100, steps: 10, isolation: isolation)174            #expect(grid.points.map(\.input) == [10, 40])175            let ends = try BreakEven.find(engine: m.engine, changing: m.cell("H1"), output: m.cell("H2"), searchRange: 10...40, steps: 6, isolation: isolation)176            #expect(ends.points.map(\.input) == [10, 40])177            let none = try expectUntouched(m, isolation, "scan without crossing") {178                try BreakEven.find(engine: m.engine, changing: m.cell("H1"), output: m.cell("H2"), searchRange: 50...90, isolation: isolation)179            }180            #expect(!none.found && none.message.contains("No break-even"))181            // level 225 is the maximum of the parabola (double root at 25): no sign change, none reported by the scan182            let never = try BreakEven.find(engine: m.engine, changing: m.cell("H1"), output: m.cell("H2"), equals: .value(300), isolation: isolation)183            #expect(!never.found)184        }185    }186187    @Test func validation() throws {188        let m = Model()189        #expect(throws: WhatIfError.self) { try BreakEven.find(engine: m.engine, changing: m.cell("B5", sheet: 1), output: m.cell("B7", sheet: 1)) }190        #expect(throws: WhatIfError.self) { try BreakEven.find(engine: m.engine, changing: m.cell("B2", sheet: 1), output: m.cell("B7", sheet: 1), searchRange: 0...1, steps: 0) }191        #expect(throws: WhatIfError.self) { try BreakEven.find(engine: m.engine, changing: m.cell("B2", sheet: 1), output: m.cell("B7", sheet: 1), equals: .cell(ModelCell(sheet: 8, row: 0, col: 0))) }192    }193}194195@Suite struct ModelSimulationTests {196    func inputs(_ m: Model) -> [ModelInput] {197        [ModelInput(cell: m.cell("B1", sheet: 1), name: "Price", distribution: .triangular(min: 22, mode: 25, max: 30)),198         ModelInput(cell: m.cell("B2", sheet: 1), name: "Volume", distribution: .normal(mean: 1000, sd: 120)),199         ModelInput(cell: m.cell("B3", sheet: 1), distribution: .uniform(min: 14, max: 17))]200    }201202    @Test func engineTrialsEqualTheClosureModel() throws {203        for isolation in WhatIfIsolation.allCases {204            let m = Model()205            m.seedHistory()206            var opt = MonteCarloOptions(trials: 1500, seed: 77)207            opt.progressInterval = 500208            var ticks = [Int]()209            let outputs = [ModelOutput(cell: m.cell("B7", sheet: 1), name: "Profit"), ModelOutput(cell: m.cell("B5", sheet: 1))]210            let r = try expectUntouched(m, isolation, "simulation") {211                try ModelSimulation.run(engine: m.engine, inputs: inputs(m), outputs: outputs, options: opt, isolation: isolation, progress: { d, _ in ticks.append(d) })212            }213            #expect(ticks == [0, 500, 1000, 1500])214            #expect(r.trialsCompleted == 1500 && !r.cancelled && r.outputs.map(\.name) == ["Profit", "Profit!B5"])215            #expect(r.inputs.map(\.name) == ["Price", "Volume", "Profit!B3"])216            // same seed, same distributions, model written as a closure → the same trials217            let ref = try MonteCarlo.run(inputs: r.inputs, outputNames: ["Profit", "Revenue"], options: opt) { x in218                [x[0] * x[1] - (x[2] * x[1] + 8000), x[0] * x[1]]219            }220            #expect(r.inputSamples == ref.inputSamples)221            #expect(allClose(r.outputSamples[0], ref.outputSamples[0], rel: 1e-12, abs: 1e-9))222            #expect(r.outputSamples[1] == ref.outputSamples[1])223            #expect(isClose(r.outputs[0].mean, ref.outputs[0].mean, rel: 1e-12))224            // E[profit] = (E[p] − E[c])·E[v] − F for independent inputs225            let expected = ((22.0 + 25 + 30) / 3 - 15.5) * 1000 - 8000226            #expect(abs(r.outputs[0].mean - expected) < 5 * r.outputs[0].standardError)227            #expect(r.outputs[0].sensitivities.first?.input == "Price" && r.outputs[0].invalidCount == 0)228            #expect(r.outputs[0].percentiles.count == 9 && r.outputs[0].histogram.counts.reduce(0, +) == 1500)229        }230    }231232    @Test func cancellationCorrelationAndInvalidTrials() throws {233        for isolation in WhatIfIsolation.allCases {234            let m = Model()235            m.seedHistory()236            var opt = MonteCarloOptions(trials: 2000, seed: 5)237            opt.progressInterval = 100238            var polls = 0239            let out = [ModelOutput(cell: m.cell("B7", sheet: 1))]240            let part = try expectUntouched(m, isolation, "cancelled simulation") {241                try ModelSimulation.run(engine: m.engine, inputs: inputs(m), outputs: out, options: opt, isolation: isolation, isCancelled: { polls += 1; return polls > 3 })242            }243            #expect(part.cancelled && part.trialsCompleted == 300 && part.outputs[0].count == 300)244            // cancelled before the first trial: nothing computed, nothing changed245            let zero = try expectUntouched(m, isolation, edits: false, "cancelled at once") {246                try ModelSimulation.run(engine: m.engine, inputs: inputs(m), outputs: out, options: opt, isolation: isolation, isCancelled: { true })247            }248            #expect(zero.cancelled && zero.trialsCompleted == 0 && zero.outputs[0].count == 0)249            // rank correlation between price and volume flows through to the inputs250            opt.correlation = MonteCarloCorrelation(matrix: [[1, -0.6, 0], [-0.6, 1, 0], [0, 0, 1]], method: .imanConover)251            let corr = try expectUntouched(m, isolation, "correlated simulation") {252                try ModelSimulation.run(engine: m.engine, inputs: inputs(m), outputs: out, options: opt, isolation: isolation)253            }254            #expect(abs((corr.achievedInputRankCorrelation?[0][1] ?? .nan) + 0.6) < 0.04)255            opt.correlation = nil256            // a model that errors for some draws: 1/x with x ∈ {0, 1, 2}257            m.set("H1", "1"); m.set("H2", "=1/H1")258            let risky = try ModelSimulation.run(engine: m.engine, inputs: [ModelInput(cell: m.cell("H1"), distribution: .discrete(values: [0, 1, 2], probabilities: [1, 1, 2]))],259                                                outputs: [ModelOutput(cell: m.cell("H2"), name: "inv")], options: opt, isolation: isolation)260            let o = risky.outputs[0]261            #expect(o.invalidCount > 400 && o.invalidCount < 600 && o.count + o.invalidCount == 2000 && o.minimum == 0.5 && o.maximum == 1)262            #expect(isClose(o.mean, 2.0 / 3, rel: 0.03))263        }264    }265266    @Test func validationLeavesTheWorkbookAlone() throws {267        let m = Model()268        let out = [ModelOutput(cell: m.cell("B7", sheet: 1))]269        let before = snapshot(m.engine), history = History(m.engine)270        #expect(throws: WhatIfError.self) { try ModelSimulation.run(engine: m.engine, inputs: [], outputs: out) }271        #expect(throws: WhatIfError.self) { try ModelSimulation.run(engine: m.engine, inputs: self.inputs(m), outputs: []) }272        #expect(throws: WhatIfError.self) { try ModelSimulation.run(engine: m.engine, inputs: self.inputs(m) + [self.inputs(m)[0]], outputs: out) }273        #expect(throws: WhatIfError.self) { try ModelSimulation.run(engine: m.engine, inputs: self.inputs(m), outputs: [ModelOutput(cell: ModelCell(sheet: 4, row: 0, col: 0))]) }274        for isolation in WhatIfIsolation.allCases {275            // an invalid distribution is rejected by the engine before any trial, in either mode276            #expect(throws: StatError.self) {277                try ModelSimulation.run(engine: m.engine, inputs: [ModelInput(cell: m.cell("B1", sheet: 1), distribution: .normal(mean: 0, sd: -1))], outputs: out, isolation: isolation)278            }279        }280        #expect(snapshot(m.engine) == before && History(m.engine) == history)281    }282}283
Files main
Menu

Theme