jessica_strategic_udf_appendix 3a_en
Contents
1. 56 Tota 10 20 mm 57 Arrangement Fees 1 00 1 00 um 58 Loan Rate sp a 275 26 53 Repayment Term years 2 20 cc ocoSccoe soo2sssg z Be 5 2 s 25ps2222 22 222 0 Repayment profile to be used 1 annuty 2 profie based 1 1 Cashflow SPPSPSPSPPSPSSPPISPS 2 Grant Funding B asrayman S Distribetior ea Grant Funding tp be used 1 0ne off receipt 2 profie based 1 switch B s 25000 5 Grant Funs te 3Dects date p Gd F profile grant funding provide inputs here double click the link gt gt 3 hok JESSICA Equity 8 interest rate LIBOR gt 200 e interest rate on Cash Overdraft Balance 5 00 2a E conc NPV Costs of Capital Calculation Rate Use Y for NPV Yes 1 Yes 0 Ns 7 Weighted Average Cost of Capital Rate 476 res 74 For the WACO catolseon ac implication we hose assumed that he cost of Equity vas the espectes ERAY 500 2 oa o A OU AX r Q a L nz Page 13 ADD A FUND 2 1 To add a new fund click on the Add Fund button presented on the top of Dashboard Fund Temp Funds sheet A new fund sheet with the default values as in the Fund Template will be added Please note that the Fund Temp sheet is the template sheet for the fund level No inputs should be added here n Project 429 JESSICA Dashboard format light yellow border pm Delete Fund A BO E F G H 2 Project A29 JESSICA Model Checks OK 3 Fund_Temp2. 218 Lifecycle Cost 1 HMM neto P A AN 3 EE S Sea o 220 Lifecycle Cost 2 i 3 i ld 221 222 223 FUNDING 224 225 Gearing 226 Project Gearing debt debt equity i96 227 Commercial Loan 96 of Debt Funding 100 0096 228 JESSICA Loan of Debt Funding 229 230 Private Equity 231 Classification 232 Description Private Equity 233 Private Equity Used Yes 12Y es 0zNo 234 Private Equity 96 of Equity Funding 100 0096 96 235 236 Repayment 237 eage Share in Cash for Repayment of Private Equity 238 Private Equity Redemption Date 239 249 JESSICA Equity 241 Classification 242 Description JESSICA Equity 243 JESSICA Equity Used No 1 Yes 0 No 244 JESSICA Equity of Equity Funding x 245 246 Repayment 247 age Share in Cash for Repayment of JESSICA Equity 50 00 248 JESSICA Equity Redemption Date 31 Dec 40 date nan Page 24 6 Case studies Examples In this section there are examples on how to use the Financial Model step by step with two simple case studies a Multicentre Urban project i Summary The project is the construction of a multicentre in France and involves a multi functional facility for culture entertainment and commercial purposes The Centre will be approximately 15 000 m The construction will start in 2013 and f Project label will last two years Part of which Portto e label Part of which Portfolio number In Fra
3. Error Check Red Shade Alert Orange Shade Page 31 Navigation Buttons Various buttons present in the Financial Model for client use Navigation Button Description This button presented at the top of each sheet helps to navigate to the Summary Summary of a particular project fund holding Cashflow This button presented at the top of Cashflow each sheet helps to navigate to the Cashflow of a particular project fund holding Returns This button presented at the top of each sheet helps to navigate to the calculation of Returns of a particular project fund holding This button presented at the top of each sheet helps to navigate to the calculation of NPV of a particular project fund holding This button presented at the top of each sheet helps to navigate to the Payback Payback calculation of Payback Period of a particular project fund holding This button presented at the top of each sheet helps to navigate to the Dashboard Dashboard Dashboard sheet of the Financial Model Delete Macros are present at the Dashboard sheet only Page 32 Macros Various Macros provided in the Financial Model Macro Description This macro button presented at the top of l Add Project Dashboard Pr_Temp Projects sheet helps Add Project in adding a new project with the default input values as in Project Template i e Pr_Temp sheet Subsequently the project s data will al
4. 5 Funding AS SUI Pee 22 6 Case studies CKD les sii cccsieccerrepeccdestneesveseuadaossiunscusnwbasstsaisvenmceuucsiaunnsscetesatsevdcncudeuuudtass 25 7 Component analysis example ccccceeeesecssssseeeeeeeeeeeeesseeeeeeeeeooessneeeeeeeeeogennseeseeeseeooennes 30 8 Annexes i MM M MM QE 31 Key Colour coding used in the Financial Model e eeeeeeee e ceeeeeee n 31 Navigation Buttons Various buttons present in the Financial Model for client use 32 Macros Various Macros provided in the Financial Model 33 Bilsiirocg ipee eet 34 Page 3 1 Disclaimer On 15th March 2013 the Mazars Consortium was appointed to carry out the 29th Assignment issued under the European Investment Bank s Framework Agreement for services to be provided within the context of the JESSICA initiative titled Strategic UDF Investing and Project Structuring The Financial Model and the accompanying user guide has been built under the Assignment as a tool to assess the impact of introducing Financial Instrument on the viability of urban investments at the different investing levels i e at project and project portfolio level It should not be used for any other purpose Users should note that the Financial Model is of a medium level of complexity therefore it is s
5. capital SEP EE mmm q cap Operating Cost 1 70 000 expenditure is 10m Operating Cost 2 i If profile cost provide inputs here Crtl gt gt c Lifecycle Cost The main project operating costs are Lifecycle Cost 1 lated PREIS nneensn Lifecycle Cost 2 MEN QUUM ier ee pA Penn Cot hee If profile cost provide inputs A re Crtl gt gt F and transmission charges carbon Funding m LI aceon DE Project Gearipg debt debt equity 10 00 P i i Commercial Loan of Debt Funding 100 0076 consumables disposal and labour SICA Loan of Debt Funding EE l Private Equity of Equity Funding 100 00 costs Expected costs per year JESSICA Equity of Equity Funding i 70 000 year Debt Inputs Commercial Loan Arrangement Fees 96 1 00700 UN Loan Rate p a 4 90 The different parties involve want to Repayment Term years 59 calculate the different keys indicators Repayment profile to be used 1 annuity 2 profile based meum and cashflows firstly using a gearing of 70 The commercial loan terms are still under negotiation but the following characteristics are expected gt Repayment terms 22 years Page 27 gt Loan rate 4 Arrangement fees 196 For this project the private investors expect a project IRR of 8 li In practice in the Financial Model Go the Pr Temp sheet Press Add Project Enter the name of the project for example Power plant and press OK o N 9 m otart enteri
6. following password Mazars However please note that should the sheets be deleted or renamed manually the macros not will worked properly FINANCIAL MODEL SPEED 14 After adding 10 projects and 10 funds approximately the Financial Model may operate slightly slower and therefore can take up to 1 minute to add a new project or a new fund Page 21 5 Funding Assumptions 1 In the Base Case 100 of the debt is provided as a commercial loan However it can be divided into commercial loan and JESSICA loan as and when required Similarly equity funding is 10096 from private equity which can be divided into private equity and JESSICA equity as and when required ABCD E F Project A29 JESSICA Model 2 3 Proj Template 6 SUMMARY 11 KEYINPUTS 13 Project Name ProjTempiate label 14 Part of which Portfolio 16 Time Inputs 17 Construction Start Year 2013 year 18 Construction Length 3 years 19 Operations Length 2 years 21 Indexation 22 RPI Index p a 24 Revenue 25 Revenue 1 bote year 26 Revenue 2 year 27 If profile revenue provide inputs here double click the link gt gt link 28 Lump sum Revenue a ee 29 Lump sum Revenue Receipt Date 7 31 Dec 40 Y date 30 Project is of Real Estate No 12 Yes 0 No 31 32 33 34 Capex 35 Capex 1 IM TCR 36 Capex 2 t 37 If profile revenue provide inputs here double click the link gt gt Ca link 39 Operating Cos
7. 1 Profitability index PI Private Equity Payback p D 0 indexation NPV Operational Cashfiow excluding Capex 284311 Project payback period 11 years i RB index Soa 8 restent 126 072 t Private Equity Payback Period years 2 NPV intial investment 2 factor JESSICA Equity Payback Period years 3 Management Fees Profitability index 2 niex Commercial Loan Paytack Pered 19 yess Management Fees JESSICA Loan Payback Period 19 years Page 18 USING THE SUMMARY SECTION 9 Summary section presented at the top of Projects Funds Holding sheets consists of Key Inputs Key Outputs Financial Index IRR NPV Payback periods Sources of Funds Gearing 9e and Graphs MACROS WARNING 10 When saving the Financial Model you may have a privacy warning just press Ok to continue TS CO KED CE BESIS Commercial Loan Private Equity years Feat Nomina years Project IRR 6 93 3 60 years Private Equity IRR 10 38 13 14 years JESSICA Equity IRR 10 33 13 15 years a Het Present Value index NPY Operating Cazhflowz 532 630 Gearing essssoocceecoosvsoog Private Equity 11 20 i PF 944 43445 Db b bd Re JESSICA Equity Microsoft Excel I Lu Commercial Loan I JESSICA Loan I Total A Privacy warning This document contains macros ActiveX controls XML expansion pack information
8. 1 then accept the project e fPI 1 then reject the project It is the percentage of rental income for the purchase price The yield is calculated by dividing the gross annual rental income by the purchase price Page 35
9. 7 3 2 UIS S SIIC SIENTE EE TTE EDEN 8 3 9 MAIS OF COIS MET m secsagsaneee passeazanetensa eE 8 3 4 ISTIC WOIVS FIC NE EUNT 8 35 gle P Se 5 cim 9 3 6 AS DO AiG EI IEEE Tm 10 3 7 Project Template Pr_temp cccccssseecccssseecceeseeeceeseeecseeeecceuseeeseegeeessgeeessanseesneaes 10 3 8 Proje ch Summaiaby Pr SDN assctessboc es extacistettaniey emvAs bunte ckenuese stus eod eRenzeste besetes esed sio es 10 3 9 Fund Template Fund Temp essing riro aniar ai a 10 3 10 Fund Summary Fund Sum ccc ccseecceccceseeececcceseeececccsseeeeesccesseeesscceeseeessouseeees 11 3 11 Holding Sheet MM 11 A ridic muirer E M 12 ADO A 01 07 rc E E E 12 ADD A dp E E E E E E 14 UPDATE THE FT NGAI UTS c 16 dgio 8a 1 8 esp ze Mg Ereren iea A EE 17 DEFETINGO A PROIEU I onset RUIN E GUMIMR NIEMAND IUE EMI UNR NIAE 17 NAVIGATING BETWEEN THE THREE LEV EUS 22 iretur entan ki n OPE TU Ps pali Tuv edax puc S apio ux edat dod umi dantis e dou Eur 2a 18 Posh ONE ds 8nidp 18 USING THE SUMMARY SECTION oppidi np RIETI RR NUMINI M MIENNE DRIN EIU Cad a i quM EUR UNE 19 aser com m 19 Bh Pip Meosants 19 NAVIGATE BE PN EEN SHEET repii einen tins questi E E M MdL Fa dub CM OU SER DD UEM CEPS 21 DINPROTECISHEELDS iode nU MEI M PME EM MEME MEER IM MI ERE ee eee er 21
10. LE l 60 Repayment profile to be used 1 annuity 2 protile ba ae The different parties involve want to B 62 Grant Funding un m ceipt 2 protile based d 63 Grant Funding to be used d12one o calculate the different keys indicators and cashflows firstly using a gearing of 80 The commercial loan terms are still under negotiati n but the following characteristics are expected gt Repayment terms 15 years gt Loan rate 3 5 gt Arrangement fees 1 For this project the private investors expect a project IRR of 10 li In practice in the Financial Model Go the Pr_Temp sheet Press Add Project Enter the name of the project for example Multicentre and press OK pe 2 oY c otart entering the inputs as per the case summary above i Under this current case the project IRR is 10 57 and the private equity IRR 14 15 The private equity IRR is above the expected IRR from the private sector therefore it is reasonable to assume that this is an attractive project to the private investor IRR Nominal Private Equity IRR 14155 Project IRR before financing 10 5796 Payback period Project Payback Period 15yeas Private Equity Payback Period 19 years Commercial Loan Payback Period 14 years Page 26 we oe Be BR ae EXE b Waste Incineration plant i Summary The project will consist of the development of Combined Heat and Power plant on an industrial area in Wales The plant will pr
11. efficiently around the Financial Model Ctrl page down will move you one worksheet to the right Ctrl page up will move you to the left To navigate to the source of a figure or value in the Financial Model press Ctrl This is particularly helpful if you want to navigate from a value in the working sheet back to the source input or from an output in the summary sheet to where it is calculated Once you ve used Ctrl you can return to your original location by pressing the F5 key followed by enter NUM CAPS SCROLL LOCK LOCK LOCK 3 2 Map sheet This sheet displays graphically the flow of the Financial Model It illustrates the three levels project fund and Holding Fund levels building up the Financial Model 3 3 Table of contents This sheet lists all the sheets of the Financial Model and provides links to bring you directly to each of them 3 4 Instructions Sheet 3 4 4 Key This section explains the colour nout Ce coding used in the Financial input Cell with defaut value link Model WIP Coding Counterfiow w thin sheet Counterfiow between sheets Dead Values moort from other sheet Export to other sheet Error Check lert 3 4 2 Instructions Light Yellow Border Light Green Border icy Blue Black Font icy Blue Blue Font Color index 24 Orange Shade This section lists the instructions to follow in order to use the Financial Model correctly 3 4 3 Funding assumption
12. the JESSICA amount used against the financial strategy investment of the fund A number on a scale of 10 10 chosen by the user to evaluate the timing of the JESSICA amount used against the financial strategy investment of the fund At fund level the same logic has been applied to calculate the financial index apart for the two questions which only applied at project level Gearing Gearing called leverage in the US and some other countries measures the extent to which a company is funded by debt One common definition is debt debt equity Internal Rate of Return It is a rate is a rate of return used in capital budgeting to measure and compare the profitability of investments Two types of IRR have been calculated real and nominal The real IRR is the IRR calculated without the inflation Page 34 JESSICA Joint European Support for Sustainable Investment in City Areas Payback period The length of time required to recover the cost of an investment Net Present Value Net present value is defined as the sum of the present values of the individual cash flows of the same entity Profitability Index PI Also known as profit investment ratio PIR and value investment ratio VIR is the ratio of payoff to investment of a proposed project It is a useful tool for ranking projects because it allows you to quantify the amount of value created per unit of investment Rules for selection or rejection of a project e f PI
13. 20 000 fyear NPV intel nvestment 1 2815 factor JESSICA Equity Payback Penod 7 yess 26 3800 year Profitability index 1 2815 nzex Commercial Loan Payback Period 18 years 27 ie provide inputs here double click the link gt gt gt fink JESSICA Loan Payback Period 19 years 28 Sue 2 iti 2s Projctsce mine wih your strategy nvesimest 0 Net Present Value 30 Size index ndex NPV Operational Cashficww 161 483 t JESSICA Loan 32 Timing Source of Funds a MH 3 Project Length in ine wih your strategy investment 0 Revenue E Timing index ndex 35 t 7 36 E Weights JESSICA Equity 2 a If profile revenue provide inputs here Double click the link gt gt ix Finencal index RR 55 00 c ndex Commercial Loan 38 Operating Cost Prostabiity index 20 00 1 ndex JESSICA Loan 40 Operating Cost 1 10 000 iyear Size index 10 00 nex Total 4 Operating Cost 2 iyear Timing index 15 00 n ex If pro amp ile cost provide inputs here doube click the link gt gt 3 link Uses of Fund 44 Lifecycle Cost Financial index 100 00 1 index Costs 150 000 45 Lifecycle Cost 1 5 600 lyear Debt Fees 1 008 46 Lifecycle Cost 2 iyear Notes Surplus Cash g a If profile cost provide inputs here double click the link gt gt E fink Total 151 008 t E Pri 2 rivate Equ so Gearing quity 51 Private Equty 10 00 cs 000 52 ESSICA Equity 10 00 Am 3 Commercial Loan 40 00 n 54 ESSICA Loan 40 00
14. Cashflow Indicates that Cash Balance is nil in last concession period Indicates the total number of checks active in all the projects project s check summary is present from row 2068 to row 2080 in their individual sheets Indicates the tota number of checks active in all the funds fund s check summary is present from row 1127 to row 1133 in their individual sheets Do not delete 117 111g ALERTS 112339 Total Alerts 112 1122 Do not delete 1123 1124 ls Cash in Credit Indicates that Cash is not negative throughout the concession period 1123 Does cash cascade reconcile to cashflow Indicates that Cash Cascade is reconciled to Cashflow EN oues Indicates the tota number of alerts active in all the projects projects alert summary is present from row 2087 to row 2092 in their individual sheets umen Indicates the total number of alerts active in all the funds fund s alert summary is present from row 1140 to row 1145 in their individual sheets 1128 109 Do notdelte DELETING A PROJECT 5 To delete a project click on Delete Project button presented on the top of Dashboard sheet The project s sheet and the project s data in the project summary sheet will be deleted TUNE A29 JESSICA Delete Project Add Fund Delete Fund Pat SEED DELETING A FUND 6 To delete a fund click on the Delete Fund button presented on the top of Dashboard sheet The fund s sheet and the fund s data in the fund sum
15. EB MAZARS Assignment 29 Strategic UDF Investing and Project Structuring Appendix 3a Project Structuring and Strategic Investing User Manual Version o2d 6 September 2013 DISCLAIMER This document has been produced with the financial assistance of the European Union The views expressed herein can in no way be taken to reflect the official opinion of the European Union Sole responsibility for the views interpretations or conclusions contained in this document lies with the authors No representation or warranty express or implied will be made and no liability or responsibility is or will be accepted by the European Investment Bank or the European Commission in relation to the accuracy or completeness of the information contained in this document and any such liability is expressly disclaimed This document is provided for information only Neither the European Investment Bank nor the European Commission gives any undertaking to provide any additional information or correct any inaccuracies in it European Investment we Bank The Eu haat European Commission SSS Table of Contents PEE onu tec E 4 2 Purpose of the Financial MOUGGL iicoiiee ooi vuic sano coo Dea sbo renean ur occu as Un a cusa vau L Us paa su UveE nnmnnn 5 3 Financial Model guidance slieel 2 3 rrnaioot noix Ee enano gna ausa nas YeisobA snas nu Ea upRaulia sues Eua SR UE Das REUS 7 3 1 Opening the Financial Model sess
16. Eghy Distibuions ETT x Gran Fanfng 25000 m Private Equi 345 l 2 Discount rate ESSICA Equity 945 l 5 Use VACC hor NP Yes tes i sNo Commercial oan 50530 l MO Weighed Average Cost of Capital Rate Ux x ESSICALoa 51530 3 Tota e072 l 3 E Gearing 3 Piate Egay 18 3 ESSICAEqUy 15 x 4 CommertialLoan 42500 x a ESSCALon 45 x eae Lu Tota 10 00 x LU u amen amp Cashflow s 5 8202 4 num 8 sU 50 200 z EI JESSICA Equity 2 xm xm T 8 Debet s 1200 5 s PPSPSPPSSOSSPSBDISISSSSSI SSS Il si RRKPIEIPRPGPIAPPRSPSP APPSPA PSSPSPSSPSPPPIIP 53 8 y y y S EH lo M To Oorsig Cons M Liec Com 99 Ung M Caper Tota Feverue o Mo v a a S LT STL a _ za 20 00 0 0 ANENGEEEOERE L adus lt a 4 IF THE CHECKS ARE ACTIVE 4 If the Financial Model checks cell G2 on the top of every sheet are active trace them by pressing Ctrl The further explanations of the checks are presented in front of the individual checks ABCD E F G l K L M N 0 P R 5 il NH CHECKS amp ALERTS ae 110 110 Checks amp Alert Summary Checks OK 1103 11036 CHECKS 1108 1109 Total Checks 1107 Do not delete CF Winding up Check CF Column vs Row Sumtotal check Retained Cash Overdraft Closing Balance Check Total Projects Check Total Fund Checks Indicates that Cashflow is nil in post concession period Indicates that all the Cashflows line items are considered in Net
17. To 54 JESSICA Equity of Equity Funding i 0 4 vo 56 Debt Inputs WR ERE CR ese oF Arrangement Fees To E E 3 5E Loan Rate sp a ME Runde ae aust wt z NA 53 Repayment Term years ed 20 doo 20 2 eo Repayment profile to be used 1 annuity Z profile based LA Oi NE NE 61 G2 Grant Funcin 63 Grant Funding to be used 1 one off receipt Z protfile based i SEDEN 1 INDEM aw itch BA Grant Funding One off Receipt Amount ME Aue eS Grant Funding Receipt Date gt SA Dec 5 date G6 IF profite grant funding provide inputs here double click tre tink gt gt Hrs ES Interest rate LIBOR es Interest rate on Cash Owerdratt Balance mS 5 p a 3 1 NPW i Costs of Capital Calculation Rate 72 Use WACC for HP i M wem 0 i 1 es 0 No 73 VVeighted Average Cost of Capital Rate Po 13 00 i i 74 E e ACET SAA E ux Euge uer E rudem ice ec an E Ca UM deo Mr EAE E en e y an Page 12 1 3 Input the Portfolio Fund name of which the project is a part of in cell F14 ABD E a H J j L 2 Project Structuring Model Checks OK 3 Projecti Prajecti Checks OK Ti SUMMARY 11 KEY INPUTS KEY OU 13 Praject Mame Prajecti label Fin 1a Part of which Portfolio name Fund label 15 Part of which Porttolia number r _ Pri Une 17 Time Inputs Private Equity TRE Expectec 2013 l 18 construction Start ear 0 OTS 770 yeart Private Equity IRR 19 Construction Length 3 years Fin
18. ancial Index IRR E REZREENEENEENAEEEXREXREENA 20 Operations Length 25 years ERE 22 Indexation Profitability index Pl 23 RPI Index 2 50 o p a MPY Operational Cashflow excluding cap 25 Revenue Initial investment 26 Revenue 1 25 000 Near MPY 7 Initial investment 1 4 If the selected calculation mode is manual calculate the Financial Model using Ctrl Alt F9 This will generate the outputs Cash flow Returns NPV and Payback for the projects The Cash flow of the projects would also flow into the selected fund aaco E F u m K L M n 0 F a E s T U i 7 Z AA 3 Projecti Projecti cks OF gt SUMMARY 11 KEYINPUTS KEY OUTPUTS 13 Project Name Projectt labe Financial Index calculations Others Key Outputs is Part of which Portfolic 1 Private Equity IRR Real Nominai Commercial Loan 16 Time Inputs Private Equity RA Expected 13 00 7 80 10 49 17 Construction Start Year 2013 yes Private Equity RA 10 49 7 80 10 49 18 Constuction Length 3 yeas Financial Index IRR a nex 452 7 13 a 15 perasons Lengh 25 years E 2 indexation Profitability index PQ Payback period 22 RPI index 250 a NPV Operasonal Cashfiow excluding Capex 161 483 Project Payback Period 16 years 24 ital nvestmert 126 008 t Private Equty PaySack Penod 7 years 25
19. ate D Age t3 date Construction End Date 3tiMare date Operation End Date 3tiMariet date Indezation PPI index pa Management Fees Management Fees x interest rate interest rate on Cash Overdraft Balance xpa Emi Distrbeti Private Equity of Equity Distributions 0 00 x Discount rate Use VACC tor NP No Yes No Ero pose Discoun rate input here x Preferential Equity Distribution Used Preferential Equity Distribution Used No Yes No 3 a ee m e n n n n n n n 5 5 5 n m n m jn en res ro jm mn s H KEY DUTPUTS Financial Indes calculations i j eR Bes Mominal Commercial Loan Private Equity FR Expected Private Equity FF 42 05 42 08 zr z Private Equity FR ESSICA Equity FR 45x 42 08 Luo c Wm pae Financial Indez IRR index Project FR ni ni F500 F mo A Profitability indez PI Private Equity Payback lum 0 NPY Operational Cashtlow escluding Capez I Project payback period ti years s bisal investment I Private Equity P agback Period amp years scm NP 1 leitialinwestment fastor JESSICA Equity Payback Period 6 gears E eomm Profitability indez index Commercial Loan Payback Period 19 years JESSICA Loan Payback Period 19 years Veigite Financial Indes FR 5000 index Net Present Value JESSICA Loan Profitability indez 50 00 ides NPY Ope
20. e Equity 5b Project payback period i ETT 5T Private Equity Payback Period Privake Equity IRF 56 JESSICA Equity Payback Period JESSICA Equity IRR 53 Commercial Loan Payback Period Project IRRA 60 JESSICA Loan Payback Period B1 B2 53 ba NPY Operating Cashflows 256 342 B5 66 Sources of Fund a PP eee eS fF SF 63 Grant Funding 50 000 Private Equity 3 12 00 E SWOG POR GB ON do o o o op opo 63 Private Equity 15 153 I JESSICA Equity amp 00 5 mum es mm ria hid mm FAS BGatbuten W Papaymi To JESSICA Equity 12 126 I Commercial Loan 45 00 5 Page 20 NAVIGATE BETWEEN SHEETS 12 Inorder to navigate more easily between the sheets a window has been created with the list of the sheets available in the Financial Model Press Ctrl Shift J and the following window will appear Navigate Comments Disclaimer Instructions Map Tac Inputs Dashboard Pr_Temp Mulkicentre Pr Sum Fund Temp Fund 5um Halding Aci abe Cancel If you want to go to the Dashboard sheet for example just click on Dashboard and press Activate Click Cancel to close the window UNPROTECT SHEETS 13 Once open the Financial Model will be automatically protected which means that only the cells in yellow could be modified If you are an advanced user of excel and want to make any changes in terms of Financial Modelling you have the possibility to do so by unprotecting the Financial Model and use the
21. ent in the Key Outputs section of the sheet are a good way to have a quick understanding of the underlying shape of the project The graphs present at the top of the sheet display the net cashflow commercial and JESSICA debt service Private and JESSICA Equity distributions Project Summary Pr Sum This sheet is an intermediate sheet which let to summarise all the projects calculations No inputs are required in this sheet This sheet is dynamic and is updated automatically when adding or deleting projects Fund Template Fund temp The sheet is a general template for the Fund level A new copy of this sheet is created whenever the Add Fund button is clicked at the top of the worksheet You will be able to name your new Fund as you like for example LEEF Foresight Merseyside or Evergreen Page 10 This sheet has buttons at the top of the worksheet to allow the user to navigate through the sheet and to provide the functionality of adding funds The sheet includes general fund characteristics such as name Indexations revenue sources Costs and Management Fees which is entered as a of JESSICA loan Balance The consolidated cashflows of the projects constitutes the cashflows of the fund of which the project is a part of and are further used at a portfolio level for the calculations of the key indicators such as Financial Index IRR NPV Payback periods Sources of Funds and the Gearing which go to the Fund Summary and final
22. ercial Loan Closing Balance Check check Indicates that Commercial Loan Balance Is all In last concession period JESSICA Loan Closing Balance Check check Indicates that JESSICA Loan Balance Is nii in last concession period Retained Cash Overdraft Closing Balance Check check Indicates that Cash Balance is alin fast concession period Do not delete Range End ERTS Total Alerts PED aet Da not delete Range Starts lz Cash in Credit _ alert Indicates that Cash Js not negative throughout the concession period Does cash cascade reconcile ta cashflow alert Indicates that Cash Cascade is reconciled to Cashfiow Da not delete Range Ends Page 28 The alert indicates that the cash balance is negative in some periods The revenues are not sufficient to cover the operating costs and service the debt in some periods The following approaches could be taken to improve the cashflows find new sources of revenue without increasing the costs Optimise the financing introduction of Financial Instruments or grant This list above is not exhaustive and will depend on each project financing structure and cost revenue sources c Fund level With these two case study examples one project Multi Centre has an IRR which meet the private sector expectations and the other project Waste Incineration Plant does not At fund level these two projects will compensate each others to get a Private Equity IRR of 9 13 De
23. ex 15009 index Financial index 100 00 index Notes Others Key Outputs IRR Private Equity IRR JESSICA Equity IRR Project IRR Payback period Project Payback Period Private Equity Payback Per JESSICA Equity Payback Pi Commercial Loan Payback JESSICA Loan Payback Pe let Present Value NPV Operational Cashflo Source of Funds Revenue Grant Funding Private Equity JESSICA Equity Commercial Loan JESSICA Loan Total Uses of Fund Costs Debt Fees Surplus Cash Total Gearing Private Equity JESSICA Equity Commercial Loan JESSICA Loan Total Cashflow Page 22 2 By default the debts are repaid on an annuity basis However this can be changed in cells F61 and G61 by selecting 2 and then by entering a manual profile row 269 and 292 s z ABCD E F G H I J K E M N o 3 Proj Template Proj Template Checks OK 6 7 SUMMARY 11 KEYINPUTS KEY OUTPUTS 13 Project Name j label Financial Index calculations 14 Part of which Portfolio Private Equity IRR 16 Time Inputs Private Equity IRR Expected 13 00 17 Construction Stat Year 1 1 1 1 2013 year Private Equity IRR n a 18 Construction Length HAM ae years Financial Index IRR index 19 Operations Length 25 years 21 Indexation Profitability index PI 22 RPI Index p a NPV Operational Cashf
24. inputs should be added here T tt Project A29 JESSICA Dosen Add Project Delete Project Add Fund Delete Fund 1 2 Update the project inputs into the particular project sheet from row 13 to row 75 in the input cells format light yellow border ABO E F t3 2 Project A29 JESSICA Model Checks OK 3 Proj Template Proj Template Checks OK SUMMARY 11 KEY INPUTS a3 Project Name 14 Part of which Portfolio 16 Time Inputs 17 Construction Start ear 138 Construction Length 19 Operations Length 21 Indexation 22 RPI Index 24 Revenue 25 Revenue 1 26 Revenue 27 IF profiile revenue provide inputs here double click ihe link gt 28 Lump zum Revenue 29 Lump zum Revenue Receipt Date 30 Project ix of Real Estate 31 32 33 34 Capex 35 Capex 1 36 Capex Z 37 IF profite revenus provide inputs here double click ihe link gt gt Operating Cost 40 Operating Cost 1 Wo Um ver al Operating Cost 2 Sect molo E year 42 IF profite cost provide inputs here double click ihe link dink 44 Lifecycle Cost 45 Lifecycle Cost 1 P ud year 46 Lifecycle Cost 2 Eee gags Mna wear ay IF profite cost provide inputs here double click he link dink ao Fumdin SO Project Gearing debt debt equity OU E w 51 Commercial Loan of Debt Funding i 100 00 oo 52 JESSICA Loan of Debt Funding MERE 0 1 ie 53 Private Equity 5 of Equity Funding i IM 100 0095
25. late Fund Tem Shecks OK amp 7 SUMMARY 10 11 KEY INPUTS d P a w 13 Portfolio Name iFune amp Templat label 14 Portfolio Number OS SS i x Macro Generated 15 16 Time Inputs 17 Construction Start Date 18 Construction End Date 19 Operation End Date 20 21 Indexation 22 RPI Index 23 24 Management Fees 25 Management Fees Te 26 27 Interest rate LIBOR 28 Interest rate on Cash Owerdratt Balance 2 30 Equity Distribution 31 Private Equity 6 of Equity Distributions 32 33 Discount rate 34 Use WACC for NPV 35 36 IF r0 provide Discount rete input here i E To z o0 00 0 0 0 0 OO 38 Preferential Equity Distribution Used 39 Preferential Equity Distribution Used i Ho D Yes Mo 40 41 42 43 as 45 46 a7 43 es 64 Project Summary 65 66 Project Project IRR 6s i Pr End IRR e Pr End IRR 7 7 TT 71 72 No of Projects active in this Fund Ju MM ME 2 is 73 74 5 DETAILED INPUTS Page 14 2 3 If the selected calculation mode is manual calculate the Financial Model using Ctrl Alt F9 This will generate the outputs Cash flow Returns NPV and Payback for the funds The Cash flow of the funds would also flow into the holding sheet FECC E F G 2 Project A29 JESSICA Me m 3 Fendi Fund 1 Checks OK SUMMARY 10 ft KEY INPUTS Portfolio Name Fund label Portfolio Number 1 Loro Generated Time Construction Start D
26. low excluding Capex 24 Revenue Initial investment 25 Revenue 1 e lyear NPV Initial investment factor 26 Revenue 2 year Profitability index index 27 If profile revenue provide inputs here double click the link gt gt link 28 Lump sum Revenue Size 29 Lump sum Revenue Receipt Date 31 Dec 40 b date Project size in line with your strategy investment 0 30 Project is of Real Estate No 1 Yes 0zNo Size index index 31 32 Timin 33 Project Length in line with your strategy investment 0 34 Capex Timing index index 35 Capex 1 MEE 36 Capex 2 hee j Weights 37 If profile revenue provide inputs here double click the link gt gt link Financial Index IRR i 55 0096 index 38 Operating Cost Profitability index 20 00 index 40 Operating Cost 1 ws 6 year Size index 10 00 index 41 Operating Cost 2 nt year Timing index F 15 0096 index 42 If profile cost provide inputs here double click the link gt gt link 44 Lifecycle Cost Financial index 100 00 index 45 Lifecycle Cost 1 botes jyear 46 Lifecycle Cost 2 year Notes 47 If profile cost provide inputs here double click the link gt gt link 49 Fundin 50 Project Gearing debt debt equity oa 51 Commercial Loan 96 of Debt Funding 100 0096 96 52 JESSICA Loan 96 of Debt Funding 96 53 Private Equity 96 of Equity Funding 100 0096 96 54 JESSICA Equity of Equity Funding 56 Debt Inputs Com
27. ly to the Dashboard As for the project sheet the graphs present in the Key Output section of the sheet show the net cashflow commercial and JESSICA debt service Private and JESSICA Equity distributions 3 10Fund Summary Fund Sum This sheet is an intermediate sheet which summarises all the funds cashflows This sheet then flows to the Holding Sheet and Dashboard Sheet 3 11 Holding Sheet The sheet is a final calculation sheet This sheet has buttons at the top of the worksheet to allow the user to navigate through the sheet The sheet includes the general requirements such as name Indexations revenue sources Costs and Management Fees which is entered as a of JESSICA loan Balance The consolidated cashflow of all the funds constitutes the cashflow of the Holding Fund This cashflow is then used in to calculate key indicators at holding level financial Index IRR NPV Payback periods Sources of Funds and the Gearing This information can be found at the top of the Holding Fund sheet or at the top of the Dashboard Page 11 4 Instructions This section explains in more details step by step how to use the Financial Model ADD A PROJECT 1 1 To add a new project click on the Add Project button presented on the top of Dashboard Pr Temp Projects sheet A new project sheet with the default values as in the Project Template will be added Please note that Pr Temp is the template sheet for the project level No
28. mary sheet will be deleted Project A29 JESSICA Dashboard Add Project Delete Project NEP Delete Fund Page 17 NAVIGATING BETWEEN THE THREE LEVELS 7 To navigate to different parts of a Project Fund Holding sheet use the different navigation buttons provided at the top of these sheets Summary Cashflow Returns NPV Payback Dashboard USING THE DASHBOARD 8 To navigate to the Dashboard sheet from the Project Fund Holding Sheet use the navigation button provided at the top of these sheets The Dashboard provides the user with an additional functionality of Deleting Projects and Funds ABCD E F G H INE L M 0 OomENN a s I U Y W 7 P oo 2 Project A29 JESSICA Wii Check a E 3 Project Template Project Tem Checks OK S 11 KEYINPUTS KEY OUTPUTS AMT E F H 1 Project A29 JESSICA leid Cheek 3 Fund Tempiate Fund Tengel Checks 1 i ABCD E J K L M N 0 P Q R 3 Holding SUMMARY 11 KEYINPUTS KEY OUTPUTS 3 Portfolo Name Hoking label Financial Index calculations Others Key Outputs E IRR IRR Rea Nominal ee a 15 Timelnputs Private Equity RR Expected 13 00 Private Equity FR 4205 42 05 iem mm c 16 Construction Start Date Dt Apri13 cate 15 4pr 12 Private Equity RR 4205 JESSICA Equity RR 42 05 42 05 ac C 1 Construction End Date 31Mari16 cat Financial Index IRR 10 niex Progc RR 13 10 13 10 zt F 18 Operation End Date 31Man s1 cate p a A mn
29. merciat ESSE 57 Arrangement Fees 96 i 58 Loan Rate 96p a ss Repayment Term years 60 Repayment profile to be used 1 annuity 2 profile based 62 Grant Fundin 63 Grant Funding to be used 1 one off reciept 2 profile based switch 1 64 Grant Funding One off Receipt Amount 9 9 65 Grant Funding Receipt Date date es 66 If profile grant funding provide inputs here double click the link gt gt link end 68 Interest rate LIBOR os 69 Interest rate on Cash Overdraft Balance ot p a 0 4 71 NPV Costs of Capital Calculation Rate 0 3 Page 23 In the Base Case Private equity and JESSICA equity are repaid equally depending on the cash availability As Private Equity and JESSICA equity repayments are one time repayments on the dates entered by the user the date can be changes for example to repay the JESSICA equity before the private equity By default the repayment date is the end of the concession ABCD E E G H J K L M N 0 d 3 Proj Template Proj Template Checks OK 202 293 LIFECYCLE COSTS 204 205 Annual Charges Gross 206 Lifecycle Cost 1 year 207 Lifecycle Cost 2 year 208 208 Indezation 210 Category 1 00 0 Les 2 RPI index 211 Category 2 2 RPI index 212 213 Sensitivity 214 Lifecycle Cost 1 100 00 215 Lifecycle Cost 2 100 0096 96 216 217 Additional Profile Lifecycle Costs 218 Project Period Ending date 31Dec 2 31 DecA 3 31 Dec44 31 Dec iS 31 Dec 16 31 Dec
30. months tolerance label Page 9 3 6 3 7 3 8 3 9 Please note that there are a variety of inputs in the Financial Model and a separate colour is used for each type of the input e Light yellow input cells which have to be populated manually e Light green input cells which have a link or calculated value by default input cells where no value is expected these are non changeable and the technical inputs Dashboard The dashboard sheet shows the key indicators to monitor the holding fund and a particular portfolio This section summarises the Financial Index IRR NPV Payback periods Sources of Funds and the Gearing Under the fund section there is a ES switch on cell E51 to display the 49 Bil outputs of a particular fund 50 51 Portfolio No 52 Portfolio No LL NN undz 53 QU d ee 54 Payback 55 56 Proiect navback period 14 Project Template Pr temp The sheet is a general template for the project level A new copy of this sheet is created whenever the Add Project button is clicked at the top of the worksheet You will be able to name your new Project This sheet has buttons at the top of the worksheet to allow the user to navigate through the sheet and to provide the functionality of adding projects The Key Inputs and Key Outputs are at the top of the sheet in the Summary section The workings and calculations are below this first section The graphs pres
31. nancial Model using Ctrl Alt F9 This will generate the outputs Cash flow Returns NPV payback for the holding which will further flow into the Dashboard FECE E F G H J K L M N 0 P R 5 T U Y X Y Z AA 3 Holding SUMMARY n T KEY INPUTS KEY OUTPUTS t n Portfolio Name Holding late Financial eder calculations Dthers Keg Outputs a RC dum Commercial Loan f Timenpats Private Eqa FF Expected fin Piate Eq FR Qn 4E E Constuction Stat Date api dae RAD Privas Equi e208 x ESSICA Egui AR gx QE LL Construction End Date Mar E Financial Indes IRR 0 inder Project FR nix Bite 9 OperaionEndDate 3IMarit iae 8 Profitability indez P1 Private Equity Payback 2 in enation WP Operational Cashion esciudng Cape 29 Project paact period 1 pus 2 FPlindes xpa Tritia investment 6072 Paivate Equity Payback Period 6 pas p APY initil investment 2 Factor ESSICA Equity Payback Period 6 pas 2 Management Fees Profitability indez 2 idet Commercial Loan Pagback Pesiod 8 pas 4 Management Fees x x ESSICA Loan Payback Pesiod 8 pas 25 Weights merest rate LIBOR Financial deo FF mex w indet Net Present Yalve ZO messe on Cash Dvendrat Balance ipa Profitabilpyindet Sx 2 indet NEY Opergisg Cashis 20 2 28 Equity Distribution Financial inde1 100 0 Lj indes Sources of Fund W Priva Egay cof
32. nce the expected inflation rate Time Inputs RPI is 3 per year Construction Start Year Construction Length 1 1 1 7 omnes years Operations Length 25 years ndexation All spaces created by the Project will 8 009 epa be commercialized and relevant Revenue agreements signed with large retail and Revenue 1 Ea us i I Revenue 2 i year service chains If profile revenue provide inputs here Crtl gt gt link Lump sum Revenue gt Revenue from ticket sales Lump sum Revenue Receipt Da date cinemas artist exhibitions other Project is of Real Es 12 Yes 0 No entertainment facilities expected Rental ue year Venue from Property Sales 12 Yes OX No revenue per year 80 000 year ifya Revenue re P gt Rent collection from the leasing of capex newly developed commercial Capex 1 Capex 2 Beaten etc space large retail food and If profile capex provide i c link services chains expected Operating Co J u revenue per year 600 000 year Operating Cost 1 80 000 E year Operating Cost 2 ee lyear If profile cost provide inputs here Crtl gt d link Lifecycle Cost p Based on the business plans the Lifecycle Cost 1 1 2 000 lyear Lifecycle Cost 2 NENNEN Cyr investors are planning to sell the If profile cost provide inputg fiere Crtl gt gt F link Funding us years c Project Gearing deat debt equi
33. ng the inputs as per the case summary above i Under this current case the project IRR is 2 75 and the private equity IRR 4 28 The private equity IRR is far below the expected IRR from the private sector In such case one should consider including some JESSICA investments into the project to improve the financial viability IRR Nominal Private Equity IRR 2 7596 Project IRR before financing 4 2896 Payback period Project Payback Period 20years Private Equity Payback Period 29 years Commercial Loan Payback Period 17 years iii Cashflow improvement At the top of the sheet you will notice that one alert is activated By pressing Crtl on cell G3 the following elements will be shown with the details CKS amp ALERTS Checks amp Alert Summary Checks OK amp 1 alert TECKS Total Checks check Do not delete Range Starts CF Winding up Check check Indicates that CasAffow Is alin posi concession period CF Column wa Row Sumtotal check check Indicates that athe Cashfiows fine items are considered in the Met Cashflow before tax Total Funding Check check Indicates that tota funding is 10098 Sources amp Uses of Fund Check check Indicates that sources of fund is equal to uses of fund Private Equity Balance Check check Indicates that Private Equity Balance Is ni in last concession period JESSICA Equity Balance Check check Indicates that JESSICA Equity Balance is ni In fast concession period Comm
34. oduce both electricity and heat of approximately 10 0 MW It will significantly improve the environment and provide a comprehensive solution for the landfilling program in the region It is envisaged that the facility will process approximately 153 000 tonnes of municipal waste and 36 500 tonnes of wet sewage sludge The installation is to consist of two technological lines an efficient multi stage flue gas treatment system with electricity and heat produced to be transmitted to the power grid and local heating network The construction will start in 2013 and Project Name PowerPlant _ will last three years Part of which Portfolio name ae aoe hich Portfolio number 1 Time Inputs In the UK the expected inflation rate Construction Start Year 2018 RPI is 3 per year Construction Length OE SOOS th n sot Hip T Indexation The project will have multiple potentia RPI Index 3 00 sources of revenue including Revenue Revenue 1 300 000 Revenue 2 220 000 gt Revenue from sale of generated If profile revenue provide inputs here Fo electricity expected revenue per Lump sum Revenue 300 000 Lump su enue Receipt Date 31 Dec 40 poste roject is of Real Estate No gt Revenue from sale of produced heat expected revenue per year 220 000 year Capex 1 gt 10000000 Capex 2 M If profile capex provide i cq i i O ost The investment requirement
35. or Web components These may include personal information that cannot be removed by the Document Inspector Cashflow m mom TTL Revenue n foboebbescoscbes ss sr Se Pee Pes B O DO DB D D 2 N 2 FF 333 34 3323333333 5 3 hJ Eg SS S SS FIPS IHF IF FI IF IF PS IHS IF IS v c eer r s sexes esxexsxesesexs FS SF FFB DASHBOARD 11 Other information The Dashboard has a switch to change between funds in the funding section which can be used to see the output graphs of a particular fund Page 19 2 Project Structuring eas Add Project Delete Project add Fund Delete Fund HOLDING 005 Commercial Loan Private Equity 12 Project Payback Period h ia d 4 08 13 Commercial Loan Payback Period Project IRR 5 333 i4 JESSICA Loan Payback Period Private Equity IRF 10 385 15 Private Equity Payback Period JESSICA Equity IRF 10 333 16 JESSICA Equity Payback Period 20 NPY Operating Cashflows 552 630 22 Sources of Fuad 24 Grant Funding 115 000 Private Equity 1 204 25 Private Equity 46 407 JESSICA Equity amp 32x 2b JESSICA Equity 35 335 Commercial Loan 4d 10 2i Commercial Loan 182 678 JESSICA Loan 35 185 2 JESSICA Loan 148 205 23 523 224 Tatal 100 005 200 000 ad 120 000 nies Ot 000 160 000 35 pp 1a ndn 36 id aT UW LUD DU 43 FUND 5i Portfolio Mo p Portfolio Ho ES 2 Commercial Loan Privat
36. ow Cash flow is the movement of money into or out of a business project or financial product It is usually measured during a specified limited period of time Measurement of cash flow can be used for calculating other parameters that give information on a company s value and situation Cash flow can be used for example for calculating parameters it discloses cash movements over the period Capex Capital expenditures CAPEX or capex are expenditures creating future benefits A capital expenditure is incurred when a business spends money either to buy fixed assets or to add to the value of an existing fixed asset with a useful life extending beyond the taxable year Dashboard User interface to summarize the data at fund and holding levels Financial index The Financial index is an index to evaluate financially a project or a fund and compare projects funds between them The financial index helps to answer the following questions Does my project fund meet my private investors requirements Does my project fund is profitable Does my project is in line with my overall financial strategy investment At project level the financial index is a weighted average index of 4 components The difference between the Private Equity IRR of a project or a fund The difference being limited between 10 10 The profitability index transformed on a scale of 10 10 A number on a scale of 10 10 chosen by the user to evaluate the size of
37. pending on the expected return of the fund this could be an acceptable IRR The fund level allows you to take a portfolio approach to strategic investing Page 29 7 Component analysis example If for example a component analysis is required the project sheet of the Financial Model can be used as the component level and the fund sheet as the project level If for example there is a project composed of commercial space and residential space and the project managers wants to follow the finances for these two spaces separately they can do so by considering each sub project as a separate project In this case the project level will be used for each sub project and the fund level will be the synthesis of the two projects 3 Real Estate project Real Estate project 5 7 SUMMARY 10 11 KEY INPUTS KEY OUTPUTS T2 eee a Portfolio Name edi Real Estate project label Financial Index calculations E Portfolio Number ORE RR o Macro Generated IRR 15 Private Equity IRF Expected H Bd Project Summary WE MERC me gt Page 30 8 Annexes Key Colour coding used in the Financial Model Cell Type Coding Used Input Cell Light Yellow Border Input Cell with default value link Light Green Border WIP Coding Counter flow within sheet Icy Blue Black Font Counter flow between sheets Icy Blue Blue Font Dead Values Colour Index 24 Import from other sheet MZR Blue Font Export to other sheet MZR Red Font
38. rating Cashflows 525 000 La Financial indez 100 002 Sources of Fund Grant Funding 25000 l Notes Private Equity 3455 l JESSICA Equity 9455 I Commercial Loan 52580 lt ESSICA Loan 580 I Total 151072 I PW Private Equity 15x SESSICA Equity 750 Commercial Loan 4250 x IX ESSICA Loan 4250 Ix Total DOM Ew B Derius Ew ix Cashflow ie aa aw 26 g 2 39 5212223592 22232 ox XX e F FF FFF FFG 500 y peu JESSICA Equity 503 a 2 DD u 2 500 e 8 risos 8 Repayment ANB zc mEHCIuLe 0uxs MD l celt m Carex Page 15 UPDATE THE HOLDING INPUTS 3 1 Update the holding inputs into the holding sheet row 13 to row 43 in the input cells format light yellow border ABCD E F G H I gt Project A29 JESSICA Checks OK 3 Holding 6 SUMMARY 10 11 KEY INPUTS BV 13 Portfolio Name Holding debel 14 OX 15 Time Inputs 16 Construction Start Date Ot Apr t3 o dae 17 Construction End Date date 18 Operation End Date date 19 20 Indexation 21 RPlindex dpa 22 23 Management Fees 24 Management Fees 2 n E 25 26 Interest rate LIBOR 27 Interest rate on Cash Overdraft Balance i 5 00 p a 28 28 Equity Distribution 30 Private Equity of Equity Distributions j EE z n s 31 En Discount rate 33 Use WACC for NPV Yes Yes No 3 Weighted Average Cost of Capital Rate hh 3 2 f the selected calculation mode for the Financial Model is manual calculate the Fi
39. ress Enable Content Then please choose the calculation mode to be used automatic or manual Select Automatic Mode or Manual Financial Model e he Automatic mode is recommended for beginner Excel Users Using Automatic Financial Model will avoid pressing further keys to fully calculate the Financial Model e he Manual Mode is recommended for proficient advanced Excel Users To see the results of any changes to inputs you make you will need to press the F9 key All macros include a calculation routine so you should not need to press F9 after running these Disclaimer Once the calculation mode chosen you will be directed to the disclaimer sheet Please press Accept to signify that you understand and accept the disclaimer contained there Please note that the Financial Model utilises a limited amount of VBA Visual Basics for Applications and therefore you may need to adjust your security settings to allow the code to run Additionally several of the Financial Model calculations rely on functions contained within the Analysis Tool pack add in which you will need to make sure is installed the Excel help function can assist with this The Financial Model has been build using the FAST Financial Modelling standard For more guidance on the standard and how you can best use its structure please visit www fast standard org Page 7 QUICK TIP Under a UK keyboard Below are some navigation tips to enable you to move
40. s Page 8 3 4 4 3 4 5 3 4 6 This section lists the funding assumptions taken in the Financial Model by default and how to change them Financial Index This section explains the financial index calculation Navigation Buttons This section explains the purpose of the navigation buttons present at the top of the project fund and the holding sheets These buttons make it easier to navigate through the Financial Model and reach the key output sections Macros This section explains the different macros present in the Financial Model 3 5 Inputs Sheet This sheet includes the basic assumptions used in the Financial Model It includes the following two sections e Financial Model Inputs in this section you can specify the name of the Financial Model the currency used and the Financial Model start date e Non changeable Technical Inputs this section is used to set basic parameters of the Financial Model You should not need to change them It has the basic timeline and the tolerance level values in It Summary Cashflow Returns NPY Payback Dashboard Add Project Delete Project Add Fund Delete Fund Unprotect Sheet Project A29 JESSICA Model Inputs NON CHANGEABLE TECHNICAL INPUTS Model Petiod Time Header Name Project Period Time Header 1 Summary Payback Dashboard Add Project Delete Project Add Fund Delete Fund Unprotect Sheet 1 ov
41. so be added in the Project Summary i e Pr Sum sheet This macro button presented at the top of Dashboard sheet helps in deleting a project The project sheet of the selected project would be deleted Subsequently the project would also be deleted from the Project Summary i e Pr Sum sheet Delete Project Delete Project This macro button presented at the top of Add Fund Dashboard Pr_Temp Funds sheet helps in adding a new fund with the default input values as in Fund Template i e Fund_Temp sheet Subsequently the new fund s data will also be added in the Fund Summary i e Fund_Sum sheet Add Fund This macro button presented at the top of Dashboard sheet helps in deleting a fund The fund sheet of the selected fund would be deleted Subsequently the fund would also be deleted from the Fund Summary i e Fund Sum sheet Delete Fund Delete Fund This macro presented at the top of protected sheets i e from Pr Start gt Fund Sum helps in unprotecting a protected sheet If changes are required to be made in a protected sheet run the Unprotect Sheet macro to unprotect it before any changes can be made The password is Mazars Unprotect Sheet Unprotect Sheet Page 33 Definition list Annuity repayment The repayment of debt is organised in such a way that the principal and interest payments are repaid over the tenor of the debt in order to produce a series of annual payments Cashfl
42. t 40 Operating Cost 1 year 41 Operating Cost 2 year 42 If profile cost provide inputs here double click the link gt gt link 44 Lifecycle Cost 45 Lifecycle Cost 1 year 46 Lifecycle Cost 2 l year 47 If profile cost provide inputs here double click the link gt gt gt link 49 Fundin 50 Project Gearing debt debt equity 51 Commercial Loan of Debt Funding D 52 JESSICA Loan of Debt Funding T 53 Private Equity of Equity Funding 54 JESSICA Equity of Equity Funding 56 Debt Inputs Commercial ESNA 57 Arrangement Fees a ia ES 58 Loan Rate p a oo MF 53 Repayment Term years LX ae ee 60 Repayment profile to be used 1 annuity 2 profile based 1 4 G Checks OK Proj Template Checks OK J K L M N 0 P Q R KEY OUTPUTS Financial Index calculations Private Equity IRR Private Equity IRR Expected 13 00 Private Equity IRR n a Financial Index IRR index Profitability index PI NPV Operational Cashflow excluding Capex Initial investment NPV Initial investment factor Profitability index index Size Project size in line with your strategy investment 0 Size index index Timing 0 Project Length in line with your strategy investment Timing index index Weights Financial Index IRR 55 0096 ndex Profitability index 0 0096 ndex Size index 10 00 index Timing ind
43. the Financial Model should be carried out to take into account the tax impacts Please find below the conceptual map of the Financial Model Page 5 Model Inputs Project Level Fund Level Holding Level NN E g uu Project Start Project 1 Fund 1 Fund Start Project 2 Fund 2 Project Template Project 3 Fund 1 Fund Template Project 1 Project 1 Fund 1 Fund 1 Project 2 rnd 2 Project 3 Fund 2 Fund 3 Funds Summary Holding Fund Project 2 Project 3 gt Projects Summary l Project n Fund n _ Project End Fund End Key Outputs and Graphs Key Outputs and Graphs DELIA Page 6 3 Financial Model Guidance Sheet 3 1 Opening the Financial Model When opening the Financial Model you will be directed to the following tab Project A29 JESSICA Macro Warning 4 Ou N 24 In case of security warnings please press Enable Content to open the Model Please choose the calculation mode to proceed by pressing one of the two buttons gt gt We recommend to choose Set Automatic Mode amp Proceed Your workbook will be calculated automatically when changing inputs gt gt Press Set Manual Mode amp Proceed if you are a proficient Excel user Set Manual Mode amp Proceed For proficient Excel user w Set Automatic Mode amp Proceed w Www ww uw uo ww 0 m w p w Ne TN Enable Content In case of security warnings before continuing please p
44. ty nn 80 00 Commercial Loafi of Debt Fundin 100 00 n ion with ield of 7 5 g NL NE Eanes te QUASI RM JESSICA Loan of Debt Funding m UR Private Equity of Equity Funding 100009 9L JESSICA Equity of Equity Funding ee e De bt Inputs Commercial Loan JESSICA Arrangement Fees Loan Rate p a Repayment Term years Repayment profile to be used 1 annuity 2 profile based arant Funding Page 25 34 Capex 35 Capex 1 7 000 000 The investment requirement capital rahe 37 iE vide nats here Catt I gt ri expenditure or Capex is 7m Operating Cost EM 40 Operating cost 1 80 000 1 There would potentially be associated operaing Coste a St provide Inputs here CH f Hi labour costs as well 80 000 44 Lifecycle Cost E 45 Lifecycle Cost 1 k a aaaaa D 100000 The main project operating costs would be Lifecycle Cost 2 47 If proie cost provide inputs here Crt i d related to the maintenance of the EN Funding 5 Project Gearj debt equity 80 00 i i 51 ercial Loan of Debt Funding 100 00 commercial infrastructure space also pM ee Private Equity of Equity Funding 100 00 called lifecycle costs 100 0 JESSICA Equity of Equity Funding 56 Debt Inputs _ fornennatl aan 57 Arrangement Fees 96 j 1 00 58 Loan Rate 6p a aU 59 Repayment Term years mW
45. uggested that users will need to familiarise themselves with its operations as set out in this guide Please note that the Financial Model does not contain the full complexities of a full project finance Financial Model and users may need to adopt some simplified assumptions when entering project data Page 4 2 Purpose of the Financial Model The Financial Model is a tool to assess the impact of introducing Financial Instrument on the viability of urban investments at the different investing levels e Project Level e Fund Level and e Holding Level This Financial Model has been designed for relevant stakeholders currently involved or interested in using Financial Instrument It is specifically designed for Managing Authorities and prospective fund managers etc who does not have high level modelling tools to evaluate the impacts of Financial Instruments on projects or funds in order to make strategic investment decisions For each level a cashflow is calculated as well as key indicators such as the payback periods Internal Rate of Return IRR Net Present Value and the financial index see the definition list in Annex The Financial Model has the flexibility to add and delete projects and funds using macros All the calculations present in the Financial Model are before tax The impacts of the tax legislation which vary between countries within the European Union are not taken into account in this Financial Model An analysis outside
Download Pdf Manuals
Related Search