Investment Appraisal - Excel

Document Sample
Investment Appraisal - Excel Powered By Docstoc
					                                                                                                                  7/3/2012



                                Capital Investment Appraisal - Guidance

1     GENERAL

         a   the underlying information needs for capital appraisal purposes have been kept to a minimum where
             at all possible.
         b   the capital appraisal uses two sheets
                (i)   a summary page on which the sources of capital funding are shown; the summary also
                      records a few key indicators.

               (ii)   a cashflow sheet which spans 20 years and is used as the basis for calculating the NPV at
                      the test discount rate of 3.5% over the same period. There is space to record 11 years worth
                      of information (years 0 to 10 inclusive) however depending on the circumstances of the
                      project / timescales it may be more appropriate to record say, the first 5 years, with years 6
                      to 10 inclusive using the same numbers as year 5.      Whilst columns for Years 11 to 20 are
                      shown, all operating costs & income use the same numbers as in the column for year 10. It
                      would only be necessary to use columns 11 to 20 for lifetime capital costs (see point o
                      below)
         c   In keeping with principles of investment appraisals, the revenue costs and income should be
             recorded as marginal costs. ie those costs and revenues incurred as a result of the project.

         d   If there is more than one variant of the project, then the spreadsheet (summary & cashflows) should
             be completed for each variant.

         e   Each appraisal / cashflow should be supported by assumptions used to compile the financials. If
             further working papers are considered necessary to support the data supplied then these should
             also be attached to the project application. Please indicate whether or not figures included / exclude
             VAT
         f   Cells shaded blue on the cashflow page contain formulae and do not need to be completed. On the
             summary & cashflow pages certain cells contain a help (comment) facility to assist completion of the
             forms.
2     COMPLETION OF SUMMARY SHEET

         g   Ref points 1 and 2 - record the name of the provider and project title. Completing points 1 & 2
             automatically feed through to the title lines on the Cashflow page.

         h   Ref point 3 - record the academic year end in which the project commences ( eg. Project
             commences in May 10- falls into the academic year ending 2010). This date automatically feeds
             through to year 0 column of the cashflow - cell D4 - and automatically creates subsequent year end
             dates for the remainder of the cashflow on row 4

         i   Ref point 4 - record the duration of the project - round up to the nearest number of complete years.
             (eg. 18 months = 2 years)

         j   Ref point 5 - record the total area in m2 to be devloped as part of the project
         k   Ref point 6 - record the total number of learners who will benefit from the project

         l   Ref point 7 - provide a brief description of the project

        m    Ref point 8 - record the estimated life of the asset as a result of the project. This is used to calculate
             the residual value of the non-land elements of the asset after 10 or 20 years.
         n   Ref point 9 - the total capital cost is driven from the cashflow page.




    79945520-e1f8-4481-866b-5156d15dbd1f.xls
    Notes
                                                                                                             7/3/2012


     o   Ref point 10 - record the various sources of project funding. If funding sources do not equal the
         project cost an error message is displayed in cell G35




79945520-e1f8-4481-866b-5156d15dbd1f.xls
Notes
                                                                                                                 7/3/2012


         p   The remaining ref. points 11 to 13 - NPV, Sensitivity analysis & value for money indicators - are
             automatically generated from the cashflow forecast.


3     COMPLETION OF CASHFLOW SHEET

         q   This page is split into four main section:-
                (i)    capital costs of a project less any disposal proceeds (if any).

               (ii)    lifetime capital costs - costs of future major refurbishments to ensure buildings are brought
                       up to date / remain in a fit for purpose state. For capital appraisal purposes retaining
                       buildings in a fit for purpose state would not likely be achieved through routine maintenance

               (iii)   operating costs and savings derived from the projects
               (iv)    income derived from learners and any other sources


         r   Capital costs: In each line of the initial capital cost section record the type of cost - the majority of
             this information would be copied from the application form - from page 4 of Annex E entitled 'Building
             Elemental Cost Breakdown for Project'. This document is an excel worksheet.
                       Land acquisition/opportunity cost:-    Cost of land required for the project
                       Construction/refurbishment :-          Building sub-total (points 1 to 5)
                       External Works:-                       External works cost - - cell D47 on page 4
                       Preliminaries:-                        Preliminaries - point 6
                       Contingencies                          Contingencies - point 8. A contingency amount of
                                                              approx 5% of total build costs may be included to take
                                                              account of uncertainties.

                       Equipment                              Equipment - point 9
                       Professional Fees                      Professional fees - point 10
                       VAT                                    VAT - point 11. Institutions may or may not be able to
                                                              recover VAT. In this line show the amount of VAT
                                                              which would be incurred on the project.

         s   Land Sales:- record as a negative figure
         t   Lifetime capital costs: record estimates of likely costs of major refurbishment to ensure buildings are
             brought up to date / remain in a fit for purpose state. For capital appraisal purposes retaining
             buildings in a fit for purpose state would not likely be achieved through routine maintenance. The
             timing of major refurbishment programmes varies depending on types of buildings / location / usage
             etc. If major refurbishments are estimated to be every 5 years, record values in columns 5, 10, 15 &
             20; every 7 years then columns 7 & 14. etc.

         u   Premises costs and savings: These refer to additional costs and savings made to the day to day
             running costs of the building. Where savings are shown these should be recorded as a negative
             figure.
         v   Staff Costs: covers teaching and support staff. In line 39 record the additional full time equivalent
             (FTE) members of staff required to support additional numbers of learners. Line 40 is an automated
             cell which calculates the cumulative FTEs per year. In line 41 record the average cost per post
             (basic +on costs). Figures in line 40 & 41 are used to calculate annual costs of teaching staff in line
             42. A similar process is adopted for calculating support staff costs in lines 43 to 46.




    79945520-e1f8-4481-866b-5156d15dbd1f.xls
    Notes
                                                                                                                7/3/2012


    w    Total operating costs at line 49 are the summation of premises costs (line 35), and staffing costs
         (line 47) minus premises savings (line 38)

     x   Income is split between the Youth Participation, Adult Learner and Employer Responsive. Changes
         in learner income must be supported by a detailled breakdown of how learner volumes are forecast
         to change and the impact on income.

     y   Third Party Income - provide annual estimates of income through lettings, casual hire etc

     z   Net cashflow:- is Initial capital cost plus lifetime capital costs plus operating costs less income.

    aa   Net Present Value is obtained by multiplying yearly net cashflows by the appropriate discount factor
         at the Treasury rate of 3.5%.




79945520-e1f8-4481-866b-5156d15dbd1f.xls
Notes
                                                                                                   7/3/2012


              Skills Funding Agency Investment Appraisal Summary

Ref.
 1 Name of Provider:




 2   Name of Project




 3   Initial academic year of Investment                               2010

 4   Construction years

                                                                   2
 5   Premises                            Total Area Improved (m )

 6   Learners (SLN's)                    Benefiting from Project

 7   Brief Description of project




 8   Estimated life of the asset (years)

     APPROVAL REQUIRED FOR:                                             £
 9   Total Capital Cost                                                       0

10 Sources of Funds:                                                              % of total
     Skills Funding Agency                                                    0   #DIV/0!
     College reserves (cash)                                                  0   #DIV/0!
     Long term Loan                                                           0   #DIV/0!
                 PLEASE ENTER ANY OTHER SOURCES of FUNDING                    0   #DIV/0!
                 PLEASE ENTER ANY OTHER SOURCES of FUNDING                    0   #DIV/0!
                 PLEASE ENTER ANY OTHER SOURCES of FUNDING                    0   #DIV/0!
                 PLEASE ENTER ANY OTHER SOURCES of FUNDING                    0   #DIV/0!
                                                                              0


11 NPV at 3.50% Test Discount Rate (TDR) over 20 years                                         0
                                                                        as at      2010

12 SENSITIVITY ANALYSIS                                                             NPV
     Capital costs increase by:                            10%                                 0
     Revenue expenditure increases by:                     10%                                 0
     Revenue income decreases by:                          10%                                 0

13 VALUE FOR MONEY INDICATORS
     Agency Contribution as % of total capital costs                              #DIV/0!
     NPV / Agency Contribution ratio                                              #DIV/0!
     Average annual running cost saving per Agency £ invested                     #DIV/0!
                                                       2
     Average annual running cost saving per area (m ) improved                    #DIV/0!
     Average annual running cost saving per learner (SLN)                         #DIV/0!

 79945520-e1f8-4481-866b-5156d15dbd1f.xls
 Summary
                                                                                                                                                                                                                                                  7/3/2012




Name of Provider:                            0
Name of Project                              0
            Cost                         Year                             0              1             2             3             4             5              6            7             8             9             10          Totals @
        Classification            Academic Year ended                    2010           2011          2012          2013          2014          2015          2016          2017          2018          2019          2020          Yr 10
CAPITAL COST                                                              £               £             £             £             £             £             £             £             £             £             £             £
Initial Capital Cost          Land acquisition/opportunity cost                                                                                                                                                                               0
                              Construction/refurbishment                                                                                                                                                                                      0
                              External Works                                                                                                                                                                                                  0
                              Preliminaries                                                                                                                                                                                                   0
                              Contingencies                                                                                                                                                                                                   0
                              Equipment                                                                                                                                                                                                       0
                              Professional Fees                                                                                                                                                                                               0
                              VAT                                                                                                                                                                                                             0
                              Sub-total                                           0              0             0             0             0             0             0             0             0             0             0              0
LESS: Capital Receipts        Land sales                        -ve                                                                                                                                                                           0
                              TOTAL-Initial Capital cost                          0              0             0             0             0             0             0             0             0             0             0              0
Lifetime Capital Costs        Superstructure                                                                                                                                                                                                  0
                              Finishes                                                                                                                                                                                                        0
                              Fixtures, Fittings, Furnishings                                                                                                                                                                                 0
                              Mechanical                                                                                                                                                                                                      0
                              Electrical                                                                                                                                                                                                      0
                              External Works                                                                                                                                                                                                  0
                              TOTAL-Lifetime Capital costs                        0              0             0             0             0             0             0             0             0             0             0              0

OPERATING COSTS
Premises Costs                Utilities - Gas, Elec, Water & Rates                                                                                                                                                                           0
(additional costs)            Rent & Leases                                                                                                                                                                                                  0
                              Repairs & Maintenance                                                                                                                                                                                          0
                              Other                                                                                                                                                                                                          0
                              Sub-total                                           0              0             0             0             0             0             0             0             0             0             0             0
Premises Savings              Project Operating Savings           -ve                                                                                                                                                                        0
(Project Operating Savings)   One off savings                     -ve                                                                                                                                                                        0
                              Sub-total                                           0              0             0             0             0             0             0             0             0             0             0             0
Staff Costs                   Addl no. of Teaching Staff FTEs                                                                                                                                                                             0.00
(additional costs)            Cumulative inc. in Staff FTEs (No)                0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00
                              Cost per post
                              Total cost of Teaching Staff                        0              0             0             0             0             0             0             0             0             0             0             0
                              Addl no of Support Staff FTEs                                                                                                                                                                               0.00
                              Cumulative inc. in Staff FTEs (No)                0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00
                              Cost per post
                              Total cost of Support Staff                         0              0             0             0             0             0             0             0             0             0             0              0
                              Sub-total - staff costs                             0              0             0             0             0             0             0             0             0             0             0              0

                              TOTAL OPERATING COSTS                               0              0             0             0             0             0             0             0             0             0             0              0


INCOME
Learner Income                Youth Participation                                                                                                                                                                                             0
                              Adult Learner Responsive                                                                                                                                                                                        0
                              Employer Responsive                                                                                                                                                                                             0
                              Total Learner Income                                0              0             0             0             0             0             0             0             0             0             0              0

                              Other - Please Enter                                                                                                                                                                                            0
Other Income
                              Other - Please Enter                                                                                                                                                                                            0
                              Sub-total                                           0              0             0             0             0             0             0             0             0             0             0              0
                              TOTAL INCOME                                        0              0             0             0             0             0             0             0             0             0             0              0

NET CASHFLOW                                                                      0              0             0             0             0             0             0             0             0             0             0              0
                                     Discount factor            3.50%    1.00          0.96618       0.93351       0.90194       0.87144       0.84197       0.81350       0.78599       0.75941       0.73373       0.70892
NET PRESENT VALUE                                                                 0              0             0             0             0             0             0             0             0             0             0              0

                              Net Present Value of Project              Year 10 =                0
                              (including Residual Values)               Year 20 =                0




           79945520-e1f8-4481-866b-5156d15dbd1f.xls                                                                                                                                                                                               Cashflow
                                                                                                                                                                                                                           7/3/2012




CAPITAL COST              Increase in Capital Costs by 10%             0             0             0             0             0             0             0             0             0             0             0   0
                                                          10%          0             0             0             0             0             0             0             0             0             0             0   0
                                                                       0             0             0             0             0             0             0             0             0             0             0   0
LIFETIME CAPITAL COSTS                                                 0             0             0             0             0             0             0             0             0             0             0   0
OPERATING COSTS                                                        0             0             0             0             0             0             0             0             0             0             0   0
INCOME                                                                 0             0             0             0             0             0             0             0             0             0             0   0
                                                                       0             0             0             0             0             0             0             0             0             0             0   0
                                                                1.00       0.96618       0.93351       0.90194       0.87144       0.84197       0.81350       0.78599       0.75941       0.73373       0.70892
                                                                       0             0             0             0             0             0             0             0             0             0             0   0


CAPITAL COST                                                           0             0             0             0             0             0             0             0             0             0             0   0
LIFETIME CAPITAL COSTS                                                 0             0             0             0             0             0             0             0             0             0             0   0
OPERATING COSTS        Increase in Rev Exp by 10%                      0             0             0             0             0             0             0             0             0             0             0   0
                                                          10%          0             0             0             0             0             0             0             0             0             0             0   0
                                                                       0             0             0             0             0             0             0             0             0             0             0   0
INCOME                                                                 0             0             0             0             0             0             0             0             0             0             0   0
                                                                       0             0             0             0             0             0             0             0             0             0             0   0
                                                                1.00       0.96618       0.93351       0.90194       0.87144       0.84197       0.81350       0.78599       0.75941       0.73373       0.70892
                                                                       0             0             0             0             0             0             0             0             0             0             0   0


CAPITAL COST                                                           0             0             0             0             0             0             0             0             0             0             0   0
LIFETIME CAPITAL COSTS                                                 0             0             0             0             0             0             0             0             0             0             0   0
OPERATING COSTS                                                        0             0             0             0             0             0             0             0             0             0             0   0
INCOME                 Decrease in Rev Inc by 10%                      0             0             0             0             0             0             0             0             0             0             0   0
                                                          10%          0             0             0             0             0             0             0             0             0             0             0   0
                                                                       0             0             0             0             0             0             0             0             0             0             0   0
                                                                       0             0             0             0             0             0             0             0             0             0             0   0
                                                                1.00       0.96618       0.93351       0.90194       0.87144       0.84197       0.81350       0.78599       0.75941       0.73373       0.70892
                                                                       0             0             0             0             0             0             0             0             0             0             0   0




         79945520-e1f8-4481-866b-5156d15dbd1f.xls                                                                                                                                                                          Cashflow
                                                                                                                                                                                                                                                          7/3/2012




Name of Provider:                            0
Name of Project                              0
            Cost                         Year                         11           12            13            14            15            16            17            18            19            20          Totals @          Residual Values
        Classification            Academic Year ended                2021         2022          2023          2024          2025          2026          2027          2028          2029          2030          Yr 20         10 years     20 years
CAPITAL COST                                                          £             £             £             £             £             £             £             £             £             £             £
Initial Capital Cost          Land acquisition/opportunity cost             0              0             0             0             0             0             0             0             0             0              0           0               0
                              Construction/refurbishment                    0              0             0             0             0             0             0             0             0             0              0           0               0
                              External Works                                0              0             0             0             0             0             0             0             0             0              0           0               0
                              Preliminaries                                 0              0             0             0             0             0             0             0             0             0              0           0               0
                              Contingencies                                 0              0             0             0             0             0             0             0             0             0              0           0               0
                              Equipment                                     0              0             0             0             0             0             0             0             0             0              0
                              Professional Fees                             0              0             0             0             0             0             0             0             0             0              0           0               0
                              VAT                                           0              0             0             0             0             0             0             0             0             0              0           0               0
                              Sub-total                                     0              0             0             0             0             0             0             0             0             0              0           0               0
LESS: Capital Receipts        Land sales                                                                                                                                                                                  0
                              TOTAL-Initial Capital cost                    0              0             0             0             0             0             0             0             0             0              0
Lifetime Capital Costs        Superstructure                                                                                                                                                                              0
                              Finishes                                                                                                                                                                                    0
                              Fixtures, Fittings, Furnishings                                                                                                                                                             0
                              Mechanical                                                                                                                                                                                  0
                              Electrical                                                                                                                                                                                  0
                              External Works                                                                                                                                                                              0
                              TOTAL-Lifetime Capital costs                  0              0             0             0             0             0             0             0             0             0              0

OPERATING COSTS
Premises Costs                Utilities - Gas, Elec, Water & Rates          0              0             0             0             0             0             0             0             0             0             0
(additional costs)            Rent & Leases                                 0              0             0             0             0             0             0             0             0             0             0
                              Repairs & Maintenance                         0              0             0             0             0             0             0             0             0             0             0
                              Other                                         0              0             0             0             0             0             0             0             0             0             0
                              Sub-total                                     0              0             0             0             0             0             0             0             0             0             0
Premises Savings              Project Operating Savings                     0              0             0             0             0             0             0             0             0             0             0
(Project Operating Savings)   One off savings                               0              0             0             0             0             0             0             0             0             0             0
                              Sub-total                                     0              0             0             0             0             0             0             0             0             0             0
Staff Costs                   Addl no. of Teaching Staff FTEs                                                                                                                                                         0.00
(additional costs)            Cumulative inc. in Staff FTEs (No)          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00
                              Cost per post                                  0             0             0             0             0             0             0             0             0             0
                              Total cost of Teaching Staff                   0             0             0             0             0             0             0             0             0             0             0
                              Addl no of Support Staff FTEs               0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00
                              Cumulative inc. in Staff FTEs (No)          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00          0.00
                              Cost per post                                  0             0             0             0             0             0             0             0             0             0
                              Total cost of Support Staff                    0             0             0             0             0             0             0             0             0             0              0
                              Sub-total - staff costs                        0             0             0             0             0             0             0             0             0             0              0

                              TOTAL OPERATING COSTS                         0              0             0             0             0             0             0             0             0             0              0


INCOME
Learner Income                Youth Participation                           0              0             0             0             0             0             0             0             0             0              0
                              Adult Learner Responsive                      0              0             0             0             0             0             0             0             0             0              0
                              Employer Responsive                           0              0             0             0             0             0             0             0             0             0
                              Total Learner Income                          0              0             0             0             0             0             0             0             0             0              0

                              Other - Please Enter                          0              0             0             0             0             0             0             0             0             0              0
Other Income
                              Other - Please Enter                          0              0             0             0             0             0             0             0             0             0              0
                              Sub-total                                     0              0             0             0             0             0             0             0             0             0              0
                              TOTAL INCOME                                  0              0             0             0             0             0             0             0             0             0              0

NET CASHFLOW                                                                0              0             0             0             0             0             0             0             0             0              0
                                     Discount factor              0.68495        0.66178       0.63940       0.61778       0.59689       0.57671       0.55720       0.53836       0.52016       0.50257
NET PRESENT VALUE                                                           0              0             0             0             0             0             0             0             0             0              0           0               0

                              Net Present Value of Project
                              (including Residual Values)




           79945520-e1f8-4481-866b-5156d15dbd1f.xls                                                                                                                                                                                                       Cashflow
                                                                                                                                                                                                                      7/3/2012




                                                                                                                                                                                                            RV
CAPITAL COST              Increase in Capital Costs by 10%         0             0             0             0             0             0             0             0             0             0   0            0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
LIFETIME CAPITAL COSTS                                             0             0             0             0             0             0             0             0             0             0   0
OPERATING COSTS                                                    0             0             0             0             0             0             0             0             0             0   0
INCOME                                                             0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                         0.68495       0.66178       0.63940       0.61778       0.59689       0.57671       0.55720       0.53836       0.52016       0.50257           NPV inc RV
                                                                   0             0             0             0             0             0             0             0             0             0   0            0


CAPITAL COST                                                       0             0             0             0             0             0             0             0             0             0   0            0
LIFETIME CAPITAL COSTS                                             0             0             0             0             0             0             0             0             0             0   0
OPERATING COSTS        Increase in Rev Exp by 10%                  0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
INCOME                                                             0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                         0.68495       0.66178       0.63940       0.61778       0.59689       0.57671       0.55720       0.53836       0.52016       0.50257
                                                                   0             0             0             0             0             0             0             0             0             0   0            0


CAPITAL COST                                                       0             0             0             0             0             0             0             0             0             0   0            0
LIFETIME CAPITAL COSTS                                             0             0             0             0             0             0             0             0             0             0   0
OPERATING COSTS                                                    0             0             0             0             0             0             0             0             0             0   0
INCOME                 Decrease in Rev Inc by 10%                  0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                                   0             0             0             0             0             0             0             0             0             0   0
                                                         0.68495       0.66178       0.63940       0.61778       0.59689       0.57671       0.55720       0.53836       0.52016       0.50257
                                                                   0             0             0             0             0             0             0             0             0             0   0            0




         79945520-e1f8-4481-866b-5156d15dbd1f.xls                                                                                                                                                                     Cashflow

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:97
posted:7/4/2012
language:English
pages:9