Capacity Model Guidance Note

Document Sample
Capacity Model Guidance Note Powered By Docstoc
					Capacity Model Guidance Note Introduction This document is designed to give a brief over-view of the Capacity Model to help associations with the completion of version v2.0 for submission to the Housing Corporation. It includes a conceptual overview to help people understand the purpose, functionality and limitations of the Capacity Model. Also included are two appendices, one setting out line definitions and one listing common input errors and their solutions. What is the Capacity Model? The Capacity Model is a business planning tool that has been designed to serve two purposes: 1. To be a free and easy to use financial model for small associations that do not wish/cannot afford to purchase a model from one of the main commercial suppliers. 2. To enable the Housing Corporation to collect the financial plans of developing associations in a consistent and transparent fashion and with the ability to flex the underling assumptions. It is on the second of these two purposes that this document concentrates. If associations are interested in using the Capacity Model as their main business planning tool then the best place to start is the on-line help file, available at: http://www.fsmd.co.uk/capacity/home.html The rest of this document assumes that users are looking to submit information to the Housing Corporation and that they already have a long term business planning tool that they want to replicate within the Capacity Model. However, the online help file also contains a lot of detailed information that all users of the Capacity Model will find useful. How does the Capacity Model work? In essence the Capacity Model works by replicating an association's business plan so that the information can be flexed. To enable this to happen associations have to input two main types of information:   Results from their existing business plan Assumptions that underlie that business plan

The Capacity Model then effectively multiplies through the assumptions that it has been given and works out how different the results are from the figures produced by the associations own business plan. It then works out a series of reconciliation factors that are effectively how much the model needs to multiply its answers to arrive at the same result as the association's own business plan.

Page 1 of 25

For example, If in Year 1 , the association's assumptions result in a turnover figure of £11m, but the association's model has a figure of £10m, the Capacity Model will multiply its answer by 0.91 to match the HA's result. However, If in Year 1, the association's assumptions result in a turnover figure of £9m, but their model has a figure of £10m, the Capacity Model, will multiply its answer by 1.1 to match the association's answer. Once the macro has calculated these reconciliation factors they become an integral part of the model and are used whenever any sensitivities are carried out. They help to capture a variety of information that is not recorded by the assumptions used in the model itself. Why do reconciliation factors occur? There are many reasons why reconciliation factors occur, partly its because of the level of assumptions used in the Capacity Model, which are much more high level and broad brush than would be the case in bespoke business planning and partly they are the result of timing differences and step changes in costs that occur in the HA's model. Whatever the cause, the existence of reconciliation factors is not something to worry about. The key issue is getting the input of assumptions as complete and accurate as possible. The Capacity Model and Input Files The Capacity Model itself is a programme written in Visual Basic that operates in Excel, it works by storing data in a dedicated input file which is a separate Excel spreadsheet. When it comes to entering data into the Capacity Model this can be done by either inputting into the Capacity Model and then saving into the input file or by inputting the data directly into the input file and then importing into the Capacity Model In general most associations will find it easier to input most of their data directly into the input file only changing from within the model as necessary. Structure of Capacity Model Data Whether data is entered into the input file or directly into the Capacity Model there is a certain logic to how data must be entered. Essentially data falls into three categories: 1. Results from HA's existing business plans 2. Explicit assumptions/information that must be entered by the user 3. Derived assumptions that are calculated by the Capacity Model when the reconciliation macro is run. The input file and the model are ordered in the following way:

Page 2 of 25









Business Plan Accounting Data: This is where the bulk of the results from the HA's business plan is entered, including the I&E account, Balance Sheet and Cash Flow Forecast. Reconciliation Factors: These are stored next on the input file, and are never entered by associations, instead they are always calculated by the model. General Assumptions: These are a mixture of explicit assumptions that have to be input by associations and assumptions calculated by the model. The cells that HA's have to input into are shaded blue and the derived cells are shaded yellow. Individual Property Sections: This is where HA's have to input information that is specific to the different types of property they own: General Needs (entered on the sheet called Housing Accommodation), Supported Housing, Shared ownership etc. Within this section there is a mixture of all three types of data input. The blue cells are the explicit assumptions that HA's must input where appropriate, the yellow cells will be calculated by the model when the reconciliation macro is run and the green cells are a breakdown of information from the HA's results. For example, on the Business Plan Accounting Data there is a figure for rents receivable for the organisation as a whole, but on each individual section there is a figure for the rents receivable for those types of accommodation. When added together the figures on the individual property section must add back to the total figure shown on the Business Plan Accounting Data

To complete the model associations must complete the Business Plan Accounting Data section and then all the other sections as appropriate. Line definitions are set out in appendix one. How to complete the Capacity Model As mentioned above the easiest way to complete the Capacity Model is to input directly into the input file and then load this into the model and run the reconciliation macro. A blank copy of the input file can be found on the Housing Corporation website along with the model itself. Some associations might find it easier in the long run to link a copy of the input file into their own business plan and then copy and paste the values from this into a blank copy of the input file to load up into the model. In this way the links can be maintained and any change in the association's own model can be swiftly updated within the Capacity Model. Which sections need to be completed? As a minimum associations must complete the Business Plan Accounting Data section, the General assumptions section and then as many of the property sections as apply to them. How to get the input file into the Capacity Model

Page 3 of 25

Once users have completed the input file they should save it somewhere on their directory. They should then open up the Capacity Model by double clicking on the file Capacity_Version2.0.xls. When this opens they should then select the 'Single association' option on the Green box in the centre of the screen. This will take them to the 'Retrieve Data' screen. Once here users should click on the grey 'Retrieve Data Now' button on the left hand side of the screen, this will open up a standard Excel 'Open File' dialogue box, they should navigate to where they have saved their file and then open it up. The Capacity Model will then import the data from the input file and populate the model. If this is the first time the file has been opened within the model than users should also input their HA name, registration code etc. into the input boxes on this screen. Users should then click on the 'Input Data' option in the grey box in the top left hand corner to take them into the data side of the model. From here users can then run the reconciliation macro by pressing the grey 'Reconcile Model Forecast' button. If the reconciliation macro runs successfully (see below for what to do if it is not successful) then users should save their data file at this point, using the 'Save' option on the bottom of the green/grey box in the top left hand corner. This is to save the reconciliation factors into the data file so that the model can subsequently be flexed and the Monte Carlo module used. What happens if the reconciliation macro does not work? Sometimes when the reconciliation macro is run users will be presented with an error message and be taken to a screen called 'Data Validation'. If this is the case then it is because there is some internal inconsistency in the data that has been entered. In total the Capacity Model looks at fifteen areas where the data had to be internally consistent and the results of all fifteen checks are shown on this sheet. The first four are about the consistency of the financial statements provided on the Business Plan Accounting Data section, these are:     Does the balance sheet balance? Does the cash flow balance? Is the Surplus Before Interest and Tax (SBIT) figure consistent between the I&E account and the Cash Flow Forecast? Is the interest payable consistent between the I&E account and Cash Flow Forecast (adjusting for capitalised interest)

The remaining eleven are making sure that the information given on the individual property sections are consistent with the information in the Business Plan Accounting Data sheet (i.e. if you sum the totals for the completed property types do they equal the figure for the business as a whole?) The eleven items are as follows:  From the I&E Account

Page 4 of 25



o Rents receivable o Service income o Charges for support services o Total revenue grants o Voids o Bad debts o Capitalised repair costs From the Cash Flow Forecast o SHG received o SHG paid back o Purchase of properties (i.e. cost of new properties) o Sale of properties

Where any of these fifteen items are inconsistent then the years in which they are wrong (and by how much) will be displayed on this sheet. If this is the case then users will need to look at their data and correct the inconsistencies. This can be done by either correcting from within the Capacity Model and then rerunning the reconciliation macro (and then resaving the file) or by shutting the model down and editing the input file and then loading it back into the model. Checks to carry out once the reconciliation macro has run If the reconciliation macro has run successfully then users should go to the 'Reconciliation Check' sheet, within the 'Input' section. This is a key sheet as it tells users how far apart the Capacity Model version of their business plan is from the information they input on the Business Plan Accounting Data sheet. It does this by taking the results calculated by the model away from the results input by the user. Ideally all the values on this sheet should be zeros, but there will be a number of occasions when this will not be the case and users will need to make their own judgement about the materiality of the differences. In general we would expect models sent to us to be all zeros, but if there are issues with your model then please speak to your local field office. Appendix two of this document sets out some of the common problems that cause the model to work incorrectly, but in general they can be summed up by 'completeness of information'. The more information has been entered into the model the more accurate its forecast will be. Final checks before flexing If the model is to be flexed with any degree of accuracy then it is important that all the relevant information has been entered. Most of this information has to be entered to get the model to reconcile and balance correctly, however the following item of information can be omitted and still produce a reconciling model, but the flexes will then be inaccurate:  The marginal costs associated with the addition of a new unit: o Management costs o Service costs o Care and support costs

Page 5 of 25

o o

Major repairs costs Ongoing repair costs

Once these have been added then the model can be used as a powerful analysis and flexing tool. It is important that accurate information is entered here (as far as possible when averaging for step changes) as this data is critical when running scenarios.

Page 6 of 25

The Analysis Sections of the model Once all the checks listed above have been completed then the analysis section of the model can be used to understand the dynamics of the business plan and to carry out sensitivities and the use of the Monte Carlo tool. Structure of the Analysis Section The Analysis section has three main elements:  Financial Statements: These are the three main financial statements in the same format as the Business Plan Accounting Data section, plus a restatement of the I&E account in standard SORP format. Ratios and Graphs: A series of ratios, credit maps and graphs to help users understand the interplay of key factors within their model. Flexing and Scenarios: The ability to flex key variables within the model and to run a Monte Carlo analysis.

 

In order to get the best use out of the ratios and credit maps there are a number of places users can go for additional guidance: How we regulate viability: http://www.housingcorp.gov.uk/upload/pdf/HWR5_Viability_final_20050921150806. pdf This defines many of the ratios and indicates the areas the Corporation are particularly interested in. The online help file: http://www.fsmd.co.uk/capacity/ratiodefinitions/ratio-definitions.html This also gives explanations of the ratios and is particularly useful for understanding the credit maps Running Sensitivities There are two main ways to run sensitivities, one is via a Monte Carlo simulation discussed below, the other is a more standard flex of key variables. The most important thing before running either type of sensitivity is to have a model that reconciles with only non-material differences between the HA's own figures and the Capacity Model figures and crucially that the model has been saved after it has been reconciled and before the sensitivities have been run. Only by doing this will the reconciliation factors created by the model be used in running the sensitivities. Stage 1 The first thing to do is load the 'Association Case', this is done by ensuring the text 'Association Case' is shown in the box near the top of the screen and then clicking on the 'Load Scenario' button on the left-hand side.

Page 7 of 25

This brings up the key assumptions used in the model as defined by the association itself. These are effectively the variables that can be flexed. Stage 2 The next step is to change the variables that need to be flexed, the Capacity Model allows users to flex as many variables as desired. There are two ways this can be done:   Typing over the individual figures in the blue boxes on the right-hand side Launching the 'Scenario Manager' by clicking on the button on the left-hand side. This launches a dialogue box that allows users to apply changes to variables in all thirty years at once

Stage 3 Once the changes have been made as the user requires, then they should select a 'Case' to save the data under. The Model allows users to store data for up to five different scenarios in addition to the Association Case. To save the flex, users should select a Case name from the drop down menu at the top of the screen and the click on the 'Save Scenario' button on the left hand side. This saves the scenario into the programmes memory, but does not yet save it into the data file. To save the scenario into the data file so that it can be recalled later when after the model has been shut down and reopened, users should save the model as a whole. Stage 4 There are now two ways that this scenario can be used:  By clicking on 'Apply Selected Scenario' it will be applied to the model and all the information in the analysis section will reflect the impact of the new variables. By clicking on 'Show Sensitivities' a macro will run that applies all the saved scenarios and stores certain key output ratios in the 'Scenario Output' section. This allows users to compare the impact of the different scenarios.



Stage 5 Once a scenario has been saved it can be recalled at any time by selecting the case name in the drop down box and selecting 'Load Scenario' it can then be run or altered as required. Monte Carlo Tool The primary object of the Monte Carlo tool is to model the impact of changes in key economic variables on the viability of an association. It does this by simulating realistic future economic cycles, consistent with patterns of inflation rates, nominal interest rates and real interest rates experienced over the last 35 years. Values are generated by the model for the key economic drivers to represent these cycles and

Page 8 of 25

then run through the model and the key output variables calculated and captured. The model does this 500 times using randomly generated cycles and then statistically analyses the key output variables. The simulation takes less than 2 minutes to run through the 500 cases. Select “Monte Carlo Simulation” from the “Analysis” section of the model (it is the last menu item). This takes you to the single Monte Carlo Simulation sheet. The top half of the sheet will display the analysis of the input variables generated by the model and the bottom half displays the analysis of the key output variable and two charts of that information. Statistical Analysis of Input Variables This displays the average, maximum and minimum values each year and for the 30 year forecast period for each of the following key input variables generated by the model: RPI % Management cost inflation (margin above/(below) RPI) % Building cost inflation (margin above/(below) RPI) % Major repairs and maintenance cost inflation (margin above/(below) RPI) % Ongoing repairs and maintenance cost inflation (margin above/(below) RPI) % Real LIBOR (margin above/(below) RPI) % Output Report The key outputs relate to Cash interest cover and are a statistical analysis of the 500 simulations. They are: Simulation yearly average 95% confidence interval – upper limit 95% confidence interval – lower limit Probability of cash interest cover below 1.0 Each of these sets of data is plotted on two charts. View / Change Settings The risk or input variables are generated by the model on a random basis but with statistical constraints imposed by the user. Selecting “View/Change Settings” allows the user to view the default settings for each of these parameters, change them, apply the changes to the current simulation or save the changes as the new default. If you accidentally overwrite the default settings you can change them back quite easily as the original settings are included as a note in the labels on the setting form. Run Simulation To run a new simulation select the “Run Simulation” button. The simulation will take about 2 minutes before the model populates all the Input data and Output Report.

Page 9 of 25

If you change any of the original settings and want to measure the impact you have to run a new simulation.

Page 10 of 25

Using the Consolidation Tool Within v2.0 there is the scope to consolidate the results from a number of different models and to bring in the results from organisations that do not have models. Launching the Consolidation Tool The tool has to be launched from within the Capacity Model, it can be accessed from the Home page and is launched by clicking on the 'Consolidation Module' option at the bottom of the green box ion the centre of the screen. When given the option users should then click on continue. Selecting the models to be consolidated Users should click on the 'Create Consolidated Plan' option from the box in the top left hand corner, they will then be presented with a dialogue box that allows them to select up to six existing input files to consolidate. Once they have been selected users should click on the 'Consolidate' button on the bottom of the dialogue box. Once this is done users should then save the consolidation file under a new name. The consolidation model now shows the consolidated business plan (and ratios) of the combined plans. Creating consolidation adjustments In order to knock out inter-company balances or bring in the results of organisations (including unregistered subsidiaries if required) HA's can enter these changes on the Consolidation adjustments sheet. These simply entered in the appropriate year and row as required. Analysis Section The analysis section of the consolidation model is the same as for the standard Capacity Model.

Page 11 of 25

Appendix 1 - Line Definitions
Thos items underlined do not have to be completed by associations General Assumptions Benchmark Inflation RPI (%) - this is a key assumption on which virtually all the models income and expenditure items are based. But the model is built so that it can be flexed to test alternative scenarios.

Other Inflation - Margin above/(below) RPI Management cost inflation (%) - when added to the assumed RPI this gives the annual inflation rate which will be used to inflate both the current level of management costs and the current per unit management cost for each business stream. Service cost inflation (%) - when added to the assumed RPI this gives the annual inflation rate which will be used to inflate both the current level of service costs and the current per unit service cost for each business stream. Care and support cost inflation (%) - when added to the assumed RPI this gives the annual inflation rate which will be used to inflate both the current level of care and support costs and the current per unit care and support cost for each business stream. Building cost inflation (%) - when added to the assumed RPI this gives the annual inflation rate which will be used to inflate the current per unit build cost inflation for each business stream. Major repairs and maintenance cost inflation (%) - when added to the assumed RPI this gives the annual inflation rate which will be used to inflate both the current level of major repairs and maintenance costs and the current per unit major repairs and maintenance cost for each business stream. Ongoing repairs and maintenance cost inflation (%) - when added to the assumed RPI this gives the annual inflation rate which will be used to inflate both the current level of ongoing costs and the current per unit ongoing cost for each business stream. Funding Real LIBOR (%) - the associations assumption about the likely rate of LIBOR in each of the forecast years. Expressed as a margin over RPI. Average rate on fixed rate debt (%) (absolute rate) - these rates will always be applied to the calculated average amount of fixed rate debt to determine the fixed interest payable, each year, which is part of the total interest. Average rate on floating rate debt (%) (margin over LIBOR) - this margin will be added to the assumed RPI and applied to the calculated average amount of

Page 12 of 25

floating rate debt to determine the variable interest payable, part of the total interest. Investment/deposit rate (%) (margin over RPI) - this will be added to the assumed RPI and applied to the calculated average amount of cash and investments to determine interest receivable. Fixed rate debt % total debt (%) - this will be applied to the calculated total debt at each year end to determine the amount of fixed-rate debt. Scheduled debt repayments (£000s) - these can only be negative amounts; increases in debt should go on other lines. Increases and decreases should not be netted off. Cash and short-term investments (£000s) - this is the level of cash balances each year not the change in cash balances. It should be the expected level or the minimum level required in the light of funding and liquidity policies. The model will deal with the cash consumed or cash generated by adjusting the level of medium and long-term debt. It will only adjust the specified cash balance if surplus cash is generated and all debt has been repaid: Increase in medium and long-term debt (£000s) - Increases and decreases should not be netted off. Effective tax rate (%)- this is the expected effective I&E tax rate which will be applied to surplus before tax to calculate the tax charge. If allowances result in no tax being payable this needs to be calculated prior to input. Debtors and other current assets % turnover (%)- based on past experience, this ratio of debtors etc to total turnover from social housing lettings, other social housing activities and non-social housing activities. Creditors and other liabilities % expenditure (%)- based on past experience, the ratio of creditors etc to social housing expenditure. Housing assets average depreciation rate (%)- this is the rate that will be used to calculate the housing assets annual depreciation charge for Income Statement and Balance sheet purposes. Other assets average depreciation rate (%)- this is the rate that will be used to calculate the other assets annual depreciation for Balance Sheet purposes. The depreciation amount is not charged to the Income Statement because, under standard accounting presentation for the sector, other depreciation is lost in overheads. Impairment cost (£000s) - a straight input of any anticipated impairment cost is required since this is often zero or insignificant and impossible to model generically. It will be taken direct to the Income Statement and deducted from the Balance Sheet value of housing properties. Capitalised interest (%)- what is required is an estimate of the percentage of total interest that is likely to be capitalised, reflecting the amount of borrowing to support development activity. This will be deducted from the I&E interest charge but

Page 13 of 25

included in cash interest paid on the Cash Flow Statement and added to the Balance sheet value of fixed assets. Development department costs (£000s) - a straight input to be taken direct to the Income Statement and deducted from general overheads. Weeks in year (Units) - to allow any adjustments to ratios. Investment in other fixed assets (£000s) - a straight input required of the forecast capital expenditure on other fixed assets, which tends to be "lumpy" and difficult to model in a simple way. Will be added to other capital expenditure for Cash Flow and Balance Sheet purposes. Total number of employees (Units) - ideally the estimated average number of employees during the year. This is only used as a measure of association size and in a few efficiency ratios, not to drive key financial parts of the model. Annual property revaluation rate (%) - if properties are partly or wholly revalued, either annually or periodically, this assumption needs to quantify the likely revaluation amount as a percentage of the gross value of housing properties eg if 25% are to be revalued each year by an estimated 10% the input would be 2.5%. This will be applied to the opening balance of housing properties at cost.

Page 14 of 25

Social Housing - Property Based

All the following nine business streams have identical input screens, although there are some additional lines for shared ownership; Housing Accommodation Supported Housing Short-life Housing PFI Residential Care Homes Rent Receivable Average weekly rental income (£s) - per - per - per unit - per existing unit new unit sold/demolished transferred unit Shared Ownership Key Workers Foyers Other

- these should all be the current average for the particular business stream. May be different values or not as appropriate. All will be inflated by the assumed inflation rate for the particular business stream. RPI differential +/- (%) (Rent) - Required for all forecast years. When added to RPI this is the rate at the assumed weekly rentals will be increased. Include "real growth" effects eg due to changing mix, as well as pure rent inflation. Gross rental income (£000s) - this is required for a reconciliation check on total rent for the business stream. Average weekly service income per unit (£s) - this current business stream average Service Income and Charges for Support Services For both of these items of income the following inputs are required: Average weekly income per unit (£s) - the current business stream average RPI differential +/- (%) - the annual rate of growth to be added to RPI. This should include any real growth as well as pure inflation. Service income and Charges for support services (£000s) - (not level 1 icon) required for income reconciliation purposes. Revenue Grants Revenue grants % rental income (%)- the average level of rental support expected for each year of the forecast. Revenue grants (£000s) - the forecast amount of grant expected for each year of the forecast.

Page 15 of 25

Major repairs grants (£000s) - a direct input for each year of the forecast because not straightforward to model. Voids and Bad Debts In relation to both voids and bad debts; Loss % rental, support and service income (%)- the anticipated percentage loss of rent and other income for the business stream, for each year of the forecast. Losses (£000s) - the forecast absolute loss of rent and other income for each year of the forecast. New Housing Stock N.B. As indicated within the model associations need to separate out their committed schemes and their aspirational development. Number of units completed during the year (Units) - for the particular business stream. Used for calculating unit-based revenues, costs, capital expenditure and SHG received. For new and reconciled forecasts, years 28-30 should ideally have no new developments since this will adversely affect the multi-period cash flow measures of capacity. Build cost per unit (£000s) - the current unit build cost for the business stream including any land purchase cost will be indexed at build cost inflation rate. Land purchase cost [total] (£000s) - for any purchases of land not included in unit build costs. For all years of the forecast where relevant. Free land at fair value treated as grant (£000s) - the fair value should be entered which will be added to the value of fixed assets and to reserves. If a cash price is paid which is well below the fair value, include the surplus here and the price paid in the land purchase row. Cash expenditure on new units and land (£000s) - used for a reconciliation check which will pick up any timing differences between payments and unit completions. SHG received % capital expenditure (%)- the expected grant percentage on completions of SHG funded housing development for each year of the forecast. This is not necessarily SHG recieved as a % of capital expenditure if the association is self-funding some development. SHG received (£000s) - the forecast amounts of SHG to be received for each forecast year. Housing Stock Sold (including part units) Number of units sold during the year (Units) - the business stream total for each forecast year. NB the sale of 47 stakes of 10% would be entered as 4.7. Ideally there should be no disposals in years 28-30 to avoid distorting the multi-period cover ratios.

Page 16 of 25

Sale price per unit (£000s) - direct inputs for each year of the forecast, because there is no obvious model for the likely changes in sale prices. Cash from sale (£000s) - used for reconciliation purposes Profit on sale per unit (£000s) - needed to calculate profit on sale and also the book value of sales for Balance Sheet adjustments. SHG paid back % revenue from sale (%) - the estimated SHG released expressed as a percentage of the sales revenues. Housing Stock Demolished Number of units demolished by year end (Units) - the business stream forecast for each year. Written-down value per unit (£000s) - the average book value of units demolished, needed for every relevant forecast year because of the changes of simply inflating today's typical value. SHG paid back (£000s) - the estimate for each relevant forecast year of the total amount released and re-paid. Demolition costs (£000s) - the estimate for each relevant forecast year of the total demolition cost. Housing Stock Transferred Number of units transferred in/(out) during year (Units) - required for each year of the forecast. Effective price per unit (£000s) - required for each relevant forecast year. If the fair value recognised on the Balance Sheet is different, include this surplus (or deficit) under New Housing Stock as "Free land at fair value treated as grant." Total Housing Stock Four items are required only for the base year, mainly for Balance Sheet purposes. Closing stock levels (Units) - the key reference number for the forecast, only required for the base year. Properties at cost (£000s) Accumulated depreciation (£000s) Accumulated grants (£000s) Expenditure Annual management costs per new unit (£'s) - the incremental cost per unit

Page 17 of 25

Annual service cost per new unit (£'s) - the incremental cost per unit Annual care/support costs per new unit (£'s) - the incremental cost per unit Annual major repairs and maintenance costs per new unit (£'s) - the incremental cost per unit Annual ongoing costs per new unit (£'s) - the incremental cost per unit All five items are only required for the base year, and will be inflated for future years. Capitalised repairs and maintenance costs (£000s) - required for all forecast years, to be added back to the Income Statement and added to fixed assets. Other EUV(SH) rent multiple - this will be multiplied by total rental income to obtain an estimate of EUV(SH) value for the business stream. The EUV(SH)/rent multiple should be based on the average of any actual recent valuations. Shared Ownership (additional lines) Sales receipts per unit First tranche sales Cash from initial share sale. Total cash for first tranche sales. Number of units sold Should reflect partial staircasing units. i.e. staircasing 25% should equal a half unit in order to halve the rental income. Early adoption of 2008 SORP As a work around pending amendments to model next year: 1. Keep inputs on first tranche sales on the Shared Ownership template as they are within the model 2. Show the total income and costs from the sales transactions on one of the lines within the "Social Housing Non-Property Based Other" line (this will get the results into the I&E) 3. Remove the surplus/deficit from these transactions in the cash flow forecast through the 'non-cash items line'

Page 18 of 25

Home-buy Average home-buy/equity loan (£000s) - Used with the number of loans granted each year to calculate Home-buy loan on the Balance Sheet Average SHG received (£000s) - Used with the number of loans granted each year to calculate Home-buy-Grant on the Balance Sheet Average revenue deficit/surplus per loan granted (£000s) -Used with the number of loans granted each year to calculate Home-buy charges / income on the Income & Expenditure Statement and surplus / deficit on the Cash Flow Statement. Number of loans granted each year (Units) - Used with the inputs above as described

Page 19 of 25

Social Housing Non-property Based The following two business streams have similar and very simple input screens, each allowing for a number of different activities: Management Contracts Other Activities Growth in income (%) - required for all forecast years to include both inflation and any real growth. Income (£000s) - required for the base year only. Annual management and other costs % income (%) - the total costs associated with the revenue stream expressed as a percentage of income - required for each year.

Page 20 of 25

Non-social Housing The following business streams have identical input screens, except for the occasional heading label change: Market Renting Student Accommodation Registered Nursing Homes Rents Receivable Average weekly rental income per unit (£s) - the average for the particular business stream, required for the base year only, to be inflated for subsequent years. RPI differential +/- (%) - the rate of growth for the business streams above or below RPI. Should include both inflation and real growth effects for each forecast year. Gross rental income (£000s) - required for each forecast year for reconciliation purposes Service Income Average weekly service income per unit (£'s) - required for the base year only, to be inflated for later years. RPI differential +/- (%) - required for the base year only, to be inflated for later years. Service income (£000s) - required for the base year only, to be inflated for later years. Voids and Bad Debts In relation to both Voids and Bad Debts; Loss % rental, support and service income (%)- the anticipated percentage loss of rent and other income for the business stream, for each year of the forecast. Losses (£000s) - the forecast absolute loss of rent and other income for each year of the forecast. New Rental Stock/New Units Number of units completed during the year (Units) - for the particular business stream. Used for calculating unit based revenues, costs, capital expenditure and SHG received. Build cost per unit (£000s) - the current unit build cost for the business stream including any land purchase cost will be indexed at build cost inflation rate. Land purchase cost [total] (£000s) - for any purchases of land not included in unit build costs. For all years of the forecast where relevant. Cash expenditure on new units and land (£000s) - used for a reconciliation check which will pick up any timing differences between payments and unit completions.

Page 21 of 25

Rental Stock Sold/Units Sold Number of units sold during the year (Units) - the business stream total for each forecast year. NB the sale of 47 stakes of 10% would be entered as 4.7. Sale price per unit (£000s) - direct inputs for each year of the forecast, because no obvious model for the likely changes in sale prices. Cash from sale (£000s) - used for reconciliation purposes Profit on sale per unit (£000s) - needed to calculate profit on sale and also the book value of sales for Balance Sheet adjustments. Profit on sale (£000s) - used for reconciliation purposes Total Rental Stock/Total Units Closing stock levels (Units) - the key reference number for the forecast, only required for the base year. Properties at cost (£000s) - the key reference number for the forecast, only required for the base year. Accumulated depreciation (£000s) - the key reference number for the forecast, only required for the base year. Expenditure For each of the following: management costs source costs major repairs and maintenance costs ongoing repairs and maintenance costs

the following inputs are required: Existing costs (£000s) - the current total Annual costs per unit (£'s) - the base year incremental running cost of each new unit, to be inflated for future years, and to be multiplied by the change in unit numbers.

Page 22 of 25

Other Non-social Housing Like Social Housing Non-property Based this is a very simple input sheet which allows for up to five different activities. The input required on each is: Growth in income (%) - Income (£000s) - Annual management or other costs % income

Page 23 of 25

Appendix Two - Common Input Errors No. 1 Symptom No (or incorrect) ther SH or Non-SH activities shown on the I&E sheet of Analysis. No forecast costs against a particular line in the output I&E account The output forecast cash flows do not balance. Issue No turnover figure given on Other SH activity or Non-SH activity in lines 35 and 36 of the I&E account No costs shown against the cost line in the base year, when there is subsequent costs against this line The SBIT figure in the CFF does not equal the figure in the I&E account. Sheet Business Plan Accounting Data Resolution Input the turnover figure – which should be the sum of turnover shown on the Other SH and Non-SH input sheets.

2

Property Sheets

A nominal figure has to be input in the base year (e.g. £1k) and this can then be netted off another line. Because of reconciliation factors both lines will then be shown correctly. These two figures need to be identical. The problem normally arises because of surpluses on disposal. Some HAs show the SBIT in the CFF excluding the impact of surpluses. The correct treatment is to show the SBIT figure including surplus and then adjust for the surplus via the non-cash items line. The interest costs and receipts need to be identical between the I&E and CFF. If there are some notional elements in the interest costs line in the I&E account then these are better shown elsewhere. One option is to put them through the „Other charges/income‟ line in the I&E account and the adjust for them via the „Other non-cash items‟ line in the I&E account. This means that the Gross Operating Cash flows and SBT figure are unaffected but does mean the SBIT figure changes slightly. This can be an issue for some HAs. The movement in debt on the Balance sheet for both Short Term Debt and Long Term Debt both need to be reflected in the CFF. If there is

3

Business Plan Accounting Data

4

The output forecast interest costs (or receipts) do not match the inputs.

Interest Paid and Received in the Cash Flow does not equal the figures in the I&E (allowing for capitalised interest)

Business Plan Accounting Data

5

The output forecast cash flows do not balance.

Movements in debt on the balance sheet are not reflected in the CFF.

Business Plan Accounting Data

Page 24 of 25

No.

Symptom

Issue

Sheet

6 7

DIV0! Error messages appear Value! Error message appear.

The model will not work with either 100% fixed debt or 0% fixed debt. When completing the model a user has pressed the space bar whilst in an input cell. Starting figures not input in the base year of the property templates. This could be in any of the categories from rents, to build costs, to incremental unit costs. No units have been entered in the development assumptions although there are costs associated with development in that year. Incomplete inputs on the property sales section

General Assumptions General Assumptions

Resolution movement in both STD and LTD but the CFF only shows movement through one debt line then the output CFF will not balance. The assumption has to be changed to either 99.99% fixed or 0.01% fixed. The cell with the space bar in has to be identified and the space value deleted. Because cells with only a space character in them appear blank this is not easy! The best thing to do is to use the auditing functionality within Excel to find the cell. A starting cost has to be put in! This is a strangely common mistake made by many associations.

8

Incorrect figures included in the cash expenditure on property line in the output CFF.

Property Assumptions Sheets

9

Incorrect costs and SHG on development are shown in the output CFF.

Property Assumptions Sheets

10

Incorrect information on property sales included in the output financial statements

Property Assumptions Sheets

Although there may be years when development expenditure occurs without any units coming on stream the model needs to have at least one unit coming on stream each year to make the assumptions feed into the output figures. User should put in a nominal unit (they could net this off the number of units coming in later if wanted). All the sections in the property disposals section need to be completed.

Page 25 of 25


				
DOCUMENT INFO