Summary
- You already have all the software you need in MS Excel (see PowerQuery if you don’t believe us) for Financial Month End Optimisation- no more licencing fees required.
- You staff are good and have the experience of month ends. No software will replace this. They are able to tell you where the REAL blocks are in the month end process and WE can help.
Our suggestion is that you let us:
- Assess your staff for their MS Excel skill levels
- Provide training in the practical areas required at a month end, BASED on their existing expertise.
- Sit with each staff member and see what takes the most time during month end. At this stage we will point out the quick wins for month end optimisation
- Build mini spreadsheets that solve the bigger problems that are perhaps outside their skill levels (see case studies below)
- Re-run an Excel training course, but this time using the company’s own data and the solutions we developed to share the knowledge.
Send an email to info@AuditExcel.co.za to discuss Financial Month End Optimisation further.
Table of contents
Background to current month end processes
Every company needs to do month ends. Every company struggles with how to optimise a (seemingly) simple process (click a button, get your results, explain them, back to the real business). Inevitably lots of processes are tried with lots of expensive software. But the chances are you don’t need much of this. You already have the 2 things you need. MS Excel and your people.
It is widely acknowledged that most companies do not get the best out of MS Excel. It is a brilliant tool that runs companies (whether you want to admit it or not). It is also advancing at a rapid pace with new tools designed specifically for financial (and other) month end optimisation.
Similarly, your people are inefficiently used, especially in conjunction with MS Excel. They have the real knowledge of how your various systems work together, where the common problems are, and have years of experience of finding the real reconciliation reasons.
Together, your people and MS Excel, are able to solve the month end problem. However, they are often stuck in their ways, and because of time pressures, don’t have the opportunity to sit back and say ‘I wonder how I can optimise the month end process and save time’.
You can now improve your month end processes by targeting the exact problem instead of treating each issue independently.
The Problem with Month Ends
Given the number of software systems that exist in any business, it is inevitable that you will be using MS Excel in some way to bring them together. In fact MS Excel is the most widely used month end software in the world. There will be people within your organisation who have had to figure out how to do it, and you probably have various (old) templates which (you hope) still work.
Your staff are good and they are spending hours getting these month ends right, but often in an inefficient way. They may not tell you this, but some of your staff are coming in early and leaving late at month end time, just to get all the work done. They are dedicated. What they often don’t realise and communicate, is how much of the time relates to automatable tasks. Too often the focus is on the end output, so staff are sent on a multitude of courses relating to the end- think Dashboard training. This is fine, but actually the majority of time is spent getting data ready to be put into a ‘pretty report’. Solve the initial data setup problem and the reports will solve themselves.
When we ask staff what takes the most time at month end, they often go into ‘finding of the reasons for the recon issues’ but forget to mention the hours spent getting the data into some sort of shape to discover the recon issues. It is almost as if it is a given that this slow way is the only way. This is often the area where the most time saving can be achieved. The actual process of understanding reconciling items involves speaking to other departments, finding errors and typos and other unique processes. All items that require some sort of human knowledge and experience.
What should NOT require human intervention is the cleaning and matching of the data. In some recent assignments, the longest part of the month end process was first finding the data that matched (and therefore did not need any reconciling). Once the matched items were removed the ‘month end’ could start. Matching items is an automatable process. But staff tend to do tons of manual VLOOKUP’s to get this right (which need to be repeated every month, or worse every time new data is sent because of an oversight). If you can click a button and immediately see the mis-matches, you can spend more of your time on the actual reconciliation process and less on the data cleanup process.
Financial Month End Optimisation- What we can do
MS Excel training is wonderful but generic. A normal course just shows you all the tools that it has but not where and when to use them. It is important that you understand the tools available, but for the most part the attendees don’t appreciate where they can save time.
We can build complex spreadsheets to assist in the process, but this is costly and outsources the knowledge of how the spreadsheet works and it becomes another black box.
Our process involves:
- Run Excel courses covering key aspects of Excel especially PowerQuery. The focus will be on tools that help with month ends i.e. quick ways to clean the data, automate repeated tasks, most efficient ways to compare reports/ lists (it is NOT VLOOKUP).
- During the courses identify (your staff will do this) areas where they waste time and can see that there might be a better way. Simply put, anywhere where a staff member spends hours on mind numbing copy/ paste/ VLOOKUP/ repeat is an opportunity for time savings.
- Schedule time with each person to sit at THEIR desk and watch the process they follow, from the very beginning. In this mini workshop we can then match what they are doing to what they learnt, and in some cases build a mini tool to solve the real problem (see examples below).
- Finish off with another Excel course, but this time it will be based on what we have seen in the mini workshops and the exercises/ examples will be using the company’s own data and specific issues.
The key benefit of this approach is that you get the best out of the 2 things you already have, MS Excel and your people.
Your people are good, they know what they are doing, they are just not empowered to do it in the most efficient way. By solving each individuals ‘real’ problem, you will find that the month ends will speed up, not because the unmatched items are solved quicker, but because getting to the point of understanding is quicker and less mind numbing. More time can be spent on understanding the issues and less on finding the issues.
Example of Financial Month End Optimisation Quick Wins
Legacy Systems with (unusual) delimiter structures
The client was still using a DOS based system 20 years later. One of the problem reports had a strange set up when the delimiter was a combination of tabs and spaces. After the consultation we prepared a converter spreadsheet which brought the information into the spreadsheet instantly. Besides the month end time savings, it saved 20 minutes per day for continuous monitoring.
Multi Level Recons
Two systems should generate the same information. The initial recon happens on a primary key BUT if there isn’t a match, then there is a secondary text field that could be a perfect match. Only once these two matches are performed can we see where the real issues are. The previous process involved multiple levels of VLOOKUP’s with filtering to copy/ paste the matched items out of the report.