2-sided market financial model - basic version
A free video tutorial from Asen Gyczew
Expert in performance improvement, turnaround and startups
4.4 instructor rating • 53 courses • 101,998 students
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 courseFinancial 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] To site its markets there are many similarities in the way two sided market functions on the customer sites to e-commerce. So it actually starts the same logic of the X-O will be very similar. We start with visits which through conversion rate are turned into transaction those transaction through average value of the transaction and the provision of the market. They generate revenues of the marketplace and sometimes we have to also apply the gross margin here because the product cost something in terms of body costs. So we have the percentage gross margin through which we get the profit margin. Then again as in the case of the e-commerce we estimate the cost of traffic because of logistics and transaction fees. And thanks to this we get the net margin off the deducing from this. The fixed cost we get the operating profit. So up to this point it was very very similar to the e-commerce model. But the problem is that in this case you also need the partners. The second part of the market so that you get liquidity and you get transaction happening. So which has to do is somehow model for the number of transaction and somehow translated into the partners. So what are we going to do is first of all assume some sort of number of you need for the customer to feel that he has a choice. And for him to be able to choose the one he likes and make the transaction there. The second assumption we have to do is the one about how many of us are being provided by one partner if we have those two things then we can easily calculate the number of partners we meet. Now this is a very good starting point for us to estimate the number of partners we have to acquire compared to go further. We need to churn and the current number of partners if we have this would get the number of partners to be acquired and through our cost of acquisition we get the cost of acquiring partners which is obviously the juicing the net margin. Now let's see how it looks like in the Exel XO is constructed in a very similar manner like the one we have seen for e-commerce. So we have the summary and then here we have the links to the sheets if you press any one of them you go to a specific sheet and if you press back you go back to the Summary sheet. On top of that remember that for the pharmacy to be tested we use blue and for the parameters we already have verified we use yellow. Now again we start with the sheet sets in margin. So here let's have a look at how we generate the sales revenue of the marketplace. So we have the number of visits it's 200000. Then the conversion rate this would get the number of transaction. Now if we put the average transaction value of the of what's happening through the marketplace we get to do a transaction value generated by the marketplace. So in this case it's a hundred thousand. The difference between this and what we have seen in e-commerce is that we have to also have some sort of a provision because we do that here on the provision. So this actually goes to the two departments this transaction by the generators and you are living from the you are getting. So in this case we use 30 percent or so out of this we get the one on the 80000. Now in some of the cases especially when we are talking about physical goods physical marketplaces we will also have to apply the gross margin in the case of online goods. We could assume here thats just 100. So basically the sales revenue is equal to gross margin. They think we will have to look at is the cost of getting visitors customers. So in this case we generated very similarly to how we did it in the case of e-commerce so we have the structure of the traffic we are getting in percentages and we have how much cost one visits and then we also have a number of visits. We want to generate. So we multiply the structure by the cost per each and every item and then on the basis this we get the average wages and then once we multiply it by the number of visits we get the total cost we have to spend on getting us customers in the specified amount. Of logistics. Its the number of transaction we have estimated here multiply by our fee we have negotiated with the supplier for Section fee is to the volume of the sales and multiply but by the transaction of the fee depending how the basic structure here you will have a 0 sales. So in this case we assumed that our size or the total transaction value generated. So we have to be very careful what you put here and this really depends on the choices you made and on the type of the market you are and then thanks to this you get all the items. So the gross margin which in this case is equal to the sales because we assumed the 100 percent gross margin. So online marketplace then cost of traffic logistics transaction fees you don't think we haven't mentioned here is the cost of acquiring Pathans which is calculated in a separate sheet as we have mentioned. We also have to somehow estimate how many partners we need and how much it will cost to get them and to be sure that we have the right liquidity on the market. So we start with a number of transaction we have estimated in the sense of margin sheet and then we assume a number of as we need for the customers to make a decision here assume that for the customer to feel okay we've done the marketplace. We basically need 10 of those transaction. And then the second assumption is on the number of office provided type period. We are talking about here in months. Buy supplies are 20 so out of this actually we get that we need 2000 partners in order to make sure that the notion of happening. Now again we have to see how many partners we've got. So we actually we have 1000 partners and we know that 30 percent of them would just leave the market for whatever reason. So this translates into the need of getting one point three thousand partners the first period and then smaller amount of partners in our next period because we assumed here lower churn rate in the periods. As you can see here the form is relatively easy. The only thing we have to introduce here is the maximum condition which basically prevents you from situation where you have your minuses. So whenever you have enough partners he's basically not recruiting any above the 2000. So we assumed that we are not getting above or below the two thousand now very similar to the situation of the customers. We have to estimate the money required to get the the partners. So here in the seventh month we estimate the average cost of acquisition per partner. So we have the methods by which we acquire them mentioned here the structure and the cost partner. How much does it cost to get one of them. So this says for example that through direct sales in the period 1 we wants to generate 30 percent of the acquisitions and what that acquisition cost us three hundred This translates to the average cost of acquisition being equal to 248 per partner. Once we have the average cost of acquiring which is 2 and 48 we just need the number of partners to be quiet which we estimated here and we get the total cost we have to spend on acquiring the customers. As you can see in the first period it's actually a lot but then thanks to reducing the churn rate we go down to 50 doesn't get 3 and then we keep this lever for the whole period of analysis. This goes here to the net margin calculation as we have shown there and reduces the net margin which is here actually very very which uses margin. Now in order to get to the operating profit we also have to copy the fixed costs fixed cost are co-creators here. So we have salary's matters into these materials and it is maintenance rents depreciation and positioning certain services. Most of them are estimation done roughly without any pumila. The only differences in salaries which is significant. Usually part of the fixed cost and rent in this case. We basically when they're deeper into the calculation. So in the case of Rand We used a number of square meters and the cost per square meter and in the case of the salaries we basically used three parameters to estimate it. So the full time equivalents. So not on the number of people with the full time equivalents then the average salary for them equivalent and then the Social Security you have to pay as a percentage of the sorry action most of the country is this is important cost position. I that we were able to calculate operating profit as you can see we are generating huge growth in the first period and then we are almost getting to the break even which actually suggests that we should either work on reducing the fixed cost or more likely work on reducing the cost of acquiring partners or the churn rate. The other option would be to work on the customer side so increase the conversion rate or increase the average value of the transaction. Maybe even with the provisions but that actually can backfire and reduce the number of partners. So to not model and put into discussion any questions youve got regarding the model if you happen to have a bit different construction of your marketplace or other two sided market piece put it also in the discussion. We will try to prepare a model for you as well.