To automate your planning and forecasting or not to automate…

Equally, a business that’s been using Excel exclusively may still adopt automation for some functions while still retaining Excel for others.

If you’re thinking about an automation project, you likely have many questions, including:

• When does it make sense to automate any of the business functions you currently manage with spreadsheets?
• Do the business and productivity benefits outweigh the costs?
• Is it worth the effort needed to train users in the automation tools you choose?

If you currently use Excel for some or all your planning, forecasting and reporting requirements, and are curious or simply unsure whether you should investigate automation tools, we wrote this article for you.

1. To help you assess whether any of your current tasks justify an investment in automation.
2. To allay any concerns you may have on rebooting your organisation to use automation.

The Spreadsheet Evaluation Framework is a useful tool for assessing whether automation is a good choice for your business or not. As items increase in either business value or repeatability, their suitability for automation also increases. The quadrant in which an item appears provides a good guide to its suitability for automation. 

Note: The examples shown here are for an IT company similar to Fusion5. Your Spreadsheet Evaluation Framework may look different. 

When choosing between Excel and automation – you need to consider repeatability, business value and complexity.

• Many tasks are done monthly, even weekly, making them highly repeatable. By comparison, others may only be performed once.
• The less repeatable a task is, and the lower its value to the business, the more likely it is that Excel is the way to go.
• Multiple users mean things can get complex – very quickly! There’s more risk of unexpected changes and working on the wrong versions.
• Easily repeatable tasks that provide high business value but involve multiple users are great candidates for automation.

Why is that?

One reason is ease of use and familiarity. Most people know how to use Excel, even if only in a basic way, whereas automation tools require some training.

Even if Excel is not ideally designed for the task, if it’s able to deliver a meaningful result, most businesses would (and should) use it.

Sounds logical, doesn’t it? But that logic begins to fail when you have non-repeatable tasks that are also of high value to the business.

An example of this is a project plan. While a given project may not be repeated, many projects are critical to the business. Success can deliver great rewards, while failure may carry a high cost.

In these cases, accuracy and the ability to play around with different scenarios become essential.

Excel is not well suited to either of these things. Most spreadsheets have errors within them (one industry study puts the number at 90%), and Excel is not designed to allow users to test various ‘what-if’ scenarios.

The sweet spot for automation is where tasks are not only high-value, but also repeatable.

Here, everything that applies to project planning still applies. But there’s the added benefit that once a task is completed, the files that support the task can be reused when the task is performed next time, the time after that, and so on.

Forecast models are a prime example of such a task. Used to predict outcomes regarding sales, supply and demand, consumer behaviour and more, forecast models are used to allocate budgets or plan for anticipated expenses over a specified period.

They can help a business assess how trends may impact them, what might happen should a current trend stop, reverse, or accelerate, or what could happen should an event like another lockdown and the ensuing supply chain disruption occur.

Because forecasts are made repeatedly and, in many businesses, frequently, investing in an appropriate automation tool makes sense.

When the value of the forecast to the business is very high, that investment becomes a no-brainer. After all, you can’t hope to win an F1 race in a modified streetcar.

While business value and repeatability rule the roost when considering automation, the complexity of a spreadsheet and the amount of manual intervention it demands are also important.

Complex spreadsheets are more prone to human error than simple ones. Automation helps reduce those errors.

Similarly, spreadsheets requiring input from multiple users are also more prone to error.

People may not clearly understand how to enter data. They may make unauthorised changes to data or formulae or simply enter the wrong data. In all cases, automation again helps reduce errors.

An important exception to this discussion is compliance matters. While reconciliation reports and the like may be of low value to the business, the cost of making errors can be high.

Because they are also highly repeatable, such reports may also well justify using automation.

Planning analytic automation tools break down the traditional silos between business unit planning.

They unify data into a central repository and integrate data from diverse data sources such as ERPs, CRMs, and HRMs. This allows all users to access one governed database that acts as the single source of truth.

It enables a cross-departmental framework that streamlines planning and creates transparency, collaboration (high participation planning), and alignment across the organisation. And it does more than just plan – it analyses data, reveals trends, and allows real-time iteration for continuous planning.

Another bonus – all your plans, budgets and forecasts are created and stored in the same application, so your users are all working with the same data. Changes to one plan will automatically update and flow to other plans, and centralised business rules can be applied to all plans, as required.