Can we talk about beauty in numbers? Amidst all the nervousness about forecasting new products, does it not make sense, and add elegance too, to talk about the classic s-curve we see in nature? So many natural phenomena show an increase along a natural S-curve and bell curve  like the two shown here on the right. Which, by the way, was done with Excel. And I’m going to explain it here and give you the formulas, so you can use them in your own sales forecasting. If you like this sort of thing.

What we see there is a natural projection of a good product — like cell phones or television or computers — spreading through a population of 50,000 people. It could be a new product technology that everybody ends up wanting, or, simply, a good idea. It doesn’t apply to a lot of new product forecasts without humans evaluating the results and tempering them with common sense. But then few mathematical models do.

The spreadsheet model that drew that line used these assumptions:

1. The total population of potential users is 50,000
2. At the start, 10 people are users
3. Each month a new person starts using for every three people already using.

If you like math buzzwords, then you’ll like knowing that the math behind this natural curve is called a diffusion model, which has been used in science in applications like epidemiology (it’s basically the plot of the recent hit film contagion). It’s also closely related to the classic idea adaptation model, which produces a classic bell curve that we often see divided into areas for the opinion leaders, early adapters, and so forth.

That’s my second chart here, and it is done from exactly the same numbers as the first. The first is total users per month, and the second is new users per month. This one was made famous by sociologists studying the pattern of adaption of a new farming technique, and has been used a lot for predicting technology adaption.

This is one way to answer that frequently asked question “how do I forecast a new product when I have no history.”

In the spreadsheet model below, you can see the key formula for calculating new users from existing users. In cell D7 there you have the formula:

=(users*contagion rate) * (population-users)/population

To read that formula you’d guess — correctly — that the users range is in row 7, contagion rate is dell B3, and population is cell B2. I enclose that basic formula inside a rounding function that rounds results to whole people, rather than portions of people. That turns my formula into:

=round((users*contagion rate)*((population-users)/population),0)

… which is what you see in the illustration above.

As you can probably guess, the users calculation in row 6 of the spreadsheet adds the beginning of the month users total to the new users to calculate the beginning-of-month total for the next month. So the 23 users in cell E6 is the sum of the 17 users in cell D6 plus the 6 new users in cell D7. For that formula I use a MIN function to make sure I don’t accidentally project more users than total population. The actual formula for cell E6 is:

=MIN(D6+E6,population)

So what’s the use of all this spreadsheet detail? First, because it gives a forecast some meaningful assumptions you can explain to an interested party, substituting a natural phenomenon for SWAG (scientific wild-assed guess). Second, because sometimes it works. One of the most accurate forecasts I ever did was a projection of personal computer usage in Latin America in the 1980s. I assumed four economic strata, so four different population groups, each of which had different contagion rates and total populations. They were basically the urban wealthy, the middle class, and the rest of the population. I did a five-year forecast that my clients checked five years later, and I had predicted the total, on a five-year time frame, to within five percent of what actually happened. That was quite a coup, and it still makes me happy many years later.

Final note: I’m enjoying all the possible contradiction between this post and my last post, make my analysis intuitive please. I’m not offering any excuses for it either. Except, maybe, the beauty of a natural curve.