Key features of the eCommerce financial model

Walk through of some key features of the sheets in the 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.

The eCommerce model 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

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
Bookings, billings and revenue: All revenue types are 'accounted' for
Grouping: Note the + buttons on the left. There's 209 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

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 (84 rows)

Detailed KPIs: 84 rows of al the meaningful KPIs I could think of
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, and financial ratios
Dates: Both monthly and annual KPIs

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

Charts

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

Staff and general costs

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

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 power

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!

Complete 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!

Paid and organic marketing

ecommerce financial model paid marketing

Organic growth: Simply add an organic growth rate of people going to your site
Paid growth engine: Set your spend per month across up to 6 channels. Set your CPCs to generate traffic to your site. In addition you can add non-paid spend which doesn't attribute traffic (one-time campaigns and brand marketing) but will still contribute to your marketing costs and metrics
Supporting metrics: See supporting conversion rates and metrics to check things make sense

Email marketing

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

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