Eliminating manual Excel processes

I had again the opportunity to improve processes for a Financial Services department with a heavily used MS Excel practice. 

The team managed in cooperation with the client to unravel their Excel puzzles despite the fact that there was no face-to-face contact with the client due to Covid-19, so everything had to be done from home office with all the known drawbacks (yes there were these remote interviews in Team sessions of course with noise of children in the background, international internet connections….) 



Because of the monthly cycles of the department there was only limited time for explaining the purpose of the Excel workbooks and how they work with it in their daily working life. The good thing was that all explanation sessions were recorded, so I could review the video’s on a better time. The analysts explained their tasks and showed these in the used Excel spreadsheets. 



So what do these analysts do? 



Often they copy data from an incoming file or mail into a preformatted Excel sheet, do some manual actions, do some analysis, add some other data and then send the Excel to another department or system for further processing.



Because of the complexity of some spreadsheets it was not always possible to immediately understand what was going on in the Excel workbook. Reasons for this are of course the usage of complex formulas, VLOOKUP’s and hidden cells. Copying new values in cells often has a huge impact whilst it is not visible what is happening behind the scenes.



The business analysts that use their Excel solutions on a daily basis could of course explain all the steps that should be taken, but are not always aware of all the functionality that is under the hood. Not in all cases the steps taken are properly documented (=understatement 😉).



All functionality including the hidden one had to be transferred to a better process using Alteryx Designer. Solving this puzzle was done by following the steps that are taken in the Excel process. Understanding the structure of the Excel, understanding why steps are taken to get from input to output. The complete business process is at stake here. Often it proofed possible that inputs could be acquired from a more direct source.



As a result Alteryx workflows were created by which an analyst could easily understand what steps were taken subsequently. The auditing of the process became much easier. Running the workflows took in most cases a fraction of the time that had to be spent usually (really seconds instead of hours).



Not all users could be helped. The team encountered users that had built impressive integrations from their spreadsheet into presentation tools using a lot of Visual Basic programming. They were reluctant in changing this. Understandable but maybe not the best long-term strategy. Because quick wins were to gain in other processes we let that for what it was. 



Unraveling the Excel puzzles delivered:



• Dramatically reduced processing times (from hours to seconds)
• Reduced manual work (which is of course error prone and time-consuming)
• Standardized processes for better auditability



Learnings:



• Again it was clear that proper data governance could be helpful in being more effective assisting the client (a.o. knowing from what source the data is coming).
• The support of (video) recordings during the intake conversations with the business analysts proofed to be really efficient in the development process.
• Analysts that spend a lot of time in preparing instead of analyzing can be helped pretty quickly if they want.