reading

Contents

Cover

Series

Title Page

Dedication

Preface

THE WALMART CASE STUDY

HOW THIS BOOK IS STRUCTURED

Part One: Financial Statements and Projections

Chapter 1: The Income Statement

REVENUE

COST OF GOODS SOLD

OPERATING EXPENSES

OTHER INCOME

DEPRECIATION AND AMORTIZATION

INTEREST

TAXES

NON-RECURRING AND EXTRAORDINARY ITEMS

DISTRIBUTIONS

WALMART’S INCOME STATEMENT

INCOME STATEMENT—MAKING PROJECTIONS

Chapter 2: The Cash Flow Statement

CASH FROM OPERATING ACTIVITIES

CASH FROM INVESTING ACTIVITIES

CASH FROM FINANCING ACTIVITIES

WALMART’S CASH FLOW STATEMENT

CASH FLOW STATEMENT’MAKING PROJECTIONS

Chapter 3: Depreciation Schedule

STRAIGHT LINE DEPRECIATION

ACCELERATED DEPRECIATION

DEFERRED TAXES

PROJECTING DEPRECIATION

Chapter 4: Working Capital

OPERATING WORKING CAPITAL

WALMART’S OPERATING WORKING CAPITAL

PROJECTING OPERATING WORKING CAPITAL

OPERATING WORKING CAPITAL AND THE CASH
FLOW STATEMENT

Chapter 5: The Balance Sheet

ASSETS

LIABILITIES

WALMART’S BALANCE SHEET

BALANCE SHEET PROJECTIONS

BALANCING AN UNBALANCED BALANCE SHEET

Chapter 6: The Debt Schedule, Circular References, and Finalizing the Model

DEBT SCHEDULE STRUCTURE

MODELING THE DEBT SCHEDULE

CIRCULAR REFERENCES

AUTOMATIC DEBT PAYDOWNS

BASIC SWITCHES

FINALIZING THE MODEL

Part Two: Valuation

Chapter 7: What Is Value?

BOOK VALUE

MARKET VALUE

ENTERPRISE VALUE

MULTIPLES

THREE CORE METHODS OF VALUATION

Chapter 8: Discounted Cash Flow Analysis

MID-YEAR VS. END-OF-YEAR CONVENTION

UNLEVERED FREE CASH FLOW

WEIGHTED AVERAGE COST OF CAPITAL (WACC)

BETA

TERMINAL VALUE

WALMART DCF ANALYSIS

Chapter 9: Comparable Company Analysis

LAST TWELVE MONTHS (LTM)

CALENDARIZATION

COSTCO AS A COMPARABLE COMPANY

Chapter 10: Precedent Transactons Analysis

IDENTIFYING PRECEDENT TRANSACTIONS

WALMART PRECEDENT TRANSACTION ANALYSIS

Chapter 11: Conclusion

52-WEEK HIGH/LOW

COMPARABLE COMPANY ANALYSIS

PRECEDENT TRANSACTIONS

DISCOUNTED CASH FLOW

Appendix 1: Model Quick Steps

Appendix 2: Financial Statement Flows

INCOME STATEMENT TO CASH FLOW

CASH FLOW TO BALANCE SHEET

Appendix 3: Excel Hotkeys

About the Author

About the Companion Web Site

Index

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

Preface

The markets are vast and complex—not only the United States, but also the global markets. Stocks, bonds, mutual funds, derivatives, options—yes, choices are endless, literally. Everyone wants to make money. Yet, throughout the past years we have faced tremendous market swings, rendering investors (and their money) in a sea of lost hopes and few investors with a plethora of wealth. Many of these market anomalies and swings are dependent, and in a sense dictated by the investor—you. The investor plays a part in setting the current stock price. The reaction of the investor can aid in determining the success of an initial public offering (IPO). Yes, the collective psychology of the market as a whole plays a major role, but if the everyday investor were better equipped with the proper tools to understand the underlying fundamentals of a rational investment, smarter investment decisions could be made, more rational investments would be made, and the markets would be a more efficient environment.

This book sets out to give any investor the fundamental tools to help determine if a stock investment is a rational one; if a stock price is undervalued, overvalued, or appropriately valued. These fundamental tools are used by investment banks, private equity firms, and Wall Street analysts.

We will evaluate Walmart, determining its current financial standing, projecting its future performance, and estimating a target stock price. We will further assess if this is a viable investment or not, but more importantly, give you the tools and concepts to make your own rational investment decisions. We will have you step into the role of an analyst on Wall Street to give you first-hand perspective and understanding of how the modeling and valuation process works with the tools you need to create your own analyses.

This is a guide designed for investment banking and private equity professionals to be used as a refresher or handbook, or for individuals looking to enter into the investment banking or private equity field. Whether you are valuing a potential investment or business, the tools demonstrated in this book are extremely valuable in the process.

THE WALMART CASE STUDY

We will analyze Walmart throughout this book. Walmart is an American multinational retailer corporation that runs chains of large discount department stores and warehouse stores. The company is the world’s third largest public corporation, according to the Fortune Global 500 list in 2012. It is also the biggest private employer in the world, with over two million employees, and is the largest retailer in the world. If we want to invest in Walmart, how do we determine the viability of such an investment? In order to ensure profitability from a stock investment, we need to understand what the future stock price of Walmart could be. Obviously, stock price fluctuations are largely based on public opinion. However, there is a technical analysis used by Wall Street analysts to help determine and predict the stock price of a business.

We ran this analysis in January 2012, determining there was still more room for the stock price to increase. Since that analysis, the stock price has in fact increased significantly from $57.39 to $73.82. We now want to reassess the analysis to see where the stock price can go from here. This time, we will walk you through a complete analysis of Walmart to determine the potential value of the stock. It is important to note this book was written in October of 2012 so all analyses are based on that date.

This technical analysis is based on three methods:

1. Comparable company analysis

2. Discounted cash flow analysis

3. Precedent transaction analysis

Each of these three methods views Walmart from three very different technical perspectives. Individually, these methods could present major flaws. However, it is the common belief that looking at all of these methods together will help us understand the technical drivers supporting Walmart’s
current stock price. Using Walmart as the example, we will build and construct all three of the listed analyses and all the supporting analyses exactly as a Wall Street analyst would. We will then have the ability to interpret from the analysis if Walmart is undervalued, overvalued, or appropriately valued. If the company is determined to be undervalued, that may suggest the stock price is lower than expected. We can potentially invest in the business and hope the stock price in time will increase. If the company is determined to be overvalued, that may suggest the stock price is higher than expected. In this case it may not make sense to invest in the business, as the stock price in time could potentially decrease. We are assuming in these cases there has been no unusual or unpredictable activity or announcements in Walmart’s business or in the stock market. Such activity or announcements would affect the stock price above and beyond what the technical analysis predicts.

It is important to note that the modeling methodology presented in this book is just one view. The analysis of Walmart and its results do not directly reflect my belief, but rather, a possible conclusion for instructional purposes only based on limiting the most extreme of variables. There are other possibilities and paths I have chosen not to include in this book, but could have also been sufficient. Many ideas presented here are debatable, and I welcome the debate. The point is to understand the methods, and further, the concepts behind the methods to properly equip you with the tools to drive your own analyses.

HOW THIS BOOK IS STRUCTURED

This book is divided into two parts:

1. Financial Statements and Projections

2. Valuation

In Part One, we will build a complete financial model of Walmart. We will analyze the company’s historical performance and step through techniques to make accurate projections of the business’s future performance. The goal of this section is not only to understand how to build a model of Walmart, but also to extract the modeling techniques used by analysts and to apply those techniques to any investment.

Once we have a good understanding of Walmart’s past and future performance, Part Two will help us interpret the company’s financials into a valuation analysis using the methods mentioned previously. You may skip directly to Part Two if your needs do not require building a complete financial model.

It is important to note it is not 100 percent necessary to have a full-scale model in order to conduct a valuation analysis, but it is recommended. Valuation techniques are based on a summary of the company’s performance. In this case, to be complete, we will use the model of the company built in Part One to extract the necessary summary information and to conduct the valuation analysis. However, you could technically use summary information as well.

The book is designed to have you build your own model on Walmart step-by-step. The model template can be found on the companion web site associated with this book and is titled “NYSF—Walmart—Template.xls.” To access the site, go to www.wiley.com/go/pignataro (password: investment).