Predicting

[RIZVI]

I have a large customer base approx 5000 in number. Each one buys according to a trend. That is some buy every month, some once in two months, some once in every three months etc. Our product / service is such that if at the right time our sales officers do not approaches the customer then they will buy from any one(Competitors) who has approached them at right time. Hence we manually check out the buying ternd / pattern from the 5000 odd customer data base and inform the concerned sales officer to approach them. Now doing the same manually is very tough task.
Is there any way in excel that can automatically check the buying trend / pattern and highlight such custtomers who need to be approached in a given month.

We have Customers listed under column A ( Like customers in A1, A2, A3....). Buying pattern in columns B,C,D....for April, May, June .....Once a customer makes a purchase in a given month that cell changes its colour to pink due to conditional formating, and the blank cell turn yellow. Presently we note this colour pattern manually and note the customer and inform the sales officers.

PLease help.

 

[guggie3]

Does your spreadsheet contain an actual date of when the customer is visited, or purchases from you? If so, then using either EDATE formula or the incrementing DATE formula with the applicable frequency value should yield a column which can then be programatically checked to give you the next "Visit the customer" target.

That's certainly easier than manually checking the colors.

Note that EDATE is simplest, but requires the Analysis Toolpack to be installed (at least on xl2003).

 

[RIZVI]

NO it does not contain the date of visit. IT only takes the info / data from other cell and gives the sales effected in the month. Also it shall itself chek out the trend as per the sales being effected. Let us says a customer buys in Jan, then in March and then on May. We will fill the sale dat in under the months. Now as per the trend appearing it must automatically now predict that next sales in likely in July.

Please tell me how to do that. YOur edate did not help me. PLease eloborate step by step.

Thanking you in advance,

Rizvi

 

[guggie3]

It appears that this is going to require that you post some samples of what your current spreadsheet looks like, as well as whatever "database report" data you use to fill in the date of purchases.

Refer to the sticky posts for instructions to post screenshots and code.

 

[RIZVI]

Is there a way to attach or insert the data sheet of excel here so that you can see what I want / desire.

RGDS,

Rizvi.M.H.