I have a large data base on Sheet 1. Customers are Listed under Column E. From F till Q are the months from April till March.
IN each row, Say E2, E3, E4, are customers says x , y z,.....
Against each customer from F thru Q are their monthly sales. That is to say that for M/s x, in F2 is the April billing, in G2, is the May billing. And so on.
Invoice details are entered manually at a separate place in Sheet 2. The monthly sales are entered into the above cells (that is F till Q) automatically thru a formula based on dates against each customer.
Now each customer buys accordingly to their requirements and as such has a buying pattern. Some buy every month, almost the same value while others show a pattern of high and low.
Some buy alternate month.
Some buy once in three months etc.
Now our product is such that if our sales person does not gives them reminder call just before they intend to buy the customer will purchase from anyone (competitor) who happened to present themselves at right time to the customer. Approaching the customers at the time they do not intend buying is waste of time and makes customer puts unnecessary questions and demands.
We have base of more than 5000 customers. Presently we look into the buying pattern of each customer manually and inform our sales force.
I request any one to help me if the same can be done by excel so that we are able to highlight those customers who intend to buy next or this month based on their buying pattern.
Secondly I can upload my file but please guide me how to do that so that the data becomes more clear.
Thanks in anticipation.
Rizvi
E F G H I J K L M N O P Q
1 X 1.2 1.4 1.2 1.4 1.2 ? ? ?
2 Y 2 1 2 ?
3 Z 1.5 1.5 1.5 ?
4 A 0 1 1 0 1 ?
5 B 0 2 1 0 0 ?
IN each row, Say E2, E3, E4, are customers says x , y z,.....
Against each customer from F thru Q are their monthly sales. That is to say that for M/s x, in F2 is the April billing, in G2, is the May billing. And so on.
Invoice details are entered manually at a separate place in Sheet 2. The monthly sales are entered into the above cells (that is F till Q) automatically thru a formula based on dates against each customer.
Now each customer buys accordingly to their requirements and as such has a buying pattern. Some buy every month, almost the same value while others show a pattern of high and low.
Some buy alternate month.
Some buy once in three months etc.
Now our product is such that if our sales person does not gives them reminder call just before they intend to buy the customer will purchase from anyone (competitor) who happened to present themselves at right time to the customer. Approaching the customers at the time they do not intend buying is waste of time and makes customer puts unnecessary questions and demands.
We have base of more than 5000 customers. Presently we look into the buying pattern of each customer manually and inform our sales force.
I request any one to help me if the same can be done by excel so that we are able to highlight those customers who intend to buy next or this month based on their buying pattern.
Secondly I can upload my file but please guide me how to do that so that the data becomes more clear.
Thanks in anticipation.
Rizvi
E F G H I J K L M N O P Q
1 X 1.2 1.4 1.2 1.4 1.2 ? ? ?
2 Y 2 1 2 ?
3 Z 1.5 1.5 1.5 ?
4 A 0 1 1 0 1 ?
5 B 0 2 1 0 0 ?
