Repository Public
1# Workbook templates23ORIX Sheet ships fifteen workbook templates. Each one is built in code, contains working formulas4on sample figures, and exists in English and in Canadian French. This document lists them, explains5how they are built and records how their calculations were checked.67Related documents: [architecture/overview.md](architecture/overview.md) (package map),8[development/testing.md](development/testing.md), [mathematics/reference-tests.md](mathematics/reference-tests.md),9[mathematics/financial-conventions.md](mathematics/financial-conventions.md).1011## Where they appear1213- The **template chooser** opens at launch when the "show welcome" setting is on, and from14 *File ▸ New from Template…* (⇧⌘N). It lists the templates by category with a thumbnail, and also15 offers a blank workbook and *Open a File…*. `Features.registerAll()` installs it by assigning16 `Welcome.provider` (`App/Sources/Features.swift`, `App/Sources/TemplateChooser.swift`).17- The launch argument `-ORIXTemplate <id>` opens a template directly18 ([development/setup.md](development/setup.md)).19- A template opens as an untitled document: the workbook is built at that moment by20 `WorkbookTemplate.build(locale:)`, fully calculated, and nothing is read from a file.2122## How a template is built2324`Packages/SheetTemplates` contains a small DSL (`DSL/Book.swift`, `Pad.swift`, `Styles.swift`) and25one Swift file per template under `Sources/SheetTemplates/Templates/`. A template declares an26identifier, a category, a title and a summary in both languages, and a closure that fills a `Book`.27Labels and sheet names follow the locale (French for a French locale, English otherwise); the28numbers are the same in both languages.2930What every template has, as asserted by the `Structure` test suite for each of the 30 builds31(15 templates × 2 languages):3233- no build problem and no error value in any cell, and nothing left to calculate;34- formulas that use registered functions only;35- on every sheet a title block with a "How to use" note saying that the figures are examples (or that36 the sheet is calculated), a tab colour, frozen header rows, column widths, and print settings (fit37 to one page wide, sheet name in the header, page number in the footer);38- at least two cell notes explaining inputs, at least one data-validation rule on input cells, and39 named ranges for the key inputs;40- charts whose series point at existing sheets and read at least two numeric values.4142`TemplateThumbnail` draws the picture shown in the chooser from the built workbook itself (the43top-left region of the first sheet: fills, borders, formatted values, charts). Conditional formats44are not evaluated in thumbnails.4546Source: `Packages/SheetTemplates/Sources/SheetTemplates/TemplateLibrary.swift`,47`TemplateThumbnail.swift`, `Tests/SheetTemplatesTests/StructureTests.swift`.4849## The fifteen templates5051Sheet names are given as English / French. The "worksheet functions" column lists the functions52that appear in the formulas of the template's source file.5354| Identifier | Title | Category | Sheets | What it calculates | Worksheet functions |55|---|---|---|---|---|---|56| `personal-budget` | Personal Budget | Personal | Summary / Sommaire, Monthly / Mensuel | monthly income and expenses by category, plan versus actual, savings rate, overspend alerts | `SUM`, `IF` |57| `business-budget` | Business Budget | Business | Summary / Sommaire, Budget, Actual / Réel, Variance / Écarts | departments by month: budget, actual and variance, year-to-date position, quarter subtotals | `SUM`, `SUMIFS`, `INDEX`, `MATCH`, `MAX`, `IF` |58| `financial-statements` | Financial Statements | Finance | Inputs / Hypothèses, Income Statement / État des résultats, Balance Sheet / Bilan, Cash Flow / Flux de trésorerie | linked income statement, balance sheet and cash-flow statement, with check cells proving that they balance | `SUM`, `SUMPRODUCT`, `ROUND`, `ABS`, `MAX`, `IF` |59| `cash-flow-forecast` | Cash Flow Forecast | Finance | Forecast / Prévision | thirteen-week forecast: receipts, disbursements, rolling balance, minimum-cash alert | `SUM`, `MIN`, `COUNTIF`, `INDEX`, `MATCH`, `IF` |60| `mortgage` | Mortgage Calculator | Personal | Mortgage / Hypothèque, Schedule / Amortissement | payment, full amortization schedule, extra payments, the Canadian semi-annual compounding convention | `PMT`, `IPMT`, `PPMT`, `EDATE`, `ROUND`, `SUM`, `MIN`, `MAX`, `COUNT`, `IF`, `AND`, `ISNUMBER` |61| `investment-analysis` | Investment Analysis | Finance | Analysis / Analyse | cash-flow table with net present value, internal rate of return, modified IRR, payback, profitability index, dated variants and rate sensitivity | `NPV`, `IRR`, `MIRR`, `XNPV`, `XIRR`, `SUM`, `MIN`, `COUNT`, `IF`, `AND` |62| `dcf-valuation` | DCF Valuation | Finance | Assumptions / Hypothèses, Projections, Valuation / Évaluation | assumptions with named ranges, five-year projections, WACC from CAPM, two terminal values, equity bridge, two-way sensitivity grid | `SUM`, `SUMPRODUCT`, `AVERAGE`, `INDEX`, `MAX` |63| `project-plan` | Project Plan | Planning | Plan, Holidays / Jours fériés | tasks scheduled in working days, progress and status, a Gantt grid, the list of late tasks | `WORKDAY`, `WEEKDAY`, `TODAY`, `MONTH`, `FILTER`, `SUMPRODUCT`, `COUNTIF`, `COUNTA`, `INDEX`, `MATCH`, `IFERROR`, `IF`, `AND`, `SUM`, `MAX` |64| `inventory` | Inventory | Business | Summary / Sommaire, Items / Articles, Movements / Mouvements, Lists / Listes | item table with stock on hand, reorder flags and value, a movements log, lookups, low-stock highlighting | `XLOOKUP`, `SUMIFS`, `COUNTIFS`, `COUNTIF`, `MAXIFS`, `FILTER`, `COUNTA`, `IFERROR`, `IF`, `SUM` |65| `sales-tracking` | Sales Tracking | Business | Summary / Sommaire, Transactions | a structured table of transactions summarised by month, representative and product, with dynamic arrays and a top-N list | `SUMIFS`, `COUNTIFS`, `UNIQUE`, `SORT`, `FILTER`, `TAKE`, `CHOOSECOLS`, `INDEX`, `MATCH`, `TEXT`, `YEAR`, `MONTH`, `ROWS`, `AVERAGE`, `MAX`, `SUM`, `IF` |66| `research-dataset` | Research Dataset | Research | Summary / Sommaire, Data / Données, Codebook / Dictionnaire | a tidy observation table with validation, a codebook, group summaries, missing-value counts | `COUNTIFS`, `AVERAGEIFS`, `COUNTIF`, `COUNTBLANK`, `COUNTA`, `SUMPRODUCT`, `ISNUMBER`, `LEN`, `ROWS`, `AVERAGE`, `SUM`, `IF`, `AND` |67| `statistical-analysis` | Statistical Analysis | Research | Analysis / Analyse, Data / Données | descriptive statistics, confidence intervals, two-sample t-tests, correlation, a frequency histogram | `AVERAGE`, `MEDIAN`, `STDEV.S`, `VAR.S`, `SKEW`, `KURT`, `QUARTILE.INC`, `CONFIDENCE.T`, `T.TEST`, `T.DIST.2T`, `T.INV.2T`, `CORREL`, `SLOPE`, `INTERCEPT`, `RSQ`, `FREQUENCY`, `SQRT`, `ABS`, `COUNT`, `MIN`, `MAX`, `SUM`, `IF` |68| `regression-report` | Regression Report | Research | Report / Rapport, Data / Données | simple and multiple regression: coefficients, standard errors, t and p values, R², F, fitted values, residuals | `LINEST`, `SLOPE`, `INTERCEPT`, `RSQ`, `STEYX`, `FORECAST.LINEAR`, `T.DIST.2T`, `T.INV.2T`, `F.DIST.RT`, `DEVSQ`, `SUMSQ`, `SUMXMY2`, `INDEX`, `AVERAGE`, `COUNT`, `ABS` |69| `dashboard` | Dashboard | Business | Dashboard / Tableau de bord, Data / Données | KPI cells and charts driven by a period selector | `XLOOKUP`, `INDEX`, `MATCH`, `SUM`, `LEFT`, `ISNUMBER`, `IF`, `AND`, `OR` |70| `gradebook` | Academic Gradebook | Education | Grades / Notes, Class / Classe, Settings / Paramètres | students by assessment with weights, weighted score, letter grade by lookup, rank, class statistics, late or exempt handling | `SUMPRODUCT`, `SUMIF`, `XLOOKUP`, `RANK.EQ`, `AVERAGE`, `MEDIAN`, `STDEV.S`, `COUNTIF`, `COUNTBLANK`, `COUNT`, `INDEX`, `MATCH`, `ROUND`, `ISBLANK`, `ISNUMBER`, `MIN`, `MAX`, `SUM`, `IF`, `AND`, `OR` |7172Source: the fifteen files of `Packages/SheetTemplates/Sources/SheetTemplates/Templates/` (identifier,73category, title, summary, `b.sheet(…)` calls and the function names in their formula strings),74`TemplateLibrary.swift` (order and categories).7576The six categories are Personal, Business, Finance, Planning, Research and Education; the test77`fifteenTemplatesWithUniqueIdentifiers` requires fifteen distinct identifiers and at least one78template per category.7980## How the calculations were checked8182Three independent checks exist; `swift test` in `Packages/SheetTemplates` runs them83(29 test functions in `docs/reports/tests.json`, all passing, most of them parameterised over the8430 builds).8586### 1. Key outputs against Python8788`Tests/SheetTemplatesTests/Generators/gen_expected.py` reads the inputs of every template89(`inputs.json`, written by the test `exportGeneratorInputs`) and recomputes the key outputs with90numpy, scipy and closed forms — "never with a spreadsheet engine", in the words of its header. The91result is `Fixtures/expected.json`, which holds expectations for all fifteen templates. The `Numeric`92suite compares the calculated workbooks with it, in both languages, with a default tolerance of 1e-993(relative and absolute).9495### 2. Whole-workbook cross-check with LibreOffice9697`LibreOfficeCrossCheck.libreOfficeRecalculatesEveryTemplateToTheSameValues`:98991. exports each of the 30 workbooks to XLSX with the cached value of every formula cell and spilled100 cell replaced by a sentinel (−987,654.321), so that a value read back can only come from a101 recalculation;1022. has headless LibreOffice (`/opt/homebrew/bin/soffice`, private profile, recalculation on load103 forced) open the files, recalculate and save them again;1043. reads the LibreOffice files back and compares **every formula cell and every spilled cell** with105 ORIX Sheet's value: numbers within 1e-6 relative, text and logical values exactly.106107Result of the last recorded run (`build/test-logs/SheetTemplates.log`): **18,714 cells compared in10830 workbooks, 0 mismatches**.109110One waiver is written in the test: in the French sales-tracking workbook, the 22 cells of111`Sommaire!I10:K17` (a `SORT` over product names containing accented letters) are excluded from the112comparison. The recorded reason is that ORIX Sheet ordered text by code point while LibreOffice uses113the collation of the language. Text ordering has since been changed to a fixed collation in which114accents sort next to their base letter115([mathematics/numerical-precision.md](mathematics/numerical-precision.md)); the waiver is still in116the test, so those 22 cells remain unchecked against LibreOffice.117118The test returns early, without failing, when LibreOffice is not installed or when119`ORIX_TEMPLATES_SKIP_LO` is set; a green run on such a machine has not performed this check.120121### 3. File round trips122123For each of the 30 builds (`InputOutput` suite):124125- saving to `.orixsheet` and reloading gives no issue and no difference (`WorkbookDiff`, exact), and126 the reloaded workbook recalculates to the same values within 1e-12;127- exporting to XLSX announces losses for embedded objects only (the charts), and values, formulas,128 names, merges, validations, conditional formats, notes, tables, frozen panes and print orientation129 come back when the file is read again.130131Source: `Packages/SheetTemplates/Tests/SheetTemplatesTests/NumericTests.swift`,132`LibreOfficeTests.swift`, `IOTests.swift`, `GeneratorInputs.swift`, `TestSupport.swift`,133`Generators/gen_expected.py`.134135## Limits136137- LibreOffice is a second implementation, not Excel: no template was opened in Microsoft Excel.138- The cross-check compares values, not appearance. Formatting, charts and conditional formats of the139 exported files were not compared with LibreOffice's rendering.140- The sample figures are synthetic. A template states on each sheet that they are examples to replace.141- Templates exist in English and Canadian French only.142- The French strings of the template chooser itself were not all translated when this document was143 written ([development/localization.md](development/localization.md)).144