Forecast Financial Modelling – Forecast

Case study

Loan Reporting Model​

Real Estate Finance

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.

Client

Our client provides highly bespoke loans to the commercial property development sector and is backed by a leading global private equity fund.

Purpose

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.

Approach

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

Outcome

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.

Like this article? You’re one step closer to making data-driven decisions.

Reach out to us to start a conversation – we welcome an opportunity to understand your data needs, challenges and aspirations. We utilise an expert toolkit of data capabilities and services to help take guesswork out of your decision making.

See more case studies

Case study

Property

Consolidation & Refinancing Model

Case study

Financial Services

Estimating Rental Expense & Affordability

More Case Studies Contact us