I would like to calculate some parameters relating to customer purchasing in a retail situation.
I have some basic information which I can use:
Customer visit frequency in the form of probability distribution (I can generate Excel poisson tables using average visit frequency and these 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%
Customer purchase quantity per visit (based on observation), e.g.:
1 unit: probability = 60%
2 units: probability = 25%
3 units: probability = 10%
4 units: probability = 4%
5 units: probability = 1%
(max 5 units)
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, 11…. etc, units being purchased in the given time period. 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 in a more general case where the numbers of potential customers n are larger! Once I know how to do the calculation, I would like to implement this in Excel.
To make this clearer: Case for 1 customer is simple, being 5 options (using my data: probability of 1 customer is 27% who is likely to purchase 1, 2, 3, 4 or 5 units with probabilities 60%, 25%, 10%, 4% ,1%). I can then calculate probability of selling 1, 2, 3, 4, or 5 units by multiplying.
The case for n = 2 customers has 25 options (I think) (customer 1 with 5 options x customer 2 with 5 options). For this case I may sell 2,3,4,5,6,7,8,9, or 10 units and I can multiply and sum the probabilities manually. This result would then be added to the result for 1 customer.
However, with the case for n = 3 customers there would be 125 options and the adding and multiplying already starts to become hairy. In practice, my customer visit table may extend to many more than 3 customers so the problem quickly becomes difficult to manage without having some kind of general formula.