Look through portfolio - Google Sheets with live prices

[Dynamic]

View-only example look through portfolio
(set to GBP currency - only I can edit this but I think you can select and copy the cells into your own Google Drive spreadsheet):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

Publicly editable version of look-through portfolio:
(might get corrupted by someone else's edits, but you can have a play with it and try changing currency and number of shares held):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

At time of posting this is updated to 30th June 2016 holdings as declared in 15th August 2016 13-F and the shares oustanding at the same date from the 10Q.

Commentary:

I track our total personal portfolio and Watchlist using Google Sheets in Google Drive (the GOOGLEFINANCE function is very useful for currency conversion and delayed prices and basic financials such as EPS). I also use various tabs to track our investment ledgers and our monthly and annual deposits into our tax-advantaged investment accounts, and to keep an eye on our pension schemes.

I wanted to make a look-through total portfolio, partly to gauge our total exposure to companies we hold that are also held by Berkshire Hathaway, such as Wells Fargo, where our look-through stake is about half of our direct stake because we're fairly heavily into BRK.

As a by-product, I've created a Google Sheet which you might wish to look at, borrow from or select-All and Copy into your own Google Sheet on Google Drive. If you paste into Excel or LibreOffice Calc you'll probably lose the GoogleFinance functionality.

The link below is my personal version (only I can edit it, but anyone can view it and I believe could copy and paste the cells to another sheet they can edit):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

The link below this is publicly editable by anyone (so you can play with it, collaborate on it or paste the contents of the other sheet into it):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

If you enter in the blue cells your number of BRK class A shares and class B shares and your native currency (e.g. GBP, EUR, USD, CAD etc.) it converts it into equivalent B shares and it estimates look-through holding values from row 8 down, and in columns way over to the right, it includes the EPS reported by GoogleFinance. Some shares have an Error (N/A) so I set these to zero in totalling EPS per BRK.B, but these are mostly minor holdings, so the overall look-through EPS at the bottom right is about right.

As a crude indication, as for my full portfolio, I also include Low, Typical and High estimates of trading range based on some kind of fundamental and multiples of it. This also sets a colour scale to crudely indicate if the current market price is Low (DARK GREEN), Typical (BRIGHT YELLOW) or High (DARK ORANGE/BROWN). In my full portfolio I sometime use this colour scale to easily whittle down my list of candidates to sell at toppy prices in order to buy something else I think is cheap, and I use the Low/Typ/High idea to project long term retirement portfolio value without so much influence of current market-wide depression/euphoria.

For BRK this fundamental is Book Value Per Share. For most others it's EPS reported by GoogleFinance. Where EPS is negative or N/A I've estimated some other value such as normalised EPS - using very little effort of analysis!!!

Do your own research etc. and don't simply rely on the spreadsheet to make investment decisions!

Soon I expect to summarise the key figures near the top and the left of the sheet and perhaps make the look-through list filtered/sortable.

[Dynamic]

I've made a couple of changes to both spreadsheets.

I've inserted rows 7, 8 and 9 on the spreadsheet.

Row 7 splits out the equity holdings per BRK.B share. It tells you how much one share of BRK.B contains in current Market Price of equity holdings.
(Market Price is about $53.66 as I type this, with effective EPS of $3.05 per B share, P/E = 17.57 or E/P yield of 5.69% from column W). It also values your total holding in your native currency.

Row 8 splits out the operating side of Berkshire Hathaway only (it does not back out cash equivalent holdings, however). It shows the operating earnings excluding dividends (as per the annual report for now, not per the 10Q).
(Market Price is about $94.41 as I type this, with effective EPS of $8.86 per B share, P/E = 10.66 or E/P yield of 9.38% from column W). It also values your total holding in your native currency.

Row 9 is the sum of the two rows above - the total Look-Through BRK.B share, with total look-through EPS in column S.
(Market Price is the same as BRK.B = $148.07 as I type, Look-Through EPS = $11.91, P/E = 12.43 or E/P yield of 8.04%)

You can use this in a few ways to approach valuation, according to your tastes, such as:

• Apply an earnings yield or P/E multiplier to Total Look-Through EPS - see cell S9 then multiply by a fair P/E or divide by a fair earnings yield.
• Back out cash and equivalents per B share then do the above to value the non-cash portion.
• Apply a sum-of-the-parts valuation to both the portfolio's look-through earnings per B share (so you free yourself from it's current market price) and to the operating company's look-through earnings per B share.
• Make a valuation of each significant holding individually and to each division of Berkshire's operating business.
• Value various business units within Berkshire Hathaway separately and combine them. E.G. Insurance, Utilities, Railways, Retail and Consumer, Financial Products, etc. to value the operating portion, then add in the shares.
• Use whatever crude metrics you normally employ to roughly gauge the relative attractiveness of different stocks. For example, my crude Low, Typical, High measure adjusted using columns Y, Z and AA.

[Dynamic]

For those who want to play or to enter private information, like your actual BRK holdings, you can copy the "View Only" version of the Google Sheet to a Google Sheet of your own.

• 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 "Look-Through BRK earnings and holdings" and click on the light downward arrow on the right of its name
• You will have only one option, namely "Copy". Click it.
• Select your newly created Google Sheet from step 3 above 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.
• It will probably be open on the blank tab you created it with, but at the bottom of your window you can click the arrow to delete that tab and leave the new tab called "Copy of Look-Through BRK earnings and holdings" which you can now edit to your heart's content.

I've tested this on someone else's Google Chrome and Google Account and it works fine. Feel free to share your own versions on this thread or to copy them to a new sheet you don't mind sharing publicly.

BTW, if you ask me for permission to edit my "View Only" version of the sheet, I'll allow you Comment access, but not Edit access, so it won't get corrupted.

If you want Edit access in the usual collaborative way, there's now a third version of my sheet to which you can request full access. Find it at: https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing
To request Edit Access, make sure you're signed into your Google account then click the blue View Only button to request edit access from me.

I've granted edit access for that to the two people who requested it on my View Only version.

Perhaps this version can be one that members of CBF forums can work on together when new financials come out. Whoever wants to can input, say, the Book Value per share, the shares outstanding (find these over to the right on the top rows) and the dividend income received from Berkshire's portfolio so that the right figures flow through. Post here or comment on the Google Sheet to say what you've updated.

Also, various minor holdings have no valid EPS figure so I just took Wild guesses at normalised EPS for the purposes only of the Low, Typical, High crude valuation of each stock. In some cases, a multiple of Book Value of other figure might be a better valuation yardstick for a specific stock, so feel free to edit columns Q to AA on any rows you like.

Do bear in mind that whatever you type there can be seen by anyone on the internet as can the edit history of the spreadsheet!!! Don't put anything proprietary or personally identifiable.

[physdude]

Thanks for the useful spreadsheet! Really appreciate your effort.

[arcube]

Well done. Thank you for sharing. Much appreciated.

Thanks for the useful spreadsheet! Really appreciate your effort.

[Dynamic]

All versions of the look-through spreadsheet are now updated to include the holding in BYD Company Limited which had been omitted from the original version. This holding was purchased by MidAmerican Energy which was since renamed to Berkshire Hathaway Energy.

The version only I can edit (but you can copy it into your own Google Sheet - see above):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

The version anyone on the internet can see and edit (so it could get corrupted, but you can enter your own currency and number of shares):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

Restricted editing - for this version you can request Edit Access and I'll grant it. It sends me an email from your Google account:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

In all case, anyone on the internet can view them so be wary of entering private information. If you want a private copy, copy the sheet to your own private Google Sheet using the instructions in post 3 of this thread at http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg272206/#msg272206

[Dynamic]

All three versions now updated to include increased holding in Phillips 66 (PSX) as at 15 Sep 2016

[Dynamic]

I noticed an error in converting the Market Values and EPS of foreign holdings like BYD into USD, so the Total Market Value of the Look-Through Holdings was about $4 per B-share too high, and thus the implied market valuation of the operating part of BRK.B was about $4 too low.

I've now corrected this on all versions.

[Dynamic]

Hi,

Slightly delayed by a short vacation in the sun, I've updated the Spreadsheets above based on the latest 13-F and updated the Book Value based on the 30th Sep 2016 10-Q (I'd calculated a slightly different value of BVPS myself than Yahoo Finance shows, so I switched to theirs, which differs in about the 4th significant figure).

I'm glad to see from the revision history and changes that a few people have played around with the numbers of shares and native currency in the Publicly editable version at:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing
...so I hope it remains useful to some of you. By copying over my new version and deleting the old one, I've undone the most recent change by a member of the public.

And don't forget there are instructions earlier in this thread to copy the latest sheet to your own Google Drive spreadsheets. If you copy it, use the version only I can edit:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

Assumptions made include that the BYD holding remains unchanged since its original report. I assume that as a foreign holding it doesn't get included in the 13-F but is still held.

I note that three airlines have been added to the portfolio - AAL, DAL, UAL
and that two companies seem to have been removed - MEG, SU

I usually make a note of my latest update in the green cell Q6, which now reads:
Latest major change: 2016-11-19. 13-F on 15 Sep 16. Add AAL, DAL, UAL. Remove MEG, SU. BV as at 30 Sep

Various other positions have changed size, such as a reduction in Walmart and various Liberty Media adjustments.

You can compare the previous version of the spreadsheet with the current one as I retained the old version as a separate tab in the Resticted Editing version:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

[scorpioncapital]

Just wan't to understand something, is the look through earning per share of 38,58,96/share embodied in the investments per share part of the 2 column method so that the difference between this calculation and that one is the implied under or overvaluation of the market value of the investment portfolio?