# Financial Modeling

Topics: Net present value, Mathematical finance, Discounted cash flow Pages: 22 (6132 words) Published: December 27, 2012
FINANCIAL MODELING

The materials in this book are intended for instructional and educational purposes, to illustrate situations similar to those encountered in the real world. The reader will understand that MIT Press and its authors do not guarantee the accuracy or completeness of any information published in this book. Neither MIT Press nor its authors is responsible for the consequences of the implementation of models or information presented in this book.

FINANCIAL MODELING

Simon Benninga

with a section on Visual Basic for Applications by Benjamin Czaczkes

THIRD EDITION

The MIT Press Cambridge, Massachusetts London, England

© 2008 Massachusetts Institute of Technology All rights reserved. No part of this book may be reproduced in any form by any electronic or mechanical means (including photocopying, recording, or information storage and retrieval) without permission in writing from the publisher. This book was set in Times Roman by SNP Best-set Typesetter Ltd., Hong Kong, and was printed and bound in the United States of America. Library of Congress Cataloging-in-Publication Data Benninga, Simon. Financial modeling / Simon Benninga.—3rd ed. p. cm. Includes bibliographical references and index. ISBN 978-0-262-02628-4 1. Finance—Mathematical models. I. Title. HG173.B46 2008 332.01′5118—dc22 2007038629 10 9 8 7 6

To our parents: Helen and Noach Benninga; Esther and Alfred Czaczkes

Contents

Preface Preface to the Second Edition Preface to the First Edition I 1 Corporate Finance Models Basic Financial Calculations 1.1 Overview 1.2 Present Value and Net Present Value 1.3 Internal Rate of Return and Loan Tables 1.4 Multiple Internal Rates of Return 1.5 Flat Payment Schedules 1.6 Future Values and Applications 1.7 A Pension Problem—Complicating the Future-Value Problem 1.8 Continuous Compounding 1.9 Discounting Using Dated Cash Flows Exercises Calculating the Cost of Capital 2.1 Overview 2.2 The Gordon Dividend Model 2.3 Adjusting the Gordon Model to Account for All Cash Flows to Equity 2.4 “Supernormal Growth” and the Gordon Model 2.5 Using the Capital Asset Pricing Model to Determine the Cost of Equity rE 2.6 Using the Security Market Line to Calculate Intel’s Cost of Equity 2.7 Three Approaches to Computing the Expected Return on the Market E(rM) 2.8 Calculating the Cost of Debt 2.9 Computing the WACC: Three Cases 2.10 Computing the WACC for Kraft Corporation 2.11 Computing the WACC for Tyson Foods 2.12 Computing the WACC for Cascade Corporation 2.13 When the Models Don’t Work

xxiii xxix xxxi 1 3 3 4 9 15 17 19 21 25 30 31 39 39 40 44 48 52 59 62 66 70 70 73 77 81

2

viii

Contents

2.14 Conclusion Exercises Appendix 1: Why Is β a Good Measurement of Risk? Portfolio β versus Individual Stock B Appendix 2: Getting Data from the Internet 3 Financial Statement Modeling 3.1 Overview 3.2 How Financial Models Work: Theory and an Initial Example 3.3 Free Cash Flow: Measuring the Cash Produced by the Business 3.4 Using the Free Cash Flow to Value the Firm and Its Equity 3.5 Some Notes on the Valuation Procedure 3.6 Sensitivity Analysis 3.7 Debt as a Plug 3.8 Incorporating a Target Debt/Equity Ration into a Pro Forma 3.9 Project Finance: Debt Repayment Schedules 3.10 Calculating the Return on Equity 3.11 Conclusion Exercises Appendix 1: Calculating the Free Cash Flows When There Are Negative Proﬁts Appendix 2: Accelerated Depreciation in Pro Forma Models Building a Financial Model: The Case of PPG Corporation 4.1 Overview 4.2 PPG Financial Statements, 1991–2000 4.3 Analyzing the Financial Statements 4.4 A Model for PPG 4.5 Back to Treasury Stock and the Dividend 4.6 The Whole Model 4.7 Free Cash Flows and Valuation 4.8 What Is PPG’s Dividend Policy?

86 87 92 95 103 103 103 111 113 115 117 118 121 122 125 127 127 130 131 135 135 136 138 142 146 147 148 151

4

ix

Contents

4.9 Modeling PPG’s Dividend Policy 4.10 Computing PPG’s Cost of Equity rE and Its Cost of Debt rD...