Power BI: Agile reporting of actual vs forecast, target or budget

21st Nov 2018
Director Accounting Insights Ltd.
Share this content
Business woman uses a calculator to calculate the company's performance

Hugh Johnson explains how tools like Power BI can help you to transform an outdated budgeting process into something more dynamic and really useful.

In my opinion, Power BI really comes into its own when you want to set targets and then track your business performance against them.

For many companies, budgeting is an outdated process

I asked one of my clients, a finance director of a growing technology business, why he doesn’t use the budgeting functionality in Sage 50. His answer was simple. He said: “My business changes too quickly to make an annual budgeting process useful and worthwhile.”

He is not alone in his thinking. In recent years, business budgeting has been criticised by very well-respected sources. Take a look at “Why budgeting kills your company”  by the Harvard Business School, or “Budgets don’t work: Here’s how businesses can do it differently” from Forbes.  

If you read what is really behind these articles, one of the main points that they are making is that the budgeting process itself is too time-consuming and the output too rigid to be relevant in today’s business environment.

Few people suggest that it is wrong to have business goals, or targets. It is just that the traditional annual budgeting process is, for many businesses, so out of sync with reality. It operates on an annual cycle that is too slow in today’s environment. Yet for many, the very thought of accelerating the cycle would be a nightmare because the budgeting process itself is so painful and time-consuming.

But we still need to have, and track, a plan

This does not mean that it is wrong to have a plan.  You know the old quote from Winston Churchill: “He who fails to plan, plans to fail.”  

So if we need a plan, how do we go about preparing it and how we should track our performance against the plan (or why have the plan in the first place?).

Modern business intelligence comes to the rescue

Fortunately, technology can come to the rescue with modern data analysis tools like Power BI. It is, after all, technology that is causing these shortening business cycles.  

My hypothesis is that budgeting, planning, forecasting or whatever you want to call it is not the wrong thing to do. It is just that we do it in the wrong way. That is slowly, manually and painfully.

Let’s break down the process. It is actually quite simple at a high level:

  1. We analyse what we know - typically past performance
  2. We project onto that what we feel may happen in the future, based on this and any additional knowledge or experience that we may have
  3. From this we create forecasts, budgets, targets
  4. We track our performance against these

Simple, as I say, at a high level. The problem comes down to actually doing it to the level of detail that is in any way robust or meaningful. Imagine this scenario for a second:

  • You are a wholesale company with c.1,000 products and c.1,000 customers
  • Around 25% of your products and customers become obsolete every 12 months
  • There is significant seasonality through the year for different product and customer combinations
  • Margins are tight and getting tighter
  • You have a long (3-5 months) lead time to order products
  • Your customers demand short lead-times (this is your main value-add)
  • Year-on-year sales are up 20%

You can see straight away that blindly adding 20% growth across the board is not going to cut it as far as a useful yearly budget is concerned. You will end up ordering the wrong mix of products and lose a truck-load of money. I would argue that a business like this needs a rolling 6-month plan, updated every month.  

The starting point would be a sales plan by product code and customer, from which everything else flows, including cash-flow, warehousing, and staffing plans. I also think that the sales forecast and actual against plan needs to be updated weekly; providing guidance for the weekly sales meeting and feedback into the overall rolling six-month plan.

If you were to do this manually, perhaps more than 90% of the work would be to analyse your historic sales patterns, project future demand and determine many of the flow-through implications.  Probably less than 10% of the work is to add to this the human element - knowledge of a big new client that is in the pipeline for example. Clearly all of this work cannot be done manually, at least not as frequently as the business cycle requires.  

The good news is that it can be automated - or at least much of it, perhaps 90%. You are swimming in data about your own business - right down to every line item of very sales invoice for every customer.  

An automated model in a tool like Power BI can trawl this data every night and update it’s own view as to what will happen in the next week, month or six months and track this against a current plan. That is 90% of the work done.  

What is needed is the human element to do the remaining 10% that is updating the weekly and monthly targets and six-monthly operating plan based on additional things that you know about the business.

It is after all much faster to eyeball a computer-generated forecast and adjust this based on what you know than to create a new forecast from scratch.

Take a look at this video that I did which shows an element of this scenario - analysis to support the weekly sales meeting. Behind the scenes, the Power BI model is doing a number of things, with two of them surfacing in this video:

  • It is trawling the last two-years line-item sales data and looking at every combination of sales by product and customer by date and using this to project a rolling forecast.  In this case, a sales forecast for the month by customer.
  • It is looking at recent changes in customer buying patterns as an early-warning system for the sales team.

What is not shown in the video is that the model is also breaking down the sales forecast by product code, looking at current stock levels and identifying what needs to be ordered.  It is also identifying slow-moving stock that is just tying up space and capital in your warehouse.

The correct kind of forecasting algorithms to use will depend on the nature of your business.  In the video, the forecasting algorithm is a quadratic regression done over two years’ data so that it takes into account seasonality.

The “last 28 days” customer behaviour algorithm is different and takes into account individual customer volatility. Automatically forecasting sales by customer only makes sense when most of your sales come from frequent customers. This does not stop you from building forecasts based on other elements.

Summary and what next?

In this article, my central point is that you can automate the initial data analysis that feeds into any business planning process (unless you are a start-up and have no history to learn from).  Once it is set up, then it just runs automatically and this data analysis can be done pretty much as often as you like - but overnight and every night is not a bad starting point. It is step number one if you want to shorten the budgeting/forecasting cycle.

It is only part of the solution though.  What I have not discussed in any depth (otherwise this would become a very long article) is:

  • What kind of algorithm(s) make sense for your business?
  • How much of what data do you need, and at what level of granularity?
  • How to turn a machine-generated forecast into a practical budget or target?
  • How then to automate tracking your actual performance against your targets?

I will go into these subjects in future posts. But before we get too esoteric, I would like to share a very simple way to set up and maintain P&L targets in Excel and track your actual and variance against these targets using Power BI. It is so simple, that setting and tracking monthly sales targets for example is really practical. This will be the subject of my next post.

Useful links


Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.