TLDR
This article, which is a cooperation between Alex and Yazad Sethna, an Investment Associate at Threecolts, explains how to create a custom dataset of publicly listed comparables, or “comps”. From scratch and for free. We also share a ready-to-use template. Read on!
How to lose $100 million
A decade ago, Alex was an Associate at an investment bank overseeing a team of juniors doing things like building DCF models, compiling pitch decks – and updating comps. Expensive plug-ins into Factset or Bloomberg were used to keep data up to date, but they were distrusted and analysts had to manually recalculate most inputs. It was absurd that we were paying exorbitant amounts for a simple share price feed!
For junior resource companies, traditional metrics like price to cash flow were irrelevant. Instead, we compared prices to net asset values (NAVs), which accounted for existing and future production. However, even as Bloomberg caught up, NAVs were still manually updated from broker reports 🤯
The penny dropped in one particularly drawn-out, challenging transaction. The market kept dropping and investors kept questioning our comparables analysis. Turns out, they were right. Peer group share prices in our model – the one column that was supposed to be automated – weren’t properly updating. The analysis was running off share prices from four months ago, when the market was much higher. After rectifying the error, the valuation dropped by $100 million.
We have good news for you
What are the lessons from the snafu? Automate more to free up the time to do quality control. The good news is that you no longer need an expensive Bloomberg plugin or a friendly banker to maintain a decent comps set. A combination of Google Sheets and free to use third party websites that track analyst estimates can do wonders.
Google Sheets for the win
You’ll need to build two sets of formulas:
- Formula set #1. Data that will be sourced from Google itself e.g. share price, share turnover, FX rates, market cap. You can use formulas like =googlefinance([cell], “price”) where [cell] is the cell reference containing the ticker – e.g. TSE:CSU for Constellation Software Inc. See here for the formula structure, syntax, and data inputs available.
- Formula set #2. Data that will be sourced from third party websites, through scraping. I’m talking about broker consensus estimates for revenue, EBITDA, net income etc., as well as net debt. I personally prefer MarketScreener. The UX is a bit eclectic, but it has all the data you need. There’s a limit on free usage. Yahoo!Finance and Wall Street Journal are other sources to consider
Here’s a step-by-step guide for Formula set #2:
- First, paste the link to the page containing the consensus estimates. Link for CSU
- Then, use this formula to export a specific table from the page: =IMPORTHTML([cell], “table”, N), where [cell] is the cell reference containing the above URL. N is the sequential number of the table you’re looking to import
- Finally, for net debt proxy, you can take the difference between Capitalization and Enterprise Value
For your convenience, we’ve set up a template here.
You’re good to go!
Words of caution
Make sure you understand the limitations:
- Financial years aren’t aligned. CSU’s financial year ends in December whereas Vitec’s in June. You may want to calendarize everything to December year-end
- Net debt calculation can be tricky. You’re relying on someone else to have done the homework – things like leases, deferred revenue liability etc. You may want to double check their work!
- Sense-check the output. Benchmark. Invest in researching individual companies so that you understand how their strategies translate into financial metrics – and ultimately why they trade the way they do
- Changes to the underlying HTML table position. Following the formula above, the ‘N’ input for the table position on the underlying web page may shift. This rarely happens, but something to note if your data is not following through correctly. One can simply adjust the ‘N’ input to correct.
- Making the imported data readable by Google Sheets. As the data is imported in the format of the website, you may face a scenario in which the numbers are unreadable by Google Sheets. Subsequently, calculations cannot be performed on them. For e.g. numbers that have commas and/or spaces within them are viewed as text in the sheet. Try simple functions such as =VALUE(SUBSTITUTE([cell], “,” , “.”)) to swap commas for decline places, or =VALUE(SUBSTITUTE([cell], ” “, “”)) to remove spaces within numbers.
