Sales Forecast

[RIZVI]

Hi,

In Excel 2007, I am having previous four month's sales against each customer. Each customer has different sales pattern. Some buy every month, some alternate month, some once in three months and so on and so forth. There is no fixed pattern.

Presently I am using if/and formula to populate the sales forecast by using this formula, which is very bulky and lengthy:

=IF(AND(BR7>0,BT7>0,BU7>0,BV7>0),(SUM(BR7+BT7+BU7+BV7)/4),IF(AND(BR7>0,BT7=0,BU7=0,BV7>0),0,IF(AND(BR7>0,BT7=0,BU7>0,BV7=0),0,IF(AND(BR7=0,BT7=0,BU7=0,BV7=0),0,IF(AND(BR7>0,BT7>0,BU7=0,BV7>0),(SUM(BR7+BT7+BV7)/2),IF(AND(BR7=0,BT7>0,BU7>0,BV7>0),(SUM(BT7+BU7+BV7)/2),IF(AND(BR7=0,BT7>0,BU7=0,BV7=0),0,IF(AND(BR7=0,BT7=0,BU7>0,BV7>0),(SUM(BU7+BV7)/2),IF(AND(BR7>0,BT7=0,BU7=0,BV7=0),0,IF(AND(BR7=0,BT7>0,BU7=0,BV7>0),(SUM(BT7+BV7)/2),IF(AND(BR7=0,BT7=0,BU7=0,BV7>0),BV7,IF(AND(BR7=0,BT7>0,BU7>0,BV7=0),(SUM(BT7+BU7)/2),IF(AND(BR7>0,BT7>0,BU7>0,BV7=0),(SUM(BR7+BT7+BU7)/3),IF(AND(BR7>0,BT7=0,BU7>0,BV7>0),(SUM(BR7+BU7+BV7)/3),"P"))))))))))))))

I need help in 2 ways:

1. I want to extend the trend for past 6 months.
2. Is there a more simpler way available then the above lengthy formula incorporating 6 months data rather than just 4 as of now?

If yes then please help.


RGDS,

Rizvi.M.H.

 

[AlphaFrog]

I'm trying to understand your logic and reasoning in the formula, but cannot discern a pattern.

Why are some combinations excluded and why some three months are added and then divided by two?

=IF(AND(BR7>0,BT7>0,BU7>0,BV7>0),(SUM(BR7+BT7+BU7+BV7)/4),
IF(AND(BR7>0,BT7=0,BU7=0,BV7>0),0,
IF(AND(BR7>0,BT7=0,BU7>0,BV7=0),0,
IF(AND(BR7=0,BT7=0,BU7=0,BV7=0),0,
IF(AND(BR7>0,BT7>0,BU7=0,BV7>0),(SUM(BR7+BT7+BV7)/2),
IF(AND(BR7=0,BT7>0,BU7>0,BV7>0),(SUM(BT7+BU7+BV7)/2),
IF(AND(BR7=0,BT7>0,BU7=0,BV7=0),0,
IF(AND(BR7=0,BT7=0,BU7>0,BV7>0),(SUM(BU7+BV7)/2),
IF(AND(BR7>0,BT7=0,BU7=0,BV7=0),0,
IF(AND(BR7=0,BT7>0,BU7=0,BV7>0),(SUM(BT7+BV7)/2),
IF(AND(BR7=0,BT7=0,BU7=0,BV7>0),BV7,
IF(AND(BR7=0,BT7>0,BU7>0,BV7=0),(SUM(BT7+BU7)/2),
IF(AND(BR7>0,BT7>0,BU7>0,BV7=0),(SUM(BR7+BT7+BU7)/3),
IF(AND(BR7>0,BT7=0,BU7>0,BV7>0),(SUM(BR7+BU7+BV7)/3),"P"))))))))))))))


Can you provide a better description of what you are calculating? I'm sure this formula could be shortened.

Does a "zero" month have an actual zero in the cell or is it just blank?

 

[RIZVI]

As I said, there is no fixed pattern.

However all can be divided by 4 as the data taken is of four months. The reason why I have taken 2 or 3 or 4 at times, that the values in those cells was zero. So took only the cells where the value was more than 1. However all can be divided by 4.

Let me know if more clarification is required.

RGDS,

Rizvi

 

[RIZVI]

"Zero" sales moth has an zero "0" and not blank

 

[AlphaFrog]

As I said, there is no fixed pattern.

However all can be divided by 4 as the data taken is of four months. The reason why I have taken 2 or 3 or 4 at times, that the values in those cells was zero. So took only the cells where the value was more than 1. However all can be divided by 4.

Let me know if more clarification is required.

RGDS,

Rizvi

I understand there is no pattern to the customer sales.
I'm trying to understand the logic pattern to your formula.
More clarification needed.

Why is this a zero result when two cells have a value?
IF(AND(BR7>0,BT7=0,BU7=0,BV7>0),0,

Why is this divided by 2 when there are three values > 0
IF(AND(BR7>0,BT7>0,BU7=0,BV7>0),(SUM(BR7+BT7+BV7)/2),

 

[RIZVI]

Well, I see this is an error in my logic. This is what happens when I am taking the values manually.

 

[AlphaFrog]

This will average only the months greater than zero. Is this what you want?

=SUM(BR7,BT7,BU7,BV7) / ((BR7>0)+(BT7>0)+(BU7>0)+(BV7>0))

Otherwise, i don't know what you want if your original formula is flawed.

 

[RIZVI]

Not Exactly.

Let me explain how I arrive at this months sales forecast by looking into the performance of past months. Let us take few scenarios:


1st Case when customer buys each past 6 months: 1 , 1 , 1 , 1 , 1 , 1 (sales more than 0 in past 6 months). So this months sales-forecast will will average of last six months = 1

2nd Scenario: 1, 0, 1 , 0, 1, 0 (alternate months sales is more than 0) then this month's forecast will be 0

3rd scenario: 1,0, 0, 1, 0, 0 (buys once in three months) so, this month's sales forecast will be 0

Like this various scenarios are possible. I know it may b near impossible to get accurate sales forecast, but we can try to get as real as possible.

The valuse of expected sales has to be average take-off a customer usually buys, as and when he buys.


RGDS,

Rizi.M.H.

 

[AlphaFrog]

What are the

2nd Scenario: 1, 0, 1 , 0, 1, 0 (alternate months sales is more than 0) then this month's forecast will be 0

3rd scenario: 1,0, 0, 1, 0, 0 (buys once in three months) so, this month's sales forecast will be 0

That doesn't seem correct: For Both the 2nd and 3rd scenarios, wouldn't the seventh month be an average (not zero)? That starts the six month pattern over again.


If the sales months are in A1:F1, this formula looks at if the first month (A1) is zero, if yes, the seventh month (the forecast month) will be zero no matter what pattern. If the first month is not zero, the forecast month is not zero so as to repeat the six month pattern. The average is always the average of all months greater than zero regardless of the pattern.

=IF(A1=0,0,SUM(A1:F1)/COUNTIF(A1:F1,">0"))

 

[RIZVI]

In all scenarios, the the first entry is the sales of the last months, the second entry in second column is sales of last to last months....and the entry in sixth column is the sales of the sixth month previous from now.

So in 2nd Scenarios, if we see the customer had purchased last month, but not previous to last month. Again purchased a month prior to the previous to previous month. and before than again no sales. So logically as he is buying alternate months, and as he has purchased just last months, so this months sales will be zero. About the value of sales expected will be the average of the sales of the 3 months he had purchased. So the sum has to be divided by 3 and not 6.

Same way in scenario 3, as the customer has purchased last month, and looking at his buying behavior we can assume that for this and next month to come we can't be expecting any sales. So again it will be zero. BUT the 3rd months from now he will again buy, and the value expected has to be average of the sales he made each time, so it has to be divided by 2, and not 3, as he makes 2 sales in six months period.

In meanwhile I will try to understand how your formula works.

RGDS,

Rizvi.M.H.