Excel automation is one of the most requested consultation services we offer. Our consultants are experts in taking an existing spreadsheet/ process, and automating it so that, instead of hours spent building the spreadsheet, the hours are spent analyzing what it is saying instead.
Table of contents
What can be automated in Excel
This is typically in processes that are repeated on a periodic basis. We can assist with any process where the key outcome is:
- taking multiple sources of input,
- cleaning the data so that it is usable,
- processing and enhancing the data,
- and providing report (summary reports, exception reports etc).
The automation also allows you to run the process more often. So where you were reporting monthly because of the effort required, you will now be able to report daily!
Characteristics of spreadsheets that can be automated
If you find yourselves doing any of the below, then Excel Automation could reduce the time required exponentially.
- Repeating the same process every day, month, quarter. You know there is a better way but you do not have the time to figure it out. This could be management accounts, KPI calculations and/or operational metrics.
- You find yourself copy/ pasting data e.g. latest month data, below last months data so that you can compare to previous periods.
- Needing to update the spreadsheet for new items e.g. for new general ledger accounts that need to filter through to the entire spreadsheet.
- You have built recon checks to make sure you have updated everything correctly.
- You have multiple reporting systems that can’t talk to each other but you need the data to be combined (and you do it via copy/ paste).
How we automate Excel reports
As a rule we avoid VBA and macros. The idea is to create a report that, using Excel’s inbuilt tools and formula, does all the necessary calculations. The latest versions of Excel have tools that make the use of VBA unnecessary.
All our spreadsheets are transparent and open. We do not create ‘black box’ spreadsheets where you need to hope it is working but cannot check it. Anyone with reasonable Excel skills will be able to follow the process.
Capturing of information into Excel- automated
The most common problem is often the collecting of information in electronic format. Even when it is already electronic e.g. multiple spreadsheets, pdf documents etc., the process of getting it into a single database is time consuming. Using our Excel automation techniques, this can be done very easily, with a simple ‘refresh’, that will consolidate the data (while you go get a cup of coffee?).
Some examples include using a form that can be entered on a smart phone, tablet or computer over the internet and it will automatically (and immediately) update a central spreadsheet connected to multiple reports.
If you have multiple Excel workbooks e.g. monthly or divisional files, we can automate the process so that if you put the file in the correct folder, it will be sucked into the central information source. You can also run ‘What Ifs’ by including say budget files, to see what year end could look like (and then remove them afterwards). You could also have flash reports where you just overwrite the spreadsheet with the latest versions to get increasingly more accurate reports.
Even if you have multiple sheets within a single workbook and add a new sheet, say every month. This can be automated so that with a ‘refresh’, the new sheet is imported to the database.
Excel calculation automation
Another common problem is the actual calculations. We have seen clients have to manually add useful information to the input database. A simple example is where a new General Ledger account has been opened during the month but now the whole spreadsheet needs to be manually updated to include the new account in the correct places.
It is possible to automate the process so that the Excel spreadsheet will:
- warn you there are new accounts that need additional information,
- allow you to capture the updated information, and
- update the full automation once the additional information is included.
Once data is in electronic format, all other calculations should be instant and seamless. MS Excel makes this possible with clever use of its automation tools and features.
So if you ever find yourself doing any of the following, it can be automated in Excel:
- Combining different data types e.g. management accounts with operational metrics like tons processes, footfall etc
- Combining management accounts with budgets and forecasts for comparisons.
- Copy/ pasting blocks of data underneath each other.
- Adding new items into reports because it was new that month (e.g. new GL account, new building).
- VLOOKUPing data to bring through more descriptions.
- Creating historical files so that you can look back months or years for comparison purposes.
Automating the reports/ dashboards
Once the input data is electronic, and the calculations run by themselves, the reports will be automated. You will now be able to spend more time analyzing the results and making informed decisions!
Our Excel Automation Example Case Studies
Some of our recent Excel automation consultations include:
- Combining management accounts with budgets and forecasts for monthly variance analysis,
- Capacity constraint spreadsheets based on forecast success in quotes issues,
- Sales pipeline analysis based on expected timing and success rates,
- Quoting tool for complex yellow machine repair options,
- Tracking tool for progress through a manufacturing process with history archived for analysis,
- Conversion of pdf based data into exception reports,
- Convert timesheets into payroll data,
- Machine maintenance for complex processes with tracking of history (when parts have failed) and forecast of likely events (when they will fail again).
Contact us about Excel Automation
If you have a requirement for Excel automation services, send an email to info@AuditExcel.co.za.