SPB Git

Repository Public

templates.md 11.7 KB

# 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 assigning Welcome.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:

  1. 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;
  2. has headless LibreOffice (/opt/homebrew/bin/soffice, private profile, recalculation on load forced) open the files, recalculate and save them again;
  3. 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 .orixsheet and 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).
Files main
Menu

Theme