How to estimate the revenue model of an eCommerce Business

published on August 2, 2020

This video was brought to you by slightly a web platform that designs your presentations for you just add the content and let artificial intelligence do the rest get one free month by signing up at sleeping calm /youtube in this video I am going to show you how to

Estimate the business scale for an e-commerce platform these projections are called a financial model or financial modeling and we've made videos about the basics of a model and we also created a financial model for a

Subscription business if you want to check that out so for this video I'm going to start off with our financial model template this Google sheet or Excel document is available for free on the link in the description so you can

Build a model yourself also the model that I create today with all its formulas will be available for purchase so you know you want to save some time you can just buy that and skip ahead so before we get into it let's understand

How a financial model works this is me from a couple of weeks ago explaining it there are many kinds of financial models this one is closer to a forecasting model which is used for financial

Planning and analysis in a nutshell what an effective model should do is number one take an estimated ad or marketing or sales spend to estimate the revenue that's going to be generated from that spend and three estimate the costs

Associated with generating that revenue like the team and the office and the server's all of this math combined should give you an answer on whether this combination of variables is gonna make the company grow for early-stage

Startups the model should take into account the team the team expansion and compare it to the available cash perhaps from a round of funding to understand what the exact company runway is if you've done your model right you should

Be able to scale your team and your budget understand the revenue impact of those changes and measure how much that will affect your runway okay so here are the basic parts of a model parts that you'll find on any financial model

Template online first the cogs sheet that stands for costs of goods sold and relates to the direct costs associated with providing your service for a supermarket or an ecommerce store cogs is very straightforward it's the cost of

The groceries or the items sold that the company pays to their suppliers for uber the cogs would be the money that they pay to the drivers now most software companies use this con section for server costs and other essential tools

Of the platform needs to be functional in our case tools like intercom and Amazon Web Services are all part of our cause if you compare revenue to cogs what you get is the gross margin that's the margin your company makes before

Accounting for the administrative expenses let's talk about the revenue sheet now revenue is used to track and estimate well your revenue what is absolutely vital for any model is the driver of the revenue revenue doesn't

Just come you have to bring it in actively depending on your business you'll need to pay to market your app or you'll need to pay to get leads you might even need to pay a sales team to close those deals if for example your

Model estimates 8 thousand dollar-a-month marketing budget that doesn't increase ever it makes no sense that your revenue grows from zero to ten million dollars by year three it's just impossible if you're not

Spending more so there needs to be some correlation with reality here in many of these benchmarks you can find online some examples app installs using Facebook ads can cause from a few cents in low competition countries to around

$2 in competitive markets like the US there is no way to get Google search traffic for less than one dollar per click and most keywords require bids of five six dollars or more an average conversion rate on a landing page could

Be around twenty five percent fifty percent would be pretty remarkable more than that is just unrealistic as the company grows acquiring customers with paid ads usually gets more expensive not cheap all these numbers need to be

Taking into account in your model growth and revenues don't come magically now your model should show the math behind your expected cost of acquisition and measure if that math was accurate at the end of the month when you get the actual

Results this is absolutely key I can't stress it enough next up is the sgna sheet pretty much every expense that doesn't classify as cogs goes into the SGA sheet that's sales general and administrative expenses and it includes

Payroll marketing costs travel office expenses rent accounting consultants pretty much everything else once again it's imperative that you connect these to your revenue estimations on a SAS platform that's a software subscription

You could say that you'll need to hire a support agent for every 1,000 customers on the platform because the more customers you have the bigger your support organization and your customer success organization needs to be so you

Can connect the number of active users on your revenue sheet to the number of employees on your sgna sheet this lets you estimate your margins in the future you can also connect the number of team members to the size of your office and

Therefore rent or you can compare the number of employees to the number of seats you'll need in your CRM it's those connections that let you be more accurate about your growth expectations and your company expenses

In the future I challenge myself at the beginning of every month so that my projected SGA expenses mat our actual expenses at the end of the month last but not least the working capital and capex sheet this sheet is

Meant for assets owned by the company if the company buys a car that car is not exactly an expense it's more of an asset the car affects your cash flow yes because you no longer have the cash available in the bank but the asset

Should be logged in the model the number of assets the company has does have an impact on its valuation or for example if the company goes bankrupt and needs to liquidate those assets desks computers and other machines are often

Assets and not expenses and they need to be logged on this sheet while most software companies don't need to pay that much attention to this section ecommerce platforms do because working capital is critical for them because

They might need to pay suppliers before they collect the sales revenue now all I've done for our template is implement an automatic computer buying system so every time the number of employees increases the model estimates that

You'll need to buy a new computer for them you can enable and disable this option and of course you can add purchases of Europe now all of these four sheets get consolidated in your monthly financial statement sheet now

These sheets provide you with a summary of your gross margin your net income per month cash in the bank and so on taxes at the end of the year are also automatically estimated based on your profit all of this stuff gets

Consolidated on the annual sheet and that's where you have your official growth estimations now I added a few sheets of my own to this template so let me tell you about those the team and salaries sheet now this sheet lets you

Automatically estimate future hires without having to dig directly into the sgna spreadsheet so you may for example define your dev team how often do you expect to hire new team members and how often do you plan to adjust their salary

Now it's designed with employee categories in mind so you shouldn't add each employee by name instead classify your team in each one of these buckets now make sure that you use the company cost for their salary line not the

Actual salary that's paid out to them in other words include your payroll costs here so that you don't have to deal with those on a separate line the KPIs sheet I've also added charts and a KPI sheet that lets you visually check the results

Of the model as well as giving you some critical insights like an estimate of the capital that you'll need to race it can also tell you if you're gonna reach profitability within the next five years

That's also included in the model okay now let's move into the projections sheet this page is where the magic happens and this is where we're gonna be working today I'm going to build a website sales model so the estimation of

The sales that you could get through a website even though we've also added functionality for an app that's a whole other business all right guys so let's dig into this model like I said this is based on the free template that we have

Made live on our website and what I've done here is I've populated the projections sheet so this is the idea of a projection sheet is to keep a place a single place in the sheet where you can log your activity where you can log the

Different variables that you're using to estimate how the business is going to grow you can even you know use the extra cells here to kind of store versions of these variables so you can say you know optimistic and then just copy all this

Column and put it here so you can log what you added and then you can keep multiple in there but now we're gonna we're going to spend more time digging through the formulas themselves okay so to me we're working on the website sales

Model I have it I have added a disabled enable option that is gonna let you enable this versus the mobile app sales you can do both as well but right now we're gonna be focusing on website sales okay so the first important part here is

Where your traffic is gonna come from so how are you gonna get traffic to your website and how that traffic converts why because you know you can't just estimate that you're gonna be doing twice as many sales in a given month

Just out of thin air those sales kind of come from the website come from traffic they come from your campaigns so the driver for this model what makes you generate more money in this company is what you spend in marketing right the

Sales don't come magically so we have added two different versions of or two different sources of traffic one is organic traffic which is people that come to your website because they know your brand because they're yeah because

They found it on social media or because they found your platform organically maybe on Google search so there's a marketing budget for this group which you have a starting number you have a maxed monthly marketing

Budget increase and the maximum budget and then traffic so the the traffic during the first month how much percentage that increases month or month and then the maximum possible traffic that you can get organically to your

Website so the important part here is that what you spend in marketing to bring organic traffic and what you actually get in terms of visits these numbers are not connected you're gonna spend the money in brand awareness

Social media blog and that's gonna be completely separate from the number from the amount of traffic that you get why because organic traffic varies a lot it doesn't necessarily scale with budget for example you could have a PR agency

That has a flat fee but that constantly gets you a lot of traffic and boosts of traffic month or a month so it's not directly connected on the other hand we have paid website traffic so this paid traffic is directly connected to your

Visits you have a marketing budget a monthly increase and the maximum ad spend per month and then a cost per visit so this is a cost per click on these ads do I did 50 cents as a standard button and this is probably

Gonna be closer to a dollar two dollars depending on your industry so how do these numbers connect both of these are going to the sgna sheet so that's your general and general and administrative expenses and the way that works is

You're gonna have a line here that says paid website promotion and organic website promotion okay so these numbers are starting to come to life in October with the initial budget and the way I do that is first we check if the website

Model is enabled then we check if we match the month of October with the date when we're starting this so if there's a match and exact match we use the first number and if the number is bigger meaning the month after instead of doing

The original number we just add the monthly increase and we use a min function to make sure that it doesn't go over the maximum all right let's go back to this one so we can change it Sables there you go okay

So we can we're going to be able to change this from without breaking anything alright so this starts November we said we could start it October November we can start it I don't know April if you will and that's gonna be

Changed to the correct month you know again I'm doing this by I'm doing this by comparing these states ok so then out of that traffic we're gonna have conversions so the new the new orders are gonna come from either organic

Traffic which we're estimating that's gonna convert at five percent organic traffic usually converts a little bit lower because maybe that traffic is going to the blog or you know through an article that you that you built so it

Doesn't necessarily convert as efficiently as paid traffic paid traffic on the other hand should be very targeted to people who are ready to buy your product so in theory it should be it should have a higher conversion rate

Both of these numbers are added here on the revenue sheet so we have first the organic traffic that we're estimating month over month starting April then we have the paid traffic that where's the mating month over month and then that

Gives us a number of orders coming from organic traffic so that's at a five percent conversion pay traffic at a ten percent conversion and then we have active users so these are users who are doing repeat orders I'm gonna get into

The eighties in a second with orders we're very simply doing an estimation on the number of orders that have been created which were eight hundred and thirteen and then that order multiplies Bennett by an average order size so

We're keeping tags on that here also in projections so this is your order breakdown we have an average order size of a hundred dollars we have a product margin of 15 percent that means that out of the out of the cost sorry that out of

A hundred dollar order you're earning $15 and the products are costing you eighty five you can of course change these numbers and say maybe your margin is seventeen your average order is 120 so that's gonna translate automatically

Said we have a shipping and fulfillment costs so if you're using third party logistics this may be a flat fee or you can just do an average depending on the different types of products that you ship then you have a monthly change so

You can we can estimate that your cost per order is gonna get lower or higher so we could do a positive number to say it's gonna increase or a negative number to say it's going to decrease and just to make sure that we don't go overboard

We have minimum possible so I'd say you know the minimum you're ever gonna pay for an order is three this should of course never be lower than zero and then the product launch which is a general setting that controls the other

Variables you can use this to to control when website sales launch and when mobile app sales launch but for now it's it's really not really used okay so these translate to both the revenue and the costs of goods sold sheet so we have

Revenue from orders which are right now at 120 per order for 813 orders and then we run the cost of goods sold we have the orders themselves so we're saying that out of the revenue that we're generating we're gonna be paying 85% of

That into the products themselves so these are the website sales that products that's the cost of the products themselves and then we have shipping cost what we're doing is we're multiplying the number of orders times

Your cost per shipping and we're keeping tags on the cost per shipping the estimated cost for shipping for order here just to keep an average of this alright so that is going to give us a total of products in shipment aside from

That here in the projection sheet we also have a credit card processing and that's why the number of the average cost per order is not 3 that's because we have a premium processing costs so we were doing we're estimating that an

Average order is gonna cost you I'm sorry an average transaction on your card is gonna cost you 29 percent plus 30 cents why because that's the average that that's the average that in a striped house which is a very popular

Credit card processing platform so the way we're so we're using those numbers here in the cause sheet to also estimate how much the payment cross processing is costing and this is giving us this this final average over here so as you can

See this is this biz is a very you know the margins here are based on that 15% 17% per order that we estimated that we're making and we can monitor how this business is behaving here in charts and KPI KPI is where yeah

The or the the green bar which is your sales or your revenue versus the pink bar which is your cost of goods so they're also gonna they're gonna be connected quite closely to each other because they're based on this thin

Margin then you're gonna have the sgna which adds on top of that and then this is a business that is gonna lose money for a while because you're having these orders your mate your shipping these products and these orders profitably but

The sgna the cost of operating the business is still longer this is still more so whenever the the business reaches a certain scale a certain volume which with these variables is happening in 2025 that's when the business becomes

Profitable and of course the game here is to project or to estimate how much money you need to get to that profitability so let's look at some more stuff in here in the projection sheet so this is the app downloads which a mobile

App sales which I'm not getting into in this video this is website sales I think we've covered everything here oh the active user base I forgot okay so with active user basis what we Resta mating here is that you're gonna have some

Repeat orders your customers are gonna come back so you're gonna have a defined number of active users that are gonna come back sometimes to purchase more products this is added here's a percentage of users who make an order

And become active users that means they'll do a repeat order and right now we're saying 50% so going back to revenue if we're saying that we had 50 plus 600 new orders this month that's six hundred fifty half of them fifty

Percent is gonna become a new active user and that's good that numbers gonna be kind of increasing further and then you're gonna lose some because you know there's always turn people that have a fight with you or whatever or just don't

Come back so some of them don't know are no longer active they're considered lost so the projection sheet also lets you estimate this here alright so that active user base we have

An average orders per active user per month so let's assume that not everybody orders per month this is 50% of people ordering every month in other words half of your users make an order in a given month or the average users makes makes

An order every other month you can trace it whenever you want this is optimistic you can you know maybe you could do 03 or less I don't know for one even once 28 alright finally here on the projections we have

Sgna so these are a few kind like general variables that are going to let you project how your expenses your administrative expenses expand we have a nice number here that does an office per desk cost we have a percentage of orders

Requiring human support so the number of orders that that you need somebody to handle maybe you do a refund or whatever orders that a single account agent can manage per month so assume that you have a support team how many of these orders

Of these complains can someone handle every month and then if you want to add agents automatically so this is great because as your number of orders increases assuming that there's a 5% dispute rate we're gonna and two hundred

Orders managed per sales agent we're gonna automatically estimate how these guys do it and this is added here in the sgna sheet so and it's it's done here with the junior customer support persona so the way that formula works is this is

A complex formula I'll explain in a second but what we're doing is we're rounding sorry yeah so we're rounding the projections we're rounding the projections number here so based on the number of orders we

Divide that by 200 and we multiply that by sorry we divide the number of orders or multiply their murderers by 5% and then divided by 200 and the rounded up number is is the number that we add to the number of sales agents dated just to

Explain how these formulas work on in terms of team the team is counted for over here in the teams and salaries sheet and this sheet is very useful because it's going to let you project how your team

Needs to scale as your business grows so for example you could say you know the CEO starts in say October 2019 you guys started working on this Co OS well you guys are taking a smaller salary just in the earliest stages and then you know

You're gonna need a CTO to build this and then that person comes and say January you're gonna need a marketing sales and say that person comes in February and then customer support team you're gonna hire a person and then it's

Gonna it's gonna be populated automatically based on the other based on the other numbers so additional agents are being added via the projection sheet so this this is very nice because for example you can do say

Well how many juniors customer support people do you need well you're you're not going to need any because we're automatically estimating these guys but say with the junior marketing people you're hiring the first one in say June

And every quarter you're gonna add a new person so you have one in the first quarter you're adding one every new quarter and you're gonna get that team all the way to a total of ten so that all gets calculated automatically it

Gets added to this sheet here so if you look at junior marketing we have one person starting every three months we're adding a new person all the way up to a maximum of ten which is pretty efficient this formula is pretty complex I'm not

Gonna get into details on how it's built but you know a good way to if you don't want to have to build this yourself you can simply go in here into the headcount section of the sgna just add these numbers so say you know you're you as a

CEO are gonna start in October and you're gonna be one forever and probably no more than one CEO so you can just extend that formula in and build it yourself you know without having to use the other model it takes a little bit

More work but it also lets you kind of build more details into it right so the magic of a model here is estimating for example these gross margins and we can look at this in the annual summary so the gross margin for your business is

11% why because it's an average of 15% per order but then you have to account for the ship so at 50% per product sold but then you have to come for shipping for the credit card processing fees and so

On so this number is gonna be really interesting and your a particular model when you become profitable that's also gonna be interesting so you need a certain volume of transactions to figure out if your business is profitable but

Also you have to you know looking at this model and the way it projects it allows you to look at the potential and also being realistic about this so we're saying that if your website eventually say you know the end of the model is

Getting six hundred and seven hundred thousand hits per month which is a lot a lot but not impossible but it means now that that's gonna translate into one hundred and twenty-nine thousand orders per month now is that realistic that's

You know that's a question for you and your business but a good way to do it to do this exercise is to say used to go into random month and say okay how much are my orders growing per month how much is that increasing and how does that

Look as a business you know if you based on this model one hundred and twenty nine thousand orders per month yep indle and last year of your business you know that's gonna translate into 120 million dollars in revenue which is gonna

Translate into an e beta which is a margin or a net income margin of around 8 million dollars which is a great business to have hopefully that gives you you know it gets investors excited but that's you know the realistic Ness

Of these numbers is something that you can check you know by going month randomly month over month now last but not least on the investment amount what goes on here is we have a fundraising close date and an investment amount

Fields and those are very useful because they let you project if your business is ever is ever gonna run out of money so right now this business with this business with the website sales enabled has a capital requirement of two hundred

Fifty three thousand dollars why because if you go and scroll over the ending cash balance month over month there is a minimum amount of money of two hundred 40:53 something that's because all this time we've been spending we've been

Having a net income monthly which is negative 18 15 and so on and at this point in February 23 we reach profitability so that's when we start making money but by that time we spent over $250,000 that we didn't have so

That's where the capital requirement comes in what you can do if you want to estimate this better is taking the investment amount and adding say three hundred and fifty thousand and then if you go back to the financial statement

Month line what you're gonna see is your ending cash balance so basically you're starting off with three hundred fifty thousand dollars that you raise in January 2021 and that number decreases because your business is losing money

Month over month until you become profitable but when you do you still have a cushion in the bank of a hundred thousand dollars so that's how you estimate your potential fundraising of course you know the magic of this model

Is that ideally it's gonna let you let you estimate how big a business can be and how interesting that business could be to investors to invest and actually give you the cash you need to build it as I mentioned we are making this

Already built model available for purchase for just 49 bucks link in the description this model is also included on our founders edition plan which provides access to all the features in slight beam monthly

Consulting calls with our team including some time with me as well in our discord we also have a financial model channel so you can exchange ideas and ask questions to the community joining our discord is free and I'm also answering

Questions in there for the next hour I do that after each video goes live so twice a week make sure you subscribe to get notified when that happens see you next week

Related Videos

This video was brought to you by Slidebean Founder's Edition. Get help from our team in your pitch deck, your financial models, and your fundraising. Sign u...
This video was brought to you by Skillshare. By clicking the link in the description for a limited time the first thousand people will get a free trial of Skill...
in the late 90s apple was a ship without a captain closed the first quarter of 97 with 708 million dollars in burn rate it was a defining moment in the story o...
This video was brought to you by Monthly. Finally, a way to keep track of all your company SaaS expenses in one place. Get one free month by using the link in t...
This video was brought to you by NordVPN. Take control of your internet experience with 68% off a two-year plan with an additional month for free at NordVPN.com...
This video was brought to you by Skillshare. The first thousand people to join with the link in the description will get 2 free months on their premium membersh...
This video was brought to you by slide bean founders edition get help from our team in your pitch take your financial models and your fundraising sign up with t...
This video was brought to you by xyro the easiest way to get your brand online create your own website today using the link in the description and get a 30 off ...
This video was brought to you by slidebean founders edition get help from our team in your pitch take your financial models and your fundraising sign up with th...
This video was brought to you by the Slidebean Agency. Get the Design Team behind Slidebean to re-do your slides. Go to slidebean.com/agency and $50 off on your...
Raising venture capital is no easy task; many variables need to come into play and align for a round of funding to happen. We've covered extensively what a ...
As we've scaled sled beam and had the chance to connect with a bunch of companies one of the most common questions I get is around finding a co-founder the ...
Airbnb's original pitch deck from 2009 has become one of the most sought-after references for new entrepreneurs. It was one of the first pitch decks from un...
Each second of this video costs slide being around 2.5 dollars that's $10 just now around $150 per minute and around $1,200 for average video length of 8 mi...
This is the pitch deck that Uber used to raise their first round of funding, back in 2008. I think it's a fantastic example of a perfectly defined problem/s...
I started my first company back in 2010 since then I've had the chance to meet brilliant entrepreneurs with fantastic businesses on their hands however 9 ou...
If you're new to this channel, my name is Caya. I am the CEO of a company called Slidebean. Slidebean is my third startup. The other two failed and left me ...
Hello! Vini here from the Slidebean Team. Today, we’re gonna be looking at the slides Canva used to pitch their company to investors. Welcome back to pitch deck...
What if I started a new business today? How would I approach it? Let's imagine this scenario- Slidebean, the presentation platform I started in 2014 disappe...
Today we’re gonna be looking at the slides of a Swedish company, most of you have heard of, probably even used recently. This is a Spotify presentation. Today, ...