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 web-
site 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   Major repairs costs
          o   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         Shared Ownership
    Supported Housing             Key Workers
    Short-life Housing            Foyers
    PFI                           Other
    Residential Care Homes


Rent Receivable

    Average weekly rental        - per   existing unit
    income (£s)                  - per   new unit
                                 - per   sold/demolished
                                 unit
                                 - per   transferred unit
- 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        management costs
         following:             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.   Symptom                  Issue                        Sheet                      Resolution
1     No (or incorrect)        No turnover figure given     Business Plan Accounting   Input the turnover figure – which should be the
      ther SH or Non-SH        on Other SH activity or      Data                       sum of turnover shown on the Other SH and
      activities shown on      Non-SH activity in lines                                Non-SH input sheets.
      the I&E sheet of         35 and 36 of the I&E
      Analysis.                account
2     No forecast costs        No costs shown against       Property Sheets            A nominal figure has to be input in the base
      against a particular     the cost line in the base                               year (e.g. £1k) and this can then be netted off
      line in the output I&E   year, when there is                                     another line. Because of reconciliation factors
      account                  subsequent costs against                                both lines will then be shown correctly.
                               this line
3     The output forecast      The SBIT figure in the CFF   Business Plan Accounting   These two figures need to be identical. The
      cash flows do not        does not equal the figure    Data                       problem normally arises because of surpluses
      balance.                 in the I&E account.                                     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.
4     The output forecast      Interest Paid and            Business Plan Accounting   The interest costs and receipts need to be
      interest costs (or       Received in the Cash Flow    Data                       identical between the I&E and CFF. If there are
      receipts) do not         does not equal the figures                              some notional elements in the interest costs
      match the inputs.        in the I&E (allowing for                                line in the I&E account then these are better
                               capitalised interest)                                   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.
5     The output forecast      Movements in debt on the     Business Plan Accounting   The movement in debt on the Balance sheet for
      cash flows do not        balance sheet are not        Data                       both Short Term Debt and Long Term Debt
      balance.                 reflected in the CFF.                                   both need to be reflected in the CFF. If there is




                                                            Page 24 of 25
No.   Symptom                 Issue                        Sheet                  Resolution
                                                                                  movement in both STD and LTD but the CFF
                                                                                  only shows movement through one debt line
                                                                                  then the output CFF will not balance.
6     DIV0! Error             The model will not work      General Assumptions    The assumption has to be changed to either
      messages appear         with either 100% fixed                              99.99% fixed or 0.01% fixed.
                              debt or 0% fixed debt.
7     Value! Error message    When completing the          General Assumptions    The cell with the space bar in has to be
      appear.                 model a user has pressed                            identified and the space value deleted. Because
                              the space bar whilst in an                          cells with only a space character in them
                              input cell.                                         appear blank this is not easy! The best thing to
                                                                                  do is to use the auditing functionality within
                                                                                  Excel to find the cell.
8     Incorrect figures       Starting figures not input   Property Assumptions   A starting cost has to be put in! This is a
      included in the cash    in the base year of the      Sheets                 strangely common mistake made by many
      expenditure on          property templates. This                            associations.
      property line in the    could be in any of the
      output CFF.             categories from rents, to
                              build costs, to
                              incremental unit costs.
9     Incorrect costs and     No units have been           Property Assumptions   Although there may be years when
      SHG on development      entered in the               Sheets                 development expenditure occurs without any
      are shown in the        development assumptions                             units coming on stream the model needs to
      output CFF.             although there are costs                            have at least one unit coming on stream each
                              associated with                                     year to make the assumptions feed into the
                              development in that year.                           output figures. User should put in a nominal
                                                                                  unit (they could net this off the number of units
                                                                                  coming in later if wanted).
10    Incorrect information   Incomplete inputs on the     Property Assumptions   All the sections in the property disposals
      on property sales       property sales section       Sheets                 section need to be completed.
      included in the
      output financial
      statements




                                                           Page 25 of 25

				
DOCUMENT INFO