Repository Public
1import Foundation2import SheetCore3import Statistics45/// One bar of a tornado chart.6public struct TornadoBar: Sendable, Hashable, Codable {7 public var input: String8 public var baseInput: Double9 public var lowInput: Double10 public var highInput: Double11 /// Output with the input at its low / high value (all other inputs at base); NaN when not numeric.12 public var outputAtLow: Double13 public var outputAtHigh: Double14 /// outputAtLow − base output and outputAtHigh − base output.15 public var deltaLow: Double16 public var deltaHigh: Double17 /// |outputAtHigh − outputAtLow|: the length of the bar.18 public var swing: Double19 /// Arc elasticity (% change of output per % change of input) between the low and high points; NaN when undefined.20 public var elasticity: Double21}2223public struct TornadoResult: Sendable, Hashable, Codable, TableReport {24 public var output: String25 public var baseOutput: Double26 /// Fraction applied to each input (0.10 = ±10 %).27 public var percent: Double28 /// Bars sorted by decreasing swing (ties keep the order of the inputs; undefined swings last).29 public var bars: [TornadoBar]3031 public func table() -> [[Value]] {32 var rows: [[Value]] = [33 [.text("Sensitivity of"), .text(output)],34 Report.row("Base output", baseOutput),35 Report.row("Input change (±)", percent),36 [.text("Input"), .text("Base"), .text("Low"), .text("High"), .text("Output at low"), .text("Output at high"),37 .text("Δ low"), .text("Δ high"), .text("Swing"), .text("Elasticity")],38 ]39 for b in bars {40 rows.append([.text(b.input), Report.num(b.baseInput), Report.num(b.lowInput), Report.num(b.highInput), Report.num(b.outputAtLow),41 Report.num(b.outputAtHigh), Report.num(b.deltaLow), Report.num(b.deltaHigh), Report.num(b.swing), Report.num(b.elasticity)])42 }43 return Report.rectangular(rows)44 }45}4647/// One-at-a-time sensitivity (tornado) analysis.48public enum Sensitivity {49 /// Moves each input to base·(1 − percent) and base·(1 + percent), one at a time with the others at50 /// their base values, and records the output. Inputs must hold numbers (not formulas).51 /// - Parameters:52 /// - names: optional display names, parallel to `inputs` (default: cell labels).53 /// - percent: e.g. 0.10 for ±10 %.54 /// - zeroBaseStep: absolute ± step used for inputs whose base value is 0 (a percentage of 0 is 0);55 /// nil leaves such inputs unchanged (swing 0).56 public static func tornado(engine: CalcEngine, inputs: [ModelCell], names: [String]? = nil, output: ModelCell, percent: Double = 0.10,57 zeroBaseStep: Double? = nil, isolation: WhatIfIsolation = .clone) throws -> TornadoResult {58 let wb = engine.workbook59 guard percent > 0, percent.isFinite else { throw WhatIfError.invalidOptions("percent must be positive") }60 if let names, names.count != inputs.count { throw WhatIfError.invalidOptions("one name per input is required") }61 guard output.isValid(in: wb) else { throw WhatIfError.invalidCell(output.label(in: wb)) }62 var bases = [Double]()63 for c in inputs {64 guard c.isValid(in: wb) else { throw WhatIfError.invalidCell(c.label(in: wb)) }65 let cell = wb.sheets[c.sheet].cell(c.cell.row, c.cell.col)66 guard cell?.formula == nil, let n = (cell?.value ?? .blank).isBlank ? 0 : cell?.value.numberValue else {67 throw WhatIfError.invalidCell("\(c.label(in: wb)) must contain a number, not a formula or text")68 }69 bases.append(n)70 }71 return try ModelSandbox.run(on: engine, isolation: isolation) { box in72 let base = try box.number(output)73 var bars = [TornadoBar]()74 for (i, c) in inputs.enumerated() {75 let b = bases[i]76 let step = b != 0 ? abs(b) * percent : (zeroBaseStep ?? 0)77 let lo = b - step, hi = b + step78 try box.set(c, number: lo)79 let yLo = try box.number(output)80 try box.set(c, number: hi)81 let yHi = try box.number(output)82 box.reset(c)83 let mid = (yLo + yHi) / 284 let elasticity = step > 0 && b != 0 && mid != 0 ? ((yHi - yLo) / mid) / ((hi - lo) / b) : Double.nan85 bars.append(TornadoBar(input: names?[i] ?? c.label(in: wb), baseInput: b, lowInput: lo, highInput: hi, outputAtLow: yLo, outputAtHigh: yHi,86 deltaLow: yLo - base, deltaHigh: yHi - base, swing: abs(yHi - yLo), elasticity: elasticity))87 }88 let sorted = bars.enumerated().sorted { a, b in89 let x = a.element.swing.isFinite ? a.element.swing : -1, y = b.element.swing.isFinite ? b.element.swing : -190 return x != y ? x > y : a.offset < b.offset91 }.map(\.element)92 return TornadoResult(output: output.label(in: wb), baseOutput: base, percent: percent, bars: sorted)93 }94 }95}96