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.

 

[RIZVI]

Checked your formula and found this is not what I am looking for.

Consider a scenario, where a customer had purchased last month, but had not purchased for past 5 months, like 1,0,0,0,0,0.

So I shall not be expecting any sales for another 5 months, having purchased just last month, as he buys once in 6 months only.

 

[AlphaFrog]

I understand. The months are older to the right. I had it where the oldest month was to the left.


If the months are in B1:G1 with the oldest month in G1, the formula is almost the same.

=IF(G1=0,0,SUM(B1:G1)/COUNTIF(B1:G1,">0"))

For Sceneriao 2, the COUNTIF counts the cells that are greater than zero (three months) and uses that as the divisor.

What are the actual six month cells you use?

 

[RIZVI]

No, I am still not getting the desired results. In a scenario, say: 0,1,0,0,0,1, I am still getting sales forecast as 1 am where I should not be getting for another 2 more months.

The actual data in six months is the actual sales that I get from a customer.

 

[AlphaFrog]

No, I am still not getting the desired results. In a scenario, say: 0,1,0,0,0,1, I am still getting sales forecast as 1 am where I should not be getting for another 2 more months.

The actual data in six months is the actual sales that I get from a customer.

The formula I suggested only considers One, Two, Three, and Six month patterns within the last six months. Four and Five month patterns are not considered. That's the best I can do.