Consolidation & Refinancing Model

Purpose

The client required a tool to consolidate their existing village development models to test multiple operational scenarios and various funding options.  The model was needed to support a process to refinance their balance sheet and provide funding for future growth.  Model requirements included:

• Consolidate multiple village development models, each with different time profiles, into one corporate model and enable future villages to be easily switched on/off depending on growth profiles,
• Add overheads, funding, tax and other corporate modules to create integrated group 3-way financial statements (including forecast fair-value investment property valuation),
• Develop a 50-year ‘Deferred Management Fee’ calculator with estimates on resident roll-overs, management fees and share of capital appreciation under various resident contract terms,
• Provide financial analysis on villa development, inventory and profitability,
• Calculate financial returns for all stakeholders, including the founders, investors and debt providers.

Approach

Working closely with the founders and their advisors, Forecast prepared a detailed 10-year monthly forecasting model that accurately reflected the structure of the industry and the client’s business.  We created an intermediary DataBook to consolidate the individual village files which then exported data into the new corporate file.  The inclusion of a scenario handler within the model was critical for testing future assumptions on development timing, market price and demand movements, and various funding options.

Outcome

Forecast presented the model directly to potential lenders and investors with the final result being a large debt and equity investment by a leading superannuation fund.

We continue to support the client to add model functionality and has incorporated changes such as:

• Full alignment with the client’s audit standards to enable monthly P&L and Balance Sheet reporting,
• Monthly cash management and village development loan drawdown documentation,
• Analysis of village acquisitions and overall company valuation under different growth scenarios.