Here is a more extensible approach that can easily be expanded to
accommodate additional products.

Assume your sales forecasts are in a worksheet called Sales with the
month in row 1, the product ID in column A, the subscription duration
in column B.

Set aside row 2 for a helper row, which you can hide later.

Set aside as many columns from C over as you will need for your
forecast of open subscriptions. Let's say that you want a 12-month
forecast. That will occupy as far as column N.

Leave column O blank, as a separator column.

Enter your sales forecasts starting in column P, ensuring that your
first month of sales is at least N months prior to the first month for
which you want an open subscriptions calculation, where N is your
longest subscription.

C1: Enter the first month to be calculated, in exact same form as
months are represented in Sales area (column P and over). The
following months can be derived by formula from this if you wish, or
set up as constants. If you derive them by formula, you can just change
the first month (C1) to move your forecast to start at a different
month. See

http://www.cpearson.com/excel/datearith.htm

for how add a month to a date. You could also have set up C1 as a
formula to automatically generate the current month, or next month.

C2: =MATCH(C1,$O$1:$IV$1,0)-1 and copy across for all months, 12
months or however long you are calculating subscriptions

A3 and down: first product IDs

B3 and down: Subscription duration for product in column A

D3: =SUM(OFFSET($O3,0,C$2+1-$B3,1,$B3)) copy across for all forecast
months (to column N if 12 months) and down for all products.

You should now have the open subscriptions for each month. You can sum
them as you wish, either in total or by duration (with SUMIF). You can
color the subscription area to differentiate it from the Sales entry
area. You should also protect the sheet, unlocking only the sales
entry cells.

One question I had is whether a sale is counted as a subscription in
the month of sale or the month after. If it is the month after, then
you should change the -1 in row 2 to -2.

I had started with two sheets, one for Sales and the other for
Subscriptions but that has the drawback that you could easily insert a
new product in Sales and neglect to insert it in Subscriptions. This
way, a new product insertion affects both areas simultaneously since
there is just one row per product.

You can also delete columns to the right of the separator column (O)
without affecting the formulas.

Hope this helps

Declan O'R