Community Land Trust Fund

Delivering Affordable Sustainable Housing
How to use the workbook:

Outputs from the Model

Once all above the entries have been completed, 4 sheets are automatically calculated:
•    Key KPIs
•    Summary
•    Cash flow
•    Balance Sheet
•    I & E – Income & Expenditure Account

Key KPIs

Key KPIs screen grab

This sheet is designed to give a snapshot of the project. It shows 4 KPIs that give an indication of the viability of the scheme.

The first graph is the Cumulative cash position which graphs line 45 from the development cash flow.  This is the net cash position after all income, expenditure and loan repayments. It is usual for this to be negative in the early years and then go into positive figures.

The second graph is the Overall Debt.  This is the sum of the cash position plus the outstanding debt less repayments. In effect this shows when the project has built up enough cash to repay the debt.

In the example shown, the debt is repaid in year 19 and interest cover is at 1.96. The overall cash surplus over 30 years is extremely positive. However, the scheme is in cash deficit in the first 5 years. This is a real challenge for all developers, especially where there are a high proportion of rented homes.

In addition the tab shows the overall cost of the development compared to the income and NPV of the rental streams.

Summary

The summary page is split into two sections. The first section shows the tenure mix and the total square metres of the dwellings together with the number of people to be accommodated on the site. The second section is a financial summary showing the development costs, income from the site, and the funding requirement.

Cash flow

The cash flow shows all the income and expenditure of the development. Income will be in respect of the development (Grants, Shared Ownership receipts, Sales receipts etc) and in relation to the long term revenue streams (Rents). Expenditure will also take into account the cost of the development and the ongoing cost of managing and maintaining the properties once they are built if they are to be rented out. In addition the loan repayments will be shown on the cash flow. In effect the cash flow shows the projected bank account of the organisation.

Income & Expenditure Account (I&E)

The Income & Expenditure Account shows the overall net surplus of the organisation. In other companies this would be known as profit. Many of the entries in the cash flow will appear on the I&E. The major difference is loan repayments. They are shown gross in the cash flow, as they are paid in full, but split between the I&E and Balance Sheet. The interest is shown on the I&E but the capital repayment is shown on the Balance sheet.

Balance sheet

This shows the Assets and Liabilities of an organisation. In this case the assets will be the homes retained and the liabilities will be the loan taken out to fund the development. The bank account balance would also be shown on the Balance sheet.