Scaling data and modeling for a Capital Markets team

Growing volume, limited resources

The client’s Capital Markets team was highly resource-constrained, with growing deal volume and limited headcount. The team ran frequent tranches into complex capital structures, with tax equity, debt, and JV models all built in Excel.

Much of the team’s time went to querying asset data, copying and pasting it in and out of large, complex Excel models, and running macros that could take hours to finish.

Because the process was so manual, the team updated its JV models only periodically. When Corporate Finance asked for ad hoc updates, it could disrupt Capital Markets’ plans at any time.

Real-time visibility into unit- and fund-level economics was also becoming critical for pricing decisions in a rising interest rate environment.

A true strategic finance challenge

This was not a traditional FP&A use case, but a strategic finance challenge that impacted both Capital Markets and Corporate Finance with existential implications.  OVG’s Microsoft-based approach is uniquely suited to these types of challenges where CPM tools will struggle.

Strategic finance work like this needs to scale data and reporting while keeping accessibility of Excel for sharing information with external parties and running pro forma scenarios on the fly. Every partner arrangement has its own unique terms, so flexibility was paramount.

Asset-level cash flows in Fabric

OVG first established a data foundation for the client’s asset and operating data, connecting Fabric directly to its Snowflake architecture with the built-in connector.

OVG then built an asset-level cash flow model and a centralized assumptions framework in Fabric, giving the team a granular baseline it never had before. On top of those cash flow projections, OVG built a DSAB calculator that replaced a five-hour macro in the debt model with a script that runs in under five minutes.

With granular cash flows in place, OVG built Power Query connections that flow Fabric data into the client’s tax equity, debt, and JV deal models. The team kept everything it needed to execute within the models themselves, with expanded visibility for both the team and its partners.

Finally, OVG mapped and coordinated the entire process in a Capital Markets app that gives the team an intuitive, consistent experience for sizing, closing, and tranching new funds, and updating debt and JV models in minutes instead of days.

The results

With capital models now updated end to end in a fraction of the time, both Capital Markets and Corporate Finance can get more accurate, up-to-date information on economics and cash flow. The APM approach has saved the team time and set it up to scale with the business, while delivering the timely insight into economics that will be critical to growing profitably.

With the data and model architecture in place, the next step is a context engine that layers in the team’s institutional knowledge to further improve and streamline the process.

The Takeaway

Next generation CPM solutions much address strategic finance challenges with external touchpoints as well as they solve internally facing use cases like budgeting, HC planning, sales forecasting.