The Model Writes the Formulas: Building Spreadsheets with Local AI

Ask a chat model for a twelve-month financial forecast and you'll get a beautiful spreadsheet-shaped answer with twelve columns of numbers — every one of them invented. Not estimated from your data: invented, with total confidence. March grows 4.6% because the model needed March to grow 4.6%. This is the moment most people conclude "AI can't do spreadsheets," and the conclusion is wrong. The model can't be the calculator. It can be an excellent architect.

The fix is a division of labor that local AI makes natural: the model writes the structure and the formulas; the spreadsheet does the arithmetic. A formula is a sentence a machine evaluates — models are great at writing sentences and terrible at being calculators. Keep the numbers out of the model entirely, and the spreadsheet becomes both the artifact and the source of truth.

Here's the full pipeline — spec to validated .xlsx — running entirely on your machine, with numbers that recalculate because they were never typed by a language model in the first place.

The Division of Labor

Every failure story — the 4.6% March, the totals that don't add up — comes from breaking this table, usually by asking the model for values. The moment a model outputs =B4*1.046 instead of 48,210, the spreadsheet stops being a spreadsheet and becomes a screenshot of a guess.

Step 1: The Spec Prompt, Assumptions First

Port over the best habit from cloud spreadsheet workflows: before anything gets built, the model must show its assumptions. Ask for structure and formulas in one structured pass:

I need a 12-month cashflow tracker in a small consulting business.

Sheets: Inputs, Monthly, Dashboard.
- Inputs: rates, one-time costs, growth starting point
- Monthly: revenue lines, costs, net, cumulative cash
- Dashboard: 6 summary cells referencing Monthly

Rules:
- Every computed cell must be a FORMULA. Never output a number
  where a formula belongs. Never invent data values — put
  placeholder cells in Inputs and reference them.
- Before building: list your top 10 assumptions about structure
  and logic so I can correct them. Then output:
  1) assumptions list
  2) the CSV content of each sheet's literal cells
  3) the formula map: cell -> formula -> plain-English meaning

The assumptions list is the sanity check. "I assumed revenue starts in month 1 and grows monthly from the Inputs tab" — correct that sentence now and you've saved the build. The no-values rule is the load-bearing constraint: it converts the model from a bad calculator into a good architect.

A good assumptions list comes back looking like this, and each line is a cheap correction instead of an expensive rebuild:

1. Revenue begins in month 1; no pre-launch period.
2. Growth compounds monthly from a single Inputs rate.
3. Costs split: fixed monthly + variable at 20% of revenue.
4. Tax is ignored; this is cashflow, not P&L.
5. Opening cash comes from Inputs, not hardcoded.
6. Currency is THB with no conversion column.

Six sentences like that, corrected in ten seconds, are the difference between a sheet that models your business and one that models the model's guess about your business.

Step 2: The Model's Output — Language, Not Numbers

A local 7B–14B model handles this fine, because the output is text the model is good at: CSV blocks for literal cells and a formula map. It looks like this:

{
  "sheet": "Monthly",
  "literal_cells": [
    "A1: Month", "B1: Revenue", "C1: Costs", "D1: Net", "E1: Cumulative"
  ],
  "formulas": [
    { "cell": "B2", "formula": "=Inputs!$B$2*12/12", "meaning": "month 1 revenue = base rate" },
    { "cell": "B3", "formula": "=B2*(1+Inputs!$B$3)", "meaning": "grows by monthly rate" },
    { "cell": "D2", "formula": "=B2-C2", "meaning": "net = revenue - costs" },
    { "cell": "E2", "formula": "=Inputs!$B$5+D2", "meaning": "cumulative from opening cash" },
    { "cell": "E3", "formula": "=E2+D3", "meaning": "running total" }
  ]
}

Notice what's absent: data. The model has described a machine for calculating your cashflow, not pretended to know your cashflow. You fill the Inputs tab — real rates, real opening balance — and every downstream number derives.

Step 3: The Script That Builds the File

The model never touches the file. A small Python script does — deterministic, inspectable, rerunnable:

# build_sheet.py — model output in, real spreadsheet out
from openpyxl import Workbook
import json

spec = json.load(open("spec.json"))
wb = Workbook()

for sheet in spec["sheets"]:
    ws = wb.create_sheet(sheet["name"])
    for cell, value in sheet["literal_cells"].items():
        ws[cell] = value
    for f in sheet["formulas"]:
        ws[f["cell"]] = f["formula"]   # a real formula, not a value

wb.save("cashflow-tracker.xlsx")
# open it: numbers appear because Excel/Sheets evaluates the formulas

This step is why the local pipeline beats pasting chat output by hand. Paste-and-pray loses formulas, mangles references, and quietly converts =E2+D3 into 48,210. The script guarantees the formula survives into the cell — which is the entire difference between a spreadsheet and a prop.

Step 4: The Validation Gate

Structure bugs still happen — a formula pointing at the wrong row, a circular reference. Add the check before you trust the file:

# validate.py — structural cross-checks, no AI involved
from openpyxl import load_workbook
wb = load_workbook("cashflow-tracker.xlsx")

checks = [
    ("Monthly!E13 equals Dashboard!B2", "cumulative year-end matches dashboard"),
    ("count of formulas in Monthly >= 36", "every computed cell is a formula"),
    ("Inputs has no formula cells", "inputs are literals by design"),
]
# assert each, print PASS/FAIL with cell detail

Three checks catch ninety percent of model mistakes: the rollup matches the detail, every computed cell is a formula, and inputs are literals. Anything failing goes back to the model with the specific broken formula — "E13 references D13 but cumulative skips month 12, fix the formula map" — which is a language problem, and models fix language problems well.

Editing Later: Talk to the Data, Locally

The cloud pitch for spreadsheet AI is "chat with your sheet." The local version is better defined: you chat with the spec, not the data. Want the Dashboard to show a worst-case row? That's a spec change:

ollama run qwen2.5:7b """
Current spec: {{spec.json}}. Change: add a worst-case scenario row to
Dashboard driven by a pessimism multiplier in Inputs. Output only the
new formula-map entries and the Inputs cells they need.
"""
# merge entries into spec.json -> rerun build_sheet.py

Rerun the builder, and the change lands as formulas across every month — no clicking through forty cells, and no risk of the "helpful" edit quietly hard-coding a number. The spreadsheet stays regenerable because the spec, not the file, is the source of truth.

The Weekly Refresh, Automated

Once the pipeline exists, it's an n8n workflow like any other:

workflow: weekly-sheet-refresh
schedule: "0 6 * * 1"
nodes:
  - name: pull-actuals
    type: http-request
    url: "http://localhost:8200/metrics/last-week.json"
  - name: merge-inputs
    type: code
    rule: "update Inputs literals from actuals (revenue, hours)"
  - name: rebuild
    type: execute-command
    command: "python /peak/scripts/build_sheet.py && python /peak/scripts/validate.py"
  - name: deliver
    type: email
    attachments: ["/peak/out/cashflow-tracker.xlsx"]
    on_success: true
  - name: alert
    type: telegram
    on_fail: true
    message: "sheet rebuild failed validation — spec or formulas drifted"

Monday 6 a.m.: fresh actuals flow into Inputs, the sheet rebuilds with real formulas, validation runs, and the file lands in your inbox — or a failure lands on your phone. Your numbers are always one generation old, never one guess old.

Cloud Extension vs Local Pipeline

Make It Practical Tonight

• Take one spreadsheet you build by hand each month; write its spec with the assumptions-first prompt.
• Run model → spec → builder → validator once, end to end, before touching a real file.
• Fill the Inputs tab with real numbers and watch every formula derive them correctly — the moment the approach clicks.
• Wire the Monday refresh only after one manual cycle has passed validation.

The model was never going to be your calculator, and it didn't need to be. Spreadsheets already have one — the model just needed to stop guessing long enough to write it down.

Frequently Asked Questions

Can the model's formulas be wrong even if it never invents numbers? Yes — wrong references, skipped months, circular logic. That's what the validation gate is for: structural checks (rollups match detail, formula counts, literal-only inputs) catch the common classes, and failures loop back to the model as a precise language fix. You audit a formula the same way you'd audit a sentence, which is a human-scaled job.

Does this really produce a normal .xlsx I can open anywhere? Yes — openpyxl writes a standard file with live formulas that Excel, Google Sheets, and LibreOffice all evaluate natively. Nothing about the pipeline makes the file exotic; it's indistinguishable from a carefully hand-built sheet, which is rather the point.

Which local model is good enough for the spec and formula map? A 7B model handles single-sheet structures; multi-tab logic with cross-references is more comfortable at 14B. The output is short and structured, so even the small tier performs — and when it fumbles a cross-sheet reference, the validator catches it and the retry fixes it. Bigger models mainly reduce the number of retry loops.

What if my sheet needs charts and conditional formatting? Both are script territory: openpyxl adds charts and formatting rules deterministically from the spec, same as formulas ("bar chart of Monthly!B2