Budget V Actual Quarterly Management Reporting with Power BI

The most common management report I see accountants deliver to clients is a quarterly Budget V Actual Profit & Loss. Check out this video on Intuit #QuickbooksOnline Turn Key solution using Microsoft #PowerBI

Where do you start when implementing BI in your accounting firm? A great first project is to automate your existing management reporting that you are delivering to clients. When you do this, you get great ongoing efficiency gains, integrity on your data and most importantly the clients love the interactive report.

Where do you start in Power BI when implementing it in your accounting firm? What should be your first project? I always encourage the automation of your budgeting process, so you're probably already doing a heap of budgeting for your clients and it's probably being done in Excel. The challenge with doing it in Excel means that every month or every quarter when you're delivering this budget, the actual V Budget, you've got a data dump, the new data, whether that's QuickBooks, MYOB, Xero, maybe you're tipping straight out of APS, maybe it's a different accounting software altogether. And then you're aligning all of this work chart of accounts. You're doing V-look ups in the Excel spreadsheet to make it all work. It's probably a half an hour to an hour process for every single client that you do

I think that is a perfect project to start with Power BI. Let's move all of those budgeting jobs out of the Excel spreadsheet and into Power BI, where we've got an automated system and we've also got integrity on your data, which is really important. So what you're looking at on your screen right now, is a QuickBooks online example, where what we've done is we've done the budget inside QuickBooks. Okay? And then we've got data flowing year to date for the 2019 financial year. I understand why you wanna look, it a day, but I prefer to look monthly and quarterly. And the reason is because if you slip above or below budget, it's no longer relevant. So if you see for a shorts, it's 60 grand under budget and then you'd look another day and it's chine slightly, maybe you're still 60 grand under budget year to date. It's not really a good way to track your progress. Looking at it monthly or quarterly in a snapshot is a much cleaner way.

Let me show you that now. So same process, we've still got that budget data that you've entered into QuickBooks down there, down the column there, budget quarter, but we've got it segmented into a quarterly, It can be monthly by the way as well, but it just gives you a sort of analysis of where you're at for this quarter that's just been so you would supply this to your client. You say, look, here's how you've gone this quarter or this month. Here's how you did the previous quarter.

We've also put into this template the upcoming quarter for the June quarter. Because that way you're looking forward and backwards, which I think is really important. So you can have a look at the historical data of how you've gone scrolling down. But then what we've done here is the budget is the budget. I mean the budget's what's coming out of QuickBooks online is what you put in for your client. Here's the budget for the June quarter. But then the projected is what we've done is we've said, okay, grab the last three quarters that have been and divide by three. So it'll give you like a projection of how you're tracking. Now rightfully what straight away come out of the bat from other accountants is that it doesn't account for anything doing dividing it by three on the previous quarter. It doesn't account for seasonality. It doesn't account for lots of different reasons why. It may not be the case that the last three quarters are a good indication. So what we've added here is also sales projections, direct cost projections and expense projection. So basically here, if you're looking at 11 grand, let's just say, look, you're running a clothes store here and you know that you run a big, big sale with 30 June sale from for pants and whatever it might be. And you know that your sales are going to be a lot higher than the last three quarters. So let's pump our sales up. We'll put sales at 15%, we'll put direct costs up 10% because of that. And we'll also just for the sake of it, let's, let's say, okay, expense expenditure overall, we'll be down 5%. And so the page updates, now we've got a new projected figure. The budget stays the same, the variance has changed. So see, we're still under about two and a half grand. This is just for example's sake, let's bump it right up to 40% to show you what we can do. Okay, so now when we do a 40% we were all in green. Now we're over budget with everything, but you can see how you can manipulate it and actually see what's upcoming. And we can also scroll down and say, look, even with a 5% reduction in advertising customers still, way over these are traffic light reports of variants feed budget are a wonderful way to visualise and identify problems with your budget the actual.

Now comes the fun part. So this is the quarterly, right? This is the scale part. Now, remember I touched on earlier how when you're doing Excel with QuickBooks, MYOB, Xero, you got a data dump each quarter and it's probably a half an hour to an hour process per client each time that you do it. So watch this though. This is how powerful it is, is now we come to the June quarter. So another three months has passed by and we're ready to talk to a client about the June quarter. So let's click on June and done. Here is the data. We don't have to actually do anything. We just changed the day and all of a sudden we're looking at the upcoming quarter. Now is the next financial year 2020 we can start doing us projections again for the upcoming quarter. So just in summary, what are you the the counter get out of this? Well you get that automated process, right, So massive efficiency gains no longer for each one of your clients. You're not getting your junior accounting to roll over the file each time. You're also getting good integrity on your data. What is the client getting? Well the client, all of a sudden he's getting these really interactive reports to play with. They can move the goalposts and they can look forward and backwards, which is really powerful. Identify problems you can do, you can adjust the budget in QuickBooks, that can adjust the budget. And then it will flow through into here. All of a sudden we've got new budget figures.

The last thing I want to touch on is that to get this set up, this is a template. This is turn-key solution. So let me show you how quick it is to get this data in. So here, you're looking at the CMS. Okay. So it would be your accounting firm, and you just add a client into the system. You go into that client, you invite that client into the system, the CEO or whoever it is, you go to third party applications, add third party. So here's Xero, here's QuickBooks, here's MYOB. We click on QuickBooks, up pops the QuickBooks login. You sign in, select which client you have, done and you will get that report. And that's the same for Xero MYOB QuickBooks out of the box budgets. That is super powerful, super quick Integrity on your data, a powerful, powerful report building capabilities with Microsoft Power BI. I mean, to me this is a wonderful first project for you to go down. Moving all your existing budgeting clients, onto Power BI, huge upside, and this is only scraping the surface of what's possible in Power BI. So that's all for me. I hope you enjoy this is a really, really important, Budget VX we are looking forward and backwards really powerful. If you would like to talk about it further with me, give me a call.