11/26/2018
Enterprise
Introducing the Lightspeed standardized e-commerce model
Sharing our attempt at the simplest possible financial template for your e-commerce startup
For the uninitiated, building an e-commerce financial model can feel a bit like constructing a Rube Goldberg machine. Named after a prolific early 20th century cartoonist, these devices famously accomplished simple tasks in the most complex way possible.
In this case, the “simple task” at hand is calculating revenue and cash flows over a couple years, but the inputs to and structure of the model can vary greatly. Without a standard model, results are often difficult to justify to others and can be unreliable. Too many founders end up with a model that’s about as easy to follow as an OK Go music video, but a lot less enjoyable:
There must be a better way!
Lightspeed made its first e-commerce investment in Blue Nile nearly 20 years ago. Since then, we’ve been fortunate to partner with a number of online commerce innovators from early days, including those below:
Nearly all of them struggled to forecast financials at the earliest stages. Part of that struggle is the inherent uncertainty in any early stage business, but another major factor is the structure of the model used. Some forecasts rely on arbitrary assumptions, like monthly revenue growth. We think there’s a better way to forecast; it all starts with cohorts.
Cohorts are ground truth for e-commerce.
Cohorts are the atomic unit of analysis and forecasting at Lightspeed.
We’ve previously written about how we use cohorts to estimate lifetime value (LTV), and how graphs of cohorts (aka “spaghetti graphs”) are the best tool to understand long-term customer engagement. We haven’t seen anyone provide a solid framework for integrating cohorts into a forecast; so, my partners Jeremy Liew, Natalie Luu, and I built one with fictitious data.
You can download it here.
It all starts with cohorts.
We start on the “Cohort Model” tab with assumptions on monthly customer acquisition spend and customer acquisition cost (CAC) through paid channels. Together, they yield the number of new paid customers per period. In a healthy business with good word-of-mouth, new paid users tend to drag a few organic users along with them. We account for this “organic halo” by assuming a ratio of organic to paid users for each cohort. Adding paid to organic users yields the total new users in the cohort.
These new users then follow a standardized cohort behavior. After the month of first purchase (=month 0), they appear again along a baseline retention curve that levels off to a steady state. Note the model uses “in month” retention and not cumulative retention. “In month” retention is equivalent to the percentage of the cohort which shows up to purchase in a given month. We also allow for some improvements in that baseline retention for subsequent cohorts. You can see how the cohorts stack up in the classic “triangle view” below, with month 0 justified left:
The magic happens in the column marked “Total Transactions,” which executes a sum along the hypotenuse of the triangle. This crucial step is the linkage between “cohort time” (i.e. month 0, 1, 2, etc.) and “calendar time” (i.e. Jan 2018, Feb 2018, etc.). For example, the total transactions in Jun 2018 is the sum of month 0 transactions for the Jun 2018 cohort, month 1 transactions for the May 2018 cohort, month 2 transactions for the Apr 2018 cohort, and so on. I’ve highlighted the relevant cells below:
You can see some other calculated values in this tab as well: weighted average CAC (aka “blended CAC”), 3-year cumulative transactions, and repeat rate. 3-year LTV and LTV/CAC are also calculated here, but require input from the P&L. We also placed the average order value (AOV) assumption on this tab, which will play into the calculation of revenue on the P&L tab.
Constructing the P&L
We built the Income Statement, or P&L, on the tab called “Monthly Model.” Under “Key Operating Metrics”, we flowed through Total Transactions and AOV from the “Cohort Model” tab. The product of these two rows creates the Gross Product Revenue line item at the very top of the P&L.
From here, we added various assumptions about shipping revenue/costs, return/discount rates, and COGS components — each as a % of AOV. To model Operating Expenses, we included assumptions for monthly headcount and salary, as well as other line items for overhead. We admittedly focused less on these fixed cost elements, which are harder to model in the abstract.
Rolling it up
The “Summary” tab features simple quarterly and annual rollups that make the forecast easier to read. You may want to include these tables in a pitch deck or something you present to your board.
Extra credit? 🙂
We hope this rudimentary e-commerce model gets you from zero to one in terms of financial planning for your startup. Extending this P&L model to a Balance Sheet and Cash Flow Statement is left as an exercise to the reader! If anyone does this, please send it over to ataussig@lsvp.com. If it’s really good, we’ll update this post with the full three statement model.
Similarly, if you find errors in the model, or have a better approach, feel free to send me a note. We can’t promise to get back to everyone, but will do our best to crowdsource the best possible model.
A special thanks to Jeremy Liew, Natalie Luu, Janou Gordon, and my other Lightspeed team members who worked on and reviewed this model.
Update (12/4/2018)
Wow! A lot of you seem to find this useful. Thanks for all the comments.
Shout out to Kelsey Ditto at Affirm for spotting a small error on the “Summary” tab, and to Steven Cruz at Plated for expanding our model to include a balance sheet and cash flow statement. Steven’s version allows you to play with assumptions around cash conversion cycle, which is essential for any e-commerce business.
Appendix: a detailed user guide for our sample model
I. Cohort Model tab:
- This model is based on a series of stacked cohorts.
- Each row represents the cohort that starts in the month in column A.
- We make assumptions about Ad Spend (column D) and Paid CAC (column E). Dividing these two gives us the number of Paid New Buyers (column F).
- We then make an assumption of the ratio of paid to organic new buyers and that gives the number of Organic New Buyers (column H). Total New Buyers for that month (column I) is paid + organic.
- Column Q is the number of new buyers that month (copied from column I). Then each column after that is the number of transactions from that same cohort in each subsequent month. For example, Q18 shows how many new (aka first-time) buyers there are in Sept 2018. R19 shows how many transactions there are from that same set of buyers (who bought for the first time in Sept 2018) one month later (in Oct 2018). R20 shows how many transactions there are from that cohort two months later (Nov 2018) and so on.
- We calculate the number of “month n” (Mn) transactions by multiplying the Number of Original Buyers (column Q) by the Cohort Retention Assumption (Row 5) and then modify this by a retention change factor (1+ column C). We included the retention change factor to allow retention to improve (as product improvements kick in and we have more to sell) or deteriorate (in which case column C would become negative), perhaps because we have saturated our core user base and are now attracting less high quality customers. And of course we round to the nearest integer because you can’t have fractional buyers!
- This model holds true for every cohort of new buyers starting from Jan 2018. However, we treat the set of new buyers who bought before Jan 2018 a bit differently because we’re lumping multiple cohorts all together, so they won’t have the same cohort retention as a regular cohort. That’s why we use row 5 to calculate the number of transactions in a month instead of row 4 — it is a flatter curve because it’s a mix of multiple cohorts.
- The Total Number of Transactions (in purple, column K) for any given month is then the number of new buyers in that month + the number of M1 buyers from the month before + the number of M2 buyers from two month before + (you get the idea):
- If you double click on any cell in column K you can see how this sum looks like a staircase as it traverses each cohort.
- If you sum the total number of transactions in a single row from new buyers (M0) through M35, you get the total number of transactions from that cohort over three years (column K).
- To get 3-year LTV (column L), you divide the Total Number of Transactions (column K) by the number of New Buyers (column Q) and multiple this by the Average Order Value (column B) x the average contribution margin (looked up from the corresponding month in the Monthly model). Strictly speaking, we should vary AOV and contribution margin over the period, but this is good enough as an approximation.
- By dividing the Total Number of New Buyers (column I) by the Ad Spend (column D), you get Weighed Average CAC (column J). By dividing 3-year LTV (column L) by CAC (column J), you get The LTV/CAC ratio (column N).
- Repeat Rate (column O) is just the number of transactions that are not from new buyers, divided by the Total Number of Transactions.
II. Monthly Model tab
- Gross Product Revenue (line 9) is Total Number of Transactions (line 50, transposed from the cohort model tab) x Average Order Value (line 53, also transposed from the Cohort Model tab).
- Forward and return shipping are calculated by the Average Shipping Charge (column A) x the number of relevant orders shipped.
- If you’re doing free shipping and returns, you can just set shipping charge to 0 in cells A10 and A11.
- The one place you may choose to overwrite formulas with assumptions is in the fixed overhead links (Rows 40–42). If you want to make a more fine grained month-by-month estimate of costs (e.g. if you know you are expanding to a bigger office with higher rents, if you have a PR push planned for a particular month etc), you shouldn’t hold the numbers constant as the model currently does. If you do make those changes, I suggest that you change the font of any cell that you overwrite to blue and give it a yellow background to match the other assumption cells. That way you can easily find hard coded values and change them later on.
- Returns is calculated in row 52 as a %(column A) of Total Transactions.
- Gross revenue is a sum of product revenue and forward and return shipping revenue.
- Net revenue is Gross Revenue less Discounts and (Markdowns and Returns).
- All the items in the Cost of Goods Sold (COGS) calculation are a product of the Number of Transactions and the cost per line item in rows 69 to 74. These in turn are calculated as a percentage of AOV from lines 83–88. The one exception is that product costs are offset by a certain recovery factor (line 94) x the number of returns x unit product costs. This reflects that there will be some costs to remediate returned products before they can be sold again, so you don’t get to offset product costs with the full cost of returned goods.
- Gross Profit is obviously Net Revenue less COGS.
- Personnel costs are calculated as salary x number of FTEs (lines 59–64) for each month.
- Personnel costs (line 37) are a ratio (column A) of salary and represent all costs variable to headcount, e.g. health insurance, travel & entertainment, 409A etc.
- Ad Spend (line 39) is transposed right from the assumptions on the Cohort Model tab.
- Other G&A line items are either held constant (column A) or there is also a variable G&A factor to account for other expenses that will grow with sales.
- Operating income is Gross Profit less Operating Expenses.
III. Summary tab
- This is just quarterly and annual rollups of the monthly numbers.
IV. How to customize this model to your company
- Only change assumptions which are in blue font on a yellow background. Everything else is a formula. Black font is used for formulas calculated on the tab and green font is used for numbers pulled from another tab.
- The assumption numbers we put in are for testing purposes only. You should replace them with your own real data.
- To calculate your own cohort retention numbers (lines 4 and 5) refer to this blogpost and the linked Google Doc. For Line 5, you’ll need to make some adjustments to your cohort retention calculations to reflect that these are a combination of multiple cohorts so will flatten out relative to a true monthly cohort
- If your e-commerce company is pre-launch, for those months before you are selling, you can zero out Ad Spend (column C). If your e-commerce is post-launch, use your actuals instead of assumptions for the months that you have data.
Authors
