Excel-based process improvements: #2

Following on from my first Excel-based process improvement article (https://www.garudax.id/pulse/excel-based-process-improvements-1-ally-mitchell/), my next example covers an area where I constantly see finance functions spending exorbitant amounts of time – copying (or linking) numbers from one workbook/report to another.

I have never been a big fan of linking Excel workbooks together – the links can break easily if the linked workbook is even slightly modified; large numbers of external links are hard to manage; and they rely on the user of the worksheet having access to the linked workbook/s (I’m sure I’m not the only one to have experienced 20 warnings on opening a workbook saying multiple links can’t be updated).

Similarly, manually copying numbers from one worksheet to another is time consuming, can lead to human error, and requires knowledge of everywhere updates are required.

Where possible, my preferred approach is to automate the process using Visual Basic – replacing a tedious manual process with a single button-click. This has the advantage of providing much more time to spend analysing and interrogating the output, rather than preparing the report in the first place. In most cases, if the workbook the data is being pulled from is in a consistent layout, these processes are quick to put in place.

In addition to importing data from one workbook to another, another area where improvements can be made is to export the data into data tables or a database. This opens the opportunity to further interrogate the numbers in new and inventive ways such as visualisations, trends or analysis tables.

Giving a quick example: a company issued flash financial reports on a weekly basis, with the cumulative position also shown in the month-end management accounts. Previously, there was a highly manual and time-consuming process not only rolling over the weekly flash reports, but also copying over the figures from the last weekly report of the month into the management accounts.

The process developed provided a single ‘export’ button on the weekly report (this exported the data into a structured data table), and ‘import’ buttons to pull the data into everywhere it was required. Not only did this save time but, with the weekly report data now stored in a organised and controlled way, new analysis was available such as: trending weekly reports in a graphical format; or drilling down into the data in a more intuitive fashion.

If you have any areas where you feel too much time is being spent on a highly manual, procedural process which does not involve any judgement, then it can almost always be automated – and this is often much quicker to develop than you may expect. Please get in touch if you would like to discuss anything I’ve mentioned, or how I may be able to help you out.