For this example, I am using an audit of a financial services client that operates 50 funds. I am going to automate the 2021 partners’ capital schedule for 1 fund. However, automation can apply to any type of client following my methodology below. If you are interested in learning more after reading this, message me on LinkedIn.
Background
Think about the workload of a public accounting firm. To perform the work, the manager or senior associate requests the client’s files. These files are typically the exact same each year unless the client changed accounting systems or administrators. The audit procedures are also generally the same with perhaps several changes to reflect new guidance issued by the firm or regulatory bodies.
A new associate is assigned to do the manual work of copying and formatting the client’s files to the audit workpaper. The associate then manually updates each of the formulas in the workpaper. This is the first time the associate has seen the client’s file and prior year workpaper. The associate spends 4 hours looking for the right data in the client’s file to fit into the workpaper. The manager and partner then review the workpaper. They think it is correct but can’t be sure since every formula was manually updated.
How can they be positive all the sum formulas are referencing the correct cells or that the source population is complete? It would take forever to review every formula, so they look for big picture issues and sign off on the audit opinion. 1 fund down and 49 to go…
Methodology
To automate or not – if you can answer yes to these 3 questions then you should automate:
- Are the client’s files the same year over year?
- Is the workpaper the same year over year?
- Is there a large volume of work?
For my example:
Are the client’s files the same year over year?
Yes. All the funds use the same administrator who has not changed the source files in the last 10 years.
Is the workpaper the same year over year?
Yes. All funds have the same audit workpaper since they are all substantially the same – the main difference being the investors within each fund.
Is there a large volume of work?
Yes. There are 50 funds to audit.
When thinking about question #3, you can also think about volume in terms of time periods. So, if you have had a client for 10 years, you could have automated 10 years’ worth of work.
Example
I am going to walk through a simple example of the automation for the partners’ capital workpaper for one of the funds. Our goal is to tie out the statement of changes in partners’ capital within the financial statements.
Template
Take a look at the NAV Template file. This is our template file. Instead of copying the client’s files to the prior year workpaper, the client’s data will be output onto this template file. This template file will be used for all 50 of the funds.
Creating the template file is the most important step in the automation process. It needs to be robust and capture all necessary information about the client. Remember this golden rule when creating the template file: It is always easier to take away information than it is to add information.
Do 10 of the funds not have management fees? Don’t remove the management fees column. Keep it and these 10 funds will show zero for management fees. Showing zero is the same as removing the column entirely.
Automation
Open the Client PCAP file. This is the client’s partners’ capital file. Note the structure of the file. There is a total of 7 unique investors listed in column D. The monthly activity type and amounts for each investor are in columns A and B.
Yikes. The client’s file is in a terrible format to audit. How would you audit this traditionally? You would need to manually transpose the data and put the monthly activity per investor in separate columns. How many new associates would know how to do this? Do they know how to use the transpose option? Do they know how to use SUMIF? Simply put, this client file is a nightmare.
To automate the client file, I am going to use the software, Alteryx. This is my TOOL, akin to a blacksmith and his hammer. With Alteryx, I am going to input the client’s file, perform a few transformations, and output the data to the template file.
Check here to learn more about the software (I am in no way affiliated with Alteryx).
https://community.alteryx.com/?category.id=external
Here is an image of my workflow. This can be subdivided into 3 steps.
Step 1 = Input
Step 2 = Transformation
Step 3 = Output
Step 1: Input
For this step, all I do is connect the client’s file to the software based on where it is saved on my file explorer.
Step 2: Transformation
I use a tool to transpose the client’s file so that the monthly activity per investor is in separate columns.
I use another tool to add a new identification column. This will split up the data so that it outputs into the correct monthly tab of the template file.
I separate the data into different segments so that I can output it onto the correct column within the template file. Here, I am grabbing the partner code, partner name, and previous month gross capital columns which will be output to columns A – C of the template file in the monthly tabs.
I add a column with my full file path of the template file so that the software knows where to output the data.
Step 3: Output
I output the data to my template file.
Output
Open the NAV Completed file. Look at each monthly tab. Everything has already been recalculated and difference columns are all showing zero. Does the table on the top of the summary tab tie to the statement of changes in partners’ capital on the financial statements? Yes!
Before it took 4 hours to manually complete this workpaper and we just did it in 20 seconds. The manual work is completely gone and partners can now rejoice that they did not miss anything during their review!
Interested in learning more about automation and how you can use it for your job or firm? Send me a message on LinkedIn and I’d be happy to talk.