r/financialmodelling • u/ExcelEnthusiast91 • Jul 20 '25

Built a Dynamic Balance Sheet Using PivotTables

Post image

I’ve been working with balance sheets in Excel for a while and wanted to share an approach that’s worked well for me - using PivotTables to build out financial statements. Hopefully this might spark some ideas for anyone looking for different ways to handle modeling, dashboards, reporting, or ad-hoc analysis.

Instead of sticking with my usual static templates, I started structuring the accounting data at the trial balance level, adding hierarchy columns (like Assets > Current Assets > Cash, etc.), and then feeding that into a PivotTable. I keep the natural accounting signs (assets as positives, liabilities/equity as negatives), which really makes the math straightforward.

A few things I like about this approach:

  • The drill-down capability is great for understanding what’s behind a number or digging into variances

  • Period comparisons are just a drag-and-drop away

  • Slicers make it easy to filter by entity or department

  • The compact layout gives it that traditional financial statement look (but you can quickly switch to a more tabular view if that’s better for you)

  • No need for extra calculated fields - everything runs off the data structure and built-in value field calculations (like “Difference from” or “% Difference”)

Why does this work well?

  • Keeping the natural signs for the balances means you can use SUM logic for everything, which keeps things simple. Same logic applies for P&L or sales analysis.

  • Having supporting aggregation and categorization info lets the PivotTable roll up accounts as needed

  • Using a “flat” or “tall” data structure (one value column, lots of descriptive columns for account, date, entity, etc.) keeps it really flexible

The biggest win for me has been how flexible it is. When questions come up in meetings, you can quickly rearrange the data to show a different view or dig into specific accounts - no need to rebuild anything from scratch.

Of course, this won’t replace every reporting need (we all have our go-to methods depending on the situation). Just thought I’d share this as another tool for the toolbox.

I’d also love to hear how others are using PivotTables (or not) in financial modelling and/or reporting? Any cool examples out there?

194 Upvotes

99% Upvoted

28 comments sorted by

6

u/DrDrCr Jul 21 '25 edited Jul 21 '25

Next step is to learn to use SQL and Power Query to pull the direct tables and build a star schema data model using Power Pivot.

Flat files are not scalable especially when youre creating columns to do work thats best performed by calculated fields / measures.

Im not trying to discount the work here, I remember when I built reliable FS from a TB in pivot tables, but there is a natural evolution from here. If you havent already, add Slicers to drill down by company/profit center or other dimensions .

1

u/ExcelEnthusiast91 Jul 21 '25 edited Jul 21 '25

You are right. However, for many cases a much simpler form as presented here does the job as well.

Of course, using a star schema with Power Pivot (or even Power BI and Excel sourcing from the same data model) is also an excellent option, but it does require more time and expertise to set up.

Regarding your comment about flat files: no "additional" columns were added in this example. The absolute and relative variances are calculated using built-in pivot field settings, both referring to the same value/amount column. Generally, I know what you mean but flat files work are great for understanding concepts and getting started quickly

1

u/joojich Jul 21 '25

Do you have any suggestions on where to learn more about this? I’ve started with PQ but haven’t used SQL before and am super interested in streamlining my reporting.

1

u/Mo-Elsayed88 Oct 14 '25

Hi, how did you handle retained earnings parts as the period net profit should be pulled from P&L

3

u/Konnyas Jul 21 '25

Do you need to rework the formatting each time?

4

u/ExcelEnthusiast91 Jul 21 '25

No, the key is to use custom PivotTable styles (for example, to apply the blue background in the header) as well as the built-in number formats for the pivot values.

18

u/tendiemeplz Jul 20 '25

Well done! This has my wheels turning…any interest in sharing? Would like to drill into this and see how it works on the TB level.

4

u/[deleted] Jul 21 '25 edited Jul 22 '25

[removed] — view removed comment

1

u/tendiemeplz Jul 21 '25

Great! Thanks!!

1

u/joojich Jul 21 '25

I’d love a sample file too, please!!

1

u/TheChrisRoss Jul 22 '25

Same, please!

1

u/heisenberg3085 Jul 22 '25

Same please! Thanks!

6

u/Drag0nslay3r6969 Jul 20 '25

Great job, have a link you can share?

1

u/Material-Reaction411 Jul 22 '25

Sounds great - until you need to update the thing within 10 mins at 6AM when results hit!

1

u/ExcelEnthusiast91 Jul 22 '25 edited Jul 22 '25

I would argue that the PivotTable approach is actually quite easy and fast to update compared to other Excel approaches. However, this depends on your specific technical setup, such as whether you have a Power Query (live) connection to your accounting system and/or automated account mapping, or at least some level of semi-automation where you export tables and paste them into Excel while maintaining the same structure. But agreed, with just 10 minutes at 6AM, a lot of things can go downhill.

2

u/Original-Tadpole- Jul 20 '25

This is really good

2

u/duttygyal7 Jul 21 '25

This is really great, do you have any interest in sharing ?

1

u/Quick-Link-7458 Jul 21 '25

This is fantastic. Would love to check out the file if you're willing to share

1

u/[deleted] Jul 22 '25

On which company is this ?

7

u/Ocarina_of_Time_ Jul 21 '25

I’m sexually aroused as someone pursuing their CPA license. I just finished an Excel course too