Guidance

Self-training exercise instructions, accessible version

Updated 23 May 2023

Applies to England

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

1. 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).
2. 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).
3. The following steps can be run from the Macro ‘Training Data Entry’
4. 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}
5. 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.
6. 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

1. 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}
2. 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.
3. 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}.
4. 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

1. Select tab Input 3 – Residential phasing. Only relevant House type phases are displayed, and for these the required date cells are highlighted as orange.

2. 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

1. Move to input tab 4 – other funding.
2. At present the scheme does not attract any additional funding.

Exercise 5 – Residential costs

1. Move to input tab 5.

2. 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.

3. 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.

4. 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.

5. 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).

6. 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.

7. Also enter {row 166} a CIL of £75 per sqm to be paid between 01 Jan 15 and 01 Jan 16.

8. Input acquisition agents fees, legal & stamp duty as 1%,1%, & 4.8%

9. 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.

10. 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 marketing - sales fees 2.8%, legal £400, and £0 letting fees.

11. The final entry on the costs tab relates to the developers overheads and profit. This is something that he will have cleared with his board and will take into account the level of risk associated with the development. On this scheme he is to bid at a rate of 18% for the open market units, plus £50k overheads and 5% for the affordable units where clearly there is less risk associated with sales.

Exercise 6 – Residual land value calculation

1. Select the Warning tab to confirm there are no warnings remaining.
2. Now that all the input data has been completed – it is time to return to tab Input 1 and press the grey ‘Residual Land Value’ button.
3. DAT computes a residual land value by finding the land value that would generate neither surplus nor deficit. The RLV should show as £ 1,417,383
4. You now have a completed DAT model.

Exercise 7 - A quick look at the main outputs

Grey sheet ‘Tabs’ are for output sheets, no data is entered in them. Select ‘Output Full’.

1. Press the yellow ‘Summary’ button at the top. Note the print preview is now on one page. The layout is similar to a ‘Profit & Loss’ for the for each tenure and then the scheme, with revenues headings at the top and cost categories below.
2. Check the Internal Rate of Return (IRR) value at the bottom (C262). Notice 7% is the annual equivalent return on the scheme cashflows, assuming positive cashflows returns are at the interest rate (‘modified IRR’).
3. To see more detailed computations it is necessary to switch to advanced user mode. Select the sheet ‘Input 0 –Setup’ and from the second row select the middle radio button ‘’Advanced User’. The only thing this sheet does is to display or hide various sheets to suit the user. Now there will be more sheet Tabs displayed on the bottom of the screen, although you may need to scroll right to see them appear.
4. Select the grey ‘Output Qtrly CF’ sheet and Print Preview. This is intended for printing only, there are no computations.
5. For details of cash flow computations it is necessary to select the right hand tab ‘sheet C0 Cashflow’.

Notice all formulas can be ‘traced back’ through this sheet. Whilst there should generally be no need to do this the whole model is ‘open’ and nothing is hidden.

Exercise 8 – An alternative bid with deferred land payments

The return on investment over time required for private investment can make longer term investment scenarios unviable. One potential way to lessen this problem where public sites are involved is to allow the purchase cost to be delayed and make stage payments, so the developer’s cash financing requirement is shortened. It could also mean that the payment for the land is increased representing a better offer for the patient land owner.

1. We will assume the £ 1,460,664 land payment is to be replaced with stage payments. Go to sheet Input 1 {L27} and select ‘Deferred payments’.
2. We will assume the two payment dates will match the phase end date for sales of open market houses i.e. 1 Aug 16 & 1 Mar 17, and make each for £725,000. Enter the dates & values in the table that opens in DAT.
3. Although the total land payment £ has increased this shows the Present Value of the payments has fallen slightly, since it has resulted in a surplus of £23,591. The developer may be keen to make such stage payments, despite the marginal change computed present value. This is because the time value of cash is generally significantly higher for a developer (who has investors to satisfy) than for a public body. Thus both parties can ‘win’ from such arrangements.

Scenario

The bid has now been received by Homes England and it is time to check the viability. Sadly the scheme has come in with an unacceptably low land offer. The operating area manager is considering how the scheme could be reviewed to improve the offer.

Exercise 9 - Making changes

1. An urban designer re-casts the scheme with increased active frontage and density, and with fewer cul de sacs. Despite the higher density it is felt the attractive layout will maintain the sales values per sqm. To reflect the higher density and higher numbers of units on the site go to Input sheet 2 and increase the number of Open Market 74m2 houses from 1 to 3 {row 8}, and the Affordable Rent 74m2 houses from 3 to 4 {row 16}. Adjust the control total and re-transfer the data into DAT. The surplus is £125,266

2. Delaying s106 payments can have a significant impact on scheme viability. Reschedule payment of the education s106 on Input 5 {C156} by one year (note the end date must be delayed first, otherwise the start would be after the end end). The surplus is now £199,980, but the beneficial impact for the developer is actually likely to be greater due to their return requirement being higher than the assumed interest rate.

3. Overwrite the Affordable Rent percentage of market on Input 2 {R10} as 70% to reflect assumed LA policy. As explained in the pop up, to test the impact of amending this it is necessary to enter the rents in Col H as a formula, reference the AR percentage (cell R10). This is because Col H is always the amount payable for consistency between tenures, but so far we have only input a fixed chargeable value. Set the following cells as formulas, rather than simple numbers. The values should not change.

   {H16} = 158.57*R10

   {H17} = 215.71*R10

   {H18} = 215.71*R10

   {H19} = 225.71*R10

   {H20] = 140.00*R10

   {H21} Leave as £160 to represent max affordable per LA Housing Needs

   A re-transfer at this point will show no significant change (£199,968)

4. Now the formula are entered the sensitivity of changes to percentage of open market rent can be easily tested. Assume that the LA has agreed to adopt 80% of market rent for affordable rent. Amend R10 to 80% and re-transfer. (‘ok’ the info note). The surplus increases to £459,331. Thus the land price could be increased without destroying viability.

   Generalising this example we can also enter any white cell as a formula rather than a fixed number where we require more flexibility. For example the infrastructure cost (on Input 5) could be set as a value per unit multiplied by the total number of units (from Output Full). The Notes & Memos page is free entry, and can be used to store information, such as infrastructure per unit values, which can then be referenced in formula.

5. Change the second stage land receipt payment on Input 1 to £1.2M (giving a total of £1.75M) It will be noticed the scheme is now still viable with this level of land payment £9,909.

Exercise 10 - Sensitivity Testing

Sensitivity testing is a useful function within the DAT model for quick testing of the impact of common assumption changes. The modelled is ‘flexed’ to evaluate the scenario, the revised Surplus/Deficit is recorded, and then the main model returned to its original state. In order to see the results on RLV we first need to set DAT back to up front land payment.

1. Select sheet Input 1 cell cell L27 and select “Site Payment is to be upfront”
2. The upfront RLV button should be pressed to find the current RLV. (£1,737,208)
3. Select sheet Setup Input 0 and press ‘add tools’ radio button on bottom right.
4. Select the sheet ‘Sensitivity’ (grey tab over the right hand side). The bottom of the ‘Live model values’ Column {C38} shows the current Surplus of £179,134 The main thing to remember here is that as with all other sheets all input entries go in WHITE cells (which are all in Column E).
5. Increase the forecast Open Market values by 5% (cell E5). Notice amended cells are highlighted in yellow. ‘Press to Run Scenario, Record and Restore’ from the button at the top left. Enter descriptive text ‘market recovery’ in {F3}. The RLV is shown at the bottom of the scenario column £2,002,734
6. We might use this surplus to reduce the AR % back down to 70%, {E18} which we can do as we set up the rent input as formula on sheet Input 2. Press the ‘Run Scenario’ button again; the RLV should have reduced to £1,747,647
7. Notice how the previous scenario has shifted to the right, and the new scenario incorporates both changes made so far, as we left both in the input Column E. Enter text ‘AR Policy compliant’.
8. Next reduce the build costs by entering -1% in {E16} & rerun. The result is a surplus of £1,796,769 . Enter the description ‘windows re-specified’ in the orange heading text cell {F3}. RLV is very sensitive to small build cost changes.

Notice that the changes are combined as all included in the scenario column when run, they could be run separately by resetting the column before each change. Scenarios do not change the original data, which still shows a surplus of £169,741. They are ‘snap shots’ if the underlying model data is changed then these will become ‘out of date’. Unwanted scenario columns can be deleted as any Excel column, just be sure not to delete the ‘working’ columns A-E.

Conclusions

The ‘number crunching’ aspect of a viability appraisal overview shouldn’t be unduly complex for typically sized schemes. This DAT model actually is really just five input sheets.

If you are going to interrogate and scenario test schemes it is necessary to obtain a copy of the ‘live model’, not just hard copy or pdf’s.

enquiries@homesengland.gov.uk

0300 1234 500

gov.uk/homes-england