Mastering Risk Modelling: A Practical Guide to Modelling

409 Pages·2012·7.47 MB·English

mastering mastering A practical guide to modelling mastering
®
risK modelling uncertainty with Microsoft Excel risK modelling
risK modelling
A practical guide to modelling uncertainty A practical guide to modelling uncertainty
with Microsoft® Excel with Microsoft® Excel
second edition
Alastair Day has worked in the fnance mastering Mastering Risk Modelling covers:
industry for more than 25 years. He has held ® l Review of model design
A practical guide to modelling uncertainty with Microsoft Excel
both treasury and marketing positions and l R isk and uncertainty
was formerly a director of a vendor leasing l Credit risk
company specializing in IT and technology Mastering Risk Modelling is a practical guide designed to provide useful risK modelling l Project fnance
assets. Following rapid company growth, the templates for applying risk and uncertainty.
l Financial analysis
enterprise was sold to a public company and
Alastair established Systematic Finance plc The book: l Valuation
as a consultancy specializing in: l Improves fnancial managers’ abilities with Excel • H elps you understand and manage risk through the l Options
l Demonstrates a systematic method of developing Excel models for fast l Bonds
• Financial modelling – design, build, audit confdent use of models
development and reduced errors l Equities
and review
• Training in fnancial modelling, corporate l Provides a library of basic templates for further development all on an l Value at risk
fnance, and leasing on an in-house and enclosed CD for immediate use • A systematic method of developing Excel models for l Simulation
public basis
• Finance and operating lease structuring This fuly revised and updated guide is an essential companion for al those who fast development and error checking
work with risk model design and those who want to build more complex models.
as a consultant and lessor
New material in this edition includes:
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
Mathematics in Microsoft Excel and Mastering models second
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
l Finding optimum mix of risk and return
l Fixed income risk models
l Visual Basic approach
FINANCE
second edition
Visit our website at Visit our website at
www.pearson-books.com www.pearson-books.com
An imprint of Pearson Education AlAstAir l. DAy An imprint of Pearson Education
CVR_DAY9298_02_SE_CVR.indd 1 4/11/08 09:05:32

mastering risK modelling DAY
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
In an increasingly competitive world, we believe it’s quality of
thinking that gives you the edge – an idea that opens new
doors, a technique that solves a problem, or an insight that
simply makes sense of it all. The more you know, the smarter
and faster you can go.
That’s why we work with the best minds in business and finance
to bring cutting-edge thinking and best learning practice to a
global market.
Under a range of leading imprints, including Financial Times
Prentice Hall, we create world-create print publications and
electronic products bringing our readers knowledge, skills and
understanding, which can be applied whether studying or at work.
To find out about Pearson Education publications, or tell us
about the books you’d like to find, you can visit us at
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
Second Edition
ALASTAIR L. DAY
A01_DAY9298_02_SE_A01.QXD:Layout 1 27/10/08 08:46 Page iv
PEARSON EDUCATION LIMITED
Edinburgh Gate
Harlow CM20 2JE
Tel: [phone]
Fax: [phone]
Website: www.pearsoned.co.uk
First published 2003
Second edition published in Great Britain in 2009
© Systematic Finance Plc 2009
ISBN: 978-0-273-71929-8
British Library Cataloguing-in-Publication Data
A catalogue record for this book is available from the British Library.
Library of Congress Cataloging-in-Publication Data
A catalogue record for this book is available from the Library of Congress
All rights reserved; no part of this publication may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, electronic, mechanical,
photocopying, recording, or otherwise without either the prior written permission of the
Publishers or a licence permitting restricted copying in the United Kingdom issued by the
Copyright Licensing Agency Ltd, Saffron House, 6–10 Kirby Street, London EC1N 8TS. This
book may not be lent, resold, hired out or otherwise disposed of by way of trade in any form of
binding or cover other than that in which it is published, without the prior consent of
the Publishers.
10 9 8 7 6 5 4 3 2 1
12 11 10 09 08
Typeset in Garamond 3 by 30
Printed and bound in Great Britain by Ashford Colour Press Ltd, Gosport
The Publisher’s policy is to use paper manufactured from sustainable forests.
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
leasing company specializing in the IT and technology industries. After sale
of the company to a public group, Alastair established Systematic Finance
plc as a consultancy specializing in:
■ financial modelling – design, build, audit and review;
■ training in financial modelling, corporate finance, leasing and credit
analysis for a range of in-house and public clients;
■ finance and operating lease structuring as a consultant and lessor;
■ financial books including those published by the FT such as Mastering
Financial Modelling (second edition), Mastering Risk Modelling, Mastering
Financial Mathematics in Excel and The Financial Director’s Guide to
Purchasing Leasing;
■ eLearning material.
More information at www.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
techniques and methods.
VI
A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page vii
Contents
Conventions xii
Overview xiii
Executive Summary xvi
1 Introduction 1
Scope of the book 3
Example model 5
Objectives of risk modelling 6
Summary 9
2 Review of model design 11
Introduction 13
Design objectives 13
Common errors 15
Excel features 18
Formats 20
Number formats 20
Lines and borders 22
Colour and patterns 24
Specific colour for inputs and results 24
Data validation 25
Controls – combo boxes and buttons 28
Conditional formatting 33
Use of functions and types of functions 33
Add-ins for more functions 36
Text and updated labels 37
Recording a version number, author, etc. 38
Using names 39
Pasting a names table 40
Comment cells 41
Graphics 42
Dynamic graphs to plot individual series 44
Data tables 46
VII
A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page viii
Mastering Risk Modelling
Scenarios 49
Spreadsheet auditing 50
Summary 56
3 Risk and uncertainty 57
Introduction 59
Risk 59
Uncertainty 66
Response to risk 66
Methods 68
Summary 73
4 Project finance 75
Introduction 77
Requirements 77
Advantages 79
Risks 79
Risk analysis 84
Risk mitigation 85
Financial model 86
Inputs 89
Sensitivity and cost of capital 94
Construction, borrowing and output 95
Accounting schedules 97
Management analysis and summaries 102
Summary 110
5 Simulation 111
Introduction 113
Building blocks 114
Procedure 119
Real estate example 124
Summary 130
6 Financial analysis 133
Introduction 135
Process 137
Environment 137
Industry 139
Financial statements 140
Profit and loss 141
Balance sheet 143
Operating efficiency 145
VIII
A01_DAY9298_02_SE_A01.QXD:Layout 1 14/10/08 11:48 Page ix
Contents
Profitability 148
Financial structure 149
Core ratios 150
Market ratios 152
Trend analysis 152
Cash flow 154
Forecasts 158
Financial analysis 167
Summary 172
7 Credit risk 173
Introduction 175
Cash flow 176
Cover ratios 176
Sustainability 180
Beaver’s model 183
Bathory model 185
Z scores 186
Springate analysis 189
Logit analysis 189
H-Factor model 192
Ratings agency 193
Summary 197
References 197
8 Valuation 199
Introduction 201
Inputs 202
Cash flow 205
Capital structure 207
Valuation and returns 210
Sensitivity analysis 212
Management summary 214
Summary 215
9 Bonds 217
Introduction 219
Bond prices 219
Interest rates 222
Yield 224
Duration and maturity 226
Convexity 230
Comparison 233
Summary 236
IX