CFO site page: https://www.pineapplecf.com/cfo-dashboards
Fractional Analyst site page: https://www.pineapplecf.com/fractional-analyst Book a consultation: https://www.pineapplecf.com/contact
[Video Transcript]
Adding a forecast to a dashboard can be pretty difficult, but we are going to walk you through an example of how to do it right now!
Hey y'all! I'm Jack Tompkins with Pineapple Consulting Firm, always trying to help the small business world become more data-driven. Today we are talking forecasts. We are looking into the future and getting that into a dashboard. The goal is an actual vs budgeted forecast and a variance there, which everyone wants. Every CFO specifically drives conversations with that info. It's super important!
If you watched our last video, we did a little tutorial on how to make a CFO style dashboard, at least a baseline functional one. Today we are going to use those same elements and incorporate a forecast on top of it. That way you can at least see the base elements or base functionality of how to incorporate a forecast into your actuals, giving you the full all-inclusive and forward-looking dashboard.
Let's jump back into the dashboard. So here's the dashboard that we referenced as sort of the end goal. We can easily incorporate forecasting into this as well but we chunk this particular step out in a previous video. It shows just some baseline elements and obviously this is a whole lot uglier than our typical dashboard, but it is the baseline elements.
Now we're going to go one layer deeper and do just a single visual to start and we're going to incorporate some forecast! So within our backend data, we've got actuals coming in from Coupler and those come in into these black columns. Then these blue columns are our "helper columns" that we use for the dashboard. So we've got our actuals here for our forecasting and we know that forecasting can be a months long process. There's a whole lot of different variables in there and if you're a CFO listening to this, I'm sure that you're a better forecaster than I am - so we won't go into the details of how to create the forecast.
I do want to stress the end result format of the forecast though. We've got a very very simple structure here. It's just a monthly forecast, revenue, expense, profit, you could do this by rep, by territory, by product, all that good stuff, but make sure to keep it in this sort of database format.
Okay, what we're going to do next is incorporate this into the dashboard. If we jump back here, we've got our revenue trend. Nice and easy, fairly simple right? What we're going to do is even though our actuals are in this database tab and the forecast is just in the same file, we do have to treat that as a different data source.
What we're going to do is, in "resources", we're going to "manage added data sources". We've got our existing one, nice and easy - it's the database one. We're going to duplicate it so that way we can save some time and it'll just link us back up to the same sheet and all that good stuff. We're going to pick the forecast sheet and then we're going to reconnect. It's going to say holy crap, so many things are different. It's okay, we expect it to be different. These are fields that didn't exist in the previous one, fields that are missing from the current one that weren't in the previous one, that's all fine. we're just going to hit apply.
Now you can see our list of fields here and I always recommend changing this name, and actually maybe we'll just change this entire name to be forecast. Hit done and we can close out. Now we're going to incorporate that into this view. We've got our database as the data source for this, what we're going to do is blend the data. Now blending the data is a joining of the data in some fashion. You can include all the metrics that you need in here, all the different dimensions, again we're doing a simplified streamlined version of this, so we're going to add a data source. It's going to be the forecast one that we just created. We're going to drag and drop the revenue forecast and then make sure that there's a date range control on both as well.
We're going to make year/month the date range and then we can do date or year/month as the date range there. Now to configure that join it normally has a pretty good suggestion, in this case, our field names are the exact same so it picks that up. If they're not you could pick another dimension that you include in the list. So we'll save that. We'll name this, instead of "blended data", we'll go actual vs forecast. We're going to save that.
Now it should look the exact same with the exclusion of nulls because nulls don't exist in our forecast, which works out nicely for us. All we're going to do is just click and drag revenue into this second spot here. Great, we've got our two different bar graphs going. You'll notice there are a bunch of lines though. We've got one axis and we've got a second axis, we need to change those. It also changed the Sorting on us but we can figure all that out.
For starters, we want the same axis or at least the same values. So in the style section, if we scroll down a little bit to "axes", we're going to have just a single axis. Looks a lot better already. Now we're going to re-sort. We've got year/month and we're going to sort that ascending which is how you would normally look at things and we're going to make sure to include a couple more months so we get at least the full year of 2024.
There we go! You could change up the formatting a little bit but you can see now we've got our revenue in here and our revenue forecast color there. You can change the colors of course as well.
One other calculation that we're going to make is just the actual variance number. We're going to make a scorecard and put that right here. It defaulted since this is our last data source. We're still in that actual vs forecast which is good. You'll notice that we can't create a field, unfortunately in blended data, it doesn't apply to the data pane. It does exist though, If you click on the metric and then you can add a field within here. Let's just call it variance. We're going to have it be a percent, sure percent one, doesn't matter, that means there's going to be one decimal place. All we're going to do here is just regular old revenue divided by our revenue forecast minus one. You'll get a little green check mark, which means we've got a good formula.
Now you'll see this change for whatever time frame that we're picking. We apparently did great versus our budget versus our forecast. What we need to do is make sure that it is controlled by a date so much like our last video, we're going to add in a date range here and we want to make sure the trend graph stays but this is impacted. So we're going to default this date range to last month so it'll be February, hit apply there, good. So this doesn't change which is what we want, and this minus 42% (we got 10,000 in actual and 18 in forecast) that feels about it right to me.
You can always conditional format this too to be red/yellow/green but our trend graph stayed and it has the full year in there, we have the control in there to control the date and we've got our variance calculation there.
This is now a somewhat functional actual vs forecast dashboard for CFOs! There we go. Not too bad right? Pretty straightforward. Again that's just the building blocks. There's a whole lot more that can come from that and I know that there are a lot of different variables and a lot of different dimensions that you may want to include in a forecast but those are the inner workings of the foundational pieces of getting a forecast into Looker Studio.
We do this stuff all the time. We love working with clients on forecasting because as we all know, financials are typically a lagging metric. We want to look a little bit into the future and that's always a really really fun conversation. So please feel free to reach out! We love building these dashboards, especially in Looker Studio and especially for CFOs or CFO style dashboards. Don't hesitate to give us a shout! Talk soon!
Learn more about our custom dashboards:
Other Dashboards:
Comments