Unit Economics sheet provides a place to analyze fundamental unit economics by calculating margins, breakeven, and LTV (lifetime value, or customer lifetime value) based on inputs of recurring and transaction (one-time) revenues and cost of sales, churn rates, customer lifetime, and more.
This is also available for free as a stand-alone model, available at Unit Economics Forecasting for Excel and Google Sheets →
How to use
Unit Economics sheet is detached from the rest of the model by design, to make it easy to use for analysis purposes. It does not draw on inputs elsewhere in the model, and can be replicated (to model unit economics of multiple segments or products independently) or deleted from the model easily.
How it works
The basic input structure is to input per-month or per-unit revenues and cost of sales, which is then used to calculate gross margin on a recurring (monthly, or the time period relevant to your revenues and cost of sales inputs) and transaction (one-time) basis. Using that and additional assumpions around growth rate in gross margin, churn, customer lifetime, discount rate, and acquisition and retention costs, the model automatically created a forecast of revenues, expenses, and margin over time. This calculates customer lifetime value (CLTV or LTV) based on the inputs for acquisition and retention costs. The model also creates a chart that allows you to easily see when acquisition costs are recouped - when breakeven is reached - and shows how the lifetime value is earned over time.
There are a few sections for inputs on the sheet. At the top of the sheet is an toggle to hide or show the column notes, select "show" to show the notes for each input, select "hide" to hide the notes.
Use the labels to define what the revenue is, for your own understanding, then input the amount and select "recurring", "one time", or "transaction" from the dropdown. That selection defines whether to model it as a recurring revenue stream every period (most people will think of this as monthly) or a one-time transaction. (You can have this transaction repeat through the churn and average number of purchases per year assumptions.) These could reflect average revenue per subscriber, per client, per order, etc.
The revenues here reflect recognized revenues. If you bill for months in advance, you would want to input the monthly revenue here, and use the input for billing cycle to set how many months are billed at once. For an annual contract, for example, you would input the average monthly revenue here (total annual contract revenue / 12 months), and then in the inputs for billing and churn input "12", to reflect twelve months billed at once and churn occuring every 12 months. For an annual contract billed monthly, inputs would be the same, except the billing input below would be "1" for 1 month.
Use the labels to define the cost of sales, then type in the % of revenues that are treated as cost of sales and select "recurring" or "transaction" from the dropdown. Enter as positive or negative.
Cost of sales can be a difficult concept to express, given that many subscription and SaaS companies include costs like customer support in their cost of sales that are difficult to think of in a per unit basis. The input is a percentage here to make it easier to make estimates based on benchmarks of what companies spend on cost of sales.
Optional, used in case you offer discounts for first subscription or first purchase, and you can enter the discount separately for recurring and transaction. For recurring, the discount is recognized over the billing period (for revenue recognition), for transactions, it is treated as a one-time discount on the first transaction. Enter as positive or negative.
Churn and Growth
These inputs are used to set the churn and growth inputs for recurring, one time, and transactions, if used.
- Recurring - Period for billing, # of months. Defines the billing cycle, so that cash collection can be separate from revenues. By default this equals the period for churn, but you can overwrite and change it.
- Recurring - Period for churn, growth, # months. Define the length of a cycle for churn. Annual = 12, monthly = 1
- % Growth in Gross Margin per unit, linear annual growth. Optional, if it applies, input an annual linear growth growth if the gross margin per unit changes over time. A common use of this is for "land and expand" strategies where average revenues (and thus average gross margin) increases as amount of time a customer remains a customer increases. Enter a positive number for increases in growth margin, a negative number for declines in growth number.
- % Churn, every N months. Percentage that do not renew, used to create a logarithmic churn curve where the churn decreases per period. "N" is set by the input for recurring, period for churn set above.
- Average Customer Lifetime, recurring: calculated from the churn inputs, number of months
- Timing, One time. Months after acquisition when one-time occurs; this will be zero for upfront, one-time fees, perhaps assume 12 for one-time fees that happen after 1 year
- Transaction - Period for churn, growth, # months. Define the period between repeat transactions. For example, for something that is purchased once a year, input "12", or something purchased quarterly, input "3".
- % Growth in Gross Margin per unit, linear annual growth. Same rationale as the input for recurring.
- % Churn, every period. Percentage that do not renew, used to create a logarithmic churn curve where the churn decreases per period.
- Average # Repeat Transactions per year. Average lifetime one time purchases, before churn.
- Average # of Lifetime Transactions. Calculated based on repeat period and churn.
Discount Rate, Acquisition and Retention Expenses
- Discount Rate: annual rate. Used to discount future growth margin to value today, input a weighted average cost of capital (WACC) applicable for your industry. This is optional, used if you want to follow the methodology from What’s your TRUE customer lifetime value (LTV)? – DCF provides the answer
- Customer Acquisition Cost: one-time customer acquisition cost (CAC) assumed to be in the first period in the model
- Cost of Retention and Expansion, Recurring: costs incurred to retain recurring revenues
- Cost of Repeat and Expansion, Transaction: costs incurred towards repeat transaction revenues
The model then create a couple calculations:
- K, for recurring. K = (1- churn) * (1- discount rate) , and uses the formula from What’s your TRUE customer lifetime value (LTV)? – DCF provides the answer
- K, for transactions
- LTV: this uses the formula from What’s your TRUE customer lifetime value (LTV)? – DCF provides the answer, to use discount rate (optional), and growth in average revenue. LTV is calculated separately for recurring, one-time, and transaction revenue streams, then summed to total LTV
- LTV / CAC: lifetime value divided by CAC, to help measure profitability performance
- CAC Payback: # of months to payback the customer acquisition costs
The model then calculates an overall LTV, and then creates a table of margin, acquisition and retention costs, and discounted gross margin by month, for a 48 month period, to help communicate the average cash flows on a per-unit basis. This is in a hidden section that can be opened to see the calculations in detail.
One common modification is inserting or deleting rows in the revenues and cost of sales sections to change the inputs used to calculate gross margin.
One common usecase of this sheet is to replicate the sheet to create multiple unit economics sheets for different customer sets, products, business segments, or any segment-based views of the business.
Unit Economicssheet is detached from the core model in the Standard Model, it is easy to edit the inputs and create different views without distrupting the core of the model.
Questions, contact me.