Simple and customizable financial dashboards for Small Business owners, built in Excel!
For a written "how to" overview in creating something like this, check out our blog about it! https://www.pineapplecf.com/post/financial-dashboards-in-excel
[Video Transcript]
Hey y'all, what's going on, Jack Tomkins with pineapple consulting firm. We're helping you become data-driven and today we're doing that in the form of financial dashboards in Excel. Excel is everybody's favorite tool right? Okay it's just my favorite tool, but it is very impactful and a lot of people know how to use at least the basics of it. So we're going to dive into how to make a financial dashboard within Excel.
We can do automated features. We can make it visual. We can make it very easy for clients to understand and internally for your own business to understand too. Let's dive in and show how to make a financial dashboard in Excel! All right, so here we are in Excel. I really am a big fan of excel because it can give you the full picture and it's also very customizable. I mean just looking at this, it kind of doesn't feel like Excel anymore right? Especially if we hide this top ribbon. If you wanted to show your client or even look as if it's just your own dashboard, if you were to hide these column letters and row numbers, it really doesn't feel like Excel anymore right? It can take that transformation, which is kind of important because I'm a big fan of making things very visually appealing. Excel does not have that reputation, but if you make a dashboard that looks something along these lines or however you want to make it, as long as it doesn't feel like Excel, it's actually a lot more enticing. So know that that's possible. It's also very customizable. A great thing about Excel is that you can put any formula in any cell. Of course you all know this, you're all Excel fanatics, I hope, or if not, hopefully you are familiar enough with Excel. You can do anything in any cell and I want to reiterate that because it is very important and it is not something that you can do in any web-based platform. Excel does still have a lot of pros in its column but there are certainly cons. One common myth is that you can't automate data into Excel. Now that's not true.
I'm going to jump over here to this data. This is actually QuickBooks online data. It's of course fake data that I've manipulated a little bit. Everything in black text here can come directly from QuickBooks online via a program called coupler.io. You can automate this data directly into Excel. You can do the same thing in Google Sheets or you could put it into Google's database software program called BigQuery. Then within Excel, you can use a program called PowerPivot up here. I won't go into that but it is an option to grab that data automatically from BigQuery and bring it into Excel. So all of this can be automated in a fairly easy way. Now everything off to the right, these blue columns, these are what I like to call helper columns. This is great, we're getting the data in here. Now let's make sure we have some columns in the format that we want them so that we can easily analyze or visualize and manipulate the data in the way that we want to.
All of this can be done again with relative ease. That's important to feed the dashboard and make it look like whatever your business goals are. I always say make sure the dashboard is tied to a business goal. Whatever your business goals are, as long as the data exists somewhere in that data pull from QuicksBooks online or whatever your accounting software is, if you're making a financial dashboard; as long as it's in there, you can make a dashboard from it. So for this dashboard, the overall goal is, are we bringing on clients profitably. That's why I've got this cost per acquisition trend as a big, sort of highlighted, trend graph. We've got some per employee metrics to make sure that in a very rudimentary analysis, we can answer that question. This section here, "revenue per employee" is for whatever month this is, $26,000 and the overhead is only $20,000, so in theory, we are making plenty of profit on an employee level.
So we're bringing in customers profitably and all the employees are contributing and we're profitable at an employee level. This would obviously change or differ based on your own goals or your clients goals but everything here can be made from the QuickBooks online data. You could even include projections if you want. I have some very rudimentary projections in here of just "revenue by month". You can make this for any account that you want and then you can bring that all into a dashboard with the help of pivot tables. If you're not familiar with them or have no experience with them, I promise they get very easy very quickly. All of this data that is shown here is directly from multiple pivot tables. All the same metrics though.
I can't stress enough visualizing your data, tying it to a business goal, and I'm still a big fan of excel. Now there are plenty of web-based options you can do this with but they are not quite as customizable. You can get a very similar level of automation with Excel even if it's just the ultimate backstop to everything of doing a CSV download. That's still pretty quick. It's not automated but you can update all of your data within two or three minutes, so don't discount Excel. It's still totally usable, totally user friendly, and it's probably something you already have on your computer, so essentially free. This is an important tool. You can get a lot of good information for your business or your clients businesses just from Excel just from something like a QuickBooks Online income statement. I hope this makes sense, if it doesn't, or even if it does, please let me know. Reach out with any questions, comments, or concerns. I've been Jack Tompkins with Pineapple Consulting Firm and I love helping folks become more data driven. Thanks so much everyone!
Take Your Small Business to the Next Level
Thank you so much for watching our video on Small Business Financial Dashboards. Are you ready to take your Small business to the next level? Check out our financial dashboards to help you package your most important financial data into a user-friendly, easy to understand dashboard. If you need a custom solution or have questions, do not hesitate to send us a message! Thanks again for visiting Pineapple Consulting Firm.
Learn more about our custom dashboards:
Other Dashboards: