The ULTIMATE Marketplace Financial Model | Build From Scratch
Вставка
- Опубліковано 7 лип 2024
- We learn how to build a two-sided marketplace financial model in Excel, one of the most important business models for eCommerce startups. Included below is a downloadable template.
✅ Marketplace Financial Model Template Download: www.ericandrewsstartups.com/o...
🚀 If you want to master the finance skills & frameworks to successfully scale technology startups, secure your spot in my "Finance for Startups" program, today: www.ericandrewsstartups.com/f...
Communities:
💼 Linkedin: / eric-andrews-1624b656
☑️ Twitter/X: / eric__andrews
🌟 Instagram: / ericandrews_startups
🔥 Discord: bit.ly/discord_eric
💻 Newsletter: bit.ly/joinericnewsletter
🎥 Clips: / @ericandrewsclips7818
Related Startup Videos:
🚩 Talent Marketplace Financial Model: • Creating a Financial M...
🚩 Marketplace Unit Economics Journey: From Idea to Exit: • Unit Economics Benchma...
🚩 How to Correctly Calculate CAC for Marketplaces: • How to Calculate CAC f...
🚩 Seed Funding Guide - Most Important Metrics: • Seed Fundraising Guide...
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Many startups these days look to build marketplaces that serve as infrastructure for businesses to market, sell, etc. But eCommerce marketplaces are complicated because they require a delicate balance between supply & demand.
If a marketplace doesn't have enough supply, there will be nothing to buy and the users will churn. And if the marketplace doesn't have enough demand, suppliers will not be able to sell. So having the right balance is tricky, and scaling this balance rapidly is not easy.
In this video, we learn about how to plan, build, and forecast a marketplace business:
Sections:
0:18 Intro to customer acquisition model for sellers vs. buyers (supply vs. demand)
4:14 Seller acquisition in a marketplace (paid acquisition, organic, direct sales team)
9:58 Forecasting seller churn
12:45 Buyer acquisition in a marketplace (paid, organic, seller-driven)
19:27 Intro to customer retention for eCommerce
19:50 Build customer cohort model to calculate customer retention & revenue in eCommerce
23:43 Individual customer cohort explanation and calculation (new and returning clients)
28:57 Calculating total orders (new & returning) for revenue
29:35 Building income statement for eCommerce marketplace
30:34 Marketplace financial model revenue assumptions
32:00 COGS assumptions
37:58 Gross margin
39:08 Easy way to project payroll and operating expenses for a startup
50:07 Operating profit, burn rate, cash runway, breakeven analysis
CONTEXT: ECOMMERCE MARKETPLACES TODAY
Most large technology companies are marketplaces for something. Amazon is a marketplace for online stores to find clients. Facebook is a marketplace for online advertisers to target groups of people. Google is a marketplace for online advertisers to target specific search queries. Uber is a marketplace for drivers to find passengers.
This is not to say that all tech companies are marketplaces, but taking something that has a costly physical infrastructure and rebuilding it digitally has incredible advantages.
MARKETPLACE FINANCIAL MODELS
In terms of building a marketplace, you need to think about customer acquisition on the seller as well as the buyer side. Customer acquisition costs will likely be much higher for sellers than buyers.
You will often have to do your own paid customer acquisition, but also be assisted by buyers bringing in clients, and organic traffic should get amplified with scale on both the supply and demand sides.
PRO TIP: CUSTOMER RETENTION IN ECOMMERCE
If you have any non-subscription business, you will expect customers to return many times over many years - but the frequency and timeline are hard to predict with simple return rates.
With e-commerce (and any business), there is really only one very accurate way to calculate customer retention and recurring revenue - customer cohort models.
In this video, we learn how to build a customer cohort model using retention assumptions to predict repeat buyer behavior for our clients.
This is probably something you've never seen before, but it will give you a much more accurate way to forecast recurring customers.
BURN RATES / CASH RUNWAY / PROFITABILITY
Marketplaces require significant upfront capital to build and scale - so you need a solid financial model for this.
In the beginning, you will fight for each customer and each seller, but over time organic traffic will snowball on the buyer & seller side.
You need a clear idea of all of this so that you can fundraise accordingly and predict burn rates.
I hope that you are now confident in building marketplace financial models. If you have questions leave a comment below and I'll try to help. Cheers!
#marketplacefinancialmodel #marketplace #startups #ecommerce
Questions? Let me know in the comments happy to discuss.
Amazing teacher, thank you for taking the time to give out your wisdom for free!
Fantastic!!!
Thanks for this video @Eric Andrews, the simplicity of your model and the way you explain it was REALLY HELPFUL!
Really glad to hear it appreciate the comment arjan
Thanks Eric. Your specialty is that you explain everything in the easiest possible way.
I appreciate that!
Amazing! Straight to the point, explained everything in detail and never got bored. Keep up good work!
That's awesome I'm super glad to hear it. Cheers!
Thanks Eric. This video was really insightful and you have squeezed in all the important information here. Seems easy while watching the video but when put in action it is a little time consuming for me as a beginner. Really appreciate your efforts.
Hey Jai - really appreciate the comment and so glad the explanation was helpful. This stuff is NOT EASY so take your time to understand the concepts and practice these models. It's a lot of material in short video tbh. It took me years to develop these skills and I'm hoping to help other people learn them a bit faster than I did! I never learned this stuff in college either, so that's why I'm trying to get this info out. Lmk if you have questions and so glad you learned some important things 👍👍👍
I cannot express how much gratitude I have for this tutorial, thank you Eric.
You are very welcome!
Super intuitive and insightful! This channel has the best no-nonsense excel finance content. Who needs to spend thousands on business school when you have Eric??
Really appreciate that gregory!! Thanks for your comment and checking it out!
Hi Eric,
Your videos are Top! I've been going through youtube looking for this type of information (knowledge) and it was very difficult to find. But thank God I managed to find your videos. They will help me in building my startup. Thank you very much.
Thank you so much,
Thank you for leaving me this comment I absolutely loved hearing this from you! Good luck with your startup 🚀
Thanks, fantastically helpful!
Appreciate the tip, glad the video was helpful!
Been waiting for this one! Thanks Eric!
Glad to hear it! Thanks for letting me know keyspan!
This is great! Couple of question
1. How to calculate the CAC for both supply and demand side
2. On the financial model how to consider cashbacks?
Hi, @Eric Andrews, Thank you very much for sharing such a valuable resource!
You are very welcome!
thanks eric, this is what i have been looking for
You are very welcome!!!
Awesome video. This is exactly what I needed. Thanks a lot, Eric. A lifesaver!
My pleasure Yasser - really glad it helped. Good luck with your marketplace!! 😀😎
Amazing!!!!! So much learning with this video
Glad to hear it !!
Thank you so much! this is great!
Wow! Great Video. For someone who is new to finance it's a lifesaver! Thanks a lot :)))
Thank you so much for the useful information.
You're very welcome!
Thank you very much Eric, it was very insightful and detailed. Unlike other models with such complexities, this was done with good explanation and simplicity.
Musa - hey I'm glad you found that this video made these concepts simpler. Thanks for checking it out!
thank you for the high quality video, looking forward to your next lesson
My pleasure, glad it was valuable. Dropping my next video Tuesday...stay tuned 😊
This was super helpful, thank you for the download and for the video describing the process in so much detail.
no worries glad it was helpful for you jason
Thank you
My pleasure Olayinka!
Hi Eric, thanks for sharing your knowledge and you are so generous in giving free templates.
I hope you can make Financial model/ projection for start up restaurants as well . Thanks Godbless you.
Thank you for this video. Super helpful!
My pleasure Zeus 👍
Fantastic video. Super helpful!
Hey - really glad to hear it! Let me know if you have any questions.
Much appreciated Eric. This is so helpful! Thank you :)
You're very welcome!!
Fantastic, thank you so much for the incredible content.
My pleasure. Glad it was valuable Daniel!
Hello from Kyrgyzstan! Thank you a lot, this video was really helpfull in understanding financial model in marketplaces♥
Hello! Thanks for tuning in, happy to hear that this video was helpful for you.
@Eric Andrews, Thank you very much for sharing such a valuable resource. In the Acquisition Model, you have considered the total number of clients as the sum of Buyers (B37) and Sellers (B41). Further, this total number of clients was used for arriving at the total number of orders and revenue calculations. However, in my opinion for a two sided market model, the total number of orders depend on the number of successful transactions that takes place. In this case, it is limited to minimum of (number of Buyers, number of Sellers). Thereby, the input for Customer cohort Model at F6 should be equal to 40 instead of 115. I got confused at this point. Not sure if I am making sense? Please suggest where I am going wrong.
Hi Eric - Thanks for the really clear model. How can I calculate LTV (lifetime value) in this model?
I was looking for a good financial modeling course as we are launching our tech startup. This is one of the best short courses I have seen. Thanks a lot Eric
Hey Afroz - so glad to hear it! Let me know here in the comments if you have any questions. If you build a model like this for your startup you will be ahead of most startups. Good luck!
@@eric_andrews Thanks Eric. Yes, since we are building a marketplace, we have SaaS tools (which we plan to give for Freemium) and we have a transactional model as it is a marketplace so we charge % on transactions. Hope this model will help me build both? Just trying to make sense how to combine your Marketplace video and the Ecommerce video and make one model.
@@afrozkapadia209 got it. So you are going to charge sellers a subscription fee and then also a % transaction fee when buyers purchase? If that's the case, just model the subscription revenue off the sellers total and the transaction rev off the buyer sales. This model should work perfectly. Make sense?
@@eric_andrews We have two ways: One is that we give free SaaS tools to sellers and charge them transaction fees And charge SaaS fees to buyers but no transaction fees. For this case, which model will suit the best? And if that is the case, do we put sellers transaction revenue in COGS and Buyers SaaS revenue in actual revenue?
Secondly, in your existing example, since we take 20% of the seller revenue, should it not be in Revenue? why is it going to COGS? Not sure if I am making sense?
Lastly, is there an email address I can reach you at to make a custom financial model?
@@afrozkapadia209 On the seller side, I would just show all the revenue (100%) from the platform in the revenue section, and then in the COGS you would report 80% of that revenue getting paid back out to sellers (assuming you charge 20%). For buyers, is the money you make from them an additional up-charge on top of what they buy? If so, report those SAAS fees as another revenue stream. Apologies - I'm not able to do individualized work currently due to my schedule.
I'm in
Thanks stanley! Thanks for leaving the first comment!!
Could you explain the Variable expense of rev?
What does it do?
Hi Eric, thanks so much. Learnt a lot and I did my projections alongside your video! Quick question - In the Customer Cohort tab, Orders/Client is 1.3. How do you derive this or is it a std estimation?
Hey - great! This is an estimation. The basic idea is that most customers will on average buy more than 1 item, on average we are estimating 1.3. That is a made up assumption for this video but it is based on eCommerce businesses I've worked with. It's important to think about your CAC thinking about customers acquired, not orders.
Great video Eric! This is so far the best video in financial modelling. Very well explained. Sadly, the download url is not working. Can you please check back again?
Hey Abdullah - I really appreciate you letting me know! You are a hardcore financial modeler if you enjoyed this one 😎.
So I just double checked the URL - it seems to be working fine for me. Can you try a different browser maybe? Here is the direct download link: mailchi.mp/ff6b388e3974/marketplace-financial-model-download
Great content Eric! Exactly what I was looking for. Question regarding churn: I noticed you only applied it to the seller side and not the buyer. Is that because a portion of the buyer calculation is derived from the seller number after factoring in churn?
Hi - no. The buyer retention ("churn") we cover in the customer cohort model part of the video. Buyer retention is usually non-linear and cannot be accurately forecasted with a simple churn rate. For sellers we just used a basic churn rate for simplicity.
Each of these methods gets us retention, they are just using different math.
Hey Eric thanks for this great model. Can you provide a bit more detail on how to generate the assumption for Orders/Client please?
Sure, if you had a real business you could just do the analysis historically and see how much the typical client is ordering. If you have no historical numbers you would be making a best guess based on your product (i.e. is it something people will buy more than one of).
Thanks for the great explanation. Curious how the cohort model holds up to a B2B services model. For instance, if buyers typically purchase 3-month contracts, would you just adjust retention in the cohort model to 33% monthly?
No, that would be 100% retention for those 3 months, and then there would be a drop off period at that break point. But these cohorts work just as well for B2B as B2C. They are used widely in eCommerce, SaaS and marketplaces.
This is very helpful - thanks! Though am wondering if a similar model applies to an app enterprise marketplace like Salesforces' AppExchange or Azure's Marketplace.
Hey, yes I think it would apply. Those marketplaces take a percent of each sale, correct? And probably once a customer joins the marketplace they continuously buy over time. Same business model
This has been really usefull. Thank you very much. Is there a way you can add yearly increases in salary? The model has a flat salary for all the years.
Yes you can just put a percent increase every 12 months in the salary line and increase everything by 3 or 5% for all salaries is an easy way to do it
Hey Eric,
Is there a video on converting this into a Financial Projection Chart(Total Sales, Gross Margin, and Net Income)? If not, can we take this conversation offline.
Thanks for another great model guide Eric! One question - in the customer cohort tab, is there a shortcut to coding the monthly total customers across all cohorts so that you don't have to add each diagonal cell for each month? That part seems tedious, especially if you're building a longer forecast.
My pleasure Duncan! I haven't figured a way to completely automate the writing of that formula but I did find something that made it a lot faster. I started with writing the formula for the final cohort (dec 2024), rather than the initial, and then just copied the formula UP row by row and just deleted the last reference each time. That made it pretty fast actually and automated like 90% of it.
Hi Eric & Duncan, I actually found a ~2 min way to do cohort analysis and total client count using nested if, index, match & sum and since Eric’s videos have literally been the best finance resource I’ve found on UA-cam EVER, I’d be happy to share my file if you are interested. Let me know!
Hey, Eric!
How would a startup go about thinking about their financial model that is not too into the weeds/specific on hitting a number metric by x year and is not too general that might make one think that the company didn’t thoughtfully think through about their model & future growth plans?
Hey Tom - I would just make sure the model has a really solid breakdown of the unit economics for an order, CAC, and payroll. Don't be overly aggressive on your sales forecast, especially in the first 12 months of the model, so that with your raise you know you have a decent runway of 12-18 months. From there, just make a simple 3 year plan that is defensible and also leaves plenty of upside (so that you are able to explain situations in which you could outperform to investors). That would be my basic advice.
Loved this video Eric! Question- I have a sliding scale commission structure for the seller depending on Selling Price. How can I incorporate that into the Summary P&L?
Simplest way would probably be to just create an assumption that splits your avenue into a few different buckets. So maybe 50% of your revenue would fall into one selling price tier, 30% of a lower tier, and 20% another, and from there you just calculate the different commission percentages off those pieces of revenue.
@@eric_andrews Thanks Eric for your super fast response! Do you mean I put in different percentage probabilities for revenue tiers instead of using a single average order value? Would it work if I put five different AOVs and average commissions corresponding to those different bucketed AOVs? For example, for SP bucket of 100$ to 300$, my AOV is 200$ and my payout for this range would be 40% and then I make four others such as this?
Hi Eric, Do you also have a Customer Cohort model for a B2B marketplace? Regards
Not on my channel, but it would be identical mathematically usually. Is there something structurally that would be different?
Hi. Thank you for sharing this template. It has really made my task easier which i was just find so hard to get my head around. I have 1 question though regarding the Customer Cohort Model sheet- Why have you assumed the Order/client at 1.3? what is the logic behind this 1.3 ?
Sure, usually your typical client will place more than 1 order in a month long period. 1.3 is a reasonable guess for an average over a group of people.
@@eric_andrews what if the client is placing 1 order in the 1 st month and then maybe another 6 months down the line? E.g. like say Airbnb. Not every customer is going to go to an Airbnb every month. What would be the percentage to consider in such a scenario?
Thanks in Advance.
Hi Eric, this is insightful and super clear. I wonder if you can do an advertising-based business model since you have covered social media, subscription and e-commerce? Thanks a lot
Hey I'm glad to hear that it was helpful. The social media model is an advertising-based business. If not that, then what exactly did you have in mind? Always looking for new ideas !! Cheers 👍
@@eric_andrews Interested in Search Ads such as Google and Social Ads such as Tiktok. Just an idea. Thank you for the great content!
@@zhaozhaoliu9489 got it, I'll think about what I could create around that. Thanks for the idea and thanks for checking out my other videos as well!! 🙏🙏
What if you measure retention on an annual basis instead of monthly? Be just four columns?
Yep, that would for sure work.
I'm an accountant for a new tech company that runs a similar operation to this one you just demonstrated. I have great inside on the income statement now. Is it possible you do a video on the balance sheet and cashflow statement?
Hey Olayinka - glad it was helpful. Yep - I've already got a video with the P&l, cash flow, and BS all together, we call it a "3 statement financial model", here's the link ua-cam.com/video/xlXDZyZ9azk/v-deo.html
@@eric_andrews You're the best. Thank you
@@olayinkaakosile happy to help!
Thanks man. Learned a lot. Really appreciate your effort. But I have a question for you: regarding the “New Capital”; how do I determine its value? So, how did you arrive at the conclusion that you might need a $2m seed capital investment and then later an additional value of $5m?
I mean what was your basis if you were to forecast that & include it in your model/pitch deck for an investor?
Cheers Eric!
Hey Ahmed - happy to help and glad you learned some new stuff. That's a great question and I can definitely help. So there are really two questions you should be thinking about: (1) how do you know how much money to raise, (2) at what what valuation do we raise the money (what will be the dilution).
First off, usually you raise money to try to cover future losses for roughly 18-24 months. So you would look at your model and figure out how much money you would be burning for that period, and then decide an amount. Obviously your model will be wrong but it's a decent rule of thumb. You might end up burning the $ faster or maybe end up turning a surprise profit. Time will tell.
Secondly, you need to think about the valuation. So if you sell $2MM in in shares, what % of the company are you selling? A very rough valuation for ecomm companies at this stage would be 10X your ARR (annual revenue run rate) - so take your monthly rev X 12 months to annualize, and multiply by 10. Then look at the amount of new capital you need divided by the total valuation - that will be your dilution.
That's a basic framework of how to think about it, although obviously everything always depends on the situation.
@@eric_andrews Thank you Eric. Your reply really helped.
But, I have another question for you: so, when I am roughly calculating the valuation, you've mentioned "take your monthly rev X 12 months to annualize, and multiply by 10"... here, which monthly revenue to be exact? Or did you mean the "average" monthly revenue across the entire 48 months?
Then, for example, after following your model, I found that the "OPERATING PROFIT/ BURN RATE = - $ 619,878". If you were to assume a value for "New Capital", what would that be? Or would it still be "$2m" like your model? and then in April-22 another "$5m"?
Last but not least, I realized that in your operating model, you've only stated the P&L statement. Before presenting the model to an investor, shouldn't I also show them both the Balance Sheet and the Cash Flow statements? And if so, how can I do that?
I really appreciate your guidance and help!! :)
Kind regards,
Ahmed.
@@ahmedrabie9162 Ok ! I'll try to help with those questions as well.
For valuation, you would take the single month when you are planning to raise the capital, and multiply by that by 12 to get the annualized revenue. Some startups will even look forward a for months in their model for it, but using the current month is a good way. Don't use averages of other months.
For my $2MM and $5MM, I wasn't putting much thought into it. But I would recommend looking at the fundraising month, and looking at the following 18 months of burn rate to see how much capital you need to survive 18 months more. That is how much I would raise. When that runs out you raise again. But don't pay to much attention to the numbers I put in, I was just freestyling it :)
In terms of balance sheet, it depends. If your business has debt or holds significant assets, yes you would likely want to include BS and CF. If not, your P&L represents the cash flow just fine and just calculate your cash balance at the bottom. A couple months ago I worked with a business that raised a $30MM venture round without a balance sheet, just a p&l, because they are a service business and have no assets or debt basically, so the p&l represents cash flow.
Cheers!
@@eric_andrews Thank you Eric. Your explanation was very useful. One last thing, in terms of "Charts & KPIs", which charts do you recommend to use and what areas do I need to focus on while designing the KPIs for example your model?
Cheers Eric! :)
@@ahmedrabie9162 I'm more interested in metrics than specific charts. Generally you want to know your revenue ARR (annual run rate), customer acquisition cost, customer lifetime value, and cash runway. I would say beyond that you could always make a bar chart showing revenue and profits scaling over time to visualize the breakeven, but that definitely isn't necessary, would be more to do just for fun.
Hey Eric, I am struggling with trying to keep my commission percentages low on both ends. I know we have to cover credit cards fees incurred as COGS, so obviously commission must be over 2.5% . Unfortunately, trying to keep low commission percentages results in a very undesirable GM%. I know benchmarks vary by industry, but what is the typical GM% for a service marketplace??? Thanks!
Typical GMV take rates (GMV that they convert to revenue) range from 20-30% based on what I've seen...can be much lower or higher depending on your product. No rules are set in stone though just depends if your customers are happy. Good luck!
how about if the model have both income from commission per transection and monthly subscription
Are they separate from each other? Meaning, you have a subscription product as well as another product?
how to make charts for this?
Hey Eric, this is amazing :) Thanks a lot
Where can I find the downloadable template?
I'm glad it was valuable!! Right there in the description in the "resources & links" section. Cheers
@@eric_andrews Thanks Eric
Another question here would be, how do i find CLTV? I believe it can be assessed via cohort analysis? Is there a formula for that?
@@abhyanksrinet8662 Hey Abhyank, yes you're right, with a non-subscription business like our marketplace the way to think about it would be cohorts - the same way we calculate revenue in this model. With cohorts you just need to look at historical retention numbers and project them forward, the same way we calculate revenue, and then just add up all the lifetime revenue of the cohort (the month), multiply by the gross margin, and divide by the number of customers. That will get you the lifetime gross profit for each customer aka LTV.
I have two videos where I dive deeper on LTV:
ua-cam.com/video/tuXILsUljKc/v-deo.html (I talk about cohorts in minute 14)
ua-cam.com/video/eHi875QuVcA/v-deo.html
I think they might help!
Are the numbers here being presented according to GAAP?
Hey Tara - yes. That being said, I didn't model in anything that would require complex GAAP accounting rules (think cash vs accruals or weird revenue recognition), but yes the basic organization of the model follows GAAP accounting.
just out of curiosity, which marketplace charges a buyer upsell commission?
Airbnb, and many more
Dear Eric,
It was quite astonishing seeing content like this available online and people like you are really helping the humanity.
i just have one quick question:
With the help of your video i was able to make to income statement for which i am very thankful. Now, i have a startup which is pre-mature(not marketed yet). i am raising investment. With no assets and liabilities at this very moment will i still be needed to show the balance sheet and cash flow statement? if yes, do you have a template for the same? i really appreciate having your earliest response in this connection. i search your page but couldnt find the template related to marketplace.
I'm so glad! No you wouldn't need a balance sheet at your stage, just an income statement should be fine.
@@eric_andrews Thank you very much for your prompt response and help. I appreciate the support by heart. I hope one day i repay the support 😉 Keep up the good work..
@@eric_andrews one last thing to inquire. What reason should be told for not having balance sheet and cash flow to the investors?
@@packpal you are very welcome!
@@packpal no assets (other than cash), no debt. Income statement accurately reflects the cash flows.
This looks like a simplified version of the model that my friend Natalie built for her VC
Hey Justin yep that's because that's exactly what it is!! A starting model for any startup that's looking to raise money 👍
HI Eric, link to download is not working. Is there any other way to download it?
It works sometimes it just takes 5 minutes to arrive, give it a second!