Don’t Let Spreadsheets Put Your Forecasting and Planning at Risk

Good planning and forecasting is not something that can be achieved overnight. The organizations which consistently succeed constantly iterate to improve their processes; they take the things that work and ditch the things which don’t work, offer limited value or are simply holding them back.

James Kipling, Product Manger at Quantrix

There are many best practices that every organization should follow, the most important of which I expand on later in this article.But first, we need to look at a systematic issue likely to be present in every organization in some form – including your own.

I am talking about spreadsheet risk.

Think outside the cell

Spreadsheets are used in almost all areas of business, in nearly every industry and every sector. The inherent flexibility of the two-dimensional table we have grown familiar with has ensured that whether you work in the front-office, advising clients or selling investment products, in the middle office analysing risk and compliance, or in the back office managing the plethora of services and products that today’s businesses utilize, you’re almost certainly going to be using a spreadsheet of some kind, often on a daily basis.

With the use of spreadsheets so widespread, risk officers should be fully aware of the damage an erroneous table, figure or calculation could cause to a business. But are they taking these concerns seriously enough?

Often small errors are shrugged off with little thought, and the framework which enable errors to propagate are frequently ignored, but we have seen – and will continue to see –how such errors can be catastrophic for businesses.

Perhaps the scariest thing about spreadsheet risk, is that you may not even know that your forecasts are based on bad assumptions. The error may not be in your spreadsheet, or the spreadsheet you got your assumptions from – but in the ad-hoc market forecast done by an anonymous intern, three years ago.Of course, no one bothered to audit and check the calculations. Why would they?No one ever saw the output of that model becoming one of the cornerstones of strategic decision making at the highest level – and yet it can, and does, happen.

Thankfully, there are some measures your organization can take to ensure you are doing all you can to provide accurate forecasts and plans.

Right question

First and foremost, you must ask the right questions. Instead of getting distracted by fancy calculations or complex methodology, which can lead to a cumbersome unwieldy model – try to stick to answering the questions which are most important for you and your organization.

Too often organizations get hung up on revenues, profits and dollars – are these really the core drivers of your business? Perhaps, perhaps not… Be sure to explore often forgotten metrics such as units, customers and conversions. It can be easy to get carried away once you start to build out a new plan, but ensuring you always ask yourself “do I need to answer this question” will go a long way to improving your capabilities.

Right data

With the questions decided, it’s time to start thinking about where you’re going to get your data from. With the rise of the ‘big data era’, the first thing in most people’s minds is “more data is good data”. This is simply not true. Sometimes you don’t need to use all the data as this can over complicate the model and increase the likelihood of errors being introduced.

Always make sure you make a direct connection to the underlying data (often called the ‘single source of truth’). The outdated method of copying and pasting data from source to input is a recipe for disaster as if the source data changes, not only are you now working from old data, but anyone who relies on the output of your forecast is also out-of-sync with the current situation. Investing early to create the single source of truth and then providing the right tools to use that data effectively, pays for itself multiple times over as your capabilities and ambitions grow.

Right structure

You’ve got the data and you know the questions you need to answer but how can you appropriately structure your model to be flexible and scalable yet robust? We live in a multidimensional world, with many organizations operating across multiple regions, selling a variety of products. Combining this with the need for your plan to allow for multiple scenarios through dynamic ‘what-if’ – this makes the model structure extremely important.

Businesses aren’t static, they’re evolving entities, constantly changing the products they offer and the ways in which they sell these products. Your model needs to be flexible enough to adapt to these changes as they happen, with minimal effort (ideally automatically)! Failure to address this early on often results in a huge amount of re-work to re-engineer the spreadsheet – giving even more chance for errors to creep in.

Right logic

Arguably one of the most important aspects to good planning and forecasting is allowing others in your organization to draw insights. For this reason, it is important to make sure the logic your model uses is both understandable and well documented.

Just as the intern had never anticipated the model they built during their summer placement would have ended up in the boardroom three years later, explaining the intention of your calculations in plain English goes a long way to ensuring that your model is future-proofed. We’ve all opened spreadsheets years later, where without context, the information on screen may as well be Hieroglyphics!

Right tools

Professionals are moving away from off-the-shelf tools. The tool of choice for graphic designers is unlikely to be Paint. Likewise, it is improbable that the professional software engineer uses notepad for their day-to-day coding.

It may sound simple, but whether its visualization tools such as Tableau, or a full enterprise level business modeling platform such as Quantrix, using the right tools for the right job will ensure that efficiency killers such as formula writing, error checking and auditing are all alleviated through the use of professional features such as natural language formula writing, built-in audit trail and a visual dependency inspector. Multi-dimensional modeling tools are on the forefront of financial planning and forecasting – the inherent nature of today’s global businesses means the questions asked of analysts often exist in a multi-dimensional problem space. Don’t let habit be the reason you put your forecasts at risk.

By James Kipling, Product Manager at Quantrix
James joined Quantrix in 2015 as Product Manager and initially worked with the team on the successful Quantrix version 6 project. He now leads the development effort for the Quantrix Modeler product working on the product roadmap and strategy, as well as coordinating marketing and sales activities.

Good planning and forecasting is not something that can be achieved overnight. The organizations which consistently succeed constantly iterate to improve their processes; they take the things that work and ditch the things which don’t work, offer limited value or are simply holding them back.

James Kipling, Product Manger at Quantrix

There are many best practices that every organization should follow, the most important of which I expand on later in this article.But first, we need to look at a systematic issue likely to be present in every organization in some form – including your own.

I am talking about spreadsheet risk.

Think outside the cell

Spreadsheets are used in almost all areas of business, in nearly every industry and every sector. The inherent flexibility of the two-dimensional table we have grown familiar with has ensured that whether you work in the front-office, advising clients or selling investment products, in the middle office analysing risk and compliance, or in the back office managing the plethora of services and products that today’s businesses utilize, you’re almost certainly going to be using a spreadsheet of some kind, often on a daily basis.

With the use of spreadsheets so widespread, risk officers should be fully aware of the damage an erroneous table, figure or calculation could cause to a business. But are they taking these concerns seriously enough?

Often small errors are shrugged off with little thought, and the framework which enable errors to propagate are frequently ignored, but we have seen – and will continue to see –how such errors can be catastrophic for businesses.

Perhaps the scariest thing about spreadsheet risk, is that you may not even know that your forecasts are based on bad assumptions. The error may not be in your spreadsheet, or the spreadsheet you got your assumptions from – but in the ad-hoc market forecast done by an anonymous intern, three years ago.Of course, no one bothered to audit and check the calculations. Why would they?No one ever saw the output of that model becoming one of the cornerstones of strategic decision making at the highest level – and yet it can, and does, happen.

Thankfully, there are some measures your organization can take to ensure you are doing all you can to provide accurate forecasts and plans.

Right question

First and foremost, you must ask the right questions. Instead of getting distracted by fancy calculations or complex methodology, which can lead to a cumbersome unwieldy model – try to stick to answering the questions which are most important for you and your organization.

Too often organizations get hung up on revenues, profits and dollars – are these really the core drivers of your business? Perhaps, perhaps not… Be sure to explore often forgotten metrics such as units, customers and conversions. It can be easy to get carried away once you start to build out a new plan, but ensuring you always ask yourself “do I need to answer this question” will go a long way to improving your capabilities.

Right data

With the questions decided, it’s time to start thinking about where you’re going to get your data from. With the rise of the ‘big data era’, the first thing in most people’s minds is “more data is good data”. This is simply not true. Sometimes you don’t need to use all the data as this can over complicate the model and increase the likelihood of errors being introduced.

Always make sure you make a direct connection to the underlying data (often called the ‘single source of truth’). The outdated method of copying and pasting data from source to input is a recipe for disaster as if the source data changes, not only are you now working from old data, but anyone who relies on the output of your forecast is also out-of-sync with the current situation. Investing early to create the single source of truth and then providing the right tools to use that data effectively, pays for itself multiple times over as your capabilities and ambitions grow.

Right structure

You’ve got the data and you know the questions you need to answer but how can you appropriately structure your model to be flexible and scalable yet robust? We live in a multidimensional world, with many organizations operating across multiple regions, selling a variety of products. Combining this with the need for your plan to allow for multiple scenarios through dynamic ‘what-if’ – this makes the model structure extremely important.

Businesses aren’t static, they’re evolving entities, constantly changing the products they offer and the ways in which they sell these products. Your model needs to be flexible enough to adapt to these changes as they happen, with minimal effort (ideally automatically)! Failure to address this early on often results in a huge amount of re-work to re-engineer the spreadsheet – giving even more chance for errors to creep in.

Right logic

Arguably one of the most important aspects to good planning and forecasting is allowing others in your organization to draw insights. For this reason, it is important to make sure the logic your model uses is both understandable and well documented.

Just as the intern had never anticipated the model they built during their summer placement would have ended up in the boardroom three years later, explaining the intention of your calculations in plain English goes a long way to ensuring that your model is future-proofed. We’ve all opened spreadsheets years later, where without context, the information on screen may as well be Hieroglyphics!

Right tools

Professionals are moving away from off-the-shelf tools. The tool of choice for graphic designers is unlikely to be Paint. Likewise, it is improbable that the professional software engineer uses notepad for their day-to-day coding.

It may sound simple, but whether its visualization tools such as Tableau, or a full enterprise level business modeling platform such as Quantrix, using the right tools for the right job will ensure that efficiency killers such as formula writing, error checking and auditing are all alleviated through the use of professional features such as natural language formula writing, built-in audit trail and a visual dependency inspector. Multi-dimensional modeling tools are on the forefront of financial planning and forecasting – the inherent nature of today’s global businesses means the questions asked of analysts often exist in a multi-dimensional problem space. Don’t let habit be the reason you put your forecasts at risk.

By James Kipling, Product Manager at Quantrix
James joined Quantrix in 2015 as Product Manager and initially worked with the team on the successful Quantrix version 6 project. He now leads the development effort for the Quantrix Modeler product working on the product roadmap and strategy, as well as coordinating marketing and sales activities.