This spreadsheet forecasts NCF membership renewal rates for the coming year, based on historical renewal data. The simplest way to forecast renewals is to apply the overall renewal rate to the current number of members.  However, since there are significant differences in renewal rates between new and long-term members (25% compared to 75%), a more accurate forecast can be developed by dividing the membership into cohorts based on the number of years a person has been a member.  The probability of each cohort renewing is determined by looking at the historical data, and then that probability is applied to the following cohort to arrive at a forecast for the coming year. Obviously renewal rates can be improved or degraded by changes of policies or conditions. Renewal revenue is also cohort-dependent (longer-term members tend to donate more), so a similar forecast is made for donations.  Donation data was last analysed in 1998.  The general 'shape' of donations versus cohort is assumed to not have changed, but the magnitude has been adjusted by using recent donation statistics.

The tab below labelled "Analysis" contains the full analysis.  The renewal forecast for the coming year is highlighted in green at the bottom of the "Analysis" worksheet.  There is a revenue forecast (which EXCLUDES revenue from new registrations) for both the 'pay-as-you-like' policy (in green) and the 'minimum contribution required' policy (in yellow).

Jim Elder

August 2000