Coca-Cola (KO) Undervalued? Finance Crawler (Part 2)



I have been making some major changes with one of my projects — my automated finance crawler!

Logic Change: Start with companies, and analyze company information first
I completely rewrote my previous script to create a more robust and useful equity analyzer. Instead of starting with the industry and finding (potentially endless number of) companies, I have reversed the order of focus. Version 2 starts by pulling comprehensive information about selectively pre-stored company ticker symbols and proceeds to calculate a valuation based on companies’ financials. While version 1 started with a generic landing page of all industries, and dived into each respective rabbit-hole of an industry, and then dived into each sub-rabbit-hole that was a company, backtracked and dove into the next industry and it’s respective companies, version 2 simply looks and directly pulls from a defined list of companies. This currently ignores industries and is poor for new discovery, but holds the tremendous benefit of controlling the mining process and finding all necessary information about a company, while limiting the number of companies analyzed companies to mostly legitimate and respected companies. This also allows me to pull information centered on tickers, which is very necessary for pulling the financial numbers.

Increased Finance Robustness
Instead of a simple P/E ratio comparison, I have set up a much more complex calculation to perform an automated Discounted Cash Flow to Equity calculation for each company. This obviously increased the number of inputs and increased the complexity of the logic to yield a valuation. The necessary data included several line items from all three financial statements, including, but not limited to, revenue for the past several years, net income over the past several years, capital expenditures for the past several years, and balance sheet line items to estimate things like working capital. I used most of Aswath Damodaran’s Free Cash Flow to Equity Model (props to Damodaran for posting his curriculum and models online) to calculate FCFE projections, and then discount it back to present value calculation. If you’re interested in a financial lesson and checking my logic, visit CH 14 of his class. While I mostly followed Damodaran’s model, there were adjustments that I made.

To calculate 5 years of Free Cash Flow and a Terminal Value, I first take and calculate the average revenue growth over the past 2 years, and create an average potential growth rate. I apply a tiered growth rate to the latest Net Income. The growth rate is reduced by 20% every year. I used this logic instead of the proposed growth that should = (Return on Equity)*(Reinvestment Rate). Net Income is adjusted to Free Cash to Equity by subtracting Capex, adding Deprecation, subtracting increases in Working Capital, and adding Net Debt. In order to project Capex, I assumed a constant ratio of Capex to Revenue based on the past 3 years of historical data. This is applied to revenue numbers. This same logic is applied to Deprecation. Projected Increases in (Non-Cash) Working Capital was a little tricky; I calculated this by holding the ratios of Accounts Receivable, Inventory, and Accounts Payable to Revenue and calculating the difference between every year. To account for Net Debt (Maturing Debt that is offset by new Debt Issued), I used Damodaran’s logic that the (1 – % of Debt) can be used to adjust Net Capex + WC. This was calculated by looking at total Debt Outstanding and dividing it by the (Market Cap + Total Debt). Again, I applied Damodaran’s logic that the % of these cash adjustments fall under the proportion that falls to Equity/Enterprise Value of the Firm, while the % Debt would be offset and covered by Debt.

This thus leaves the equation: (Projected) Net Income – (% Equity)*( Capital Expenditures – Deprecation) – (% Equity)*(Inc in Working Capital) = FCFE for the nth year. The terminal value is calculated by taking year 5’s FCFE, and calculating a Gordan Growth terminal value with a 2% perpetuity growth. Because I only really care about cash that returns to Equity, not Debt, I discounted all of these FCFE figures back by the Cost of Equity, not WACC. I pulled the Beta from the company profile, levered it, and found a DCR with (Risk Free Rate) + (Equity Premium)*(Beta). I add up the discounted values of the FCFE, the terminal value, and VIOLA! I have an estimated Market Cap that I can compare to the current Market Cap.

This attached model does a better job of fully explaining the logic behind my calculations. I used the baseline numbers of perpetuity growth assumptions, risk free numbers, risk premium numbers, and so forth, within the model. Please feel free to question or comment my logic.

Cleaner Storage with SQL Tables and Data Storage
The amount of information increased in complexity to the point where I have three separate tables to store all necessary information. The first is a general table that holds stock information such as ticker symbol, company name, and industry (if given). This table is exists so I can call upon a list of companies to pull financial information for alternate uses. Instead of using the tickers I pulled previously, I used “respectable” companies from the SP1500. The second table holds companies’ income statements, balance sheets, and cash flow statements for the past 3 years. Each row is identified by their respective ticker symbol as well as the financial information’s year. I set this up so I can update financial information as new information comes in. The third table holds current valuations, so I wouldn’t need to constantly repull and recalculate companies’ valuations.

Ability to Create Insightful Analysis and Understand Companies
The cool thing about this is that I can automatically run this fairly time consuming FCFE DCF calculations on companies in a large and super scalable method. As an overall gutcheck, I utilized a very basic formula calculation (Current Market Cap – Projected Market Cap) / (Current Market Cap) to come up with a gross % under or over-valuation metric. How do all companies in the SP 1500 stack up?
My automated DCF crawler estimates that 21% of companies are under-valued and (respectively) 79% companies are over-valued. Given the volume of my assumptions, I was frightened and half expected all of my projections to have a huge variance against all current public valuations. This distribution is slightly comforting!

This crawler also does much of the heavy lifting for me – it assesses the financial viability of companies with little to no effort on my part. Two examples of interesting companies are Wal-Mart and Coca-Cola (as shown in my example model). According to my calculations, Wal-Mart’s current stock price is very rationally priced to its projected FCFE DCF valuation. It looks like Coca-Cola may be slightly under-valued compared to the underlying financial numbers at up to 60% under-valued. I reduced the growth by 1/2 to be more conservative, and my model still shows an upside of over 25%. I plan to utilize this script to give me some directional leads for equity investments!
(PS: Apple also looks incredibly under-valued. Full disclaimer, I am an Apple shareholder and think many people are a little pessimistic about Apple.)

Lacks Comparable Information
One of the major premises of financial valuation is the ability to compare similar companies’ financial numbers to see how it falls into line. Unfortunately, beyond manually picking companies out, my current script lacks the ability to truly compare companies to other industries. It may be possible to assign companies to the generic blanket industry coverage by crawling certain portions of a financial website, but it’s fairly obvious that many times companies within 1 industry cannot really be logically compared. At the same time, there are a number of more detailed and more robust series of analysis that I can create if I were to set up comparable logic. Thus, this is on the roadmap for a future operation. This, and more analysis, is to come!

Leave a Reply