spreadsheet-modeling

Files (6)

• README.md
• SKILL.md
• evals.json
• financial-modeling.md · references/
• formula-patterns.md · references/
• vba-patterns.md · references/

SKILL.md (first lines)

## Key principles

1. **Separate inputs, calculations, and outputs** - Every model should have a
   clear flow: assumptions/inputs on one sheet, calculations on another, and
   summary/output on a third. Never mix hardcoded inputs into formula cells.

2. **One formula per row/column pattern** - A column of formulas should use the
   same formula copied down. If row 5 has a different formula than row 6 in the
   same column, the model is fragile and hard to audit.

3. **Name things** - Use named ranges and structured table references instead of
   raw cell addresses. `=Revenue * Tax_Rate` is auditable; `=B7*$K$2` is not.

4. **No magic numbers** - Every literal value in a formula should either be a
   named constant or live in a clearly labeled input cell. If you see `*1.08`
   in a formula, extract `Tax_Rate` as a named input.

5. **Design for the next person** - Use consistent formatting, color-code input
   cells (typically blue font on yellow background), and add cell comments for
   non-obvious logic. Models outlive their creators.

---

## Core concepts

**Workbook architecture** organizes a model into layers. The standard pattern is:
Inputs/Assumptions sheet (all editable parameters), Calculations sheet (pure
formulas referencing inputs), and Output/Dashboard sheet (charts, KPIs, summary
tables). Larger models add a Cover/TOC sheet and a Data sheet for raw imports.

**Structured tables** (Excel Tables / named ranges in Sheets) are the foundation
of maintainable formulas. A table auto-expands when data is added, supports
structured references like `=SUM(Sales[Revenue])`, and makes pivot tables
reliable. Always convert raw data ranges to tables before building on them.

**Array formulas and dynamic arrays** enable powerful multi-cell calculations.
Excel's FILTER, SORT, UNIQUE, and SEQUENCE functions (and their Google Sheets
equivalents) replace many complex INDEX-MATCH or helper-column patterns with
single formulas that spill results across multiple cells.

**Pivot tables** summarize large datasets without formulas. They support grouping,
calculated fields, slicers for interactivity, and can feed charts. The key skill
is choosing the right row/column/value/filter field layout for the question being
asked.

---

## Common tasks

### Write a lookup formula

Use XLOOKUP (Excel 365+) or INDEX-MATCH as the universal lookup pattern. Avoid
VLOOKUP for new work - it breaks when columns are inserted.

**XLOOKUP (Excel 365+ / Google Sheets):**
```
=XLOOKUP(lookup_value, lookup_array, return_array, "Not found", 0)
```

**INDEX-MATCH (all versions):**
```
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
```

**Two-criteria lookup (INDEX-MATCH-MATCH):**
```
=INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))
```

> Always wrap lookups in IFERROR or use XLOOKUP's built-in if_not_found argument
> to handle missing values gracefully.

### Build a conditional aggregation

Use SUMIFS/COUNTIFS/AVERAGEIFS for multi-criteria aggregation.

```
=SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Date], ">="&DATE(2025,1,1))
```

**Dynamic array alternative (Excel 365+):**
```
=SUM(FILTER(Sales[Amount], (Sales[Region]="West") * (Sales[Date]>=DATE(2025,1,1))))
```

> SUMIFS criteria ranges must all be the same size. Mismatched ranges produce
> a #VALUE! error with no helpful message.

### Create a pivot table

Step-by-step framework for designing a pivot table:

1. **Define the question** - "What is total revenue by region and product category for Q1?"
2. **Identify the fields** - Rows: Region, Product Category. Values: SUM of Revenue. Filter: Date (Q1)
3. **Build the pivot** - Select data table, Insert > PivotTable, drag fields to areas
4. **Format** - Apply number formatting to values, add a slicer for Date for interactivity
5. **Refresh strategy** - If source data changes, right-click > Refresh. For auto-refresh, use VBA or Apps Script

**Calculated field example** (add a margin calculation inside the pivot):
```
Margin = Revenue - Cost
```

> Pivot tables silently exclude rows with blank values in row/column fields.
> Clean your data before pivoting.

### Design a dashboard

Build dashboards on a dedicated output sheet that references calculation sheets.

**Layout checklist:**
1. Top row: Title, date range selector (data validation drop-down), refresh button
2. Row 2-4: KPI cards (large numbers) - Revenue, Growth %, Units Sold
3. Main area: 2-3 charts (combo chart for trends, bar chart for comparisons, pie only if fewer than 6 categories)
4. Bottom or right: Detail table with conditional formatting (data bars, color scales)

**KPI formula pattern:**
```
=TEXT(total_revenue, "$#,##0") & "  (" & TEXT(growth_rate, "+0.0%;-0.0%") & ")"

From mkurman/zorai, MIT.

More from mkurman/zorai