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 most. There are many questions you may have, including:

• When does it make sense to automate any of the business functions you currently manage with spreadsheets?
• Do the 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 of your planning, forecasting and reporting requirements, and are curious or unsure whether you should investigate automation tools, we wrote this article for you.

1. To help you assess whether any of your current tasks might justify automation.
2. To allay concerns that using automation requires a complete reboot of your organisation.

The Spreadsheet Evaluation Framework is a useful tool for assessing whether automation is a good choice or not. As items increase in either business value or repeatability, their suitability for automation also increases. Which quadrant an item appears provides a good guide to its suitability for automation.  Note that 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 want to consider repeatability, business value and complexity.

1. Repeatability – how often you use an item

• Many tasks are done monthly, even weekly, making them highly repeatable. 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.
• More users can quickly add complexity, because with that comes increased risk of unexpected changes and users working on wrong versions.
• Highly repeatable tasks with multiple users, that also provide high business value, 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.

2. The greater the business value – the more automation shines

Where that logic begins to fail is with non-repeatable tasks that are also of high value to the business.

Take project plans as one example. While a given project may not be repeated, many projects are very important to the business. Success can deliver great rewards, while failure may carry a high cost.

Now accuracy and the ability to play around with different scenarios become important.

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.

Everything that applies to project planning still applies – with the added benefit that once a task is completed, the files that supported the task can be reused when the task is performed next time, and 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 might happen should a possible event take place (say another lockdown and disruption to the supply chain).

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

When their value to the business is very high, that investment now becomes more like a no-brainer. You can’t hope to win an F1 race in a modified streetcar.

3. The complexity and degree of manual intervention

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 prone to greater human error than simple ones. Automation helps reduce those errors.

Similarly, spreadsheets that require input from multiple users are also more open to error.

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

An important exception to this discussion is compliance matters. Here, while reconciliation reports and the like may not be of high value to the business, the cost of 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, allowing all users to access one, governed, database that acts as the single source of truth.

It enables a cross departmental framework that acts to streamline planning and create 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 of your plans, budgets and forecasts are created and stored in the same application, so all users are 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.