Why spreadsheet based forecasting is so 2019
If 2020 has proven anything, it’s that uncertainty is the only certainty. For businesses of all sizes, it has been a huge challenge to adapt and move forward in the face of a worldwide pandemic.
If you’re going to steer your business safely through this period, you should probably think about predicting the future. Unfortunately, none of us has a crystal ball (although wouldn’t that be wonderful?) so instead, Business Owners, CFO’s and Strategists need to make their decisions and plans for future success based on spreadsheets, gut feeling, experience, consensus or to put another way – educated guesses.
In this article, we take a look at how many companies manage their forecasts, what the future of forecasting looks like and we offer some advice along the way.
The Rocky Road
In the normal course of events, you would be commended for putting together highly sophisticated spreadsheets with complex formulae with different revenue models, sales forecasts and other sector specific models such as Capacity Yields, Manufacturing, Professional Services etc.
There is nothing wrong with this approach again I would stress “in the normal course of events”. Right now, we are in the throes of massive uncertainty coming from many different sides including most notably the Global Pandemic and Brexit and while spreadsheet-based forecasting has its upsides, there are some obvious flaws that spring to mind:
The Time Factor – it would be a good exercise to work out how much time is taken up creating your financial forecasts and models. Time can be eaten up unnecessarily preparing, inputting, uploading, creating formulae, sharing and presenting your data.
The Accuracy Factor – one tiny error in the middle of a complex forecasting model can be embarrassing at a minimum and can cause all sorts of damage at its worst.
The Multiple Versions Factor – often multiple versions end up in circulation, requiring consolidation, users may “break” models with no audit trail causing big headaches for the Finance Director.
The Scenario Planning Factor – Spreadsheet-based models can often be cumbersome to change several variables and see both short step changes as well as long-term impacts on the business.
The Manual Factor – plugging in actual values for variance reporting involves CSV file mapping or manual data entry to fill the gaps (seasonality, Cost of Goods Sold and Sales Volumes). This can be time-consuming and imprecise.
The Detailed Factor – when it comes to forecasting based on different divisions, departments or companies, as the complexity increases, so too does the probability of errors and inaccuracies.
A More Sophisticated Approach to Forecasting
We would always suggest that having a centralised system with one source of truth with models that can’t be broken by end users, is the ultimate best practice when it comes to preparing your forecasts.
Having a baseline forecast with micro and global “What If” scenarios so that you can easily see the business impact is crucial. In these turbulent times, the decisions you need to make today may be very different to the decisions you need to consider in a week, a month or a quarter. Being able to predict, plan and model with precision with data you can absolutely bank on is critical for CFO’s and Strategists alike.
Throw in some really nice-looking data visualisations, benchmarking to compare your projections against the industry as a whole and artificial Intelligence to help you predict sales volumes, COGS and seasonality… now you’re talking.
Forecasting in the Future
Forecasting the future of your business depends on many factors:
- How much data do you have available?
- Where is this data held?
- How accurate is your data?
- What have you done in the past?
- How much time and effort are you willing to spend?
Instantly getting your hands on accurate and robust data will set you up for guiding your business through the chopping waters ahead.
- What if sales decrease by 25%?
- What if our COGS increases by 10%?
- What if our importation costs increase by 10%?
- What if demand for our services declines by 15%?
- What if we have unexpected exchange rate fluctuations?
- What if we reduce our overheads by 10%?
- What if we take a short-term repayment holiday on our bank loan?
It doesn’t all have to be doom and gloom either. How many opportunities are out there that didn’t exist 8 months ago? Modelling your data and working through your scenarios can help you not only survive but thrive.
Let’s also not forget about cash flow, the life blood of every single business. If you have your forecasting models nailed, then cash flow should literally flow out of it. You should be in a position to predict the impact a Hard Brexit or Soft Brexit will have on your bank balance or further lockdowns and restrictions in Q1 2021.
Our Tuppence Worth
Using specific forecasting software that integrates directly into your ERP/Accounting solution means your business forecasts can be updated as and when you need them and can provide you and your team with ongoing information to help with the day-to-day running of a business.
ProForecast is a cloud-based forecasting solution that can be accessed from any device, anywhere and at any time. Fully integrated with Sage 200cloud software, it provides the complete solution for financial planning, forecasting and cash flow.
To watch a recording of our recent webinar which gives a full overview of ProForecast, please click below:
Additional Resources:
