Subscription Brand Forecasting: Profit-First Template

[00:00:00]Most e-commerce brands have no idea on how their subscription business will actually scale. I'm Carlo O'Brien, co-founder of Store Hero, and in this video, we're going to build an e-commerce forecast specifically for a subscription business so you can scale with confidence and not just hope. Make sure to download our free e-commerce forecast template in the description below. Enter your own numbers and follow along as we go. Okay, so you've downloaded the template and we're ready to dive in. So ultimately, we have two tabs within our forecast template. Here we have the inputs and forecast itself and then we have the export CSV option where we could actually kind of clean up the data and do something with it. So ultimately we have two sections within this first um individual sheet. We have the assumption section where all the inputable values are all listed in yellow and then we actually have the generated forecast down below.

[00:00:49]Ultimately where we want to get to is we have our overarching business metrics for the level of revenue you know cost of goods marketing contribution margin operating expenses and net profit we want to achieve in each given month over the next 12. We have some percentage benchmarks on things like marketing, gross margin, net margin. And then from there, we have both our acquisition, our new customer and retention/re repeat customer metrics that we can make sure we're kind of managing and maintaining progress in both of those areas and get clear visibility on that as well. So just to start ultimately right at the top here, you can see we want to define our starting month. So very simply, you can change whatever month you want to start and that just adjusts the columns below. From there, we have our cost of goods percentage. It's set once and it applies consistently across the year.

[00:01:40]And really with cost of goods, what we want to do is make sure we're appreciating all of the variable costs or order level costs that it takes to get a product shipped out the door in the hands of that customer. So it's not only the product cost, it's product fulfillment, shipping, transaction fees, and that's already after accounting for things like discounts, refunds, stuff like that as well. So with that in mind, again, uh we want to make sure we're h, you know, not falling into the common trap of just treating product cost as that cost of goods. Unfortunately, we have those other elements to take into account as well. So if you use store hero, you'll see that number on your dashboard and you can easily grab that and plug it in. If not, we actually have an order profit calculator that can help you calculate your cost and percentage on a per order basis by entering in your individual values. So, I'll make sure to link it here as well. So, next we have our operational expenses. So, these are our fixed costs. They're not our order level. They're kind of business level.

[00:02:38]They're things that we don't necessarily expect to change as we scale and drive more orders. For example, we might have, you know, a salary for yourself. Maybe you'll have um somebody working part-time to help you as from an ecom executive perspective.

[00:02:53]Maybe there's a part-time team member.

[00:02:55]Maybe you have rent costs, you have staff, maybe you have rent costs, you might have subscription costs, but ultimately anything that's not marketing and it's not order level. Um so ultimately we want to make sure that we're generating enough profit from our orders to cover any of these fixed costs at a business level. So in this example again we have 185k that's split evenly across the year. From there from there we can jump into our repeat customer assumptions we're making. So ultimately as I mentioned at the start we're really trying to break down repeat and new customers treating them separately especially in the context of a subscription business that couldn't be more important. Typically, if we're looking at an e-commerce forecast for a business, we're maybe looking at their overall goals and looking at the seasonal trend and assigning responsibility to each month of the year. So, for example, if November is our peak alongside maybe a kind of a summer sale in June, we're going to look at, okay, what those months h look like as a percentage of the overall year and assign kind of an adjusted target to those months. With a subscription business, that's not necessarily the same thing. You're going to have a base of repeat customers that you're growing in incrementally on a percentage basis mixing between your new customers acquired offset by the level of churn or kind of drop off of any existing subscribers. And then from there on the new customer side that's going to be very much a function of okay how much are we spending and what return are we getting on that spend to continue feeding the pipeline and making sure uh we have a sustainable uh kind of track of customers we're acquiring that will then become repeats. Um so in the context of repeats there h the first metric we want to look at is last month's repeat customer revenue. So in the this context, let's say we're starting a forecast in January, we're going to look at December's repeat customer revenue. And similar to likes of a cash flow statement, um we're looking at the closing balance of the previous month. Whatever left is left in the bank account on the 31st of December becomes the opening balance for the 1st of January. And we try to treat repeat customers in a similar way. That base of repeat customers is your foundations that will then lead you to see growth in the number of months that that follow that. So, in this case, let's say we have a base of $50,000 in repeat customer revenue. You can get more advanced with this and model out one-time subscri uh repeat customer sales versus uh subscribers, but ultimately we we find that it's much better for people to start here. Um you're not getting too granular to the extent that you're kind of getting lost in the details. Ultimately, we first want to just understand what does what responsibility does repeat customer revenue have as part of the mix because whatever we can't do on the new customer acquisition site, we might hit a limit on the amount of customers we can you know acquire based on how much spend we can uh put to good use uh and get a good return on. Repeat customers overall need to make up the difference. So realistically that might by be by way of subscribers. It might be by way of utilizing our email list to drive maybe once off purchases and maybe re-engagements. So starting on an overarching basis, that's a good a better way we can think about it. If you want to see a more advanced version where ultimately you're looking at the uh new customers acquired in the previous number of months and based on the lifetime value and typical number of renewals of those customers, how that kind of trickles down over the course of the the next couple of months. H we have a version for that as well. So just please let us know from there. Once we have that base, we want to see what's the percentage growth over the course of the following 12 months. So in this model, you can see we're selling 5%. So what I would typically recommend is instead of, you know, looking at overarching sales kind of on a seasonal basis, maybe look at net churn or net re repeat customer revenue growth on a percentage basis. Um, you could do an if you don't see as many seasonal trends, if it's not maybe a health related uh kind of subscription business, maybe you don't necessarily see different uh a net change in in subscriber growth and and ultimately it's going to be much more of a function of maybe like an average of the last six months. In this case, we're saying 5% incremental growth. In this case, we can see, okay, if we're starting at 50K and we're compounding that 5% month on month, you can see in our repeat customer sales section, that's turning into 52.5, 60, 70, 81, and up to 85. So you can see So you can see in our repeat customer revenue section here that 50k compounding 5% month on month is turning into 52.5 60 70 77 81 and up to 85 just behind me there. So with that in mind we have a good base of how we can model out repeat customer revenue and we can actually see the cumulative value. Okay, if we can actually maintain that, that puts us in a position there whereby we're driving 800k of repeat customer revenue off the back of ultimately that that base of customers compounding in the way that we expect. Okay, the next element we have to factor in there as well is the repeat customer AOV. So the average order value of each of those repeat customer sales that may change at different times of the year. So we've given some optionality there to enter that month on month and you can adjust that and ultimately as we adjust these values the intention is it's going to adjust our overall model. So let's say for argument sake we're actually just starting with a slightly smaller base of repeat customer revenue. You can see that will compound in a slightly different way where ultimately we're going from 34 to 56k. And really in order to kind of take that ultimately what you'd want to do is kind of take that underlying number and understand okay what can we do from a lifetime value perspective to try to maintain that 5% or even increase it and see kind of a greater level of profit coming from those repeat customer sales. Okay. So we have that and now we need to move on to the next section which is around that new customer acquisition. We have our base of repeat customer sales and now we need to ensure that we're acquiring enough new customers in order to actually fill that pipeline and make sure there's longevity in the business.

[00:09:04]Naturally, if we're profit oriented, the most profitable way to do it is completely shut off new customer acquisition. Uh squeeze as much as we can from repeats, but that's kind of a a short-lived strategy. H it's not going to give, you know, it might give short-term profitability, but ultimately we want to create sustainable profit that's going to serve the value of the business. So the variables on the new customer side are going to be slightly different. Really our new customer acquisition is going to be d very much driven by how much we're spending and what return we're getting on that spend in the context of new customer sales. So ultimately here you can see we've modeled out okay 10k 7 and a half 15 20 and kind of varying levels of spend month on month based on past performance. I know that I can put this level of spend to good use. I have enough demand to support this level of spend. And from here we have a separate metric which might be a new one but we can give some context on it which is ameor or acquisition me otherwise known as new customer rorowass. So ultimately if we consider rorowass or meor you know you know let's say meor or rorowass of three would be I spend one I get three back. ultimately across my full business acquisition meor is going to look at okay what do I get back but just in the context of new customer sales so if our acquisition meor is 1.15 in this case and what this mean is what this means is for every $1 I invest in marketing I'm generating $1.15 back in new customer acquisition. So ultimately in this particular case um again you're ultimately wanting to use your past performance to give relative indication of what sort of return you can recently expect on a similar level of spend and kind of model that out a little bit more.

[00:10:57]So what we want to try to do here is look at past performance to understand what based on my current strategy with improvements you know with justifiable improvements in that strategy. What do we what do we reasonably expect we can do in terms of a return on whatever marketing spend we want to invest.

[00:11:29]So next we have acquisition me or new customer rows. So this is a new metric for many people. Ultimately, if you think about me or ROS overall, it's going to represent, okay, what am I spending in marketing and what multiple am I getting back in revenue to the business? And ultimately here, it's a very similar calculation, but we're just looking at not revenue back to the business, looking at new customer revenue back to the business. And ultimately this is really important in the context of any subscription business whereby we don't want to see a rorowass inflated or me inflated by those repeat customer sales. Um they might fall into the net to whatever extent poss what to whatever extent they might fall into the net but realistically they're not indicative of how we're going to acquire new customers moving forward. We don't want to count them as part of this calculation. So in this particular case, we've a 1.15 acquisition me. So for every $1, we're expecting $1.15 back.

[00:12:27]And ultimately, depending on your underlying margins as a subscription business, you may need a certain kind of level of a return to be profitable on first order, order two or three, whatever that looks like. But again, suppose we can look at that separately in a couple of minutes. And then thirdly, we have our new customer AOV.

[00:12:46]So again, similar to the repeat customer side, what revenue are we driving on a per order basis and which allows us to to make some further calculations as well. So all of a sudden here you can see these elements give us a really comprehensive view of ultimately how we're actually kind of performing here on a month-on-month basis whenever we set that overarching kind of net sales target here.

[00:13:12]So now by taking these assumptions we have a very clear breakdown not only of our overarching sales goal not even our overarching net sales goal.

[00:13:24]So now month on month so now month on month as part of our forecast we not only have an overarching set of goals that are combination between a repeat customer and new customer performance listed. So now in our forecast section, we not only have our kind of overarching uh goal. So now in our forecast section, we not only have our overarching goals for things like net sales, cost of goods, marketing, contribution, margin, operating expenses, but we have our new customer and repeat customer.

[00:13:59]So by entering these variables we now have a really comprehensive forecast month on month that helps us kind of break down three key elements. Number one our overarching performance and then number. So now within our forecasting section we have a really comprehensive breakdown of all the underlying targets and metrics we need to hit in order to achieve our overarching goals. So you can see that in terms of our cumulative sales, profit, variable cost goals, but also as well looking at that in the context of new and repeat customer sales. So just to look at um our acquisition side for a second before we jump up, we can see now based on the AOV of our orders, how much we're spending, the return we're getting on that spend, we know, okay, what is our cost per acquisition going to be? What number of new customers do we want to acquire?

[00:14:47]What's the level of net sales from those customers specifically? What's the gross profit we expect from those new customers based on the margin based on our cost of goods? What's our new customer acquisition cost and our new customer contribution margin? So this is a really beneficial metric for cont for anyone unaware contribution margin is going to be your gross profit after marketing spend. So gross profit is definitely a really good indication but marketing cost is obviously a big variable we need to take into account.

[00:15:17]It's not treated as a variable cost but as we know it is very variable itself.

[00:15:21]So with that in mind if we know our contribution margin on an overarching basis or per order.

[00:15:28]So if we know our contribution margin either on an overarching basis for our business or alternatively on an order level we know exactly how much profit is left over. We know exactly how much profit is left over after accounting for all of those costs and after our marketing spend. So we can grow with a much greater level of confidence. So you can see here in this particular case if we're generating about $21 of gross profit per new customer order and it's going to cost us based on the our expected return $30 in order to acquire that customer gross profit minus acquisition cost uh 21 minus 30 leaves us -957.

[00:16:07]So we're technically losing money on that first order. Now ultimately the benefit a subscription business have as you will well know is that ultimately we don't always have to be profitable on that first order. Take a more dramatic example like a furniture business. I don't know if I'm a furniture business when I'm going to see that customer again. I need to be a very good salesperson in order to sell them a sofa again next month. and where realistically if someone is on renewal you have a high degree of certainty and you're willing to make that bet and take that risk that ultimately I'm willing to acquire more customers in order to acquire that great num in order to acquire that greater number of customers I need to expect that I might actually have to spend more to acquire each of them might not make money on that first order but I'm willing to make that sacrifice not out of the goodness of my heart it's because I know that customer based on my business model will drive more profit for me later than I'm with than I'm having to give up now. So, it's a worthwhile trade-off and beneficial trade-off for the business.

[00:17:22]So with that in mind, I would also make sure to be very careful on what sort of expectation or target you're actually setting here. And ultimately, you want to make sure that you don't lose too much money here. H that you know that you're so the logical question will be okay, what should this number actually be for me and what can I afford for it to be?

[00:17:48]Ultimately different businesses with different lifetime values will see a greater benefit.

[00:17:58]So the next logical question is okay what should that number actually be for me and what can I afford it.

[00:18:08]So the next logical question here is what should this number be for me? Not only to make sure I'm not losing too much money, that I'm not able to achieve the level of profit that I want to achieve at a business level, which we'll go through now, but also that we actually are profitable on a per customer basis. If we have a ton of repeat customers, we're losing money on new and even despite the fact we're hitting our net profit goals, we're never making a profit from those new customers we're acquiring. That's got to be a That's going to be a recipe that's going to be a recipe for disaster moving forward. So ultimately I would so ultimately I would always recommend taking a look at the lifetime value chart whether it's store here or otherwise through the lens of profit to understand well what sort of profit do you generate at first order after 3 months after 6 months after nine and after 12 and ultimately what you might find is that businesses and I suppose brands who are growing more aggressively and they have the cash flow to to finance that aggressive growth will be willing to lose money for longer.

[00:19:13]And with that in mind, they might actually look to say, well, we want to spend 20K to acquire customers. And that's the on, you know, we're losing money on each of that kind of that new customer acquisition. But ultimately, if I say here, okay, well, I'm actually as I spend more, I'm going to