Cool software? The Connected Business – Part 3

Yeah, those spreadsheets. Welcome to part 3 of this series of posts. An interesting special section in the Financial Times prompted me to post a quick summary with interesting quotes. But the report did not necessarily provide some specific details about why spreadsheets make our lives difficult. Part 2 then focused on the particular pain points we all experience in our daily business lives.

THE PROBLEM & ANSWER

But how exactly does Business Analytics software help and how can we replace spreadsheets? I get very frustrated when I read white papers that talk about ‘gaining efficiencies and critical insights with the push of a button’. There is so much BS talk out there. And as a result, I find that there are many managers out there that still don’t understand what technology can do for them. It’s time for clarity, isn’t it?

A FAIRY TALE?

Let’s take a look at one of my clients. When we first met, the new CFO was extremely frustrated with the tedious annual budgeting process. A team of six well paid finance professionals spent almost seven months pulling the annual budget together. The entire process was run through spreadsheets. The team spent an awful lot of time managing the 250 something workbooks for the different cost and profit centers. Complex formulas had to be fixed, data had to be copied & pasted from SAP R3, files had to be distributed and collected, reports had to be manually compiled and reconciled. In other words: it was painful and the team of six had very little time to do something fun. Their workload was extremely close to the picture we discussed in part 2 of this post.

HERE COMES THE SOFTWARE

The CFO decided to do something and selected Cognos to help automate the budgeting process. It was actually a rather short project with only 35 business days for the expense budget. Cognos replaced the entire spreadsheet jungle. But why and how did it help exactly. Here are some basic examples:

  • Centralized model maintenance: One of the key issues with spreadsheets is formula maintenance across different workbooks and files. Instead of copying and pasting, BA software allows me to maintain my formulas in a central repository. One change in one place only. That saves a ton of time and also ensures data integrity. How is that?
  • Automated interfaces: Why should we have to manually load actuals or other data from transactional systems into spreadsheets? There is a lot of risk (apart from the boring manual labor). BA software allows you to run automated interfaces. Just like in our transactional systems. Nothing fancy. It just works. And it saves a ton of time and frustration.
  • GPS: One of the other frustrations with spreadsheets comes from the inability to track progress. A simple question like: “Has Ted started his budget?” can be extremely difficult to answer. The file is not on the central server anymore as Ted is out on a business trip (and then he deletes his file by mistake..). BA software allows us to keep track of progress and it stores all the data in a central repository. Again…a very simple thing. But extremely effective. Imagine a spreadsheet with a powerful GPS on the roof!
  • Cool interface: Working with a spreadsheet template can be very frustrating. Either the templates are completely locked down or they are so flexible that I can break them. Modern software provides us with easy-to-use interfaces (web-based). They are actually fun to use and I can customize the layout to my personal needs – all that without breaking the model. This is a great way to engage managers that are not so technology-savy.
  • Automated aggregations: Getting a snapshot of your budget can be a bit tedious with spreadsheets. We need to make sure all files are accounted for so that we can link our master spreadsheet to them and aggregate the numbers. BA software takes care of that….in real time. All the data is stored in a central repository. You need a snapshot? Give me a minute. Here you go!
  • Easy analysis: Performing detailed analysis on a spreadsheet budget can be extremely difficult. Management decides to make some top-down changes and finance then has to figure out how to push this into the different organizational units. Asking some what-if questions can turn into a tedious exercise. BA software allows us to perform analysis in real-time. It’s that simple. We make change somewhere, and we can report on that right away. Cool!

Ok…I admit it. This is probably a bit ‘salesy’. But that’s the way it is. By the way, this particular client is now able to turn the entire budget around in less than 1.5 months. They actually like the budget process (scary thought?) now. It’s nice to talk about this but it’s better to see this in action. Why don’t you come and join one of our global workshops?

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

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 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 useful budget?

Fall of 2010. Many people across the world are engaged in a process that has polarized generations of business managers. Some love it. Most hate it. Yet, almost every company invests endless numbers of hours into the process. We are talking about the annual budget.

BEGINNINGS

The annual budget is a management tool that was invented to help managers guide their undereducated employees. It had its origin in the public sector. The annual budget provided workers with guidance and predefined resource allocation throughout a fiscal year. It helped managers control their employees. That was back in the 1920s and 1930s. A lot has changed since then. Volatility has increased, employees are highly educated and the speed of business has accelerated. But we are still using the same tool. Every year. Is that the right approach?

WASTED TIME?

Jack Welch famously called the budget a serious waste of time & resources. And indeed, I have worked with many companies over my career where the budget consumes an enormous amount of resources. One particular Fortune 100 company I met with, spends around 9 months per year working out their budget. 9 months! But the reality is that about 75% of all budgets loose their validity after only 2-3 months of a fiscal year. We spend so much time and get such little return?

DYSFUNCTIONAL BEHAVIOR

Many companies use the budget as the main pillar of their variable compensation scheme. As a cost center manager I get a bonus if I stay within my budget. As a sales manager, I get a bonus if I make my sales numbers. Unfortunately, experience has shown that this type of compensation leads to dysfunctional behavior. The cost center manager won’t invest in a useful activity only to stay within budget. The sales manager might cut some bad deals towards the end of year to make his number. And then there is sandbagging: people will do anything to protect their budgets. The cost center manager will try to spend his budget to avoid any potential cutbacks in the following year. And the story goes on and on.

A BETTER BUDGET?

We could spend all day arguing about the budget. But truth of the matter is that most companies are not ready to get ride of the process…yet. Clearly there must be some value given the enormous amount of time and resources? Personally, I do think that the budget can add value, but it has to be done in the right way. Without going into any details, every company should consider a few things:

  • Less detail: Reduce the detail in the budgets. This speeds up the process tremendously.
  • Change compensation: Move away from the budget compensation to include more important metrics.
  • Update the budget more frequently: Once a year does not cut it, anymore.
  • Automate the process: Throw those spreadsheets away and speed up the process by using proper technology.

Over the next few weeks, I will drill down on some ideas. There are some great stories from my clients. Until then….Happy Budgeting!

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.