Financial Modeling and Valuation A Practical Guide to Investment Banking and Private Equity

The fully revised new edition of the best-selling guide to using financial models to determine if a stock is over or undervalued

Written by the founder and CEO of the world-renowned New York School of Finance, Financial Modeling and Valuation provides clear and systematic guidance on accurately evaluating the soundness of a stock investment. This invaluable handbook equips investors with the tools necessary for understanding the underlying fundamentals of a rational investment and for making smarter investment decisions in any market environment.

Built around an in-depth case study of global retail leader Amazon, this fully updated Second Edition shows you how to analyze the financial standing of a company using the methods of Wall Street professionals. Step-by-step, you will learn to build the core three statements—income statement, cash flow statement, and balance sheet—as well as the three major supporting schedules required for complete company valuation and analysis. All line items are explained in clear language and include real-world tips and techniques for using them as tools for valuing and managing a business. This must-have guide:

• Features new and in-depth case studies based on Amazon that simulate real-world modelling and valuation
• Explains valuation techniques such as illustrative comparable company analysis, precedent transactions analysis, and discounted cash flow analysis
• Covers all essential applications of a model, including pricing a stock, raising debt, and raising equity
• Includes an introductory section describing the recent and dramatic shift of the entire retail industry
• Provides end-of-chapter questions, downloadable practice models, additional case studies, and common interview questions via a companion website

Financial Modeling and Valuation: A Practical Guide to Investment Banking and Private Equity, Second Edition is essential reading for finance professionals, venture capitalists, individual investors, and students in investment banking and related degree programs in finance.

PAUL PIGNATARO is Founder of the New York School of Finance, a world leader in providing finance education to banks, firms, and universities throughout the world. He is also Managing Partner of Caterson Consulting, a consulting firm providing merger, acquisition, and private equity services. He is the author of the Investment Banking and Private Equity in Practice series of books. Previously, Paul worked at TH Lee Putnam Ventures, a $1 billion private equity firm affiliated with buyout giant Thomas H. Lee Partners, and at Morgan Stanley, where he worked on various transactions in the technology, energy, transportation, and business services industries.

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

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

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

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

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

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

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 recedent 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