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