Repository Public
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