Workbook templates
ORIX Sheet ships fifteen workbook templates. Each one is built in code, contains working formulas on sample figures, and exists in English and in Canadian French. This document lists them, explains how they are built and records how their calculations were checked.
Related documents: architecture/overview.md (package map), development/testing.md, mathematics/reference-tests.md, mathematics/financial-conventions.md.
Where they appear
- The template chooser opens at launch when the "show welcome" setting is on, and from
File ▸ New from Template… (⇧⌘N). It lists the templates by category with a thumbnail, and also
offers a blank workbook and Open a File….
Features.registerAll()installs it by assigningWelcome.provider(App/Sources/Features.swift,App/Sources/TemplateChooser.swift). - The launch argument
-ORIXTemplate <id>opens a template directly (development/setup.md). - A template opens as an untitled document: the workbook is built at that moment by
WorkbookTemplate.build(locale:), fully calculated, and nothing is read from a file.
How a template is built
Packages/SheetTemplates contains a small DSL (DSL/Book.swift, Pad.swift, Styles.swift) and
one Swift file per template under Sources/SheetTemplates/Templates/. A template declares an
identifier, a category, a title and a summary in both languages, and a closure that fills a Book.
Labels and sheet names follow the locale (French for a French locale, English otherwise); the
numbers are the same in both languages.
What every template has, as asserted by the Structure test suite for each of the 30 builds
(15 templates × 2 languages):
- no build problem and no error value in any cell, and nothing left to calculate;
- formulas that use registered functions only;
- on every sheet a title block with a "How to use" note saying that the figures are examples (or that the sheet is calculated), a tab colour, frozen header rows, column widths, and print settings (fit to one page wide, sheet name in the header, page number in the footer);
- at least two cell notes explaining inputs, at least one data-validation rule on input cells, and named ranges for the key inputs;
- charts whose series point at existing sheets and read at least two numeric values.
TemplateThumbnail draws the picture shown in the chooser from the built workbook itself (the
top-left region of the first sheet: fills, borders, formatted values, charts). Conditional formats
are not evaluated in thumbnails.
Source: Packages/SheetTemplates/Sources/SheetTemplates/TemplateLibrary.swift,
TemplateThumbnail.swift, Tests/SheetTemplatesTests/StructureTests.swift.
The fifteen templates
Sheet names are given as English / French. The "worksheet functions" column lists the functions that appear in the formulas of the template's source file.
| Identifier | Title | Category | Sheets | What it calculates | Worksheet functions |
|---|---|---|---|---|---|
personal-budget |
Personal Budget | Personal | Summary / Sommaire, Monthly / Mensuel | monthly income and expenses by category, plan versus actual, savings rate, overspend alerts | SUM, IF |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Source: the fifteen files of Packages/SheetTemplates/Sources/SheetTemplates/Templates/ (identifier,
category, title, summary, b.sheet(…) calls and the function names in their formula strings),
TemplateLibrary.swift (order and categories).
The six categories are Personal, Business, Finance, Planning, Research and Education; the test
fifteenTemplatesWithUniqueIdentifiers requires fifteen distinct identifiers and at least one
template per category.
How the calculations were checked
Three independent checks exist; swift test in Packages/SheetTemplates runs them
(29 test functions in docs/reports/tests.json, all passing, most of them parameterised over the
30 builds).
1. Key outputs against Python
Tests/SheetTemplatesTests/Generators/gen_expected.py reads the inputs of every template
(inputs.json, written by the test exportGeneratorInputs) and recomputes the key outputs with
numpy, scipy and closed forms — "never with a spreadsheet engine", in the words of its header. The
result is Fixtures/expected.json, which holds expectations for all fifteen templates. The Numeric
suite compares the calculated workbooks with it, in both languages, with a default tolerance of 1e-9
(relative and absolute).
2. Whole-workbook cross-check with LibreOffice
LibreOfficeCrossCheck.libreOfficeRecalculatesEveryTemplateToTheSameValues:
- exports each of the 30 workbooks to XLSX with the cached value of every formula cell and spilled cell replaced by a sentinel (−987,654.321), so that a value read back can only come from a recalculation;
- has headless LibreOffice (
/opt/homebrew/bin/soffice, private profile, recalculation on load forced) open the files, recalculate and save them again; - reads the LibreOffice files back and compares every formula cell and every spilled cell with ORIX Sheet's value: numbers within 1e-6 relative, text and logical values exactly.
Result of the last recorded run (build/test-logs/SheetTemplates.log): 18,714 cells compared in
30 workbooks, 0 mismatches.
One waiver is written in the test: in the French sales-tracking workbook, the 22 cells of
Sommaire!I10:K17 (a SORT over product names containing accented letters) are excluded from the
comparison. The recorded reason is that ORIX Sheet ordered text by code point while LibreOffice uses
the collation of the language. Text ordering has since been changed to a fixed collation in which
accents sort next to their base letter
(mathematics/numerical-precision.md); the waiver is still in
the test, so those 22 cells remain unchecked against LibreOffice.
The test returns early, without failing, when LibreOffice is not installed or when
ORIX_TEMPLATES_SKIP_LO is set; a green run on such a machine has not performed this check.
3. File round trips
For each of the 30 builds (InputOutput suite):
- saving to
.orixsheetand reloading gives no issue and no difference (WorkbookDiff, exact), and the reloaded workbook recalculates to the same values within 1e-12; - exporting to XLSX announces losses for embedded objects only (the charts), and values, formulas, names, merges, validations, conditional formats, notes, tables, frozen panes and print orientation come back when the file is read again.
Source: Packages/SheetTemplates/Tests/SheetTemplatesTests/NumericTests.swift,
LibreOfficeTests.swift, IOTests.swift, GeneratorInputs.swift, TestSupport.swift,
Generators/gen_expected.py.
Limits
- LibreOffice is a second implementation, not Excel: no template was opened in Microsoft Excel.
- The cross-check compares values, not appearance. Formatting, charts and conditional formats of the exported files were not compared with LibreOffice's rendering.
- The sample figures are synthetic. A template states on each sheet that they are examples to replace.
- Templates exist in English and Canadian French only.
- The French strings of the template chooser itself were not all translated when this document was written (development/localization.md).