Our client provides highly bespoke loans to the commercial property development sector and is backed by a leading global private equity fund.
Our client required a detailed financial model to accurately manage loans on an individual basis for a loan book with a value of c.£500m. Their existing model was both extremely large and unstable and was difficult to operate or audit as an inexperienced user. The model was required to model both the borrower loan and the inter-bank lending that funded part of the overall loan. Further challenges included the requirement for daily time series and the large number of bespoke features of the loan facilities with borrowers.
We subsequently worked with the client to build a suite of dashboards to provide the outputs of the model to the wider business and stakeholders.
Forecast worked closely with the client’s team to gain a detailed understanding of the funding model and the bespoke nature of the loans. As a result of the daily time series, Forecast required to focus on the calculation efficiency of the tool as a key challenge of the development phase, especially as the number of loans made by the business increased. The Forecast team devised an innovative solution where a single loan calculation sheet was used and then this was copied to a storage sheet via the use of a macro. The model outputs in turn refer to the storage sheets, significantly improving the performance and calculation speed of the model.
The Forecast-developed model:
- Modelled the complex and bespoke elements of each loan
- Had capacity for up to 50 loans to be modelled on a daily time series
- Included a number of new output tools for the client
- Allowed the client, for the first time, to forecast each loan individually in the granular detail required
- Included a suite of interactive loan dashboards that were developed in Power BI, meaning that stakeholders could view the outputs of the model without having to access the underlying Excel model
- Included best practice layout and formatting implemented throughout
- Provides a “golden source” of data for the private equity fund
The Forecast-developed model is now the central tool used by the client in the management and reporting of a significant commercial property loan portfolio. This solved a number of challenges for the client’s team who now have a robust and easy to use loan reporting platform. The subsequent addition of PowerBI dashboards allowed the data to be viewed and utilised by colleagues in the operational teams without those individuals requiring access to the complex Excel model generating the financial data.