Forecasting revenue for a membership

Few businesses are as forecastable as a membership. Most of next month's revenue will come from people who are members today. That predictability is one of the model's great strengths, yet many owners never use it. They plan by feel, hope for a good month, and are surprised when growth stalls.

A simple forecast changes that. With a spreadsheet and four inputs you can see where your revenue is heading, test what would happen if you changed your price or your marketing, and spot limits to growth long before you reach them.

The four inputs

• Starting members: how many paying members you have now.
• New members per month: your recent average, based on the last three to six months.
• Monthly churn rate: the share of members who leave each month, also averaged over several months.
• Average revenue per member: your monthly recurring revenue divided by paying members.

Use your own recent history for each input, not your hopes. If you need to review how the revenue figure is built, see tracking monthly recurring revenue; for churn, see calculating churn rate.

Building the model month by month

Each row of your spreadsheet is a month. For each month:

1. Start with the previous month's ending members.
2. Subtract members lost: starting members times the churn rate.
3. Add new members.
4. Multiply ending members by average revenue per member to get monthly revenue.

Take a hypothetical gardening club with 200 members paying $20 a month, gaining 30 new members a month and losing 5% a month. The figures are illustrative and rounded to whole members.

• Month 1: 200 − 10 + 30 = 220 members, revenue $4,400
• Month 2: 220 − 11 + 30 = 239 members, revenue $4,780
• Month 3: 239 − 12 + 30 = 257 members, revenue $5,140
• Month 6: about 306 members, revenue about $6,120
• Month 12: about 384 members, revenue about $7,680

Growth is steady, but notice that each month adds a little less than the one before. That is not a coincidence.

The ceiling your churn rate sets

As your membership grows, the number of members leaving each month grows with it, because churn is a share of a bigger base. Eventually the members leaving equal the members joining, and growth stops. You can calculate that level directly:

Ceiling = new members per month ÷ monthly churn rate

For the gardening club, 30 ÷ 0.05 = 600 members, or $12,000 a month. However long the owner waits, with these inputs the club levels off around 600. After two years the model shows about 483 members, and the remaining climb is slow.

This is one of the most useful things a forecast reveals. To raise the ceiling, the club can bring in more members each month or lose fewer. Cutting churn to 4% raises the ceiling to 750 members. Increasing new members to 40 a month at 5% churn raises it to 800. Comparing the effort each would take is a good planning conversation.

Scenarios, not a single number

A single forecast implies a certainty you do not have. Build three versions side by side:

• Cautious: fewer new members and slightly higher churn than your recent average, say 20 new members a month and 6% churn.
• Expected: your recent averages, 30 and 5%.
• Hopeful: what a realistic improvement could achieve, say 40 and 4%.

For the gardening club, those scenarios level off at about 333, 600 and 1,000 members. The range is wide, and that is exactly the point: it shows how much depends on the two numbers you can influence. Plan your spending around the cautious case, and treat the hopeful case as something to earn.

Annual plans, price changes and cash timing

• Annual members: model them separately. They pay in one lump and can only leave at renewal, so forecast renewals month by month based on when each annual term ends and your renewal rate.
• Price changes: a price rise often applies to new members first and to existing members later, if at all. Change average revenue per member gradually in the model rather than all at once, and consider whether churn might tick up for a while.
• Cash versus revenue: forecast revenue is not the same as cash in the bank. Annual payments arrive in lumps, and refunds and payment fees come out. If cash planning matters, add a separate cash row.
• Seasonality: if sign-ups rise and fall at predictable times of year, use your own past averages for each month rather than one flat figure.

Keeping the forecast honest

A forecast is most useful when you check it. Each month, enter the actual figures next to the forecast and note the gap. If you are consistently too hopeful about new members, lower that input. If churn is better than expected, find out why and protect whatever is causing it.

Your forecast also gives you a sound basis for goals. Rather than picking a round number you like, you can see what it would take to reach it, as discussed in setting realistic goals for your first year.

Your first forecast

1. Gather current members, average new members, average churn and revenue per member from the last three to six months.
2. Build a 24-month model using the steps above.
3. Calculate your ceiling: new members per month divided by churn.
4. Add cautious and hopeful scenarios.
5. Model annual members separately if you have them.
6. Each month, record actual figures against the forecast and adjust the inputs.