© 1999-2011 training the street, inc. all rights reserved. of instructor-led courses in financial...

1

© 1999-2011 Training The Street, Inc.All rights reserved.

Training The Street, Inc. (“TTS”) owns all rights, including copyright, in this publication. This publication may not be reproduced or redistributed, in whole or in part, in any format or by any means without TTS's prior written consent. No TTS seminar, workshop, or other instructional activity may be recorded or transmitted in any format or by any means without TTS's prior written consent.

Preparing Financial Professionals for Successwww.trainingthestreet.com

TRA

ININ

G T

HE

STR

EET

Table of Contents

1. THE LIFE CYCLE OF AN M&A DEAL ..............................................................................................................111.1 Overview ..................................................................................................................................................121.2 The Six Steps of the Merger Process (From the Seller's Perspective) ................................................131.3 The Six Steps of the Merger Process (From the Buyer’s Perspective) ................................................231.4 Knowledge Check ....................................................................................................................................30

2. FUNDAMENTALS OF MERGER MODELING ................................................................................................ 332.1 Introduction .............................................................................................................................................342.2 The Three Phases of Merger Consequences Analysis .........................................................................382.3 Purchase Accounting ..............................................................................................................................512.4 Pro Forma Transaction Adjustments .....................................................................................................562.5 Relative P/E Analysis: When You Need a Quick Look ...........................................................................732.6 Comparing P/E of Cash and P/E of Stock ...............................................................................................822.7 Optimizing the Deal Structure: Cash versus Stock Consideration ......................................................842.8 Considerations with Mixed Stock-Cash Combinations ........................................................................862.9 Estimating Synergies ..............................................................................................................................932.10 Exchange Ratio Analysis .......................................................................................................................972.11 Contribution Analysis ..........................................................................................................................1002.12 Pro Forma Ownership Analysis .........................................................................................................1012.13 Premiums Paid Analysis: Acquisitions—and Rumors of Acquisitions............................................1032.14 Knowledge Check ................................................................................................................................109

3. STEPS ON BUILDING A MERGER MODEL ................................................................................................ 1153.1 Overview of Building a Merger Model ................................................................................................1153.2 Checking Your Model ............................................................................................................................1673.3 Determine the Optimal Structure .........................................................................................................1843.4 Knowledge Check ..................................................................................................................................189

4. ADDITIONAL TOPICS IN M&A MODELING ............................................................................................... 2014.1 When Target and Acquirer Use Different Currencies .........................................................................2024.2 Calendarizing: When Target and Acquirer Have Different Fiscal Year Ends .....................................2034.3 An Overview of Modeling Write-Ups ...................................................................................................2064.4 Tax Considerations: U.S. Corporate Tax Law ......................................................................................2134.5 Deal-Protection Mechanisms ................................................................................................................2194.6 Collar Agreements .................................................................................................................................2204.7 Defenses Against Takeovers .................................................................................................................2254.8 Qualitative M&A Issues .........................................................................................................................2294.9 Creating Value ........................................................................................................................................2444.10 Wrapping Up ........................................................................................................................................2534.11 Knowledge Check ................................................................................................................................255

HOTWORD GLOSSARY ................................................................................................................................... 261

TRA

ININ

G T

HE

STR

EET

Before We Get Started…

Welcome to Training The Street's Merger Modeling course pack. Training The Street (TTS) is the world's foremost provider of instructor-led courses in financial modeling and valuation training. We work with Wall Street investment banks, Fortune 500 companies, business schools, and colleges.

This self study book was written to meet a need of many of our participants—a need for a course pack you could use on your own if you can't attend one of our live classes. It covers one of our most popular valuation topics: merger modeling. What you have in your hands is the very best out-of-classroom experience possible—a distillation of all the knowledge we've gained from training thousands of professionals in live seminars. You won't need any other resources to become proficient at Excel-based merger modeling. It's a complete, self-contained course. Welcome aboard, and let's get started.

If you've already purchased our Corporate Valuation self study book, you'll find that this course pack delves into greater detail about M&A topics, such as asset purchases versus stock purchases. Another big difference is that this course pack covers how to build a merger model in Excel and use it to analyze a sample deal. If you're already familiar with the Corporate Valuation guide, you may find some of the basic terms and concepts repetitive here, but we wanted to provide a comprehensive treatment of M&A that doesnt leave beginners behind.

WHO IS THIS COURSE FOR?

You don't have to work on Wall Street—or be planning to—in order to benefit from this course pack. Merger modeling has a vast range of applications, and our course is designed for professionals and students from many backgrounds and with potentially very different goals. We've focused on transferable skills and techniques that can be applied to a diverse range of industries and businesses. These include traditional areas like investment banking, investment research, and sales and trading, but they also include:

• Consulting – Merger analysis can help management and strategy consultants to develop and deliver value-added advice to their clients

• Industry Finance – Merger analysis can be used in the internal finance departments of companies exploring merger opportunities with other companies

• Accounting – Understanding merger purchase accounting is critical in preparing merger-related documents

6

Merger Modeling Course Pack – BEFORE WE GET STARTED...

• Legal – Attorneys involved in an M&A transaction should know what the terms of the deal are, and how they were derived by clients and their investment bankers

This course pack is designed for:

Intermediate Excel modelers

Experienced Excel modelers

This course pack already assumes that you have an introductory level understanding of Excel shortcuts, techniques and have experience with financial modeling. If you are new to financial modeling, we strongly recommend that you check out our Excel Best Practices and Financial Modeling course packs on our website which will help you improve your skills and become a faster, more-efficient modeler. You'll find our course packs are designed to be intuitive and step-by-step.

HOW IS THIS COURSE ORGANIZED?

With years of professional teaching experience behind us, we have assembled what we believe is the optimal way to teach you merger modeling. Our Merger Modeling course pack includes the following:

Merger Modeling Self Study Book – This book teaches you how to construct an integrated merger model from scratch. It will also cover merger accounting concepts via practical pen-and-paper exercises.

Merger Modeling Excel Drills – The Excel exercises, templates, and models reinforce the concepts covered in the core book. Our online media library is continually updated with the latest software, functionality, and support.

Online Companion – a. Merger Modeling Excel Best Practices: Shows you advanced excel skills you'll need in order to be proficient at merger modeling

b. Building and Working with the Model: Step-by-step walkthrough on how to build the model to be used in conjunction with the printed book; also covers additional modeling complexities not covered in the book

SPECIAL FEATURES

Keep a look out for the following icons as you review the book:

Multimedia – This icon indicates that you can find multimedia clips in the Online Companion. Please log in to the website to view the Online Companion. You can pause to watch the multimedia clips and then return to the book.

Key Formulas – To accelerate your learning, we have identified key formulas we regard as especially critical to merger modeling. You will recognize them by the “key” icon next to them.

7

Hotwords – You will come across hotwords throughout the book. These words, bolded and bracketed in solid arrowheads, are terms widely used in corporate finance and M&A work. You can look them up in the Hotword Glossary at the end of the book.

Excel Drills – This icon indicates that you can find Excel based exercises in the Online Companion. You can pause to watch the companion multimedia clips and then try your hand at the calculations.

Excel Shortcut – We are big fans of using shortcut keys! It makes you more efficient. For more tips and tricks, we recommend purchasing our Excel Best Practices Course Pack.

There are a few other helpful features of this book that we'd like to bring to your attention. As you progress, you'll see one or more of these boxes:

Ask the Finance Guru – In these boxes, various TTS experts share their thoughts on particular questions. Because we train thousands of finance professionals every year, our experience has allowed us to identify the finance and valuation questions that most often puzzle students and practitioners. We have drawn on this reservoir of experience to frame and answer some of the most commonly posed questions.

Tips of the Trade – These boxes offer you practical suggestions, calculation assistance, shortcut tools, and how-to tips, or flag commonly encountered pitfalls and errors that you should watch out for.

Financial Factoids and Did You Know? – These boxes present interesting, relevant facts that will help you contextualize merger analysis.

A Word on Terminology – These boxes offer you quick definitions of difficult or arcane terms.

A final piece of advice before we jump into the thick of things: try to view this book not as a “textbook” but rather as a practical guide that offers you a framework for the kinds of merger analyses that finance professionals perform when they value businesses. Above all, we hope it helps to demystify the whole topic by laying out—in a truly clear, down-to-earth way—the types of analytics that finance professionals use every day when they go to work.

At this point, please log in to the website to view the Introduction video in the Online Companion for a more in depth look at how the core book and the online companion will work together.


8

REVIEW: WHAT EXACTLY IS A FINANCIAL MODEL, AND HOW DOES A MERGER MODEL DIFFER?

Broadly speaking, a financial model is a simplified, mathematical representation of a company's financial and operational performance over a specified period. Business professionals use models to predict the outcome of a financial decision or to forecast how a company will respond to various hypothetical economic events.

There are many kinds of models to choose from. Here are a few basic ones:

Corporate Finance Models• Merger consequences model• Earnings model/Profit and loss model• Debt/Recapitalization/Financing model• Comparables model• Discounted cash flow model

Sales and Trading Models• Bond pricing model• Option pricing model

Industry Finance Models• Detailed budgets/Profit and loss model

This book will guide you through the creation of one particular type of model: the Merger Consequences Model, or Merger Model. Our goal in using it is to examine the pro forma impact of combining two companies. Pro forma means “as if.” Merger modeling, in other words, studies the post-transaction changes in the financial position of two companies as if the merger had already taken place. Using various metrics, ratios, and other analytical tools, modeling creates a revealing story about the core financials of the would-be merged company.

This composite picture gives us a quantitative basis for advising either the buying or the selling company about whether to even consider the transaction, how attractive it may be, how to structure and value the deal, and the potential risks associated with the proposed transaction.

Merger modeling is therefore both conceptual and practical, both analytical and concrete. Here are some key conceptual and practical questions we will address in these materials:

• What does the M&A process look like from both the seller's perspective and the buyer's perspective?

• What are some qualitative and quantitative issues to think about when performing merger consequences analysis?

• How do practitioners utilize a merger model?

• What tools do finance practitioners most widely use when evaluating a transaction?

• What are the limits of merger analysis, and are there other analytical tools that may complement it?

• What are the typical terms outlined in a merger agreement, and what do they mean?


9

FOUNDATIONAL SKILLS: ACCOUNTING AND FINANCIAL STATEMENT ANALYSIS

Aside from the Excel skills we mentioned earlier, you'll need some basic knowledge of accounting and financial statement analysis. Here's an example of an accounting rule:

1. A = L + E. The balance sheet in a merger model must be balanced, or “in parity.” This means its asset balances (A) must equal the sum of its liability (L) and equity balances (E). That’s an accounting rule. After we’ve performed merger accounting and financing adjustments in our model, our pro forma (i.e., post-merger) balance sheet should balance and be "in parity" if we made our adjustments correctly. We will cover this in greater detail later in the book. There are other accounting concepts throughout the book that we will refer to or make note of later on.

The other fundamental skill you need some familiarity with is Financial Statement Analysis. In short, you need to be able to read a disclosure of financial information and use various ratios and metrics to analyze that information. We will cover important leverage ratios, such as Debt to EBITDA, to see how much debt the acquirer can use to finance the purchase of the target (recall, EBITDA stands for earnings before interest, taxes, depreciation, and amortization; and is a commonly used proxy for free cash flow). In general, it's important to have a foundational grasp of the three financial statements—income statement, balance sheet, and cash flow statement—to understand how M&A impacts each of them.

If you're new to accounting or financial statement analysis, we strongly recommend that you consider our Fundamentals of Financial Accounting & Analysis course pack on our website to help you improve your knowledge and skill set in this area. It’s available on our website.

We urge you to learn how to calculate every number in your merger model using pen and paper. We call this "conference call math", "meeting math", or "cocktail napkin math". Why do we recommend this so strongly? Because you won’t always have a computer in front of you when discussing valuation with a coworker or a client. Suppose you’re asked a merger analysis-related question that involves some computation when you’re away from your computer (which happens frequently). We want to get you ready for that situation!

So before you launch Excel and start modeling, you first need to know how to do merger analysis by hand. Don’t worry, we have plenty of exercises to help you practice this and get up to speed. Once you’ve mastered the underlying concepts of merger modeling on paper—which won’t take long—we can then move on to modeling in Excel.


10

Merger Modeling Course Pack – TABLE OF CONTENTS

WHAT ARE YOU TRYING TO DO? END GOAL OF A MODEL

When you're creating any model, you should always have the end goal in mind. If you know what you're trying to achieve, then you can pursue that goal more efficiently and effectively. Our goal is to come up with a virtual re-creation of a particular company's business that is both flexible and dynamic. What do we mean by these terms?

A flexible, adaptable model is one that allows you, at some future point, to expand it to accommodate more advanced analyses. For example, let's imagine that we wanted to incorporate some additional advanced merger analysis, such as analysis at various prices, or a sensitivity analysis utilizing data tables. We would need to make sure that our model has the built-in flexibility to include these additional functions.

Flexibility in a model: It helps to make a model dynamic. A dynamic model is one that permits you to change its assumptions or drivers easily in order to reflect real or hypothetical changes within the company, within its industry, or in relation to its macroeconomic environment. A flexible model, in other words, allows you to test different scenarios. We should, for example, be able to change our sales growth rate assumption and readily see how this affects the company's projected financial performance.

So, without further ado, let’s turn to Chapter 1. Chapter 1 offers you a Big Picture overview of the stages of an M&A transaction from the competing perspectives of the seller and the buyer. This perspective will prepare us to walk through the same M&A “life cycle” again in Chapter 2, but this time at a more deliberate pace and at a greater level of detail. That, in turn, will prepare us to actually build a merger consequences model—for real—in Chapter 3. Chapter 4 will then cover additional topics in M&A analysis.

TRA

ININ

G T

HE

STR

EET

The Life Cycle of an M&A Deal

Kraft Buys Cadbury in a Hostile M&A TransactionIn August of 2009, Kraft, a maker of consumer food products, approached Cadbury, the European consumer food products company, about a possible merger. The offer was for a cash/stock mix of 755 pence, representing a 31.0% premium over the previous day close. At the time, the deal was estimated to have approximately $625.0 million of cost savings. Cadbury's Board of Directors unequivocally rejected the offer starting a half a year process of courtship from Kraft. After some back-and-forth, creative deal making and term revisions, eventually, the Cadbury Board relented, and a deal was reached in January 2010 with a revised bid of 840 pence, which translated into an offer value of approximately £13.0 billon.

Merger analysis is a fascinating topic, not just because of the scale of the numbers, but also because of the human story behind the numbers. The stakes are often unbelievably high, and besides the two companies directly engaged in the merger process, there are many other parties involved in negotiating the entire transaction. We'll be using various analytical tools, along with qualitative frameworks, to examine the M&A transaction.

The newspaper capsule about the Kraft/Cadbury merger, above, offers little more than a mere snapshot, a skeletal timeline of just the important landmarks in the story as it unfolded. But behind this sparse synopsis lay a six-month-long drama of shifting expectations, revived hopes, late-night negotiations, corporate adrenaline, and seemingly tireless analysis by vast armies of well-trained professionals.

This book won’t tell that particular story in full, but we’ll use various analytical tools, complemented by qualitative frameworks, to examine and unpack the different stages of M&A transactions like the Kraft/Cadbury merger. By the end of Chapter 1, you should have a sound grasp of the different steps of a merger transaction and of the roles, perspectives, and priorities of the different parties involved in it. This will provide us with a context for the quantitative analyses that will form the basis of our discussion throughout this book.

12

Merger Modeling Course Pack – LIFE CYCLE OF AN M&A DEAL

Before studying this topic, you should:

1. Have a general understanding of what an M&A transaction is

STUDY OBJECTIVES:

After studying this chapter, you should be able to:

1. Understand the life-cycle of a sample M&A transaction

2. Understand the M&A transaction from the seller’s point of view

3. Understand the M&A transaction from the buyer’s point of view

A WORD ON TERMINOLOGYValuation terminology may vary depending on the context. A case in point: the phrases “buy-side” and “sell-side.” In investment banking, the 'sell side' refers to institutions that sell securities, such as investment banks. The 'buy side' refers to the investors, such as hedge funds and assets managers, who are buying securities or making investments. In the M&A context, buy-side and sell-side refer to the buyer and the seller of a particular company in a proposed transaction. It's important to know the audience when discussing valuation.

1.1 OVERVIEW

The next few sections will describe the M&A transaction process both from the seller's and the buyer's perspectives. This will provide some context for the quantitative analyses that will form the basis of our discussion throughout this volume.

As you can see below, the activities and perspectives of the buy-side and the sell-side are virtually mirror images of each other:

SELL-SIDE BUY-SIDE

1. Develop exit strategy & engage

1. Develop growth strategy & engage

2. Establish perspective on valuation

2. Establish perspective on valuation

3. Identifying buyers 3. Approach target

4. Preparing for due diligence 4. Conduct due diligence

5. Bidding, negotiation, & structuring

5. Bidding, negotiation, & structuring

6. Closing process 6. Closing process

TRA

ININ

G T

HE

STR

EET

Chapter 3: Steps on Building a Merger Model

Before studying this topic, you should:

1. Log on to the website to download the Excel templates and drills2. Watch the videos online to help out with the steps and troubleshooting

STUDY OBJECTIVES

After studying this chapter, you should be able to:

1. Build a merger model2. Troubleshoot and perform safety checks on a merger model3. Understand how the Acquirer and Target determine a price and consideration mix

3.1: OVErVIEw Of BUIlDIng A MErgEr MODEl

In Chapter 3, we will actually build a merger model, using as a blueprint the three conceptual phases of merger consequences analysis that we went over in Chapter 2. The three phases are:

I. Structure the Terms of the Transaction II. Calculate Transaction Adjustments III. Analyze Pro forma Impact

To construct our merger model, we will follow a 25-step process. We’ve summarized these steps in a Quick-Look Chart to offer you a road map of where we’re headed—the terrain that lies ahead of us. After we’ve built the model, we will then check its integrity by stress-testing the drivers and performing a few other important checks.

116

Merger Modeling Course Pack – BUILDING A MERGER MODEL

Step Setting Up the ModelI. Structure the

Terms of the TransactionII. Calculate Transaction

AdjustmentsIII. Analyze Pro forma

Impact

1 Preliminary Set-Up:a. Choose a basic or

advanced scheduleb. Choose a vertical or

horizontal layoutc. Choose a top-

down or bottom-up income statement approach

2 Set Up the Transaction Details

3 Set Up the Opening Balance Sheet

4 Set Up the Opening Income Statement

5 Input Basic Information and Calculate Offer Value

6 Calculate Uses

7 Calculate Sources

8 Estimate Synergies

9 Input Target and Acquirer Balance Sheet Information and Calculate Ratios

10 Calculate Goodwill

11 Complete Goodwill Allocation Adjustments

117


Step Setting Up the ModelI. Structure the

Terms of the TransactionII. Calculate Transaction

AdjustmentsIII. Analyze Pro forma

Impact

12 Complete Financing and Other Adjustments

13 Calculate Pro Forma Combined

14 Calculate Totals and Balance

15 Input Target and Acquirer Income Statement Projections and Complete Calculations

16 Include the Impact of Synergies

17 Calculate Incremental Pre-Tax Interest Expense from Acquisition Debt

18 Calculate Incremental Taxes and Net Income

19 Calculate New Shares Issued

20 Adjust Shares Outstanding

21 Calculate Pro Forma Results and Accretion / (Dilution)

22 Calculate Incremental Pretax Cushion / (Synergy)

23 Conduct Relative P/E Analysis

24 Conduct Contribution Analysis

25 Conduct Ownership Analysis

118


Once we’ve constructed our merger model, we will then check it by using a five-step procedure:

1. Auditing2. Stress-testing the Drivers 3. Verifying/Checking your Data Tables4. Replugging—Stress-testing the Synergies and the

Breakeven Offer Prices5. Checking by Hand

The final step will be to determine the optimal structure for the transaction you’ve been modeling by subjectively weighing various tradeoffs between the different parts of a transaction, such as the offer price and the consideration mix, and their pro forma effects on the new company’s earnings, credit rating, and ownership structure.

Ready for a little piece of advice? In modeling, things can get very detailed very quickly because it requires that you translate theory (what we covered in the previous sections) into practice (the Excel model). Keep your focus on the big picture because it's easy to get lost in the details. Regularly ask yourself: “Where am I within the modeling process? What stage is this? Where are we going, and what am I trying to do at this moment?”

Obviously the better you understand the theory and the Excel, the better you will be at modeling, but it's tough if you're good at just one and not the other. To that end, the steps include brief overviews of the theory that reiterate many of the things discussed in previous sections of this book.

Also, in the Online Companion (login through the website), the Merger Modeling Excel Best Practices section contains videos that review some of the more advanced excel skills you'll need to complete the model:

• Auditing in Excel

• Working with Data Tables and Stress Testing Data Tables

• Text Functions

• Installing the TTS Turbo Macros

Notice that basic excel skills are not covered as this book assumes some Excel and modeling experience. This course pack already assumes that you have an introductory understanding of Excel shortcuts, techniques and financial modeling experience.

If you are new to financial modeling, we would strongly recommend that you check out our Excel Best Practices and Financial Modeling course packs on our website to help get you up to speed and become a faster, more-efficient modeler. You'll find our course packs are designed to be intuitive and step-by-step.

Once you've reviewed the above Excel skills videos, let's plunge in and start modeling, beginning at the beginning: Step 1.

120

Setting Up the Model

Our first step actually consists of three preliminary sub-steps that we need to get out of the way:

• Choosing a basic or advanced schedule• Choosing a vertical or horizontal layout• Choosing a top-down or bottom-up income

statement approach

a. Schedules: Decide if you need a basic schedule or a more advanced one

Transaction Details Opening Balance Sheet Pro forma Income Statement

• Target and Acquirer information

• Offer value calculations• Synergies estimates• Sources and uses

• Target and Acquirer balance sheet (BS) information

• Goodwill created calculation• Pro forma adjustments • Pro forma credit analyses

• Target and Acquirer income statement (IS) information

• Pro forma EPS adjustments• Pro forma accretion /

(dilution)• Additional analyses

Most merger models generally have at least three core schedules, as shown above. Alternatively, you’ll often see simplified merger models that contain only a few core analyses and don’t have full balance sheets or income statements. These models essentially consolidate the three schedules above into one for a quick snapshot of merger consequences. However, for more advanced analyses—such as AVP (analysis at various prices), write-ups, scenarios, and contribution analysis—you’ll often see additional schedules that enhance the model with more detail and functionality.

In this chapter, our model will follow the basic schedule setup: It will have a transaction details page, an opening BS, and an opening IS. Later, once we’ve finished learning about the fundamentals, we’ll work on more advanced analyses.

STEP 1Preliminary Set-Up

NOTES

121


b. layout: Decide on a vertical or a horizontal layout for your model

A Vertical Model A Horizontal Model

• All the schedules are contained on one tab in the worksheet

• Often easier to navigate when there is less detail

• Harder to navigate when the model is large because the model gets “deep” as the number of rows increases

• Here, each schedule has its own tab in the model

• A little harder to navigate because you have to keep switching back and forth between tabs

• Easier to navigate when the model is large since each schedule is then “contained” on separate tabs

NOTES


How do you decide which approach to take—vertical or horizontal?• First, think about the model’s purpose. Let form follow function.

• Ask: Is this a quick preliminary analysis? In that case a vertical model may be the more efficient layout. Are you building an in-depth model with lots of deal-specific functionality which will require additional analyses and schedules? Then a horizontal model may be the better choice.

• There’s no single right choice: You’ll see merger models built by practitioners that follow each layout.

122



c. Choose a top-down or bottom-up approach for the income statement

BOTTOM UP TOP DOwn

Start with earnings per share (bottom of an income statement) and build up to pro forma net income and pro forma shares

Start with individual income statement line items (beginning with “Revenues” in the topmost line) and work down to pro forma net income and pro forma shares

123


How Do You Decide?

Choosing between bottom-up and top-down essentially comes down to how much detail you want to include, specifically:

• How much information you have to start with? A bottom-up model requires only the EPS projections; a top-down model by contrast requires full income statement disclosure.

• How much information you want to show at the end? A bottom-up model shows the impact of the transaction only on major line items such as pro forma net income, pro forma shares, and pro forma EPS. With a top-down model, you can show the impact to every line item.

Both approaches will get you to the same result, as long as the information and assumptions you use are consistent.

• The process we used in most of the pen-and-paper exercises in Chapter 2 was very similar to a bottom-up approach. But in Chapter 3, we’ll use the top-down approach for the model we’ll be building because we want additional disclosure.


NOTES

© 1999-2011 training the street, inc. all rights reserved. of instructor-led courses in financial...

Documents