How we helped a private equity firm build a real estate portfolio cash flow model for analysing its performance

The Context

The client, a Private Equity firm, wanted the TresVista team to work with the CFO and the business development head of the client’s real estate portfolio company to prepare a cash flow model as a part of their annual business plan, to track current and future performance. The client primarily wanted the team to create an assumptions section, cash flow statement, funding details, and a valuation summary for four types of assets which included land sales, hospitality, build to sell and build to lease assets. Moreover, the team was also required to perform a two-level roll-up, where a summary tab was created for each project which was further rolled up into ‘Sources & Uses’, ‘CAPEX Summary’, and ‘Annual Cash Requirements’ tabs.

The Objective

To prepare a consolidated real estate cash flow model that performs a two-level roll-up to track and analyze its future performance-making outputs in such a way that it can be presented directly to the board on updating the assumptions.

The Approach

The TresVista Team followed the following process:

• Created individual asset tabs for land sales, hospitality, build to sell and build to lease with a specific set of assumptions and independent calculations
• Replicated tabs if the project included more than one hospitality, build to sell, or lease assets
• Created project summary by using excel functions that pulled data from the asset tabs
• Pulled values from the individual project summaries for the 2nd level of roll up to consolidate the ‘Sources & Uses’, ‘Capex Summary’ and ‘Annual Cash Requirements’ tabs

The Challenges We Overcame

The major hurdles faced by the TresVista team were:

• The base files sent by the client which included old models of the assets had a lot of value inputs and manual intervention
• Evolving nature of the model structure as a lot of futuristic projects had to be modeled and this kept changing with the inclusion/exclusion of multiple projects
• Limited access to quantitative information on a project which was a Joint Venture (JV) with an external entity

 

The team overcame these hurdles by maintaining constant communication with the client over calls that gave the team a good background about the project and helped in understanding what type of calculations and uses they desired for the model. The client also provided the master schedule for their land sales and off-plan development which had granular information related to the projects which supported the output.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The model built by the TresVista team was dynamic with numerous options to give the client the flexibility to change inputs according to their preferences. The team also built a land rate calculator based on the down payment and payment plan with the help of a macro and goal seek function. Moreover, the team added a switch for leasing projects, wherein the land could either be selected as an upfront injection or it could be leased out over the years with the NPV remaining the same in both cases.