Look through portfolio - Google Sheets with live prices

[Dynamic]

I've made a small adjustment to all three sheets.

 

I've made sure to use live EPS data from GoogleFinance for all shares, but then three showed N/A errors - LBTYK, LILAK, NASDAQ:LILA (new ticker required for LILA to play nice with Google Finance functions) because they don't have EPS data.

 

I've hard-coded an alternative very rough valuation metric for all three and shaded the cells in columns K:M and Q:AA pink to indicate this. These three stocks don't contribute to the look-through EPS figure, but they're very small holdings anyway (0.16% of portfolio by market value at present) so I'm not concerned.

[Dynamic]

There are a few new things to incorporate into the Look-Through Spreadsheet. They mostly fairly immaterial, but I'd still like to get them roughly right.

 

1. There's the 8-K from STOR June 23rd showing NICO's investment of 18,621,674 shares of the Company’s common stock, which I'll add to the 13-F LATEST worksheet as if it were on the last 13-F.

http://ir.storecapital.com/Cache/389226355.pdf?IID=4553160&FID=389226355&O=3&OSID=9

 

2. There are two SEC Form 4's from BRK on May 25th regarding various Liberty stock purchases.

https://www.sec.gov/Archives/edgar/data/315090/000120919117035615/xslF345X03/doc4.xml

https://www.sec.gov/Archives/edgar/data/315090/000120919117035614/xslF345X03/doc4.xml

In the first, regarding LSXMK, I think I should use 23,293,786 and ignore the 7,153,027 in various pension plans for which Berkshire and its subsidiaries have no beneficial ownership.

In the second, regarding LSXMA, I think I should use 10,174,586 (a tiny reduction compared to the last 13-F) and ignore 4,308,117 held in pension plans with no beneficial ownership.

 

The full number of shares including non-beneficial pension plan shares has been recorded (possibly in error) on CNBC's Portfolio Tracker http://www.cnbc.com/berkshire-hathaway-portfolio/ . If the pensions are defined benefit types, excess returns may accrue to BRK over time, but if they're money purchase types, the returns will accrue to the benefit of the employee beneficiaries of the pension. I'll assume the latter.

 

3. Speaking of the CNBC Portfolio Tracker, they made an estimate of the number of IBM shares held from Buffett's interview around the time of the Annual Meeting where he said about 30% had been sold, bringing their estimated share count to 56,862,612 versus the 13-F's 64,561,955. I haven't made any such adjustment, and its effect is far more material than the matters above, due to the size of the position (0.64% difference in total portfolio value).

 

While I'm at it, CNBC's doesn't account for the BAC Warrants or the Sanofi or BYD foreign holdings (the latter of which might actually be from the Hong Kong listing, rather than the Shanghai listing I used, although this is probably immaterial). And speaking of foreign holdings...

 

4. Berkshire now owns or will soon own 40,000,000 shares in Canadian Home Capital (HCG.CA) subject to shareholder approval of the private placement of 24,000,000 shares over and above the 16,000,000 common shares purchases, so I'm including these in the same way as BYD, for example.

http://www.cnbc.com/2017/06/22/home-capital-to-get-1-point-5-billion-loan-from-berkshire-hathaway.html

 

 

Nos 1,2 and 4, I will probably include as mentioned above.

No 3 is one I will leave alone until we get the next 13-F in July/August and find out how much IBM is still being held. The Look-Through Portfolio is always going to be out of date, so user beware!

[LongTermView]

Dynamic,

 

Thanks for putting this together!

 

Cell S7 shows $4.16 L-Thr EPS holdings per class B share through June 30th.

 

Cell AH4 shows $1.48 dividends per class B share last fiscal year.

 

Does that mean the retained earnings from stock holdings are about 2.8 times [$4.16/$1.48] more than the reported dividend earnings?

 

Thanks!

 

[Dynamic]

I don't have computer access until tomorrow and will not look into this on my smartphone in detail, but from my memory, I believe I entered the dividends received by BRK from its shareholdings in the last full year (from the Annual Report) so that I could lower the Earnings of BRK-operating-Company to exclude equity dividends and not double-count that form of income and only count income from operating activities and non equity investments such as treasury bills.

 

It would then be the case that these dividends would simply be part of the look through Earnings (meaning Earnings of the investee companies, whether distributed or not) which ought over time to accrue to Berkshire in terms of Total Return, meaning dividends plus capital appreciation.

 

I'll check your question again and may reply in my next post.

[Dynamic]

Dynamic,

 

Thanks for putting this together!

 

Cell S7 shows $4.16 L-Thr EPS holdings per class B share through June 30th.

 

Cell AH4 shows $1.48 dividends per class B share last fiscal year.

 

Does that mean the retained earnings from stock holdings are about 2.8 times [$4.16/$1.48] more than the reported dividend earnings?

 

Thanks!

 

Thanks for your comments, LongTermView

 

The Earnings of the investee companies includes anything they distribute as dividends.

 

The retained earnings would be = earnings - dividends - stock buybacks

 

If we can ignore the buybacks we have a ratio of

(4.16 - 1.48)/1.48

= 2.68 / 1.48

= 1.81

 

Expanding the top line you can write it as:

(4.16/1.48) - (1.48/1.48)

which simplifies to

(4.16/1.48) - 1

so my answer is always 1 less than your formula.

 

The original ratio you showed was Total Earnings / Dividends.

 

So you can say that for every $1.00 of investee dividends received at Berkshire Hathaway in 2016 there's about an additional $1.81 retained which should eventually accrue to Berkshire as a combination of capital appreciation and increased future dividends.

 

This means that for each $2.81 of total investee earnings, only $1.00 was received as dividends and reached Berkshire's GAAP earnings bottom line in 2016, while the remaining $1.81 was retained by the investees to fund future growth or buybacks which should increase the value of Berkshire's shareholding over the long term.

 

If you want to get more accurate you could look up the trailing 12 month dividends of the investees and apportion them to the look through number of shares held so that the timings are roughly within the same quarter instead of half a year out.

[LongTermView]

Dynamic,

 

I appreciate you replying with these details.

 

This part is key:

So you can say that for every $1.00 of investee dividends received at Berkshire Hathaway in 2016 there's about an additional $1.81 retained which should eventually accrue to Berkshire as a combination of capital appreciation and increased future dividends.

 

Of course we're talking about dividends from stocks like KO (as opposed to subsidiaries like BNSF) and we're not including things like interest on treasury bills. Where did you get the total of these KO type dividends on the 2016 annual report?

 

Thanks Again.

[Dynamic]

I looked at the Annual Report p84 and section 'Insurance—Investment Income' then used the figure for Dividends received which I sanity checked by calculating a yield against the equity portfolio valuation that seemed reasonable. That number goes in the first part of the formula in cell AH3 where the formula divides it by equivalent number of shares of BRK.A in circulation which is in cell AC6.

 

Might be worth me looking up the dividends paid by the investees representing 85-90% of the weighted portfolio value to verify it's about right or even the whole lot. It's a shame GoogleFinance functions don't include dividends.

 

I realise I had the 2015 figure originally and still needed to lower it just a fraction to match 2016's and should do so every February when going through all the blue cells. The 2016 figure is 3,552 in millions of dollars and had been 3,662 in 2015 so I was able to search the 2016ar.pdf for exactly that string of characters to find the number for the next year.

 

I'm crazy busy at work after a week off but hope to update the look through sheets soon with this figure.

 

There will be discrepancies if I include the BAC holding in the EPS but not the dividends as it had been warrants and preference shares previously. I haven't worked out how significant they would be, though these calculations aren't the main purpose of the spreadsheet.

[LongTermView]

Got it, thanks again.

 

So if we make some rough estimates/assumptions for 2016 then we can say the GAAP income of $3,552 million on page 84 is around $9,946 million in economic terms for a difference of about $6,394 million, right?

[SlowAppreciation]

Here's v1 of the Berkshire look-through earnings portfolio: http://minesafetydisclosures.com/individual-investor-portfolio

 

I will be doing a lot more to improve this over the coming months, but just wanted to share the progress so far for initial feedback.

[CassiusKing1]

Thanks for your continued work and sharing of this!

[Dynamic]

Very impressive work, SlowAppreciation!

[DW]

Completely agree; very impressive!

 

Very impressive work, SlowAppreciation!

[SlowAppreciation]

Thanks everyone. I'm taking suggestions/feedback too, so if there's anything additional you'd like to see, please let me know and I can see if it can be included.

[Dynamic]

Got it, thanks again.

 

So if we make some rough estimates/assumptions for 2016 then we can say the GAAP income of $3,552 million on page 84 is around $9,946 million in economic terms for a difference of about $6,394 million, right?

 

Just had a chance to look your figures over, LongTermView. I make it roughly $9,520 million of economic income from the investments -  a difference of roughly $5,970 million over the GAAP income. It would probably be better to judge in February when the Annual Report comes out, then the ratio (currently 2.82) will be nearer to matching that based on the look-through earnings of the portfolio held for the year included in the Annual Report, also taking account of adjusted holdings. It would also be a bit out if massive new positions were purchased for cash, say, half way through the year, as only half a year's dividends would be received, while a full year's look-through earnings would be counted.

 

Maybe one day I'll get round to using the YahooFinance import functions so I can also obtain dividend info that's missing from the GoogleFinance functions. This thread will probably help me do so: http://www.cornerofberkshireandfairfax.ca/forum/general-discussion/google-spreadsheet-tips-and-tricks/

 

Before I do that I hope to get time to include the latest 13-F in my spreadsheets.

[Dynamic]

The latest 13-F is now included in all three spreadsheets.

 

You can enter your currency and number of shares in the blue cells near the top-left of the first Worksheet tab.

 

I've also added a new tab to each spreadsheet called Look-Through Summary - which has a red stripe below the tab name.

 

This is a somewhat simplified view of the Look-Through portfolio. Rather than re-calculate, it just grabs most of the info from the main sheet, so don't go entering your holdings on this sheet and expect it to work. It adds on the right hand side a couple of columns showing the Look Through earnings of your holding of BRK and showing each constituent stock's percentage of these earnings (just as column J show's its percentage of the BRK portfolio at current prices)

 

I still include WABCO for now even though the position has now been sold and thus contains 0 shares.

 

This version is the one nobody but I can edit. It contains instructions for copying them to your own private spreadsheet so you can edit to your heart's content:

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837

 

 

This version is editable by anyone, but be aware that anyone can see your edit history, so if you enter private information there, it's potentially disclosed to the whole internet! It could also get corrupted by anyone!

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

 

There's another version that you may request permission to edit, here:

https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

 

 

If you'd like a simplified overview of the BRK portfolio, I can recommend http://minesafetydisclosures.com/individual-investor-portfolio and for CNBC's Look Through, https://www.cnbc.com/berkshire-hathaway-portfolio/

[Dynamic]

The latest 13-F as of 31st Dec 2017 is now included in all three spreadsheets. There are some doubts about figures assumed but not reportable in the 13-F (see below), but these might be resolved on 23rd Feb when the Annual Report comes out.

 

If you select a Google Sheets workbook you can edit (or copy it to a private Google Sheet workbook of your own using the instructions on my non-editable version) you can enter your currency and number of shares in the blue cells near the top-left of the first Worksheet tab and you can also set the currency of your choice.

 

By default, however, I've set the number of shares to represent one-millionth of all those outstanding, amounting to 2467.18 BRK.B equivalent shares at present and the currency to USD.

 

When cell C5 contains that number, 2467.18, you can read off the equivalent number of shares in each holding as millions of shares held by Berkshire Hathaway as a whole, and the values of those holdings as if they were in millions of US dollars (or whatever currency is set in cell B2). Cell I6 therefore would also represent Berkshire's Market Cap in millions.

 

If you prefer a simpler layout, click on the tab with the red stripe below its name, "Look-Through Summary". Columns A-J have been distilled down to basic info you might want (less useful columns such as my very rough Low End Valuation have been 'hidden from view' to simplify matters a little). However, if you want to change currency or number of shares you must enter these on the first sheet, entitled "Look-Through BRK earnings and holdings" (as the red text in cell A1 warns you)

 

I have removed WABCO as it was sold entirely in 2017Q3. Teva is now included.

 

This version is the one nobody but I can edit. It contains instructions for copying them to your own private spreadsheet so you can edit it to your heart's content in full privacy:

https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837

 

This version is editable by anyone, but be aware that anyone can see your edit history, so if you enter private information there, it's potentially disclosed to the whole internet! It could also get corrupted by anyone!

https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

 

There's another version that you may request permission from me to edit, here:

https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

 

If you'd like a simplified overview of the BRK portfolio, I can recommend http://minesafetydisclosures.com/individual-investor-portfolio and for CNBC's Look Through, https://www.cnbc.com/berkshire-hathaway-portfolio/

 

Discussion of discrepancies

The 13-F numbers and the totals agree with one posted on Seeking Alpha, so I'm pretty happy we both entered the information just fine. https://seekingalpha.com/article/4147016-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2017-update

 

The rocketfinancial version of BRK's portfolio seems to have additional stock in US Bancorp, which they seem to have taken from the 13-G filing. I think something odd is going on there as the 13-F is made up to the same date 31st Dec 2017. I love the way they present it (and the speed it was updated yesterday), and I'm sending them a support email to query it. http://www.rocketfinancial.com/Holdings.aspx?fID=1058

They might be correct to rely on the 13-G and the rest of us wrong. I'm not sure. Could the difference be amounts held in pension funds etc, not attributable to shareholders?

 

Phillips 66 (PSX) shows at 45,689,892 on CNBC. The rest of us have 80,689,892, so I think they're wrong there, https://www.cnbc.com/berkshire-hathaway-portfolio/ meaning they're about $3bn out. The rest matches just fine. I've just contacted them to let them know.

 

I only update cell AH3 when the annual report tells us the dividends received. This avoids double-counting that portion of look-through earnings that was already reported in GAAP earnings. At the moment this is a year out of date while I wait for the Annual Report.

 

The Annual Report is usually the only hint to non-US holdings such as BYD, Sanofi (SNW) on Euronext (which I enter as an equivalent number of Sanofi ADRs (SNY)), Società Cattolica di Assicurazione - Società Cooperativa and Home Capital Group (CANADA) - you'll note that these are highlighted differently on the sheets including 13-F LATEST.

[gfp]

The difference in USB, AAPL, etc, is probably in New England Asset management, under general re on Edgar- but i’m Away from the office and can’t check if that’s your issue at the moment

[gfp]

Yeah - I had a chance to look at your sheet and it looks like you are short the following share amounts, which are also owned by Berkshire but reported separately through New England Asset Management:

 

ticker, number of additional shares not included in BRK's 13F:

AAPL, 4,217,000

BAC, 21,000,000

DEO, 227,750

GS, 431,063

USG, 4,385,964

USB, 17,386,443

VRSK, 2,954,050

WFC, 24,312,200

 

 

It makes sense if you remember that BRK got 700 million BAC shares and hasn't sold any.  You are showing 679 million in your sheet.

 

Here is the most recent New England Asset Management filing - all the stocks with "Other Manager 01 02" are Berkshire's

 

https://www.sec.gov/Archives/edgar/data/1004244/000108514618000878/xslForm13F_X01/form13fInfoTable.xml

 

Phillips 66 (PSX) shows at 45,689,892 on CNBC. The rest of us have 80,689,892, so I think they're wrong there, https://www.cnbc.com/berkshire-hathaway-portfolio/ meaning they're about $3bn out. The rest matches just fine. I've just contacted them to let them know.

 

They aren't wrong.  They are reflecting the big sale that Berkshire just announced:

http://www.berkshirehathaway.com/news/feb1418.pdf

 

 

 

 

The difference in USB, AAPL, etc, is probably in New England Asset management, under general re on Edgar- but i’m Away from the office and can’t check if that’s your issue at the moment

[Dynamic]

Thanks, I've just seen the SEC filing come through on the PSX. I'll update my sheets when I'm on the computer to include the extra stocks via New England that you mention. Probably tomorrow.

 

If these are beneficially owned by BRK they belong in a Look Through analysis I guess. Does anyone disagree with this plan or the belief that they are to shareholders' benefit?

 

I'll probably include a separate New England 13F sheet and Berkshire 13F then combine those into aggregate holdings.

 

Thanks again for the clarifications.

[Dynamic]

OK, I've updated all three versions of the Spreadsheet.

 

These now include more stock holdings than most BRK portfolio summaries you'll find online:

 

1. Actual holdings reported in the Berkshire Hathaway Inc 13-F filing at the end of the most recent quarter.

2. Actual holdings belonging to Berkshire in the New England Asset Management Inc 13-F filing (Owner Code "01 02" in Column 7 only) - thank you to globalfinancepartners for steering me here.

3. Assumed holdings of non US securities that are not reportable to SEC but are mentioned in the Annual Report and presumed still to be held unless we hear otherwise.

4. Plus or (minus) acquisitions or (disposals) of securities announced since the last 13-F quarter. This quarter that's the 35,000,000 PSX shares bought back by PSX on 13th Feb 2018 to keep share count below 10% for regulatory reasons.

 

I may in future cease to maintain the editable version where people can request access soon. It seems sensible just to have two in future:

• my privately editable one (Read Only to you);
  
• the publicly editable version.
  

I believe I cannot just 'Make a Copy' and retain the existing URL I've published, so my copying process is more long-winded, hence the reduction to 2 versions.

 

The privately editable one is as I edited it. It's now very simple to create a Copy for your own private use by pulling down the File... menu and selecting Make A Copy... which will create a Copy of the whole workbook in your own Google Drive, where you will have Read/Write access.

 

The publicly editable version is definitely not private - so don't post private or confidential information on it even for a moment as even the Revision History is public. It may also get corrupted by anyone else on the internet, so it's less likely to be reliable than a copy of mine unless someone only fixes errors I've made!

 

As I said above, I will only update cell AH3 when the annual report tells us the dividends received. This avoids double-counting that portion of look-through earnings that was already reported in GAAP earnings. At the moment this is a year out of date while I wait for the Annual Report. I'm not 100% sure whether dividends received on the New England Asset Management holdings show up in Berkshire's GAAP Earnings, but I suspect they would - it makes accounting sense. If not, it may create a minor error in the operating company earnings adjusted to exclude dividends received to avoid this double-counting.

 

The Annual Report is usually the only hint to non-US holdings such as BYD, Sanofi (SNW) on Euronext (which I enter as an equivalent number of Sanofi ADRs (SNY)), Società Cattolica di Assicurazione - Società Cooperativa and Home Capital Group (CANADA) - you'll note that these are now included in a separate column in the tab 'COMBINED HOLDINGS' where I also included as a negative figure the sale of 35 million PSX shares announced since 31st Dec 2017, an adjustment that will need to be removed once the 2018q1 13-F filing is published in mid May 2018.

[gfp]

Just to update on this - the primary Berkshire 13F contains some shares held by pension funds.  The New England Asset Management listings are not the holdings of the pension funds, but some number of shares on BRK's 13F linked below are held by pension funds.

 

( https://www.sec.gov/Archives/edgar/data/1067983/000095012318002390/xslForm13F_X01/form13fInfoTable.xml )

 

Combining all Apple shares together form both 13Fs (as of 12/31/2017) gives a total of 169.55 million AAPL shares, where Berkshire's annual report shows 166.713209 million AAPL shares (which excludes pension fund holdings).  We know that Todd and Ted manage assets for the pension funds of the various subsidiaries, so the difference could be the shares 'owned' by Todd and/or Ted's accounts.

 

So it makes it difficult to get a 100% accurate spreadsheet going for Berkshire's look through holdings / real time book value.  But roughly right is good enough for analyzing BRK.  Thanks Dynamic, for sharing your work

[Dynamic]

Hi @globalfinancepartners. This is what is so great about CoBF - everyone noticing little improvements in accuracy.

 

A friend and I noticed the differences between the holdings attributable to shareholders and the 13-F at the weekend and I have already adjusted the top holdings (which are the most material) to the exact number of shares shown in the AR on a private Google Sheet that uses the Look Through earnings. There may be other holdings below the level of the top 15 or so that are over-reported. It might be possible to dig through the 13-G forms to ascertain which belong to pension funds (and sanity-check against known examples such as Apple)

 

I'm snowed under with work today but just realised I can change it very quickly as I put in an adjustment for Pension Funds in my Combined Holdings sheet (the one that combines the BRK 13-F, the NEAM 13-F and the non-US holdings), so I have done so on all three versions of the sheet.

 

Contrary to the Title, the column for 'Assumed non-US holdings' also now includes these adjustments for the pension fund holdings, which can hopefully be updated every February when the 10-K is released. I imagine we cannot expect more because the 13-F's purpose is in disclosing how much voting power is controlled by a person or entity, not for portfolio tracking.

 

I'm assuming that no mention of Sanofi in the AR means no change in their Euronext holdings. BYD was in the top holdings, confirming the number I've been using.

 

Incidentally the Combined Holdings sheet does include links, where applicable, to the news release or other source of information for the 'Assumed non-US holdings' in the rightmost column.

 

The PSX sale on 13th February 2018 to get below 10% is also included as an adjustment, but I'll need to remove that for next quarter's 13-F.

 

Here are the links to the two main versions of the Look-Through Google Sheet:

• my privately editable one (Read Only to you);
  
• the publicly editable version.
  

 

[Dynamic]

I'm just finishing up analysing the 13-F filings as of 31st March 2018 (filed 15th May 2018)for Berkshire Hathaway and will soon modify the Look Through Portfolio Google Sheets to include this.

 

As I've said before, 13-F is meant to disclose control/voting interest to the SEC, not to report economic interest in the companies. SC 13G and 13D filings are similarly about voting and dispositive power, not economic interest, but help reveal pension fund holdings.

 

My version differs deliberately from most of those you'll see on the web, as I am interested in approximately understanding the look-through economic interest we as Berkshire Hathaway shareholders have in the companies whose shares it owns.

 

Thanks to the valuable help of other forum members since about the end of 2017 my analysis includes holdings reported on the New England Asset Management 13-F filing where the OTHER OWNER is shown as "01 02" (meaning Berkshire).

 

This makes a significant adjustment in a few of the positions.

 

I have also used information I have gleaned from the Berkshire 10-K Annual Report and SC 13D or 13D/A or SC 13G or 13G/A filings (e.g. Berkshire's holding in Davita) to adjust for holdings whose economic benefit is attributable to pension funds for employees of Berkshire subsidiaries (in Davita's case, this was over half the holding controlled by Berkshire, for American Airlines, close to 40%). I usually link to these next to the adjustments so you can verify and make any queries on the forum.

 

R Ted Weschler has some apparent personal and shared holdings listed among the Liberty Sirius XM stocks which I think I didn't need to adjust for, but I might review this with some care shortly.

 

It will not be completely accurate as Berkshire only has to file stocks traded in the USA to the SEC in this way. I have made some adjustments for declared foreign holdings to the best of my knowledge, usually based on declarations of significant holdings in the Annual Report. Some of these are based on news reports of Berkshire investments and may be only approximately correct regarding holding sizes. Also some of the holdings of pension funds within Berkshire may not be adjusted for.

 

I have sanity-checked the assumed Apple holding against the 10Q, and it seems to match:

Used -2,837,753 adjustment at year-end to match page 9 of 2017 Annual Report figures (to exclude pension scheme assets). At 31/03/18 AAPL closing price was $167.78. Calculated market value using my adjusted figure was $40,676,730,846, which matches the $40.7 billion (rounded to $0.1bn) mentioned on p11 of 2018Q1 10Q, so I will not assume any change in pension scheme holdings unless I find them disclosed in SC 13G or G/A filings - I gone back to about 31 Dec 2015 with those filings.

 

A few comments about the new interpretation or quarterly changes to holdings (in ticker symbol order):

 

The holdings reported are those attributable to shareholders, not the total holdings controlled by Berkshire which may include pension fund holdings. The adjustment mentioned may be larger than the shareholders' holding (e.g. DVA)

 

AAL - holding unchanged at 25,258,000 but newly adjusted by -20,742,000 for pension fund holdings

AAPL - 242,440,880 for shareholders, 75,727,671 added (+46.2%) already adjusted for pension holdings

AXTA - unchanged 20,000,000 shares but newly adj for pension fund holdings

BK - 54,680,199 for shareholders, 1,372,665 added (+2.3%) assuming unchanged pension holdings

CHTR - 6,522,536 for shareholders, -266,518 reduce (-3.1%)

DAL - 53,599,357 add 488,962 (+0.9%)

DVA - 18,513,482 unchanged but -20,052,088 pension adjustment newly noted

IBM - eliminated -2,048,045 (-100.0%)

LBTYA - 17,906,408 reduce -2,274,489 (-11.3%) possibly to stay <10% of class

LILA - 1,625,185 unchanged but -1,089,669 pension adjustment is newly noted

LSXMA - 10,552,243 unchanged but -4,308,117 pension adjustment newly noted - R Ted Weschler's private holdings noted in form SC 13G but not 13-F, I think, so no adjustment needed

LSXMK - 23,942,958 unchanged but -7,148,027 pension adjustment newly noted - R Ted Weschler's private holdings noted in form SC 13G but not 13-F, I think, so no adjustment needed

MON - 18,970,134 add 7,261,387 (+62.0%)

PSX - 39,587,892 reduce -35,000,000 (-43.4%), assuming still -6,102,000 pension holdings adjustment

SNY - 44,134,997 assuming no sale of Euronext stock (non-reportable) reduce -177,512 (-4.6%)

UAL - 26,114,584 reduce -505,600 (-1.8%), assuming -1,591,379 adjustment for pension holdings unchanged

VRSK - 3,238,828 reduce -1,278,656 (-81.8%) - N.B, majority held by New England Asset Management

VRSN - unchanged at 7,905,481 but noted -5,047,264 pension holdings

WFC - 480,825,444 reduce -1,719,024 (-0.4%) probably to stay <10% avoiding bank holding co status

[Dynamic]

I've just updated the sheets with all the changes mentioned above.

 

I also note that it's now very easy to make your own copy of my spreadsheet - just use the File/Make a copy... command in Google Sheet, so I changed the instructions sheet - now much simpler!

 

There was a $128.00 per share cash takeover of Monsanto (MON) by Bayer on 7th June, closing out this presumed merger arbitrage position, worth almost $1 per BRK.B share in total. I've assumed close to $0.09 per BRK.B share after tax profit over the 6-9 months the position has been held.

 

I have hard-coded MON (Monsanto) to $125.56 market price. This reflects the estimated cash left after an estimated average purchase price of $116.38, and $11.72 USD pre-tax gain per share. Tax at 21% ~= $2.44/sh. Approx 7.9% after tax gain estimated in 6-9 months. In practice Berkshire may well have bought more MON stock this quarter too and the gain could have been a little more or a little less, but better to be roughly right etc.

 

I've also hard-coded the EPS for MON to essentially zero (I actually set it to 1e-18 USD to avoid #DIV/0 errors, which is essentially zero) as this position is mainly cash now, and will add to the cash balance at the end of this quarter, when the 13-F will be updated (in mid August).

 

Within 6 months, the USG deal for $44 per share acquisition by Knauf will close, too, but for now I'll leave it on the spreadsheet. That deal will be $0.50 per share as a special dividend plus $43.50 per share in cash. For now, I expect the stock price to track an estimated time-value discount to the takeover price plus dividend, remaining close to $43-44 per share.

 

The spreadsheet I'd advise you to Make a copy of for your own use is:

• Berkshire Hathaway Look Through Earnings & Holdings

 

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:

• Berkshire Look through earnings - Public editing allowed

 

 

[Dynamic]

I believe (please correct me if I'm wrong) that the Berkshire interest in Kraft Heinz Co. (KHC) is consolidated in Berkshire's earnings statement, so it would be double-counting to include its Look-Through earnings of almost $0.44 per year per Berkshire Class B share, in the Look-Through Portfolio EPS calculations (in column AH) as they're already included in Berkshire's GAAP EPS and Operating earnings (those being earnings before the mark-to-market adjustments that now must be carried into the income statement, but which Berkshire presents alongside the new GAAP figure for consistency and comparison with past performance).

 

I also presume that the mark-to-market 'earnings' from changes in the stock portfolio market value would specifically exclude the market-value change of KHC, this being considered a consolidated subsidiary entity.

 

You can find and copy the Google Sheets as before:

The spreadsheet I'd advise you to Make a copy of for your own use is:

• Berkshire Hathaway Look Through Earnings & Holdings

 

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:

• Berkshire Look through earnings - Public editing allowed

 

Caution: Note that EPS figures for portfolio components taken from Google Finance may include historic figures over the most recently filed 4 quarters with a mixture of US tax-rates assumed (e.g. old 35% federal rate and new 21% federal rate), so EPS shown may be materially below EPS by end of 2018.

 

The results include, at about 09:00 EDT on 3rd July:

Market Value of Look-Through Portfolio except KHC = $72.31 per BRK.B share.

EPS of Look-Through Portfolio = $3.7860 per BRK.B share (excluding KHC, which earned $0.4379 per BRK.B)

P/E ratio of portfolio = 72.31/3.7860 = 19.1. Earnings yield = 5.2%

 

Market Value of remainder of BRK.B (including cash) = $115.52 per BRK.B share (at 2nd July 2018 closing price of $187.83)

EPS less portfolio dividends for Operating part of Berkshire = $3.2229 per BRK.B share

Look-Through P/E ratio of Operating part = 115.52/3.2229 = 35.8. Earnings yield = 2.8%

 

From 2018Q1 10-Q p6:

Cash and cash equivalents and restricted cash at end of first quarter = $58,374mn

Therefore Cash per class B share = $58,374mn/2,467.6mn = $23.66 per BRK.B

 

Market Value of Operating part of BRK.B less cash per share = $91.86

P/E ratio of same = 28.5. Earnings yield of same = 3.5%

 

Total Look Through Earnings Per Share of BRK.B = $7.0089 per BRK.B share

Look-Through P/E ratio = $187.83 / $7.0089 = 26.8. Earnings yield = 3.7%

Look-Through P/E net of cash* = $164.17 / $7.0089 = 23.4. Earnings yield = 4.3%.

 

*This assumes cash is taken at face value. If cash is used to fund undervalued acquisitions or purchase undervalued stock, it may have higher ultimate value (optionality premium). If you are comparing Berkshire to alternative investments without 'cash drag' you might wish to discount its value.

 

There's also some additional cash included in the look-through sheet, such as the estimated after-tax proceeds of the Monsanto acquisition by Bayer, which will show up in the Q2 financials released in early August 2018. The USG acquisition will eventually add to the cash position also. Some of this, however, might have been invested in the mean time, for which we'll await the 13-F filings later in August.

 

I'd anticipate the Look-Through EPS and Book Value Per Share are both likely to compound at around 10% cagr (8-12% range) in the long term (6-9% above inflation) regardless of the cash build up at this point in the cycle, but your mileage may vary.

 

 

Miscellaneous Adjustments to the Spreadsheet - feel free to ignore these

I've altered a few of the columns around columns AE and AF.

 

Column AE is now for EPS including KHC, and AF is now the EPS excluding KHC column.

 

The green shaded columns AH and AI are those I consider 'correct' for calculating look-through earnings as they exclude KHC. The sheet carries through the totals of these columns at the foot of the page into the Look Through Market Value (copied to cell D7) and the Look Through EPS of the portfolio in cell S7. The Total EPS in S9 is the sum of this figure plus the operating earnings of Berkshire (including its share of KHC), less the dividends received from the stock portfolio (these dividends, shown in the financial statements should not include dividends from KHC as these are considered distributions from a consolidated subsidiary, I believe, so I don't need to adjust further)

 

The blue text on a pink (light red) background includes KHC. You might want to know these figures for some specific purposes of your own, but as KHC is consolidated, they don't now find their way into cells D7 or S7.

 

I also made a few improvements to the robustness of the sheet possibly to do with the update going on with Google Finance, which sometimes throws up error messages that it used not to, and which may also speed up the calculations by reducing unnecessary GOOGLEFINANCE() queries:

 

I fixed the currency for Monsanto (no longer trading since Bayer takeover) to USD as their debt in Canada is now showing under symbol MON and reporting the currency as CAD instead. After the 13-F and 10-Q in August, this will become irrelevant as the after-tax cash proceeds of the takeover will be reflected in the financial statements.

 

I've also made the sheet a bit more resilient to changing functionality of the GOOGLEFINANCE() function which I have reported but which hasn't been permanently fixed - I guess it depends whether their data provider at any moment returns a value or not in some edge cases.

 

Where both currencies were the same, e.g. CURRENCY:USDUSD, it used to return 1.0, but now returns #N/A error sometimes, meaning any dependent cells also show #N/A error. I now check if they match and return 1.0 if they do.

 

On my personal sheets, I sometimes track UK shares priced in British Pennies GBX but want to convert to British Pounds GBP, my home currency, so on those sheet I also check for that and return 0.01 to fix a similar sporadic bug that covers my sheet in error messages.