Spotlight on Forecast Accuracy

Forecast accuracy in turbulent times

Forecast accuracy is one of those measures many finance professionals think and talk about. Turbulent times require companies to produce reliable and solid forecasts. Accuracy is a useful measure that helps finance managers assess the quality of the process (to a certain degree!).

In late 2011, my good colleagues Mark, David and I conducted a survey amongst 160 UK finance professionals. One of the things we wanted to find out was whether accuracy is being measured at all. And guess what – we were pleasantly surprised to see that the majority of all companies do measure and also communicate accuracy. Only a few organizations face difficulties doing so (they utilize spreadsheets as their main tool).

Forecast Accuracy Survey

“We stand very little chance of forecasting successfully unless we measure our performance continuously and correct our forecasts accordingly.”, Steve Morlidge & Steve Player, authors of “Future Ready”

Accuracy Insights

About a year ago, I posted a series of articles that focused on forecast accuracy. If you are interested in this topic, I would like to invite you to read and share those entries.

The basics

There is a set of posts that cover the basics of this topic. You can find a bunch of examples in there as well.

Experiences

Experiences are also important. You can find out what some experts are saying about this topic.

If you have any experiences with forecast measurement, please leave a comment. As solid as the above mentioned survey results look, experience shows that many finance professionals are looking for more information about this topic.

An imaginary conversation with a weather frog

European folklore believed that frogs kept in a glass would be able to forecast the weather. People filled some water in the glass to keep the amphibian happy, and then added a small ladder. A climbing frog would indicate good weather, whereas a frog hanging out in the water would show bad weather. This belief especially stuck with people in the German speaking countries where weather forecasters are typically called ‘Weather Frogs’ (Wetterfrosch). Well, weather forecasters do one thing well: forecasting. They are the true masters and I thought that we could get some insights from one of them. It is frog migration season in Bavaria and I happened to have found one who is willing to talk to me. Please meet Franz the Frog. Franz resides in Bavaria, Germany where people recognize him as a trusted master forecaster.

 

AN IMAGINARY CONVERSATION WITH THE FROG

Christoph: How is life as a master forecaster? Your pictures are every on the side of the roads these days. You must be pretty busy? Spring is known for its volatile weather.

Franz the Frog: All cool here in the pond. Thanks for asking. We were quite busy up until yesterday. That’s when we finished our quarterly forecast. I am looking forward to jumping around for the next few weeks.

Christoph: But wait a second! It’s a volatile climate out there. How can you just sit there, jump around and not forecast whenever things change?

Franz the Frog: Dude, I hear ya’. But the big boss here in the pond decided that 2-3 forecasts per year are totally fine. Plus we have so much other stuff to do. Also, do you realize how much work we have to do to complete a forecast?

A weather frog

Christoph: Sorry, this was news to me.  Then tell me, why exactly is this so much work?

Franz the Frog: Helllllooooooo!!!!! Each forecast starts with us looking at weather patterns for the past five years. Just simply gathering the data that is stored all over our pond takes us forever. Our big boss in the pond also wants us to create detailed variance reports for those years. That takes about a month. To create the actual forecast, we turn over every single leaf in our pond. And we record every little rain drop. That takes a lot of time. Get it?

Christoph: Oh…I see…a lot of detail. But a lot of detail should result in higher accuracy right? My wife Jen complained about your reliability the other day. She claimed that she would not even ‘pack a suitcase for vacation’ using your information. Errr…please don’t shoot the messenger.

Franz the Frog: Watch it, buddy! I will stick my tongue out here in a second. What do you expect? We get paid by our big boss in the pond. If the boss is happy, the flies are happy and we are happy. The boss decided that it’s best for us to provide a forecast that tells you guys exactly what you want to hear. Last year we saw rain coming. Your wife complained that she did not want any rain that particular week. She said:’Ahh, this stupid forecast. Rain is driving me crazy. It should be sunny!!!’. That got my big boss in the pond really upset. And guess what happened: no pay. That made our decision easy: we would eliminate a lot of pain and frustration if we simply forecast what everybody wants to hear.

Christoph: Oh…ok. That is so not cool. Let’s change topics. What type of tools do you guys use to do your forecasts? I mean, we’re in the year 2011 so  I suspect that you guys have some cool tools…like that PC game Frogger?

Franz the Frog: Frogger rules!!! If you like Frogger, you will be happy to hear that we are still using the same platform. No changes. Here, take a look: We have special leaves from a searose that was created in Redmond, WA’. Those leaves allow us to play with the data that we collect. The nice thing is that everybody in our pond has a ton of those searoses flying around. And the other guys love those leaves. The all create their own versions. But do me a favor and DO NOT talk to the green guy over there: he has to collect all the leaves at quarter-end. He hates his job. A bunch of my colleagues sometimes play a joke on him and swap out leaves or hide them. Others change the carefully thought-out leaf structures by ripping a holes in them or by chewing on them. You should see his face when the leaves don’t stack!!! Haha…RRRiibbitt.. Hilarious.

Christoph: Holy tadpole! That sounds like a tough job. Can you trust the data then?

Franz the Frog: Probably not. But hey…that’s the way the pond has been for a long time. It worked in the past it should work in the future, right? We have been around for thousands of years.  And the big boss is happy and we’re getting paid—what’s not to like?

Poor forecast accuracy

Over the last few weeks three separate clients have expressed their frustration with inaccurate forecasts delivered by certain members of the salesforce. Nothing new here. It happens all the time. However, what struck me about these three independent cases was the nature of the issues: The salesforce consistently forecast higher than actuals. This is not typical. Most sales people try to forecast lower to build up some buffer in case of bad news.

BOOKING INVENTORY

What happened here? Very simple: sales tried to utilize the forecast to ‘reserve” inventory of their extremely well-selling products. Their rational was that a higher sales forecast would inevitably lead to a higher availability of finished products ready for sale. In the past several sales people had encountered product shortages which affected their compensation negatively.

The sales forecast as an inventory 'reservation' mechanism

THE THREAT

This kind of poor forecast accuracy could lead to a precarious situation. In case of an unexpected economic downturn, the company could end up sitting on a ton of finished goods inventory. And not only that: average inventory could trend upward reducing liquidity as a result. As we all know, inventory is central to effective working capital management.

THE REMEDY

The controllers of these companies were very frustrated with the situation. Despite senior management discussing the resulting issues with the sales force, accuracy barely improved. But one controller had developed an interesting idea that he is about to implement: start compensating the sales teams based on Working Capital measures.

WORKING CAPITAL & FORECAST ACCURACY

The basic idea of this evolves around punishing sales for consistently producing these unacceptable variances. And the implementation of this does not necessarily have to be that hard. We can measure forecast accuracy. A series of negative variances (Actuals < Forecast) leads to a reduction in the sales bonus. The critical thing here will be to avoid punishing people for random variances. I could see using a rolling average to only punish consistent ‘offenders’.

This is an interesting idea. The actual compensation impact would obviously have to be worked out carefully. But the basic idea is quite interesting!

The case for continuous forecasting

Continuous Forecasting

Time for a confession. I really hated forecasting back in my old job. Here I was working with clients on improving their planning, budgeting & forecasting processes. Yet, I absolutely hated doing my own forecast. It just didn’t feel right. What was wrong? Well, I never really understood the template that our controller sent out. And it always took forever. Luckily, I had to do this only 2-4 times per year. But that was also part of the issue. Every time I received the forecasting template (a complex spreadsheet!) I had to collect and enter a ton of data. Also, I had to re-orient myself and figure out how the template worked this time. And then there was the reconciliation between my project plans and the prior forecast. To sum it up: The ramp-up time was simply too long. The result: I hated the forecast because the process took too long and it was too infrequent.

Fire-Drill

Indeed, the typical process for updating, distributing, collecting and aggregating forecasting templates can take up to a few weeks in most companies. It is critical to understand that the templates are typically unavailable to the user community during extended periods of time. Analysts are busy and need to take care of other tasks between forecasting cycles. As a result, forecasts are being conducted infrequently and the business owners feel like conducting a ‘fire-drill” when the templates are actually sent out.

The traditional spreadsheet-driven process

Forecasting Software

But there is a much better model that many of my clients have implemented. Modern planning & forecasting software allows us to keep our forecasting templates online nearly 24*7. We no longer have to collect our 100s of spreadsheets, fix formulas, manually load actuals, manually develop new calculations and the re-distribute the templates in long and manual cycles. Thanks to OLAP technology (sorry for the techie term), we can make model changes in one place only and they can automatically be pushed out to the different templates (e.g. cost centers, profit centers etc..). Automated interfaces between the ERP (for actuals) and the forecast models can be setup. We can automatically aggregate data in real-time and we can control the process flow. Overall maintenance is a lot easier and the templates are available pretty all the time and the users can work with their data around the clock and throughout the year.

Using this technology, Finance departments can allow the business users to work in their templates around the clock. A sales manager can update her data right after a critical customer meeting (e.g. change the sales quantity for a product). In other words, people can make quick incremental changes to their forecast data instead of performing time-consuming, infrequent larger data input exercises.

Continuous Forecasting

But the Finance department now has to carefully communicate with the business. They need to clearly communicate submission deadlines etc..

The continuous data collection process

But what is the advantage to the business users and the finance department? How would this technology have change my personal experience in the prior job?

Clients typically experience three main advantages:

  • The templates are available 99% of the time and users can work in them on a daily basis. As a result, users become a lot more familiar with the templates and their comfort levels rise.
  • The actual forecast process is a lot faster for the business users. They can make incremental changes which typically don’t take that much time. Contrast that to my case where I had to build a bottom-up forecast almost every quarter. The ramp up time can be considerable.
  • Forecasts tend to be more complete. In the case of an urgent ad-hoc forecast (imagine something critical happened), the business is able to compile a near complete forecast in very short time. This is where the incremental updates add serious value. Contrast that to the traditional spreadsheet process. People might be out on vacation or they are out traveling. The potential time-lag to get somewhat decent data can be quite long.

Let me clarify one last thing: A continuous process does NOT mean I can simply aggregate my data every night and obtain an updated forecast. No, I need to communicate to the business WHEN I need the data. But due to the 99% availability I can collect my data very quickly.

Let’s go continuous! Would love to hear your thoughts and experiences. Good or bad.