Walk through of some key features of the sheets in the subscription ecommerce financial model

There is a lot in this model. I can't possibly explain all the details involved, and neither do you want to read it ;). I've whacked in a screenshot per sheet so you have an idea what is going on. If you want to see me explain it in more detail watch the videos.  This should give you an idea of the magnitude of how much there is in this model and the time I put into making it. 

Table of Contents (Click to section)

The Subscription eCommerce model architecture

subscription ecommerce architecture
  • Fully integrated model: Changes in one sheet flow through the whole model automatically
  • Logical flow: Expenses, marketing, and revenue are all segmented so they connect logically
  • P&Ls: Actuals and forecasts have their own P&Ls. These are then combined into a 'combined sheet' so you can see the progress month to month
  • Other: Charts and KPIs have their own sheets

Formatting sheet

ecommerce financial model formatting
  • Actual start: You can add up to 11 months in the actual sheet (12m is a historic, right?) 
  • Forecast date: Forecast sheet has 36 months of forecast
  • Constantly update your model: I spent 2 weeks just figuring out a way to constantly update your model. You start with your Actual start date, then you can move your forecast month forward as time goes by. You then add your new actual month in
  • Month end: Set your month end. Your annual bonus, tax etc will be paid out that month in the CFS, but accrue in the P&L as usual
  • Currency change: With my plugin, you can switch to all the currencies you can see in the list

Detailed Profit and Loss statements

  • 3 P&L sheets: Actual, forecast and combined. The combined presents actual to forecast together
  • Non key assumptions in line: If you want to add discounts, cancellations and bad debt, you can easily add in a percentage in line
  • Fundraising: When you plan on raising money, you just input the amount into your cash balance
  • Accounting and cash flow: Two main parts. The main part follows normal accounting practices (There is a sheet for Tax to manage NOLs as well as depreciation following Investment Banking standards). The bottom of the sheet converts accounting to operating cash flow so you know when money is actually going in and out. There will be an update with an integrated Balance Sheet and CFS the guys are working on, but you don't really need it as you aren't a bank
  • Complex adjustments: Bonuses are paid on year end in cash flow, but accrue in the P&L. Same for tax. Accrued bonuses from actuals are factored into your forecast for when they need to be paid
  • Grouping: Note the + buttons on the left. There's 228 rows you can group and ungroup like all of the sheets. The image above is the grouped one

Depreciation and tax

ecommerce financial model depreciation tax
  • Control depreciation and tax: Simply manage the boring accounting bits with only a few assumptions like an M&A banker would
  • Depreciation: Accounting for depreciation
  • Tax: Calculate when you need to actually pay tax. Net Operating Losses (NOL) accounted for
  • UPDATE COMING: I hired an accountant to add more boring nerd stuff to include R&D capitalisation etc. I'm not adding amortisation of intangibles because you're not a big corporation... I purposefully only add what might be useful

Logistics, payment, and tech expenses

ecommerce financial model expenses
  • Warehouse: Calculate your warehouse costs, handling, and packaging
  • Delivery and returns: Calculate your weighted average delivery and return costs
  • Payment costs: Simply manage your payment costs with credit card, PayPal, bank transfer and payment on delivery. There are two sections for the SME and Enterprise revenue to calculate separately. I assume all SME incurs payment fees but that this can vary for Enterprise in caser clients wire cash and incur the costs themselves
  • Customer care: Calculate how many customer care agents you need to serve your customers
  • Tech costs: Automate server and email costs. You can build this out if it is a big deal to you. I've kept it fairly simple with sections for both SME and Enterprise
  • Photography: Two options to deal with photos of your SKU. Do it internally and have photographers on staff and hire models as needed. Or, outsource photos and pay per SKU

Detailed KPIs (184 rows over 2 sheets)

  • Detailed KPIs: 184 rows of all the meaningful KPIs I could think of
  • 2 sheets: General and subscription-based KPIs
  • Editable: Nothing is linked to this sheet. You can add whatever KPIs you like if you want to
  • Lots of details: Track your CAC, CPO, churn, and financial ratios
  • Dates: Both monthly and annual KPIs

Runway calculator

ecommerce financial model runway calculator
  • Runway calculations: Understand your gross and net burn over your defined runway (in months). See how many months your planned fundraise will last based on your model forecasts
  • Detailed operating expenses: See exactly where money is being spent per department over your runway
  • Charts: Pre-made fancy charts to use in your deck if you want


ecommerce financial model charts
  • Each sheet has charts: I've stuck in relevant charts to check trends at the bottom of every sheet

Staff and general costs

ecommerce financial staff
  • Automatic forecasting: Customer care, warehouse, photographers, and recruiters are automatically calculated in the Expense sheet and filter in here
  • Scale recruitment as you need: Opt to hire recruiters when you hire [5 or more] new people a month so you don't have to think about how many recruiters you need
  • Granular control over hiring: Easily add benefits/tax, choose the date you hire/fire staff and the date you want to increase their salary (say at Series-A)
  • Other costs: Quickly add all the 'other' costs you need like rent, onboarding costs (e.g. laptops) of staff, and whatever costs you need without having to overthink it. Most costs are calculated by multiplying the average cost be the number of staff you have

Price and cost basis of SKU for an ecommerce store

ecommerce financial model sku
  • Forecast SKU: Add and remove SKU depending on the season
  • Price vs cost: Model both the cost and sales price in one place to track your gross margin
  • Model in 3 categories: Pick three categories (Shoes, Apparel, and Accessories as an example) and use these as your average avatars to make things simpler
  • New vs repeat: Model both your first and repeat customers (assuming repeat pay more)
  • Outright vs consignment: Have different prices and costs depending on how you get your SKU

Cohort build for ecommerce store

ecommerce financial model cohort
  • Adoption curve: Registered customers can adopt over months
  • Different adoption: Assume customers adopt faster/slower depending whether they came from paid/earned channels
  • Churn: Adjust for churn
  • Granular modelling: The entire model is based on monthly cohorts. This is complicated to build but enables you to do a lot of things you couldn't without them. There are 1,065 rows!

Conversion assumptions for subscriptions

  • Categorise by purchase type: Are your users buying a product on your eCom store, a subscription, or both?
  • Refine your categorisation: Are customers annual or monthly? Are they basic, premium, or pro customers?
  • Churn: Adjust for churn
  • Package switching: Do your customers trade up or down between pricing packages?

Set your box subscription pricing

  • Set pricing: Customers can either pay monthly, or they can pay annually with a discount

Detail what's in the box with your subscription SKU

  • Define the items per box: Set the number of items you will have per box on a monthly basis
  • Set price per item: Set how much each item will cost to granularly define the cost basis your box will cost

Subscription calculations (Monthly and Annual)

  • Two sheets: Monthly and an annual sheet to do super-duper granular calculations (Annual sheet has 72,323 formulas!)
  • Hands off: All the calculations are done for you

Complete marketing

ecommerce financial model marketing
  • Granular assumptions: Control how traffic turns into registrations by setting conversion rates
  • Conversion: Convert your traffic to registration
  • 4 marketing sheets: Main marketing sheet gets fed by 3 marketing activity sheets which are all integrated. Paid/organic, email, and blog & social.
  • Growing too large?: In built calculations to stop you growing larger than your defined market. Turn it on and off... if you want that level of nerdy- ignore and hide it otherwise!

Email marketing

ecommerce financial model email
  • Earned growth: Support sheets effectively help you to reduce your paid CAC
  • Fancy assumptions: There's a load of assumptions built-in from open rates, click through, forward rates, rebroadcasts, unsubscribes, etc.

Blog & Social

ecommerce financial model blog social
  • Earned growth: Support sheets effectively help you to reduce your paid CAC
  • PR: Get press, featured in TechCrunch etc and get earned traffic to your site. Simple to follow and understand. 
  • Social: Build a social following with lots of detailed assumptions as the email sheet. How many times you post, follow CTR, follower shares, rebroadcast rate and CTR, as well as builds to your email list