Budget Dashboard

Post date: Mar 19, 2020 6:33:01 PM

Have a peek into my work. The link at bottom is to the user documentation for a budget dashboard I recently created.

I create this because the company I worked for in 2019 was sold. As is often the case, the acquiring company is larger and already had an IT department from which I would take orders. I prefer being in charge so I accepted a head-of-IT position closer to my wife's family who needed our help.

One of the first things new department heads need to know is how much money we have to spend. So I asked my new employer's CFO for access to my budget. I was handed a printed report from last month. This wasn't what I hoped for, Real-time, interactive access to current year budgets, current period and cumulative spend, with last year's actuals wasn't available. So I created it in Excel. Other department heads saw it and loved it so I made it available to everyone.

Dashboards are easy. The prototype took less than 30 minutes to complete. It had these basic components:

  1. Data - We can pull data from reports or download it from systems. My prototype used PowerQuery but my final version relied on VBA, SQL, and ADO because I have those skills and its faster.

  2. How to get data using parameters with PowerQuery: https://www.youtube.com/watch?v=P9cUYpXIKsU

  3. How to use VBA, SQL and ADO https://sites.google.com/site/beyondexcel/project-updates/introtoado

  4. PivotTables - We can easily summarize data using PivotTables. PivotTables also support drilldown.

  5. PivotCharts - Every dashboard uses charts because they provide at-a-glance understanding of basic conditions like: Are we over spent? Where are we spending the most? Which account has available funds? Will I run out of money? etc.

  6. Slicers - In very little time we see an account or two that we must understand better. Slicers help us focus on problem areas and turn dashboards into interactive analysis tools.

My final version added some nice-to-have options enabled by VBA such as:

    • A ribbon interface

    • Click-to-Drilldown Pivot Charts

    • Click-to-Expand-to-Fullscreen/Restore-position charts and slicers

    • And more..

Excel is great for dashboards. Power BI is better but that's another topic.

Budget Dashboard User Instructions: https://www.dropbox.com/s/yt3gtaxwvhrudv6/Budget%20Dashboard.pdf?dl=0