Excel is any financial expert's good friend, but most people outside of the financial world don't even want to open Excel.
So, we in the financial business have to make things digestible for our clients, in order for Excel to not come off as repulsive. The easiest way to do this is to make everything in dashboard format.
What are Financial Dashboard in Excel?
Financial dashboards in Excel are user-friendly visual representations of financial data ranging from the three statements, to individual product sales, and everything in between. A proper dashboard should be very flexible so that even a user that hates Excel can go in, understand the financial story, and be able to click around to see the information they're looking for.
Excel dashboards should not be a one size fits all. You may pull some similar elements from other dashboards, but at the end of the day, you're making a financial dashboard in Excel because all the templated ones through things like QuickBooks or BI software don't get at what you want to know. Or they're expensive and annoying to use, whereas Excel is essentially free and your home base for analytics (it certainly is mine).
Top 3.5 Benefits of a Financial Dashboard in Excel
As mentioned in the intro, a dashboard view of financials can lead to significantly better conversations because even clients that hate Excel can see the full picture.
1. Clients can see the difference
When a lot of small business owners, or clients in general, are shown a spreadsheet, their eyes start to roll over quickly. Whether you're talking through an income statement or looking at a budget, folks that aren't numbers-oriented will rely on the conversation to see what is happening.
If they look at at dashboard though - with graphs, large font KPIs, and indicators - they can very easily see their performance and they might even be excited to check it out. More examples on our Financial Dashboards page
The conversation you have with your clients then becomes about decisions based off of the numbers, as opposed to explaining the layout of the income statement and finding which numbers to look at.
2. It's completely customized
There are default dashboards in most accounting software options out there, but as anyone looking through them will quickly realize - it doesn't really show what they need.
One of the biggest benefits to Excel itself is a huge plus for financial dashboards too - you can create just about anything you want.
Your clients will love custom metrics in a dashboard that showcases their brand colors and has their budget assumptions on display all in one place.
3. They're pretty quick to update
If your financial dashboard is built thoughtfully, updating it with data will easily take less than 5 minutes. There are programs that can update it automatically too, but for this, we'll focus on the CSV update.
With a regular update process, grabbing a CSV from QuickBooks or somewhere similar becomes very routine very quickly and you can get an update done in under 2 minutes (see here for proof).
It may not be automated, but it's pretty close and it won't be an inconvenience.
3.5 Universal data with CSV exports
Pretty much anything that you or your clients are doing with data can be exported to a CSV. That makes combining financial data from QuickBooks, with CRM data from HubSpot, and time tracking data from Clockify pretty easy to do. Especially with the flexibility of adding in helper columns in Excel like you're probably already used to doing.
Adding in those helper columns isn't something that's easily done when it's automated data or data that goes directly into a database.
How to Build a Financial Dashboard in Excel
If you are a fractional CFO, savvy business coach, or anyone else that has decent Excel experience, you're in a good spot! These few pointers are under the assumption that you have some Excel experience.
1. Spend time on the data intake (Step 1 - Database-ing)
In order for your dashboard to be easy to update (and therefore useable!), incorporating new data has to be a simple process. Typically this means grabbing a standard format CSV and putting it into a "data download" sheet.
From that data sheet, you can either create a macro or have it build onto what will be a database. We won't get into macro building here (although it typically does save a bunch of time).
The goal, though, is to create some sort of a database with both historical and newly downloaded data. This should be columns of the standard fields from the source, calculated columns that help with analysis, along with a whole lot of rows with all of the data you want to potentially see.
Calculated columns could be something as simple as classifying when a data entry has a revenue number or changing a date format into something that Excel recognizes as a date.
Once you've got a process to bring the new data into a "database" in a sheet, half your work is done!
2. Pivot Tables will be your best friend (Step 2 - the intermediary)
And not just pivot tables, but the GETPIVOTDATA() formula, too. Pivot tables are obviously very powerful and can summarize data in incredibly helpful ways. However, they typically aren't very visually appealing and can even be confusing when there isn't good context.
Enter the GETPIVOTDATA() formula. This formula basically says "look in this pivot table and grab me this metric with these different criteria". Pretty helpful when you are searching for a specific KPI for a specific month in a sea of data.
It takes some getting used to, I'll admit, but once you get the hang of it, you can make the formulas very dynamic and actually just write one formula for one KPI and then copy it for the rest and be done!
The Pivot table and the formula end up being the intermediary step - between database and dashboard.
3. Have fun with the dashboard layout (Step 3 - Building the dashboard)
The dashboard is truly a combination of art and science. You've got all the data, now you've got to put it into a format that your client will enjoy - so make it fun! Graphs are fairly easy to incorporate, especially if you're making a Pivot graph that's based off of a Pivot table.
Make sure the KPIs appear to be the health check of the business that they are, too. Big, bold fonts at the top of the sheet will help them stand out. Then you can also add in some quick math to compare to the prior year or prior month.
Horizontal bar graphs deserve to be incorporated too. Easy to make and very powerful - most people these days argue they are much better than a pie chart. They are intended to show ranking and breakdowns (ex. revenue sources).
And it's as easy as that! Kind of... obviously there is a lot that goes into each component of a dashboard, but hopefully this overview gives some helpful tips on the process of building a financial dashboard.
Excel really still is a powerful tool, even with all of the cool BI software out there today. It's going to hard to beat the familiarity and customizability of Excel for a long time.
We offer a lot of services that have a base in Excel... from our fractional analyst offering, to an entire offering on just Excel tools! We love it and are always happy to help however we can!
About Pineapple
Pineapple is a data analytics company ready to help you become data-driven! We help analyze and visualize your data in custom dashboards so you can see your full business performance at a glance, and provide analysis to drive your strategy. Our interactive dashboards will save you time, provide deeper insights & analysis, and help you make better business decisions.
Learn more about our custom dashboards:
Set up a call to learn more about how we can help you!
Commenti