Custom financial dashboards - WAY better than QuickBooks P&L reports!
Steps mentioned in the video:
1) Use Coupler to get your data out of QuickBooks Coupler link: https://app.coupler.io/register/sign_up?referral_code=Pineapplecf
2) Get the data into database format with some helper columns
3) Load into Google Looker Studio and create the visuals that are important to you! Much more on our website here: https://www.pineapplecf.com/cfo-dashboards
What's going on CFOs and folks looking for CFO-esque or financial dashboards? So a very common thing people are used to seeing is QuickBooks. But they want to see it in a more visual way so that it's actually usable as opposed to a P&L. So this is a quick overview of how to make something like that (a financial dashboard) with some tools that I use all the time for my clients and some common features for CFO style or financial dashboards. Let's Dive in! Okay so you're probably used to seeing something like this right? This is QuickBooks and this is their sample company and a monthly P&L. Nothing super exciting, but there's a lot of good information in here. Getting this out of QuickBooks and into something much more usable and much more user friendly is a bit of an art. But this fairly easily can actually turn into something that looks like this. We'll get into all the "how" of that and the "why" some tools are used and stuff like that but first, to get to that, we do of course have to start with QuickBooks.
I'm going to go through a program, two programs, one is a data transfer one, it's called Coupler.io. There's a link in the bottom of the description of this video for Coupler. They do a great job of taking things from QuickBooks into a lot of different sources. I'm going to be using Google Sheets. You can also use it for Google BigQuery. You can use it for Excel and lots of other things but this is sort of the quick and easy way in some cases. The other tool I'm going to be using is Google Looker Studio, which used to be called Google Data Studio, if that name rings more of a bell. It is just an online web-based/ cloud-based dashboarding software from Google. So this monthly P&L is great right? It has all the insights that we need, we just have to get it out of here and into something that looks much more usable and much more manageable.
So Coupler, this is sort of like their home page. They can take the data from QuickBooks. You just log in and they dump it into Google Sheets and it'll come in looking something like this. I've cleaned up a lot of this data and made it fake data and all that good stuff but it'll come in kind of looking like an income statement. That's because it is an income statement. You've got dates. You've got transaction types. You've got whatever, invoice numbers. This is like a client name or who's buying it, whatever description is in there. The amount is kind of the big column here and then it starts off with revenue or income as they call it and it goes through all the different revenue sources. I'm going to jump back up to the top, but it goes through all the expense categories and numbers too. So that's step one. That's actually an automated pull from QuickBooks through Coupler into Google Sheets. Pretty easy to set up honestly. Coupler does a really good job of walking you through how to do that.
Next is making it into what I call database format. So this is great, it comes in a pretty good format honestly. There are some bells and whistles that I always put in to make it into this correct format and it kind of looks like this. I've got a bunch of columns over here that are in blue. That is what I call helper columns. So from that database format now we can upload it into something that looks like this CFO Financial dashboard. Within here, I'm going to make this larger. Let's go into presentation mode and again this is Google Looker Studio (used to be called Google Data Studio). It is just their web-based platform for making dashboards. Everything in here comes from that export. This is the one that does require some manual or human intervention just basically tagging where different leads came from. You could technically do that in QuickBooks. It's sometimes just much easier to say "customer A came from a referral". Great let's just log it there, then use that as a helper column. Like I said, everything in here is super super useful.
Right so you've got this whole date selector up here. We've got the month of October 2022 selected and then you've got some main KPIs for the selected month, revenue expense and net income. Obviously nothing groundbreaking there. You can get into whatever detail you want but then also some year-to-date info. Again the budget you could do in QuickBooks but it is sometimes easier to do outside of QuickBooks because you're typically making it outside of QuickBooks. Obviously you can easily loop that in. I'm a big fan of trends, so again nothing too crazy here. You could really trend whatever you want because it does come in with some really good dates but revenue and net income versus that budget. Right so if you hover over any specific month it looks like we fell a few grand short of budget in February 2022. Then March, we were a few grand above budget in 2022. So it's a really good visual representation of how you're doing over time. Especially if you have that budget comparison, which I know with a lot of financial dashboards, is kind of a necessity.
So top clients this month. Now you can switch this from clients to products. This is currently products, this name could change as well. I’m putting those both in here not as an error but to show that you could do clients, you could do products, either way, the data comes in pretty nicely. Then top expenses too. Just what does that breakdown look like? You could have this comparing two different months or you could have any of this comparing two different months, or versus a year to date percentage kind of thing. You can get really creative with this data and this is just a one page, pretty simple dashboard but it gets the point across. It is very very impactful and if you are looking for that CFO knowledge or that CFO insight, this isn't going to be the entire thing but it's definitely going to put you a couple steps in the right direction. It's close enough to getting that full financial picture of your business. Seeing what's working, seeing what's not working, and then identifying some trends. Some things you might kind of have a gut feeling about but the data will back it up hopefully. If not, it'll help point you in the right direction.
So quick recap. From QuickBooks we go into this, what I call a P&L import. Here it is an automated data pull from Coupler that dumps into here. Make some helper columns and put everything into what I call database format and then link that up with Google Looker Studio. Add that as a data source and you can create all these visuals and make this entire process a whole lot easier. Better than checking out each individual month within a P&L and looking by month at trend C, then what was July versus June? Oh man what's that percentage? Do we care about all this stuff? Pick your favorite KPIs. Pick the things that you really need to watch in your business and you need to see grow or change. Get everything into that database format through Coupler and then make something that looks like this. It'll completely change the way you look at financials. I almost never look at my QuickBooks anymore. I know this is just intentionally an overview because there's a lot of nitty gritty steps in between, but if you have any questions on this, reach out. I am more than happy to talk, share some insight, get you signed up with Coupler or any of the other tools that are good out there. Please feel free to reach out. My name is Jack and as always, thank you so much for watching everybody.
Take Your Business to the Next Level
Thank you so much for watching our video on Custom CFO Dashboards Online Overview - Google Looker Studio. Are you ready to take your business to the next level? Check out our CFO 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: