What’s up guys!
Here we have the training videos for the ecommerce financial model. This is for model version v2.3 (as of November 2020). I’m on v2.4 (as of a day later), the noticeable difference is that I added conditional formatting to the Actual sheet to make it easier to see what you should be filling in. There will be no future updates unless you give me feedback to add something.
If you are struggling with something, sound out with what you would like explained and I’ll put up a video.
Quick model overview
We are going to go through the model at a high level, but enough to have an idea of what is going on.
Next, we are going to run through the basics of the model that I find some people get confused about. These are things like grouping, turning on automatic calculations, ensuring you use all threads to enable faster calculations, column structure and flows between sheets.
Even if you are a CFO or self-proclaimed Excel nerd, I still recommend just going through this video.
How to approach the filling in the e-commerce model
In this 40 minute video, I’m going to give you my thoughts on how I would approach filling in the model. If you have never used a huge template like mine or much financial modelling experience, this will really help you to understand what you should be doing.
It’s important to internalise that this model will feel overwhelming to you at the start. You just need to start and keep learning more details over time. Eventually, it will make more sense to you. Understand that my models have facilitated learning experiences, not a destination you want to get to ASAP because “an investor wants one”.
As I commence in the video, the absolute first thing to do is to read my blog on how to fill in a financial model template. I don’t care how senior you are, you’ll learn at least one new thing. You have to do the exercise that I explain in order to triangulate (as I will constantly reiterate to you in the videos).
Profit and loss, and Depreciation and Tax
We are going to understand the three profit and loss sheets, the pitch deck sheet, and the depreciation and tax sheet.
The annual sheet is the most work. The forecast sheets only have around 5 assumptions to fill in. The combined sheets have none.
The pitch deck sheet is an example of what you ‘could’ put in your deck. You can delete it for all I care (There will be no consequence, so you can delete it).
Finally, the depreciation and tax sheet enables you to do funky calculations assuming you are profitable. The title says D&A and tax. There is no amortisation in my model because it is irrelevant to startups. I’m just used to calling it D&A because I was a banker. You don’t have to do anything if you don’t understand the sheet. If you want to learn more, I probably made more content in older videos. See the end of this page for “Tax and Depreciation”.
D&A and Tax
I deal with this in the Profit and Loss video (video 3). If you want to learn more look at the old one done for the SaaS financial model. The sheet is the same for all my models. But frankly, unless you are an old company and know nerdy accounting things you can largely ignore this sheet.
Source and Use
How are you going to use the funds? What is your runway? We are going to go through this sheet briefly so you understand how to use it. When you have completed your model, you can use this sheet to calibrate the raise you need and runway.
If you want a ‘little’ more detail you can read and watch the video on my runway calculator page.
KPI and Charts
We are going to learn about the importance of the KPI sheet in the model. As an investor, this is a sheet I would certainly look at first. You will use it repeatedly to check you are on the right path, and at the end to ensure your assumptions coherently make sense.
We will also go through the charts sheet. They’re just charts so not much to learn! You use them to ensure that you are trending in the right direction.
Staff and Expenses Forecast
The staff sheet is the core expense sheet in the model. It enables you to do quite a lot, but also simply. Most of the sheet is for the various departments you have. The end allows you to add the various costs that you will have, which are mainly staff related. Included is also the other general costs.
Expenses
This sheet comprises of automated staff calculations and your other costs.
You will be able to set assumptions for variable staff (customer care, warehouse, photographers) so you can increase those staff regardless of whether you change to a bull or bear set of assumptions.
You will also map out your logistics costs (shipping and return, warehouse etc) as well as your payment and tech costs.
Cohort build
This is a workhorse sheet to calculate your customers and revenues. You are going to set assumptions for conversions from registrations to customers. It’s not the easiest set of assumptions to get your head around!
SKU
This sheet is about setting your SKU (Stock Keeping Unit) assumptions. How much do you charge and what is the cost basis for your three categories of products?
Marketing
We are covering all the marketing sheets in the model in this video. If you want to see some more detailed insight into the sheets (for example blog and social), you can look at the old ones done for the SaaS financial model. The marketing sheets across all my models are basically exactly the same.
Checking you got it right
This is the last video. I’ve sort of covered this in previous videos, but we will briefly go through some sense checks you should do to ensure you don’t send something to investors that make you look dumb (assuming you are raising- the model isn’t just for VC addicted startups).