Repository Public
1import Foundation2import SheetCore3import SheetUI4import FinanceModels5import WhatIf6import AnalysisKit78/// Two-way sensitivity of the DCF value per share (discount rate × terminal parameter).9struct DCFSensitivity: Sendable, Hashable {10 var rates: [Double]11 var parameters: [Double]12 /// values[i][j] = value per share at rates[i] and parameters[j].13 var values: [[Value]]14 var isGrowth: Bool15 var baseRate: Double16 var baseParameter: Double1718 /// Grid around the base case: WACC ± 1 point in half-point steps; growth ± 1 point in half-point steps, or the multiple ± 2x in 1x steps.19 static func axes(_ a: DCFAssumptions) -> (rates: [Double], parameters: [Double], isGrowth: Bool, base: Double) {20 let w = a.discountRate.rate21 let rates = [-0.01, -0.005, 0, 0.005, 0.01].map { ((w + $0) * 1e6).rounded() / 1e6 }22 switch a.terminal {23 case .gordonGrowth(let g): return (rates, [-0.01, -0.005, 0, 0.005, 0.01].map { ((g + $0) * 1e6).rounded() / 1e6 }, true, g)24 case .exitMultiple(let x): return (rates, [-2, -1, 0, 1, 2].map { x + $0 }.filter { $0 > 0 }, false, x)25 }26 }2728 /// Writes the live layout into a PRIVATE scratch workbook and runs `DataTable.twoVariable` on its29 /// discount-rate and terminal-parameter cells. The user's workbook is not involved.30 static func compute(_ a: DCFAssumptions) throws -> DCFSensitivity {31 var fixed = a32 fixed.discountRate = .fixed(a.discountRate.rate) // the CAPM layout has a formula in the WACC cell; the table needs an input cell33 let layout = try DCFModel.layout(fixed)34 let engine = CalcEngine(workbook: Workbook(sheetNames: ["DCF"]))35 try layout.write(to: engine, sheet: 0)36 engine.recalculate()37 guard let wacc = layout.names["wacc"], let param = layout.names["terminalParameter"], let out = layout.names["valuePerShare"] else {38 throw ToolError(L("The model layout lacks the cells needed for the sensitivity table."))39 }40 let ax = axes(a)41 let t = try DataTable.twoVariable(engine: engine, rowInput: ModelCell(sheet: 0, cell: param), rowValues: ax.parameters,42 columnInput: ModelCell(sheet: 0, cell: wacc), columnValues: ax.rates, output: ModelCell(sheet: 0, cell: out))43 return DCFSensitivity(rates: ax.rates, parameters: ax.parameters, values: t.values, isGrowth: ax.isGrowth, baseRate: a.discountRate.rate, baseParameter: ax.base)44 }4546 func parameterLabel(_ x: Double, locale: Locale) -> String { isGrowth ? Fmt.percent(x, 2, locale: locale) : Fmt.fixed(x, 1, locale: locale) + "x" }47 var cornerLabel: String { isGrowth ? L("WACC \\ terminal growth") : L("WACC \\ exit multiple") }4849 func table(locale: Locale) -> ReportTable {50 var rows: [[Value]] = [[.text(cornerLabel)] + parameters.map { .text(parameterLabel($0, locale: locale)) }]51 for (i, r) in rates.enumerated() { rows.append([.text(Fmt.percent(r, 2, locale: locale))] + values[i]) }52 var t = ReportTable(title: L("Sensitivity of the value per share"), rows: rows,53 note: L("Computed with a two-variable data table on a private copy of the live model; the base case is the centre of the grid."))54 for c in 1...max(parameters.count, 1) { t.columnFormats[c] = .money }55 return t56 }57}5859/// Inserts the DCF model as live formulas on a new sheet.60enum DCFInsertion {61 struct Inserted: Sendable {62 var sheet: Int63 /// Layout names → cell on the new sheet.64 var cells: [String: CellRef]65 /// Defined names created (workbook scope).66 var definedNames: [String]67 }6869 static func definedNameMap(_ a: DCFAssumptions) -> [(layout: String, name: String)] {70 var isGrowth = true71 if case .exitMultiple = a.terminal { isGrowth = false }72 return [("wacc", "DCF_WACC"), ("terminalParameter", isGrowth ? "DCF_TerminalGrowth" : "DCF_ExitMultiple"), ("taxRate", "DCF_TaxRate"),73 ("baseRevenue", "DCF_BaseRevenue"), ("terminalValue", "DCF_TerminalValue"), ("enterpriseValue", "DCF_EnterpriseValue"),74 ("equityValue", "DCF_EquityValue"), ("valuePerShare", "DCF_ValuePerShare")]75 }7677 /// Call inside ONE transaction (`session.perform`). Formulas are parsed before anything is written.78 static func insert(engine: CalcEngine, at index: Int?, assumptions a: DCFAssumptions, sensitivity: DCFSensitivity?, sheetName: String, locale: Locale) throws -> Inserted {79 let layout = try DCFModel.layout(a)80 var parsed = [(ModelLayout.Cell, CellInput)]()81 for c in layout.cells {82 switch c.content {83 case .label(let s): parsed.append((c, .value(.text(localized(s)))))84 case .number(let d): parsed.append((c, .value(.number(d))))85 case .formula(let f):86 guard let e = try? FormulaParser.parse(f) else { throw ToolError(L("A formula of the model could not be parsed: %@", f)) }87 parsed.append((c, .formula(e)))88 }89 }90 let wb = engine.workbook91 let si = engine.addSheet(named: ReportWriter.uniqueSheetName(sheetName, in: wb), at: index.map { min($0, wb.sheets.count) })92 for (c, content) in parsed {93 engine.set(sheet: si, row: c.row, col: c.col, content)94 let role = c.role, format = c.numberFormat95 engine.setStyle(sheet: si, range: RangeRef(CellRef(c.row, c.col))) { s in96 if let format { s.numberFormat = format }97 switch role {98 case .title: s.bold = true; s.fontSize = 1599 case .header: s.bold = true; if format == nil { s.textColor = BlockWriter.accent } else { s.borders.bottom = BorderEdge(style: .thin, color: BlockWriter.accent) }100 case .input: ModelStyle.applyInput(&s)101 case .output: ModelStyle.applyOutput(&s); s.borders.top = BorderEdge(style: .thin, color: BlockWriter.muted)102 case .label, .calculation: break103 }104 }105 }106 // defined names for the key assumptions and outputs (suffix when a previous model already uses them)107 let map = definedNameMap(a).filter { layout.names[$0.layout] != nil }108 var suffix = ""109 var k = 1110 while map.contains(where: { wb.definedName($0.name + suffix) != nil }) { k += 1; suffix = "_\(k)" }111 let quoted = FormulaPrinter.quoteSheet(wb.sheets[si].name)112 var created = [String]()113 for m in map {114 guard let ref = layout.names[m.layout] else { continue }115 let name = m.name + suffix116 engine.setDefinedName(DefinedName(name: name, formula: "\(quoted)!$\(A1.columnName(ref.col))$\(ref.row + 1)", comment: "ORIX DCF model"))117 created.append(name)118 }119 var bottom = (layout.range?.r1 ?? 0) + 2120 var w = BlockWriter(engine: engine, sheet: si, origin: CellRef(bottom, 0))121 w.note(L("Colour convention: blue cells on a light fill are inputs (assumptions); black cells are formulas; bold cells are outputs."))122 w.note(L("Defined names: %@.", created.joined(separator: ", ")))123 if let s = sensitivity {124 w.skip()125 w.heading(L("Sensitivity of the value per share"))126 w.note(L("Values (not formulas) computed with a two-variable data table at the time of insertion: discount rate %@, %@ %@.",127 Fmt.percent(s.baseRate, 2, locale: locale), s.isGrowth ? L("terminal growth") : L("exit multiple"), s.parameterLabel(s.baseParameter, locale: locale)))128 w.header([s.cornerLabel] + s.parameters.map { _ in "" })129 let headerRow = w.row - 1130 for (j, p) in s.parameters.enumerated() { w.number(headerRow, 1 + j, p, format: s.isGrowth ? "0.00%" : "0.0\"x\"") }131 for (i, r) in s.rates.enumerated() {132 w.number(w.row, 0, r, format: "0.00%")133 w.style(w.row, 0) { $0.hAlign = .left; $0.bold = true }134 for (j, v) in s.values[i].enumerated() {135 engine.set(sheet: si, row: w.row, col: 1 + j, .value(v))136 let base = abs(r - s.baseRate) < 1e-9 && abs(s.parameters[j] - s.baseParameter) < 1e-9137 w.style(w.row, 1 + j) { $0.numberFormat = "#,##0.00"; if base { ModelStyle.applyOutput(&$0) } }138 }139 w.row += 1140 }141 }142 bottom = w.row143 let lastCol = layout.range?.c1 ?? 1144 engine.editMeta(sheet: si) { m in145 m.colWidths[0] = 300146 for c in 1...max(lastCol, 6) { m.colWidths[c] = 104 }147 }148 var cells = [String: CellRef]()149 for (k, v) in layout.names { cells[k] = v }150 return Inserted(sheet: si, cells: cells, definedNames: created)151 }152153 /// Labels of the engine layout, translated.154 static func localized(_ s: String) -> String {155 labelMap[s] ?? s156 }157158 static let labelMap: [String: String] = [159 "Discounted cash flow valuation": L("Discounted cash flow valuation"), "Assumptions": L("Assumptions"), "Tax rate on EBIT": L("Tax rate on EBIT"),160 "Net working capital % of revenue": L("Net working capital % of revenue"), "Discount rate (WACC)": L("Discount rate (WACC)"),161 "Risk-free rate": L("Risk-free rate"), "Beta": L("Beta"), "Equity risk premium": L("Equity risk premium"), "Additional premium": L("Additional premium"),162 "Cost of equity (CAPM)": L("Cost of equity (CAPM)"), "Pre-tax cost of debt": L("Pre-tax cost of debt"),163 "Tax rate for the debt shield": L("Tax rate for the debt shield"), "Market value of equity": L("Market value of equity"),164 "Market value of debt": L("Market value of debt"), "WACC": L("WACC"),165 "Mid-year offset (0.5 = mid-year, 0 = year-end)": L("Mid-year offset (0.5 = mid-year, 0 = year-end)"),166 "Terminal growth rate": L("Terminal growth rate"), "Exit multiple (EV/EBITDA)": L("Exit multiple (EV/EBITDA)"), "Projections": L("Projections"),167 "Year": L("Year"), "Revenue growth": L("Revenue growth"), "Revenue": L("Revenue"), "EBIT margin": L("EBIT margin"), "EBIT": L("EBIT"),168 "Taxes on EBIT": L("Taxes on EBIT"), "NOPAT": L("NOPAT"), "D&A % of revenue": L("D&A % of revenue"),169 "Depreciation & amortisation": L("Depreciation & amortization"), "Capex % of revenue": L("Capex % of revenue"),170 "Capital expenditure": L("Capital expenditure"), "Change in net working capital": L("Change in net working capital"),171 "Free cash flow": L("Free cash flow"), "EBITDA": L("EBITDA"), "Discount period": L("Discount period"), "Discount factor": L("Discount factor"),172 "Present value of FCF": L("Present value of FCF"), "Valuation": L("Valuation"), "Sum of PV of FCF": L("Sum of PV of FCF"),173 "Terminal value (Gordon growth)": L("Terminal value (Gordon growth)"), "Terminal value (exit multiple)": L("Terminal value (exit multiple)"),174 "PV of terminal value": L("PV of terminal value"), "Enterprise value": L("Enterprise value"), "Less: debt": L("Less: debt"),175 "Plus: cash": L("Plus: cash"), "Less: minority interest": L("Less: minority interest"), "Less: preferred equity": L("Less: preferred equity"),176 "Plus: non-operating assets": L("Plus: non-operating assets"), "Equity value": L("Equity value"), "Diluted shares": L("Diluted shares"),177 "Value per share": L("Value per share"), "Terminal value share of EV": L("Terminal value share of EV"),178 ]179}180