0_273_70806_6_COVER 19/4/06 09:38 Page 1 MASTERING MASTERING inM a practitioner’s guide to MASTERING MA FINANCIAL S applied corporate finance FINANCIAL i FINANCIAL MODELLING c T r MODELLING oE MODELLING MASTERING sR in Microsoft® Excel o I in Microsoft® Excel fN in Microsoft® Excel t ® G A practitioner’s guide to applied A practitioner’s guide to applied E corporate finance A practitioner’s guide to applied corporate finance x F FINANCIAL corporate finance cI Alastair Dayhas worked in the finance industry for Mastering Financial Modelling in Microsoft®Excelis a practical book and CD combination that will eN Mastering Financial Modellingis designed to help more than 25 years in treasury and marketing help finance professionals and business students alike to become more proficient in building l people build more usable Excel applications – faster A functions and was formerly a director of a vendor Microsoft®Excel models and applying corporate finance concepts. and with fewer errors. Divided into two clear parts, it leasing company specializing in the IT and N MODELLING provides a powerful insight into spreadsheet design and This book answers many core questions such as: technology industries. After rapid growth, the C how to apply finance to Excel successfully. directors sold the enterprise to a public company and • How to write more usable Excel models I Part A of the book analyses model design and outlines he established Systematic Finance plc as a • How to add advanced features to Excel models A a design strategy for faster, more accurate application consultancy specializing in: • How to be more confident that the model is giving you the right answer L development, using templates and demonstrations of • Financial modelling – review, design, build and • How to add more in-depth analysis to your models. M in Microsoft® Excel key features and techniques. Part B demonstrates how audit Mastering Financial Modelling in Microsoft®Excelprovides you with an approach not covered in to apply financial theory in Excel. • Training in financial modelling, corporate finance, corporate finance textbooks or Excel manuals. It is a compendium of techniques designed to save O The book summarises the objectives and theory leasing and credit analysis on an in-house and you time and help you become more productive. The accompanying CD-Rom contains all the D and provides worked solutions in a number of public basis software introduced in the book, enabling you to build more powerful and robust spreadsheet E important areas: • Finance and operating lease structuring as a applications. L • Addresses all the key areas of financial modelling, consultant and lessor L • Analysing performance • Cash flow Mastering Financial Modelling in Microsoft®Excelis an essential reference book on financial I from simple balance sheets through to company • Forecasting models • Forecast financials Alastair is the author of a number of other books modelling techniques, allowing you to to apply financial theory effectively in Excel. It will appeal to N • Variance analysis • Break-even analysis published by FT Prentice Hall: Mastering Financial CFOs, finance directors, financial controllers, analysts, accountants, treasury managers, risk G valuation and risk management • Portfolio analysis • Cost of capital Mathematics in Microsoft®Excel andMastering managers, general managers, academics, business and MBA students. • Bonds • Investment analysis Risk Modelling. • A genuinely hands-on approach using Excel • Risk analysis • Depreciation Alastair has a degree in Economics and German from • Leasing • Company valuation London University together with an MBA and is an spreadsheets throughout the book and on the • Optimisation • Decision trees associate lecturer of finance with the Open University • Risk management accompanying CD Business School. The emphasis throughout this book is on simplicity, modularity and ease of use, whilst using Excel features to speed up development and reduce errors. D A Y ––––––––––––––––––––––– Visit our website at FINANCE www.pearson-books.com ––––––––––––––––––––––– Visit our website at ISBN 0-273-70806-6 www.pearson-books.com 9 780273 708063 An imprint of Pearson Education ALASTAIR L. DAY 8063 prelims i-xx 8/1/06 4:23 AM Page i Mastering Financial Modelling 8063 prelims i-xx 8/1/06 4:23 AM Page ii 8063 prelims i-xx 8/1/06 4:23 AM Page iii m a r k e t e d i t i o n s Mastering Financial Modelling A practitioner’s guide to applied corporate finance ALASTAIR L . DAY London · New York · Toronto · Sydney · Tokyo· Singapore Hong Kong · Cape Town · Madrid · Paris · Amsterdam · Milan · Munich 8063 prelims i-xx 8/1/06 4:23 AM Page iv PEARSON EDUCATION LIMITED Edinburgh Gate Harlow CM20 2JE Tel: +44 (0)1279 623623 Fax: +44 (0)1279 431059 Website: www.pearsoned.co.uk First published in Great Britain in 2001 © Pearson Education 2001 The right of Alastair Day to be identified as author of this work has been asserted by him in accordance with the Copyright, Designs and Patents Act 1988. ISBN-10 : 0 273 64310 X ISBN-13 : 978 0 273 64310 4 British Library Cataloguing in Publication Data A CIP catalogue record for this book can be obtained from the British Library. 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, 90 Tottenham Court Road, London W1P 0LP. 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. This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold with the understanding that neither the authors nor the publisher is engaged in rendering legal, investing, or any other professional service. If legal advice or other expert assistance is required, the service of a competent professional person should be sought. The publisher and contributors make no representation, express or implied, with regard to the accuracy of the information contained in this book and cannot accept any responsibility or liability for any errors or omissions that it may contain. 16 15 14 Typeset by Pantek Arts Ltd, Maidstone, Kent Printed and bound in Great Britain by Bell & Bain Ltd, Glasgow The Publishers’ policy is to use paper manufactured from sustainable forests. 8063 prelims i-xx 8/1/06 4:23 AM Page v ABOUT THE AUTHOR Alastair Day has worked in the finance industry for twenty years in treasury and marketing functions. He worked originally for the NFC, negotiating funding and administering leases, and switched to marketing finance upon the privatization of NFC in 1980. During the 1980s he was a director of a leasing company whose rapid growth was based on programmes in the IT, print and machine tool industries. The directors sold the company to a PLC at the end of the decade. In 1990 Alastair established Systematic Finance plc as a consultancy and financial lessor concentra-ting on the computer and communications industries. Alastair has a degree in Economics and German from London University, an MBA from the Open University Business School, and is an associate lecturer in corporate finance with the OUBS. Other publica- tions include books such as The Finance Director’s Guide to Purchasing Leasingpublished by Financial Times Prentice Hall, and a range of soft- ware products. In addition, he develops and presents public and in-house courses on a range of topics including financial modelling, leas- ing, credit and cash flow analysis and other corporate finance topics. v 8063 prelims i-xx 8/1/06 4:23 AM Page vi 8063 prelims i-xx 8/1/06 4:23 AM Page vii C O N T E N T S Introduction – who needs this book ? ix Acknowledgements xi Conventions xiii Executive summary xv Part A DEVELOPING FINANCIAL MODELS 1 Overview 3 2 Design introduction 9 3 Features and techniques 18 4 Sample model 58 5 Example model 78 Part B APPLICATIONS 6 Analysing performance 101 7 Cash flow 116 8 Forecasting models 122 9 Forecasting financials 131 10 Variance analysis 145 vii 8063 prelims i-xx 8/1/06 4:23 AM Page viii Contents 11 Breakeven analysis 153 12 Portfolio analysis 163 13 Cost of capital 172 14 Bonds 186 15 Investment analysis 199 16 Risk analysis 218 17 Depreciation 242 18 Leasing 255 19 Company valuation 279 20Optimization 296 21 Decision trees 312 22 Risk management 323 23 Modelling checklist 351 Appendices 355 Bibliography 363 Index 365 viii 8063 prelims i-xx 8/1/06 4:23 AM Page ix I N T R O D U C T I O N – W H O N E E D S T H I S B O O K ? I was introduced to Microsoft® Excel about 15 years ago when a client asked me to prepare a lease versus purchase analysis to prove that leasing was indeed more beneficial than purchasing. I spent much of the follow- ing weekend developing a Basic program to provide an after-tax net present value for both options. The client produced his own Lotus 1-2-3® model and while we both derived similar answers, the development time for the spreadsheet was only a couple of hours. After this experience, I began to use Lotus 1-2-3 to review funding alternatives, lease pricing and portfolio cash flows. As spreadsheets have become more powerful, I have increased the scope of models and improved my own approach and design, which has had benefits in devel- opment time and accuracy. It is this personal approach which is outlined in this book and disk combination. The success of spreadsheets, especially since the introduction of Office 95, means that most managers have Microsoft Excel as part of their desktop. Yet few receive specific training in modelling procedures just as few business schools teach Excel as a core part of their curriculum. Managers achieve a certain standard, but this means that many spreadsheet models are: • incomprehensible except to the author; • contain serious structural errors; • not able to be audited without a great deal of effort; • not maintainable or flexible enough to be developed further; • failing in their key objectives. The simplicity of Excel means that models can be written ‘on the fly’ with no thought to any of the above problems. Excel is a sophisticated tool and I would argue that it should be a core skill for managers to produce clear maintainable applications and be proficient in spreadsheet design. There is much management writing about ‘empowering individuals’ and providing them with ‘decision tools’ and Excel can assist in providing solutions. This is what I call ‘Applied Financial Excel’, which brings together: • financial skills; • modelling; • design methodology. ix