To illustrate the way this report works, consider an example of running it using the date filter of "last calendar year", using the year 2012.  The report attempts to project totals for the three following calendar years, so in this case - 2013, 2014, and 2015.

First, the report determines the renewal rate by taking the number of non-drop membership transactions that occurred in the date range (year 2012) and dividing it by the number of membership transactions that have an expiration date that falls within that date range. 

At this point, the report also calculates the sum of revenue broken out per program per level term per month during the date range, as well as the month, year, and term ID values. The amounts are displayed as the values for the current year in the report. The term ID is used in the final calculations to determine the amounts to use to figure out the expected totals for each month in the following years. The month and year are used to help track numbers during the calculations for the number of expirations and the final totals.

The report then determines the number of renewals for each of those years in the following way:

  • Year 1 (2013) - any memberships with an expiration date in that year
  • Year 2 (2014) - any memberships with an expiration date in that year + (renewal rate * any memberships with an expiration in year 1 that have a 1 year term)
  • Year 3 (2015) - any memberships with an expiration date in that year + (renewal rate * any memberships with an expiration in year 2 that have a 1 year term ) + (renewal rate * any memberships with an expiration in year 1 with a 2 year term)

Finally, we calculate the money expected for each of those years in the following way:

  • Year 0 (2012) - amount retrieved above when determining renewal rate
  • Year 1 (2013) - renewal rate * year 1 expiration total * membership level term price
  • Year 2 (2014) - renewal rate * year 2 expiration total * membership level term price
  • Year 3 (2015) - renewal rate * year 3 expiration total * membership level term price