Edward Bodmer - Corporate and Project Finance Modeling: Theory and Practice

Edward Bodmer

Cover image: iStock.com/Kamil Krawczyk

Cover design: Wiley

Copyright 2015 by Edward Bodmer. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey.

Published simultaneously in Canada.

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 as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600, or on the Web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

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. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

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.

Library of Congress Cataloging-in-Publication Data:

Bodmer, E. (Edward)

Corporate and project finance modeling/Edward Bodmer.

pages cm

ISBN 978-1-118-85436-5 (cloth); ISBN 978-1-118-85446-4 (ePDF); ISBN 978-1-118-85445-7 (ePub); ISBN 978-1-118-95739-4 (oBook)

1. ValuationMathematical models. 2. FinanceMathematical models. 3. Financial riskMathematical models. I. Title.

HG4028.V3B52 2014

658.15011dc23

2014016731

Corporate and Project Finance Modeling: Theory and Practice is intended to be a comprehensive guidebook for anyone who is interested in creating and/or interpreting and/or understanding financial models. Through compiling many years of experience in creating, reviewing, and teaching corporate finance, project finance, acquisition, and real estate modeling, I describe in this book how you can master many difficult modeling problems and how you can build highly structured models from the ground up. Flexible, efficient, and stable model structures are explained along with describing unique solutions that address complex issues. In explaining model design as well as detailed programming techniques, Corporate and Project Finance Modeling can help you to become a much better modeler, whether you are just beginning or are very experienced and want to take your skills to a really high level. By covering how to build, analyze, and present results from a variety of alternative financial models, the book will provide an understanding of why particular modeling features are generally included in one kind of model but not in others. It is hoped that you will be able to find creative ways to borrow subtle concepts from issues addressed in different modeling applications and apply them to your own models.

Corporate and Project Finance Modeling explains how you can build flexible, transparent, and accurate financial analyses, but it does not simply document techniques that are commonly applied in modern models, which have become ever more elaborate and artistic over the past few years. Instead, the book also introduces unique modeling techniques that address many complex issues that are not typically used by even the most experienced modelers. For example, you can learn how to build user-defined functions to solve circular logic and avoid cumbersome copy and paste macros or how to write a function that derives the ratio of enterprise value to earnings before interest, taxes, depreciation, and amortization (EV/EBITDA) that accounts for asset life, historical growth, taxes, return on investment, and cost of capital. Distinctive modeling techniques introduced in Corporate and Project Finance Modeling include accounting for retirements when computing depreciation, automating models to incorporate additional periods of historical data, combining and presenting scenario and sensitivity analysis in a flexible manner, accurately computing net operating loss carryforwards and deferred taxes, adding time series equations and Monte Carlo simulations into financial models without any Microsoft Excel add-ins, normalizing terminal period capital expenditures and deferred taxes, sculpting debt repayments to after-tax cash flow, computing debt service and maintenance reserve accounts, modeling portfolios of assets with different starting and ending periods, and establishing long-term models to prove the treatment of items in the bridge between equity value and enterprise value. Some of the topics address things that you may not even have realized are issues, such as automatically computing the stable level of capital expenditures to depreciation as a function of historical and prospective growth when normalizing cash flow in a corporate model. Many of the unique ways to address risk analysis, circular logic, normalized cash flow, depreciation expense, and modeling multiple assets with different start and end dates are solved by programming user-defined Visual Basic for Applications (VBA) functions.

The goal of Corporate and Project Finance Modeling is not only to show you how to solve a modeling problem but also to explain the finance theory underlying why you should construct your models in a particular way. To address modeling issues ranging from the fundamental structure of different types of financial models to the creation of user-defined VBA functions that resolve circular references, each topic is introduced with theoretical discussion clarifying why the issue is relevant from a financial perspective. Theoretical topics that precede explanation of step-by-step modeling mechanics cover different corporate structures; valuation in the context of project finance, corporate finance, and acquisition finance; risk allocation resulting from cash flow waterfalls; credit analysis in corporate and project finance; debt structuring in the context of risk analysis; determination of items that should be in the bridge between enterprise value and equity value; and many other subjects. The modeling subjects are also described in the context of patterns of valuation mistakes and whether a particular aspect of constructing model assumptions could have avoided some of the common and recurring financial blunders. After the theoretical discussion of each modeling concept, modeling problems are explained on a step-by-step basis with many diagrams and screen shots from actual models.