At SNS, we build a lot of Excel workbooks that serve as “process accelerators” for finance and accounting processes. These are workbooks that, if laid out well, take advantage of no-code data automation techniques in Excel to get things done on a semi-automated basis, which can take certain processes from hours to minutes. While there are plenty of fancier tools to accomplish these things – some of which are quite magical – sometimes the most efficient solution is good old Excel.
We thought we would share some basic thoughts for anyone who has ever thought, “If I only had time, I could stop doing this procedure over and over again just to get a weekly number or two,” or something we hear a lot, “payroll accounting journal entries take me forever!”
Rows and Columns are the Starting Point
Process accelerators tend to grab data from one or more data sources and use it to produce some kind of reporting. The secret to making that data useful is to export it in simple row/column format. A table is the best. Once you have data in row/column format, then you can do whatever you want with it (more on that later).
As you build your process accelerator, the source report or reports are what makes the update process – an important time-saver – simple. Just pull the very same report from the system – maybe as of a different date – and paste the new data over the old data.
Next-Level Excel Techniques are the Skills to Have
If possible, you can use Next-Level Excel formulas in a different tab to pull the right info from the source report or reports to give the answers that you want. Learning just a few formulas can go a long way, and it is pretty hard to Google an Excel issue without finding a formula that can handle it.
Sometimes, however, it takes a little more juice to get the data into a decent format for reporting. Maybe information from two different systems (now sitting in two tabs in your Excel) need to be combined. That is when to call on Power Query to model some data.
Data modeling is as much an art as a science. It takes creativity to think about something like, “How would I need those tables to combine so that I can build a report off a single table?” Fortunately, there are two amazing things going for a finance and accounting professional who wants to learn: (1) Power Query has buttons to do just about everything, and (2) there is a ton of information out there if you Google it.
Iterate on the Updates
You work for a small business, so the testing team is not there to help you (because that team doesn’t exist). Do not stop because there are errors on the first try, and the “accelerator” only speeds things up a little bit. Fix them. Keep fixing them. Eventually you will run out of errors to fix and you will be feeling uncomfortable because it should have taken longer to accomplish everything that you just accomplished.
Concluding Thoughts
Learning these tools takes a bit of time, but your tedious recurring processes are perfect labs in which to learn. So, pick something that only takes you twenty minutes a week, but you hate. Start tinkering with how you can pull a row/column report, paste it into an Excel, and have Excel do the work. You will be learning techniques that will give you ideas for how to improve the bigger issues. Soon people will be coming to you for help, which is why the accelerator is the hidden hero.
