Sheet Model — Headless Spreadsheet for Agents

Build spreadsheet models programmatically using HyperFormula (headless computation engine) + ExcelJS (xlsx export). The spreadsheet IS both the computation and the deliverable.

When to Use This vs the xlsx Skill

Setup

Install dependencies (run once):

cd {baseDir}
npm install

Architecture

Agent code (declarative)          SheetModel wrapper          Output
────────────────────────    ──────────────────────────    ──────────────
addRow('Revenue', 701384)   → HyperFormula (compute)     → Console table
addRow('EBITDA', formula)   → Named ranges auto-tracked  → .xlsx with live
addScenarioSheet(config)    → {Name} refs resolved       →   formulas +
getValue('EBITDA')          → Dependency graph updates    →   styling +
exportXlsx('model.xlsx')    → ExcelJS (export)           →   cond. format

Core API

All code is ESM (.mjs). Import the wrapper:

import { SheetModel } from '{baseDir}/lib/sheet-model.mjs';
const M = new SheetModel();

Creating Sheets and Adding Data

M.addSheet('Data');

// Section headers (bold, no value)
M.addSection('Data', 'BALANCE SHEET');
M.addBlank('Data');

// Data rows — addRow returns the A1 row number (use it in SUM ranges)
const r_first = M.addRow('Data', '  Revenue',    701384, { name: 'Revenue' });
const r_costs = M.addRow('Data', '  Costs',     -450000, { name: 'Costs' });
const r_other = M.addRow('Data', '  Other',        5000);

// Formula rows — use returned row numbers for SUM ranges
M.addRow('Data', '  EBITDA', `=SUM(B${r_first}:B${r_other})`, { name: 'EBITDA' });

// Formula rows using named references (auto-resolved by HyperFormula)
M.addRow('Data', '  Margin', '=EBITDA/Revenue', { name: 'Margin' });

Build top-to-bottom: Names must be defined before any formula that references them. Define data rows first, then formulas.

>

addBlank() and addSection() also return the A1 row number (useful for SUM range boundaries).

When to use which formula style:

Never use named expressions as range endpoints: =SUM(Revenue:OtherIncome) is undefined behavior.

Named References

The { name: 'Revenue' } option on addRow:

1. Registers a HyperFormula named expression (usable in any formula as Revenue)
2. Tracks the A1 row for internal cross-referencing
3. Exports as a proper Excel named range in .xlsx (via cell.names)

⚠️ Names are global across all sheets. Using { name: 'Revenue' } in two different sheets overwrites the first. Use unique, prefixed names for multi-sheet models: { name: 'Rev2024' }, { name: 'Rev2025' }.

Name validation rules:

• Must be valid Excel names: start with a letter or underscore, no spaces
• Cannot collide with Excel cell references: names like AC, PC, R1C1, A1 are rejected with a clear error
• Use descriptive names: AdjPC, TotalAC, CurrentAssets (not AC, PC, CA)

#### Cross-Sheet References (Data Sheets Only)

In addRow formulas, reference named cells on other data sheets with dot notation:

M.addRow('CashFlow', '  From Operations', '={PnL.NetIncome} + {PnL.Depreciation}');

This {Sheet.Name} syntax only works in addRow formulas, NOT in scenario output formulas. For scenarios, use named expressions (bare names) — they are global across sheets.

Scenario Sheets

The core feature — define inputs, scenarios, and output formulas declaratively:

M.addScenarioSheet('Scenarios', {
  inputs: [
    { name: 'GrowthRate', label: 'Revenue Growth %' },
    { name: 'CostCut',    label: 'Cost Reduction' },
  ],

  scenarios: [
    { label: 'Base Case',    values: {} },                              // all inputs = 0
    { label: 'Optimistic',   values: { GrowthRate: 0.10, CostCut: 50000 } },
    { label: 'Conservative', values: { GrowthRate: 0.03, CostCut: 20000 } },
  ],

  outputs: [
    // {InputName} → column-relative (B2, C2, D2...)
    // DataSheetName → named expression from Data sheet (fixed)
    // {PriorOutput} → column-relative ref to earlier output in this sheet
    { name: 'AdjRev',  label: 'Adj. Revenue',  format: 'number',
      formula: 'Revenue * (1 + {GrowthRate})' },
    { name: 'AdjCost', label: 'Adj. Costs',    format: 'number',
      formula: 'Costs + {CostCut}' },
    { name: 'AdjEBITDA', label: 'EBITDA',      format: 'number',
      formula: '{AdjRev} + {AdjCost}' },

    // Section separator
    { section: true, label: 'RATIOS' },

    // Ratio with conditional formatting thresholds
    { name: 'EBITDAm', label: 'EBITDA Margin', format: 'percent',
      formula: '{AdjEBITDA} / {AdjRev}',
      thresholds: { good: 0.15, bad: 0.08 } },

    // Inverted threshold (lower = better)
    { name: 'DebtEBITDA', label: 'Debt/EBITDA', format: 'ratio',
      formula: 'TotalDebt / {AdjEBITDA}',
      thresholds: { good: 2.5, bad: 4.0, invert: true } },
  ],
});

Do NOT call `addSheet()` before `addScenarioSheet()` — it creates the sheet internally. Only use addSheet() for data sheets.

>

`addScenarioSheet` is a one-shot call. You cannot add rows to a scenario sheet after creation. Include all inputs, outputs, and sections in the config object.

>

Every output you want to reference later MUST have a `name` property. Without it, {ThatOutput} in a subsequent formula will throw an error.

#### Formula Reference Resolution in Scenarios

Inside outputs[].formula, references are resolved as follows:

Important: Bare names (no {}) are HyperFormula named expressions — they resolve to a fixed cell. {Wrapped} names resolve to column-relative cells within the Scenarios sheet.

⚠️ Name collision rule: If a scenario output {name} matches a Data sheet named expression, {OutputName} will resolve to the Data sheet's fixed cell, not the scenario output's column-relative cell. Always use unique names for scenario outputs. Example: Data sheet has { name: 'EBITDA' }, scenario should use { name: 'AdjEBITDA' } — never both EBITDA.

#### Output Formats

#### Thresholds (Conditional Formatting)

thresholds: { good: 0.15, bad: 0.08 }             // Higher is better (green >= 0.15, red < 0.08)
thresholds: { good: 2.5, bad: 4.0, invert: true }  // Lower is better (green <= 2.5, red > 4.0)

Colors: green (#E2EFDA), amber (#FFF2CC), red (#FCE4EC).

Reading Computed Values

// From Data sheet (by named ref)
const ebitda = M.getValue('Data', 'EBITDA');

// From Scenarios (by scenario index, 0-based)
const baseEBITDA = M.getScenarioValue('Scenarios', 0, 'AdjEBITDA');
const optEBITDA  = M.getScenarioValue('Scenarios', 1, 'AdjEBITDA');

// Raw cell access (sheet, col 0-indexed, a1Row 1-indexed)
const val = M.getCellValue('Data', 1, 5); // col B, row 5

Use getValue() for data sheets, getScenarioValue() for scenario sheets. getValue() on a scenario sheet returns the first scenario's value (column B).

Error Handling

// Formula errors (division by zero, etc.) return CellError objects, not exceptions
const val = M.getValue('Data', 'Margin');
if (typeof val !== 'number' || !isFinite(val)) {
  console.error('Formula error:', val);
  // val might be: { type: 'DIV_BY_ZERO' }, { type: 'REF' }, { type: 'NAME' }, etc.
}

// To prevent #DIV/0! in formulas, guard with IF:
M.addRow('Data', '  Margin', '=IF(Revenue=0, 0, EBITDA/Revenue)', { name: 'Margin' });

// Reference errors throw immediately during addRow/addScenarioSheet
// → Always define named rows BEFORE formulas that reference them
// → Error messages include the row label and cell reference for easy debugging

Common causes of formula errors:

• Division by zero in ratios → guard with IF(denominator=0, 0, numerator/denominator)
• Misspelled named expression → throws immediately with clear error message
• Circular reference → throws immediately with row context

Console Output

M.printScenarios('Scenarios');

Prints a formatted table with emoji flags for threshold-based RAG status (🟢🟡🔴).

Export to .xlsx

await M.exportXlsx('output.xlsx', {
  creator: 'Agent Name',
  headerColor: '1B3A5C',  // Dark blue header background (ARGB hex, no #)
});

The exported file contains:

• Live formulas (not static values) — user can change inputs and see results update
• Named ranges on all named cells (visible in Excel's Name Manager)
• Conditional formatting on cells with thresholds (green/amber/red)
• Frozen panes — first column frozen on all sheets; header row also frozen on scenario sheets
• Input cells highlighted in light blue (#DAEEF3)
• Section headers in bold

Named ranges on scenario sheets point to the first scenario column (column B). They exist for cross-sheet references in Excel, not for selecting all scenarios.

Advanced Styling

For styling beyond what SheetModel provides, modify the file after export with ExcelJS:

import { createRequire } from 'module';
const require = createRequire(import.meta.url);
const ExcelJS = require('exceljs');

await M.exportXlsx('model.xlsx');

const wb = new ExcelJS.Workbook();
await wb.xlsx.readFile('model.xlsx');
const ws = wb.getWorksheet('Data');
// Custom column widths, borders, fills, page setup, etc.
ws.pageSetup = { orientation: 'landscape', fitToPage: true, fitToWidth: 1 };
ws.getColumn(2).numFmt = '$#,##0';
await wb.xlsx.writeFile('model.xlsx');

Complete Example: Financial Model with Scenarios

import { SheetModel } from '{baseDir}/lib/sheet-model.mjs';

const M = new SheetModel();
M.addSheet('Data');

// ── Balance Sheet ──
M.addSection('Data', 'BALANCE SHEET');
M.addBlank('Data');
const r1 = M.addRow('Data', '  Cash',           50000, { name: 'Cash' });
const r2 = M.addRow('Data', '  Receivables',   120000, { name: 'Receivables' });
const r3 = M.addRow('Data', '  Inventory',      30000);
M.addRow('Data', '  Current Assets', `=SUM(B${r1}:B${r3})`, { name: 'CurrentAssets' });

M.addBlank('Data');
const r4 = M.addRow('Data', '  Payables',       80000, { name: 'Payables' });
const r5 = M.addRow('Data', '  Short-term Debt', 40000, { name: 'STDebt' });
M.addRow('Data', '  Current Liabilities', `=SUM(B${r4}:B${r5})`, { name: 'CurrentLiab' });

M.addBlank('Data');
M.addRow('Data', '  Equity', 200000, { name: 'Equity' });
M.addRow('Data', '  Long-term Debt', 150000, { name: 'LTDebt' });

// ── P&L ──
M.addBlank('Data');
M.addSection('Data', 'INCOME STATEMENT');
M.addBlank('Data');
const p1 = M.addRow('Data', '  Revenue',         500000, { name: 'Revenue' });
const p2 = M.addRow('Data', '  COGS',           -200000);
const p3 = M.addRow('Data', '  Operating Exp',  -150000);
const p4 = M.addRow('Data', '  Depreciation',    -30000, { name: 'Depreciation' });
M.addRow('Data', '  Operating Income', `=SUM(B${p1}:B${p4})`, { name: 'OpIncome' });
M.addRow('Data', '  Interest Expense',  -15000, { name: 'IntExp' });
M.addRow('Data', '  Net Income', '=OpIncome+IntExp', { name: 'NetIncome' });

// ── Scenarios ──
M.addScenarioSheet('Analysis', {
  inputs: [
    { name: 'RevGrowth',  label: 'Revenue Growth' },
    { name: 'DebtPaydown', label: 'Debt Paydown' },
  ],
  scenarios: [
    { label: 'As-Is',       values: {} },
    { label: 'Growth 10%',  values: { RevGrowth: 0.10 } },
    { label: 'Deleverage',  values: { RevGrowth: 0.05, DebtPaydown: 50000 } },
  ],
  outputs: [
    { name: 'AdjRev',   label: 'Adj. Revenue',      format: 'number',
      formula: 'Revenue * (1 + {RevGrowth})' },
    { name: 'AdjEBITDA', label: 'EBITDA',            format: 'number',
      formula: '{AdjRev} + (Revenue - OpIncome + Depreciation) / Revenue * {AdjRev} * -1 + ABS(Depreciation)' },
    { name: 'TotalDebt', label: 'Total Debt',        format: 'number',
      formula: 'LTDebt + STDebt - {DebtPaydown}' },
    { name: 'NetDebt',   label: 'Net Debt',          format: 'number',
      formula: '{TotalDebt} - Cash' },

    { section: true, label: 'KEY RATIOS' },
    { name: 'CurrRatio', label: 'Current Ratio',     format: 'ratio',
      formula: 'CurrentAssets / CurrentLiab',
      thresholds: { good: 1.5, bad: 1.0 } },
    { name: 'DebtEBITDA', label: 'Debt/EBITDA',      format: 'ratio',
      formula: '{TotalDebt} / {AdjEBITDA}',
      thresholds: { good: 2.5, bad: 4.0, invert: true } },
    { name: 'ICR',  label: 'Interest Coverage',      format: 'ratio',
      formula: '{AdjEBITDA} / ABS(IntExp)',
      thresholds: { good: 3.0, bad: 1.5 } },
    { name: 'EBITDAm', label: 'EBITDA Margin',       format: 'percent',
      formula: '{AdjEBITDA} / {AdjRev}',
      thresholds: { good: 0.20, bad: 0.10 } },
    { name: 'ROE',  label: 'Return on Equity',       format: 'percent',
      formula: 'NetIncome / Equity',
      thresholds: { good: 0.12, bad: 0.05 } },
  ],
});

// Use
M.printScenarios('Analysis');
console.log('EBITDA (Growth):', M.getScenarioValue('Analysis', 1, 'AdjEBITDA'));
await M.exportXlsx('financial-model.xlsx');

Recipe: Loan Amortization

const M = new SheetModel();
M.addSheet('Loan');

M.addSection('Loan', 'LOAN PARAMETERS');
M.addBlank('Loan');
M.addRow('Loan', 'Principal',    500000, { name: 'Principal' });
M.addRow('Loan', 'Annual Rate',  0.05,   { name: 'AnnualRate' });
M.addRow('Loan', 'Years',        20,     { name: 'Years' });
M.addRow('Loan', 'Monthly Rate', '=AnnualRate/12', { name: 'MonthlyRate' });
M.addRow('Loan', 'Periods',      '=Years*12',      { name: 'Periods' });
M.addBlank('Loan');
// PMT returns negative (cash outflow) — negate for display
M.addRow('Loan', 'Monthly Payment', '=-PMT(MonthlyRate, Periods, Principal)', { name: 'Payment' });
M.addRow('Loan', 'Total Interest',  '=Payment*Periods - Principal', { name: 'TotalInterest' });

await M.exportXlsx('loan-model.xlsx');

Available Formulas

HyperFormula supports 395 built-in functions including:

• Math: SUM, AVERAGE, MIN, MAX, ABS, ROUND, CEILING, FLOOR, MOD, POWER, SQRT, LOG
• Financial: PMT, FV, NPER, PV, RATE, NPV, XNPV
• Logical: IF, IFS, AND, OR, NOT, SWITCH, IFERROR
• Lookup: VLOOKUP, HLOOKUP, INDEX, MATCH
• Statistical: COUNT, COUNTA, COUNTIF, SUMIF, SUMIFS, AVERAGEIF
• Text: CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, TEXT
• Date: DATE, YEAR, MONTH, DAY, TODAY, DATEDIF, EOMONTH

Full list: https://hyperformula.handsontable.com/guide/built-in-functions.html

ExcelJS Gotchas (Critical)

These bugs were discovered empirically and MUST be followed:

1. Named ranges: Use cell.names = ['Name'], NEVER definedNames.add() or addEx().

• add() tries to parse the name as a cell ref → crashes on names like InvFinCP
• addEx() silently doesn't persist to the .xlsx file
• SheetModel handles this automatically — don't use ExcelJS definedNames directly

1. Formula prefix: HyperFormula getCellFormula() returns "=SUM(...)" with leading =.

ExcelJS expects { formula: 'SUM(...)' } without =. Double = causes #NAME? errors. SheetModel handles this automatically.

1. Formula language: .xlsx always stores formulas in English (SUM, ABS, IF).

Excel translates to locale on display. Always write English function names.

Limitations

• Row limit: Practical limit ~5,000 rows. For larger datasets, use pandas/openpyxl via the xlsx skill.
• Single value column on data sheets: addRow writes to columns A (label) and B (value) only. For multi-period models (Year 1, Year 2, Year 3), use a scenario sheet where each "scenario" is a period, or use direct HyperFormula API (M.hf.setCellContents(...)) for additional columns.
• Data sheet formatting: All values in data sheets are formatted as integers (#,##0). For percentages, ratios, or decimals, compute them in a scenario sheet output with the appropriate format option, or post-process with ExcelJS (see Advanced Styling).
• No charts: HyperFormula/ExcelJS can't create Excel charts. Add charts manually or use a separate tool.
• No pivot tables: Use pandas for pivot-style analysis.
• Scenario columns: Maximum 25 scenarios per sheet (columns B–Z). For readability, keep to 10 or fewer.
• Named range naming: Names that match Excel cell/column references (e.g., AC, R1C1, A1) are rejected automatically. Use descriptive names.
• Data sheet formulas in .xlsx: The exported Excel formula is the original text, not extracted from HyperFormula. Stick to bare named expressions (e.g., Revenue, OpIncome) and A1 refs via template literals (e.g., ` =SUM(B${r1}:B${r3}) `).