Excel All the Way Down: Building an FP&A Data Platform with Power Query, Power Pivot, and CUBE Functions

After a decade of SQL, Python, and R, I took a finance role where none of them is available. What I found inside the Microsoft stack surprised me: a full dimensional data warehouse, hiding in plain sight — with Excel as both the kitchen and the dining room.

A different country

Earlier this year I joined the FP&A function of a multinational, in a regional role covering several Central European markets. Financial planning and analysis was a new field for me. My previous decade — Amazon, research institutions, marketing analytics — ran on a familiar stack: SQL for the data, Python and R for the analysis, AWS when the work outgrew the laptop, LaTeX for the write-up, git for version control underneath it all, and the quiet assumption that if you need a database, someone will provision one.

FP&A, I quickly learned, is a different country. They speak a different dialect and they use different tools, and the two facts are connected.

The dialect first. The atomic object of FP&A is the P&L statement — revenue at the top, operating profit at the bottom, a fixed canonical order in between. The core activity is comparing versions of it: actuals against budget, this month’s forecast against last month’s, this year against last year. And the native way of consuming those comparisons is the bridge — the waterfall chart that decomposes a variance into named drivers. Volume did this, price and mix did that, costs did the rest. A well-built bridge is genuinely a beautiful thing: a decomposition exercise, an accounting identity, and an executive summary all at once.

Then the tools. The corporate software catalog does not include PostgreSQL or SQL Server access for finance teams. Corporate policy blocks Python package installation on work machines. Conservative organizations make a trade-off: security and compliance governance over developer velocity. Finance, sitting far from the engineering side of the house, lives in that perimeter. The set of what you can actually run is, to a first approximation, Microsoft 365. Excel, Power BI, SharePoint, and whatever lives inside them.

So the problem statement wrote itself. The team needed a central data repository — one trusted place where every version of every market’s P&L lives — and the entire modern data stack I would normally reach for was unavailable. What I had was Excel, Power BI, and SharePoint. That third one matters more than it sounds: with workbooks living on SharePoint rather than on anyone’s hard drive, Power BI can consume them in the cloud, and Excel can consume Power BI right back. The pieces could talk to each other cloud-to-cloud, no file ever attached to an email. That is not a spreadsheet with delusions — that is a data platform waiting to be assembled.

The monster workbook

Before describing the fix, it’s worth describing the disease, because the FP&A community knows it well. I first heard this pattern articulated in The FP&A Guy podcast, and it showed up everywhere once I started paying attention.

When your storage format is a spreadsheet, data accumulates in reporting layout: P&L lines down the side, months across the top, merged headers, embedded subtotals. That layout is excellent for reading and terrible for storing. Every new forecast version needs a new column block or a new sheet. Every new reporting unit needs another. Formulas reference row positions, so inserting a line breaks everything downstream. Within a few planning cycles you have the monster workbook: a file that takes ten seconds to open, holds a dozen versions of the truth across thirty sheets, and is maintained through a mixture of muscle memory and fear.

The analogy I used to communicate this to my colleagues: think of the data warehouse as a depot, the transformation layer as logistics, and the formatted P&L as the customer walking into a storefront. A storefront is not where inventory lives—it’s where it is displayed. The depot stores raw materials in bulk. Logistics picks, sorts, and stages them. The customer sees a curated presentation. Nobody confuses these layers. Yet in spreadsheet finance, we were treating the storefront (formatted P&L) as the depot (the source of truth). Storing your data as stacks of formatted P&Ls works fine when you have three of them. It collapses when you have eight reporting units times twelve months times five plan versions, because now you are curating hundreds of hand-maintained storefronts and calling it a repository.

The shape of the fix

The fix is the oldest idea in data warehousing, and it survives the loss of the warehouse. The data warehouse community has debated several design philosophies: fully normalized relational schemas, Ralph Kimball’s star-schema dimensional model, and Bill Inmon’s data vault approach. For this context—constrained to Excel and Power Pivot, needing to balance analyst accessibility with analytical power—Kimball proved the right fit. Store everything as one tidy, long-format table, schema-locked: every single value gets its own row, with columns identifying what it is — reporting unit, product, P&L line, period, plan version, value. Around that fact table, a small star of dimension tables in the Kimball fashion: one describing reporting units, one describing products, one describing metrics, one describing periods, one describing versions.

Two design choices matter more than the rest.

Version is a dimension. In the monster workbook, a new forecast version is a structural event — new columns, new sheets, new formula ranges that ripple through dependent calculations. In the dimensional model, version is just another dimension: a column in the fact table that you can filter on or group by. The schema does not change when you add a new plan version; you add rows. Comparing budget to latest estimate is a filter on two version values and a subtraction. The bridge — FP&A’s native artifact — stops being a hand-built sheet and becomes a query.

Snapshots, not overwrites. A team that forecasts monthly produces many versions of the same underlying fact, and the instinct is to keep only the latest. That destroys exactly the information a planning function should treasure: forecast drift. Every load appends rows stamped with a snapshot date; nothing is ever overwritten. Any analyst hungry to understand how estimates evolved will recognize this immediately — it is vintage data, the discipline of preserving every revision to see how your view of the year changed with new information. You cannot audit how your understanding evolved if you keep painting over it.

None of this required SQL. It required the ideas that SQL taught me. That distinction turned out to be the theme of the whole project.

A database in disguise

Here is the discovery that made the architecture possible: the Microsoft stack contains a real database. It is just not called one.

The Power BI semantic model — the same engine that appears inside Excel as Power Pivot, the “data model” — is an in-memory, columnar, tabular store. It holds millions of rows without complaint, understands relationships between tables, and exposes a full analytical language (DAX) for defining measures. Power Query, sitting in front of it, is a genuine ETL layer: it ingests folders of files, unpivots reporting layouts into long format, merges, types, and cleans — every transformation recorded as an auditable step.

Once you see it, the layered architecture that data engineers would recognize falls out naturally. Files from the reporting units land in a SharePoint folder — the bronze layer. Power Query transforms them into the canonical long-format tables inside the semantic model — the silver layer. And reporting surfaces consume the model — the gold layer. The same medallion pattern the big data platforms advertise, executed entirely within the Office perimeter, by a single person.

What’s neat is the circularity. Excel is the data collection instrument at one end and the presentation surface at the other, with the semantic model in the middle. Data flows from Excel, through Power Query, into the model — and streams back into Excel through Power Pivot and CUBE functions. Excel in, Excel out. Excel all the way down.

Excel in: the template is the contract

The collection side deserves its own paragraph, because it is the highest-leverage and least glamorous piece.

Reporting units send their numbers in Excel files. Where the corporate structure defines the format, the input template becomes a contract: a protected workbook with locked structure, unlocked entry cells, data validation on every input, and hidden signature cells the ingestion layer reads to detect the template version. Power Query then trusts that structure absolutely — a file that doesn’t match is a process violation, not a data-cleaning exercise. Where reporting units keep their native formats, the mapping into the canonical schema lives in per-unit Power Query logic instead, written once and rerun every month.

Either way, the principle is the same one I preached in my last essay from the other side of the fence: data quality is decided upstream, at the point of collection, not downstream at the point of analysis. If the intake is clean, everything after it is arithmetic.

Excel out: three surfaces, not one

The consumption side taught me the single most useful framing of the project: a planning team needs three distinct kinds of output, with different design constraints, and conflating them is precisely what produces monster workbooks.

The exploration surface is for the analyst in the meeting. It is a PivotTable connected to the data model, with slicers for reporting unit, version, and period. Optimized for speed of iteration: drag fields, slice, drill, refresh. Nobody prints it. It is a thinking tool, and the classic PivotTable — unfashionable as it is — remains unbeatable at this job.

The reporting surface is for the director and everyone above. It is a hand-laid-out sheet in strict financial-reporting convention: canonical P&L order, parentheses for negatives, bold totals. The layout is hand-controlled; the numbers are not. In its first incarnation every value cell holds a SUMIFS against the long-format table, driven by filter cells at the top — change the version filter, the whole statement recalculates. The mature incarnation replaces SUMIFS with CUBE functions.

The input surface is the protected template described above. Its design constraint is neither flexibility nor polish but enforcement.

Most broken workbooks I have inherited are one of these surfaces that accreted features of the other two — the report that grew pivot tables, the exploration file that grew hand-formatted summaries someone started depending on. Each accretion is individually reasonable. The accumulated mess is not. The discipline is to recognize when a request is asking the wrong surface to do the wrong job, and route it to the right one instead.

CUBE functions, the unsung heroes

CUBE functions deserve a section, because almost nobody outside corporate finance seems to know they exist, and they are the most elegant thing in this entire architecture.

CUBEVALUE and its siblings (CUBEMEMBER, CUBESET) let an ordinary worksheet cell query the data model directly. You define a measure once in the model — Gross Margin, Operating Profit, Variance vs. Last Year, each with its business logic encoded in DAX — and then any cell, anywhere, in any layout, can ask for that measure filtered to any combination of dimensions. The sheet looks hand-typed. Underneath, every number is data-bound to a single governed definition.

What this buys you is the separation that report builders in every BI tool take for granted and Excel historically never had: layout and logic live in different places. The formatting, the row order, the white space that makes a P&L readable to a finance director — all hand-controlled on the sheet. The calculation — defined once, tested once, reused everywhere. When the definition of a measure changes, it changes in one place, and forty reports update. Anyone who has ever chased an inconsistent margin definition across a folder of workbooks will understand what that is worth.

There is a pleasing historical depth here, too. Under the hood, CUBE formulas speak MDX — the query language of Analysis Services, the OLAP engine Microsoft has shipped since the late nineties — and the old literature is explicit that Excel was always meant to be the organization’s “core BI client,” tightly integrated with the analytical engine behind it. The Wrox guide to Analysis Services 2008 reads today like a description of this exact architecture, written one corporate generation early. The tools I am wiring together were designed to be wired together; most organizations just never got around to it.

The honest ledger: the syntax is verbose, the learning curve is real, and it needs the data model to exist first. SUMIFS against a tidy table is the right first step — it works everywhere, everyone can audit it, and ninety percent of FP&A reports in the wild are built this way for good reason. CUBE functions are what you graduate to once the model has earned the team’s trust. The Excel 365 Bible mentions them only once, in passing—as the formula type Excel generates when you convert a Power Pivot pivot table to OLAP sourcing. Useful as a reference, but not as exhaustive on CUBE functions as one might hope.

A footnote on the newer alternatives: the dynamic-array functions (PIVOTBY, GROUPBY, FILTER) are wonderful for live summary views that should update without a refresh click. But they resist the canonical P&L: financial statements have a deliberate, non-alphabetical row order, and number formatting doesn’t stick reliably to spilled arrays. Different tools, different jobs — a sentence I seem to write in every essay.

Shadow IT, reframed

There is a name for what I have described, and it is usually said with a sneer: shadow IT. Business users building their own solutions in Excel because the official systems don’t deliver what they need, in the shape they need it.

I want to push back on the sneer. Deckler and Powell’s Mastering Microsoft Power BI offers a better frame: deployment modes on a spectrum. At one end, corporate BI — IT owns everything; secure, governed, and slow to adapt. At the other, pure self-service — analysts own everything; responsive and fragile. In between sits managed self-service: the business owns the work, the platform provides governance, and both sides can see what is happening. Artifacts are not condemned to one mode forever — a workbook can start as one analyst’s tool, mature into a team-owned model with schema discipline and validation, and eventually graduate into IT-supported infrastructure if it earns the right.

Through that lens, a finance team’s Excel workarounds are not evidence of unsophistication. They are evidence that the team is competent at solving problems faster than the official channels can. The productive move is not to confiscate the spreadsheets; it is to migrate the load-bearing ones a step along the spectrum — add the schema, add the snapshots, add the single source of truth — while leaving everything else gloriously, usefully improvised.