Rees - Financial Modeling in Practice: a Concise Guide Using Excel and VBA for Intermediate and Advanced Level

1. Chapter 1
2. Chapter 3
3. Chapter 4
4. Chapter 5
5. Chapter 6
6. Chapter 7
7. Chapter 8
8. Chapter 9
9. Chapter 10
10. Chapter 11
11. Chapter 12
12. Chapter 13
13. Chapter 14
14. Chapter 15
15. Chapter 16
16. Chapter 17
17. Chapter 18
18. Chapter 19
19. Chapter 20
20. Chapter 21
21. Chapter 22
22. Chapter 23
23. Chapter 24
24. Chapter 25
25. Chapter 26
26. Chapter 27
27. Chapter 28
28. Chapter 29
29. Chapter 30
30. Chapter 31
31. Chapter 32
32. Chapter 33

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers' professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation and financial instrument analysis, as well as much more.

For a list of available titles, visit our Web site at www.WileyFinance.com.

This edition first published 2018

2018 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 see 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 is Available:

ISBN 978-1-118-90401-5 (hardback) ISBN 978-1-118-90400-8 (ePub)

ISBN 978-1-118-90394-0 (ePDF)

Cover Design: Wiley

Cover Images: AmbientShoot/Shutterstock;

whiteMocca/Shutterstock

To my mother, father and the Godsall and Rees families

This text aims to address key topics in the design and building of financial models, so that such models are appropriate to decision support, are transparent and flexible. It aims to address the issues that are generally applicable in many applications, highlighting several core themes:

• Building models that meet their decision-support role.
• Designing models so that sensitivity analysis can be conducted as relevant, and which meet core requirements for flexibility.
• Minimising the complexity, subject to the model meeting the core requirements.
• Structuring models to have an effective layout and flow, with audit (dependency) paths as short as possible.
• Focusing on the creation of transparency.
• Using Excel functions and functionality (and perhaps VBA/macros) in the most effective and appropriate way (requiring one to have a good knowledge of the possibilities and options available).
• Employing problem-solving skills in an integrated way.

The work is structured into six Parts:

• presents a framework to describe modelling processes, discusses the role of models in decision support and summarises some key themes and best practices.
• discusses model design, focusing on sensitivity and flexibility requirements, and the optimisation of data structures and layout.
• covers the process of building models, focusing on maximising transparency, using the appropriate Excel functions, and creating models which are error-free and easy to audit.
• covers sensitivity and scenario analysis, simulation and optimisation.
• provides practical examples of the use of Excel functions and functionality in financial modelling.
• covers an introduction to VBA and its key areas of application within financial modelling.

Note that are relevant on a stand-alone basis and could be read before the other Parts. This allows the earlier Parts of the text to focus on the general issues relating to model design, build and use, even as, on occasion, they refer to the later Parts.

This text builds on some key principles that were also a core aspect of the author's earlier work Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level (John Wiley & Sons, 2008), especially that of using sensitivity thought processes as a model design tool. However, the discussion here is more extensive and detailed, reflecting the author's enhanced view of these topics that has been gained through the decade since the publication of the earlier work. Indeed, this text is approximately three times the length of that of the corresponding elements of the earlier work (i.e. of in that work). Note that, unlike the earlier work, this text does not aim to treat specific applications in depth (such as financial statements, valuation, options and real options). Further, the topic of risk, uncertainty and simulation modelling is covered only briefly, since the author's Business Risk Modelling in Practice: Using Excel, VBA and @RISK (John Wiley & Sons, 2015) provides a detailed treatment of this topic.

The website associated with this text contains approximately 235 Excel files (screen-clips of most of which are shown in the text). These are an integral part of this work, and it will generally be necessary to refer to these files in order to gain the maximum benefit from reading this text.

Michael Rees has a Doctorate in Mathematical Modelling and Numerical Algorithms and a B.A. with First Class Honours in Mathematics, both from Oxford University. He has an MBA with Distinction from INSEAD in France. In addition, he studied for the Wilmott Certificate of Quantitative Finance, where he graduated in first place for coursework and received the Wilmott Award for the highest final exam mark.