Financial Modeling and Valuation: A Practical Guide to Investment Banking and Private Equity(Wiley Finance)

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.

Table of Contents:
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

About the Author :
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.