Get documents about "Investment Appraisal - Excel
Document Sample
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
