Investment banking in practice

ffirs.indd 1 6/5/2013 8:37:07 PM

Financial

Modeling and Valuation

ffirs.indd 1 6/5/2013 8:37:07 PM

Founded in 1807, John Wiley & Sons is the oldest independent publishing

company in the United States. With offices in North America, Europe,

Australia, and Asia, Wiley is globally committed to developing and marketing

print and electronic products and services for our customers’ professional

and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance

and investment professionals as well as sophisticated individual investors

and their financial advisors. Book topics range from portfolio management

to e-commerce, risk management, financial engineering, valuation, and

financial instrument analysis, as well as much more.

For a list of available titles, visit our web site at www.WileyFinance.com.

ffirs.indd 2 6/5/2013 8:37:07 PM

Financial

Modeling and Valuation

A Practical Guide to Investment

Banking and Private Equity

Paul Pignataro

ffirs.indd 3 6/5/2013 8:37:07 PM

Cover Design: Wiley

Cover Image: © Getty Images/Chad Baker

Copyright © 2013 by Paul Pignataro. 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 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 booksupport.wiley.com. For

more information about Wiley products, visit www.wiley.com.

Library of Congress Cataloging-in-Publication Data:

Pignataro, Paul.

â•…Investment banking in practice : financial modeling and valuation / Paul Pignataro.

â•…â•…pages cm. — (Wiley finance series)

â•…Includes bibliographical references and index.

â•…ISBN 978-1-118-55876-8 (cloth) — ISBN 978-1-118-55874-4 (ePDF) —

â•…ISBN 978-1-118-55872-0 (Mobi) — ISBN 978-1-118-55869-0 (ePub)

1.╇Investment banking.╅I. Title.

HG4534.P54 2013

332.66 — dc23

2012050850

Printed in the United States of America

10 9 8 7 6 5 4 3 2 1

ffirs.indd 4 6/5/2013 8:37:07 PM

This book is dedicated to every investor in the pursuit of enhancing his or

her wealth. Those that have gained; those that have lost; this continuous

struggle has confounded the minds of many. This book should be one

small tool to help further said endeavor; and if successful, the seed planted

to spawn a future of more informed investors and smarter markets.

ffirs.indd 5 6/5/2013 8:37:07 PM

ffirs.indd 6 6/5/2013 8:37:07 PM

vii

Preface xv

The Walmart Case Study xvi

How This Book Is Structured xvii

Part ONE

Financial Statements and Projections 1

Chapter 1

The Income Statement 3

Revenue 4

Cost of Goods Sold 4

Gross Profit 4

Operating Expenses 5

Other Income 6

EBITDA 6

Depreciation and Amortization 8

EBIT 8

Interest 9

EBT 9

Taxes 9

Net Income 10

Non-Recurring and Extraordinary Items 10

Distributions 11

Net Income (as Reported) 11

Shares 11

Earnings per Share (EPS) 12

Walmart’s Income Statement 12

Revenue 15

Getting to EBITDA 19

Digging up Depreciation 19

Cost of Goods Sold 21

Gross Profit 22

Contents

ftoc.indd 7 6/1/2013 6:34:46 PM

viii Contents

Selling, General, and Administrative Expenses 23

Other Income 23

EBITDA 24

Beyond EBITDA 24

Depreciation and Amortization 24

EBIT 24

Interest 25

EBT 25

Taxes 26

Net Income 26

Non-Recurring Events 26

Net Income (after Non-Recurring Events) 28

Distributions 28

Net Income (as Reported) 29

Shares and EPS 29

Income Statement—Making Projections 31

Revenue 32

Cost of Goods Sold 36

Operating Expenses 38

Depreciation and Amortization 40

Interest Income 40

Taxes 40

Non-Recurring Events 42

Non-Controlling Interest 43

Shares 44

Basic Shares Outstanding 44

Diluted Shares Outstanding and the Treasury Method 45

Earnings per Share 47

Chapter 2

The Cash Flow Statement 51

Cash from Operating Activities 51

Revenue 53

Cost of Goods Sold 53

Operating Expenses 53

Depreciation 53

Interest 53

Taxes 54

Cash from Investing Activities 55

Cash from Financing Activities 55

Financial Statement Flows Example 56

Walmart’s Cash Flow Statement 62

ftoc.indd 8 6/1/2013 6:34:46 PM

Contents ix

Cash from Operating Activities 62

Cash from Investing Activities 65

Cash from Financing Activities 66

Cash Flow Statement—Making Projections 68

Cash from Operating Activities 68

The Seven Methods of Projections 69

Cash from Investing Activities 73

Proceeds from Disposal of Property and Equipment 78

Items Based on Cash Available 78

Cash Flow from Financing Activities 81

Dividends 81

Purchase of Common Stock 82

Purchase of Redeemable Non-Controlling Interest 85

Payment of Capital Lease Obligations 85

Other Financing Activities 85

Effect of Exchange Rate on Cash 86

Chapter 3

Depreciation Schedule 91

Straight Line Depreciation 92

Accelerated Depreciation 93

Declining Balance 93

Sum of the Year’s Digits 94

Modified Accelerated Cost Recovery System (MACRS) 94

Deferred Taxes 97

Deferred Tax Asset 97

NOL Carryback Example 98

Deferred Tax Liability 99

Projecting Depreciation 101

Straight Line Depreciation 101

Anchoring Formula References 105

Accelerated Depreciation 116

Chapter 4

Working Capital 139

Operating Working Capital 140

Walmart’s Operating Working Capital 142

Receivables 145

Inventory 147

Prepaid Expenses 148

Accounts Payable 149

Accrued Liabilities 151

ftoc.indd 9 6/1/2013 6:34:46 PM

x Contents

Accrued Income Taxes 152

Projecting Operating Working Capital 153

Receivables 154

Inventories 156

Prepaid Expenses 157

Accounts Payable 159

Accrued Liabilities 159

Accrued Income Taxes 160

Operating Working Capital and the Cash Flow Statement 160

Changes in Accounts Receivable 164

Inventories 164

Prepaid Expenses 165

Changes in Accounts Payable 168

Changes in Accrued Liabilities 168

Changes in Accrued Income Taxes 169

Chapter 5

The Balance Sheet 175

Assets 175

Current Assets 175

Cash and Cash Equivalents 175

Accounts Receivable 175

Inventory 176

Prepaid Expense 177

Non-Current Assets 178

Property, Plant, and Equipment (PP&E) 178

Intangible Assets 178

Liabilities 178

Current Liabilities 178

Accounts Payable 178

Accrued Liabilities 179

Short-Term Debts 179

Non-Current Liabilities 179

Long-Term Debts 179

Deferred Taxes 179

Walmart’s Balance Sheet 180

Current Assets 180

Non-Current Assets 180

Goodwill 182

Other Assets and Deferred Charges 182

Current Liabilities 182

Non-Current Liabilities 183

Deferred Taxes 183

ftoc.indd 10 6/1/2013 6:34:46 PM

Contents xi

Redeemable Non-Controlling Interest 185

Shareholders’ Equity 187

Balance Sheet Projections 191

Cash Flow Statement Drives Balance Sheet vs. Balance Sheet

Drives Cash Flow Statement 191

Assets 193

Accounts Receivable 194

Inventory 196

Liabilities 200

Accounts Payable 201

Balancing an Unbalanced Balance Sheet 205

NYSF Balance Sheet Balancing Method 211

Chapter 6

The Debt Schedule, Circular References, and Finalizing the Model 215

Debt Schedule Structure 216

Modeling the Debt Schedule 216

Short-Term Debt 217

Mandatory Issuances/(Retirements) and Non-Mandatory

Issuances/(Retirements) 217

Long-Term Debt Due within One Year 220

Obligations Under Capital Leases Due Within One Year 223

Long-Term Debt 225

Long-Term Obligations under Capital Leases 227

Total Issuances/(Retirements) 231

Total Interest Expense 231

Cash Available to Pay Down Debt 231

Circular References 242

Circular Reference #Value! Errors 250

Automatic Debt Paydowns 254

Basic Switches 255

Finalizing the Model 256

Part TWO

Valuation 277

Chapter 7

What Is Value? 279

Book Value 279

Market Value 279

Enterprise Value 280

Multiples 284

ftoc.indd 11 6/1/2013 6:34:46 PM

xii Contents

Three Core Methods of Valuation 286

Comparable Company Analysis 286

Precedent Transactions Analysis 287

Purchase Multiples 287

Discounted Cash Flow Analysis 288

Chapter 8

Discounted Cash Flow Analysis 291

Mid-Year vs. End-of-Year Convention 291

Unlevered Free Cash Flow 292

Weighted Average Cost of Capital (WACC) 301

Cost of Debt 302

Cost of Equity 302

Market Risk Premium 305

Beta 307

Levering and Unlevering Beta 308

Terminal Value 309

Multiple Method 309

Perpetuity Method 310

Walmart DCF Analysis 311

WACC 313

Cost of Equity 313

EBITDA Method 319

Perpetuity Method 323

Chapter 9

Comparable Company Analysis 327

Last Twelve Months (LTM) 328

Calendarization 330

Costco as a Comparable Company 331

Costco Adjusted 2012 Year End 331

Costco Adjusted LTM Data 332

Costco Annual Income Statement 334

Revenue 334

COGS and Operating Expenses 334

Depreciation 336

Interest 338

Taxes 338

Non-Controlling Interests 341

Non-Recurring Events 341

Earnings per Share (EPS) 341

Costco Quarterly Income Statement 342

ftoc.indd 12 6/1/2013 6:34:46 PM

Contents xiii

Backing into Q4 345

Costco Year-End and LTM Adjustments 349

Costco Projections 355

Revenue 357

COGS 357

Depreciation 358

Other Income 359

Taxes 360

Non-Controlling Interests 360

Shares and Earnings per Share 360

Calculating Comparable Metrics 361

Diluted Shares Outstanding and the

Treasury Stock Method 366

Costco Market Value and Enterprise Value 368

Multiples 372

Chapter 10

Precedent Transactions Analysis 373

Identifying Precedent Transactions 373

Walmart Precedent Transaction Analysis 374

Chapter 11

Conclusion 383

52-Week High/Low 383

Comparable Company Analysis 384

Precedent Transactions 385

Discounted Cash Flow 386

Appendix 1

Model Quick Steps 391

Appendix 2

Financial Statement Flows 393

Appendix 3

Excel Hotkeys 395

About the Author 397

About the Companion Web Site 399

Index 401

ftoc.indd 13 6/1/2013 6:34:46 PM