Business Risk and Simulation Modelling in Practice
For other titles in the Wiley Finance seriesplease see www.wiley.com/finance
This edition first published 2015
© 2015 John Wiley & Sons Ltd
Registered office
John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, United Kingdom
For details of our global editorial offices, for customer services and for information about how to apply for permission to reuse the copyright material in this book please visit our website at www.wiley.com.
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, except as permitted by the UK Copyright, Designs and Patents Act 1988, without the prior permission of the publisher.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Designations used by companies to distinguish their products are often claimed as trademarks. All brand names and product names used in this book are trade names, service marks, trademarks or registered trademarks of their respective owners. The publisher is not associated with any product or vendor mentioned in this book.
Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. It is sold on the understanding that the publisher is not engaged in rendering professional services and neither the publisher nor the author shall be liable for damages arising herefrom. If professional advice or other expert assistance is required, the services of a competent professional should be sought.
Library of Congress Cataloging-in-Publication Data
Rees, Michael, 1964–
Business risk and simulation modelling in practice: using Excel, VBA and @RISK / Michael Rees.
pages cm
Includes index.
ISBN 978-1-118-90405-3 (cloth)
1. Risk management-Computer simulation. 2. Risk management-Data processing. 3. Microsoft Excel (Computer file) I. Title.
HD61.R44 2015
658.15′50285554-dc23
A catalogue record for this book is available from the British Library.
ISBN 978-1-118-90405-3 (hbk) ISBN 978-1-118-90403-9 (ebk)
ISBN 978-1-118-90404-6 (ebk) ISBN 978-1-118-90402-2 (ebk)
Cover Design: Wiley
Cover Image: ©iStock.com/Mordolff
Preface
This book aims to be a practical guide to help business risk managers, modelling analysts and general management to understand, conduct and use quantitative risk assessment and uncertainty modelling in their own situations. It is intended to provide a solid foundation in the most relevant aspects of quantitative modelling and the associated statistical concepts in a way that is accessible, intuitive, pragmatic and applicable to general business and corporate contexts. It also discusses the interfaces between quantitative risk modelling activities and the organisational context within which such activities take place. In particular, it covers links with general risk assessment processes and issues relating to organisational cultures, incentives and change management. Some knowledge of these issues is generally important in order to ensure the success of quantitative risk assessment approaches in practical organisational contexts.
The text is structured into three parts (containing 13 chapters in total):
• Part I provides an introduction to the topic of risk assessment in general terms.
• Part II covers the design and use of quantitative risk models.
• Part III provides an introduction to key ways to implement the repeated calculation steps that are required when conducting simulation, covering the use of VBA macros and that of the @RISK add-in.
The text has been written to be software independent as far as reasonably practical. Indeed (apart from an assumption that the reader wishes to use Excel to build any models), most of the text in Parts I and II would be identical whichever platform is used to actually perform the simulation process (i.e. whether it is VBA or @RISK). Thus, although some of the example files use Excel functionality only, and others use features of @RISK, essentially all could be readily built in either platform if necessary (there are a handful of exceptions): One would have to make a few simple formula changes in each case, with the tools presented in this text showing the reader how to do so. On the other hand, in the context of presenting data arising from probabilistic processes and simulation results, @RISK's graphical capabilities are generally more flexible (and quicker to implement) than those in Excel. Thus, for purposes of quality, consistency and convenience, many of the illustrations in the book use @RISK in order to show associated graphs, even where the model itself does not require @RISK per se. Thus, a reader is not required to have a copy of @RISK at that point in the text. Indeed, apart from when working with the examples in Chapter 13, there is no fundamental requirement for a reader to own a copy (or a trial version) of @RISK in order to gain value from the text. In fact, readers who wish to use other implementation platforms for the simulation itself may find many aspects of this text of relevance.
The choice to present both Excel/VBA and @RISK approaches serves a number of purposes:
• Whichever platform is used for the simulation, the core concepts, most of the modelling techniques and issues concerning process alignment and other organisational challenges are essentially the same. An integrated approach allows a reinforcement of some of the concepts from different perspectives, and provides a comparison between the possible implementation approaches whilst ensuring minimum repetition.
• Each platform has its own merits, so that in practice, some readers may need one approach whilst other readers would need another. In particular, not only is Excel essentially ubiquitous (and hence the implementation within Excel/VBA involves no additional cost), but also the range of possibilities to use Excel/VBA for risk modelling is larger than is often realised. For example, it is fairly straightforward to create random samples from over 20 probability distributions, and to correlate them. On the other hand, the use of @RISK can facilitate many aspects of the process associated with the building and communication of risk models and their results; in many organisation contexts, its use would be the most effective, flexible and transparent option, with the cost of the required licences generally being insignificant compared to the potential benefits and the investments being made (both in terms of participants' time and in terms of project investment budgets). The visual tools in @RISK also represent very powerful benefits from an organisational process perspective, where there is typically a large variety in the level of understanding of statistics and modelling within groups of participants.
The main content of each part and chapter is as follows:
• Part I introduces the need for risk assessment, its uses, the general process steps, possible approaches to risk quantification and the associated