Model and Calculate Customer Purchase Probabilities using Excel

[markccb]

I would like to calculate some parameters relating to purchase demand in a retail situation and would like to do this with Excel. The first problem is knowing how to do the statistics calculation. The second part is then how to implement in Excel.


I have some basic information which I can use:

Customer visit frequency in the form of probability distribution (Excel poisson tables work well) for number of customer visits in a given period (1 month), e.g.:
0 Customers: 14%
1 Customer: 27%
2 Customers: 27%
3 Customers: 18%
4 Customers: 9%
5 Customers: 4%
6 Customers: 1%
.... etc

Customer purchase quantity per visit:
1 unit: probability = 60%
2 units: probability = 25%
3 units: probability = 10%
4 units: probability = 4%
5 units: probability = 1%


The data points above are provided by way of example but will differ from case to case.


I would like to calculate the probability of a total of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, etc, units being purchased in the given time period and would then like to develop a general Excel model to calculate the answers for any given case. I think I can do this if the max number of customers is very small (2 or less!) but am struggling to see how to achieve this where the numbers are larger!


As I have had no success on the internet so far trying to find a similar example to mine, I would be super grateful for any input here !

 

[ctd2015]

I don't have an exact solution to your queries but just two important inputs
1) Do a goodness of fit test or some other hypothesis tests to check if your weibull assumption is correct
2) If you are assuming the customers are i.i.d which is independent , identical distributed then the possibility will simply be a summation if I am not mistaken

 

[markccb]

I don't have an exact solution to your queries but just two important inputs
1) Do a goodness of fit test or some other hypothesis tests to check if your weibull assumption is correct
2) If you are assuming the customers are i.i.d which is independent , identical distributed then the possibility will simply be a summation if I am not mistaken

Thanks for the input CTD! I have actually made some good progress in the last day or two using a combination of the multinomial distribution and simpler permutation/combination calculations. But what you say seems to make sense and I will need to check the assumptions once I have finished with the hard bit!