Advanced Spreadsheets and Reporting Documentation
Index

Common Use Cases

This page walks through the spreadsheet scenarios most teams start with. Each walkthrough uses only features documented in this guide, with links to the details.

1. A Live Financial Report You Can Compute On

Goal: run the income statement and add your own ratios next to it.

  1. On the Financial Reports page, select your income statement and choose Open as Spreadsheet. A new sheet opens ready to use: the report's description as a title, a Period ?RANGE slicer and one ?DIMENSION slicer per report dimension (row 4), all already wired to the report region below — the slicer presets (This Month, Year to Date, Last Year…) and dimension picks recalculate the whole statement.
  2. Add your own formulas in the free columns to the right — a report cell is an ordinary cell to a formula. If Total Revenue lands in B12 and Total Cost in B19:
    • Gross margin: =B12 - B19
    • Margin %: =Round((B12 - B19) / B12 * 100, 0.1) (add a SafeDiv init-code procedure if revenue can be zero)
  3. Ctrl+click any amount to drill down to the entries behind it, exactly like the Financial Report overview page — the drill honours the current slicer picks.
  4. Save the sheet; from now on, opening it is running the report.

See Financial Report Regions.

2. Budget Entry in a Matrix

Goal: let a controller maintain a G/L budget in a familiar accounts-by-months grid.

  1. On G/L Budget Names, select the budget (say 2027) and choose Open as Spreadsheet. A dashboard-shaped sheet opens: the budget's description as a title, a Period ?RANGE slicer and one ?DIMENSION slicer per global and budget dimension (row 4), all wired to the matrix below. The chart of accounts runs down, the work-date year's months run across.
  2. Pick 2027-01-01..2027-12-31 in the Period slicer (or a preset), and narrow the accounts in the region's settings: Account Filter 40000..49999 (the revenue accounts).
  3. Type amounts into posting-account cells — each edit is validated and written back to the budget entries immediately. Totaling accounts show their sums read-only. Formulas stay live: =B12*1.1 in a month cell keeps writing "10% over that cell" back on every recalculation.
  4. Turn on Compare with G/L Actuals (Comparison Date Formula -1Y) to show 2026's actuals beside every 2027 budget cell — and give the actuals a light tint with Comparison Color so the editable cells stand out.
  5. Select a year of cells on an account row and use Sheet → Spread Total to distribute an annual amount — for example 120,000 weighted by a helper row holding last year's twelve monthly figures (=GL.GLSum(...) per month), so the budget follows last year's seasonality.
  6. Budgeting per department? Set Select Dimension to Add as Detailed Budget Lines to Global Dimension 1: every account unfolds into one editable line per department, and amounts you type are stamped with that department.
  7. To compare planning assumptions, keep them in ordinary cells and capture them with Scenarios (BASE, STRETCH). Scenarios do not capture or restore amounts typed directly into the budget matrix, including amounts entered through Spread Total. Budget formulas referencing the captured assumptions can write their recalculated results when a scenario is loaded.
  8. Share the sheet with the budget owners at the Edit Budget Entries access level: they can change budget amounts and use the slicers, and nothing else.

See Budget Matrix Regions and Documents & Sharing.

3. An Interactive Management Dashboard

Goal: one page with KPI cards, a chart and a top-10 list, filterable by period and area.

  1. Title in A1 (bold, larger font). Slicers on row 2 with bold labels beside them:
    • C2: ?RANGE->2026-01-01..2026-12-31 — the period
    • E2: ?DIMENSION(AREA)-> — the sales area, with an (All) option
  2. In a helper area far to the right (from column AA, with a "calculation cells — do not edit" banner), compute the key figures:
    • AB3 revenue: =GL.GLSum('40000..49999', C2, E2)
    • AB4 budget: =GL.GLBudget('40000..49999', C2)
    • AB6:AB17 a month-per-row series for the chart, e.g. =GL.GLSum('40000..49999', '2026-01-01..2026-01-31') and so on, with month labels in AA6:AA17
  3. Add a KPI Card region over B4:D7: Value Cell AB3, Compare Cell AB4, Comparison Label vs budget, Delta Mode Percent. Repeat for the next cards at E4:G7, H4:J7.
  4. Add a Chart region over B9:G21: type Column, Data Range AB6:AB17, Categories Range AA6:AA17, title Revenue by month [C2] — the title shows the picked period.
  5. Below, a top-10 list: a Pivot region at B24 over Cust. Ledger Entry or Value Entry, row field Customer No., aggregate Sum, Sort Rows by Total on, table view WHERE(Posting Date=FILTER([C2])) — or a SQL region with ORDER BY ... DESC when you want specific columns.
  6. Add one Page region over the visible dashboard (A1:M45) so preview and print show only the finished page — the helper columns stay backstage.
  7. Pick one base colour and reuse it on every region — the colour dialog remembers the sheet's scheme.

Faster route: Sheet → Insert Template and start from a standard dashboard template, or ask the Spreadsheet Agent to build it.

4. A Maintainable Data List with Write-Back

Goal: a working list over any table — for mass-updating a field, or reviewing records with your own annotations. Example: a credit-limit review.

  1. Create a Table region CUST at A4 over Customer, fields No., Name, Balance (LCY), Credit Limit (LCY), table view WHERE(Balance (LCY)=FILTER(>10000)).
  2. Turn on Allow Table Editing — the Credit Limit (LCY) cells accept input and validate it into the customer record, running the field's normal validation logic. FlowFields (like the balance), primary key fields and disabled fields stay read-only automatically.
  3. Add two Virtual columns in the field list:
    • Utilization % with the column formula =SafeDiv(C4, D4) * 100 (written against the anchor-row addresses shown in the field list's Cell column) — computed for every row.
    • Review note with no formula — free-text annotations, typed per row. Virtual values are sheet data (they never touch the table) and stay glued to their customer through sorting, filtering and paging.
  4. Above the region: =CUST.Count and =CUST.TableSum("Balance (LCY)") — the number of customers under review and their total exposure across the whole filtered set, not just the visible page.
  5. Ctrl+click a row to open the customer card when a note needs more digging.

See Table Regions.

5. Ad-hoc Analysis with a Pivot

Goal: answer "X by Y" questions — sales by salesperson by month, items by location.

Example — which salesperson sold what, per month, this year, biggest first:

  1. Create a Pivot Table region over Value Entry.
  2. Configure: Row Field Salespers./Purch. Code, Column Field Posting Date with Column Date Bucket Month, Value Field Sales Amount (Actual), Aggregate Sum, Sort Rows by Total on.
  3. Table view: WHERE(Item Ledger Entry Type=FILTER(Sale),Posting Date=FILTER([C2])) with C2 a ?RANGE slicer — changing the period re-pivots the whole cross-tab.
  4. Ctrl+click any amount to open exactly the value entries behind it; the Total column and grand-total row drill to their own wider scopes.

Variations: add Then Group By = Item Category Code for a second row level with bold subtotals; switch the column bucket to Quarter of Year to compare Q1s across years (sub-unit buckets group across the calendar); or aggregate Count of open service orders per technician — Count needs no value field.

See Pivot Regions.

6. What-If Modelling

Goal: a calculation model where assumptions drive live data. Example: next year's revenue plan built on this year's actuals.

  1. Lay out labelled assumption cells: B2 = ?RANGE->2026-01-01..2026-12-31 (the baseline period), B5 = 0.05 (growth).
  2. Pull the live baseline and project it: =GL.GLSum('40000..49999', B2) for this year's revenue, and beside it =GL.GLSum('40000..49999', B2) * (1 + B5) for the plan. Change B5 to 0.08 and the plan updates instantly — change the slicer and the baseline updates.
  3. Numbers read from cells are decimals, so =B5/100 divides properly. The integer trap bites literals typed inline: =5/100 is 0, because two integer operands give an integer result (0.05 rounds to 0) — write =5.0/100 (see Formulas).
  4. Define reusable logic in Init Code — a SafeDiv so ratios never divide by zero, or a MonthRevenue(DateFlt, ItemFlt) procedure for per-month lines — and call it from any cell.
  5. Capture assumption sets as Scenarios (BASE, STRETCH, WORST) and flip between them in the review meeting.