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

ISBN-13: 9781118558768 / Angielski / Twarda / 2013 / 432 str.

Paul Pignataro

Opis

Written by the Founder and CEO of the prestigious New York School of Finance, this book schools you in the fundamental tools for accurately assessing the soundness of a stock investment. Built around a full-length case study of Wal-Mart, it shows you how to perform an in-depth analysis of that company's financial standing, walking you through all the steps of developing a sophisticated financial model as done by professional Wall Street analysts. You will construct a full scale financial model and valuation step-by-step as you page through the book.

When we ran this analysis in January of 2012, we estimated the stock was undervalued. Since the first run of the analysis, the stock has increased 35 percent. Re-evaluating Wal-Mart 9months later, we will step through the techniques utilized by Wall Street analysts to build models on and properly value business entities.

• Step-by-step financial modeling - taught using downloadable Wall Street models, you will construct the model step by step as you page through the book.
  • Hot keys and explicit Excel instructions aid even the novice excel modeler.
  • Model built complete with Income Statement, Cash Flow Statement, Balance Sheet, Balance Sheet Balancing Techniques, Depreciation Schedule (complete with accelerating depreciation and deferring taxes), working capital schedule, debt schedule, handling circular references, and automatic debt pay downs.
  • Illustrative concepts including detailing model flows help aid in conceptual understanding.
  • Concepts are reiterated and honed, perfect for a novice yet detailed enough for a professional.
  • Model built direct from Wal-Mart public filings, searching through notes, performing research, and illustrating techniques to formulate projections.
• Includes in-depth coverage of valuation techniques commonly used by Wall Street professionals.
  • Illustrative comparable company analyses - built the right way, direct from historical financials, calculating LTM (Last Twelve Month) data, calendarization, and properly smoothing EBITDA and Net Income.
  • Precedent transactions analysis - detailing how to extract proper metrics from relevant proxy statements
  • Discounted cash flow analysis - simplifying and illustrating how a DCF is utilized, how unlevered free cash flow is derived, and the meaning of weighted average cost of capital (WACC)
  • Step-by-step we will come up with a valuation on Wal-Mart
• Chapter end questions, practice models, additional case studies and common interview questions (found in the companion website) help solidify the techniques honed in the book; ideal for universities or business students looking to break into the investment banking field.

Spis treści

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

O autorze

PAUL PIGNATARO is the founder and CEO of the New York School of Finance (NYSF). He has over thirteen years of experience in investment banking and private equity in business mergers, acquisitions, restructurings, asset divestitures, asset acquisitions, and debt and equity transactions covering the oil, gas, power and utility, internet and technology, real estate, defense, travel, banking, and service industries. At NYSF, Mr. Pignataro continues to participate on the training team, actively providing training at bulge bracket banks and M&A teams at corporations, and billion dollar funds. Prior to his entrepreneurial endeavors, Mr. Pignataro worked at TH Lee Putnam Ventures, a $1 billion private equity firm affiliated with buyout giant Thomas H. Lee Partners. Prior to TH Lee, Mr. Pignataro worked in the Investment Banking department of Morgan Stanley.

Blurb

Praise for Financial Modeling and Valuation

"In Financial Modeling and Valuation, Paul Pignataro provides individual investors with an easy–to–use, step–by–step method for analyzing companies and their securities using the same financial modeling techniques used by professionals. By using Walmart, an American icon that is familiar to all, as a case study, Paul makes financial modeling come alive. Paul trained our staff in Beijing and is exceptionally well qualified to write this book."
Jack Perkowski, Wall Street veteran; Author; and Founder and Managing Partner of JFP Holdings Ltd.; former CEO and Chairman, ASIMCO Technologies, one of the largest producers of automotive components in China

"Paul Pignataro′s book is a treasure. He combines in one place a presentation of the concepts used in accounting and finance with the practical Excel ways to put them in action. This manual presents everything a student or a professional would like to understand about financial statements and valuation methods used in mergers and acquisitions. It is written in simple language, and the clarity of the exposé makes it a perfect companion for those who are involved in financial matters."
Georges Ugeux, Chairman and CEO, Galileo Global Advisors; Adjunct Professor, Columbia Law School; former Group Executive Vice President, New York Stock Exchange

"Financial Modeling and Valuation is a key handbook for investment banking professionals and business students looking to get into the field. As a founder of a leading investment banking, private equity and hedge fund recruiting business, and as the former head of the analyst program at Morgan Stanley, I know the importance of demonstrated proficiency in financial modeling and valuation both during the hiring process and on the job. Finally, there exists a book that actually goes through the model building and valuation process in complete detail with methodologies critical to both budding analysts and experienced professionals."
Eleni Henkel, Founder and Partner at Henkel Search Partners; former Managing Director and Global Head of Morgan Stanley′s Analyst and Associate Programs

"Financial Modeling and Valuation offers an extensive overview of key fundamentals of investment banking, private equity, and M&A analysis. It will be of interest to students and discerning investors alike."
Professor Charles H. McGill, NYU Stern School of Business; Advisor with several leading private equity firms

"I thoroughly endorse this financial ′model′ work of art! Financial modeling and valuation methods are an art form of comparable analysis with a wide range of variables that can be considered quite subjective in terms of which method should be utilized. To become and stay informed, these mentioned models or tools can identify the proper program of analysis for ′rationalizing′ a potential investment."
Henry W. McCusker, Analyst, Journalist, and Publisher; Founder and Director of Research, Scimitar Equity

"This book contains all the basic concepts required to successfully apply for, and get a head start on, a career on Wall Street, whether as a sell–side analyst, an investment banker, or a private equity specialist. I especially value the focus on Walmart, because this company touches on so many sectors and trends in today′s business world, from retailing to logistics to real estate."
Julio Marquez, Partner, Wyndham Global Partners