A P&L that foots.
A full profit & loss statement built from a real double-entry general ledger: 27,909 postings, 26 P&L accounts, seven territories. One matrix answers three questions, and subtotals sit in the same table as the detail lines beneath them without counting anything twice. The statement below is the Power BI report rebuilt for the web, from the same account mapping and the same measure definitions, followed by the report’s second page, which explains why the year is behind plan.
The statement
One P&L, three questions. The columns change with the view, not just the numbers; in the report that is a disconnected caption table on the matrix’s column axis and a single measure resolving each cell. Costs print positive, and a variance is green when it moves the right way for that line: under budget on a cost is favourable, under budget on revenue is not.
Revenue by month: actual against budget and forecast
EBITDA variance to budget, by business unit
The full-year miss is not spread evenly. Two territories carry almost all of it, and two are ahead of plan, which is the difference between “we missed” and a conversation someone can actually act on.
Page 02: why the year is behind plan
The statement says where the year stands. The second page of the report asks why, and is shown here as the report itself rather than redrawn. January to the October close, 2020 revenue is 3.6% behind budget but EBITDA is 26.2% behind: costs did not come down with sales, and the EBITDA margin fell from 30.6% to 23.4%. The USA (−$324k) and the UK (−$312k) carry the miss while New Zealand and Germany beat their budgets. Against 2019 both are up (revenue 37.7%, EBITDA only 4.0%), which is the same margin squeeze seen from the other side.
- Effect on profit needs no sign table. Amounts stay signed as posted, so actual minus comparison is favourable when positive for income and costs alike; the page can rank all 26 accounts on one scale.
- The cards are SVG, drawn by DAX. Revenue and EBITDA with a ring of actual as a share of the comparison, a tile per business unit, a bar per account. Each is one measure, so it redraws with every choice.
- Every sentence is a measure, and one was wrong. The summary first said “costs moved in step with sales” for revenue up 37.7% and EBITDA up 4.0%, because it compared the two growth rates. It now tests the margin, which fell 7.6 points.
- A filter panel over the page narrows everything to a region or business unit, including the cards and the ranking.
- Mocked up in HTML first, with the real figures, then generated into the existing report without touching the statement page, and checked against pandas.
What is real here, and what is not
- The actuals are the ledger, untouched. Every actual figure is a sum of real GL postings from a genuine double-entry ledger, chosen over the “budget vs actual” datasets that rank higher in search, which are personal expense trackers, not P&Ls.
- Budget and forecast are generated. No public dataset carries plan data at account × entity × month grain: that is proprietary FP&A data. Rather than pretend otherwise, the ETL builds a planning layer from the actuals with rules stated in one place: budget is the prior year grown by a per-account rate and spread on a smoothed seasonality curve; forecast is actuals to the October close plus a re-based budget for November and December. It is deterministic, so it reproduces exactly.
-
Subtotals and detail lines share one matrix. Gross Profit and the lines
that build it are both on the row axis, so the accounts underneath are claimed by
several rows at once. A bi-directional relationship would double count them. Instead a
disconnected line table maps to accounts through a bridge, and the measure applies that
account list with
TREATAS: a filter, not a join. -
One column does two jobs. A display sign of −1 on cost lines
makes costs print positive the way a statement reads, and defines
favourability as
variance × sign > 0. That is why every green and red cell above is right with no per-line exception list behind it. - Margin rows carry their parent’s accounts. Gross Margin % resolves to Gross Profit’s account set, so the ratio has a numerator; its variance is in percentage points, not a percentage change, because the difference between two percentages is not a percentage.
- The ETL refuses to write a statement that does not foot. Every subtotal must be exactly the union of the lines building up to it, with no account counted twice. That assertion caught a missing interest-expense account in Net Income during the build.
- The ledger itself is not published. Kaggle lists it as “Data files © Original Authors”, which grants no redistribution licence, so neither the workbook nor the star schema is in the repository. What this page reads is the 21-line statement summary, not the ledger; the page 02 screenshots show year-to-date totals per account across all territories, never a posting.