mastering mastering m mastering A practical guide to modelling risK modelling risK modelling a uncertainty with Microsoft® Excel risK modelling s t A practical guide to modelling uncertainty A practical guide to modelling uncertainty with Microsoft® Excel second edition e mastering with Microsoft® Excel r i Alastair Day has worked in the finance n Mastering Risk Modelling covers: industry for more than 25 years. He has held A practical guide to modelling uncertainty with Microsoft® Excel g risK modelling l Review of model design both treasury and marketing positions and l Risk and uncertainty was formerly a director of a vendor leasing r l Credit risk company specializing in IT and technology Mastering Risk Modelling is a practical guide designed to provide useful i l Project finance templates for applying risk and uncertainty. assets. Following rapid company growth, the s l Financial analysis enterprise was sold to a public company and K The book: l Valuation Alastair established Systematic Finance plc as a consultancy specializing in: l Improves financial managers’ abilities with Excel m • Helps you understand and manage risk through the l Options l Demonstrates a systematic method of developing Excel models for fast confident use of models l Bonds • Financial modelling – design, build, audit development and reduced errors o l Equities and review • Training in financial modelling, corporate l Provides a library of basic templates for further development all on an d l Value at risk finance, and leasing on an in-house and enclosed CD for immediate use e • A systematic method of developing Excel models for l Simulation l public basis This fully revised and updated guide is an essential companion for all those who l fast development and error checking • Finance and operating lease structuring work with risk model design and those who want to build more complex models. i as a consultant and lessor n New material in this edition includes: g Alastair is the author of a number of other l Thoroughly revised models books published by Financial Times Prentice Hall, including: Mastering Financial l More material on credit risk modelling such as portfolios, VaR and bankruptcy models second Mathematics in Microsoft Excel and Mastering Financial Modelling in Microsoft Excel, now in l Dual 2003/2007 Excel key strokes edition its second edition. l The use of statistics in Excel - tools and methods l Advice on capacity to borrow and repay D l Finding optimum mix of risk and return A l Fixed income risk models Y l Visual Basic approach FINANCE second edition Visit our website at Visit our website at www.pearson-books.com www.pearson-books.com AlAstAir l. DAy An imprint of Pearson Education An imprint of Pearson Education CVR_DAY9298_02_SE_CVR.indd 1 4/11/08 09:05:32 A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page i Mastering Risk Modelling A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page ii Inanincreasinglycompetitiveworld,webelieveit’squalityof thinkingthatgivesyoutheedge–anideathatopensnew doors,atechniquethatsolvesaproblem,oraninsightthat simplymakessenseofitall.Themoreyouknow,thesmarter andfasteryoucango. That’swhyweworkwiththebestmindsinbusinessandfinance tobringcutting-edgethinkingandbestlearningpracticetoa globalmarket. Underarangeofleadingimprints,includingFinancialTimes PrenticeHall,wecreateworld-createprintpublicationsand electronicproductsbringingourreadersknowledge,skillsand understanding,whichcanbeappliedwhetherstudyingoratwork. TofindoutaboutPearsonEducationpublications,ortellus aboutthebooksyou’dliketofind,youcanvisitusat www.pearsoned.co.uk A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page iii Mastering Risk Modelling A practical guide to modelling uncertainty with Microsoft® Excel SecondEdition ALASTAIR L. DAY A01_DAY9298_02_SE_A01.QXD:Layout 1 27/10/08 08:46 Page iv PEARSONEDUCATIONLIMITED EdinburghGate HarlowCM202JE Tel:+44(0)1279623623 Fax:+44(0)1279431059 Website:www.pearsoned.co.uk Firstpublished2003 SecondeditionpublishedinGreatBritainin2009 ©SystematicFinancePlc2009 ISBN:978-0-273-71929-8 BritishLibraryCataloguing-in-PublicationData AcataloguerecordforthisbookisavailablefromtheBritishLibrary. LibraryofCongressCataloging-in-PublicationData AcataloguerecordforthisbookisavailablefromtheLibraryofCongress Allrightsreserved;nopartofthispublicationmaybereproduced,storedinaretrieval system,ortransmittedinanyformorbyanymeans,electronic,mechanical, photocopying,recording,orotherwisewithouteitherthepriorwrittenpermissionofthe PublishersoralicencepermittingrestrictedcopyingintheUnitedKingdomissuedbythe CopyrightLicensingAgencyLtd,SaffronHouse,6–10KirbyStreet,LondonEC1N8TS.This bookmaynotbelent,resold,hiredoutorotherwisedisposedofbywayoftradeinanyformof bindingorcoverotherthanthatinwhichitispublished,withoutthepriorconsentof thePublishers. 10987654321 12 11 10 09 08 TypesetinGaramond3by30 PrintedandboundinGreatBritainbyAshfordColourPressLtd,Gosport ThePublisher’spolicyistousepapermanufacturedfromsustainableforests. A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page v About the author Alastair Day has worked in the finance industry for more than 25 years in treasury and marketing functions and was formerly a director of a vendor leasingcompanyspecializingintheITandtechnologyindustries.Aftersale of the company to a public group, Alastair established Systematic Finance plcasaconsultancyspecializingin: financialmodelling–design,build,auditandreview; (cid:2) training in financial modelling, corporate finance, leasing and credit (cid:2) analysisforarangeofin-houseandpublicclients; financeandoperatingleasestructuringasaconsultantandlessor; (cid:2) financial books including those published by the FT such as Mastering (cid:2) Financial Modelling (second edition), Mastering Risk Modelling, Mastering Financial Mathematics in Excel and The Financial Director’s Guide to PurchasingLeasing; eLearningmaterial. (cid:2) Moreinformationatwww.financial-models.com V A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page vi Acknowledgements I would like to thank my family, Angela, Matthew and Frances, for their support and assistance with this book. In addition, Liz Gooster of Pearson Education has provided valuable support and backing for this project. Finally I would like to acknowledge the input of all the clients and atten- dees of my courses who have provided inspiration and discussion of Excel techniquesandmethods. VI A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page vii Contents Conventions xii Overview xiii ExecutiveSummary xvi 1 Introduction 1 Scopeofthebook 3 Examplemodel 5 Objectivesofriskmodelling 6 Summary 9 2 Reviewofmodeldesign 11 Introduction 13 Designobjectives 13 Commonerrors 15 Excelfeatures 18 Formats 20 Numberformats 20 Linesandborders 22 Colourandpatterns 24 Specificcolourforinputsandresults 24 Datavalidation 25 Controls–comboboxesandbuttons 28 Conditionalformatting 33 Useoffunctionsandtypesoffunctions 33 Add-insformorefunctions 36 Textandupdatedlabels 37 Recordingaversionnumber,author,etc. 38 Usingnames 39 Pastinganamestable 40 Commentcells 41 Graphics 42 Dynamicgraphstoplotindividualseries 44 Datatables 46 VII A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page viii MasteringRiskModelling Scenarios 49 Spreadsheetauditing 50 Summary 56 3 Riskanduncertainty 57 Introduction 59 Risk 59 Uncertainty 66 Responsetorisk 66 Methods 68 Summary 73 4 Projectfinance 75 Introduction 77 Requirements 77 Advantages 79 Risks 79 Riskanalysis 84 Riskmitigation 85 Financialmodel 86 Inputs 89 Sensitivityandcostofcapital 94 Construction,borrowingandoutput 95 Accountingschedules 97 Managementanalysisandsummaries 102 Summary 110 5 Simulation 111 Introduction 113 Buildingblocks 114 Procedure 119 Realestateexample 124 Summary 130 6 Financialanalysis 133 Introduction 135 Process 137 Environment 137 Industry 139 Financialstatements 140 Profitandloss 141 Balancesheet 143 Operatingefficiency 145 VIII A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page ix Contents Profitability 148 Financialstructure 149 Coreratios 150 Marketratios 152 Trendanalysis 152 Cashflow 154 Forecasts 158 Financialanalysis 167 Summary 172 7 Creditrisk 173 Introduction 175 Cashflow 176 Coverratios 176 Sustainability 180 Beaver’smodel 183 Bathorymodel 185 Zscores 186 Springateanalysis 189 Logitanalysis 189 H-Factormodel 192 Ratingsagency 193 Summary 197 References 197 8 Valuation 199 Introduction 201 Inputs 202 Cashflow 205 Capitalstructure 207 Valuationandreturns 210 Sensitivityanalysis 212 Managementsummary 214 Summary 215 9 Bonds 217 Introduction 219 Bondprices 219 Interestrates 222 Yield 224 Durationandmaturity 226 Convexity 230 Comparison 233 Summary 236 IX