SPB Git

Repository Public

DCFInsertion.swift 11.3 KB 180 lines swift

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
Files main
Menu

Theme