Dashboards are much more exciting than an income statement... let's be honest.
Excitement aside though, they are much easier for clients to quickly understand than an income statement. Highlighting the major items that need to be discussed, putting them into visuals, being able to go to a shared link to view them - it's all much simpler and promotes significantly better conversation.
If you are a CFO reading this or someone considering a CFO-esque style dashboard, these all apply to you both, I promise!
If you've read my previous article on creating a KPI dashboard in Excel, you may remember some of the main benefits of "dashboard-ing" your results, but here is a quick refresher. Keep reading to learn the options you have when it comes to your CFO KPI Dashboard.
Benefits of a CFO KPI Dashboard
Some main benefits of a CFO KPI dashboard (in Excel or Web-based for that matter) include the following:
Conveying large amounts of important financial info in a very digestible way
Significantly better conversations with clients or team members because they will be based in data and everyone will be on the same page
Quick identification of positives and negatives in financial performance
Allow for deeper analysis in problem areas (or successful areas for that matter!)
All in all, dashboards are the most effective way to consume financial data whether you like looking deep into financial data... or would rather watch paint dry.
Creating a Web-based CFO KPI Dashboard
The overview process here is quite simple - get the data out of your accounting software, into a database of some sort, and create a dashboard from it. Sounds simple enough, right?
Let's go step by step...
Getting data out of your accounting software
We'll use QuickBooks Online as the example here, but know that most accounting software will work similarly.
Two main options:
Export a CSV (the classic default method)
Use a third-party online program
1. Export a CSV
Tried and true. We've all done it before and we'll all continue to do it in the future for a lot of analysis.
Since we're talking QuickBooks data, and specifically income statements, this can be found in Reports / Profit and Loss by Month. Then, in the upper right of the report, you can export into a CSV and mess around with it in Excel if you want.
That's it for the step of getting the data out of QuickBooks though! Again, I haven't met an accounting software that doesn't have a CSV export capability.
Pro: very simple to do and free
Con: not automated and takes a little extra massaging to get into a dashboard.
2. Use a third-party online program
A slightly more sophisticated method that has its own pros and cons.
Sticking with QuickBooks Online, I'm a huge fan of using a tool called Coupler (referral link here). It has a lot of default report pull options that will be sufficient for most standard data needs out of QuickBooks.
They do a great job of walking you through how to use the tool and set up integrations here, so I encourage you to check that out. The overview is: link your account (you will need Admin rights), choose where to send the report you want (Google Sheets, Excel, BigQuery), and how often you want the report to run.
Once you have that set up, then you've done step 1 and the data is out of QuickBooks and into something you can manipulate!
Another good third-party option is Zapier which works somewhat similarly but is less report-focused and more individual transaction-focused.
QuickBooks Online is very good with linking up with third-party options, and you can find more options than those two I'm sure. QuickBooks Desktop on the other hand is not, unfortunately. Other accounting software are hit and miss, but some major ones like Xero and Odoo generally have some options. Just google "[accounting software name] API integrations" and you should find some additional choices. They may require a bit more sophistication like an ODBC connection though.
Pro: automated data pulls
Con: may not be free and takes slightly more practice to get working
Get your accounting data into a database
If you went the CSV export route, no worries - there will just be a bit more manipulation.
If you went the automated route - there will still be some manipulation most likely.
The goal here is to transform the data into "database format" which is what you need to create a pivot table. The picture below shows the comparison between the two.
Database format basically means that each unique metric and qualifier (like "Month") needs its own column, or must have all the options listed in the same column (like "Account" above).
Example... all of your financial accounts should be in one column. All of your timeframes should be in their unique column. All of your customer names should be in one unique column. All of your dollar amounts should be in one column. Every unique piece of the data should be in its own unique column!
Again, if you're familiar with the data you need to make a pivot table, it's very much the same!
You may need "helper columns" as I call them though. These are columns that you create in order to have something specific show in your dashboard. This step is needed regardless of what data route you chose (CSV or Automated).
For example, if it's complex to get Revenue from the way the P&L comes in, you may want to make a column with a formula to pull the "Amount" number if the "Account" is a Revenue account.
Keep in mind, this is database format - not necessarily the "scary" database. This can absolutely be done in Excel and Google Sheets - and oftentimes, it's easiest to do there, honestly!
So now you've got your data out and into the right format, let's make some visuals!
Creating your KPI dashboard
This is the fun part!
The first two steps are crucial but can feel like a necessary evil. This is the step where we now get to see our results!
Regardless of how you got the data out of your accounting system, this step works similarly, but it will be based on where your database lives.
If your database is in Google Sheets, I'd recommend using Google Looker (Data) Studio. It's totally free and very user-friendly! Big fan, honestly.
If your database is in Excel, I'd recommend using PowerBI. It's very much NOT free, but is more powerful than Google's version.
Quick comparison...
Google Looker Studio
Free
Highly user-friendly and intuitive
Integrates incredibly well with Google products
Integrates well enough with non-Google products as long as you have a way to get the data into a database that Google can talk to easily (BigQuery or Sheets would be the ideal two)
Can do the majority of what the average user needs
Microsoft PowerBI
Not free - multiple license options
Medium user-friendly and intuitiveness
Integrates with a LOT of different data and database options
Can do the majority of what an expert user needs
Creating a CFO KPI Dashboard in Google Looker Studio
Big step one is telling the program where to get the data. In the upper right, click "Add Data".
Select the Google Sheets (or other) file where you have your data. Any header (row 1) will be treated as either a unique dimension or a metric and will show up on the right in the data panel!
From this data panel, you can start by clicking and dragging a metric onto the blank canvas. It will show up in a visual and then you can change that visual in the data panel to the following (and more):
Scorecard: single metric pop-out - good for highlighting a main KPI
Column chart - good for trend graphs
Bar chart - good for things like revenue source breakdowns
Those are probably the big three, but there are plenty more!
Everything from here on out is just clicking and dragging!
You can customize visuals, colors, metrics, add formulas (bottom of the data panel), and with a little practice, you'll find that it's pretty reasonable!
This dashboard will naturally be online since it's a web-based program, so all you have to do is share the report like you'd do any Google product, and now you've got a web-based dashboard!
Creating a CFO KPI Dashboard in PowerBI
PowerBI is very similar in theory to Google Looker Studio, but isn't quite as obvious when making visuals.
First, again, grab some data! On the Home tab, click Get data as shown below.
Much like Google, PowerBI will take the first row as the different metrics and dimensions.
Now that your data is in, it makes sense to pick the visual you want and then click and drag the data you want into the pre-determined sections that PowerBI creates for that particular visual.
Again, from here, it's just clicking and dragging, and you can customize almost anything you want, just like we talked about above!
To make this web-based (since PowerBI is predominantly a desktop app), just go to Home / Publish, follow the prompts and you're just about good to go!
Last thing is making sure your data auto-refreshes. That is done by going to the published report online, Data Hub on the left rail, ellipses of the data set you just published, Settings, and following what's needed for a Scheduled Refresh!
CFO KPI Web-based dashboard summary
Don't underestimate the importance of getting the data into database format! It'll make your life a lot easier when you're building the dashboard itself!
I know this was just an overview, but those truly are the three main steps! Get the data, put it into the right format, click and drag for some visuals. Not that daunting, right??
Obviously, there can be some nooks and crannies along the way, but if I detailed all of them, this would be an infinitely long article...
With a little bit of patience and a clear mind, you can create dashboards that are entirely functional with relative ease. And once you do it the first time, it only gets easier!
Get A Custom CPI KPI Dashboard
I hope this overview was helpful, but I am always happy to answer and specific questions if you have any! Feel free to reach out!
A note on our CFO Dashboard services... we love them! Can't get enough of them and have a whole CFO service page dedicated to them here. Check it out - would love to talk more!
Learn more about our custom dashboards:
Other Dashboards:
תגובות