See-Through Modelling. Dominic Robertson

      Publishing Details

      HARRIMAN HOUSE LTD

      3A Penns Road

      Petersfield

      Hampshire

      GU32 2EW

      GREAT BRITAIN

      Tel: [phone]

      Email: [email protected]

      Website: www.harriman-house.com

      First published in Great Britain in 2013 by Harriman House.

      Copyright © Harriman House Ltd.

      The right of Dominic Robertson to be identified as Author has been asserted in accordance with the Copyright, Designs and Patents Act 1988.

      ISBN: 9780857193148

      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 the prior written permission of the Publisher. 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 written consent of the Publisher.

      No responsibility for loss occasioned to any person or corporate body acting or refraining to act as a result of reading material in this book can be accepted by the Publisher or by the Author.

      About the author

      Dominic Robertson has been modelling since the mid-nineties and is the founding director of Lazuli Solutions, a firm specialised in building financial models that are accessible to both modellers and finance directors.

      Lazuli Solutions provides see-through modelling solutions to existing and new infrastructure projects in the UK and Europe, the UK regulated energy distribution market, media-financing slate deals, high speed rail concessions and for shipping & transport.

      Prior to this, Dominic was an analyst in the Babcock & Brown infrastructure team working on projects ranging from the development of renewable energy in the UK to defence and utilities PFI projects. Dominic also spent numerous years as a manager in the Arthur Andersen business modelling group in London. During this time, Dominic led the Andersen model audit function across Europe and the Middle East managing the review of 19 large transaction-based models.

      Dominic has a degree in Mathematics and Statistics from the University of Bristol and a post-graduate diploma in business administration from ISDA in Rome. Dominic also teaches a post-graduate business modelling course at IPE, a private university in Naples and leads project finance modelling professional accreditation courses at BPP in London.

      Dominic grew up in Italy and France and divides his time between London and his farm in southern Italy, where he produces certified organic olive oil.

      Contact the author

      I welcome feedback from you about this book or about books you would like to see from me in the future.

      You can get in contact with me by writing to [email protected]. For more information about my work, or for training courses based around the content of this book, please visit www.lazulisolutions.com where you will also find a page dedicated to the book.

      Preface

      What this book covers

      This book aims to provide the reader with a solid theoretical and practical basis for becoming an advanced modeller of see-through financial models in Microsoft Excel. Particular reference will be made to the project finance operating model, and the lessons learned from UK PFI will be drawn upon.

      Topics covered by this book include:

       financial theory

       modelling theory

       Excel theory and techniques

       a step-by-step practical guide to building a project finance operating model

       computer setup and efficient use

       keyboard skills

       macro-economic data collection and distribution.

      Who this book is for

      This book is for finance directors who are looking for a deeper understanding of the dynamics of their enterprise. Above all, this book is for finance directors who want to understand the benefits of adopting a see-through modelling strategy within their enterprise. These benefits are:

       greater strategic vision due to great flexibility of forecast

       lower risk of modelling errors due to standardised modelling

       decreased reliance on individual analysts due to increased ease of model interchange

       clear, detailed and holistic modelling function overview.

      This book is also for aspiring financial modellers in general, and UK PFI project finance modellers in particular. Let me explain.

      UK PFI has conceived over 900 projects in the UK and has gone a long way towards helping with the evolution of the financial model in Excel. Although there are some fundamental differences between a financial model for a public or private corporation and a model for a project finance operating company, there are also very many similarities.

      I initially took knowledge learned from outside of project finance and used it for UK PFI modelling. Subsequently, I have successfully adapted knowledge from my UK PFI models for use in business areas as diverse as regulated energy distribution, media finance funding, telecom spectrum auctions, large container ports, high speed rail, and assorted manufacturing.

      Readers of this book should keep in mind that learning to build a UK PFI project finance model is an extremely good place to start to learn financial modelling. UK PFI is like the world in miniature with simplified operations and simplified finance but containing all the accounting and cash elements that are necessary for wider application.

      How this book is structured

      The book is divided into three main sections preceded by an introduction and followed by an appendix of useful information.

      The introduction outlines:

       the modelling problem

       the possible solutions available.

      Part 1 covers the base theory for the professional modeller:

       modelling theory, from mind-set to structure and on to content

       finance theory, from reporting cycles to shareholder returns and banking cover ratios

       FAST theory for Excel coding, from principles to rules and building blocks

       Excel theory, from setup, through useful formulae, to automation and known issues

       computer theory, for ways to optimise the use of the computer.

      Part 2 is a practical guide to building a model. It includes:

       building a model in 59 steps

       modelling techniques in practice

       ways of maintaining the modelling investment.

      Part