Development appraisal tool: user manual
Contents
1. BELSESSEL SEES This will have added a Developer button to the green excel ribbon at the top of your screen If you click on Developer you will see a macro button on the second layer of the ribbon Click on Macro and a further window will open up This time you need to select Populate from GLA style ennen rn timma mee puea bet NOT OUT Musen hen Cabanon ENTRE Ve hen we vee SS teuzas eure hse terra thee seems res tart se Sot oseng crm ai Open Wartbosts Clicking on Run will have the same effect as the grey input to DAT button on the Windows version of the DAT model Email DATEnquiries hca gsi gov uk for further assistance if needed Development Appraisal Tool 22 GOV UK hca mail homesandcommunities co uk 0300 1234 500 Homes amp Communities Agency Homes and Communities Agency Fry Building 2 Marsham Street London SW1P 4DF The Homes and Communities Agency is committed to providing accessible information where possible and we will consider providing information in alternative formats such as large print audio and Braille upon request Publication date November 20142. 1 700 000 1 01 2007 1 500 000 1 01 2011 olm D x a a 3 2 a Fig 9A The basic site details The Land value entries require some explanation The historic land purchase price is recorded but this is used only to compute the historic costs relating to land acquisition primarily stamp duty At start up there is a choice of Viability assessment or Residual Land Value modes Cell L27 also contains a drop down list that allows selection of one of three choices 1 Site Payment to be upfront for viability assessment this is the default option The valuation entered is assumed to be fully paid at the date of valuation The output of the assessment is a surplus or deficit after inclusion of this cost Development Appraisal Tool 8 Homes and Communities Agency 2 A Deferred payment schedule of proposed stage payments is to be input it opens a table that allows input of up to 12 dated stage payments to be input These will be transferred to the cash flow on the given dates This reduces the present value of the total payment compared to a straight addition of pound values 3 Residual Land Value where the objective is to value that is being sold A zero land value should be entered This value will be the output of the model once all other input has been completed This mode may also be chosen at start up 4 3 Residential details Where the Simplified option is chosen in the Setup sheet property ty
3. sheet when Show scheme is selected They are intended primarily for HCA use but may also be of interested to those familiar with financial modelling concepts The functionality most likely to be of interest is the sensitivity analysis 6 1 Scenario sensitivity analysis Residual values are the result of taking one large total costs away from another large number revenues Consequently a small percentage change in either large number has the potential to make a large percentage change in the resulting residual difference Owing to this sensitivity slight changes in a few of the many variables in the model will often result in a wide range of residuals and could easily eliminate the surplus deficit For this reason a scenario capability is provided to rapidly test to these sensitivities Reduce Reduced Live model 106 Reduced Annual Reduced values payments rental yield Costs build cost ch in ch in ch in ch i Press to Run Scenario Record and Restore eal peat Cael Mae eal Dues i in INPUTS Sales Value Sales Value Sales Value Sales Value Open N A Q 0 Q 0 Shared 0 0 0 0 35 35 35 35 changein changein change in change in Building Building Building Building Casts Costs Costs Casts 5 5 5 5 Revised Revised Revised Revised Percentage Percentage Percentage Percentage 80 80 80 80 15 0 15 0 15 0 20 0 2 5 2 5 2 5 2 5 15 0 15 0 15 0 20 0 5 00 5 00 6 00 6 00 FINANCE 6 0
4. The HCA will always endeavour to provide its independent opinion but will not become the final decision maker on issues such as the appropriate level for an assumption as these must reside with the relevant accountable body The HCA is not able to provide evidence on viability to a planning appeal or examination in public and so local authorities may still require independent advice on the appropriate values to use if they cannot reach agreement with the developer Development Appraisal Tool 4 Homes and Communities Agency 3 Entering a scheme 3 1 How the model works The development appraisal tool operates on a monthly cash flow basis Detailed guidance on use is held within cells notes of the model including precise definitions of terms such as build cost and abnormals The Affordable Rent valuation is computed by default from the net cash flows and required yields in line with RICS guidance It is not driven by the amount a Registered Provider RP may have agreed for a purchase transaction as this may include internal cross subsidy or competitive considerations Where the price agreed implies such variance from valuation this should be entered separately under Other funding see below For Affordable Rent open market rental values are critical and should be comparable with local market information sources and sensitivity tested Affordable rents entered in the sheet Res details are actual values expected to
5. originally 477 600 to match the land valuation 1 5m To see the impact of singular changes these must be entered one at a time pressing the reset button after each scenario run Old scenarios may be deleted simply by deleting the entire column or columns More detail of the operation of scenarios may be found on the scenario sheet itself 6 2 Output affordable additionality The purpose of this sheet is to show e The overall impact of the proposed affordable housing mix on viability For instance this allows the affordable housing impact to be compared to other costs such as 106 infrastructure requirements e A quick estimate of how changes in the tenure mix would change the viability e g would a particular switch from rent to Shared Ownership LCHO make a scheme viable without a need for grant The analysis is computed by calculating all tenure revenues and costs on a square metre basis This reveals the contribution towards scheme costs provided for each square metre of each tenure type built Affordable rent may or may not cover its costs whilst LCHO will normally provide a contribution albeit at a considerably lower value per sq m than open market From these results the impact of switches per sq m between affordable tenures and open market sales is arrived by simple arithmetic This then allows the two key results to be computed e The total impact of affordable provision on the scheme e The number of units of Affor
6. be charged see section 4 3 By convention an all risks yield is used to value fixed property income streams to perpetuity and this is the default method but there is also an option to value expected inflated cash flows over 30 years as RP s often uses this method internally see Advanced User sheet Summary 2 Res Values D8 Either method is appropriate provided suitable capitalisation rates are used Notice that as DAT is a development model its cashflow incorporates this computed capital value for rental units at the time they are sold The long term rental and expense cashflows are not directly within the cashflow because they are used instead to compute this capital value the buyer would assume responsibility for those From v3 0 there has been an option to entered assumed capital values for AH properties see section 4 3 However this option should only be used where competitive RP bidding gives for a site a measure of confidence in the values used Where a planning viability exercise is being undertaken the reasonableness of the values used needs to be assessed using the full valuation in line with the RICS paper Similarly Shared Ownership valuation is made from estimates of expected net cash flows Primarily this is the addition of the initial sales tranche plus the capital value of the net rent on the unsold proportion Again the two capital valuation methods above are available It is also possible to make some speculative
7. development viability tool into the search box on the home page will find the relevant page from which a download maybe made Is there a dashboard showing the important outputs Yes The Full Output can be compacted using the yellow button giving a single page report with the key indicators Can t I just input a straightforward value for the Affordable Housing Yes you can see section 4 3 How should service charges be treated Development Appraisal Tool 13 Homes and Communities Agency Affordable rents entered in sheet Res details are actual values expected to be charged The model derives the implied market rent based by default at 80 and stores this in sheet Summary 2 in order that advanced users may test amendments to the parameter Using this method it is possible to have five phases of affordable rent charged at separate percentages of market rent Service charges on any tenure are recoverable from the individual occupiers of the units and therefore do not have any impact on the scheme residual consequentially they do not need to be entered into DAT However they are relevant to the affordability of housing to tenants and are an issue that must be deemed as satisfactory in considering local housing need In particular note the 80 of market rent for affordable rent includes any service charge therefore rents need to be reduced by the service charge amount for this tenure Practical evidence is that mos
8. of pages necessary for easy reading 5 4 Scheme history Scheme information can easily be changed at a later date once it is clearer what the various costs values and timings etc will be Itis recommended that once a scheme has been modified the file is re saved under a different name possibly using the date of change in the filename These stored models will provide a history thus allowing monitoring of a development through its various stages from inception stage to the latest reality Further detail can be recorded in the notes sheet 5 5 Evidencing Grant Additionality The Affordable Housing Framework is clear that any Grant from central government is provided only to provide additional Affordable Homes above and beyond that possible without grant In order to use DAT to evidence this the following procedure should be adopted Produce the DAT for the no grant case and save a copy of the file as a Base case Make changes only to the housing mix that are additional to the Base case The difference in residual between the two models is the valuation of additonality arising from the mix change Such evidence is a necessary but not of itself sufficient part of the case for grant Notice too that grant is not normally made for s106 schemes under NAHP 15 18 Development Appraisal Tool 16 Homes and Communities Agency 6 Analytical tools These are accessed by pressing the Add Tools button on the Input 0 Setup
9. the works These fees must be disaggregated and included in the appropriate site cost figure not the works figure From v4 site costs can be split into up to 5 phases 4 7 External works and infrastructure From v4 a separate section for infrastructure cost opens for each phase of building that has been populated Site costs necessary to provide serviced plots for building construction from unoccupied secured and uncontaminated site Where such costs also serve other development which is not part of the application e g a spine road costs should be attributed across the elements and a statement will need to be provided to justify the percentage allocation of common costs to each element Any cost of surety bonds to the LPA for performance of these undertakings should be included in the relevant line Notice that fees and contingency percentages are not applied to these items so any such amounts should be included in these values Plot externals relate to items such as garages and fencing which are not included in the structure build definition 4 8 Site abnormals ONLY for items not normally expected for sites of its kind eg flood protection ground stabilisation for abnormal conditions noise abatement The site investigation report is likely to be required The other line might be used for items such as third party land ransom costs including easements and covenants 4 9 Fees The building cost fees such as architect quant
10. 0 6 00 6 00 6 00 100000 0 0 0 41275 DEVELOPER S OVERHEAD AND RETURN FOR RISK before taxation amp Overhead 15 0 15 0 15 0 15 0 3 7 3 7 3 7 3 7 1 500 000 1 500 000 1 500 000 1 500 000 OUTPUTS Pi s at F O AS Fig 12 A combination of changes that would be sufficient for the surplus to match the land valuation Sensitivity analysis can be completed within the tool for increasing decreasing build costs and capital values operational costs of the affordable rented element financing interest rate rental yields and developer returns Normally small variations say 2 5 5 10 are entered in the New scenario to test column of the scenario sheet Pressing the Run Scenario button computes the resulting residual value and stores this at the bottom of a new scenario column Development Appraisal Tool 17 Homes and Communities Agency created to the right of the input but leaves the model data in its original state Text can be entered at the top of the scenario column for the run as an aide memoir as to its purpose Entries made in the New scenario to test column are combined to run as a single scenario Every run will create a new scenario column and the existing scenarios will shift to the right in order to preserve results The example in fig 12 above shows how a combination of changes was built up by adding one extra entry between each press of the scenario run button in order for the surplus
11. W Homes amp Communities Agency DEVELOPMENT APPRAISAL TOOL User manual Contents Homes and Communities Agency Context and potential uses 1 Purpose and principles 2 Purposes 2 1 Principles 2 2 HCA programmes 2 3 Assessment of potential land value 2 4 Values for assumptions 2 5 Entering a scheme 3 How the model works 3 1 An open model 3 2 Overview 3 3 Input Entry 3 4 Description of major parts 4 Set up sheet 4 1 Site 4 2 Residential details 4 3 Residential phasing 4 4 Other funding 45 Residential costs 4 6 External works and infrastructure 4 7 Site abnormals 48 Fees 4 9 Building contingencies 4 10 Statutory payments 4 11 Finance cost 4 12 Marketing costs 4 13 Developers overheads and return for risk 4 14 Non residential 4 15 Notes and memos 4 16 Errors and warnings 4 17 FAQs 4 18 Understanding the results 5 Cash flows 5 1 Off site provision of affordable housing 5 2 Saving and printing the results 5 3 Scheme history 5 4 Evidence Additionality 5 5 Analytical tools 6 Scenario sensitivity analysis 6 1 Output affordable additionallity 6 2 Version changes history 7 Technical note on Excel including Apple Mac version 8 The Homes and Communities Agency makes no warranties representations or undertakings about any of the content contained in this Model including without limitatio
12. al is taken at a point in time taking account of values and costs at that date A site may be purchased some time before a viability assessment takes place and circumstances may change That is part of the developer s risk The site value must equate to the market value of the site at the date of the appraisal Holding costs attributable to the purchase of the site should not therefore be allowed as the site value will be updated 2 3 HCA programmes The HCA 2015 2018 Affordable Homes Programme Framework document includes the following comments on s106 schemes We expect the Homes and Communities Agency funding or the use of Recycled Capital Grant Fund or Disposals Proceeds Fund to be agreed on S106 schemes only very exceptionally Open book provision of data about the economics of the scheme will be required from both the developer and the long term owner of the affordable housing if they are different We will test the economics of individual schemes through our Development Appraisal Tool and reserve the right to request other information to inform our decision making if necessary 79 Use of the DAT in this instance would require two scenarios to be model one without grant and one with to demonstrate the impact of grant 2 4 Values for assumptions It is for the parties to the planning process to determine the values for all key assumptions However costs always need to be interpreted in relation to a good understanding of the part
13. and Ecological costs Landfill tax and archaeology Notice that s278 Highway and s104 sewers agreement costs are categorised under the external works section There is a line for CIL per sqm and one for a LPA per unit tariff All others values are a total scheme sum All values input require a date for phasing and should be entered at the value expected at the payment date e g the CIL is likely to require indexation to the mid point date EXCLUDES s106 housing unless a commuted sum payment is required since this is modelled in the housing mix 4 12 Finance cost The interest rate is the cost of funds to the scheme developer it is applied to the net cumulative negative cash balance on the scheme as a whole each month It depends on the developer the perceived scheme risk and the state of the financial markets There is also a credit interest rate which is applied should the cumulative month end balance be positive As the developer has other variable borrowings such as an overdraft or other investment opportunities then Development Appraisal Tool 12 Homes and Communities Agency the value of credit balances in reducing overall finance charges is generally the same as the debit interest charge It is unlikely that the developer would simply put the funds into the bank bit if so then a lower rate could be appropriate A zero rate of credit interest is not generally plausible and will generate significantly erroneous results in a lon
14. atically highlighted and require explanation but maybe valid provided the Sales Values reflect these unit sizes normally current building costs are used identified from sources such as BCIS with whom definitions are similar or other local schemes Note normal preliminary costs such as securing the site that occur in all sites of the type should be included in the base build cost Infrastructure and externals are dealt with separately below The build cost should be based on the actual sustainability standards that will be achieved which is also itself recorded within the input The HCA is likely to consider the updated third version of costing the code for sustainable homes when evaluating schemes built above applicable building regulations Table B being particularly useful Additionally evolving experience of comparable schemes will also inform this view It should be ensured that the declared works costs do not include any e Design fees e Planning and building regulation charges e Structural warranty NHBC fees e Minor pre tender works Development Appraisal Tool 11 Homes and Communities Agency Notice that Net to Gross adjustment definition is defined according to surveying convention i e the percentage to add to net area to obtain gross e g 15 Particular care is required to accurately declare the works figure for a scheme when using design and build contracts as the contract sum will include various fees as well as
15. ation may be recorded in the notes and memo sheet The five open market build phases may be used to reflect build phases e g five blocks of flats or five parcels of land They may also be used to split units into separate marketing phases used to approximate an expected sales curve This would be achieved by two or more phases being setup for a single physical construction phase with different sales rates and values over different periods Such phasing can be crucial as cash flow income from open market sale is pivotal to scheme s viability For schemes of five years or less it is typical to value at current cost amp prices Long term schemes are very sensitive to such assumptions and varying scenarios should be tested for example using industry forecasts Developer s own shared equity products should be included in the open market build units on the Phasing and Valuation sheets at the full 100 sales value Only where planning agreements require sale at less than market value will Shared Ownership valuation be appropriate at zero rent are Populates unit numbers sizes values and rents AND annual c The number of units remaining to be entered Q ransfer COMPLETED Ok m2 1 33 1 Bed Flat Lowrise _ Affordable Rent phase 1 MQW 72 73 5 45 1BedFlatLowrise Affordable Rent phase 1 NN lt N lt NI NWWV 7273 2 Bed Flat Low rise Affordable Rent phase 1 MQ GG J 8336 6 85 3 Bed Flat Low rise Affordable Rent phas
16. be selected in order to be visible 5 1 Cash flows There is a Summary quarterly cash flow output suitable for printing Additionally there is a detailed monthly cash flow computation sheet with from the top down phasing residential non residential and combined sections All computations are visible 5 2 Off site provision of affordable housing For the off site provision of affordable housing different development appraisals should be undertaken for each component of the overall development For instance if the proposed development involved two sites there should be two different development appraisals the first for the original site and the second for the affordable housing site It is likely that the original site will have a large positive residual surplus and the second site will have a deficit Once the two or more development appraisals have been carried out then the residual land values should be combined in the manner described above to give an overall surplus deficit for the proposed development as a whole This combined surplus deficit will indicate the viability of the development as a whole If payment in lieu of affordable housing commuted sum is made this should be inputted as a payment in the s106 costs heading in the Other Funding sheet Development Appraisal Tool 15 Homes and Communities Agency 5 3 Saving and printing the results As usual with Excel the tool model can be saved at any time usi
17. dable Rent that would need to be switched to either LCHO or open market sales to eliminate a surplus or deficit It should be noted that the Contribution Analysis on which this section is based is necessarily an estimation For example changing tenures may in practice require changes in the physical scheme Therefore the precise impact of changing tenures would require a revised scheme to be fully appraised Nevertheless estimation from marginal contribution analysis is capable of providing sound estimates that should prove useful in appreciation of a scheme s economics To further investigate the impact of such proposals the tenure mix in the model could be amended to test the results See section 4 5 Development Appraisal Tool 18 Homes and Communities Agency 7 Version changes history The Development Appraisal Tool was developed by the HCA from the Economic Assessment Tool EAT provided by GVA Grimley Subsequent releases have been as follows Results using all versions of the model should be the same excepting any changes noted below Version List of maintenance updates V1 0 V1 01 V1 02 V1 03 V1 04 V1 04b V2 0 V2 02 V2 03 V2 04 V3 0 v3 01 V3 02 V4 0 Initial version made available Fixes for Security preventing selection of cost inputs by sq ft Social Rent phasing prevented from hiding even where input required 100 private sale residential input transfers no longer trigger error message EAT style inp
18. e 1 AQ WG 9317 4 114 3BedFlatLowrise Affordable Rent phase 1 A QAI G QV 5 45 1 Bed FlatLowrise Shared Ownership phase 1 130 500 KWK i Eel at coerce hared Qanership phase Se SSS 2 45 2 Bed Flat Lowrise Private Rent Build phase 1 3 58 3 Bed Flat Low rise Private Rent Build phase 1 3 Bed Flat Lowrise Private Rent Build phase 1 mA 4 Bed Flat Low rise Private Rent Build phase 1 37 ae a a a REN d 58 2 Bed Flat Low rise Open Market Build phase 1 168 200 AM 3 90 3Bed FlatLowrise Open Market Build phase 1 26100 4 114 4Bed Flat Lowrise Open Market Build phase 1 330 600 Raan Fig 10 Input of the residential build mix and values into the Res Details sheet wjn I ci i tft ow Development Appraisal Tool 9 Homes and Communities Agency For rented tenures there is a choice whether this is computed from applying a net yield to net rents or simply inputting an expected capital value per unit The former provides more detailed evidence and is thus to be preferred when viability is being tested see below The later may be appropriate where bids are being submitted for a parcel of land where competitive comparisons can be made However it does not aid benchmarking of input values The choice is made from cell K5 under the grey Transfer to DAT button where there is a drop down box which allows the selection of the method for inputting the af
19. elow may appear The security warnings enable option must then be chosen Further information is available on the Microsoft site see http office microsoft com en us excel help change macro security settings in excel HP010096919 aspx CTT 1 Toc272500832 Change macro security settings Security Alert Macros amp ActiveX Macros amp ActiveX Macros and one or more ActiveX controls have been disabled This active content might contain viruses or other security hazards Do not enable this content unless you trust the source of this file Warning It is not possible to determine that this content came froma trustworthy source You should leave this content disabled unless the content provides critical functionality and you trust its source More information File Path C ents homes and communities Demo DAT Release candidate macro xlsm Help protect me from unknown content recommended Open the Trust Center Windows Excel V2010 and v2013 DAT has been tested under Excel 2010 On opening the message bar will display a security warning Macros have been disabled unless previously disabled Press the button to enable content and set as trusted document if offered See http office microsoft com en gb excel help change macro security settings in excel HP010342232 aspx and http office microsoft com en gb excel help enable or disable security alerts on the message bar HA010354318 aspx CTT 1 OpenO
20. er is pressed the Usage Mode is presented for selection as in Fig 4 below this determines the mode of output from the model The top option is the Viability Assessment whereby land value is entered and a final deficit surplus results This is suitable for use where the viability of planning obligations is being assessed The second option is similar except that no land valuation is entered and the model computes to the Residual Land Value which Select USAGE MODE Public Land sale valuations All the same input values as for Viability will br requied except a land valuation which is computed on the basis of acheving neutral viability after al Figure 4 Selection of the Usage Mode The third option is for the situation where it is only required to calculate the value of the Affordable Housing element of a scheme for example to agree a transfer price from developer to Registered Provider The Affordable Homes Framework states 5 15 our assumption is that the price paid for affordable housing by a Registered Provider will be no more than the capitalised value of the net rental stream of the homes Using the tool in the Affordable Housing Cash flow mode with suitable assumptions on rents annual costs and yields enables this valuation to be made notice build cost are irrelevant in this regard When thus selected a reduced subset of inputs is displayed for completion and once entered the value at the top of each screen shows
21. estimates of potential values arising from future stair casing sales Some Local Authorities have used a shared ownership model where only part payment is made for a home and the same proportion of current value received at sale time This can be modelled within DAT using the Shared Ownership tenure with a zero rent charge proportion 3 2 An open model For those who choose the advanced user option all computations can be viewed but not amended in the Excel Formula bar This open approach is intended to build understanding of the tool workings and trust in results from all parties Note It is necessary to select the advanced user option to view all computation shets 3 3 Overview The tool is broadly divided into three sections Inputs Computations and Output The user needs to work through a number of Input sheets tabbed from left to right the exact sequence depends on the complexity of the scheme In the most straightforward case these would comprise Sheet Contents The site Descriptions size value Residential details Types numbers and values of each property type for each tenure and build phase Once complete a button transfers this data to the computation sheets Residential phasing The dates for build and expected sales period Residential costs Build infrastructure s106 finance and marketing After these are complete a residual surplus deficit for the development will be compu
22. ffice amp LibreOffice Calc Will not run Visual Basic for Application Macros consequently DAT doesn t function Development Appraisal Tool 20 Homes and Communities Agency Using the DAT on a Mac Mac Excel 2008 Will not run Visual Basic for Application Macros consequently DAT doesn t function Mac Excel 2011 This software will run VBA and can run DAT but ActiveX buttons and controls do not function This can be worked around by manually unhiding required sheets Open the DAT file enable macros and open read only version It is then necessary unhide the sheets tabs To do this right click on the Cover tab a drop down menu will give you a choice of which sheets to unhide We suggest that initially you unhide all the input sheets and the Output full sheet Development Appraisal Tool aes i Once you have all the sheets you need open you will need to enable the Input to DAT calculation to work Go to input sheet 2 Check whether you have Developer on the top green ribbon on your Mac Excel If not you will need to open this up Press command and comma keys on your keyboard A window will open press the green ribbon icon button Sttateett totes hit HHH aranan HA eetteeetetetetee Development Appraisal Tool 21 Homes and Communities Agency Another window will open this time you should look to make sure that the Developer option is checked
23. fordable receipt and requires the capital values of each type to be input AH amp RENTAL VALUATION BASED ON CAPITAL VALUES for RESIDUAL VALUATION When this is toggled to AH amp RENTAL VALUES BASED ON NET RENTS the AH sales value column cells will be turn white indicating that entries are required The above is the default selection on Input 2 cell K5 and allows benchmarking of input assumptions Rent values to be paid per week are entered An Annual Costs table is also present for rented tenures see fig 11 These costs are deducted to compute the net rent which is divided by the input yield to derive the capital value of the affordable housing Fig 11 The annual costs input Orange cells will appear against populated tenures Input sheet 2 is a scratch pad for batch input which may be revised as required without direct impact on the model Once all entries are complete so no orange cells remain and the control total agrees then the grey Transfer button on the top of the sheet should be pressed This will use the list to populate the full model including property data and annual costs This entry method is likely to be easiest in most cases especially where a user is familiar with the GLA toolkit or a known property build listing has already been determined There may for example be more than one type of 2 bed flat in which case separate numbers sizes and values or rent may be entered
24. g term scheme Notice that it is often the case that schemes are modelled at current costs amp values i e ignoring inflation In this case RICS Financial Viability in planning paper states in appendix D 4 5 current values and costs should be used together with a net of inflation finance rate Such a net of inflation rate would be much lower than a bank rate which naturally includes inflation expectations 4 13 Marketing costs See pop up notes attached to each input cell 4 14 Developers overheads and return for risk A fixed overhead amount plus a percentage of open market capital value including private rented units A percentage of affordable housing build costs as the developer is holding no sales risk then we expect a contract type profit based on costs NB Even if the developer for a particular scheme is a not for profit RP it still requires a yield to cover the risk of investing if it is to survive and grow in the long term Notice it is normal for a developer s to realise their return at the end of a scheme when the outcome is known and all revenues received and this is the default assumption in DAT However for very long schemes that will be developed in separate phases it would be unrealistic to assume the developer has to wait until final completion Therefore an option exists in cell E142 to spread the return across the scheme according to the sales phasing or across periods with positive cumulative Cashfl
25. icular site for example e Brownfield what are the site constraints that may impact the scheme e Greenfield to what degree will new infrastructure and services be required e What is the scale of scheme property type mix and especially how many storeys are being built Development Appraisal Tool 3 Homes and Communities Agency e Are there special requirements e g underground parking local standards requirements Financial inputs such as rate of interest rental yields and returns will vary over time and according to the organisation carrying out the development In addition they partially depend on the scheme itself particularly its perceived risk characteristics The open market values of property are crucial and one data source is the Department for Communities and Local Government Housing Market data however knowledge of the local market is crucial Similarly the Building Cost Information Service BCIS of RICs may be a useful resource but comparison with other local submissions can become a most valuable dataset over time The use of scenarios and sensitivity analysis will often be helpful where there is either uncertainty or disagreement between parties over key values see section 6 1 LPA s may request the assistance of their local HCA contacts to act as a critical friend in discussions with developers who are seeking to reduce the level of planning obligations on a scheme supported by a viability assessment
26. igations e Assessing the case for financial support from the HCA under the Affordable Homes Programme e Assessment of the potential land value where a local authority is considering a disposal e Bids for development of Public Land under the agency s Development Partner Panel 2 process e Abid comparator is available to help compare a number of submissions and their assumptions National planning policy guidance expects Local Planning Authorities LPAs to define their approach to seeking developer contributions and make the best possible use of planning obligations to improve affordable housing delivery through mixed tenure developments This implies effective but realistic affordable housing policy targets and thresholds supported by evidence of their economic viability together with viability appraisal of specific sites at an early stage of the development management process If local authorities require assistance in using the tool they should contact their local representative from the HCA or email DATEnquiries hca gsi gov uk Limitations A maximum of fifteen years of monthly cash flows can be modelled Five sales phases of each tenure type All Cash flows are modelled as linear across the specified period though phasing can be used to approximate curves htto www homesandcommunities co uk ourwork affordable homes programme 2015 18 Development Appraisal Tool 2 Homes and Communities Agency 2 Purpose and princi
27. ity surveyors fees project management and building control fees as a percentage of building costs Notice this is not applied to External and other works Such costs are liable to have fees at differing rates and should be entered at a gross of fees value e Will be phased 50 at construction start 50 spread across build 4 10 Building contingencies The percentage of building costs that are set aside to counter unanticipated building events problems in schemes where there is a high level of build cost risk e g refurbishments large complex schemes etc Generally for volume house building on greenfield sites no contingency on the basic house building is to be anticipated Similarly BCIS costs will include contingency where used as a basis for build cost estimation Notice this percentage is not applied to External and other works Such costs are liable to have fees at differing rates and should be entered at a gross of contingencies value 4 11 Statutory payments The amount of money payable primarily for s106 contributions and Community Infrastructure Levy CIL These payments can be divided into 14 types three of which are user defined Typical types of s106 payments may include contributions towards infrastructure public transport community facilities schools doctor s surgery community centres etc and public realm and environment trees landscaping etc The user definable types may be used for items such as Environmental
28. n any as to the quality accuracy completeness or fitness for any particular purpose of such content The Homes and Communities Agency will not be liable for any loss arising out of or in connection with the use of the Model in negligence tort by statute or otherwise Development Appraisal Tool 1 Homes and Communities Agency 1 Context and potential uses Development viability for planning has become an important issue for the local authorities and their partners Recent and forthcoming changes in planning including the Community Infrastructure Levy and the HCA s 2015 2018 Affordable Homes Programme Framework all impact on development viability The HCA Development Appraisal Tool DAT is a site specific development viability tool that is freely available from the HCA website see link below to any organisation that wishes to use it It is designed to inform the development management process by appraising the viability of specific sites As a common format it may also serve as a communication and negotiation tool encouraging a collaborative process The Development Appraisal Tool v4 is intended for use on small and medium to medium large size schemes with a development period of up to fifteen years Uses of the tool could include e Analysis of a scheme when considering whether the level of required planning obligations is viable e Helping a local authority consider the balance between affordable housing and other planning obl
29. ng statistics If car park spaces are being sold separately to dwellings then they will have to entered in Summary 2 Res Values as will any ground rents chargeable 4 4 Residential phasing The tool has a maximum period of 240 months 20 years The scheme start date is taken as the appraisal date entered in the site details sheet all site activities must occur subsequent to this date There is no need to enter an end date the tool will compute this as the latest of all other relevant dates entered In addition to the assessment date timings will be required and highlighted in orange for all tenure phases that have had an associated number of units entered in the model Note that other funding s106 and non residential phasing are included adjacent to the associated costs in the Other funding Res costs and Non res sheets respectively All costs and revenues cash profiles are flat lined but see discussion about multiple phasing possibilities under Residential Details above It is important the monthly sales rate is realistic in the local market circumstances and this value is displayed for checking If the development s projected timetable changes all affected start and completion dates will need to be updated Notice that dates are only displayed for entry on tenure phases that have been entered into the model 4 5 Other funding e RP Cross Subsidy use of own assets A registered social housing p
30. ng the Save As option in the File menu It is recommended that the file is saved as an Excel 97 03 file or Excel 2007 macro enabled workbook in the appropriate directory and that the filename clearly states the particulars of the scheme in question Once the top line of the screen which is identical on all input sheets is displaying a numeric residual valuation result as opposed to a warning message you have a consistent model you may print Depending on what you want to see you might choose The Output Full sheet from grey tab on the right may be printed to give a three page summary starting with the scheme statistics and moving on to a line by line detailed financial residual valuation The yellow summary button on this Output Full sheet compresses the summary down to one page of sub total values from the valuation The GLA style Output sheet gives a one page summary in the format familiar to users of the GLA tool For a closer examination of a scheme finances a quarterly cashflow may be printed Got to Input 0 Setup and select the Advanced user button You will then be able to find a sheet with a grey tab sheet named Output Qtrly CF next to the Full Output Printing this gives a one page cash flow summary but A3 size is required for legibility If hardcopy of inputs is required the relevant input sheets should be printed each is formatted to the minimum number
31. nput error warning Timings are entered as any format Excel recognises as dates e g dd mm yyyy Invalid dates are barred otherwise incorrect results would be produced Restrictions are based on prior input e g see Fig 1b Invalid date Xx Must be after construction start date and before scheme end Cancel Fig 1b Input error restriction It is possible to select the Warnings sheet with Red Tab at any time and this will produce a list of mandatory inputs that remain to be entered Phasing cost and revenue input sheets are automatically compacted to only show tenures and property types that have units entered The top line of input sheets shows the resulting valuation once a consistent set of input has been made Prior to this it displays an incompletion warning The list of missing inputs can be found at any time by moving to the Warning sheet Only sheets with green tabs at the bottom of the screen are input sheets in which users are able to enter data for example as in fig 2 Fig 2 Tabs for input sheets The grey tabs are for output sheets which may be examined to examine results but are only valid when no incompletion warnings remain see fig 3 GLAstyle Output Output Full Fig 3 Tabs for output sheets Development Appraisal Tool 6 Homes and Communities Agency 4 Description of major parts 4 1 Selecting the usage mode Once the legal disclaim
32. on Residential CIL added Bug fix on sixth deferred land payment stamp duty Development Appraisal Tool 19 Homes and Communities Agency 8Technical note on Excel DAT was developed for a PC Windows Excel platform It requires Excel 2003 onwards i e will not function on Excel 2000 The Excel model must be opened with Macros Enabled You may need to modify your Macro security setting please see below for details by Excel version It is suggested no other spreadsheets are opened while the model is in use and only one version is loaded at one time DAT is digitally signed to prove its authenticity Windows Excel v2003 See hitp office microsoft com en us excel help about macro security HP003084611 aspx for details If Excel is set to a medium security setting is then the user is asked whether to enable macros when the model loads to which the answer must be Yes in order to use DAT If you are presented with a dialogue box that questions whether you want to install an Add in this can be declined DAT will still function Windows Excel v2007 Care is required in v2007 onwards to avoid Macros being removed or disabled by security settings If the Excel default is set to save files as xlsx then macros will be removed Use File Save as to save as a xlsm or if in doubt a Excel 97 2003 format If security options are set to prevent macros running then DAT cannot function a Security Alert dialogue box as b
33. on separate lines The transfer routine computes the averages the model requires and copies the data to the normally hidden mix and values input summary sheets Tenure phases without entries in the table will not appear in the subsequent phasing and costs sheets Notice that if the unit control total entered in cell E5 does not match the total input on this sheet as shown in cell C37 then the transfer will abort and post a red warning at the top of the sheet G6 Similarly if there are no annual costs entered in the table to the right then a warning to that affect will be seen Otherwise a green confirmation of success will appear in the G6 Also note that if a revised build values listing is to be entered and transferred this can be done at any stage and will overwrite all previous data This may be useful for testing varying mix options It is not mandatory to use the Res Details batch input sheet if the advanced user setup option is selected then data may be keyed directly instead into the Summary Input and Residential Mix sheets This may be more appropriate where a detailed build plan is not yet known and the user is familiar with direct entry It is also necessary to use Summary 1 Res Development Appraisal Tool 10 Homes and Communities Agency Mix input sheet directly if it is desired to input numbers of rooms and person Neither of these is necessary for the financial computation but may be useful for reporti
34. ow This will have the impact of increasing the present value of a given return percentage 4 15 Non residential This sheet is not visible when the simplified input screens option is chosen the advanced user selection needs to be made There are sections for revenue costs and phasing for office retail industrial leisure and community use 4 16 Notes and memos By keeping any intermediate computations on this page eg on cost computations all information will stay attached to the model itself Input cells may reference these computations directly via input of formula 4 17 Errors and warnings If the top line of input sheets and output summaries show incomplete entry see warnings sheet then this signifies that a necessary input hasn t been entered The warnings sheet should then be selected from its tab to check for listed warning notifications which will inform the user of necessary input missing Conversely a value on the top line shows that input is consistent of course a set of inputs may still need to be added for a particular scheme e g abnormals The checks are simply that each sub set of data is complete If a warning cannot be understood or error found than a copy of the model may be mailed to DATEnquiries Hca gsi gov uk for assistance 4 18 FAQs Where do I obtain the model It is suggested that prior to completing an appraisal a blank copy of the current version is obtained from the HCA website Entering
35. pes sizes numbers and values are entered in the Res Details sheet see fig 10 This may be familiar to some as a GLA toolkit style entry These are entered on a per property type basis with description no of units property type tenure type with phase sales valuation or rental value The property type amp tenure type are chosen from a drop down list This sheet is has a batch control total for the number of units entered above the table This is so the number of properties can be agreed to prior expectation before the data is transferred into the model itself Notice that it always possible to go back and change the property details and re transfer at a later date Property is divided into 13 different unit types throughout bedroom numbers of low rise flats high rise flats and houses Any other types e g bungalow should be entered in the nearest equivalent Five phases of each tenure are available except Social Rent one phase These phases are used so that sales amp build as cost can be calendarised as close to the expected pattern as possible Notice rent entered is that actually payable For AFFORDABLE Rent this will be AFTER REDUCTION from market rent If it is required to be able to test changes in the AR then this must be entered as a formula relating to the market rent ie Market Rent AR cell R10 so type eg 120 R10 Market rent is after allowance for service charge and this comput
36. ples 2 1 Purpose The tool is an open source cash flow model which can model affordable or mixed tenure housing development with scope to capture ancillary commercial uses Based on a series of assumptions about costs values and reasonable profit the model calculates the surplus or deficit created by a given scheme It also allows estimation of the level of affordable housing and other s106 requirements that can be supported by a scheme There is an option to compute a residual land value RLV instead of entering a land value to derive the surplus deficit 2 2 Principles In deficit surplus mode land value and reasonable developer s profit can be treated as inputs and the deficit or surplus arising from a given scheme is an output Input of the current land valuation is a mandatory field in this mode because viability needs to be based on current information Alternatively the RLV mode excludes a land value input which after all other input is the value resulting in zero deficit surplus A key aspect of the model is that it is open in the sense that all parties can see all data and computations which is intended to bolster a collaborative approach The model also demonstrates the financial impact of the level of affordable housing on the development and allows parties to compare affordable housing with planning obligations which consist of financial payments The model is based on the principle that a viability apprais
37. rovider may decide to contribute funds from their own reserves which implies a payment for affordable housing above the computed capital values Such payments will enhance overall scheme viability The level of RP cross subsidy is one of the assumptions that HCA is likely to explore as part of the HCA investment assessment If an RP agrees a payment to a developer for a larger sum than the computed affordable housing valuation then the implicit cross subsidy will need to be entered here to bring up the total scheme affordable revenue to the agreed amount e LA re cycled SHG or s106 commuted in lieu e A local authority may contribute such funds to help achieve its local aims through the scheme 4 6 Residential costs Building cost per square metre or sq ft choose from dropdown box in title of gross internal floorspace for building each type of housing tenure type It is anticipated that low rise high rise 4 floors or more and houses will have differing build costs so separate inputs are provided It is essential that if there is the presence of a given type of housing a corresponding building cost must be entered for that type of housing and these will be highlighted in orange This assumes a serviced plot as a starting point and a ready for let dwelling as an output thus including both substructure and superstructure but not infrastructure back up to pavement but no further Dwelling sizes above the normal range will be autom
38. s reason an IRR measure is also computed see below A surplus suggests the site is viable given all modelled assumptions whereas a deficit measures the viability gap Note this is based on land valuation not purchase price paid Similarly land holding costs are included in the assessment only from the scheme assessment start date Prior holding costs are sunk i e incurred regardless of the develop don t develop action and thus not relevant to the decision The scheme Internal Rate of Return IRR shows the true rate of return for the funds invested in the development of the scheme It is computed before interest charges and developer profit to avoid double counting Unlike crude margin percentages or even a Return on Capital Employed measure the IRR takes into account the full cash flow profile For example other things being equal IRR would be higher for a scheme where the peak capital requirement is for a briefer period such as housing that is built in phases compared to a blocks of flat Notice the IRR is computed based on monthly cash flows for accuracy but shown as an annual rate From v4 positive Cashflows are assumed to be re invested at the input interest rate Modified IRR An alternative tightly summarised scheme presentation is available on the GLA style output sheet which is particularly suited to previous users of that model The remaining outputs require the setup button Advanced User to
39. t service charge costs are determined at design time and assurance this issue has received consideration by that stage is likely to be crucial in determining the final outcome Can the model be used for Support Housing or Extra Care Schemes The basic computations for these tenures are the same as other rented tenures so DAT can be used However since there is no tenure defined in the dropdown list on the property Input 2 Social Rent can be used for the purpose The free text on each line can be used to note the actual tenure arrangements The difficulty with Extra Care schemes in particular is that the range of provision can vary widely Whilst this will influence the level of service charge as this is recoverable from the tenant or benefits it will not affect the scheme residual directly However Extra Care does make it difficult to benchmark build costs which will depend partly on the level of facilities provided It should be noted that the BCIS cost service does include a category for these types of schemes 843 but the variation may require care in matching similar schemes Individual schemes data can be viewed using the BCIS Analyses The Build Net to Gross Adjustment at the top of sheet Input 5 will need to reflect the fact that facilities such as canteens require space to be built that won t be rented to tenants This means the reduction ratio will be greater than for general needs but the precise value can only be
40. ted and displayed at the top of every input sheet Additional sheets may be entered for non residential development plus notes and computations More details are given below in the sub section Description of major parts 2 The Valuation of Land for Affordable Housing RICS May 2010 http www ricsbooks com productInfo asp product_id 18397 Development Appraisal Tool 5 3 4 Homes and Communities Agency Input Entry Simplified or full input can be selected see below but in either case it will be seen that When cells are selected most include pop up text with guidance relevant to the particular input Occasional longer notes can be accessed by pointing the cursor at the red Excel comment tag White cells indicate areas where data may be input into the model Cells appear orange when an input is required e g costs for declared units Cell values may be copied via Edit gt Paste Special gt Values but must NOT BE CUT as this reassigns formula Most cells have warnings for entries outside a feasible value range Warnings will be given when the inputs are outside the expected range and give an indication of the source of the problem as fig 1a below Notice this is designed for simple error trapping of clearly erroneous input it is no way intended to guide values the HCA would deem reasonable Outside of expected size range Please input square metres per unit 1 sqm 10 76 sqft Continue Fig 1a I
41. the resulting affordable housing element valuation see fig 5 Valuation of present value of Affordable Housing at 1 1 2011 2 445 491 Fig 5 The result appears in this format at the top of the screen The remainder of this guide assumes the full scheme viability is to be appraised i e the first or second selection chosen Development Appraisal Tool 7 Homes and Communities Agency 4 2 Site Basic Scheme Info is input into this sheet see fig 9 In particular note the distinction between site purchase price and latest valuation These are key inputs and BOTH are required if financial assistance from the HCA is to be justified Whilst not preventing computations full completion of this short sheet is required X Y co ordinates allow linkage to a GIS system and site area is crucial because density is a relevant factor in assessment of results As dates are entered the model calculates which month of the development each falls into this is measured from the appraisal date which is treated a day 0 thus this is a mandatory input It defaults to the date when the appraisal is first opened and it is reasonable to leave this untouched It may be overwritten with the date at which developer contributions and affordable obligations are to be agreed if this is expected to be significantly later but it must pre date any site activities Basic Site Details FULL VIABILITY ASSESSMENT Mixed tenure examplar with dummy data 1 00 0 81
42. ut selection allows date phasings to be entered before any unit input Build Cost input by Sq Ft now possible Scheme start auto computes from Assessment Date Fixed protection lock out of Other Acq Costs Corrected Decant phasing GLA style output values amended to include phase 2 AR amp LCHO CIL amended to work with Gross Net Area instead of Net Internal Area Some phasing date validation amendments Fix for error erroneous error message appearance on GLA style output Monthly Sales rate computation amended Abnormal items Other 2 phasing amended to spread correctly Ground rents applied only to flats Allows computation of the Residual Land value Fixes to Private Rental amp car parking Tested under Excel 2010 Fix to Private rental Margin Separated Private Rental Return Fixed Other Acq Phasing error Memo Gross Floor areas shown on Input 5 Col J corrected no impact on computations Affordable Housing direct input of Capital Values enabled Deferred land payments schedule allowed Developer s return spread phasing option Stamp duty for deferred payments corrected Qtrly cashflow revenue row alignment corrected Deferred payments validation issue fixed Five phases now available for Affordable Rent Private Rent amp Shared Ownership Five phases of infrastructure items now available Extra Statutory cost lines Contingency no longer applied to fees NOTE THIS WILL CAUSE SOME VARIANCE IN RESULTS COMPARED TO V3 N
43. verified by checking against the drawings How should VAT be treated The model transfers all entered costs as cash flow items and does not make any VAT reclaim computations Therefore all costs should be entered net of any reclaimable VAT but gross of any irreclaimable Development Appraisal Tool 14 Homes and Communities Agency 5 Understanding the results Once all of the various inputs have been entered into the relevant sheets the results are displayed on the Output Full sheet The sheet is collapsible by pressing the yellow Summary button at the top of the sheet which is then in a summary format suitable for single page printing The Detail button alongside reverses this action to display all lines The top section of the detailed sheet provides a comprehensive examination of the mix and values of each housing type as well as the key values and costs It is a good place to check the reasonableness of results in proportion to the remainder of the scheme It will print on three pages The final summation figure near the bottom of the Output Full sheet is the site surplus or deficit at completion This is then discounted back to the scheme start date to provide that value at the decision date and this is repeated at the top of every input page As per industry norm this is carried out using the interest rate There are reasons to regard this rate as less than the ideal for this purpose and for thi
Download Pdf Manuals
Related Search