Director Accounting Insights Ltd.
Share this content
Forecast
istockprimeimages_forecast

ABC of Power BI: F is for Forecast

by

How relevant are trends and forecasts in volatile times like now? Power BI supremo Hugh Johnson puts forward the case that they are more relevant than ever. It is just that you need to come at them from a different angle.

14th Jan 2021
Director Accounting Insights Ltd.
Share this content

For a limited few, 2020 was not all bad. The Irish economy is expected to close out with growth of around 3.5%. So despite all the business interruptions, quite a number of companies and sectors must be part of that “limited few”. Others are happy just to have survived. Sadly many businesses haven’t. 

However, a year such as 2020 makes you reflect. What is the point of a forecast, and any plan that follows it, when we can get struck out of the blue with such a punch in the mouth? 

It actually reinforces the need for forecasting and planning but in a much more short-term and agile way. 

We have never been in a situation where the early warning signs of short-term change are more important. I am not talking about what will happen in three years, or even next year. I am talking about what will happen this, next and in the following few weeks. More than ever we need short-term forecasts and the quick, immediate plans that follow. 

The early warning gold mine

In business, anything that can give you early warning of change is gold dust. You can combine what the data is telling you with what you are actually experiencing and hearing to get the best picture you can. The result is that you end up closely tracking a few vital key indicators of what is actually happening, versus some kind of forecast.

The weather forecast that I may monitor on my phone is driven automatically by algorithms that are constantly monitoring what is really happening. The forecast is updated and published automatically every three hours. Unfortunately, business forecasting and reporting typically follows a much slower, manual cycle and is not particularly useful in spotting that punch in the mouth. 

It is the equivalent of heading out for a walk with a weather forecast that you printed out in the previous week, then writing up a blog the week after about how you got caught out in a heavy squall. Traditional business forecasting has a purpose, but it is not a very good early warning system.

Track, monitor and communicate

So where does Power BI come into this? The answer is to try to regain the extra time, rather than getting soaked in a squall and wondering what happened. 

This gain ultimately comes from the ability to track the important elements of your data, monitor and communicate surprising changes as they happen. To regain the time, there are a number of things that you may be able to put in place. What they are and what is possible will depend on the precise nature of your business:

1. Accounts Receivable / Cash Collection

Take a look at the report below, courtesy of Sage UK.

 

It is a standard Aged Debtors report from Sage 50 Accounts. As you would expect, the information presented is accurate and up to date. It just doesn’t tell you what you need to know in a crisis. 

It doesn’t tell you how much cash you are likely to collect this month, nor where and how you need to direct your efforts to achieve that. If there is a business shock that affects some or all of your debtors, it will be some time before the effects show up in this report. Even then, given the presentation of the data, it may be difficult to interpret. 

Now, compare that with this report that takes up the same space on the screen and is essentially looking at the same data, just crunched and presented differently:


 

Based on the latest cut of the data (which could be less than an hour old), that in normal circumstances I could reasonably expect in £197k in customer receipts this month and so far, I have collected £140k of this. Both numbers are driven automatically from the underlying data and will therefore change as the month progresses. 

This very simple step can provide you with a warning before month-end if something is awry and the rest of the report will show you where and how you might want to direct your actions. 

2. Traditional Actual vs Budget / Forecast Reports

Another simple step that you can consider is to use Power BI, instead of Excel, for your monthly reporting. As part of your report, you may end up with a page like this:

This is not going to buy you much time in terms of an early warning system, but it is very simple to put in place and can deliver three main advantages:

  • Faster production and distribution of the monthly report, thanks to the automation of tedious background steps and checks taken by many financial controllers in the early days of each month.

  • Faster interpretation of the report, thanks to:

    • Thoughtful data visualisation (what is right for your business may, of course, vary from the above)

    • Easy drill-down and cross-examination of headline variances (since all your data is together in a single model)

  • Improved confidence from your directors, shareholders and creditors that you are on top of things and in control. This is not to be underestimated, especially in these difficult, unpredictable times.

3. The “Algorithmic Smoke Detector”

The idea of the Algorithmic Smoke Detector is to look for the unusual things in your data. The concept is to produce a model that scans your previous data and looks at trends in order to produce a forecast range of expected values right now (this could be customer orders, receipts, or even a full set of financials). 

Something that is normally very volatile won’t be sensitive, and something that is usually a very steady state will be. If you compare the range of forecast values with the actuals, this can give you that early warning system that might not prevent the punch in the mouth, but very usefully tell you from where it is coming. It is the equivalent of putting a smoke detector in every room instead of just the hallway. 

Here is a promo video of a model that I did three years ago, based on a real but anonymised, Sage 50 Accounts dataset. The model analysed customer purchasing patterns by product and sales amount, every day, on a rolling 28-day basis over the previous 24 months. It assumed a normal distribution within the data and used the 24-months to bring in some seasonality. The piece that I want you to focus on is the Customer Heat Map. 

The “forecast” that produces this heat map is retrospective. In other words, it is using the historical data to flag what looks strange today. It calculates expected range (within one standard deviation) of customer behaviour (product purchase quantities and values) in the last 28 days, for each customer. 

It is therefore dynamic in the sense that the range would be very narrow for a customer that pretty much orders the same things every week, and very broad for a more volatile customer. The report then flags any customers that fell outside this range as an early warning system for behavioural change. 

In an extreme case, a customer that always ordered exactly the same every Friday for the last two years would flag up as an exception in the small hours of Saturday morning if they had missed an order. 

At the opposite end, a customer whose buying patterns were all over the place would not get flagged until it had either been dormant for some time or had placed an exceptionally large order.

Now, something out of the blue hits your business. It could be Covid-19, or it could be an action from one of your competitors. Wouldn’t it be good to have a smoke detector in every room of the house, rather than wait until the whole building is on fire?

Summary

I have outlined three things to think about to help you to make your forecasting and reporting more relevant in these turbulent times. This is not an exhaustive list.

The first two examples are relevant to many businesses and are very simple to implement in an almost generic way. The third example is technically very simple but requires deeper thought and adaptation to any particular company’s needs and natural business cycles.

Useful links

Replies (0)

Please login or register to join the discussion.

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