How To Use Our Marketplace Financial Model Template (+ Free Preview)
Disclaimer: this article is for anyone who downloads our Marketplace financial model template and need a step-by-step guide on how to use it. We remind you that our template also comes with a 1 hour onboarding video as well.
Before we dive in the step-by-step guide, note that we are using for the purpose of this article the Google Sheets version. You can of course download the template use it with Excel instead. Let’s dive in!
Step 1: Settings
The first thing we need to do is to enter a few details in the Settings tab. They are:
- Company name: the name of your business / project
- Start date: the first month the model will forecast from. Note that the first month should be in the future, and it isn’t necessarily the following month. For instance, assuming we are today in July 2021, and you are raising capital which you expect to last 2 months, the start date can very well be September 2021 or even later.
- Fiscal year end: by definition, this is the date that defines the end of a 12-month period for accounting purposes. In our templates, this date is important as it will serve to calculate the annual summaries of your P&L, balance sheet and cash flow statement. Note that most businesses have 31st December as fiscal year end. When in doubt, ask your accountant.
Note: the year in the fiscal year end is not relevant. For instance, if your fiscal year ends on December 31st, 31/12/2021 and 31/12/2022 are exactly the same thing.
- Currency: the main currency of your business and in which you want to present your financials. Note that the currency does not impact any calculation: it is simply used as a legend throughout the model.
Note: if your business operates in different currencies, use the same currency throughout for your model. For instance, assuming you sell your products in USD but have some staff paid in EUR and your reporting currency is USD. In this case, the expenses (the salaries of the staff in EUR) should be converted in their USD equivalent when you enter your hiring plan (more on that below)
- Corporate tax rate: this is the tax rate you pay in your jurisdiction. If you aren’t sure which is yours, refer to KPMG’s exhaustive updated list here.
Step 2: Monetization
A marketplace can generate revenues from a number of sources. The template allows you to choose which one(s) are relevant for your business. They are:
- Commission percentage: if you charge a percentage of each order. For instance, you earn 20% of all products that are being transacted on your marketplace
- Commission (fixed fee): if you charge a fixed fee for each order. For instance, you earn $10 for each transaction
- Subscription fees: if you charge subscription fees to the sellers for them to be able to list their products on your marketplace. Our template gives you the ability to segment your sellers into up to 4 different categories, with each a separate pricing. Of course, you can also set a free plan if you operate a freemium
2.1. Average order value
Before we dive into commission revenues, the first thing we need to set is the average order value (AOV) of the product being transacted on your marketplace.
There are 2 types of AOV: new buyers and repeat buyers AOV. Indeed, repeat buyers tend to have different buying patterns vs. new buyers: they buy more and more often. As such, repeat buyers typically have higher AOVs.
The template allows you to set different AOV for up to 5 years. The types of products and/or services that transact on your marketplace might change over time, and their prices as well.
Note: if you have existing metrics, get the AOV from your dashboard (e.g. Google Analytics or similar). Otherwise, set an assumption for AOV as the weighted average price of the items being transacted on your marketplace. For repeat buyers AOV, set a premium (e.g. 125% new buyers AOV).
2.2. Commission revenues
Now that we have set the AOV, we need to set assumptions behind commission fees. As explained earlier, the template gives you the ability to have both commission percentages and fixed fee commission revenues. The values you need to enter are:
- Commission percentage: the percentage of AOV you charge (the sellers or the buyers). For instance, if you take a 20% commission on all purchases, enter 20%
- Fixed fee commission: the amount you charge for each transaction. For instance, you earn $10 for each transaction, enter $10 here
Note: commission percentage and fixed fee commission are additional. For instance, if you set 20% and $10, the model will include both sources of revenues for each transaction. Assuming a $100 transaction, revenues will be $30. Therefore, if you only charge one or the other, leave the relevant input to zero.
2.3. Subscription revenues
The marketplace financial model template allows you to earn revenues from subscription for sellers only. As such, you can set a series of assumptions for up to 4 subscription tiers. They are, in order:
- Subscription name: the name of the subscription tier. For instance, “Free” for a free plan, “Premium” or “Paid” for other plans. This name will flow through the rest of the model.
- Monthly fee: the monthly price of the relevant subscription.
- Annual fee: the annual price of the relevant subscription. Note that this is the annual amount, for instance here whilst Paid users pay $19.90 monthly, they only pay $190 annually (20% discount)
- Annual: the percentage of sellers who choose annual billing over monthly billing.
- Breakdown: the percentage of new sellers who fall into the respective tiers. In the example above, 80% of new sellers (who “convert” i.e. join the marketplace for the first time) choose the “Free” plan, 10% choose the “Paid” and so on. The sum of all percentages need to be 100%.
- Churn: the annual churn percentage for the relevant tier. In our example above, all sellers churn at a rate of 10% per annum. The different percentages for each tier allow you to differentiate the tiers where a given tier might be more “sticky” than another. Indeed, paid users tend to be more sticky (i.e. they churn less) than free users.
Step 3. Acquisition
In the Settings sheet we need to enter now assumptions for Acquisition. This is where we will estimate how many users you will acquire over time (how many will join your marketplace). As you would have guessed, we need to forecast 2 types of users: sellers and buyers.
3.1. Sellers
The number of sellers you have on your marketplace at any point in time is the function of how many you acquire, minus the ones who leave (“churn”, as we saw previously). Here we need to estimate how many you acquire over time.
In the marketplace financial model template we forecast the number of sellers you acquire by setting 3 different parameters, they are:
- Acquired Sellers (first month): the number of sellers you acquire in your first month of operations. The first month is by default the start date of the model
- Acquisition monthly growth: the monthly percentages at which the number of acquired sellers grow over time. For instance, if you acquired 100 in your first month, and monthly growth is 20%, then you acquire another 120 in your 2nd month.
- Customer Acquisition Cost (CAC): the total cost you incur in paid marketing (e.g. paid ads) to acquire one seller. For a refresher on what CAC is, read our article on the most important metrics for marketplace businesses. By multiplying the number of acquired sellers by the CAC we then obtain the paid marketing spend which we will include in your profit-and-loss as an expense.
3.2. Buyers
The number of buyers you acquire over time is calculated differently vs. sellers. Buyers are “acquired” thanks to paid marketing spend (separate from the paid marketing budget for sellers). As such, the number of buyers is the result of the following equation:
Acquired buyers = Paid marketing / Cost-per-click
Therefore, we need to set 3 different assumptions:
- Paid marketing (first month): the amount in paid marketing you spend in your first month of operations. By default the first month is the start date of the model
- Paid marketing monthly growth: the monthly percentages at which the paid marketing budget will grow over time. Indeed, you will very likely increase your spending over time as you grow. For instance, if you spend $5,000 in your first month (as in our example above), and you grow at 10% every month, you will spend $5,500 in your 2nd month.
Note: the same logic than acquired sellers monthly growth applies here too. The monthly percentages are decreasing over time as the actual numbers of your marketing spend grow. Otherwise, because these percentages are monthly, you would quickly end up with very large numbers..! Think 20% monthly growth for the next 5 years, or 60 months, this is a ~5,600,000% increase. Be careful: percentages might be high in the first months (e.g. 20%) because numbers are low, but they drop usually quite quickly (e.g. 10% max. or so within a year for instance) and finally end up around ~2/3% in the outers years (4th and 5th year).
- Cost-per-click: the average cost-per-click of all your campaigns. If you are running search and social media ads for instance, enter the average CPC. For more information about cost-per-click benchmarks per industry, have a look at our article here.
Step 4. Conversions
In the same Settings sheet we now need to enter assumptions behind conversion. This is where we will calculate how many purchases (or conversions) you generate each month from the buyers you acquire on your marketplace.
Yet, as we said earlier, we need to differentiate new vs. repeat buyers. As such, the number of conversions are the sum of both new and repeat buyers conversions:
Conversions = new buyers conversions + repeat buyers conversions
4.1. New buyers
New buyers conversion are estimated simply as the number of new buyers you acquire (new buyers haven’t necessarily made a purchase yet: they are leads who landed on your marketplace and for which you incurred some paid marketing spend).
Therefore we simply need to set a conversion rate. In our example above, we assume that new buyers leads convert (make a purchase) at a 3% rate. In other words, for each 100 new buyers leads landing on your marketplace, only 3 make a purchase.
4.2. Repeat buyers
Repeat buyers conversions are estimated differently vs. new buyers. To do so, we follow 3 simple steps:
- What percentage of your buyers are repeat customers? Here we assume a given percentage of first time buyers who are repeat buyers. In other words, how many of all buyers who already make a purchase will make at least another one in their lifetime? If set at 75%, this means 1 out of 4 buyers will only make a purchase and never come back. The other 3 will come back and buy again at a frequency we will set later
- How often do repeat buyers buy (per year)? The number of times repeat customers make a purchase. Are they purchasing in average every year (therefore 1) or every 2 months (6)?
- What is the annual churn for the repeat buyers (if any)? Typically, repeat customers do not buy indefinitely. Instead, like sellers, they churn over time. Whether the churn rate is high or low depends on the type of products you sell and the success of your retention and cross-selling strategy. A rate of 20% for instance will mean that repeat customers churn after 5 years
Step 5: Hiring Plan
One of the biggest expense category of Mobile app businesses often is salaries. It is therefore very important to set up your future roles and their related expenses (salaries, bonuses) correctly.
5.1. Teams
The first thing we need to do is to list the different teams you have and group them into up to 5 categories. This will allow us to differentiate salaries into the different teams in your P&L and your metrics later on.
Note: if you selected inbound acquisition under Acquisition (section 3 above) earlier you will need to enter at least a “Sales” team so the template can calculate correctly the number of Sales people that generate customers in the Acquisition sheet.
5.2. Roles
In the Hiring Plan sheet, you will need to enter all the roles you expect to hire in the future. For each, you will need to enter (as shown in the table above), in order:
- Role: either the role or the name of the recruit
- Team: drop-down list to select the name of the team the role falls into (the teams are the ones we set up earlier)
- Salary: the annual gross salary
- Starting: the date when you expect to hire the role.
Note: if you already have existing roles before the start of the model, enter the date in the future you recruited the role. For instance, if you are CEO and started to work since January 2021 but the first date you project your financials from is September 2021, enter January 2021 as the starting date for CEO.
- Taxes: any taxes and benefits you incur on top of salary, expressed as a percentage of gross annual salary
- Leaving. If you have contractors with fixed term employment contracts for instance, enter the date at which you will not employ that role anymore
- New salary amount. If you expect to increases salaries in the future of certain roles, enter the new annual gross salary here
- New salary starting date: the date you start to pay the new salary
- Bonus %. If you pay bonuses on top of salary to certain roles, enter the corresponding percentage of annual gross salary you pay each year as a cash bonus.
- Bonus timing. Here you can set whether you pay bonuses every month, quarter or yearly.
Step 6: Expenses
In the Settings sheet you can enter a number of assumptions for the marketplace financial model template to calculate automatically the expense amounts in Expenses.
6.1. Payment processing fees
Enter the percentage of revenues you will pay to your payment processing provider (e.g. Stripe, Paypal, etc.)
6.2. Customer Support
Customer support is an important expense category for most marketplace businesses. The marketplace financial model template gives you the ability to forecast Customer Support in 2 different ways, either:
- Enter the customer support roles one by one in the Hiring Plan as shown above; or
- Forecast customer support salaries as a function of the number of users you have
We are covering here the 2nd approach. Customer support salaries will be calculated by taking into account 3 different assumptions which you will need to set:
- Percentage of customers requiring customer support per month. Let’s assume you have 1,000 active customers in a month, not all might contact your customer support service. For instance, if a customer calls your CS say, every 2 months, enter 50% (indeed, 50% of all active customers contact CS every month, ie every 2 months)
- Number of tickets processed by a customer support agent per hour. Here we set the “efficiency” of your customer support team. If you are a Enterprise SaaS business, the tickets may take more time to be processed (hence an agent might only process a handful per hour). In comparison, if your tickets are usually dealt via email within a few minutes (e.g. B2C SaaS) enter a higher number here
- Hourly cost of a customer support agent. This is the total hourly cost you pay your customer support agents (including taxes and benefits, if any)
Note: If customer support is outsourced to a 3rd party instead (to Teleperformance for instance), simply enter here the hourly cost you pay per customer agent.
6.3. Hosting
Enter here the amount you spend per month per user in hosting, tech infrastructure, etc. If unsure, ask you your CTO.
6.4. Team expenses
For each team, you have the ability to set a spending per role, it includes:
- Onboarding costs: any one-off you incur when you hire a role (computer, desk, etc.)
- Ongoing costs: any recurring expenses you incur per month per role (subscription, meal allowances, etc.)
- Travel: the travel allowance per role per month. Management and sales team may travel more than the engineering team for instance, so you can differentiate them here.
6.5. Other expenses
Enter here any other recurring expenses you haven’t included yet. For each expense there are 4 assumptions to set:
- Expense: the name of the expense
- Start date: the date from which you start to incur such expense
- First monthly expense: the amount of the first month
- Increase (per year): the percentage increase (per year) at which the expense grows over time (if it does)
Note: if the expense stays flat over time, leave percentage increase to zero instead.
Step 7: Balance sheet
- Cash starting balance: the amount of cash you already have in the bank before the start of the model
Note: do not include the fundraising round (see below) you expect to raise here. It will be added automatically to your cash balance.
- Losses carry-forward starting balance. If you have any tax credit from your state, enter the amount here. For a refresher on what losses carry-forward, see Investopedia’s definition here.
- D&A lifetime amortisation. Any capex you will spend in the future (see below) will be depreciated over time. This is where you select the depreciation schedule. If in doubt, ask your accountant.
Step 8: Capex
If you expect to spend any capital expenditure in the future (building, equipment, etc.) or simply capitalise part of your expenses, enter the amount under “Other capex” in the Capex & D&A sheet.
Note: by default the marketplace financial model template already includes onboarding costs (see team expenses) within capex. Indeed, onboarding costs such as computers and desks are assets and need to be included within capex and depreciated over time.
Step 9: Fundraising
This is where you can set the fundraising rounds you expect in the future. Set here 3 assumptions:
- Round: name of the round (e.g. Seed, Series A, etc.)
- Amount: the amount you expect to raise from this round
- Date: the date of the round