Author: Jeff

Fun with Excel #9 – A Random Walk

Jeff, , , 5 Comments

As someone who prefers to walk from Point A to B, I have noticed that the “estimated time  to destination” on Google Maps has been a lot less accurate ever since I moved to NYC. For example, an inquiry for directions from my apartment (37th St and 10th Ave) to my office (49th St and 7th Ave) provides 3 suggested routes:

  1. Walk to 39th St, cross from 10th Ave to 7th Ave, then walk straight to 49th St.
  2. Walk to 48th St, cross from 10th Ave to 7th Ave, then walk straight to 49th St.
  3. Walk to 49th St, cross from 10th Ave to 7th Ave. (My preferred route)

Curiously, Google shows all 3 routes as taking 24 minutes. While Options 2 and 3 are essentially the same, Option 1  is clearly inferior due to the amount of time spent on 7th Ave and cutting through one of the busiest areas of Times Square. Therefore, whatever method Google is using to map out its walking routes, it doesn’t seem to be employing the optimal shortest-path algorithm.  One explanation is that it is simply using actual distances as edge weights, which is highly naive. Indeed, given the grid-like nature of midtown Manhattan, that would yield the same travel time for any path from Point A to B. A more effective method would be to use time values as edge weights, by approximating the time it takes to travel the length of any one block (using either actual walking data, or traffic data as a proxy for human walking activity).

In this Excel file, I create a quick “contour map” of midtown Manhattan, estimating the walking time for every block (or edge) that can exist on a given path from my apartment to my office. This involves giving much higher time values to avenues than streets, and relatively higher values to busier areas (e.g. Times Square).

Contour

In this case, my preferred route (Walk to 49th St, cross from 10th Ave to 7th Ave.) also happens to be an optimal route and takes a total of 1,140 seconds, or 19 minutes. On the other hand, crossing directly to 7th Ave from 10th Ave and then walking to 49th St takes 1,350 seconds, or 22.5 minutes. Running a random sample of 2,337 routes gave me an average travel time of 1,344 seconds (22.4 min) and a max travel time of 1,390 seconds (23.2 min). While it may not seem like much on an absolute basis, the difference of ~3.5 minutes between the optimal and average travel time equates to about an 18% increase in travel time. Do that twice a day for a year and the minutes can really add up.

The point of this (besides engaging in an extremely nerdy exercise), is to not only highlight the value of shortest-path algorithms, but also the importance of choosing the correct inputs when optimizing such algorithms. Google clearly has the resources at its disposal to bring us all a much more accurate Maps interface. As an avid user, that is something I would love to see in the future.

Fun with Excel #8 – Candy Crushed

Jeff, , , , , , , , 7 Comments

I just realized it’s been two months since my last post! I have been keeping busy both in and out of the office, but with things finally showing signs of slowing down, I’m glad that I’m able to get back to one of the things I love.

As most of you probably know by now, I have been a critic of what I dub Tech Bubble 2.0, or what I perceive to be the sky high valuation of social media and other tech companies/start-ups in the current economic environment. I gave Facebook and Twitter a pretty big bashing back in November, so you can imagine my shock (or perhaps delight) when I found out that King Digital Entertainment (KING), the creator of the wildly popular puzzle game Candy Crush Saga, would be going public in a transaction that would value the company at an astounding $7.1 billion.

On the very first day of trading (Wednesday 3/26/14) KING dropped 15.6% from its opening price of $22.50 to close at $19.00. The company fell another 2.7% to $18.49 on Thursday, followed by a 2.2% slump on Friday to close out the week at $18.08, or an overall decline of 19.6% since its IPO. It would seem that the equity markets have gotten a little wiser since the Twitter craze, but despite the beating that it has received in its first few days as a public company, I still believe KING is overvalued. Here are three main reasons why I am so bearish on the company:

1. The Company is essentially a one-hit wonder.

Take a look at the last four quarters of KING’s performance. Impressive, right?

Here’s what the charts don’t tell you:

  • Candy Crush Saga accounts for 78% of the Company’s total gross bookings, as well as 69% of Daily Average Users (DAUs) and 74% of Daily Game Plays (DGPs). The Company’s top 3 games account for 95% of total gross bookings, but Candy Crush Saga is about five times more popular than its next two games, Farm Heroes Saga and Pet Rescue Saga.
  • KING was founded in 2003. Candy Crush Saga was launched in April 2012.

The proverb “don’t put all your eggs in one basket” is quite appropriate when it comes to KING and mobile gaming in general. The potential risks of having the majority of your revenue driven by one source is quite clear – if that revenue source becomes impaired or disappears, you’re in serious trouble. The bad news for KING is that there is evidence that this is already happening. As the charts above show, while the Company’s quarterly average DAUs and MAUs are still increasing, the number of Monthly Unique Payers (MUPs) actually decreased meaningfully in the fourth quarter of 2013. Given the 99.8% correlation between the number of MUPs and Revenue performance, it is not surprising that Revenue also declined in the fourth quarter.

Returning to the fact that Candy Crush Saga accounts for roughly 80% of KING’s top line, one can conclude that a major factor behind the the decline in MUPs and Revenue is the shrinking popularity of the Company’s most popular title.

This brings us to the second bullet point. The fact that it took KING nearly ten years to come up with a megahit highlights the extreme difficulty of developing a game that appeals to a wide audience and that people are actually willing to spend money on. As Candy Crush Saga loses popularity, the chances that KING can develop a game that is equally successful or even half as successful to be its successor are slim. Even if it does manage to achieve this feat, investors need to ask themselves how long this would take. 5 years? 10 years? Longer?

2. The Company has limited means of improving its ability to monetize its user base.

When the vast majority of a company’s business is driven by online and mobile users, the challenge is two-fold: (1) building up the user base and (2) monetizing the user base in a sustainable way. While KING has demonstrated the ability to do the former, it is largely reliant on the “freemium” model to monetize its user base, where users can play for free but are incentivized to make in-game purchases in order to improve their results or improve their gaming experience. While the freemium model is a tried and true method of generating revenue, it has virtually no power to prevent an user from leaving (and stop paying) if he/she becomes uninterested in the game  itself. This is where Facebook and Twitter hold a crucial advantage over companies like KING; by serving as platforms for other apps and services (try to think of all the apps/services you use on a daily basis that are connected to Facebook), these social media giants make it very difficult for users to leave their networks.

As seen from the above the charts, while a highly successful product can help increase your user base almost exponentially, improving your ability to monetize on that user base (measured here by Gross Average Bookings per User and Monthly Gross Average Bookings per Paying User) is another story entirely. While KING’s ability to monetize has largely held steady (with fluctuations from quarter to quarter), there is no indication that the Company will actually be able to improve on this ability in a meaningful way in the long-run. Without this ability, revenue growth becomes almost solely dependent on (paying) user base expansion, and given KING’s reliance on its Candy Crush Saga title, we once again find ourselves asking whether the Company can repeat its past success.

3. The Company does not have an economic moat.

Warren Buffett once said, “In business, I look for economic castles protected by unbreachable ‘moats’.” What Buffett means by “moat” is a sustainable competitive advantage that allows a business to maintain its profitability over the long-run.

It is hard to argue that KING or any of its competitors in the online and mobile gaming market have a substantial competitive advantage. From FarmVille to Words with Friends to Candy Crush Saga to Flappy Bird, it is impossible to tell where the next megahit game will come from. While KING can devote resources and personnel to increase the chances of its own success, it cannot prevent thousands of other game developers from doing the same thing.

So what’s it worth?

For a company like KING, the biggest driver of valuation is growth. Given the strong correlation between the number of Monthly Unique Payers and Revenue performance, we set out to project the number MUPs on a quarterly basis from 2014 to 2017.

We start with the number of Monthly Unique Users (MUUs), which has been growing, albeit at a slowing pace. This is likely due to the fact that there is a market penetration ceiling that KING is approaching. According to eMarketer, the number of smartphone users is expected to hit 1.75 billion in 2014 and 2.5 billion in 2017.

If we assume that KING can reach a quarter of those users, that gives us an approximate market of between 438 million and 625 million unique users over the next few years.

In the base case, let’s assume that KING is unable to develop anything that rivals the popularity of Candy Crush Saga. Quarterly average MUU growth, which is already showing signs of slowing down, is likely to slow further. In my model, I have the number of MUUs reaching 395 million by the end of 2014, and increasing by 10 million every quarter thereafter. In terms of KING’s monetization percentage, which I define as MUPs/MUUs, the Company managed to achieve a 5.3% monetization percentage in Q2-13, although that figure fell to 4.8% in Q3-13 and 4% in Q4-13. I believe this percentage will continue to decline in future quarters. If we look at the historical levels, it seems likely that the Company’s recent out-performance in this category in the last 4-6 quarters is driven by the success of Candy Crush Saga. Thus, on a going-forward basis, I have this percentage dropping and eventually holding at 2.5%. What all this results in is a near term decline in the number of MUPs (as the popularity of Candy Crush Saga declines), followed by a slow reversal starting in the middle of 2015 (as the Company continues to develop smaller hits in the future). The last step is linking the projection of MUPs to Revenue growth, which I do through a simple regression in Excel.

In the downside case, I simply apply a 10% haircut to the Revenue growth figures in the base case. Finally in the upside case, I assume KING manages to develop another Candy Crush Saga-esque megahit, leading to triple-digit Revenue growth in the immediate future.

I attribute probabilities of 50%, 40%, and 10% to the base, downside, and upside cases, respectively. For those of you who think I’m being too conservative in my upside case, I think there are two important takeaways here:

  1. KING’s ability to penetrate the market is limited by the overall number of smartphone users, and the percentage of those users who will play its games. Even if the Company is able to develop one or two more Candy Crush Sagas in the near future, the number of MUUs is constrained. We could, of course, assume that the Company is able to achieve significant gains in its monetization percentage, but even if the Company is able to do so…
  2. The likelihood of the upside case even happening is too low that its impact on our overall projections is muted.

My final Revenue model is a simply a weighted average of the three scenarios:

Doing a quick-and-dirty projection of unlevered free cash flows and taking a discount rate of 15%, I get to a valuation figure of $2.3 billion.

While there is still a lot of uncertainty in our model, the bottom line is that it would take an inordinate amount of optimism to arrive at a $7.1 billion valuation, or a even a $5 billion valuation.

Before I sign off, I will leave you with another interesting snapshot:

Those are not the financials of KING, but Zynga (ZNGA), the creators of FarmVille. Coincidentally, ZNGA also had a $7 billion valuation when it went public in 2011, but has since lost over half of its value. I think the 5-year summary financials do a pretty good job of telling the moral of the story: in the ever-changing world of social media and mobile/internet gaming, growth (and “value”) can disappear just as quickly as it appeared.

KING shareholders, beware.

—

Acknowledgements: Thanks to Michael Lai for his insight on looking at KING’s growth from a top-down rather than a bottom-up perspective.

Fun with Excel #7 – A Better Chance of Winning One Billion Dollars

Jeff, , , , , , , Leave a comment

Warren Buffett and Quicken Loans made quite a splash last week when they announced that they would be awarding $1 billion to anyone who filled out a perfect bracket for this year’s NCAA Men’s Division I Basketball Tournament.

As many news articles all over the web have already pointed out, the total number of possible variations is enormous. In a single-elimination bracket involving 64 teams, there are exactly 63 matches played. As each match can have two outcomes, the total number of possible outcomes is 2^63, or 9,223,372,036,854,780,000 (roughly 9.2 quintillion).

However, without knowing too much about basketball, much less having an educated view of which teams are most likely to make the tournament this year, we can actually narrow that number down by quite a bit.

Part A: The first and most important step is noting that in the history of the tournament, no #1 seed has ever lost to a #16 seed in the first round (116-0). Assuming things stay that way this year, we can reduce the number of variations to 2^59 (since 4 of the matches are decided), which is 576,460,752,303,423,000. This is a still a massive number, but only 6.25% of the theoretical total.

Part B: Continuing off of A, we can build on the concept of upsets to further narrow the number of bracket variations. If we define an upset as simply any result where a lower ranked seed defeats a higher ranked seed, then the result of every match becomes binomial: it is either an upset, or it isn’t. This is a powerful notion, because we can now think of the total number of variations as the sum of distinct sub-variations based on the number of upsets. In a tournament consisting of 63 matches, there can either be 0 upsets, 1 upset, 2 upsets, 3 upsets…62 upsets, or 63 upsets. Furthermore, there is (63 choose 0)=1 way of achieving 0 upsets, (63 choose 1)=63 ways of achieving 1 upset, etc. Summing up all of these terms in fact gives us 9,223,372,036,854,780,000, which is what we expect. Astute readers will note that this is simply a combinatorial identity demonstrated by Pascal’s Triangle, but it is nonetheless meaningful to take a moment to verify this identity yourself if you’ve never encountered it. While it is theoretically possible for any number of upsets to occur during a particular tournament, the last five NCAA tournaments have all had between 16 and 20 upsets (average of 18.4). Thus, if we only consider brackets with  15 to 22 upsets (going out by ~2 standard deviations on both ends for some margin of safety), the number of variations drops down to 19,420,762,596,874,200, or 0.211% of the theoretical total.

Part C: We make one last simple observation to refine our analysis: for any given tournament, most of the upsets (about 50%) occur in the first round (average of 9.2), with no less than 7 upsets in the first round over the last five years. Thus if we disregard all the bracket variations in B that include 5 upsets or less in the first round, we can cut down the number of variation by an additional 200 trillion of so, giving us 19,219,810,265,601,500 variations, or 0.208% of the theoretical total.

Science has also shown that you are more likely to click on a colorful graphic.

Conclusion:  Clearly, the odds of predicting a perfect bracket is still extremely small. However, the key takeaway here is that even with no knowledge of college basketball, we can quickly reduce the total theoretical number of bracket variations by almost three orders of magnitude to a more practical number by using basic probability concepts and making a few simple assumptions.

P.S. While you do have a better chance of winning the lottery than getting your hands on the grand prize of 1 billion dollars, the NPV of this particular “investment” is still positive since the cost of registration is free 😛

Fun with Excel #6 – Chipotle for Years

Jeff, , , Leave a comment

Although I consider myself a Chipotle enthusiast, I am also a creature of habit. My usual meal at the popular restaurants chain is a chicken burrito bowl (tortilla on the side, of course) with brown rice, black beans, fajitas, mild salsa, corn, cheese, and lettuce. But as I was indulging in my favorite cause of food coma last week (with a side of chips to boot), I started thinking about one of the restaurant’s biggest selling points, namely the ability to offer a highly customizable product at a low cost (both in terms of raw ingredients and labor). It seemed apparent that I could go to Chipotle twice a day every day for a whole year and still be able to eat a different meal every time, but the power of combinatorics never fails to astound me. Taking my train of thought to the spreadsheet, I found that given Chipotle’s diverse menu of options, you could theoretically eat 442,368 different meals. That’s 606 years of Chipotle if you managed to eat there twice a day!

You can take a quick look at my work here. As a bonus, I used Excel’s random number generator to suggest a different meal every time you refresh the sheet, so if you’re ever unsure of what to get at Chipotle, feel free to use it. Just don’t blame me when probability laughs in your face and suggests 3 hard shell tacos and nothing else 😛