Version 4.02
January 2015 (rebranded for Homes England 2021)
Introduction
These training exercises are intended to be used as part of an introductory session on the use of the Homes England’s Development Appraisal Tool (DAT). Each participant should have access to a computer and be able to input the exercise data alongside the trainer, checking each step as the appraisal proceeds.
Notice that cell notes (in yellow) will often ‘pop up’ when entering data into white input cells. These give hints on the definition for the relevant inputs.
Column headings and row numbers are hidden, but the cell references can always be seen at the top left, and these are referred to in this document within ‘curly brackets’ {}.
Glossary
In this document:
Pointer
Refers to a graphical image on the computer monitor or other display device. The pointer echoes movements of the mouse.
Cursor
Is an indicator used to show the position on a computer monitor or other display device that will respond to input from a text input. It is activated by moving the pointer to a cell and clicking the Left Hand mouse button.
Drag and drop
Means move the pointer to the object:
- Press, and hold down, the button on the mouse, to “grab” the object
- “Drag” the object to the desired location by moving the pointer to this one
- “Drop” the object by releasing the button
Drop down box
Refers to a cell, that once selected, offers a visible list of possible values to choose from using mouse or arrow keys. In the case of only two options this may be referred to as a toggle switch.
Radio buttons
Are arranged in groups of two or more to allow the user to select conditions or an approach for the appraisal. They are displayed on screen as a list of circles that are white space (for unselected) or a dot (for selected). Each radio button is accompanied by a label describing the choice that the radio button represents. When the user selects a radio button, any previously selected radio button in the same group becomes deselected. Selecting a radio button is done by left clicking the mouse on the button,
Notice: The values used in this example are set to produce desired outcomes and in no way are intended as any kind of ‘benchmark’.
Scenario
You are a working for a leading house builder, pulling together a submission for a Homes England Delivery Partner Panel/DPS public land disposal. Homes England has asked for a completed version of the DAT model to be submitted with your proposal – and would like to see two potential offers for the land – first of all a straight payment for the land and secondly a preferred delayed payment offer. There is no non-residential development on this site (though note DAT can handle mixed use schemes).
Exercise 1 – Inputting site details
- Please open the DAT model provided “DAT v4 Training”. Note the colour code index on the bottom left which will explain the cells you may populate (white) and those which must be populated (orange).
- Accept the disclaimer, and then from the pop up menu select the residual land value calculation, which is the centre option of the 3 offered. (Note: e.g. if we were assessing s106 viability then we would choose the top ‘Viability’ option where land value is an input).
- The following steps can be run from the Macro ‘Training Data Entry’
- Tab to the Input 1 ‘Site’ sheet. This sheet contains the basic information about the site. Note the drop down boxes for HCA Operating area, Registered Provider and Local Authority partners. Enter your name as author. {cell B20}
- The date of the appraisal should normally be the current date, and this is the default. In bid situation the bid closing date may be used. For this training exercise Enter 1/1/15 as date of appraisal.
- As this example is utilising Residual Land Value (RLV) mode the initial site value can be left blank; this will be updated at the end of the appraisal. Note that the separate historical cost is only used for computing site acquisition costs; in RLV mode this will also be computed.
Exercise 2 – Inputting residential details
- Now tab to sheet input 2. This sheet contains the information about all the housing types to be built on the development. Input the control total number of units proposed on the site as 74 units. {E5}
- The developer has already input a schedule of the open market sale units to be constructed based on his usual house types and local sales value intelligence. The description of each unit is a free field to use usual house type names – eg the Windermere or the Kentucky.
- Note the option to select m2 or sqft for the floor area {D7}, and the drop down menu for the house types and tenure/phase boxes {Columns E & F}.
- Please now APPEND the affordable housing units proposed ( see table below) {in rows 16 to 21}
| Example | ||||||
|---|---|---|---|---|---|---|
| A – PRP Mews – 2 bed | 3 | 74 | 2 bed house | Affordable rent phase 1 | 111.00 | |
| D – PRP Mews – 3 bed | 3 | 82 | 3 bed house | Affordable rent phase 1 | 151.00 | |
| F – PRP Mews – 3 bed | 2 | 82 | 3 bed house | Affordable rent phase 1 | 151.00 | |
| K 402A | 1 | 100 | 3 bed house | Affordable rent phase 1 | 158.00 | |
| KP – PRP Mews – 2 bed | 12 | 64 | 2 bed house | Affordable rent phase 2 | 98.00 | |
| E – PRP Mews – 4 bed | 1 | 107 | 4 bed+ house | Affordable rent phase 2 | 160.00 |
Notice in the size column that cells shaded yellow indicate large units (which will impact on build costings).
On the right hand side notice the orange cells. This indicates that data input is now required here. Enter the Annual cost data for the Affordable Rent units as:
Management 10%,
Voids & Bad Debts 2.5%,
Repairs & Maintenance 15% and
Yield 5%
Notice row 16 below shows the annual £ values these inputs equate to.
It is possible to Copy data onto this sheet from existing plot list, or from duplicate row to row (Edit-Paste Special-Values), but it is important not to Cut & Paste, as Excel then rearranges formulas.
5 If the developer has accepted an offer from an RP for the affordable units on the scheme, then instead of DAT computing a value from rents, costs, and yields this sum may be input directly into DAT. This is would be done from cell {K5} (the large white cell), which allows the selection of the method for inputting the affordable receipt and requires the capital values of each type to be input.
6 Test selecting the alternative from the drop down box.
AH & Rental valuation based on capital values for Residual Valuation
Notice how this results in cells {G16..G21} turning orange, which means that values would be required to use this method. However selecting this option allows no ‘benchmarking’ against which to assess how realistic the valuation is, and our training example is set to compute from first principles, so restore {K5} to:
AH & Rental Valuation based on net rents
7 Press the grey ‘Transfer to DAT’ button at the top right. Notice the new red message in {G6}. The total number of units acts as a control check to make sure that you have fully populated this units sheet – if the box is red then you have a difference. Correct the total {E5} to reflect the full 75 unit scheme, and notice the check cell below becoming green to confirm agreement.
8 Press the grey ‘Transfer to DAT button again. After a short delay there should be a green transfer complete confirmation in {G6}.
9 Note also the red/purple bar at the top of the DAT model. This should be saying ‘Incomplete or invalid entry ,see warning sheet’, which simply reflects the fact that not all mandatory inputs have yet been entered in order to allow a RLV computation to be made.
10 Tab to the red ‘warnings’ tab on the bottom of the spreadsheet (you may need to scroll the tab bar to the right to see this). This sheet contains a list of all the missing information in the DAT model required before a valuation can be made. As more data is input the list will be shortened.
Exercise 3 – Inputting residential phasing
Select tab Input 3 – Residential phasing. Only relevant House type phases are displayed, and for these the required date cells are highlighted as orange.
Input the following dates that the developer is planning on starting construction of all house types (the affordable units are to be pepper potted throughout the site) as
Phase 1 01 Jan 16 and complete the build on 01 Jul 16.
Phase 2 01 Jun 16 31 Dec 16
into the appropriate orange cells. (Always use the keyboard enter button to input the data rather than clicking the mouse, which we’ve found can cause unexpected validation errors). NB The date may be entered in any normal Excel format.
3 The RP has agreed to pay the developer instalments for the affordable units on the first day of construction of each phase
Enter to DAT 01 Jan 16 (twice) and 01 Jun 16 (also twice)
4 Having spoken to the sales and marketing team, the developer has decided to start selling the phase 1 open market units off plan from
01 Nov 15 and expects all sales to be concluded by 01 Aug 16. Input these dates into the relevant orange cells {row 59}. The 2nd phase assumptions are more conservative, 01 Aug 16 to 01 Mar 17. This completes residential phasing.
Exercise 4 – Other funding
- Move to input tab 4 – other funding.
- At present the scheme does not attract any additional funding.
Exercise 5 – Residential costs
Move to input tab 5.
The developer has spoken to his estimating team and agreed that on this project the unit build costs will be at a rate of:
£750 per sqm for Affordable Housing and
£800 per sqm for Open Market. Input these costs into the top orange boxes.
(either of these might be greater, depending on the scheme in question).
Note that if the developer wanted to he could toggle this to be based upon £per sqft {C10}. Move the cursor out of {C10} but put the cell pointer in {C10}. Notice the definition of this build cost in the cell note; it does not include the builder’s return, which comes later.
The developer estimated 7.5% for design fees, and has decided that as this is a green field low risk site, that no building cost contingency will be included.
The next section contains cost and programming information about the external works and infrastructure costs. Input the cost of roads and sewers at £645,000 {C58} and insert the dates at which these works will be paid for – between the 1st April 2015 and 1st June 2015. Notice that from v4 there is a separate section for each phase’s infrastructure costs.
The developer has already populated the remaining costs in this section. There is a place for notes to be added alongside the heading if this is useful. Notice that all descriptions may be overwritten (i.e. they are in white cells).
The abnormals section should only include any items of work that are not normal for that particular kind of development. In this case the developer has entered Decontamination and Flood protection here.
Also enter {row 166} a CIL of £75 per sqm to be paid between 01 Jan 15 and 01 Jan 16.
Input acquisition agents fees, legal & stamp duty as 1%,1%, & 4.8%
The housebuilder has quite favourable finance terms and is able to borrow the development finance at a cost of 5%, and the same for credit balance re-investment. Please enter this interest fee in the finance costs section below. Assume no other finance costs.
There are no Affordable Housing sales costs assumed, so these three entries may be set to zero. Enter the developers costs associated with sales and…