I have a mdoel where a growth assumption has been attached to sales and a pattern of buyer behaviour assumed. For simplicity a linear pattern of behaviour of customers has been assumed (although in my main spreadsheet this can be amended by changing the assumption around how often a customer buys). Over a 36 month period there will be 36 cohorts of customers. In a customer's first year they will purchase in month 1, and 9 (and always buy 1 item).
Therefore in month 5, Cohort 1 is joined by Cohort 5 (and in Month (, Cohort 9 is joined by Cohort 1 and Cohort 5 etc etc).
In the attached example in Month 1 there are 10 sales (therefore 10 customers). In month 5 there are 241 sales so there will be 10 customers from cohort 1 and 231 from cohort 5. I am trying to write a formula that will automate that calculation but struggling at the moment - any help much appreciated.
Thanks
Richard
I'm confused. Do you need the calculation for the second group of Cohorts? In using the example of Cohorts 1 and 5 where you have 231 for Cohort 1 you want 10 and where you have 241 for Cohort 5 you need 231?
