Financial Modelling in Practice E-Book

Michael Rees

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Series
• Sprache: Englisch

Seitenzahl: 437

Veröffentlichungsjahr: 2011

Beschreibung

Financial Modelling in Practice: A Concise Guide for Intermediate and Advanced Level is a practical, comprehensive and in-depth guide to financial modelling designed to cover the modelling issues that are relevant to facilitate the construction of robust and readily understandable models. Based on the authors extensive experience of building models in business and finance, and of training others how to do so this book starts with a review of Excel functions that are generally most relevant for building intermediate and advanced level models (such as Lookup functions, database and statistical functions and so on). It then discusses the principles involved in designing, structuring and building relevant, accurate and readily understandable models (including the use of sensitivity analysis techniques) before covering key application areas, such as the modelling of financial statements, of cash flow valuation, risk analysis, options and real options. Finally, the topic of financial modelling using VBA is treated. Practical examples are used throughout and model examples are included in the attached CD-ROM. Aimed at intermediate and advanced level modellers in Excel who wish to extend and consolidate their knowledge, this book is focused, practical, and application-driven, facilitating knowledge to build or audit a much wider range of financial models. Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Leseprobe

Contents

Cover

Half Title page

Title page

Copyright page

Dedication

Background, Objectives and Approach

About the Author

Acknowledgements

Chapter 1: Building Blocks: Selected Excel Functions and Tools

Core Functions for Financial Modelling

Database Functions, Features and Pivot Tables

Statistical Functions

Lookup and Reference Functions

Text Functions

Information Functions

Array Functions, Formulae and Matrix Calculations

Goalseek and Solver

The Analysis Toolpak and Other Add-Ins

Selected Excel Short-Cuts

Chapter 2: Principles of Modelling

What is A Good Model?

Model Design

Model Structure and Planning

Model Building

Results Presentation and Other Uses of Sensitivity Analysis

Model Auditing

Chapter 3: Financial Statement, Cash Flow and Valuation Modelling

Financial Statement Modelling: Core Points and Example

Error Checks and Feasibility Checks

Adding Generality

Cash Flow Valuation

Chapter 4: Risk Modelling

Benefits and Challenges of Risk Modelling

The Risk Modelling Process

An Introduction to Simulation Techniques

The Selection and Use of Distributions

Further Example Models

Chapter 5: Introduction to Options and Real Options Modelling

Financial Market Derivatives: An Introduction

Real Options Modelling

Chapter 6: VBA for Financial Modelling

Introduction

Building Blocks

Further Topics

Examples: Recording Macros and Related Topics

Examples: Simulation Modelling

Examples: User-defined Functions

Structure and Organisation: Further Topics

Further Reading

Index

Financial Modelling in Practice

For other titles in the Wiley Finance series please see www.wiley.com/finance

Copyright © 2008

John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex PO19 8SQ, England

Telephone [phone]

Email (for orders and customer service enquiries): [email protected] our Home Page on 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, scanning or otherwise, except under the terms of the Copyright, Designs and Patents Act 1988 or under the terms of a licence issued by the Copyright Licensing Agency Ltd, Saffron House, 6-10 Kirby Street, London, ECIN 8TS, UK, without the permission in writing of the Publisher. Requests to the Publisher should be addressed to the Permissions Department, John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex PO19 8SQ, England, or emailed to [email protected], or faxed to ([phone].

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.

This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold on the understanding that the Publisher is not engaged in rendering professional services. If professional advice or other expert assistance is required, the services of a competent professional should be sought.

Other Wiley Editorial Offices

John Wiley & Sons Inc., 111 River Street, Hoboken, NJ 07030, USAJossey-Bass, 989 Market Street, San Francisco, CA 94103-1741, USAWiley-VCH Verlag GmbH, Boschstr. 12, D-69469 Weinheim, GermanyJohn Wiley & Sons Australia Ltd, 42 McDougall Street, Milton, Queensland 4064, AustraliaJohn Wiley & Sons (Asia) Pte Ltd, 2 Clementi Loop #02-01, Jin Xing Distripark, Singapore 129809John Wiley & Sons Canada Ltd, 6045 Freemont Blvd, Mississauga, ONT, L5R 4J3, Canada

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 products, visit www.wiley.com.

Library of Congress Cataloging-in-Publication Data

Rees, Michael, 1964- Financial modelling in practice : a concise guide for intermediate and advanced level / Michael Rees. p. cm. – (Wiley finance series) Includes bibliographical references and index. ISBN 978-0-470-99744-4 (cloth/cd) 1. Finance–Mathematical models. 2. Corporations–Finance–Mathematical models. 3. Options (Finance)–Mathematical models. 4. Microsoft Excel (Computer file) I. Title. HG106.R44 2008 332.01’51–dc22

2008038607

British Library Cataloguing in Publication Data

A catalogue record for this book is available from the British Library

ISBN 978-0-470-99744-4 (HB)

To My Family

Background, Objectives and Approach

The decision to write this book was driven by several beliefs about the current needs of the modelling community:

Many modellers have a reasonable knowledge of core Excel functionality, but desire to increase and consolidate their knowledge in a way that is prioritised, focused, practical and application-driven. In this text we cover those aspects of Excel that are most frequently needed in many intermediate and advanced situations. Such functionality is demonstrated with practical examples, which in most cases have been drawn from real-life situations in which the author has been involved.There is a need for a text that helps modellers to design, structure and build models that are relevant, accurate and readily understandable. Many texts and training courses in the modelling area put their emphasis either on Excel functionality, or on financial theory, or on mathematical models, but seldom address the modelling process. This text aims to cover the modelling issues that are relevant to facilitate the construction of robust and readily understandable models.There is an increasing need to conduct uncertainty analysis as part of the modelling process. Not only are the benefits of such analysis becoming better understood, but also the tools required in practice to do so are becoming more intuitive and easier to implement. The willingness of decision-makers to accept single-point forecasts is likely to continue to reduce. Currently, most modelling texts either do not adequately treat the topic of risk analysis, or treat it from a mathematical perspective that is both inaccessible to many modellers and lacking in practical tools. This text aims to address this need in a way that is accessible and practical to the general reader.

The contents of this book are based on many years’ experience of building models in business and finance, and of training others how to do so. The aim has been to write a guide that is not only as comprehensive as possible within the space available, but is also concise, disciplined and focused in its selection of topics. The book is structured into six chapters:

Chapter 1 reviews a selection of Excel functions that are generally most relevant for building intermediate and advanced level models. It presents many practical examples of the application of these functions.Chapter 2 discusses the principles involved in designing, structuring and building relevant, accurate and readily understandable models. Topics covered include the use of sensitivity analysis, best practice modelling principles and related issues, and model auditing tools.Chapter 3 covers the modelling of financial statements and of cash flow valuation. We discuss a variety of ways to deal with each of the core modelling issues that arise in these applications.Chapter 4 covers risk and uncertainty modelling. Many practical applications and example models are presented in an intuitive and accessible way. We use an add-in to Excel to implement simulation models; such an approach also allows readers to rapidly build their own models.Chapter 5 covers options and real options modelling, treating these as a natural extension of risk modelling. The approach to real options modelling is less theoretical than in some other texts, and does not specifically require knowledge of financial market derivatives. Models are implemented using Excel as well as add-ins for simulation and decision trees, and readers should be able to build their own models after reading this chapter.Chapter 6 covers VBA for financial modelling applications. The topics selected for inclusion have been established by consideration of the core types of financial models that frequently require the use of VBA. The chapter should provide beginners in this area with a focused and practical guide to the topic, and a base on which to discover the richer possibilities available to modellers by using VBA.

There are of course many topics that either cannot be covered in this text, or can be dealt with only in a cursory fashion. For example, it is assumed that the reader is familiar with basic Excel operations and functions, so that the Excel functionality covered is that which is most relevant at the intermediate and advanced level. It is also assumed that the reader is comfortable with the core principles of finance, corporate finance, accounting and financial market products. Although some aspects of these topics are presented as contextual reminders, the focus is on the modelling issues. It is believed that the readers will be able to complement the tools of this text with an adequate knowledge of their own application to build models appropriate to their situation. The Further Reading lists a small selection of works that may be referred to by readers wishing to enhance their knowledge and to explore some topic areas that may not have been covered in detail in this text.

The model examples are included in an attached CD-ROM. This text is designed to be read in conjunction with these models; readers relying purely on the text and the screenshots are unlikely to obtain the maximum benefit. The examples are generally built and presented in Excel 2007. Users of Excel 2003 should nevertheless find this text of equal value: first, menu sequences for Excel 2003 are also provided; second, the text may facilitate Excel 2003 users who wish to convert to Excel 2007. A few of the example files (in Chapter 1 and Chapter 2 only) use functions that are new in Excel 2007; in such cases opening them in Excel 2003 will give a warning message and result in a read-only file, but the essence of such examples should generally still be relevant. Excel functions and menu icons are generally presented in bold, and menu sequences with a (the VBA code and menu items in Chapter 6 are presented in Courier New). For conciseness, the Excel menu group generally is not displayed unless it is required for the purposes of clarity (e.g. Formulas/Trace Dependents is used rather than Formulas/Formula Auditing/Trace Dependents).

The model examples in Chapter 4 use the Excel add-in @RISK from Palisade Corporation, and those in Chapter 5 use both @RISK and PrecisionTree (also from Palisade Corporation). A free time-limited trial version of these products can be downloaded from the Palisade website (see Chapter 4). The models using these software are built in Excel 2003 (but still presented in the screenshots in Excel 2007); this is due to some backward compatibility issues with the Palisade software that were present at the time of writing.

About the Author

Michael Rees gained a B.A. with First Class Honours and a Doctorate in Mathematics from Oxford University in 1985 and 1988 respectively. In 1992 he gained an MBA with Distinction from INSEAD, and in 2003 graduated in first position on the Certificate in Quantitative Finance program, also winning the Wilmott award.

Michael started his career as a strategy consultant with Braxton Associates and Mercer Management Consulting, and later worked as an analyst at J.P. Morgan. Since 2002 he has worked independently. He provides services in topics related to financial modelling, such as training, transaction support and valuation, general financial and business planning, model review, validation and rebuilding, and portfolio optimisation modelling.

Michael lives in Richmond, UK. He was born in Canada, has lived in several countries, and is fluent in French and German.

Michael can be contacted at [email protected]

Acknowledgements

I would like to thank those people who were most influential either directly or indirectly in the existence of this book. My parents created an environment which encouraged and supported learning during my early years. During my academic studies, Graham Hoare, Dr Martin Powell, Prof. William Morton and Dr Paul Wilmott were especially influential. Many former colleagues from Braxton Associates were instrumental in laying the foundations of my business modelling knowledge and in supporting my career development at that time. In particular, Jim Bacos, Faisal Rahmatallah and Michael Schwarz deserve special mention. Palisade Corporation was also helpful in preparing this book, by allowing me the flexibility to draw on the materials developed by me for their training courses.