Excel-based process improvements: #1

Do you, or your employees, ever dread completing certain tasks due to their repetitive and monotonous nature? With the majority of financial reporting falling into regular cycles (perhaps weekly, monthly or annually), this means that accountants are regularly responsible for completing the same tasks time after time, and if these processes are inefficient or largely time-consuming, then motivation, productivity and work-life balance can suffer.

Throughout my accounting career to date, I have consistently taken an interest in identifying weak and inefficient accounting processes, and developing new ways of working - all with the aim of making people’s lives easier. This is particularly true for Excel based accounting processes, which still play a significant role in the finance function of most businesses – even those using large ERP systems such as SAP or Oracle. In this series of articles, I’d like to share some of my past experience and highlight examples of process improvements I have implemented.

If any of these insights help to inspire ideas of where your business could potentially make improvements, please don’t hesitate to get in touch.

----------------------

Invoicing of rechargeable timewriting

In a previous project accounting role, I took responsibility for a project which provided a collective of six clients with full-year access to a Dive Support Vessel (DSV), and a dedicated engineering and support team. The revenue generated by this project generally fell into three areas:

• Vessel time
• Rechargeable timewriting
• Rechargeable costs and expenses

While vessel time generated the highest proportion of revenue for the project, the highest proportion of the project accounting team’s time was being spent dealing with the rechargeable timewriting. In particular, one member of the team was spending almost their entire working month on the preparation of timewriting invoice backup. Added onto the other accounting processes required, this was not only resulting in the project falling behind on invoicing, but in the panic of trying to catch-up, increasing amounts of invoicing errors.

The process as it previously stood involved the following:

• Downloading a system report of all timewriting booked to the project
• Using a basic lookup to compare the current month’s report to the prior month’s report on a line-by-line basis and extracting only the new items into a new report
• Manually splitting the resulting data into multiple tables – one for each sub-project*, of which there may be up to 50 open at any one time
• Creating a pivot table for each sub-project to summarising the hours booked by each employee each week
• Copy and pasting the resultant pivot tables and manually adjusting these into a suitable invoicing format

* Each sub-project was an individually defined workscope for one of the six clients, with each being invoiced separately.

Any process in Excel which is highly manual but largely procedural and repetitive, such as that set out above, can almost always be automated within Excel using Visual Basic. Visual Basic is a computer programming language, more commonly known in Excel as being the language used by the ‘Macro Recorder’. The ‘Macro Recorder’ allows users to record a series of mouse-clicks and key-presses, and recall these exact steps again in the future through running the saved ‘macro’. While this can be used for simple tasks such as applying simple formatting to a selected cell, it is simply not suitable for automating processes which are more than just a few clicks, due to some fundamental limitations:

• Inability to utilise proper object orientated programming concepts such as loops or repeating statements
• Lack of error handling
• Inability to use dialog boxes or user forms for user input
• Code is less efficient than writing it from scratch
• Local settings or user options tend to be taken into account – so distribution is limited, or can result in errors
• The recorder picks up everything the mouse and keyboard does, so if any clicks or key presses are made in error, this is recorded and you may need to start again

As such, any more complex processes must be programmed directly in the Visual Basic Editor.

In the above example, a new process was developed and coded whereby the current and prior month timewriting reports could be pasted into two Excel tabs, and a button clicked to initiate the Visual Basic procedure. This automatically completed the entire process, which previously took almost entire month, in a matter of minutes. In addition, due to the programmatic nature of the new process, there was no chance of human error. Not only could invoicing be brought back up to date, customers no longer had any queries to raise regarding the accuracy of the invoice backup.