SPB Git

Repository Public

BreakEven.swift 5.3 KB 103 lines swift

1import Foundation2import SheetCore3import Statistics45/// What the output cell is compared with.6public enum BreakEvenTarget: Sendable, Hashable {7    /// A fixed level (0 for a profit cell).8    case value(Double)9    /// Another cell of the model (e.g. revenue against total cost).10    case cell(ModelCell)11}1213public struct BreakEvenPoint: Sendable, Hashable, Codable {14    /// Value of the changing cell at the break-even.15    public var input: Double16    /// Value of the output cell there.17    public var output: Double18    /// Output − target at that point (≈ 0).19    public var residual: Double20}2122public struct BreakEvenResult: Sendable, Hashable, Codable, TableReport {23    /// Break-even points in increasing order of the input (empty when none was found).24    public var points: [BreakEvenPoint]25    public var evaluations: Int26    public var message: String27    public var found: Bool { !points.isEmpty }2829    public func table() -> [[Value]] {30        var rows: [[Value]] = [[.text("Break-even analysis"), .blank, .blank], [.text("Input"), .text("Output"), .text("Residual")]]31        for p in points { rows.append([Report.num(p.input), Report.num(p.output), Report.num(p.residual)]) }32        rows.append([.text(message), .blank, .blank])33        return rows34    }35}3637/// Break-even finder: the input value(s) at which an output cell equals a level or another cell.38public enum BreakEven {39    /// - Parameters:40    ///   - searchRange: when given, the range is scanned on `steps` equal intervals and EVERY sign change41    ///     is refined with Brent's method (finds multiple break-evens, e.g. a profit curve that turns down again).42    ///     When nil, one break-even is searched from the current input value with automatic bracketing43    ///     and a secant fallback (as Goal Seek does).44    ///   - tolerance: largest acceptable |output − target|, multiplied by max(1, |target level|).45    /// The live workbook is not modified.46    public static func find(engine: CalcEngine, changing: ModelCell, output: ModelCell, equals target: BreakEvenTarget = .value(0),47                            searchRange: ClosedRange<Double>? = nil, steps: Int = 64, tolerance: Double = 1e-9,48                            isolation: WhatIfIsolation = .clone) throws -> BreakEvenResult {49        let wb = engine.workbook50        var cells = [changing, output]51        if case .cell(let c) = target { cells.append(c) }52        for c in cells { guard c.isValid(in: wb) else { throw WhatIfError.invalidCell(c.label(in: wb)) } }53        guard steps >= 1 else { throw WhatIfError.invalidOptions("steps must be ≥ 1") }54        let current = wb.sheets[changing.sheet].cell(changing.cell.row, changing.cell.col)55        guard current?.formula == nil else { throw WhatIfError.invalidCell("\(changing.label(in: wb)) contains a formula; the changing cell must hold a value") }56        let start = current?.value.numberValue ?? 057        return try ModelSandbox.run(on: engine, isolation: isolation) { box in58            var evals = 059            var lastOutput = Double.nan60            func gap(_ x: Double) throws -> Double {61                evals += 162                try box.set(changing, number: x)63                lastOutput = try box.number(output)64                switch target {65                case .value(let v): return lastOutput - v66                case .cell(let c): return lastOutput - (try box.number(c))67                }68            }69            func point(_ x: Double) throws -> BreakEvenPoint {70                let r = try gap(x)71                return BreakEvenPoint(input: x, output: lastOutput, residual: r)72            }73            var scale = 1.074            if case .value(let v) = target { scale = Swift.max(1, abs(v)) }75            let tol = tolerance * scale76            guard let range = searchRange else {77                let o = try WhatIfSolver.solve(start: start, tolerance: tol, maxIterations: 200, lower: nil, upper: nil, step: nil, gap)78                guard o.converged, let x = o.root else { return BreakEvenResult(points: [], evaluations: evals, message: o.message) }79                return BreakEvenResult(points: [try point(x)], evaluations: evals, message: "1 break-even point found.")80            }81            var points = [BreakEvenPoint]()82            let width = (range.upperBound - range.lowerBound) / Double(steps)83            var a = range.lowerBound84            var ga = try gap(a)85            for k in 1...steps {86                let b = k == steps ? range.upperBound : range.lowerBound + Double(k) * width87                let gb = try gap(b)88                if ga.isFinite, abs(ga) <= tol {89                    if points.last?.input != a { points.append(try point(a)) }90                } else if ga.isFinite, gb.isFinite, (ga < 0) != (gb < 0), abs(gb) > tol {91                    if let r = try? RootFinder.brent(gap, lower: a, upper: b, xtol: 1e-14), r.residual.isFinite, abs(r.residual) <= tol {92                        points.append(try point(r.root))93                    }94                }95                a = b; ga = gb96            }97            if ga.isFinite, abs(ga) <= tol, points.last?.input != a { points.append(try point(a)) }98            let msg = points.isEmpty ? "No break-even point in the search range." : "\(points.count) break-even point\(points.count == 1 ? "" : "s") found."99            return BreakEvenResult(points: points, evaluations: evals, message: msg)100        }101    }102}103
Files main
Menu

Theme