How to Save Thousands of Hours in Your Routine Processes

Why is this important?

Too often employees relent to spending hours on repetitive processes because they have to be done, and there is no way around it. We are used to it, even trained to accept it - consider being taught to brush your teeth each night as a child or organizing your work emails. Over a month of your life will be spent brushing your teeth, and you don’t want to know how much time you spend dealing with your inbox. In fact, the vast majority of your life is spent brushing your teeth, organizing your emails, driving to work, cooking dinner, cleaning, going to the bathroom, making the bed, etc. The clever constantly think about efficiency. They brush their teeth in the shower, and set up email filters to automatically organize their inbox. The benefits they reap are invisible but invaluable - greater productivity, capacity for more work, and additional heavenly R&R.

My point is, mundane tasks are everywhere, and if you’re smart, you reduce it. Now, as an employee or manager, think about how much time (i.e. money) your company spends doing repetitive analysis in Microsoft Excel. There is clearly an opportunity here, can you see it? Are you going to be clever?

What is the Solution?

The trick is using VBA (Visual Basic for Applications) in Excel to write macros that automate the process. If you’re familiar with macros, good. If you’re not, open up Excel and press ALT+F11. In the VBA editor, a user can write subroutines to automatically create, generate and save excel files, run vlookups (or any function for that matter), create, rename, organize and format spreadsheets, create pivot tables, generate and format plots, display and process user-created forms including drop-down boxes, text boxes, buttons, and more. Whatever you know how to do in Excel, it can be done using VBA, and then some. At the click of a button, an hour long (or even day long) process can be completed in seconds. This is powerful stuff, and deserves some attention.

Think of repetitive process that start with the same or similar spreadsheet. Examples include generating billing documents based off of client usage, analyzing routinely published data, and tracking or interpreting market data over time. Your company may have routine internal processes done within Excel as well, such as resource allocation tracking, spend analysis, etc.

When is a Macro the Best Option?

Developing a macro to automate a process with an Excel macro is just like any other ROI problem. On one hand, resources can be spent up-front to develop the macro, and once developed, much fewer resources will be spent on the process. On the other hand, the process can routinely be done manually. Whichever route incurs the least cost wins out.

What are the benefits?

When trying to determine when to develop a macro, some less intuitive benefits should be considered.

1. Quality Control
 

   
   Human error made when manually running a process is mitigated. This not only increases output quality but reduces risks associated with erroneous analysis. This is difficult to value.

   
   
2. Simplification
 

   
   The macro will not only speed up the process, but also simplify it. A junior analyst may be able to output what a senior project manager was once required to complete, at a much lower wage.
   
   
3. Developmental Improvements
 

   
   The more often macros are developed, the greater the developers speed and quality will be, reducing the up-front cost and increasing the long-term resource savings. Similarly, the complexity of macros employees can generate may increase over time, opening up new opportunities.

   
   
4. Standardization
 

   
   The more often macros are used, the more standardized processes may become throughout the company, increasing efficiency beyond the scope of the macro itself.

   
   
5. Employee Insights
 

   
   Often, the analysts performing the nitty-gritty analysis are most familiar with areas with the most improvement opportunities. You may be surprised what a employee with deep familiarity with both VBA in Excel and your company processes may be able to save you.