I am horrible with math and was hoping someone can help me out here with a formula. I am using a spreadsheet to try to predict my annual sales volume.
Assume I make widgets that I can sell for $1000 each and I can find one brand new customer per week who will buy one unit. Depending on how often the customer uses their new widget, it can last anywhere from 1 month to 9 months, but the average lifespan is 3 months. Let’s say that 80% of customers are happy with their widget and opt to buy a new once when theirs wears out.
Assuming a 50 week work year, I should have 50 customers at the end of the year and $50,000 in first-time sales from them. But the compounding nature of the repeat business is what confuses me. What can I expect my total annual sales to be? And how much does the average lifespan of the widgets affect that number?
Thanks in advance for your help!
Bonus points if you can somehow take into consideration that if a person buys a second widget from me, there’s a 90% chance that they’ll buy a third. And if they buy a third, there’s a 95% chance that they’ll buy a fouth.
One way to visualize the compounding nature of the calculations is to keep one customer per row. Put the weeks in separate columns. Each new customer is a new row under the previous one but the new customer starts on week later (i.e. one column to the right of the previous one). Customer 1 starts in col A, customer 2 starts in col B, etc.
You’ll end up with 50 row block that is staggered. Seeing the returns for a year or week is simply taking the sum of the column.
After that, you can combine all the formulas in different cells to make on single formula.
This is not enough information to give an exact answer. Officially we would need to know the entire probability distribution for widget lifespans to come up with an exact value for predicted sales. However, we can get an estimate if we assume that every widget lasts 3 months. In that case, if a customer buys their first widget at time 0 (we measure time in months), then there’s probability p = .8 that they buy another at time 3, probability p = .8 * .8 = .64 that they buy another at time 6, probability p = .8 * .8 * .8 = .512 that they buy another at time t = 9, and so forth.
Now in your spreadsheet, for each customer you create one column for the time, and one for the probability that they buy a widget at that time. Each row then includes a time that’s a multiple of 3 months from their start time, and the probability. The first customer would have columns looking like:
t | p
0 | 1
3 | .8
6 | .64
9 | .512
For each new row, you add 3 to the time column, and multiply the probability column by .8 to get the probability of that customer buying a new widget. Multiply the probability by $1000 to get the expected profit from this particular customer, and then add up the results for all customers.
For the bonus problem, you just have to use a different multiplication factor for finding the probability in each row. You would use a factor of .9, rather than .8, to get the probability in the second row, for instance.
I was going to suggest a Monte Carlo simulation of customer growth, using a running tally of profit. Give yourself the following odds:
95% chance of making a sale of one unit each week.
(5%, 33%, 50%, 54%, 60%, 67%, 75%, 84%, 95%) chance of failure at the (1,2,3,4,5,6,7,8,9) month point – that distribution is a wild guess, but should meet your purposes, I think.
So your program, in very pseudo code, would read like so:
time = 0
sales = 0
endtime = 48 #four year simulation
Define ReSell(element, numsales) as
{salesodds = some function of numsales;
if (rand() < salesodds) then
element in CustArray = zero;
sales = sales + 1000;
;}
# Your CustArray should have a SalesArray that mirrors it, initialized to "1" for each element in CustArray that is a sale.
for time = 0 through endtime{
ReSell (CustArray(time), 0);
for each element of CustArray{
if element = 0, continue
if (element = 1) AND (rand() < .05) then ReSell(CustArray(n), SalesArray(n)++)
if (element = 2) AND (rand() < .33) then ReSell(CustArray(n), SalesArray(n)++)
...
if (element = 10) then ReSell(CustArray(n),SalesArray(n)++)};
time++;
}
There are some big problems with this, but it’s pseudo-code and you get the drift.
I wish it were; I’d wouldn’t have to ask such basic questions on an Internet messageboard However, my education is in political philosophy and it’s been a long time since I’ve had homework. :smack:
I thought about doing that, but wondered if there were a more elegant way. Thanks for the suggestion.
Are the second-4th widgets part of the initial purchase or an adjusted probability that customers will develop a brand loyalty and be more likely to continue purchasing which would seem to contradict your 80% return customer figure.
Sales and rough cut capacity planning like this should be based on real world numbers. Until you have some actual figures any forecasting is basically a wild guess. With a years worth of real numbers many dopers (myself included ) could build you intricate product supply models complete with lead times for widget subcomponents, economic order quantities, and production methods to maximize return on investment.
It always amazes me the shear number of bright people here with such widely varying experiences. Thanks for your input all!
drachillix, I was simply making up numbers out of thin air which is why it probably doesn’t make much sense. I was trying to shoot for predicting brand loyalty. To make matters even more complex, I’m actually selling services not goods. But I thought that I’d try to simplify things. This is a new venture and I don’t have any historical numbers to plug in.
I’m just shooting for the best Scientific Wild Ass Guess I can get. When I get further along, I’ll be sure to get someones’ contact info and actually pay for a real analysis.
There is a simple solution to every complex problem…and its probably wrong. In some respects a service is a custom product with no shelf life and a “serviceperson” produces an average of X units of that service per day. Variations can be accounted for and added in once some numbers have come in.
However, my education is in political philosophy and it’s been a long time since I’ve had homework. :smack:
) could build you intricate product supply models complete with lead times for widget subcomponents, economic order quantities, and production methods to maximize return on investment.