Spreadsheets Confidential – The Connected Business – Part 2

The other day I wrote about the special section of the Financial Times called ‘The connected business’. One of the key take aways from this section was the fact that way too many finance departments are putting their faith into spreadsheets.

A few years ago, my team started conducting some surveys amongst finance professionals. For this purpose we teamed up with David Axson (co-founder of the Hackett Group, book author). We specifically went after professionals that were not using Performance Management software, yet. One of the key things we were interested in was the type of work finance professionals do. It quickly crystalized that there were five major categories of work. The results looked like this:

Cognos Finance Survey 2008

The majority of the time is apparently spent on manual tasks such as collecting data (loading data from systems into spreadsheets, copying & pasting, manually entering budgeting numbers, etc..), maintaining spreadsheets (development, fixing formulas, aggregating spreadsheet data, Visual Basic programming etc.) and then also developing reports & presentations (creating spreadsheet reports, graphs, Powerpoints etc.). Only about 20% of the overall time is apparently spent on the high-value tasks such as performing in-depth analysis, running what-if scenarios, personal development etc.. A shocking but not a surprising picture. When we present the results to finance professionals we get a lot of head-nods. But I often sense a certain level of resignation as well (“Oh yeah….I know….that’s just the way it is.”).

Statistics are always a bit dry. So we took the data and applied the percentage distribution to a work week. The picture now looks quite interesting. What do you think?

Cognos Finance Survey 2008 - part 2

How does this feel? Same numbers. Just a different perspective. Two key questions come to mind: Can we live with that situation? Would we want to live with this situation? I doubt it. I have been there and didn’t necessarily like this. Sure, it’s nice to play around with spreadsheets knowing that you are indispensable. But is that what we want to get out of our professional lives? Is that why we went to business school? Is that why we spent so many hours studying for the CPA, CMA, CFA exams?

Technology helps shift this picture around tremendously. We can literally reverse this. I will write about that in the next few weeks. For now, I will leave you with this picture. Take a look at your own work environment. How do you get things done? How do your clients operate? Is there room for improvement? Would love to hear your thoughts and about your own experiences.

The connected business?

The Financial Times from December 8th 2010 included an interesting special report called “The Connected Business”. A bunch of great articles about technology in the office of the CFO. One thing that caught my attention was the fact that the entire section highlighted the problems caused by the use of spreadsheets.

For people who work in the field of Business Analytics it has long been clear that while spreadsheets are useful, they are not the right tool to manage your company. People like us sometimes take that knowledge for granted. Here are some interesting pieces I picked up in the section.

Nigel Rayner from Gartner is quoted as saying: “But when I talk to them [CFOs] about management reporting, understanding the drivers of profitability, forecasting or planning, all they know are spreadsheets because they have always done it that way. They are not aware of how the technology market has developed.” Andrew Meade from Accenture confirms this notion in another article: “Most companies bemoan the fact that they have a spreadsheet jungle.”

Mr Giles Thomas from Revolver system highlights some of the inherent risks: “Businesses that start using spreadsheets for a specific purpose can get grabbed and they never escape. When you have a spreadsheet with thousands of rows in it, things can get quite nasty.” He highlights this problem with the case of an unnamed investment company: “For several months, they had been running the business on numbers that were meaningless. When people copy and paste, they often forget to incorporate the changes they have made.” The FT then highlights some of the other associated risks like proliferation, tracking, version control etc..

One article raises the question of whether the massive use of spreadsheets is likely to change in the near future. But the somber conclusion is: “He (Mr. John Van Decker – Gartner) and other business analysts do not see this situation changing quickly, because they say most business and finance education does not address these issues“. Mr Rayner adds “It is a cultural issue, because finance folks tend to be very conservative. They are comfortable with what they know, which is spreadsheets, and having an army of spreadsheet jokeys.

The point about education is an excellent one. I used to be an active  member of the Institute of Management accountants (IMA). The organization itself is great and I highly recommend their professional degree CMA (Certified Management Accountant). But they consistently frustrate me with their singular focus on spreadsheets. Hundreds of articles in their publications teach people how to leverage spreadsheets. They offer courses for managing a complex budgeting process with spreadsheets. Time to wake up, from my perspective.

The conclusion? Well, the section does offer some nice examples of how technology helps and I will continue to write more blog posts in the near future about that. But the key finding for me here is the astonishing fact that there is a serious opportunity for all of us. As a CFO I have the ability to gain a serious competitive advantage (or: to avoid disaster) and as a Business Analytics professional I can make a serious contribution to the success of many businesses. Let’s use this opportunity.

E9RBNEHCCAP3

The case for forecast accuracy

People always say that you get what you measure. And it is true. When I want to loose weight and I am serious about it, I do have to step on the scale frequently. The same thing is true for business. What gets measured gets done.

FORECAST ACCURACY

Forecasting has become a critical business process. Pretty much every company that I talk to is either improving or looking to improve this process. One of the measures that can be used to manage the forecast process is forecast accuracy. Forecast accuracy measures the percentage difference between Actuals and Forecast. Let’s say we forecast 100 units sold for next month and it turns out that we actually sold 95, the forecast accuracy value -5%. We can measure accuracy at different levels of an organization, let’s say at the Profit Center level or at the BU level.

EMOTIONS RUN HIGH

A few weeks ago, I had an interesting discussion with a group of consultants. They argued that forecast accuracy is not worth measuring. Their main arguments were:

  • Forecast accuracy cannot be influenced. The markets follow a random path and it can therefore not be expected to achieve accurate forecasts.
  • Forecast accuracy is a dangerous thing to measure and manage. People can start influencing the accuracy by managing their numbers according to expectations (for example sales managers can hold back deals for sake of influencing accuracy)
  • The quality of forecast accuracy is hard to define. Let’s say we beat our own forecast by performing really well. Forecast accuracy is off. Is that good or bad?

MY VIEW

Here is my personal view on this topic.

  • No single measure is perfect when looked at in isolation. Let’s say profits. What does the profit number for a certain quarter tell us? Nothing! We need to look at a mix of measures. Forecast accuracy is one measure that we can/ should look at.
  • Forecast accuracy provides us with the ability to identify potential bias. One of my clients, for example, found that their models were flawed. Forecast accuracy revealed this by highlighting a certain consistency.
  • Markets movements are difficult to anticipate. But it is the job of the forecaster to identify potential actions to make sure that targets are achieved. I should have a general clue about what is happening in my business. Once in a while, we encounter some surprises. Does that mean we should not measure forecast accuracy? I beg to differ. At the very least, a detailed analysis of the accuracy measurements can help us learn a lot about our organization and our environment.
  • Forecast accuracy is easy to measure. It can be automated. Cost are almost zero. Why not measure it and potentially learn something?

I could go on and on. The bottom-line is that forecast accuracy is easy to measure and that it allows us to get a good sense for our ability to forecast and manage our business. But we need to be careful about how we utilize the metric. A singular focus on managing just accuracy won’t do anybody any good. But that’s true for anything. If I want to loose weight, I should also look at muscle mass and water content – not just weight as measured in lbs or kg. But to start bashing a single metric is not a good way. I am all for looking at forecast accuracy – often.

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.