How To Use Our Subscription Ecommerce Financial Model Template?

Disclaimer: this article is for anyone who uses our Subscription Ecommerce financial model template and needs a step-by-step guide on how to use it.

If you haven’t yet downloaded our Subscription Ecommerce financial model template, you can do so opposite.

Before we dive in the step-by-step guide, note that we are using for the purpose of this article the Google Sheets version. You can of course download the template use it with Excel instead. Let’s dive in!

Step 1: Settings

The first thing we need to do is to enter a few details in the Settings tab. They are:

• Company name: the name of your business / project

• Start date: the first month the model will forecast from. Note that the first month should be in the future, and it isn’t necessarily the following month. For instance, assuming we are today in July 2021, and you are raising capital which you expect to last 2 months, the start date can very well be September 2021 or even later.

• Fiscal year end: by definition, this is the date that defines the end of a 12-month period for accounting purposes. In our templates, this date is important as it will serve to calculate the annual summaries of your P&L, balance sheet and cash flow statement. Note that most businesses have 31st December as fiscal year end. When in doubt, ask your accountant.

Note: the year in the fiscal year end is not relevant. For instance, if your fiscal year ends on December 31st, 31/12/2021 and 31/12/2022 are exactly the same thing.

• Currency: the main currency of your business and in which you want to present your financials. Note that the currency does not impact any calculation: it is simply used as a legend throughout the model.

Note: if your business operates in different currencies, use the same currency throughout for your model. For instance, assuming you sell your products in USD but have some staff paid in EUR and your reporting currency is USD. In this case, the expenses (the salaries of the staff in EUR) should be converted in their USD equivalent when you enter your hiring plan (more on that below)

• Corporate tax rate: this is the tax rate you pay in your jurisdiction. If you aren’t sure which is yours, refer to KPMG’s exhaustive updated list here.

Step 2: Revenue

In the same Settings tab, we need to set a number of assumptions behind your revenue.

2.1 Subscription tiers

• The name of your subscription tiers: the model allows you to have up to 4 subscriptions (including a free plan if you need to). The names you enter will flow through the Revenue and Operating Model sheets of the template

Note: as you we will see later, the 4 subscription tiers all have separate assumptions, especially pricing but also the number of products / services each subscription tier allows. For example: customers within the “Basic” plan may receive one product which costs you $10, 4 times a year, and you price the subscription at $100 per year ($60 profit) ; the “Premium” plan customers receive one product costing $12, 6 times a year, priced at $200 ; and so on…

• Pricing: enter the monthly and annual fee of your subscriptions. If your plan does not include one or the other, simply enter zero. Annual subscriptions typically are discounted vs. monthly billings, so the template gives you this ability.

Note: the annual fee is per year. In the example above, the price of the “Basic” plan is $39 per month (so $468 per year), or $190 per year if billed yearly.

• Start date: this is the date from which the subscription is launched. For instance, you might not launch all subscriptions at the same time, instead you might launch some later in the future. In the example above, all subscriptions are launched in January 2022

Note: start date cannot be prior the model Start date.

2.2 New Users distribution

This is where we decide which new user choose which plan.

Note: This is only valid for new users (the leads who converted and became customers). Indeed, existing customers will have the ability to move between the subscription tiers via upsell and downsell (more on that below).

For each of the subscription tiers, you will need to enter:

• The breakdown of new users per tier: in our example, if you convert 100 new customers in a month, 60 will choose “Basic”, 30 “Premium”, 5 “Advanced” and the remaining 5 the “Expert” plan

Notes:

1. The sum of all percentages need to be 100%
2. If you have less than 4 tiers (for instance 2) enter 0% for the relevant tiers.
3. The breakdown of new users is calculated in the Revenue tab. Because the users are rounded, if the number of new users is too low the percentage might round up or down the value. For instance, assuming you convert 10 new users in a month, because we can’t have 0.5 customers choosing Advanced and Expert respectively, the model will round Advanced to 1 and Expert to 0
4. If you entered different start dates in the Subscription tiers section, the breakdown will take it into account. Let’s assume you have 2 tiers and you launch the 2nd six month after the 1st, and you enter 50/50% for the new users breakdown. The model will attribute 100% of new users to plan 1 for the first 6 months, and 50/50% from month 7 onwards.

• The breakdown of new users per billing cycle: choose which percentage, for each tier, of new users who choose to pay annual vs. monthly.

2.3 Upsell & Downsell

By upsell and downsell, we allow customer movements between the different tiers after they have converted as customers for the first time.

For each plan, you will need to enter a percentage. This is the percentage of a plan’s total users that move to the nearest plan (either up for upsell, or down for downsell). For instance, in our example above, every month, you expect 2% of the “Basic” plan to move (or “upsell”) to the “Premium” plan.

Downsell is simply the inverse of upsell.

Note: remember these values are monthly. Therefore, if you enter 20% it means that every month, 20% of a given tier will upsell or downsell to another plan. If you expect, say, a 20% upsell rate from plan A to B, simply use 20%/12 for the monthly upsell equivalent.

2.4 Churn

Here you need to enter the percentage of monthly churn to calculate the number of users, each month, who cancel their plan and leave. Note this is across all subscription tiers.

Note: like upsell and downsell rates, churn is a monthly percentage. As such if you expect 20% annual churn, enter 20%/12 for the monthly churn instead.

Step 3: COGS

Before we jump into the specifics, let’s first take a step back to understand what are the Cost of Goods Sold and how the template calculates it:

The Cost of Goods Sold (COGS) are the costs for you to source (purchase) the products or services you offer as part of the subscription(s).

Let’s assume your subscription ecommerce business offers men care products delivered at home within a subscription. If you charge say $200 per year to send 12 sets of products per year that cost $10, the COGS for 1 subscription is $120 (12x $10). Gross profit (per subscription) is then $80.

Therefore, you need to enter here the following assumptions:

• Average unit cost: the average cost per product which is included within the subscription. In our men care example above, this is the $10 unit cost

• The number of units per annum: the number of units (products or services) you sell per year as part of the subscription. In our men care example above, that’s 12 times a year

Step 4: Acquisition

In the same Settings sheet, we need to change a few inputs affecting your acquisition: how to “acquire” customers. It will allow us to forecast the number of customers you have over time (factoring churn as we saw earlier).

Typically subscription ecommerce businesses generate orders via online traffic (i.e. visitors landing on your online shop for example). Traffic is either paid (pay-per-click campaigns e.g. Google Ads) or organic (you do not pay for the clicks you generate).

4.1. Paid traffic

Paid traffic is by definition the number of visitors who land on your website via paid ads (Google Ads, social media, etc.). The number of paid visitors is obtained using the following formula:

Paid visitors = Paid marketing budget / Cost-per-click

For instance, assuming you spend $10,000 in paid marketing in a month, and your average CPC is $2, the number of paid clicks (or visitors) you generate is 5,000.

In order to estimate the numbers of paid visitors we need to set 3 assumptions:

• The average cost-per-click of all your campaigns. If you are running search and social media ads for instance, enter the average CPC. For more information about cost-per-click benchmarks per industry, have a look at our article here.
• The first month of your marketing budget spending. By default, the first month is the date of the launch of your first product category. The amount is the monthly budget you expect to spend in that month.

How does growth works?

• The monthly growth of your marketing budget. This is a tricky one: you marketing budget will change over time (likely it will increase) from the first month you just set. As such, you will spend more in the future, and we need to estimate the amount of monthly marketing budget you will spend over time. This monthly marketing budget is calculated using a monthly percentage growth. Yet, because the monthly budgets will increase over time, the percentages themselves will decrease. In other words, you are still increasing your marketing budget over time, yet the positive percentage growth decreases. See below an illustration to make it clearer.

Note: the percentages are set at the beginning of each year. In our example above, monthly growth starts at 20% in the beginning of the first year, and decreases progressively down to 10% at the beginning of year 2, and so on.

4.2. Organic traffic

Organic traffic is the number of visitors you generate organically (unlike paid traffic for which you pay per click, or impressions). It works in a similar fashion as paid traffic. The only difference is that we do not have to set a cost-per-click here. Therefore the assumptions to set are:

• First month organic traffic. The number of visitors you expect to generate in the first month (again, the date of the launch of your first subscription) from organic sources
• Organic traffic monthly growth. The same way marketing budget increases thanks to a percentage growth, organic traffic will do so too. Refer to paid traffic above for a refresher on how this works.

Step 5: Conversions

This is where you can set the conversion rate that applies to the number of visitors you generate (as we see in “Acquisition” above).

• Conversion rate: the conversion rate from visitors to customers. For example, if 1.0%, for every 100 website visitors, only 1 turns into a customer (and subscribe to one of your subscriptions)

Note: the conversion rates can change over time as you can see in the table above. This allows you to improve conversion rates, if you wish so, as your conversion funnel gets more efficient over time.

Step 6: Fulfilment

Fulfilment expenses are all the costs you incur to fulfil the orders (the products or services you send to your customers as part of the subscription they have subscribed to).

The fulfilment expenses include: warehousing, handling, packaging and shipping.

The template is flexible and works for any type of subscription ecommerce business. It works for businesses that have in-house and outsourced fulfilment. The difference between the two can be summed up below:

• In-house fulfilment: you manage yourself the packaging, shipping and handling of your orders. As such, you have your own warehouse and operators handling the orders, and you ship yourself (or outsource) shipping and packaging.

• Outsourced fulfilment: fulfilment is outsourced to a third-party who manages all the different steps (warehousing, handling, packaging and shipping). Typically, you pay a fixed price per order. Amazon FBA is an example of an outsourced fulfilment provider.

Let’s start with outsourced fulfilment.

6.1. Outsourced fulfilment

If you outsource fulfilment to a 3rd party provider, enter the corresponding average cost per order here. For an Amazon FBA fulfilment fees for instance, refer to the list here.

For instance, if you pay in average $5 per order and make 100 orders in a month, fulfilment costs are $500. This means you do not spend anything else in shipping, warehousing, packaging, handling: all these costs are already included within the fulfilment cost per order.

6.2. In-house fulfilment

If you do not outsource fulfilment to a 3rd party, you need to set assumptions separately for the different categories below.

6.2.1. Handling costs

Handling costs are the salaries you pay the operators who handle the orders (they sort them, package them and prepare them for delivery). Therefore, handling costs are a function of the number of orders you have, and the efficiency of your operators. The assumptions you need to set are:

• Number of orders processed by an Operator per hour. This sets the efficiency of your operators. Can 1 person prepare 10 orders an hour (small electronics for instance) or only 1 (furniture for example)?

• Number of shifts per day: the number of shifts for the operators. Do you operate 1 shift per day from 9am – 5pm or 3 shifts covering 24/7?

• Number of hours worked per shift. Operators might be paid 8 hours but they might not be effectively working 8 hours but 6 or 7 instead (count lunch time and other breaks)

• Hourly salary: the cost per hour of your operators (including taxes and benefits)

6.2.2. Warehousing costs

Warehouse costs are a function of the number of orders you have. Of course, a warehouse isn’t a variable cost either: you increase warehouse costs by adding up another warehouse space, maybe every year or so.

Still, the ecommerce financial model template allows you to forecast the cost of the lease of your warehouse depending on a number of assumptions:

• Numbers of items per day per sqm. This number depends on the size of your SKUs (products). The higher the number, the more orders you can process per sqm.

• Monthly rent per sqm (including bills). The monthly rent of your warehouse per sqm (including bills)

• Number of sqms (minimum). This is the minimum number of sqms for your warehousing operations. For instance, if you start operations with a 300 sqms warehouse and plan to add more surface as you grow, enter 200 here.

• Number of sqms (per increase). As said earlier, warehousing costs aren’t fully variable. So, when you increase, you typically rent another warehouse (additional space or you replace your existing space with a bigger warehouse). The number here is the number of sqms per incremental increases of surface. For instance, if you plan to add a new space every time you are at full capacity by an increment of 200 sqms, enter 200 here. As such, using our example above, if you have 301 orders in a month, you will need to open a new warehouse so you have 500 capacity in total.

6.2.3. Packaging costs

Enter here the amount you spend per order for packaging.

6.2.4. Shipping costs

Shipping costs are two-fold: the costs you incur when sending the products to your customers and when they return them. Therefore, to forecast shipping costs we need to change 3 assumptions:

• Shipping cost per order: the total cost you incur to send one product (or return it). This can be in-house shipping (your own couriers) or 3rd party costs (mail for instance)

• Free shipping. Set the percentage of total orders for which you pay for shipping. If you do not pay for shipping (but your customers do), leave 0%

• Free returns. choose here whether you pay for return shipping (enter “Yes”) or whether your customers need to pay for it (enter “No”)

Step 7: Hiring Plan

One of the biggest expense category of Ecommerce businesses often is salaries. It is therefore very important to set up your future roles and their related expenses (salaries, bonuses) correctly.

7.1. Teams

The first thing we need to do is to list the different teams you have and group them into up to 5 categories. This will allow us to differentiate salaries into the different teams in your P&L and your metrics later on.

Note: if you selected inbound acquisition under Acquisition (section 3 above) earlier you will need to enter at least a “Sales” team so the template can calculate correctly the number of Sales people that generate customers in the Acquisition sheet.

7.2. Roles

In the Hiring Plan sheet, you will need to enter all the roles you expect to hire in the future. For each, you will need to enter (as shown in the table above), in order:

• Role: either the role or the name of the recruit

• Team: drop-down list to select the name of the team the role falls into (the teams are the ones we set up earlier)

• Salary: the annual gross salary

• Starting: the date when you expect to hire the role.

Note: if you already have existing roles before the start of the model, enter the date in the future you recruited the role. For instance, if you are CEO and started to work since January 2021 but the first date you project your financials from is September 2021, enter January 2021 as the starting date for CEO.

• Taxes: any taxes and benefits you incur on top of salary, expressed as a percentage of gross annual salary

• Leaving. If you have contractors with fixed term employment contracts for instance, enter the date at which you will not employ that role anymore

• New salary amount. If you expect to increases salaries in the future of certain roles, enter the new annual gross salary here

• New salary starting date: the date you start to pay the new salary

• Bonus %. If you pay bonuses on top of salary to certain roles, enter the corresponding percentage of annual gross salary you pay each year as a cash bonus.

• Bonus timing. Here you can set whether you pay bonuses every month, quarter or yearly.

Step 8: Expenses

In the Settings sheet you can enter a number of assumptions for the template to calculate automatically the expense amounts in Expenses.

8.1. Payment processing fees

Enter the percentage of revenues you will pay to your payment processing provider (e.g. Stripe, Paypal, etc.)

8.2. Customer Support

Customer support is an important expense category for most SaaS businesses. The template gives you the ability to forecast Customer Support in 2 different ways, either:

1. Enter the customer support roles one by one in the Hiring Plan as shown above; or
2. Forecast customer support salaries as a function of the number of users you have

We are covering here the 2nd approach. Customer support salaries will be calculated by taking into account 3 different assumptions which you will need to set:

• Percentage of customers requiring customer support per month. Let’s assume you have 1,000 active customers in a month, not all might contact your customer support service. For instance, if a customer calls your CS say, every 2 months, enter 50% (indeed, 50% of all active customers contact CS every month, ie every 2 months)

• Number of tickets processed by a customer support agent per hour. Here we set the “efficiency” of your customer support team. If you are a Enterprise SaaS business, the tickets may take more time to be processed (hence an agent might only process a handful per hour). In comparison, if your tickets are usually dealt via email within a few minutes (e.g. B2C SaaS) enter a higher number here

• Hourly cost of a customer support agent. This is the total hourly cost you pay your customer support agents (including taxes and benefits, if any)

Note: If customer support is outsourced to a 3rd party instead (to Teleperformance for instance), simply enter here the hourly cost you pay per customer agent.

8.3. Hosting

Enter here the amount you spend per month per user in hosting, tech infrastructure, etc. If unsure, ask you your CTO.

8.4. Team expenses

For each team, you have the ability to set a spending per role, it includes:

• Onboarding costs: any one-off you incur when you hire a role (computer, desk, etc.)

• Ongoing costs: any recurring expenses you incur per month per role (subscription, meal allowances, etc.)

• Travel: the travel allowance per role per month. Management and sales team may travel more than the engineering team for instance, so you can differentiate them here.

8.5. Other expenses

Enter here any other recurring expenses you haven’t included yet. For each expense there are 4 assumptions to set:

• Expense: the name of the expense

• Start date: the date from which you start to incur such expense

• First monthly expense: the amount of the first month

• Increase (per year): the percentage increase (per year) at which the expense grows over time (if it does)

Note: if the expense stays flat over time, leave percentage increase to zero instead.

Step 9: Balance sheet

• Cash starting balance: the amount of cash you already have in the bank before the start of the model

Note: do not include the fundraising round (see below) you expect to raise here. It will be added automatically to your cash balance.

• Losses carry-forward starting balance. If you have any tax credit from your state, enter the amount here. For a refresher on what losses carry-forward, see Investopedia’s definition here.

• D&A lifetime amortisation. Any capex you will spend in the future (see below) will be depreciated over time. This is where you select the depreciation schedule. If in doubt, ask your accountant.

Step 10: Capex

If you expect to spend any capital expenditure in the future (building, equipment, etc.) or simply capitalise part of your expenses, enter the amount under “Other capex” in the Capex & D&A sheet.

Note: by default the template already includes onboarding costs (see team expenses) within capex. Indeed, onboarding costs such as computers and desks are assets and need to be included within capex and depreciated over time.

Step 11: Fundraising

This is where you can set the fundraising rounds you expect in the future. Set here 3 assumptions:

• Round: name of the round (e.g. Seed, Series A, etc.)

• Amount: the amount you expect to raise from this round

• Date: the date of the round