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

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

As underwriters for companies issuing securities, investment bankers put a lot on the line every day. That's why, before committing to a particular business, they go to great lengths to develop as clear and detailed a picture as possible of that company's current financial standing—and generate an accurate projection of its future performance.

Likewise, as facilitators of mergers and acquisitions, investment bankers are tasked with finding answers to crucial questions such as, "How much is the company really worth? Is its stock overvalued or undervalued, and, if so, what is the most correct stock price?"

But when it comes right down to it, investment bankers aren't all that different from you or any investor looking to make money in the stock market. Unlike you, though, the big banks employ armies of analysts trained in the most sophisticated financial modeling techniques.

Designed to balance the scales and give individual investors like you the same competitive edge as today's most prestigious investment banks, Financial Modeling and Valuation arms you with many of the same analytical tools and techniques used by the likes of Goldman Sachs, JPMorgan Chase, and Bank of America Merrill Lynch.

Written for complete novices and experienced professionals alike, Financial Modeling and Valuation is a highly accessible, step-by-step guide to understanding and performing fundamental analysis and stock valuation.

Author Paul Pignataro, founder and CEO of the world-renowned New York School of Finance, takes a unique approach to educating you in the fundamental tools for assessing the soundness of a stock investment. Building on a book-length case study of Walmart, he shows you how to perform a deep-dive analysis of that company's financial standing, walking you through all the steps of developing a sophisticated financial model using a downloadable Excel template.

While the first part of the book is devoted to building a complete financial model of past and future performance, the second part focuses on stock valuation. Once again taking Walmart as the example, Pignataro clearly and methodically shows you how to use the three analytical methods used by Wall Street analysts to determine whether a stock is overvalued, undervalued, or valued appropriately: comparable company analysis, discounted cash flow analysis, and precedent transaction analysis.

As an added bonus, the Financial Modeling and Valuation companion website features a downloadable version of the Walmart model template, along with additional case studies, exercises, test questions, and a practice model and solution to let you test and fine-tune your analytical skills.

Schooling you in time-tested financial modeling and stock valuation techniques used by analysts at the world's top investment banks, Financial Modeling and Valuation will help you make smarter, more rational investment decisions.