Step One: Defining the Accounting Process and Dependency on Excel
With these uncertain times, the importance of having the ability to work remotely and maintain staff flexibility has become an integral part of a successful practice group. For example, leveraging Florida staff that can work seamlessly with management in New Jersey is a competitive advantage for your practice group in the marketplace. The most efficient way to accomplish this is to build a standard workflow supported by business process automation that is implemented across all offices within the practice group. The lack of consistent, standardized work papers makes it challenging to grow your team. It may work while the group is small, but if you genuinely want to grow a practice that functions across multiple offices, it’s most efficient that all staff are trained on and work in the same set of work papers.
Another area that decreases efficiency is the manual data entry performed daily by staff. Manual data entry takes longer for a user, increases the risk of error, and requires quality sanity checks by reviewers. Maintaining and enhancing standard work papers can become a pain point of its own. All work paper updates need to be made to all existing variations of the standard work paper. Depending on your firm’s security, you may not be able to push updates to work papers automatically which can result in manual updates of tens to thousands of variations of the standard template.
Just so we’re on the same page, standard work papers are the excel files that tax teams use to calculate the data to fill out their client’s required federal and state tax forms.
Step Two: Increase Your Technology, Visual Basic for Applications (VBA)
All the issues mentioned above can be solved with the use of Excel VBA. VBA stands for Visual Basic for Applications. At a high level, Excel VBA is Microsoft’s programming language for Excel and allows a user to write macros that enhance their time spent in Excel. VBA allows with macros the ability to automate tasks, such as adding investor rows, hiding or unhiding columns, creating tabs, manipulating data, and doing all these tasks faster than a user would do so manually.
In the past year, I have assisted the Financial Services Tax and Audit practices in creating nine standard templates that calculate based on tax type and entity type. These work papers are accompanied by a set of macros to automate simple template updates, including adding individual and combined partners, hiding columns that have no data and hiding/unhiding tabs as needed. These work papers also include more complex macros, including importing PBC data, creating review files and automating the PBC workflow and data entry provided to us by our Admin partners. These address the team’s need to have standardized work papers to grow their practice.
Step Three: Further Enhancements with Macros
In addition to all this, VBA has been able to save us an incredible amount of time in two areas:
1.A macro that creates imports into our tax return software.
2.Macros allow me to cycle through a folder of files and push updates/enhancements to multiple variations of our standard work papers.
With the click of a button, macro cycles through the data entered and creates an import that allows the user to push data into the tax return software for over 1,000 fields, many of which were manual data entry fields in previous years. This eliminates user data entry, decreases chances for error and increases engagement efficiency.
Maintaining nine standard variations of work papers can be a cumbersome task. Each file can have a different number of columns, tabs, and calculation methodologies, but all nine variations ultimately push the same required fields to the 1065 Tax Return. Writing a macro to update all nine variations dynamically saves hours in the update, test, and review processes of these changes. In addition to this, any engagements that have already been started before this update can also be updated dynamically using the same macro. This ensures that all live templates are up-to-date and function as intended.
Step Four: A Business Case for Robotic Process Automation
If you are working on a team that utilizes Excel as your primary data calculation source, I would strongly recommend implementing VBA macros into your workflow to increase efficiency and productivity in your business processes. Doing so will unlock many hours of billable work for your tax engagement teams and will allow them to use their time more effectively being trusted advisors for their clients. From work paper setup to data entry to import automation, the possibilities for adding automation to your workflows are achievable and Excel VBA is a great way to implement automation in your Excel workflow. Although excel VBA is a programming tool withstood the test of time, it also lays the framework to implement newer technologies, such as Robotic Process Automation (RPA), which can automate workflow across multiple applications.
Author: Joseph Dente | [email protected]
reach out to us should you have any further questions.
Technology and Digital Transformation Services
