PART 6 : How to Build a Live Excel Financial Dashboard 📊 Connected to AppSheet 🔗

In Video 6 of the AppSheet & More series, we return to Power Query to fix a critical issue in our General Ledger (GL) data — ensuring that the Balance Sheet balances at the end of every month, not just at year-end.

By default, many accounting systems generate year-end write-off (closing) entries dated 31st December to close Profit & Loss accounts. While this works for annual reporting, it breaks monthly Balance Sheet accuracy when building dashboards in Excel.

In this video, we solve that problem properly.

🛠 What you’ll learn in this video:

Why Balance Sheets often only balance at year-end in Excel dashboards

What write-off / closing entries are in an accounting system

How system-generated closing entries affect monthly reporting

How to use Power Query to adjust transaction dates

How to move write-off entries from 31st December to the month they actually occurred

How this fix ensures the Balance Sheet balances every transaction month

CHAPTERS;
Introduction - 0:00
Creating A measure to negate expenses - 0:40
Sorting Months in Power Pivot - 9:05
Fixing Data flaw - 11:00
Changing Profit flow to Retained Earning to monthly - 14:38


🔍 Key Power Query & Accounting Concepts:

Cleaning GL data for reporting (without changing source data)

Separating operational data from reporting logic

Handling retained earnings and closing entries correctly

Using Power Query to enforce accounting logic over time

By the end of this video, you’ll have:
✅ A General Ledger that supports monthly Balance Sheet reporting
✅ Accurate retained earnings movement across periods
✅ A data model that works for both P&L and Balance Sheet analysis

This step is essential for anyone building financial dashboards, management reports, or BI solutions on top of accounting data.

This tutorial is part of a collaboration with ‪@FinexSkillsHub‬ , one of Ghana’s leading data analytics training hubs, and belongs to the AppSheet & More series — focused on real-world accounting, Power Query, and Excel analytics.

📌 Next up: using this corrected data model to build accurate Balance Sheet and P&L reports in Excel — stay tuned.

🔎 Power Query & Accounting SEO Keywords (Tags)

Add these to your YouTube tags:

power query accounting

excel balance sheet automation

power query gl cleanup

general ledger excel

retained earnings excel

closing entries accounting

monthly balance sheet excel

power query date transformation

financial reporting in excel

accounting dashboard excel