For a "how to" overview of creating something like this, check out our blog about it! https://www.pineapplecf.com/post/cfo-kpi-dashboards-overview-how-to-create-one
Link to our original CFO Dashboard Excel example: https://youtu.be/o62AFrQ7is8
Want to learn in more detail how to create a dashboard from start to finish? Check out our online course!
For more examples and details, feel free to check out Pineapple Consulting Firm at www.PineappleCF.com!
Book a consultation: https://www.pineapplecf.com/contact
[Video Transcript]
What's up y'all! If you're looking for a CFO level dashboard and you're looking to build it in Excel (there are reasons for that I promise) here's an outline you can
follow!
What's going on everybody, I'm Jack Tompkins with Pineapple Consulting Firm, and we are always trying to help the small business world become more data-driven. If you are a CFO of a small business or a fractional CFO or just the finance person of a startup and you want to build some sort of projection or dashboard so that you can manage your finances and manage towards your budget and all that other stuff, we have got you covered! We're going to give you an outline that keeps things easy to use, easy to update, and easy to understand. Okay, there's the big three. I'm going to say them again plenty of times I'm sure but first and foremost, why the heck would we even use Excel in this age of AI?
Why not use something else that's out there right? The quick use case for Excel, nothing against AI, but we get a lot of CFOs that we talk to and a lot of business owners that still like Excel or Google Sheets. (We're working on that video and we'll publish the link when it's ready.) But the reason for Excel is its ability for constant customization. We have a lot of tools that deal with automation such as an automated QuickBooks dashboard and all sorts of similar stuff.
There is nothing more customizable than Excel. It does require some manual effort, absolutely, but based on a lot of conversations with people, that's what they're looking for. They want just a little bit of manual effort. You know, it's still easy to update, still all that stuff, it's just not completely automated. Being able to change forecasting, being able to do things on the fly, and having some skin in the game is what it comes down to. If you have to do that little bit of manual work, it keeps it interesting. It makes you kind of play the what-if game and see the results of your actions as you're doing it in real-time. The updating is relatively easy and that's why there is still a use case for Excel for anything in this CFO world.
Again though, it's important that it needs to be easy to understand (for you, for your team, or for your clients), it needs to be easy to update, and needs to be easy to use (ex. changing inputs or something like that). We're going to jump through an example, give you an outline, and a few steps to follow, and walk through a relatively complex-looking example that is actually fairly straightforward behind the scenes. Let's dive in!
We're going to end up making a dashboard that looks like this. We've got all the main KPIs up here or at least some main KPIs up here. We've got some revenue breakdowns, some scenarios, some trends, and all sorts of good stuff, and as always we have a date selector. You can get fancy with what kind of dates you want to actually see in the dashboard but before we get to the projections, we need to start with step one.
Step one is the data intake. Now this will look very familiar. This is directly from QuickBooks. This is QuickBooks Online but the desktop version is very similar. Xero is also very similar, and really any accounting software that you use should have something like this. All you have to do is create a consistent repeatable data intake.
This is profit and loss by month for about two years and all we do is paste values into cell A1 and then we have a macro that runs and puts it into database format. The importance of this is that, we know that this download comes in a very consistent format from QuickBooks or whatever accounting software you use. Next, putting it into this database format, which I'll walk through in a second, keeps things very repeatable and very easy to do a sum-if formula from or do a pivot table from, whatever you want to summarize your information from.
This is database format. You'll notice over here, I won't highlight it all but this is our profit and loss statement. These are the accounts that we had on this sheet, they just get transferred over here. We then tag it with a date and we carry over the amount. These three are formulas that just trim the accounts up because you'll notice that a lot of them start with this little buffer in the beginning and that can be kind of annoying. Then we mess with the date so we have some flexibility there.
If I scroll down you'll see, that once we get to February, it is the same income statement. We just hit it with a new date, we grab the new fields, etc. This is something that you can build a macro for. That's our preferred way of doing this. You could use Chat GPT to write a macro for you. It'll get you most of the way there. The other option is actually to go online and use a tool called Coupler. Couple with an "r" at the end. We'll post our affiliate link below in the description. We use that for an automated version of this. Let me show you that quickly.
This is Coupler's homepage. I am just going to go super quick here. You can log into your account. then you can add an importer. What you can essentially do is select QuickBooks reports, that's where the profit and loss statement comes from. I'm going to dump it into Excel. You could do Google Sheets, you could do any of these different options here. They do have some templates that are in Looker Studio (we do that as well) and you can just set up this automated data pull. You can go through and set it up from scratch here, you set a time frame, you set a destination, all that stuff, and it comes through at the transaction level. Or you can do it at the summary level as well.
Pretty straightforward once you get in here. This is a great way to automate this data pull if you don't want to do the quick CSV download from QuickBooks or something like that. So that is step one. Taking the data and making sure that it is consistent and very repeatable.
Now we're on to step two, getting some detailed historical trends. We are back in Excel here, looking at a fairly detailed level and getting some historical trends. So how we do this is in our detailed view tab. It pulls everything from this P&L summary. We have some pivot tables that are off to the right here that are intentionally hidden. This is so our clients can't mess with it, candidly, and it pulls everything into this detailed view.
You'll notice that this is one formula. We are just doing a "get pivot data" formula and it goes across all of these different dates and if I scroll, we are always up to date with the current month. This current month will basically look up the maximum date and say, "Okay, that's where we are, this date will be that date minus 1 month, this date will be that date plus one month".
This is part of the easy-to-update thing. Always make sure that you have a flexible date for the current month. Then, all of these other formulas are the same down here for all the different expenses. That way we can see this quick little 12-month trend. We also have projections that in the next step we go through. These historical trends are important to see though, especially if you are in that finance seat and you need to see exactly what something was in a very easy-to-understand way. We recommend doing something like this.
Even more importantly, back in the dashboard tab, we want to make sure that we have some historical trends that are visual too. This, you can hover and see what the value was. It takes a little bit of effort and Excel can be kind of funky with this. This has our historical trends for the two things that we've selected in this example for this fake company: revenue and the ending cash position. Make sure that this date, the current date, is always flexing with the most up-to-date data, and make sure that you have some detailed options along with some visual options for historical trends. That is it for step two. On to step three.
Step three is to create inputs for projections. We want to make projections and we want easy inputs for it. We know that we've got this easy to update so now all we're doing is downloading a CSV. We're using some automated data through Coupler. We've got some historical trends. Now we want to scroll this way and be able to make some projections. All of these things in blue are future-looking at this point in time. How do we do that? We have an input dashboard.
You want to make this as easy to use as possible. We've got the easy to update part. This is the easy to use part. We want this to be. "Hey, instead of 15% growth, I think we're going to have 20% growth here." Great, so instead we've got our low, medium, high, or bear, assumed, bull scenarios. If we adjusted that organic growth whatever that category is up to 20%, then maybe our aggressive scenario or our bull scenario is 30%. You can see that anything in this sort of light yellow area is an adjustable thing, and all of the inputs are held here. Just a quick rundown we've got some owner salary that comes through, some averages there. We've got some Equity transactions over here. We can get into all that, but the biggest thing is "What is our projection home base?" That is the input dashboard here.
In each of those three scenarios, you can choose the type of change or the type of forecast that you're doing. You can make the year-over-year growth whatever you want it to be there and it can be effective in a specific date into the future. Same thing on the expense side. We want to make sure that everything is held in this one spot so that when we go over here, everything gets impacted by our selection.
Now this is a crazy formula, don't worry about it. I'll show you even the back end here. We've got our bear scenario looking forward, our assumed scenario, and our bull scenario looking forward. All it does is say, depending on what you select here, (and notice if we change this to bull that moves there) whatever you select here will control everything else. That control impacts this detailed view and it also impacts these scenarios here that are forward-looking and this projection scenario as well. Without going into all of the nitty-gritty back end of this, you always want to make sure that your inputs are held in one place and that any projections that you have don't have any hard coding. They are just based on the inputs that are changed in this one section to create your projections. That's it for step three. Make sure it's centralized.
On to step four. Now we get to create the pretty picture. Step four is create the
dashboard. We are back here where we started. We've got our main KPIs up here. We've got all these trends and all this other stuff. How the heck do we make all of this and most importantly make it flexible as well? All of this comes from just a few places. Really just one place; this detailed view that we looked at. The reason why it was so important to create in the beginning is because we're going to pull a lot of information from it. It has everything nicely detailed out for us.
I will scroll down real quick. We have a lot of expenses so I'm going to hide all the individual expenses. Then we've grouped things into these five overarching buckets of expenses. You could see here that this is a lot cleaner to look at. So we've got our revenue up here. We've got our expenses here. We've got whatever impacts that aren't captured in the P&L, like equity, transactions, or owner draws, and then that results in our end income. This is still our P&L statement, just a little bit more cleaned up with projections as well.
Everything in here will come just from a table that we create over here. If I scroll way over here, it's not the prettiest thing but we've got this. It is always important to have some dates that you can flex between depending on how the dates are input. We've got the current year, which we pull from the most current date and then we just list out January to December for each of those years. We hit that with a date number as well, just with the way that the dates come in. For the trend graphs, we just made some simple things here. These are directly pulled from the detailed view as you can see and it just lists things out as actuals or projections.
You'll see there that we've got this revenue trend. January to January and then February ongoing. The actuals end for our current month and the projections start for our next month. You can see how that looks over here when we look at this graph. You'll notice that these are two different bars. This is an actual bar and if we could highlight it, we could see that the actuals are zero for these future months. Obviously, since they're zero, we can't quite see them. So the only thing that's left is the projection. We just color it so that it's a little bit different, so you can tell that we're projecting forward.
Same thing in this ending cache position too. This is essentially "how do we get this information into a summarized view like this?" Well, we just create some summary tables over here. All of this is 100% automated because it's all formula-driven. We've got our different projection graphs here. It works the exact same way as the trend graphs that we were just looking at. All of these go right to the detailed view so we don't even create a middleman here. We just go right to the detailed view and everything is going to be based on this date selector. We compare it to the prior year but you could throw in a budget here. You could throw the prior month in here, or anything like that. We've just defaulted to the prior year. This is all just straight from the detailed tab.
To create this visual summary, this is the easy-to-understand part. This is the part that you need your clients, your team, your leadership, and whomever to be able to easily understand. "Where are we currently? How are we doing and where are we looking in the future?" If you have this date selector and you have those projections and you have the current state, you can easily get the financial health of your entire company in a matter of really 15 to 30 seconds once it's all built. All you need to do is look at this. "We're down here, why is revenue down here? Well, we know from conversations that product income was a little bit down this month but our profit was still killing it so we're doing great there" etc. So all in all, you're doing okay and looking positive in the future. Easy. Done. There we go.
That is the dashboard part of the easy to understand part of the big three: easy to update, easy to use, and easy to understand. Now we're out of Excel quick for step five. Step five is the last step here and it is just the conversation with your client or your leadership team. This is the important part, making it a financially data-driven conversation is huge. Having a tool like this that has projections that has the easy to view dashboard, easy to understand, all that stuff, it changes the conversation. It allows you to be financially savvy even if you're not.
We highly recommend using something like this, whether it's in Excel or not, doesn't really matter. We've got QuickBooks dashboards out there that are fully automated. We've got all sorts of things but we highly recommend using something like this because it keeps your team accountable. It allows for changes on the fly once you build it and it is very easy to use. You don't have to build it as fancy as we just walked through but those are the overarching steps. Please feel free to reach out if you have any questions, comments, or concerns. We are more than happy to help as always. Keep being data-driven, everybody! We'll talk soon!
Learn more about our custom dashboards:
Other Dashboards:
Comments