and the distribution of digital products.
Steal My Subscription, Revenue Forecasting Model (P.S.: It Tracks Churn!)
\ Welcome Back!
\ Alright, let’s get right to the good stuff—subscription revenue forecasting. Imagine being able to predict not just your subscription revenue today, but also see exactly how it’ll grow over time while factoring in churn, market reach, and pricing tiers.
\ That’s the magic behind this little beasty of an ARR forecasting model I built on Google Sheets (ARR stands for Annual Recurring Revenue).
\ This free tool will guide you through every detail, from market sizing to lesser-known gems like backcasting, which projects backwards from a future goal to help you map out your growth journey month-by-month.
\ Whether you’re aiming for consistent MoM growth or trying to hit your SOM target in a year, this model has you covered.
Let’s dive into each piece of this puzzle.
:::tip Steal my Subscription Forecasting tool
:::
\
Market Sizing – TAM, SAM, and SOMTo get accurate forecasts, we start with market sizing.
Think of TAM, SAM, and SOM as the layers of your total market potential, progressively narrowing down to the slice you can realistically achieve:
Total Addressable Market (TAM)TAM is your biggest possible audience—not the dream.
\ In this sheet, TAM is the maximum revenue you could make if you had 100% market penetration for your ICP. It’s a theoretical number and a good way to visualize what’s possible.
\ Input your TAM estimate in cell C9, considering factors like the global or regional demand for your product type.
SAM represents the portion of TAM that you can actually access with your current resources.
\ It accounts for realistic market conditions—things like geographical constraints, your product’s specific target audience, and any other factors that would prevent you from serving the entire TAM.
\ Input your estimated SAM percentage in cell E10 to get a clearer view of your attainable slice of the TAM pie.
SOM is where we get real.
\ SOM tells you the actual, attainable market size based on your budget, brand awareness, and sales reach over a set time.
This helps you map out an ARR target grounded in reality rather than wishful thinking (if you’re pitching to a VC, forget everything I say here and use the TAM numbers—JUST KIDDING!).
\ Input SOM in cell E11.
You’ll use SOM as a goalpost in the backcasting section, which we’ll explore shortly.
\ These market sizing fields are designed to provide a data-backed approach to understanding market limits and opportunities.
Once TAM, SAM, and SOM are filled in, they’ll flow into the ARR projections, giving you a structured forecast.
\
Pricing – Defining Your TiersIn cells B5, B6, and B7, you can name your subscription tiers.
\ In cells C5, C6, and C7, you can set your subscription pricing for each tier.
\ Each tier represents a different level of customer value, and you’ll notice there are default values set up for each (e.g., $49 for Basic, $199 for Starter, etc.).
\ These prices are, of course, placeholders—you should adjust them based on your actual pricing strategy.
\ Each tier can be individually populated (in cells E13, E14, and E15) with customer volume, so you can explore how shifting subscriber numbers between tiers impacts overall ARR.
\ These pricing tiers give you flexibility, allowing you to experiment with subscriber numbers across levels and see the impact on ARR.
Crunching Customer Lifetime Value (CLTV)Welcome to the Customer Lifetime Value (CLTV) corner of the ARR Forecaster sheet—a powerhouse tool that lets you see what each subscriber is worth and how to make those numbers work for you.
\ This part of the model pulls together metrics like Total Subscriptions (your SOM), Average Revenue Per User (ARPU), Customer Acquisition Cost (CAC), and Churn Rate to spit out CLTV, giving you a full breakdown of your subscribers’ lifetime revenue. Here’s how to make the most of it, cell by cell:
1. Total SubscriptionsTotal subscriptions is automatically filled into cell C26 from your Serviceable Obtainable Market (SOM).
\ This is your current or goal subscriber base—the crowd you’re banking on to drive those revenue numbers. Getting a good estimate of SOM helps anchor the whole CLTV calculation, so keep it realistic.
\ It’s the foundation for knowing your true revenue potential.
Next up is ARPU (yep, another acronym), which stands for Average Revenue Per User.
\ This is automatically calculated in the sheet as a weighted average across all three subscription tiers in the sheet.
\ Find ARPU in cell C27.
Think of it as the average monthly spend per user, giving you a bird’s-eye view of each customer’s value across all your subscription tiers.
ARPU is powerful because it smooths out the ups and downs across all customer types.
3. CAC!We love CAC (Customer Acquisition Cost).
\ This sheet spits out a CAC calculation when you fill out your specific expenses in cells E27:E29:
\
- Fixed Costs (E27): These are your standard, can’t-escape costs like rent, salaries, or servers.
- Marketing Spend (E28): Throw in what you’re spending to bring in those new subscribers through ads, promos, or campaigns.
- Operating Fees (I have shopify fees as an example) or any platform-specific fees (E29).
\ As soon as you get these expenses in, CAC will pop up in E26.
\ This tells you the average cost of bringing in a new customer, so you’ll know if you’re over (or under) spending on acquisition.
:::info Note: underspending on acquisition could be a huge reason you’re not growing. Bump those numbers up kids.
:::
\
4. Gross MarginOnce your CAC and ARPU are sorted, the sheet calculates gross margin for you in cell C28.
\ This percentage shows how much cash you’re keeping after direct costs, giving you an idea of profitability per subscriber. A healthy margin means you’re making more than you’re spending on each customer—keep this one within your industry’s acceptable margins, and you’re golden.
5. Monthly Churn RateReady to face churn?
\ Input your monthly churn rate in cell C29.
\ It’s a crucial metric since it tells you how many subscribers you’re losing each month. Churn can drag down ARR faster than you’d expect, so take a cold, hard look at this rate.
\ Lowering churn means customers stick around longer, boosting your lifetime revenue.
6. Average Lifetime (Months)Once you plug in churn, average lifetime (in months) automatically calculates in cell C30. This metric gives you the expected duration each subscriber sticks around, based on your churn rate.
\ Lower churn means longer lifetimes.
7. Customer Lifetime Value (CLTV)Now, here’s the big one—CLTV (cell C31). This number is the total revenue you can expect from a single subscriber during their time with you. This metric helps you see just how much each customer is worth from start to finish.
\ A high CLTV means each subscriber has long-term revenue potential, giving you the confidence to invest more in acquisition or retention.
8. Total CLTVFinally, we’ve got Total CLTV, which is the total expected revenue across your whole SOM. Think of it as a sum of all the individual customer lifetimes combined.
\ It’s the big-picture view of your revenue potential, assuming you can hold onto all those subscribers for their expected lifetime.
By nailing down these numbers, you get a clearer view of your ARR, CAC, and CLTV, all of which are cornerstones for building a profitable SaaS model.
\ With each metric feeding into the model’s forecasts, you’re setting yourself up to see exactly where the growth opportunities (or leaks) lie.
\ Congrats.
You’re now officially a nerd (a potentially very profitable nerd).
\
ARR Forecasting – Plugging in the DetailsOnce your market sizing and CLTV metrics are filled, it’s time to let the model do its thing.
\ Here’s what the ARR forecaster tracks for you:
Subscription Revenue: Based on your input for each pricing tier, the sheet projects total subscription revenue across 12 months.
Growth Rate (cell F37): You can input growth assumptions—say, 5% MoM growth—to visualize how your revenue changes over time. The model recalculates ARR based on these assumptions, allowing you to gauge various growth trajectories.
Now, here’s where things get interesting.
A lot of people look at forecasting forward, but few think to backcast—that is, working backward from a future goal to define your present-day requirements.
\ Backcasting is especially valuable if you have an ambitious SOM target you want to reach in, say, 12 months.
In the Backcast section of the tool, input your desired SOM and set a goal Month over Month (MoM) growth rate.
The model will automatically calculate the exact number of new subscribers needed every month to reach that SOM goal, even factoring in churn.
\ This approach is incredibly useful for planning because it lays out a clear subscriber roadmap:
- Starting with SOM: Identify your SOM goal and set it as your destination.
- Defining MoM Growth: Set a growth rate based on your team’s capabilities and market conditions.
- Accounting for Churn: With churn factored in, backcasting shows the number of monthly subscribers you need to not only grow but also retain a portion of your customer base.
\ Let’s say you’re aiming to achieve SOM in 12 months with a 5% MoM growth rate and a 3% monthly churn. The Backcast section will break down, month by month, how many new subscribers you need to add to hit your target by the end of that period.
\ It’s a pragmatic way to reach your goals and keeps you on track, no matter how challenging churn might be.
Using This Model for Dynamic ForecastingAs you play around with the ARR Forecaster, here’s what you can achieve:
\
- Explore Scenarios: Tweak market size, subscriber numbers, and pricing tiers to understand various ARR outcomes.
- Analyze Churn Impact: Adjust churn rates to see how quickly attrition can erode ARR and test how different retention strategies might affect revenue.
- Test Backcast Assumptions: Try different MoM growth rates and churn scenarios in the Backcast section to ensure that your SOM goals are feasible.
\
:::tip Steal my Subscription Forecasting tool
:::
\ Well. That’s all.
K bye!
\n
\ \