top of page
Sontai Blog Background.png

How to save hours with automation.

If you’re currently running your business reporting in Excel, there is a high possibility that you’re spending a lot of time repeating the same steps day after day, week after week. In this post we are going to explore the areas in which moving your reporting to the cloud can help you introduce automation into your processes, ultimately saving time and increasing accuracy.

What do we mean by automation?

Automation is simply the reduction in the human interaction in a process or procedure. In the world of business reporting and data analytics this means the automatic repetition of steps in a process, consistently every time.

In the real world this looks like the removal of manual steps in the process of updating your reports that you can teach your software to do.

For example, if your reports are updated every week from a system or database, where you extract the data, then move the columns around, maybe replace some descriptions that need to be amended to make the report more readable? Maybe you have to pull in several tables, adjust the ranges, make sure that the formulae that you have built are still working?

All of this can be set to be done automatically, removing the need for these tasks to be done every month.

Direct Data Connection

If you are building a report in Excel, then it’s likely that you’re extracting data from a system in order to populate your report. This could be in the form of a CSV file that you download, then copy and paste into your report.

Before we think about whether you need to manipulate your data extract once you have added it to your report, there are benefits in automatically connecting to your data.

Through a direct data connection, you will see improvements in:

  1. Speed - as you don’t need to log into your system and download a file

  2. Accuracy - as you don’t have to worry about whether you have copied all of the data, or if there was a problem with how you pasted the data in.

Even in Excel you have the ability to connect directly to many data sources which can pull in the required data automatically, but this is definitely something that you should be doing if you are going to migrate your reporting to the cloud.

Data Processing

Once you have downloaded and imported your data into your reports, its more than likely that you then spend time making changes to the structure, the layout, and maybe some of the contents.

This is something that people play down as a task as they are just so used to having to manipulate their data as part of a report update that they do it on autopilot and don’t always realise how much time it can take every time they update their reports.

Historically some of the data processing was automated in Excel by macros, that simply recorded a set number of steps and repeated them every time the macro was run. From experience, I know that most people don’t like macros as they have either inherited them and don’t know what they are really doing, or they break halfway through an update because the incoming data has changed. This is both frustrating and time consuming to fix.

I quite often liken a broken macro as a wound that needs to be attended to. When these macros go wrong, they tend to the wound and put a plaster in place, hoping that that will stop the bleeding. Then, when something else happens, another plaster is applied. I’ve seen first-hand this process continue for years.

The problem with always putting a plaster over the wound is that you solve the problem for today, but the issues keep reoccurring. The real fix, and what is really required, is surgery. In this situation, the surgery that needs to be applied to your reports is the rebuilding of the automation.

If you either have no automation at all in your reporting updates, or if you have an outdated macro that needs to be rebuilt then you should use automation tools to update and improve your reports.

In Excel and in Power BI, this data modelling and data manipulation is done through Power Query, which would record a series of steps that you apply, such as moving or removing columns, changing titles, and formatting. These steps are then applied in the background whenever the data is refreshed.

Automating these steps has one obvious advantage, that you don’t have to spend the time making the changes every time you run the report, but it will also help increase accuracy. The reduction in human interaction will reduce the human error too.

Additionally, within tools such as Power Query, you can see the steps that are being applied, in simple easy to understand language, rather than in code (although the code is available in Power Query should you wish). This means that in the event that there is an error in the data processing you can not only see which step went wrong, but quickly understand what the fix would be.

Report Sharing

One of the tasks that many people don’t really factor into the time that they spend updating reports is the time they spend sending emails, with attachments or links, to tell everyone that the reports are available.

When you move your reporting to a cloud based solution you can automate email notifications to users to tell them that the data has been updated, or that certain numbers have been met, targets achieved or red flags raised.

The exact types of communication that you can send will depend on the business intelligence tool that you use, but all of the top tools offer some kind of email communication.

No more worrying that you sent the wrong attachment, or that the link to the file isn’t working.

Additionally, all your report users can know that they can access the cloud reporting from anywhere, and that the data they see is the most up to date.

How much time can you really save?

The amount of time that you can save through automation is dependant on the number of reports you have and their complexity. But even if your reports are simple and only run on a monthly basis you can still save time through automation.

In Part 2 of our Cutting the Cord series, we looked at understanding the return on your cloud investment. In that article the main saving that could be made by moving your reporting to the cloud was the saving in time through the automation of report updates and processing.

In that article we estimated that there was a 75% reduction in the time spent updating reports, based on the automation of tasks, with the remaining time being spent on checks and some communication.

For most people who update reports this time saving would be hours per month. Time that could be spend doing other tasks and adding other value back to their role and to your business.

Up next:

Next in our Cutting the Cord series - How good data visualisation can improve your analysis.


bottom of page