Multiple phasing

[jayp2104]

Hi Everyone

I need help with multiple phasing.

A typical customer will purchase as follows
Month 1 - 0.5
Month 2 -1
Month 3 - 1.5
Month 4 - 2
Month 5 - 2.5
Month 6 onwards 3

But we can have new customers that can start in month 3 which will have the phasing from month 1 (0.5) while the original customer in month 3 will be at 1.5 therefore the result will be 2

in Month 4 we can have 6 customers in total
Customer 1 who started in Month 1 will be at 2
Customer 2 who started in month 3 will be at 1
and the 4 new customers in month 4 will be at 2 ( 0.5*4)
total will be 5 in month 4

and the same goes for all new customers going forward.

How would you formulate this?

 

[kvsrinivasamurthy]

Data given in image and in explanation does not match. unable to understand. Give correct Data and expected result.

 

[Trevor G]

Welcome to the forum.

Can you explain your multiple phasing please? Is there another sheet that holds the data as the sample doesn't help. How do you identify the customers as well as new customers. I think there's a lot of missing info which I'm sure your familiar with but we aren't!

 

[jayp2104]

Hi Trevor

This is a forecast model so we are trying to forecast number of test that we should get

The idea is that each customer will have a 1 month ramp (0.5 to 3) than stay stable at 3 after that.

For each new number in the subsequent months will have the same ramp, however their starting months can be different.

I am building the forecast model from scratch and need to forecast the tests that we can anticipate but adding new customers in each month and build a 3-5 year plan.

 

[kvsrinivasamurthy]

For 365 version. In B2.

=LET(a,IFERROR(MONTH(B2:B8&0),""),b,MONTH(C1:N1&0),st,P2,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(r,c,IF(INDEX(a,r)="","",IF(INDEX(a,r)>INDEX(b,c),"",(INDEX(b,c)-INDEX(a,r)+1)*$P$2)))))

For other version. In B2 copied to full range.

=IF($B2="","",IF(MONTH($B2&0)>MONTH(C$1&0),"",IF(MONTH($B2&0)=MONTH(C$1&0),$P$2,B2+$P$2)))

 

[kvsrinivasamurthy]

Slightly modified for 365 version, In B2

=LET(a,IFERROR(MONTH(B2:B8&0),""),b,MONTH(C1:N1&0),st,P2,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(r,c,IF(INDEX(a,r)="","",IF(INDEX(a,r)>INDEX(b,c),"",(INDEX(b,c)-INDEX(a,r)+1)*st)))))

NOTE: Month names are entered in text format.