I've done a few models that forecast revenues based on looking solely at new customer cohorts and their lifecycles. There are all kinds of ways to build a spreadsheet that does this and I'll talk about some techniques I've done below:
Manufacturing per Customer Re-Purchase - On this one I define high level variables like average selling price, expected month 1 customers, monthly sales growth in each year over 5 years, percentage of customers that will re-purchase, average length (in months) a customer will re-purchase for.
I also added three licensing options on this model based on the average monthly sales for up to three licensing agreements and the average revenue per sale on the licensing deal.
It also has an interest view for inventory. The user can define up to 10 tranches where the cost to acquire inventory is entered and based on the period length the model calculates the total cost based on the defined average cost per unit.
There are some general assumptions about debt and a final output summary for monthly cash flow.
I believe this was one of the very first models I ever built that had an option for cohort retention and modeling with matrix style configurations.
You can buy this forecasting template here if you want to play with it.
More Models Driven by Customer Behavior
- Customer Retention and Spend Patterns - This one focuses on calculating the revenue value of customers based on a define curve for their retention over time and the average amount spent over time. It is a pure revenue model and doesn't dive into things like opex, debt, or cash flow / IRR.
- SaaS Pricing Model - This template focuses on modeling the ideal SaaS customer and you can create a forecast for what the cash flows look like for an individual customer based on key configurations like average retention rate for the contract period (renewal rate), the average revenue per contract, the average length per contract, the average customer acquisition cost. You will get an IRR per customer metrics as well as an average customer lifetime value and more. It is really good for looking at different kinds of subscription model customer performance goals.
