BPM’s Data Analytics Services team successfully implemented process automation for one of its largest Family Office practice clients. Learn about how they achieved significant improvements in efficiency and cost reduction for complex month-end close operations.  

Challenges 

A large private investment company backed by a Family Office faced several critical operational challenges: 

• Time-intensive manual processes: Month-end close tasks required 8-10 hours monthly, risking accuracy and straining resources. 
• High staff turnover: Frequent personnel changes necessitated continuous training of new team members  
• Complex financial integration: The month-end close task for recording investment balance changes in Sage Intacct, a cloud-based financial management software, was burdensome. 

Month-end close process (pre-automation) 

The existing process consumed eight to ten hours per month and involved: 

1. Receiving supporting documentation from the client 
2. Preparing the file into a usable format 
3. Mapping new investment accounts or location 
4. Calculating changes between beginning and ending balances for each investment 
5. Preparing a journal entry in a template format for Sage Intacct upload 
6. Importing the entries  
7. Reviewing posted entries for accuracy in Sage Intacct 

Kris Marney, BPM Director, recognized the need for automation and engaged the Data Analytics Services team to streamline the month-end close task of recording changes in investment balances month over month into Sage Intacct. 

Solution 

BPM’s Data Analytics Services team initiated the project with a comprehensive process review and transition planning meeting. The team strategically chose to develop the automation solution within Excel rather than Python. This approach ensured the engagement team could maintain ownership of the month-end close process. 

The development process took approximately 12 hours, including follow-up consultations and performance optimizations. The resulting VBA program operates in the background, requiring only that the engagement team select two source files. The automation not only generates the journal entry for Sage Intacct import but also identifies any new accounts or locations requiring creation, preventing import errors due to unmapped data.  

Results 

The automation project yielded significant measurable improvements: 

1. Time savings: Reduced work time by over 90% (from 8-10 hours per month to just 30 minutes). 
2. Cost savings: Generated total annual savings of $27,000 – $34,000. 
3. Resource reallocation: Freed up staff time for higher-risk areas of the account. 
4. Simplified training: Significantly reduced new staff training requirements. 
5. Reduced knowledge loss: Minimized impact of staff turnover by reducing reliance on institutional knowledge.

Ongoing innovation: Weekly bank transfer automation 

Following the successful completion of this automation project in early 2024, BPM launched a new initiative in July 2024 focusing on weekly bank transfer activity related to interfamily notes. The team is exploring bank API integration to automatically record transfers into Sage Intacct with correct account and dimension mapping. This solution addresses the limitation of Sage Intacct’s direct bank feed availability and helps minimize the risk of human error. 

Additionally, the team is developing a VBA-powered Excel solution to generate journal entry templates for note interest accrual, leveraging Sage Intacct’s API for direct posting. This new automation initiative is expected to reduce weekly processing time by 90% (from 10 hours per week for two staff members to one hour or less), while significantly improving accuracy and compliance. 

Transform your Family Office operations 

Explore how we can help your Family Office achieve results through targeted process automation, contact us today.