Just Give Me a Number: How to Avoid the Flaw-of-Averages
Problem: Your CEO just popped his head in and asked you for a quick estimate of the expected number of paying customers by year-end. He has an analyst meeting in an hour to discuss first half results and needs a number fast.
Of course, the first half numbers aren't quite in yet, but you do know that:
- at the beginning of the first half, there were 425,000 customers;
- the company has been adding an average of 50,000 per quarter from direct marketing campaigns;
- channel initiatives add an average of 35,000 per quarter;
- customer referrals have been averaging around .08 (meaning that each customer at the beginning of the period can be expected to refer .08 new customers that period); and,
- that the attrition rate from quarter-to-quarter has averaged about 15%.
So, if we load all that into a little spreadsheet, we come up with:
|
Based on what you know, your best guess estimate is 623,000. Good job. Completed with 49 minutes to spare.
Now, flash forward seven months when the year-end results come in and the actual year-end customer count is only 575,000 — off by almost 50,000. What went wrong?
Well, to begin with, while direct marketing might generate an average of 50,000 new customers each quarter, it has historically ranged between 38,000 and 62,000. Next, while the channel initiatives have averaged 35,000 per quarter, they too have ranged between 29,000 and 41,000. Then the customer referral rate, while indeed averaging .08 over the past 12 quarters, has oscillated between .05 and .11. And the attrition rate, widely quoted within the company as being around 15%, it has been as low as 11% or as high as 19% in any one quarter.
OUCH! You've been bitten by the "flaw of averages." But there is a simple way to avoid this math trap and improve the quality of your estimates — without spending a lot more time building models.
Let's assume each of the four variables — customers from direct marketing, channels, customer referrals, and attrition — are best described not by their average, but by identifying a estimate for the low, median, and high possibilities for each that we feel 95% certain about. Here's what that might look like in table format:
|
Next, we assume, for simplicity, that these three data points represent the midpoints and tails on a bell curve. Then we rebuild the spreadsheet used earlier, but instead of inputting average figures for each variable, use a number randomly selected by Microsoft Excel from each variable's own bell curve. Using a $100 plug-in software packet called XLSim®, we ask Excel to do this little exercise 1,000 times (which takes less than a minute) as a means of simulating all the many possible combinations between variables that are likely to occur. And finally, we see a simple graph like the one below, which tells us the probability of the year-end customer count being less than a certain number.
|
We can see from the chart, the median year-end customer count is 630,000. But we also see that there is an equal likelihood (10%) of the actual count being less than 570,000 or more than 689,000.
From this analysis, we can report to the CEO, in only 15 minutes, that there is an 80% likelihood the number will be between 570,000 and 690,000, with a median guess of 630,000. We might further advise him to tell the analysts that we expect something in the 575,000 to 650,000 range as a conservative estimate. Further, we might take the opportunity to reinforce the importance of your new customer referral program initiative, which, you are now able to point out, could boost the customer count by an extra 30,000 between now and year end.
