Forum Discussion
Formula help - how to build a formula for a cohort model
Hi all, I could really do with some help. I am building a sales forecast cohort model which i want to take the previous months units and apply a retention % on. I am currently using this form...
I will assume that you can extend the rate table on the logic tab to include a 13th entry (for month differences of 13 or more). To use that last entry (as well as the first 12), your calculation formula must then reference $A$20 instead of $A$19. And… The crucial change is then to limit the second INDEX argument to 13, by wrapping the YEARFRAC * 12 calculation inside either an IF function or (as I did) a MIN function:
=ROUND(O14*OFFSET(Logic!$A$7,INDEX(Logic!$A$8:$A$20,MIN(INT(YEARFRAC($A14,P$1)*12),13)),1),0)
I am attaching a bare-bones spreadsheet that you can experiment with. Cells B15 and B16 on the Calcs worksheet are not needed, but help to see what is going on, the latter showing the "adjusted months".