Udemy

2-sided market 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
69 courses
165,253 students
2-sided market 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:31 of on-demand video • Updated June 2022

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]
Two sided markets. There are many similarities in the way two sided market functions on the customer side to e-commerce. So it actually starts the same logic of the excellent will be very simple. We start with visits, which through conversion rates are turned into transaction. Those transactions through average value of the transaction and the provision of the marketplaces generate the revenues of the marketplace. And sometimes we have to also apply the gross margin here because the product costs something in terms of variable costs. So we have done the percentage of gross margin through which we get the gross margin. Then again, as in the case of the e-commerce, we estimate the cost of traffic, cost of logistics and transaction fees. And thanks to this, we get the net margin after the deducing from this to 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 departments. The second part of the market so that you get liquidity and you get transaction happening. So what you have to do is somehow model for the number of transactions and somehow translate it 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. 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 need. Now this is a very good starting point for us to estimate the number of partners we have to acquire, you know, give and period to go further. We need churn and the current number of partners. If we have this, we get the number of partners to be acquired and through average cost of acquisition, we get the cost of acquiring partners, which is obviously reducing the net margin. Now let's see how it looks like in the excellent excel is constructed in a very similar manner, like the one we have seen for E! Commerce. So we we have the summary and then here we have the links to the sheets. If you press any one of them, you go to, uh, specific sheet. And if you press back, you go back to the summary sheet. On top of that, remember that for the promise to be tested, we use blue. And for the bottom of this, we already have verified, we use yellow. Now again, we start with the sheet sets and 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 out of this, we 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 the transaction value generated by the marketplace. So in this case, it's a six hundred thousand. The 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 you live here on the provision. So this actually goes to the to the partners this transaction body generated and you are living from the cops you are getting. So in this case, we assume 50 percent. So out of this, we get the one on the eighty thousand. 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 that just one hundred. So basically, the sales revenue is equal to gross margin. Next week, we will have to look at is the cost of getting visitors customers. So in this case, we again generated very similar 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 it costs one visit. And then we also have no visit we want to generate. So we multiply the structure by the cost per each and every item. And then on the basis of this, we get the the average weighted. And then once we multiply it by the number of visits we get, the total cost we have to spend on getting visitors customers in the specified amount. Cost of logistics. It's the number of transaction we have estimated here. Multiply by our fee we have negotiated with the supplier. Transaction fee is the the value of the sales and multiplied by the by the transaction of the fee. Depending how the basic structure here, you will have either your sales. So in this case, we assume that our sales or the total transaction value generated, we have to be very careful would 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 assume the one hundred percent gross margin. So online marketplace then because of traffic. Cost of logistics, transaction fees, the only thing we haven't mentioned here is the cost of acquiring partners, 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 ensure that we have the right liquidity on the market. So we start with the number of transaction we have estimated in the case and margin sheet, and then we assume a number of offers we need for the customer to make a decision. We here assume that, uh, for the the the customer to feel OK with the marketplace, we basically need 10 offers per transaction. And then the the second assumption is on the number of offers provided per period. We are talking about here in months by suppliers or twenty. So out of this, actually, we get that we need 20 partners in order to make sure that the deflection is happening now. Again, we have to see how many partners we've got. So we start actually, we have 1000 partners and we know that 30 percent of them will 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 the next year because we assumed here lower churn rate in in the periods. As you can see here, the form is relatively easy. The only thing we have to introduce here is to this this maximum condition, which basically prevents you from situation where you'd have your minuses. So whenever you have enough partners, he's basically not recruiting any above the two thousand. So we assume that we are not getting above or below the two thousands now, very similar to the situation of the customers, we have to somehow estimate the money required to get the the partners. So here in a similar manner, 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 per partner. How much does it cost to get one of them? So this says, for example, that through direct sales in the period one, we want to generate 30 percent of the acquisitions and one acquisition cost us 300. This translates to the average cost of acquisition being equal to 248 per partner. Once we have the average cost of acquiring, which is 148, 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 fifty thousand impedance three and then we keep this level for the whole period of analysis. This goes here to the net margin calculation, as we have shown it in and reduces the the net margin, which is here actually very which uses margin. Now we know that it gets to the operating profit. We also have to calculate the fixed cost. Fixed costs are calculated here. So we have salaries matters until this material. Since utilities, maintenance, rents, depreciation and amortization and external services, most of them are estimation done roughly without any formula. The only difference is in salaries, which is the significance usually part of the fixed cost and rent. In this case, we basically when the deeper into the calculation. So in the case of rent, we use the 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 equivalent, then the average salary per full time equivalent and then the Social Security is you have to pay it as a percentage of the salary actually in most of the country. This is important cost position. Having said that, we are able to calculate the operating profit. As you can see, we are generating huge wealth 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 costs 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 pay with the provisions. But that actually can backfire and reduce the number of partners so they off the model and put in the discussion any questions you've 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.