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