Home

Development Appraisal Tool user manual v2.02 (PDF 348KB)

1. SS 5 45 1 Bed Flat Lowrise Shared Ownership phase 1 130 500 WW G 7 67 2 Bed FlatLowrise Shared Ownership phase 1 194 300 WW CG ee e haya yan ow Fig 10 Input of the residential build mix and values into the Res Details sheet An Annual Costs table is also present for rented tenures see fig 11 These are used in computing the capital value of the affordable housing the annual costs being deducted in computing the net rent Fig 11 The annual costs input Orange cells will appear against populated tenures This input sheet is a scratch pad area which may be revised as required without direct impact on the model Once all entries are complete and so no orange cells remain then the grey Transfer button on the top of the sheet should be pressed and 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 Development Appraisal Tool 10 Homes and Communities Agency separate numbers sizes and values or rent may be entered 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 phase
2. 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 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 procedure as less than economically ideal and for this 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 Notice that in contrast to earl
3. of the tool is acknowledged The tool can also be used to value the capitalised net income cash flows arising from the affordable housing element of a scheme in isolation i e this gives the price we should expect an RP to pay for the Affordable Housing Reporting of the internal rate of return for the scheme a discounted cash flow measure This takes account the time profile for which capital is invested which particularly differs between housing and blocks of flats Thus it is a more complete measure for comparisons than just the margin on sales value Land value is treated as an input but the model can be used to calculate a residual land value output in the same way the EAT The model allows rapid scenario testing The financial impact of tenure changes both between affordable tenures and to open market are estimated using contribution analysis Uploading of data for collation of benchmarks is possible Version List of maintenance updates V1 0 V1 01 V1 02 V1 03 V1 04 V1 04b V2 0 V2 02 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 input 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 p
4. and medium size schemes with a development period of up to ten years The model has been developed from the EAT by the HCA and has been independently tested for its accuracy of calculation 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 planning obligations and affordable housing 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 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 of the HCA or email DATEnquiries hca gsi gov uk Limitations A maximum of ten years of monthly cash flows can be modelled Five sales phases plus two phases for Affordable Housing and private rent are available All Cash flows are mod
5. is a final option which includes some further analytical output sheets In both these more advanced options the screens to be displayed are EAT style GLA toolkit style or by default both The EAT style shows only screens equivalent to the previous EAT model screens and does not hide unused tenure phases GLA style shows the new scratchpad entry and hides unnecessary tenure phase dates and costs The simplified option is recommended it is always possible to return to this screen at a later stage and reveal the additional screens Development Appraisal Tool 8 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 For example 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 it is a mandatory input This defaults to the date 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 affo
6. latest reality Further detail can be recorded in the notes sheet Each sheet may be printed if paper copy is desired The Quarterly Cash Flow Output Full set to summary level and GLA Style Output sheets should print neatly onto one sheet of A4 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 11 15 Development Appraisal Tool 16 Homes and Communities Agency Analytical tools These are accessed by pressing the Add Tools button on the Input 0 Setup 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 ar
7. 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 Context and potential uses Development viability for planning is an increasingly important issue for the local authorities and their partners Recent and forthcoming changes in planning including the introduction of the Community Infrastructure Levy and the HCA s 2011 2015 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 DAT accommodates the changes in affordable housing provision being introduced in the 2011 14 spending review period and supersedes the HCA s 2009 Economic Assessment Tool EAT v2 planning obligations viability tool A complementary HCA Area Wide Viability Model is also available which is designed to inform strategic viability modelling to evidence and test policy for affordable housing targets and CIL across a wider area The Development Appraisal Tool is intended for use for small
8. 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 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 selection chosen Development Appraisal Tool 7 Homes and Communities Agency 4 1 Set up sheet The setup option concerns a user selection of screens to be displayed no computations are affected simply the screens displayed The simplified data entry option is the quickest completion method as in fig 7 PRESS for Development Control mode gt gt gt gt gt Simplified Screens Input amp Output formats similar to the GLA toolkit Fig 7 Selection of the simplified entry screens The advanced user option has more extensive input calculation and output sheets see fig 8 Press gt gt gt Advanced user lt lt view more outputs and calcs EAT v2 format outputs plus cash flow computations Fig 8 Selection of the advanced user option There
9. the Trust Center Windows Excel V2010 DAT V2 1 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 OpenOffice amp LibreOffice Calc Will not run Visual Basic for Application Macros consequently DAT doesn t function Mac Excel 2008 Will not run Visual Basic for Application Macros consequently DAT doesn t function Mac Excel 2011 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 Email DATEnquiries hca gsi gov uk for further details Development Appraisal Tool 20 Homes and Communities Agency Development Appraisal Tool 21 0300 1234 500 www homesandcommunities co uk mail homesandcommunities co uk Homes amp Communities Agency Homes amp Communities Agency Maple House 149 Tottenham Court Road London W1T 7BN The Homes and communities Agency will consider providing literature in alternative formats upon request Please contact us on 0300 1234 5000 or by email at mail homes and communities co uk for further information Published April 2011
10. 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 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 It is 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
11. AT will still function DAT v2 no longer requires the Analysis Toolpak to be installed 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 below 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 Microsoft Office Security Options 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 from a 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 E Open
12. Homes amp Agency Communities DEVELOPMENT 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 Look and feel 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 4 5 Residential costs 4 6 External works and infrastructure 4 7 Site abnormals 4 8 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 Key changes from the Economic Assessment Tool 7 Technical note on Excel 8 The Homes and Communities Agency makes no warranties representations or undertakings about any of the content contained in this Model including without limitation any as to the
13. alues as will any ground rents chargeable 4 4 Residential phasing The tool has a maximum period of 120 months 10 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 Golden Brick scenarios are not covered Notice that dates are only displayed for entry on tenure phases that have been entered into the model unless the EAT style displays have been specifically requested from the setup sheet in which case all lines are shown 4 5 Other funding e RP Cross Subsidy
14. 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 particular 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 e Are there special requirements e g underground parking local standards requirements Development Appraisal Tool 3 Homes and Communities Agency 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 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 via
15. bility assessment 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 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 the relevant part of the model including precise definitions of terms such as build cost and abnormals The affordable rent valuation is computed 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 liable to be subject to both comparison with local market information sources and sensitivity testing Affordable rents entered in the sheet Res details a
16. cross 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 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 Development Appraisal Tool 12 Homes and Communities Agency etc and public realm and environment trees landscaping etc The user definable types may be used for items such as Environmental 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 and one for a LPA per unit tariff All others values are a total scheme sum All values set require a date for phasing EXCLUDES s106 housing unless a c
17. data for example as in fig 2 Fig 2 Tabs for input sheets The grey tabs are for output sheets which may or may not be examined depending on user requirements see fig 3 le Output Output Full Fig 3 Tabs for output sheets Development Appraisal Tool 6 Homes and Communities Agency Description of major parts Selecting the usage mode Once the legal disclaimer is pressed in version 2 the Usage Mode is presented for selection as in Fig 4 below this determines the type of output from the model The top option is the standard selection that gives equivalent Viability Assessment functionality to version 1 The second option is similar except that no land valuation is entered and the model computes to the Residual Land Value This may be relevant for example where public land is being valued for 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 all 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
18. e been added 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 ie Market Rent AR cell R10 so type eg 120 R10 Market rent is after allowance for service charge and this computation 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 over different periods Such phasing can be crucial as cash flow income from open market sale is pivotal to scheme s viability Developer s own shared equity products should be included in the open market build units on the Phasing and Valuation sheets _ 80 Populates unit numbers sizes values and rents AND annual c The number of units remaining to be entered 0 Transfer COMPLETED Ok Pe 33 Si Bea Fiat Lowrise Aforaable Rent phase 1 WY nn 6 45 i Bed Flat Lowrise _ Affordable Rent phase1 KX 72 73 12 67 2BedFlatLowrise Affordable Rent phase 1 WW _82 38_ CE 85 3 Bed Flat Lowrise Affordable Rent phase E a 4 114 8BedFlatLowrise Affordable Rentphaset NW 10297
19. e parameter Using this method it is possible to have two phases of affordable rent charged at separate percentages of market rent For both the affordable and open market housing have been ignored in this development appraisal tool These charges are payable by the individual occupiers of the units and therefore do not have any impact on the scheme residual 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 most 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 There is a simple proforma in the notes and memos sheet that may be used to record the market rent service charge percentage of market affordable rent computation VAT 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 Understanding the results Once all of the various inputs have been entered into the relevant sheets the results are displayed on the
20. e 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 Eea n INPUTS Sales Value Sales Value Sales Value Sales Value Open N A o 0 in 0 Shared 0 0 0 0 35 35 35 35 changein changein changein change in Building Building Building Building Costs Costs Costs Costs ALL 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 00 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 1 500 000 OUTPUTS P 4 Fig 12 A combination of changes that would be suff
21. eclared units Most cells have warnings for entries outside a feasible value range Warnings will be given when the inputs are outside the allowed 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 Input error warning Timings are entered as dates not month numbers as in EAT v2 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 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 where users may need to enter
22. elled as linear across the specified period though phasing can be used to approximate curves http www homesandcommunities co uk sites default files our work affordable homes framework pdf http Awww homesandcommunities co uk ourwork area wide viability model http www homesandcommunities co uk ourwork development appraisal tool Development Appraisal Tool 2 Homes and Communities Agency Purpose and principles 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 In version 2 onwards 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 By default the model is a gap funding tool in that 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 because viability needs to be based on current information However where the objective is to arrive at a residual land value then land values should to be iteratively inpu
23. f 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 using 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
24. icient for the surplus to match the land valuation 2 Sensitivity analysis can be completed within the tool for increasing decreasing build costs and capital values s106 costs 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 Development Appraisal Tool 17 Homes and Communities Agency column 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 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 deta
25. ier EAT versions of the tool there is no HCA financial support or grant included in these computations 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 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 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 o
26. il 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 s106 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 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 Affordable 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 ph
27. 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 computed 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 3 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 Look and feel Simplified or full input can be selected see below but in either case it will be seen that When cells are selected many include pop up text with guidance relevant to the particular input Longer notes are accessed by pointing the cursor at nearby cells that have the red Excel comment tag Cells appear orange when an input is required to maintain consistency eg costs for d
28. ommuted sum payment is required 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 each month on the scheme as a whole 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 If the developer has other variable borrowings such as an overdraft or other investment opportunities then the value of credit balances in reducing overall finance charges is potentially the same as the debit interest charge If not and the developer would simply put the funds into the bank then a lower rate is appropriate 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 and for any internal funds committed Development Appraisal Tool 13 Homes and Communities Agency 4 15 Non residential This sheet is not visible when the simplified input screens option is chosen the advanced user selec
29. rdable 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 1 700 000 1 01 2007 1 500 000 1 01 2011 oo ao H n c rad 3 5 3 ec lt Qa S ot D pn Fig 9 The basic site details Note that if the Residual and Value option is chosen then a Land Valuation input is not required this value will be the output of the model once all other input has been completed 4 3 Residential details Where the Simplified option is chosen in the Setup sheet property types 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 basis for each property type and also displayed by the tool as per square metre information A control total for the number of units is entered above the table Development Appraisal Tool 9 Homes and Communities Agency 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 There are two build phases of shared ownership and affordable rent available and one of social rent Five open market sales phases are included and finally two private rent phases hav
30. re actual values expected to be charged there is a simple proforma in the notes and memos sheet that may be used to record the market rent service charge percentage of market affordable rent computation see FAQs regarding testing the affordable rent parameters Traditionally 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 Either method is appropriate provided suitable capitalisation rates are used 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 estimates of potential values arising from future staircasing sales 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 in the tool workings and trust in results from all parties Note It is necessary to select the advanced user option to view complete detail 3 3 Overview The tool is broadly divided into three sections Inputs Computations and Output The user needs to work through a number
31. rotection 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 Development Appraisal Tool 19 Homes and Communities Agency Technical note on Excel DAT was developed for a PC Windows Excel platform It requires Excel 2003 on 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 http office microsoft com en us excel help about macro security HP00308461 1 aspx for details It is suggested no other spreadsheets are opened while the model is in use and only one version is loaded at one time Windows Excel v2003 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 D
32. s without entries in the table will not appear for 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 cell 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 cell G6 Also note that if a revised build 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 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 Indeed data may first be transferred via the Res Details sheet and the advanced user option be used later to amend the transferred data This may be more appropriate where the user is familiar with previous EAT versions of the tool It is also necessary to use Summary 1 Res 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 needed for reporting statistics If car park spaces are being sold separately to dwellings then they will have to entered in Summary 2 Res V
33. t or an Excel goal seek used until a zero surplus is arrived at such a land value is the residual 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 appraisal 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 2011 2015 Affordable Homes Programme Framework document includes the following comments on s106 schemes In order to ensure the best value for public resources there will be a strong presumption that funding is not required for affordable home ownership on s106 sites as planning gain is expected to provide sufficient subsidy 4 6 The Development Appraisal Tool is therefore primarily intended to assist in determining viability 2 4 Values for
34. t be disaggregated and included in the appropriate cost figure not the works figure 4 7 External works and infrastructure 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 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 quantity 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 a
35. t not infrastructure back up to pavement but no further Usually current building costs are used identified from sources such as BCIS with whom definitions are similar 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 There is now no separate sustainability cost multiplier It was recognised that to fully cost a scheme configuration that was not to be built was onerous for small developments The HCA is likely to consider the updated DCLG Cost of Building to the Code for Sustainable Homes Aug 11 when evaluating such bids Additionally evolving experience 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 Notice that Net to Gross adjustment definition is changed from EAT and now 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 the works These fees mus
36. tion 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 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 4 18 FAQs Where do 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 development viability tool into the search box on the home page will find the relevant page from which a download maybe made Affordable rent and service charges 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 th
37. use of own assets A registered social housing provider 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 LAre 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 Development Appraisal Tool 11 Homes and Communities Agency and superstructure bu
38. ysical 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 Key changes from the Economic Assessment Tool The Development Appraisal Tool was developed by the HCA from the Economic Assessment Tool EAT provided by GVA Grimley with a number of modifications There are more tenure options around the new affordable rent and private rented units plus more private sales phases HCA support grant or investment is not an input rather it can be considered if the output of the model shows a deficit The cash flow and computations are open i e can be made visible to users The user interface has been significantly amended Entries are validated against expected ranges user guidance is embedded and input screens redesigned with specific colour coding Input values may be set to either per square foot or per square metre There is a rapid input option for mix and values on a single sheet A list of mandatory entries still required can be seen at any time Advice from M3 Ltd with regards to this aspect

Development Appraisal Tool user manual v2.02 (PDF 348KB)

Contents

Download Pdf Manuals

Related Search

Related Contents