SPB Git

Repository Public

DemoWorkbook.swift 13.5 KB 225 lines swift

1import Foundation2import SheetCore3import SheetUI4import Statistics56/// The demo workbook of the previews. Every number is SYNTHETIC (seeded pseudo-random generators); the sheets7/// say so in their first note. Sheets and ranges (stable — tools' `demo` closures refer to them):8///9/// * `Regression`  A1:G121 — Sales, Advertising, Price, Stores, Region (text), Weight, Promo (0/1); two blank cells in B.10/// * `Groups`      A1:C31 — Method A / B / C (independent samples); E1:F31 — Before / After (paired);11///                 H1:K4 — contingency table (Segment × Product A/B/C); M1:N91 — long format (Group text, Score).12/// * `Series`      A1:B85 — Month (date serial), Demand (monthly, trend + seasonality, 84 observations).13/// * `Returns`     A1:B501 — Day, Return (simulated GARCH(1,1) daily returns, 500 observations).14/// * `Prices`      A1:F253 — Date, Asset A, Asset B, Asset C, Asset D, Benchmark (252 daily prices).15/// * `Panel`       A1:E61 — Firm (text), Year, Investment, Value, Capital (10 firms × 6 years).16/// * `Model`       product-launch model with formulas: inputs B4:B9 (price, units, variable cost, fixed costs,17///                 tax rate, growth), results B12:B18 (revenue … net profit B17, year-2 profit B18),18///                 loan B21:B23 with payment B24 (=PMT).19/// * `CashFlows`   A1:D8 — Year, Project A, Project B, Project C; F1:G7 — Date, Amount (dated flows);20///                 I1:I4 — Non-conventional flows (two IRRs).21/// * `Statements`  A1:C19 — Line item, FY2025, FY2024 (the 18 lines used by the ratio analysis).22@MainActor public enum DemoWorkbook {23    public static let sheetNames = ["Regression", "Groups", "Series", "Returns", "Prices", "Panel", "Model", "CashFlows", "Statements"]2425    public static func make(locale: Locale = .current) -> WorkbookSession {26        let wb = Workbook(sheetNames: sheetNames)27        let session = WorkbookSession(workbook: wb, locale: locale)28        session.perform("Demo data") { e in29            regression(e, 0); groups(e, 1); series(e, 2); returns(e, 3); prices(e, 4); panel(e, 5); model(e, 6); cashFlows(e, 7); statements(e, 8)30            for si in 0..<sheetNames.count {31                e.setStyle(sheet: si, range: RangeRef(r0: 0, c0: 0, r1: 0, c1: 13)) { $0.bold = true }32                e.editMeta(sheet: si) { m in33                    m.comments[CellRef(0, 0)] = CellComment(author: "ORIX", text: "Synthetic sample data generated for the preview. Not real observations.")34                }35            }36            e.setStyle(sheet: 6, range: RangeRef(r0: 0, c0: 0, r1: 0, c1: 13)) { $0.bold = true; $0.fontSize = 14 }37        }38        session.engine.clearUndoHistory()39        session.activateSheet(0)40        session.select(RangeRef(a1: "A1:G121")!, active: CellRef(0, 0))41        return session42    }4344    static func header(_ e: CalcEngine, _ si: Int, _ col: Int, _ names: [String]) {45        for (i, n) in names.enumerated() { e.set(sheet: si, row: 0, col: col + i, .value(.text(n))) }46    }47    static func num(_ e: CalcEngine, _ si: Int, _ r: Int, _ c: Int, _ v: Double, round digits: Int = 2) {48        let k = pow(10, Double(digits))49        e.set(sheet: si, row: r, col: c, .value(.number((v * k).rounded() / k)))50    }51    static func text(_ e: CalcEngine, _ si: Int, _ r: Int, _ c: Int, _ s: String) { e.set(sheet: si, row: r, col: c, .value(.text(s))) }5253    static func regression(_ e: CalcEngine, _ si: Int) {54        var rng = Xoshiro256StarStar(seed: 20261121)55        header(e, si, 0, ["Sales", "Advertising", "Price", "Stores", "Region", "Weight", "Promo"])56        let regions = ["North", "South", "East", "West"], effect = [0.0, 6.0, -4.0, 9.0]57        for i in 0..<120 {58            let r = i + 159            let adv = 20 + 60 * rng.nextUniform()60            let price = 8 + 6 * rng.nextUniform()61            let stores = (5 + 25 * rng.nextUniform()).rounded()62            let region = rng.nextInt(below: 4)63            let weight = 0.5 + 1.5 * rng.nextUniform()64            let sales = 40 + 1.8 * adv - 5.5 * price + 2.1 * stores + effect[region] + 9 * rng.nextNormal() / weight.squareRoot()65            let promo = rng.nextUniform() < 1 / (1 + exp(-(adv - 50) / 9)) ? 1.0 : 0.066            num(e, si, r, 0, sales)67            if i != 17 && i != 64 { num(e, si, r, 1, adv) }          // two missing values68            num(e, si, r, 2, price); num(e, si, r, 3, stores, round: 0)69            text(e, si, r, 4, regions[region])70            num(e, si, r, 5, weight); num(e, si, r, 6, promo, round: 0)71        }72    }7374    static func groups(_ e: CalcEngine, _ si: Int) {75        var rng = Xoshiro256StarStar(seed: 7)76        header(e, si, 0, ["Method A", "Method B", "Method C"])77        header(e, si, 4, ["Before", "After"])78        header(e, si, 12, ["Group", "Score"])79        let means = [71.0, 75.5, 80.0], names = ["A", "B", "C"]80        for i in 0..<30 {81            for g in 0..<3 {82                let v = means[g] + 7 * rng.nextNormal()83                num(e, si, i + 1, g, v, round: 1)84                text(e, si, 1 + g * 30 + i, 12, names[g]); num(e, si, 1 + g * 30 + i, 13, v, round: 1)85            }86            let before = 120 + 12 * rng.nextNormal()87            num(e, si, i + 1, 4, before, round: 1); num(e, si, i + 1, 5, before - 4 + 5 * rng.nextNormal(), round: 1)88        }89        header(e, si, 7, ["Segment", "Product A", "Product B", "Product C"])90        let table: [(String, [Double])] = [("Students", [34, 22, 14]), ("Professionals", [28, 41, 26]), ("Retirees", [18, 25, 37])]91        for (r, row) in table.enumerated() {92            text(e, si, r + 1, 7, row.0)93            for (c, v) in row.1.enumerated() { num(e, si, r + 1, 8 + c, v, round: 0) }94        }95    }9697    static func series(_ e: CalcEngine, _ si: Int) {98        var rng = Xoshiro256StarStar(seed: 11)99        header(e, si, 0, ["Month", "Demand"])100        let seasonal = [-12.0, -9, -2, 3, 8, 14, 18, 15, 6, -3, -14, -24]101        for i in 0..<84 {102            let serial = DateSerial.serial(year: 2019 + i / 12, month: 1 + i % 12, day: 1)103            e.set(sheet: si, row: i + 1, col: 0, .value(.number(serial)))104            num(e, si, i + 1, 1, 180 + 1.6 * Double(i) + seasonal[i % 12] * (1 + Double(i) / 160) + 5 * rng.nextNormal(), round: 1)105        }106        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 0, r1: 84, c1: 0)) { $0.numberFormat = "yyyy-mm" }107    }108109    static func returns(_ e: CalcEngine, _ si: Int) {110        var rng = Xoshiro256StarStar(seed: 23)111        header(e, si, 0, ["Day", "Return"])112        let omega = 0.000004, alpha = 0.09, beta = 0.88, mu = 0.0004113        var variance = omega / (1 - alpha - beta), shock = 0.0114        for i in 0..<500 {115            variance = omega + alpha * shock * shock + beta * variance116            shock = variance.squareRoot() * rng.nextNormal()117            num(e, si, i + 1, 0, Double(i + 1), round: 0)118            num(e, si, i + 1, 1, mu + shock, round: 6)119        }120        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 1, r1: 500, c1: 1)) { $0.numberFormat = "0.000%" }121    }122123    static func prices(_ e: CalcEngine, _ si: Int) {124        var rng = Xoshiro256StarStar(seed: 5)125        header(e, si, 0, ["Date", "Asset A", "Asset B", "Asset C", "Asset D", "Benchmark"])126        var p = [100.0, 54.0, 212.0, 31.0], bench = 1000.0127        let betas = [1.2, 0.7, 1.0, 0.3], idio = [0.011, 0.008, 0.013, 0.006], drift = [0.0005, 0.0003, 0.0006, 0.0002]128        var serial = DateSerial.serial(year: 2025, month: 1, day: 2)129        for i in 0..<252 {130            if i > 0 {131                let m = 0.0004 + 0.009 * rng.nextNormal()132                bench *= exp(m)133                for k in 0..<4 { p[k] *= exp(drift[k] + betas[k] * m + idio[k] * rng.nextNormal()) }134                serial += DateSerial.weekday(serial) == 6 ? 3 : 1      // skip weekends135            }136            e.set(sheet: si, row: i + 1, col: 0, .value(.number(serial)))137            for k in 0..<4 { num(e, si, i + 1, 1 + k, p[k]) }138            num(e, si, i + 1, 5, bench)139        }140        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 0, r1: 252, c1: 0)) { $0.numberFormat = "yyyy-mm-dd" }141        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 1, r1: 252, c1: 5)) { $0.numberFormat = "#,##0.00" }142    }143144    static func panel(_ e: CalcEngine, _ si: Int) {145        var rng = Xoshiro256StarStar(seed: 31)146        header(e, si, 0, ["Firm", "Year", "Investment", "Value", "Capital"])147        var r = 1148        for f in 0..<10 {149            let effect = 20 * rng.nextNormal()150            var value = 400 + 300 * rng.nextUniform(), capital = 80 + 120 * rng.nextUniform()151            for y in 0..<6 {152                value *= 1 + 0.06 * rng.nextNormal(); capital *= 1.03 + 0.05 * rng.nextNormal()153                text(e, si, r, 0, "Firm \(Character(UnicodeScalar(65 + f)!))")154                num(e, si, r, 1, Double(2020 + y), round: 0)155                num(e, si, r, 2, 10 + effect + 0.11 * value + 0.31 * capital + 6 * rng.nextNormal())156                num(e, si, r, 3, value); num(e, si, r, 4, capital)157                r += 1158            }159        }160    }161162    static func model(_ e: CalcEngine, _ si: Int) {163        let rows: [(Int, String, String)] = [164            (0, "Product launch model (synthetic)", ""), (2, "Assumptions", ""),165            (3, "Unit price", "48"), (4, "Units sold", "12000"), (5, "Variable cost per unit", "29"), (6, "Fixed costs", "150000"),166            (7, "Tax rate", "0.265"), (8, "Growth rate", "0.05"), (10, "Results", ""),167            (11, "Revenue", "=B4*B5"), (12, "Variable costs", "=B6*B5"), (13, "Contribution margin", "=B12-B13"),168            (14, "Operating profit", "=B14-B7"), (15, "Income tax", "=MAX(B15,0)*B8"), (16, "Net profit", "=B15-B16"),169            (17, "Year-2 net profit", "=B17*(1+B9)"), (19, "Loan", ""),170            (20, "Loan amount", "250000"), (21, "Annual rate", "0.065"), (22, "Years", "10"), (23, "Monthly payment", "=PMT(B22/12,B23*12,-B21)"),171        ]172        for (r, label, content) in rows {173            text(e, si, r, 0, label)174            if content.hasPrefix("=") { _ = e.setFormula(sheet: si, row: r, col: 1, content) }175            else if let v = Double(content) { e.set(sheet: si, row: r, col: 1, .value(.number(v))) }176        }177        for r in [2, 10, 19] { e.setStyle(sheet: si, range: RangeRef(CellRef(r, 0))) { $0.bold = true } }178        e.setStyle(sheet: si, range: RangeRef(a1: "B4:B7")!) { $0.numberFormat = "#,##0.00" }179        e.setStyle(sheet: si, range: RangeRef(a1: "B8:B9")!) { $0.numberFormat = "0.0%" }180        e.setStyle(sheet: si, range: RangeRef(a1: "B12:B18")!) { $0.numberFormat = "#,##0.00" }181        e.setStyle(sheet: si, range: RangeRef(a1: "B21")!) { $0.numberFormat = "#,##0.00" }182        e.setStyle(sheet: si, range: RangeRef(a1: "B22")!) { $0.numberFormat = "0.00%" }183        e.setStyle(sheet: si, range: RangeRef(a1: "B24")!) { $0.numberFormat = "#,##0.00" }184        e.editMeta(sheet: si) { $0.colWidths[0] = 190; $0.colWidths[1] = 110 }185    }186187    static func cashFlows(_ e: CalcEngine, _ si: Int) {188        header(e, si, 0, ["Year", "Project A", "Project B", "Project C"])189        let flows: [[Double]] = [[-100000, 28000, 32000, 36000, 30000, 24000, 18000], [-100000, 8000, 16000, 30000, 44000, 52000, 46000],190                                 [-60000, 22000, 22000, 22000, 14000, 8000, 4000]]191        for y in 0..<7 {192            num(e, si, y + 1, 0, Double(y), round: 0)193            for p in 0..<3 { num(e, si, y + 1, 1 + p, flows[p][y]) }194        }195        header(e, si, 5, ["Date", "Amount"])196        let dated: [(Int, Int, Int, Double)] = [(2025, 1, 15, -50000), (2025, 4, 1, 9500), (2025, 9, 30, 12250), (2026, 2, 12, 14000), (2026, 8, 20, 13500), (2027, 1, 15, 11800)]197        for (i, d) in dated.enumerated() {198            e.set(sheet: si, row: i + 1, col: 5, .value(.number(DateSerial.serial(year: d.0, month: d.1, day: d.2))))199            num(e, si, i + 1, 6, d.3)200        }201        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 5, r1: 6, c1: 5)) { $0.numberFormat = "yyyy-mm-dd" }202        header(e, si, 8, ["Non-conventional"])203        for (i, v) in [-1600.0, 10000, -10000].enumerated() { num(e, si, i + 1, 8, v) }204        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 1, r1: 7, c1: 8)) { s in if s.numberFormat == "General" { s.numberFormat = "#,##0" } }205        e.editMeta(sheet: si) { $0.colWidths[8] = 120 }206    }207208    static func statements(_ e: CalcEngine, _ si: Int) {209        header(e, si, 0, ["Line item", "FY2025", "FY2024"])210        let lines: [(String, Double, Double)] = [211            ("Revenue", 5_240_000, 4_810_000), ("Cost of goods sold", 3_196_000, 2_982_000), ("Operating income (EBIT)", 786_000, 673_000),212            ("Interest expense", 92_000, 98_000), ("Pre-tax income", 694_000, 575_000), ("Net income", 510_000, 423_000),213            ("Depreciation & amortization", 214_000, 201_000), ("Cash", 412_000, 355_000), ("Marketable securities", 120_000, 90_000),214            ("Accounts receivable", 655_000, 610_000), ("Inventory", 708_000, 690_000), ("Total current assets", 1_960_000, 1_805_000),215            ("Total assets", 4_870_000, 4_590_000), ("Accounts payable", 468_000, 441_000), ("Total current liabilities", 1_045_000, 1_010_000),216            ("Total debt", 1_520_000, 1_610_000), ("Total liabilities", 2_790_000, 2_840_000), ("Shareholders' equity", 2_080_000, 1_750_000),217        ]218        for (i, l) in lines.enumerated() {219            text(e, si, i + 1, 0, l.0); num(e, si, i + 1, 1, l.1, round: 0); num(e, si, i + 1, 2, l.2, round: 0)220        }221        e.setStyle(sheet: si, range: RangeRef(r0: 1, c0: 1, r1: 18, c1: 2)) { $0.numberFormat = "#,##0" }222        e.editMeta(sheet: si) { $0.colWidths[0] = 210; $0.colWidths[1] = 110; $0.colWidths[2] = 110 }223    }224}225
Files main
Menu

Theme