E-commerce financial model - basic version

A free video tutorial from Asen Gyczew
Expert in performance improvement, turnaround and startups
Rating: 4.5 out of 5Instructor rating
78 courses
239,198 students
E-commerce financial model - basic version

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:32 of on-demand video • Updated January 2024

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 in Excel. Before we go into details, let me start with a short intro. We're going to present here the logic we're going to use in the Excel 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 revenue side of the e-commerce player. Then on the basis of that, using the 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're going to estimate three three types of costs. 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 payment platform we're using. On the basis of this, we're going to get something which we'll call net margin and to get to the operating profit, the last level we will be reaching. In this modeling, we need fixed costs. Every model is built roughly in the same manner and we're using the same philosophy. So we will have a summary sheet where we have the links to the the underlying sheets. If you go to any of them, you can always go back through this link to the summary. This is especially important if you build a very complicated model with a lot of sheets. This allows the other guys to get around easy. So we start with the sales and margin development. Here we're going to go exactly as we did in the PowerPoint intro. So we start with the number of visit, which is in period one we actually give it by months. It's two hundred thousand visitors. Just just to remind you, blue means that we haven't tested the assumptions. So this is something we have to test in the in the P stage or we have to test it in the very first month. Then we have to put some conversion rate again assumption. We assume that this will be two to percentage and out of this much being one by another one, we get four thousand through action. Then we put the average assumed transaction value, which is one hundred fifty, and we get to the 600 K per month. Now having those sales revenues, now let's go to gross margin. So gross margin is calculated on the basis of the sales and revenues we've calculated above here, and that assumes gross margin. Again, it is in doing so. It is something you have to test out of this. We get one hundred eighty thousand per 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, cost of logistics and transaction and out of this after we calculated them will get net margin. So let me just go through how we go around and estimates 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 one on the road. So this says that 20 percent of our visitors to our site will be a direct. So they basically know the site and they just directly enter of the scene the first stage. It might be that it's close to zero because they don't know your site 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 basically, you got the emails and the emailing them Google AdWords. So obviously, by showing ads at the search, you get the traffic affiliation if you have them display ads, so it could be on other places other than, uh, search and Facebook ads. Always, we will have others. So you're going to have this here if you have any other ideas, so it could be also offering ideas. Now this is the the share, and then we have two for each and every source, try to estimate how much it costs us to get one visit from there. So obviously, directions zero or close to zero organic the same, we have to pay for new start. This may be for sure for Google AdWords of the Asian display ads, et cetera. So now let's see how the formula works. So basically, what what it does, it multiplies the structure at which you get to the traffic by the cost. So in this way and this very first pass of 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 to get. So in order to get two hundred thousand visits, 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 we have calculated here, we put them here and we put also the cost of delivery, post-production. We have negotiated with our supplier. Now let's go to the transaction fees. So transaction fees are calculated very easy. So we get basically the the revenue, the revenues, sales we want 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 would 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 form that 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 margin, which is in this case, seventy six, as we have shown in the pipeline intro. We need also the fixed cost. So basically everything which is not connected with generating the course. So here you you'd have to. The head office is a work. Of course you would also have any development you are using and this is actually quite important whenever you are trying to get to the break, even point. You know, it goes, we're going to be using very simple formulas. So we have as a cost position, salaries, materials and utilities, maintenance, rents, depreciation and external services out of this. Salaries are somehow complicated and a little bit difficult manner. And then also the rent for salaries. We are using the so-called fees, so fees of full time equivalent and it is much better to use than the number of people being assembled people. It's not really a good estimation of your costs. Usually how you would estimate the cost is to translate a number of people into full time equivalents. How you do it. It's very simple. You just look at the number of hours they are working for you per month and divided by one in 70. So if somebody is working 80 hours, it is roughly half FTE. The total cost we get of salaries, we get by multiplication of the 40s, the average sales. And then we have something which is very important in many countries, such as Social Security in the case of Poland and constitutes up to 23 percent and this is substantial. So model for this as well, then rents is estimated in an easy manner. We get the square meters and we have the the fee per square meters. We multiply them and we get the costs. So all in all, we get eighty two thousand 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 space in margin sheet is 76 six. But the fixed cost, it's actually eighty six thousand and we end up with negative operating profit, which basically means that we are burning a lot of money. And actually, as you can see, there is no like 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. Think what you 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 the structure of how you get the traffic and the cost per visit. Many maybe by optimizing some of them with changing the structure, you can get to the cost of one visit lower, which will help you boost the results up and uh, will help you to stop burning money. The other thing you could have a look at is the fixed costs. So just make sure that you need all the people and make sure that the office you you've got is basically not too big for your budget. That's all when it comes to e-commerce. And as you can see, this is just one of the or 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 adjust for your way of getting the revenues. Please also put this in. The discussion will answer you and we'll provide you with a modified version provided we can obviously do it. OK, have a good luck.