Community Land Trust Fund

Delivering Affordable Sustainable Housing
How to use the Workbook:

Before you start

The workbook has been set up for new build development, for a range of procurement options and tenure types.  Works to existing buildings are too varied in character to be capable of incorporation into a standard appraisal spreadsheet, as they can encompass everything from an “existing satisfactory” house requiring only catch-up repairs, to conversion of a derelict non-residential building to houses or flats.  The different assumptions for works to existing buildings, in terms of works costs, on-costs and time-scales, do not lend themselves to a standard appraisal tool such as this.

However, the capital and revenue assumptions can be modified to create a workbook that is applicable to such situations.  Jon Watson Consulting would be happy to advise on these amendments, or CLTs’ existing consultants could do so.  Areas that require modification include:

  • Build costs, to reflect whether they encompass catch up repairs to existing residential properties, rehabilitation of poor quality existing residential properties,  conversions of either residential or non-residential properties, etc.
  • Professional fees, again reflecting whether the works comprise catch up repairs to existing residential properties, or virtual reconstruction of derelict residential or non-residential properties.
  • Phasing of expenditure through the development period, which is different from new build.
  • Revenue provisions for maintenance and building fund contributions through the appraisal term – existing properties tend to be more expensive to maintain than new build.

Before starting to complete the workbook you should review the checklist below to ensure you have all the information required in order to complete the appraisal.

Before you start

The following checklist shows the information you will require in order to complete the model.  Please note that this checklist relates to new build schemes – if looking at refurbishment or conversion, additional activities are required.  It may be worth printing off a copy to complete as you proceed.

Before commencing, check you have downloaded the correct model. There are two models:

  • One for organisations that are – or are seeking to be – Registered Providers.
  • One for non-Registered Providers.
Description Information complete
1 Have you selected the correct version of the model? Yes/No
2 Procurement type: Traditional, Design & Build or Off the Shelf Yes/No
3 Tenure mix and numbers of each tenure type including commercial elements Yes/No
4 Date initial purchase of site complete Yes/No
5 Start on site date Yes/No
6 Practical completion date Yes/No
7 Funding sources (excluding bank funding) – grant, local authority grants or other funding including community funding. Yes/No
8 Local Authority Yes/No
9 Year rental income will commence – for Social Rent schemes Yes/No
10 There are default values for the following variables. You will need to review these for your individual scheme:

11 Estimate of development costs split between land, build costs and on-costs Yes/No
12 Open market value of completed units Yes/No
13 1999 open market values of Social Rent units Yes/No
14 Size in m2 of each type of property Yes/No
15 Date of sale – for Shared Ownership, Shared Equity and Outright Sale Yes/No
16 Estimate of rents for Affordable Rent and Market Rent and commercial rents Yes/No

Entering data

The workbook should be completed in the following order:

All compulsory fields are indicated by **

A checklist tab is included in the workbook. This allows you to indicate which sheets are complete and which are not. This can be used as a prompt to ensure all sheets are completed.

This tab also includes a table of warnings. The warnings will only appear if obvious errors have been made in the completion of the workbook.  An example of this is having practical completion before the start on site date.

In all worksheets, manual input is required where a field is shaded blue. Where a field is shaded yellow, a calculation is included in the cell. Where better information is available, you can over-write it with your own data. The output sheets are all password protected to avoid them being overwritten inadvertantly, thus corrupting the results. The password is TRUST if you do wish to over-write them deliberately.

Some versions of Excel will ask if you wish to have macros enabled. If prompted, settle to have them enabled.

Risk Management

The workbook also has an important role to play in risk management.  As well as illustrating the financial consequences of a particular course of action, it should also be used to test the sensitivity of an appraisal to changes such as building cost overruns, delays in the development period or delays in sales.  Therefore, once an appraisal has been constructed, a number of those key variables can be changed in order to see whether the financial consequences can be sustained by the CLT.

We suggest that the following are tested:

  • Development costs increase by 10%
  • Construction period increases by 10%
  • Sale prices (if the scheme contains outright sale, equity share or Shared Ownership) decrease by 10%
  • Rent levels reduced by 10%
  • Sales delayed by 3 months.

Next page: download the Financial Appraisal Tool.