I had the opportunity to support an investment management client by aiding them in rethinking their complex allocation processes. They had millions of dollars in back office operating expenses that needed to be equitably indirectly allocated across a myriad of front-facing fund P&Ls. When I first stepped in, their process was heavily manual, requiring more than 10 separate Microsoft Excel models and lasting more than three weeks to complete. By rebuilding and automating financial processes, I was able to scale down their allocations end-to-end to a span of two to three days’ duration within one cohesive model. I wanted to share some of the best practices I applied while supporting this client so that your business may too be able to benefit from streamlined allocation methods.
Focus on Shared Overall Processes Rather Than Allocation Methods
Your business likely has different cost and revenue buckets to allocate using inherently different percentages and allocation methods. For example, one bucket may be allocated by fund AUM, while another may be allocated by investor count. However, if you look at all the items to allocate from a high level, are the base processes virtually the same? For example, are the end receivers of your allocations (e.g., fund P&Ls) all the same?
If so, it is irrelevant that the base allocation percentages may differ as you can likely build a cohesive, all-encompassing model around the shared overarching process. For my client, I noticed that for all of their piecemeal Excel models, the end receivers (i.e., funds) were the same. This provided an opportunity for consolidation.
Consolidate Items That Stem From One Source Of Truth
If you can leverage one “source of truth” (e.g., a Trial Balance report) as the data basis for all items to be allocated, despite the allocations themselves being inherently different, then there should be a pathway to consolidate these allocation methodologies into one model. It is much easier to paste in one report output, such as a trial balance, and construct a model that looks up data from one source versus building separate models around different methodologies and pasting in the same source data multiple times. While working with my client’s team members, I discerned all of the source data for their allocations could be gleaned from one trial balance report. This provided a clear pathway to automate their processes.
Weigh Materiality Versus Automation
While initially examining the Excel models my client had been leveraging for allocations, I noticed that certain cost buckets were immaterial. Yet those buckets required a process that was significantly manual. For example, telephone-related costs amounted to approximately $30K per quarter. However, this cost was being spread across a multitude of P&Ls that had Net Operating Income balances in the millions of dollars. The original process involved researching individual GL transactions and allocating them based on a specific individual’s time. That is to say, if Employee A had a monthly phone stipend of $50/month, that $150 charge over the quarter was allocated based on how Employee A had spent their time across funds during those three months. This took a tremendous amount of manual effort for such a small expense relative to the size of the P&Ls that were receiving this cost. In weighing the cost-benefit of effort versus materiality, it made the most sense to find one default allocation percentage that could reasonably apply across all of the telephone-related costs.
Once I decided to automate the allocation, the next step was to leverage historical data to prove that allocating the costs using the automated methodology would have made no material impact. I ran the newly streamlined model with the automated allocation and compared what the percentage shift in Operating Income would have been versus what was actually reported. The result was a less than a 1% shift across any fund strategy P&L. In this case, it was clearly more valuable to the client to automate this allocation since the impact of doing so was immaterial.
When Should You Get Support on Allocation Methods or Start Exploring Automation?
If you need a third-party perspective on your financial reporting and accounting processes or allocation methods, 8020 Consulting can help. We have a team of 120+ finance and accounting consultants based in Los Angeles who are able to deploy across Southern California. Contact us to start a conversation.
We also have a checklist to help you explore the process of automation and system selection, which you can download below:
About the Author
Jaime has 12 years of diverse finance and project management experience in the real estate, entertainment, video games and technology industries. Prior to joining 8020, Jaime was the Director of Studio Finance and Operations at Activision Publishing, and had previously worked for WB Games, Warner Bros. Entertainment, Mesa West Capital and SL Green Realty Corp. Her expertise centers around FP&A, operational strategy, budgeting and forecasting, cause of change analysis, reporting, cash flow forecasting, asset valuation, process design and financial systems implementations. Jaime holds a Bachelor of Science degree in Economics from the Wharton School of Business at the University of Pennsylvania.
