How to get the most out of your automated forecasting cycle

Automation is great. It can remove repetition from your work load, increase the speed of reporting cycles, it always sticks to the rules it’s given and it can provide a sandbox to forecast numerous ‘what if’ scenario’s based on varying conditions rapidly.

However, there are many potential risk heavy areas where a project can fall down if automation design is not well thought out.

There are a number of principles to consider when designing an automated forecast within your system.

One version of the truth

When dealing with monthly actuals it is tempting to make adjustments to them there and then in excel to get immediate what if analysis. In practise this can cause all sorts of issues when consolidating end of month reports included dreaded re-work. It’s best to leave actuals as actuals and utilise a multidimensional reporting tool. Copying across your actuals into a Forecasting member before performing any calculations on them allows comparison and prevents accidental deviation in end of month reporting.

Let the computer do the grunt work, and leave the decision making to yourself

Computers are great at repetitive simple tasks and following logical steps. It’s very difficult for a computer to be creative and understand your business. By all means lock down your complicated financial equations into the system but don’t try and get your infrastructure to think strategy.

IT solutions have no concept of indirect outside forces that impact business. Unless you can find a way to explicitly programme complex economic concepts in a timely and cost effective way, it’s best to leave these considerations to a human.

The job of your forecasting solution is to present the clearest picture of your financial position going forward and indicate possible trends so you can make the best decisions from it, not make decisions on your behalf.

“One machine can do the work of fifty ordinary men. No machine can do the work of one extraordinary man.”

Elbert Hubbard

Identify Core Business Drivers

There is no benefit to be had from including every source of data available to you and writing complex algorithms that drive your forecast, if the information isn’t relevant.

Complexity does not equal accuracy.

Review the core variables that you know drive your business, then:

  • Prioritise them in terms of importance
  • Review the time cost of build VS the value received in accuracy
  • Omit complex to implement cost/income drivers that have marginal impact on your forecast
  • Speak to cost centre management to understand what they identify
  • Use only business drivers that you know are relevant and worthwhile your time and investment

If you’re unsure if a variable is suitable to base your forecast on, there is no harm in using a regression calculation to see how likely it is to have a close relationship with your actual past performance.

Use robust, maintainable and well documented calculations

A low maintenance system that is simple to support is almost always going to be more successful than one that is neither of those things.

When writing calculations:

  • Keep complexity low where possible, simple is almost always best
  • Annotate with relevant notes and ideally a title and description note inline
  • Be mindful of end users, support staff who may have to delve into the back end on occasion
  • Use well documented naming conventions and provide a detailed design document
  • Format consistently
  • Keep core variables at the top of a script
  • Split long complex scripts into task based smaller scripts and schedule them to run in a process (By doing so it’s easier to identify where an error may be occurring/process failing and it makes it much more readable)


Multi-layers forecasting infrastructure

(Please note best practise dictates that if it’s possible to run a full forecasting process swiftly in the current system, it’s best to adjust the initial actuals and do a full run rather than make adjustments. However, due to restraints in time and the ability to view snapshots of the financial data throughout the process, many enterprises decide to incorporate an adjustments mechanism into their solution. If managed properly by means of auditing and saving notes upon input it can be a viable and effective solution.)

Consider using automation to seed the base of your forecast and use it as a platform to build a realistic picture upon.

When designing the start to end forecasting process consider allowances for manual adjustments at suitable intervals. In a perfect world, no transactions will occur between generating a forecast and the reporting of that forecast to stakeholders.

However in the real world arbitrage is always a possibility and having the ability to manage that process manually gives you the ability to manage that risk.

If you are going to use manual amendments, it may be worth partitioning them in your system into two categories;

  • Updated/unaccounted for financials/pending transactions
  • Known unrecorded future sales/events outside the comprehension of the system that will affect your business

Make sure amendments are separated, audited and require a descriptive note from the calculated figures so you can easily review who made the adjustment and why.


Standardise Data Collection

It is a common complaint of finance departments that they spend a lot of time chasing cost centre managers to submit their monthly budgeting and forecast reports. Even then it’s easy to spend hours manipulating data excel to get it to consolidate down into one set of numbers.

Make the most of the systems capabilities and use it to collect data into a central location in a way that is;

  • Easy to Audit
  • Standardised
  • Consistent in format
  • Understandable
  • Straightforward to use

By simplifying this process it places accountability on managers to submit in a timely fashion and reduces risk around data collection/consolidation.

Systems such as SAP BPC allow you to review current process flows and see who in the data consolidation process is causing delays so they can be informed to expedite their contribution.

BPC also allows for this sort of functionality, where input schedules can be distributed via an embedded excel spread sheet.

This provides easy distribution and data loading functionality, and familiarity without the intimidating task of getting to grips with a complex new GUI.

Consider the Automation Paradox

As Josh Kaufman detailed in ‘The Personal MBA’ (a great general business read) the paradox of automation is that the more efficient an automated system is, the less human input is required, but the human input becomes more critical.

Errors (caused by poor design or incorrect data input) in a highly automated system will be multiplied exponentially, therefore it is essential to invest enough time into your initial design and testing phase to reduce this possibility.

Going live on a system that requires constant attention, maintenance and regularly spurts out unrealistic figures will be infuriating for end users and defeat the point of automation.

In summary, limit the risks of automation paradox by:

  • Keep things as simple as possible whilst maintaining functionality
  • Don’t be afraid of investing a little more time in development, it pays dividends later on
  • Get the right talent involved in the project and keep them involved in on-going operating and maintenance
  • Document in detail and throughout
  • Testing, testing and more testing


Contact Us
Contact Us

For any questions please feel free to contact us.