E-commerce financial model - basic version

Asen Gyczew
A free video tutorial from Asen Gyczew
Expert in performance improvement, turnaround and startups
4.4 instructor rating • 53 courses • 100,965 students

Lecture description

In this lecture I will show you how you can build the model in Excel and what conclusions can you draw from it.

Learn more from the full course

Financial Modeling for Startups

Learn how to model a startup business in Excel and draw conclusions about your business

02:50:31 of on-demand video • Updated July 2021

  • Model your business in Excel
  • Manage your business based on numbers / KPIs
  • Set well priorities to value creating activities
  • Pick the right business model for your business idea
  • You will understand what is important in your business
  • You will know what KPI you should look at and what are the benchmarks
  • You will be able to write down your business model in the form of an Excel model and to draw conclusion out of it
English [Auto] Modeling of e-commerce. So before we go into details let me start with a short intro. We're going to present you here. The logic we're going to use in the X-O to reflect the the business model. So in the case of e-commerce we'll have some visits that through conversion rate will translate into number of transaction with thanks to assumptions on the average value of transaction. We'll get to the revenues of the e-commerce play. Then on the basis of that using the gross margin expressed as a percentage of the revenues we'll get the gross margin before going any further. We'll have to estimate the costs related to the business. So we are going to estimate 3 3 decks of course. One is the cost of getting traffic to our site then the cost of logistics. So delivering the goods to the customers and also transaction fees related to the transaction we have made so many to the payments that we are using on the base of this were going to get something which we will call that margin and to get to the operating profits. The last level we will be watching this more link. We need tax cuts. Every model is built roughly in the same manner we are using the same philosophy. So we will have a summary sheet where we have links to the the underlying sheets if you go to any of them we can always go back through this link to the summary. This is especially important if you build a very complicated models. And so with a lot of sheet This allows the other guys to get around easily. So we start with the sales and margin development here we going to go exactly as we did in the prior point. So we start with the number of visits which is in period 1. We actually give it by months. Two hundred thousand visitors. Just to remind you. Blue means that we haven't tested the assumptions so this is something we have to test in the NDP stage. All we have to test in the very first month. Then we have to put some conversion rate and an assumption. We assume that this will be two to percentage and out of this by multiplying one by another one. We get 4000 reduction then we put the average assumed transaction value which is 150 and we get to the 600 K per month. Now having those sales revenues. Now let's go to gross margin gross margin is calculated on the basis of the sales in revenues we've calculated about here and the assumed gross margin again it isn't. So it is something you have to test out of this. We get 180 2000 the month of gross margin in order to get to the net margin as we mentioned before we have to somehow estimate the cost of traffic because of logistics and transaction and out of this. We calculated them to get the net margin. So let me just go through how we go around and estimate the cost of traffic. So basically what we have to do is first of all estimate the structure in which we get the traffic to our site. So here we have the sources and this is the they share in generating the total traffic it should add up to 180. There you go. So this says that 20 percent of our desert just outside will be a direct. So maybe not the site and they just directly enter the scene. The first stage. It might be that it's close to zero because they don't know either side. And this grows over time to those 20 percent that we can actually model it here over time. Then organic search. So they basically put it into the Google or Bing and they get to your site as a result of the research newsletters and emailing so that you got the image and then emailing them Google AdWords. So obviously by showing ads that we search you get the traffic affiliation if you have them display onse. So it could be on other places other than search and Facebook ads. Always we'll have others. So you're going to have this here if you have any other ideas. So it could be also of fun. Yes. Now this is though the share and then we have two for each and every source try to estimate how much it cost us to get one visit from there. So obviously there's only zero or close to zero organic the same. We have to pay for new starters may be true for you Adwords for the display ads and cetera. So now let's see how the family works. So basically what it does it's money buys the structure in which you get to the traffic by the cost. So in this way and this very first Pozzo some product we get the average weighted cost of getting one visitors and then it basically multiplies by the number of visits we want to get. So in order to get 200000 business we have to spend sixty six thousand per month. Now let's see we take care of the logistics logistics. Basically we take the transaction and we have created here. We put them here and we put also the cost of delivery purchases action. We have negotiated with our suppliers now to go through a transaction piece. So transaction fees are calculated very easy so we get basically the revenue the revenues sales we're going to generate and then we put the transaction fee as a percentage of those revenues. This is the usual practice in most cases or in some services you can have a mixture. So usually it will be a fixed amount which is small. And then the percentage and then it can be also capped by some sort of a form. So really here it should be reflected depending on what kind of phone you're using. Now as we mentioned before we get the gross margin from here the cost of traffic from here because of logistics from this formula intersection from here and we get the next minute margin which is in this case 5:53 as we have shown in the pipeline interval. We need also the fixed cost. So basically everything which is not connected with generating the calls. So you'd have to the head offices or head office you also have any development you're using and this is actually quite important. Whenever you are trying to get the break even point you know OK we're going to be using very simple formulas. So we have as a composition sourest material and utilities maintenance rents depreciation and extreme solaces out of this salary Some somehow complicate it a little bit difficult manner. And then also the rent for salaries we are using the so-called he's so as to ease isa full time equivalent. And it is much better to use the number of people who is a symbol of people. It's not really a good estimation of the cost. Usually how you estimate the costs is to translate a number of people into full time equivalents. How you do it is very simple. You just look at the number of hours that waiting for you per month and divide by 1 and 70. So if somebody is working 80 hours it is roughly half empty. The total cost we get sourish we get by month. But the creation of the fte the average sales and then we have something which is very important in many countries which is Social Security. In the case of Poland it constitutes up to 23 percent and this is substantial. So mobile for this as well then rents is estimated in easy manner. We get the square meters and we have the fee per square meters. We must apply them and we get the cost. So all know we get 82 thousands per month of fixed costs. So how does that translate to the piano. Well as you can see the net margin from the previous exercise from the sales and margin sheet is 76 box the fixed cost. It's actually eighty six thousand. And we end up with a negative operating profit which basically means that we are burning a lot of money and actually as you can see there is no way out in the next 12 months. There is apparently no movement upwards. So this would probably mean that you should go back to the sales and margin I think Waldie can change in terms of the conversion or in terms of the average value transaction. The other thing you could have a look at is the structure of how you get the traffic and the cost per visit many maybe by optimizing some of them or changing the strategy you can get to the cost of one visit lower which will help you boost the results up and will help you to stop burning money. The other thing you could have a look at is the fixed cost. So just make sure that all the people and make sure that the office you've got is basically not too big for your budget. That's all when it comes to e-commerce as you can see this is just one of the many models you can create in terms of e-commerce. If you have any questions please put them into discussion if you would like to know how to modify it to just for your way of getting revenues. Please also put this in the discussion. We will answer you and will provide you with the modified version provided we can obviously do it. Okay. Good luck.