Author Topic: Look through portfolio - Google Sheets with live prices  (Read 22778 times)

globalfinancepartners

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1453
Re: Look through portfolio - Google Sheets with live prices
« Reply #50 on: July 03, 2018, 06:51:36 AM »
You have the gist right on KHC.  It is not consolidated technically, it is accounted for under the equity method.  The effect is the same for your purposes though.  Berkshire's share of the earnings are already included without the need to 'look-through.'


Dynamic

  • Sr. Member
  • ****
  • Posts: 369
Re: Look through portfolio - Google Sheets with live prices
« Reply #51 on: July 03, 2018, 06:54:44 AM »
You have the gist right on KHC.  It is not consolidated technically, it is accounted for under the equity method.  The effect is the same for your purposes though.  Berkshire's share of the earnings are already included without the need to 'look-through.'

Thanks GFP, I was hoping you'd tell me if I had the essence correct or not.

Would I also be right to imagine that changes in KHC's market price would not carry over to Book Value except perhaps in the annual report where Berkshire might reassess its carrying value, as I think it did in the 2017 Annual Report?

globalfinancepartners

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1453
Re: Look through portfolio - Google Sheets with live prices
« Reply #52 on: July 03, 2018, 07:00:39 AM »
Correct, wiggles in KHC share price will not effect our carrying value unless they do another deal that involves BRK's ownership percentage changing.  BRK had to mark up their carrying value for KHC in connection with the Kraft merger because for GAAP purposes when your ownership goes from 52% to 26% or whatever the net result was, they treat it as if you sold a proportional piece of your position at the merger price (not for tax purposes obviously).

Wiggles in other stocks in the portfolio will now pass through the income statement, and always were reflected in carrying value (net of their deferred tax liability).

Dynamic

  • Sr. Member
  • ****
  • Posts: 369
Re: Look through portfolio - Google Sheets with live prices
« Reply #53 on: July 03, 2018, 08:02:15 AM »
Thanks for your response, GFP. I'm reassured that the look-through sheet is a better tool now than before these changes and the various others (such as accounting for the New England Asset Management and pension holdings) that you inspired earlier in this thread.

Dynamic

  • Sr. Member
  • ****
  • Posts: 369
Re: Look through portfolio - Google Sheets with live prices
« Reply #54 on: August 09, 2018, 12:52:20 AM »
A few minor changes just made updating Berkshire BV/share and share count. Nothing significant unless you want to monitor the current Price-to-Book Value ratio in cells X3 and X4.

The spreadsheet does NOT include the latest estimated Apple holding (up about 2% in share count) as the 13-F is soon due for release and it can be updated then to a more exact figure rather than mess with the established adjustment for pension scheme holdings. If you wish to account for changes to Apple and Wells Fargo, you can make a copy on your own Google Drive and easily add or subtract from the formula in the Adjustment column in tab "Combined Holdings".

This sheet also includes MON (Monsanto) with a estimate of the after-tax proceeds from its takeover. This cash and tax should be in Berkshire's quarterly financials so I'll remove the Monsanto proceeds when updating after the 13-F is released in a week or two.

Dynamic

  • Sr. Member
  • ****
  • Posts: 369
Re: Look through portfolio - Google Sheets with live prices
« Reply #55 on: August 10, 2018, 08:10:38 AM »
Berkshire appears to have increased its stake in AXALTA COATINGS SYSTEMS LTD. - SEC Filing Schedule 13G/A.

The beneficial ownership we count on as shareholders had been 20,940,000 shares and has increased by 940,000 to 21,880,000 shares.

There are also 2,384,000 shares for the benefit of various pensions which are controlled by Berkshire but not for their economic benefit. These pension holdings are unchanged except that Berkshire has consolidated some of its pension plans (three that held 700,000 shares between them before are now in the Berkshire Hathaway Consolidated Pension Plan, which now holds those 700,000)

Including these too, on the last 13-F it controlled 23,324,000 shares, now increased by 940,000 to 24,264,000, which is shown as 10.1% of the outstanding shares known on the filing date.

The event that resulted in this filing was 14 days ago (10 working days) - July 26th. I guess they paid around $30 per share judging by recent price action.

I will not adjust the Look-Through spreadsheet to include this until after the 13-F filing is released, and then I will link to that 13G/A filing in the worksheet 'COMBINED HOLDINGS'.
« Last Edit: August 10, 2018, 08:16:45 AM by Dynamic »

Dynamic

  • Sr. Member
  • ****
  • Posts: 369
Re: Look through portfolio - Google Sheets with live prices
« Reply #56 on: August 14, 2018, 06:28:19 PM »
Both sheets now updated for the latest 13-F filings for Berkshire Hathaway and Owner 01 02 in New England Asset Management (NEAM).

As always I net out holdings by pension funds to show only beneficially owned shares and I also make adjustments for known or previously declared foreign holdings believed to be held, but not required to be reported to SEC in 13-F. My holdings are thus a little different from most Berkshire portfolio updates you'll find online.

The Axalta holdings in the 13-F seem no different to the 13G/A referred to above, and the pension fund holdings in that 13G/A were unchanged in total number compared to the previous quarter.

As Berkshire's press release warned, some financial and airline stocks were trimmed to remain below 10% while other financials and airlines were added to during the same quarter.

The estimated Apple holding (based on the 10Q's holding value divided by the closing price) was correct to 3 significant figures and does represent an increase above the 5% threshold of voting power.

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

I removed the estimated after tax Monsanto buyout proceeds that I put in during June now that the share count is reduced to zero. The PSG holding is still there. I still include various eliminated positions on the Look-Through sheet, which come to zero.

There's a summary of the changes from quarter to quarter on the tab COMBINED HOLDINGS, columns D, E and F.

AAL: -5.1% of beneficially owned shares (-1,300,000 shares) presumably to stay below 10% voting power including large non-beneficial pension fund holding.

AAPL: +5.1% (+12,388,244 shares) taking ownership over 5%

AXTA: +4.5% (+940,000 shares) as disclosed in 13G/A (prev post)

BK: +4.8% (+2,608,928 shares) % assuming none of new stake owned by pension funds.

CHTR: -11.0% (-718,688 shares) % assuming none sold were pension holdings.

DAL: +18.8% (+10,066,483 shares)

GM: +3.1% (+1,393,611 shares)

GS: +20.1% (+2,294,971 shares)

JNJ: +8.7% (+28,832 shares)

LBTYA: +10.5% (+1,884,592 shares)

LUV: +18.6% (+8,887,943 shares)

MON (Monsanto): -100% (-18,970,134 shares) due to Bayer acquisition.

PSX: -27.7% (-10,960,378 shares) % assuming none sold by pension funds.

TEVA: +6.7% (+2,709,585 shares)

UAL: -3.9% (-1,021,421 shares) presumably to stay below 10% voting power including pension funds.

USB: +10.5% (11,273,496 shares)

VRSK: -14.2% (-458,692 shares)

WFC: -0.5% (-2,295,019 shares) to stay below 10% to avoid bank holding co status

No new positions. Only Monsanto eliminated this quarter. VRSK, while eliminated from BRK's own 13-F, was still present in NEAM 13-F [Edit: VRSK not AXTA as I previously stated in error].
« Last Edit: September 07, 2018, 04:30:47 AM by Dynamic »

Dynamic

  • Sr. Member
  • ****
  • Posts: 369
Re: Look through portfolio - Google Sheets with live prices
« Reply #57 on: September 27, 2018, 12:37:01 PM »
Just edited both versions to account for the sale of Philips 66 (PSX) announced on Sep 10th 13G/A filing.

I've added a line for the pre-tax proceeds of the sale, as this cash is available for investment but the actual post-tax proceeds will be reflected in the balance sheet for Q3 when release in November, and I'll remove it once the 2018-Q3 13-F is filed.

I assumed it raised about $117 per share on selling 12.5 million shares, raising around $1.467 billion pre-tax ($1.467 bn). Assuming the cost basis is still around $78.31 as it was at 2017 Q4, this gives about $484 million estimated capital gains that might have about a $97 million tax liability at 20% (about $7.74 capital gains tax per share sold). The $117 is essentially hard-coded in the price. The remaining stock is still shown at current market price. If you want to account for the estimated tax, you could hard-code the price as $109.26.

I'm including the gross proceeds in the portfolio until the end of the quarter just to aid in tracking its total value. The cash proceeds don't contribute to look-through earnings any more. In the quarterly financials, the cash balance and the tax liability will be updated to include the effects of the sale and I'll then remove the cash. This is much the same as happened during Q2 when Monsanto was acquired by Bayer for $128 per share.
« Last Edit: September 28, 2018, 01:01:35 AM by Dynamic »