All the version of the spreadsheet are up-to-date with the latest figures and I've got my head around all the Liberties and their tickers. I've also made some substantial changes to how I handle odd cases, like Sanofi and Bank of America Warrants.
• The one only I can edit:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing• The one only approved users can edit (click the Share tab to request permission from me):
https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s• The one anyone on the internet can edit without signing in:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharingAgain, please remember that if you edit the public versions, your data can be seen by anyone on the internet (including the use of File/See Revision History to revive previous versions), so keep your privacy in mind.
If you wish to copy both sheets to your own private Google Sheet, start with the 13-F worksheet tab and hit Copy to... from the menu on the tab, then rename it to remove the added words "Copy of ", the copy the Look-Through worksheet which picks up data from the 13-F worksheet, hence the need to rename it to match the name used in each such formula).
Here's the full procedure to copy it:- Open Google Drive or http://drive.google.com in the usual way.
- Go to or create the folder you want then press NEW and select Google Sheet.
- Your Untitled Spreadsheet will open. You can click in the title to change its name.
- Open my view only Sheet at https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing
- Once it has fully loaded, go to the tab marker at the bottom called "13-F 2017-05-15" and click on the light downward arrow on the right of its name
- Click on the option, "Copy to..."
- Select your newly created Google Sheet from step 3 above (e.g. you can find it in Recent) and hit OK.
- It will then say worksheet copied and offer a link "Do you want to open the target Google Sheet?". Say Yes and it will open your Google Sheet.
- At the foot of the new tab called "Copy of 13-F 2017-05-15" click the arrow then "Rename" and delete the words "Copy of " including the space at the end.
- If you return to my sheet, go to the tab marker at the bottom called "Look-Through BRK earnings and holdings" and click on the light downward arrow on the right of its name and again click Copy To... and select your new Google Sheet. There's no need to rename this sheet if you don't want to.
- Close my version and feel free to edit your own in private. If you adjust the blue shaded cells to your preferred currency and holdings, that will cover most use cases.
While going through the 13-F compared to the previous one I noticed a couple of things:
• IBM - was listed in various ownership codes on the previous 13-F but is now all under code 4,11 on a single line
• A couple of other holdings under various codes seem to have moved around including Wells Fargo (WFC).
Could these changes indicate tax-efficient selling and/or buying in certain divisions of Berkshire?
Some items are not in the 13-F but I've made my own entries on my 13-F worksheet in gray background, and I'll summarise them as I've changed how I handle them:
1.
BYD (China), as before is included on the assumption it is still held at the same position size.
2.
Sanofi ADR. The 2016 Annual Report p19 (Chairman's Letter) mentions the total holdings including both ADR (ticker SNW) and direct shares traded on Paris Euronext. The latter are not reportable on 13-F, but I've put the equivalent number of ADRs (2 ADRs = 1 share of SNY) on the assumption they're still held. I sanity-checked this and the holding is about 1.76% of the market cap, which is about the 1.7% shown in the AR.
3.
Bank of America warrants being included in effect as if it were BAC exposure.
Berkshire holds preferred stock with 700 million warrants to purchase BAC common stock at $7.14 until mid 2021.
I'm including these in the look-through effective exposure by using ticker BAC but adjusting the number of shares as if this was a cashless exercise at the current price.
e.g. at $23.99 for BAC, the warrant is worth $23.99 - $7.14 = $16.85
700 million of these amount to $11.795 billion.
That will buy 491,663,193 shares of BAC, so that's the number of shares I put in the look-through portfolio.
In fact, I run this calculation to adjust the effective number of BAC shares ‘live’ to give some sort of indication of the effective exposure and total value of the warrant.
The formula for the effective number of BAC shares I've used is:
No of shares = 700 million x (BACprice - $7.14) / BACprice
Thus, it reflects the current value of the warrants reasonably well, and gives at least some indication of the exposure to BAC - so if you held BAC yourself, you could work out the additional effective exposure via BRK approximately, although you'll be aware that it will increase in 2021 unless the warrants expire worthless.
In reality Berkshire will exchange $5 billion of cash from the operating company to exercise the warrants before they expire, but this will reduce the cash held and reduce the value of the operating component, which I’ve called BH.Own on line 8 of the spreadsheet, by $5 billion while the portfolio BH.Portf will increase in value.
