Excel data table for sensitivity analysis

I've used Microsoft Excel for so long that it has become kind of like another appendage at work. It is one of the most powerful tools you can learn to use to increase your productivity in an office. I played a lot of video games growing up, and in video game terms increasing your Excel skill is kind of like adding experience points and making your character more powerful. The better you know how to use excel, the more you can do in less time, especially if you're in finance/accounting/marketing/other numbers-oriented fields (and these days with rise of big data, almost every field is now numbers oriented). Every day I learn a new function, re-learn how to use an old one that I haven't tried in a while, or discover a new way to do something quicker.

One powerful tool I use often enough to have memorized how to use it is the data table function. Let me put its usefulness in context...

So you've created a working DCF model that values a company and you've assumed a discount rate and a revenue growth rate as part of this. Your boss/portfolio manager/wife/self looks at it and says "ok, but a DCF is just a tool to estimate value. You're giving me an precise number. How sensitive is that number to the discount rate you chose? By the way I don't believe the revenue growth rate you chose- what would the value look like if you dropped it by 5%?"

You go back to your spreadsheet and build a table. You spend the next hour methodically changing the discount rate input, and the earnings rate input, and noting the results. You come up with a whole range of values. You format them nicely, print it out and show your boss/portfolio manager/wife/self. He/she says "great, this is what I was looking for. I'm not confident the revenue growth will be as high as it needs to be to make a good return buying the stock at its current price so I'm not sure what to do here. How about you change the margin assumption in your model and show me what it does to this sensitivity table?"

You sigh. It's six o'clock. It will take another hour to do this, but you go back and make the change to your margin assumption. You then slog through, methodically changing the discount rate and the revenue growth rate and noting the results. You think about that beer waiting for you at home. Your mind wanders and you wonder if Excel has a way to automatically create a data table that will be sitting there, ready to update at a second's notice if you change any part of your model. One that will vary the assumptions for you behind the scenes and print out the results for you... 

You fire up the google. Your hard drive and RAM are jolted with electrical impulses. The google algorithm does your bidding. It sorts, it seeks. Your ISP brings you data at your maximum download speeds, your monitor renders bits and bytes into pleasing words and pictures.... and one second later you get search results. Pages upon pages of search results, all of them pointing you to the data table function. You fire up youtube, watch a tutorial, and seconds later you have a dynamically linked sensitivity table ready to do your bidding at a moment's notice. It takes a bit of time to get it right- some videos only use a single variable analysis, you keep getting the row and column inputs confused.... but it is time well spent. Learning how to use a data table just shaved an hour off of every future DCF you do. It made you more efficient. It made you better at your job. 

Anyway, if you haven't learned this yet, I strongly suggest you check out how to create a data table in Excel. Yes it is a bit complicated at first, but it gets much, much easier. 

As a side note, I've found the best way to learn how to do things in excel is with actual live problems. Something that you need to deliver and that would be mind-numbingly tedious without an excel trick. 

I suck at using blogger for anything but text, so as of now I'm not able to give you an effective tutorial on how to use data analysis (apologies to anyone who came here looking for this). What I can say is that there are hundreds of demos just waiting for you on google. I tend to prefer a youtube demo because you can see how people do things live, which helps you understand what you may be doing wrong when you mess up. 

I just did a quick search and thought this demo was pretty good.

Have fun.

-Bernanke

As many advanced googlers know, if you type in a search term with a minus sign before it, your search will not return results with that word in it. I was just sitting here doing a thought experiment- what if I spent the next few weeks only reading financial news stories/research/information that do not contain the word "bernanke." Better yet, what if I could just run the big "minus" filter on a host of terms including "qe," "fed," "gold," and also "bubble" for good measure.

I probably wouldn't be able to visit zerohedge anymore, which would be a bummer because that is one of my favorite sites.

I know this whole thing is in uncharted waters currently, but Every now and then don't you get bernanke fatigue? Fiat fatigue? Maybe it is just a part of getting older. Maybe my father was in this position 30 years ago when he was looking at the economic future of the country and hearing doubts about where it was going to be in 30 years. Maybe you can always find things to worry about.. Maybe my father had Volker fatigue in 1983. Maybe things have never looked good to anyone and all of us current market participants are just in the same position our fathers were in. Maybe it is time to stop worrying about it so much.

I know you can respond with umpteen stats about how this time it's different. How the gap between the rich and the poor has steadily increased, and how incomes have come down (or if you want to make the argument they've gone up in aggregate). Don't bother making them to me though. At this point I tend to just nod my head when I hear someone's prediction about where the economy and the country are headed.

I'll continue to read about and follow it all because that's the way I'm wired, but boy a vacation would be nice. 

Thoughts on 3G Capital's LBO of Heinz or "Warrant" Buffett Strikes Again

On Feb 14 (last Thursday), Berkshire and 3G Capital announced that they agreed to acquire food company H.J. Heinz Co. for $23 billion in cash ($72.50 per share, a 20% premium to where the stock closed the day before).

Well, that's what the headlines would have you believe anyway. However, that is not what is happening. My headline is a bit more accurate. I'll explain below.

Initial read

I was pretty busy last Thursday so I didn't pay a whole lot of attention to the acquisition, but as may you know I'm a big Buffett buff and I made a mental note to take a closer look at the numbers at some point in the future. I like to check in on what smart people are valuing businesses at every now and then so I have a good market reference point in my head.

My initial assumption based on the headlines alone was that the deal made sense- Buffett loves these big, high quality brand name companies and Heinz seems to make sense as a piece of the portfolio alongside Wrigley, Coke, and Gillette. I also know Buffett likes to pay a reasonable for a business (read basically any book about him and you'll see some reference to the "margin of safety" concept he learned from Ben Graham) so I assumed that he got a good deal. I haven't really followed Heinz, so I thought that maybe the stock had been neglected and possibly didn't take part in the recent market rally.

Today I revisited the story, pulled open Heinz's last 10-K and realized that I was completely wrong. This was not an old-style Warren Buffett margin of safety "be greedy when others are fearful" acquisition of a great business at a substantial discount to intrinsic value. This was much more what I've come to think of as a "new style" Warren Buffett where he gets to put capital to use at rates no mere mortal can obtain. The price paid for Heinz was not a bargain from what I can see.

The price tag was high - 25x earnings!

Looking at Heinz's 2012 10-K (see page 33 for the income statement), the company earned about $939 million of net income for the year ended April 2012. I opened another couple of 10-Ks to look at the five year history, and net income averaged a bit below $939 million for this period, so I figured it was a pretty good number. Divide the purchase price of $23 billion by $939 million and you'll see the Buffett/3G team paid about 25 times trailing earnings for the company, not a low multiple by any stretch of the imagination. For the sake of comparision, Google sells for a similar multiple, is trading at an all time high, and though I'm getting out of my league here, I think it is considered more of a growth stock. Heinz does not make technology, it makes food products.

Going a few pages further in the 10-K, I figured I'd get a rough sense of what the company's free cash flow is. Take net income of $939 million, add back $300 million of depreciation, $50 million of amortization, deduct CAPEX of $400 million and you're at roughly $900 million of free cash flow. If you pay $23 billion for the company, $900 million of free cash flow equates to roughly a 4% yield. The multiple is still about the same, roughly 25x FCF. I also checked how FCF looked over the past five or six years, and again the average was below the current $900 million number.

I didn't create a DCF model of the company because I'd seen enough at this point and I didn't have the time to put into it, but I think if you do a DCF with some reasonable assumptions, you're not going to get to a $23 billion valuation for the company assuming things continue along as they have in the past.

I believe I read in the press that the price was something along the lines of 8x book value and 14x EBITDA, again generally high multiples (though book value isn't the greatest metric for a company like this).

Berkshire didn't buy the company, it bought half of the equity in the deal, plus high-yielding preferred and warrants as a kicker

Reading past the headlines of the articles, I realized that Berkshire's investment in the company wasn't purely an equity stake (like Buffett's investments in Coke, Gilette, Washington Post etc. that he became famous for). Instead, Berkshire is going to pay $8 billion for preferred stock in Heinz yielding 9%, and invest $4 billion of equity.

In addition, 3G is only investing $4 billion of equity and financing the rest. The company is also going to roll its current $5 billion of debt.

So doing some rough math, when the deal is complete, the capital structure will be something like:

$8 billion of equity
$8 billion of preferred stock
$12 billion of debt ($5 billion existing plus ~7 billion of new debt hence the "LBO")

Oh, and if you dig around, Buffett is also getting warrants to buy shares of the company. ("Warrant" Buffett also has Bank of America warrants, had Goldman warrants, and GE warrants). Terms of these warrants weren't disclosed.

Anyway the upshot of all of this is that if the deal is approved, Heinz will become a private company 50% owned by a PE firm with a history of cost cutting. It will have twice the debt load it had previously and its debt will be downgraded by the rating agencies, but as the LBO story goes, should be able to service the debt over time with steady cash flows thrown off by the business. It will enjoy levered returns for a few years, and then 3G will likely look for an exit, possibly selling its 50% stake to Berkshire. The company may be more profitable at that time.

In the meantime, Berkshire rakes in the 9% dividends on the preferred stock. Don't forget that preferred stock dividends enjoy a very favorable tax deduction for corporate owners, so Berkshire also gets to avoid some taxes it would have been hit by had it acquired Heinz outright.

And those warrants. Berkshire can maybe exercise those warrants someday.

Berkshire's price tag - more like 18x earnings, with upside

My final thought- Berkshire's earnings stream from the company will be as follows:

-$8 billion of preferred stock at 9% yield for $720 million a year in pretax preferred dividends
-Since generally 70% of preferred stock dividends are deductible for corporations, the effective tax rate on these dividends will be approximately 10%, for after-tax preferred dividends of about $650 million.
-Plus, Berkshire's 50% share of the company's earnings. This is a harder number to take a guess at but I'll do some extremely rough late-night math. $939 million of earnings in 2012. Subtract preferred dividends of $720 million and this leaves you with about $220 million of earnings. 2012 earnings include about $300 million of pretax interest expense. Since the debt load of the company is going to roughly double, lets assume interest expense doubles, to $600 million (since the rating will fall to junk, the rate on new debt will likely be higher and the cost of rolling old debt will be higher but im not going to get too precise here). Tax effecting the additional $300 million of interest expense at 30%, you get about a $210 million hit to after tax earnings, reducing the $220 million to $10 million of after-tax earnings. Buffett gets the right to half of that, roughly $5 million
-$650+5 = $655 million of after tax earnings per year
-Buffett invested $12 billion of cash
-This results in a P/E multiple of more like 18x earnings. Better than 25, but still not cheap.

Let me know if I missed something. It's late.