There has to be a better way? confusing myself...

[mz123]

I am trying to forecast orders for a subscription product.

I have estimations on the return rate for each period in Sheet1, such as:
Return before 1st rebill: 5%
Return before 2nd rebill: 7%
Return before 3rd rebill: 5%
and so on

I also have estimations for the % of people that will continue to rebill each period in Sheet 2:
1st Rebill: 70%
2nd rebill: 50%
3rd rebill: 20%
4th rebill: 5%
5th rebill: 4%
and so on.

So... orders that come in during period 1 would use the appropriate return and rebill rates -- this part is simple.

New orders that come in during period 2 would be "staggered" and the calculations would be 1 period before the orders that came in during period 1.

The complexity increases each subsequent period.

I'd like to show it in simple terms:
.................................P1...... P2 .....P3..... P4
New Orders
# Cancelled
# Rebills
Total Orders in Period

How do I "follow" the remaining number of rebills that started in period 1 and keep them separate (for the purposes of calculating) from orders that began in Period 2?

 

[Andrew Fergus]

I recommend you break the workings for each month into separate rows and then the part you want to see is the sum of the parts.

For instance in a workarea below (or hidden or elsewhere) you would create the staggered effect but on a relative basis, for instance you would have a single row for orders in the month (e.g. column G row 10 might be for Feb). Then the row below that would have the impact from the previous month (i.e. column G row 11 would be Feb impact of Jan orders) and the row below that has the impact from 2 months ago (i.e. column G row 12 would be Feb impact of Dec orders) and so on. This way if you want to look out say 10 months on rebills then you will only need 10 rows of workings.

Andrew