SPB Git

Repository Public

BreakEvenTool.swift 8.3 KB 114 lines swift

1import Foundation2import SheetCore3import Statistics4import WhatIf5import AnalysisKit67/// Break-even finder on the live model: where an output cell equals a level or another cell.8enum BreakEvenTool {9    static let descriptor: ToolDescriptor = {10        var t = ToolDescriptor(11            id: "breakeven", title: L("Break-Even Finder"), group: L("Solve"), systemImage: "equal.circle",12            summary: L("Finds the value(s) of an input cell at which an output cell equals a target value or another cell."),13            fields: [14                .cell("changing", L("Changing cell"), help: L("An input cell holding a number (not a formula).")),15                .cell("output", L("Output cell")),16                .choice("targetKind", L("Break-even when the output equals"), [FieldOption("value", L("A value")), FieldOption("cell", L("Another cell"))]),17                .number("target", L("Target value"), 0, visible: { $0.choice("targetKind") == "value" }),18                .cell("targetCell", L("Target cell"), visible: { $0.choice("targetKind") == "cell" }),19                .toggle("scan", L("Scan a range for all break-even points"), false,20                        help: L("Without a range, one break-even is searched from the current value of the changing cell.")),21                .number("min", L("Range minimum"), 0, visible: { $0.bool("scan") }),22                .number("max", L("Range maximum"), 100, visible: { $0.bool("scan") }),23                .integer("steps", L("Scan intervals"), 64, min: 2, max: 5000, visible: { $0.bool("scan") }),24            ],25            info: [26                L("Single search: automatic bracketing from the current value, Brent's method, secant fallback (as Goal Seek)."),27                L("Range scan: the range is cut into equal intervals and every sign change of (output − target) is refined with Brent's method, so several break-even points can be found."),28                L("Limitation: in range-scan mode a tangency (the curve touches the target without crossing it) is not detected; the single search can still find it."),29                L("Two crossings inside the same scan interval are seen as none: increase the number of intervals when in doubt."),30                L("The model is evaluated on a private copy of the workbook; validated against F/(p − c) and the two roots of a quadratic profit curve (1e-10 relative)."),31            ],32            needsModel: true, runTitle: L("Find"), run: run)33        t.demo = { _, v in34            v.set("changing", text: "Model!B5"); v.set("output", text: "Model!B15"); v.set("target", number: 0)35            v.set("scan", bool: true); v.set("min", number: 0); v.set("max", number: 20_000); v.set("steps", int: 64)36        }37        return t38    }()3940    static func run(_ input: ToolInput) throws -> ToolResult {41        let model = try input.requireModel()42        let wb = model.workbook43        let changing = try input.cell("changing"), output = try input.cell("output")44        try WhatIfSupport.requireInput(changing, in: wb)45        let useCell = input.values.choice("targetKind") == "cell"46        let targetCell = useCell ? try input.cell("targetCell") : nil47        let targetValue = input.values.number("target")48        guard useCell || targetValue.isFinite else { throw ToolError(L("The target value must be a finite number.")) }49        let target: BreakEvenTarget = targetCell.map { .cell($0.modelCell) } ?? .value(targetValue)50        var range: ClosedRange<Double>?51        if input.values.bool("scan") {52            let lo = input.values.number("min"), hi = input.values.number("max")53            guard lo.isFinite, hi.isFinite, lo < hi else { throw ToolError(L("The range minimum must be below the range maximum.")) }54            range = lo...hi55        }56        let steps = max(input.values.int("steps"), 2)57        let r = try BreakEven.find(engine: model, changing: changing.modelCell, output: output.modelCell, equals: target, searchRange: range, steps: steps)5859        let inName = WhatIfSupport.name(changing, in: wb), outName = WhatIfSupport.name(output, in: wb)60        let inFormat = WhatIfSupport.format(changing, in: wb), outFormat = WhatIfSupport.format(output, in: wb)61        let targetText = targetCell.map { WhatIfSupport.nameWithAddress($0, in: wb) } ?? Fmt.text(.number(targetValue), outFormat, locale: input.locale)62        var blocks: [ResultBlock] = []63        if r.found {64            let list = r.points.map { Fmt.text(.number($0.input), inFormat, locale: input.locale) }.joined(separator: "; ")65            blocks.append(.metrics([Metric(L("Break-even points"), Double(r.points.count), .integer)]66                                   + r.points.prefix(3).enumerated().map { Metric(L("%@ at point %d", inName, $0.offset + 1), $0.element.input, inFormat) }))67            blocks.append(.interpretation(r.points.count == 168                                          ? L("%@ equals %@ when %@ is %@.", outName, targetText, inName, list)69                                          : L("%@ equals %@ at %d values of %@: %@.", outName, targetText, r.points.count, inName, list)))70            var rows: [[Value]] = [[.text("#"), .text(inName), .text(outName), .text(L("Residual"))]]71            for (i, p) in r.points.enumerated() { rows.append([.number(Double(i + 1)), .number(p.input), .finite(p.output), .finite(p.residual)]) }72            blocks.append(.table(ReportTable(title: L("Break-even points"), rows: rows, headerColumn: false,73                                             columnFormats: [0: .integer, 1: inFormat, 2: outFormat, 3: .scientific])))74        } else {75            blocks.append(.warning(range == nil76                                   ? L("No break-even point was found from the current value of %@. Try scanning a range.", inName)77                                   : L("No break-even point was found for %@ between %@ and %@.", inName, Fmt.number(range!.lowerBound, locale: input.locale),78                                       Fmt.number(range!.upperBound, locale: input.locale))))79        }80        // curve of the gap over the range (or around the solution)81        var window = range82        if window == nil, let p = r.points.first {83            let current = wb.sheets[changing.sheet].value(changing.cell.row, changing.cell.col).numberValue ?? 084            let lo = min(p.input, current), hi = max(p.input, current)85            let pad = max((hi - lo) * 0.25, abs(p.input) * 0.1, 1e-6)86            window = (lo - pad)...(hi + pad)87        }88        if let w = window {89            let n = 6090            let xs = (0...n).map { w.lowerBound + (w.upperBound - w.lowerBound) * Double($0) / Double(n) }91            let outputs = [output.modelCell] + (targetCell.map { [$0.modelCell] } ?? [])92            let grid = try DataTable.oneVariable(engine: model, input: changing.modelCell, values: xs, outputs: outputs)93            let gap = grid.values.map { row -> Double in94                let y = row[0].numberValue ?? .nan95                return y - (targetCell == nil ? targetValue : (row[1].numberValue ?? .nan))96            }97            if gap.contains(where: \.isFinite) {98                var spec = ChartKit.lines([.init(L("%@ − target", outName), gap, x: xs)], xTitle: inName, yTitle: L("%@ − target", outName))99                spec.annotations = [.referenceLine(.horizontal, at: 0)] + r.points.map { .referenceLine(.vertical, at: $0.input) }100                blocks.append(.chart(ChartBlock(title: L("Distance to the target over %@", inName), spec: spec,101                                                summary: L("Line chart of %@ minus the target for %@ from %@ to %@; it crosses zero at %d point(s).", outName, inName,102                                                           Fmt.number(w.lowerBound, locale: input.locale), Fmt.number(w.upperBound, locale: input.locale), r.points.count),103                                                height: 220)))104            }105        }106        var notes = [L("Target: %@ = %@.", WhatIfSupport.nameWithAddress(output, in: wb), targetText),107                     L("Model recalculations: %d.", r.evaluations)]108        if range != nil { notes.append(L("Range scan with %d intervals: a tangency without a sign change is not detected.", steps)) }109        blocks.append(.note(notes))110        return ToolResult(title: L("Break-Even Finder"), subtitle: L("%@ = %@ by changing %@", outName, targetText, WhatIfSupport.nameWithAddress(changing, in: wb)),111                          blocks: blocks, sheetName: L("Break-Even"))112    }113}114
Files main
Menu

Theme