Samples / FP&A month-end model

FP&A month-end model

A small company's monthly P&L model, written the way such models grow in real life: an order log with lookups, a revenue roll-up with whole-column SUMIFS, headcount-driven costs, a P&L with running totals and a dashboard.

Sheets

8

Formula cells

2,477

Logic blocks

43

Input cells

1,275

Final outputs

335

What the workbook contains, on purpose

• An order log with VLOOKUP price and region lookups, an EOMONTH month key, a discount rule and an FX conversion through a defined name.
• A revenue roll-up by product, region and month using whole-column SUMIFS and COUNTIF, a headcount-driven cost sheet with a running month index across the row, and a P&L with margins, tax, a year-to-date running total and quarter labels built with & and ROUNDUP.
• A dashboard with INDEX/MATCH, COUNTIF, AVERAGEIF and a TEXT headline.
• The habits that make migration hard: a volatile TODAY(), a hidden sheet feeding a management adjustment into the dashboard, a plug typed over a column of formulas, a product code missing from the price list so #N/A flows through SUMIFS into the totals, a month with no orders caught by IFERROR, a note that starts with = but is text, merged headers and defined names.

Functions used most: VLOOKUP (600), EOMONTH (312), TEXT (301), IF (300), SUMIFS (152), SUM (39), COUNTIF (14), MAX (13), ROUND (12), IFERROR (12), ROUNDUP (12), INDEX (2), MATCH (2), TODAY (1).

Results by target

Every formula cell is compared with the value Excel stored in the file. A match is within 1e-9; errors must match by code. The SQL target declares that Excel errors and the empty string are NULL, and the report counts the cells that matched only under that policy.

Generated input variations: 20 input sets were run through Excel 16.0 and the Python code side by side; 2,476 of 2,477 cells agreed on every set, 0 disagreed, and 1 depend on TODAY() so are not compared (Excel moves with the clock; the code is frozen to the file's save date).

The dependency graph

Sheets laid out by calculation depth; arrows carry formula counts. Click a sheet for its blocks and functions.

The generated code