Mastering Risk Modelling: A Practical Guide to Modelling Uncertainty with Microsoft Excel (2nd Edition) (Financial Times Series)

409 Pages·2009·7.47 MB·english

by  Alastair Day

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:[phone]
Fax:[phone]
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