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바카라 사이트檚 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 바카라 사이트榩op 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 바카라 사이트榗urly 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 바카라 사이트済rab바카라 사이트� the object
• 바카라 사이트淒rag바카라 사이트� the object to the desired location by moving the pointer to this one
• 바카라 사이트淒rop바카라 사이트� 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 바카라 사이트榖enchmark바카라 사이트�.

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 바카라 사이트淒AT 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 바카라 사이트榁iability바카라 사이트� option where land value is an input).
3. The following steps can be run from the Macro 바카라 사이트楾raining Data Entry바카라 사이트�
4. Tab to the Input 1 바카라 사이트楽ite바카라 사이트� 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 바카라 사이트榖enchmarking바카라 사이트� 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 바카라 사이트楾ransfer 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 바카라 사이트楾ransfer 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 바카라 사이트業ncomplete 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 바카라 사이트榳arnings바카라 사이트� 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바카라 사이트檝e 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바카라 사이트檚 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바카라 사이트檚 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 바카라 사이트楻esidual 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 바카라 사이트楾abs바카라 사이트� are for output sheets, no data is entered in them. Select 바카라 사이트極utput Full바카라 사이트�.

1. Press the yellow 바카라 사이트楽ummary바카라 사이트� button at the top. Note the print preview is now on one page. The layout is similar to a 바카라 사이트楶rofit & 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 (바카라 사이트榤odified IRR바카라 사이트�).
3. To see more detailed computations it is necessary to switch to advanced user mode. Select the sheet 바카라 사이트業nput 0 바카라 사이트揝etup바카라 사이트� and from the second row select the middle radio button 바카라 사이트�바카라 사이트橝dvanced 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 바카라 사이트極utput 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 바카라 사이트榮heet C0 Cashflow바카라 사이트�.

Notice all formulas can be 바카라 사이트榯raced back바카라 사이트� through this sheet. Whilst there should generally be no need to do this the whole model is 바카라 사이트榦pen바카라 사이트� 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바카라 사이트檚 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 바카라 사이트楧eferred 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 바카라 사이트榳in바카라 사이트� 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. (바카라 사이트榦k바카라 사이트� 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 바카라 사이트榝lexed바카라 사이트� 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 바카라 사이트淪ite 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 바카라 사이트榓dd tools바카라 사이트� radio button on bottom right.
4. Select the sheet 바카라 사이트楽ensitivity바카라 사이트� (grey tab over the right hand side). The bottom of the 바카라 사이트楲ive 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. 바카라 사이트楶ress to Run Scenario, Record and Restore바카라 사이트� from the button at the top left. Enter descriptive text 바카라 사이트榤arket 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 바카라 사이트楻un 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 바카라 사이트楢R 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 바카라 사이트榳indows 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 바카라 사이트榮nap shots바카라 사이트� if the underlying model data is changed then these will become 바카라 사이트榦ut of date바카라 사이트�. Unwanted scenario columns can be deleted as any Excel column, just be sure not to delete the 바카라 사이트榳orking바카라 사이트� columns A-E.

Conclusions

The 바카라 사이트榥umber crunching바카라 사이트� aspect of a viability appraisal overview shouldn바카라 사이트檛 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 바카라 사이트榣ive model바카라 사이트�, not just hard copy or pdf바카라 사이트檚.

enquiries@homesengland.gov.uk

0300 1234 500

gov.uk/homes-england