Financial model for e-commerce business
Caya
June 10, 2020
  |  

Financial model for e-commerce business

Caya
June 10, 2020
  |  

Join 100,000 entrepreneurs who read us every month

We've got it!
You'll be hearing from us soon.
Oops! Something went wrong while submitting the form.

I am going to show you how to estimate the business scale for an eCommerce platform with our financial model template.

Also, the model I will create today is available for purchase, you know, if you want to save some time. 

There are many kinds of financial models; this one is closer to a Forecasting Model, which is used for financial planning and analysis (FP&A)

In a nutshell, what an effective model should do is:

1- Take an estimated ad/growth/marketing/sales spend. 

2- Estimate the revenue generated from it. 

3- Estimate the costs associated with generating that revenue, including team, office, server, etc..

4- And finally, give you an answer on whether this combination of variables in making the company grow. 

For early-stage startups, the model should take in the team size, the team expansion, and compare it to the available cash, perhaps from a round of funding, to understand what the exact company runway is. 

If you've done your model right, you should be able to scale your team and your budget, understand the revenue impact of those changes, and measure how much that will affect your runway. 

Here are the BASIC parts of a model: 

COGS Sheet 

That stands for Costs of Good Sold and relates to the direct costs associated with providing your service. 

For a supermarket or an eCommerce store, COGS is straightforward. It's the cost of the groceries or the items sold that the company pays to the suppliers. 

Most software companies use COGS for server costs and other essential tools that the platform needs to be functional. In our case, tools like Intercom and Amazon Web Services are part of COGS. 

If you compare revenue to COGS, what you get is the Gross Margin: the margin your company makes before accounting for the administrative expenses. 

Revenue Sheet

Revenue is used to track and estimate, well, your revenue. 

What is absolutely vital for any model is what drives your revenue. Revenue doesn't just 'come.' You have to bring it in. Depending on your business, you'll need to pay to market your app or pay to get leads. You might even need to pay for a sales team to close those sales. 

If, for example, your model estimates a $10,000/mo marketing budget that doesn't increase, it makes no sense that your revenue grows to $10MM in year 3. That's just impossible. 

There needs to be some correlation with reality here, and many of these benchmarks are available out there.

Some examples:

  • App installs using Facebook ads can cost a few cents in low-competition countries, and $2 in competitive markets like the US. 
  • There is no way to get Google Search Traffic for less than $1 per click, and most keywords require bids of $5 or $6 per click.
  • An average conversion rate on a landing page could be around 25%. 50% is remarkable. More than that is unrealistic. 
  • As the company grows, acquiring customers with paid ads usually gets more expensive, not cheaper. 

These numbers need to be taken into account in your model. Growth and revenue don't come magically. 

Your model should show the math behind your expected cost of acquisition. This is absolutely key. I can't stress it enough. 

SG&A sheet

Pretty much every expense that does not classify as COGS goes into the SG&A sheet. That includes payroll, marketing costs, travel, office expenses, rent, accounting, consultants... you name it. 

Again, it's imperative to connect these to your revenue estimates. On a SaaS platform (software subscription), you could say that you'll need to hire a support agent for every 1,000 active customers on the platform. 

So, you can connect the number of active users on your revenue sheet, to the number of employees on your SG&A sheet. This lets you estimate your margins in the future. 

You can also connect the number of team members to the size of your office, and therefore, rent. Or, you can compare the number of employees and the number of seats you'll need on your CRM. 

It's those connections that let you be very accurate about your growth expectations and your company expenses. I challenge myself every month so that my projected SG&A at the beginning of the month actually matches our bank accounts. 

Working capital and CAPEX 

This sheet is meant for assets owned by the company. If the company buys a car, that car is not an expense; it's an asset. 

The car affects your cash flow, yes, because you no longer have that cash available in the bank, but the asset should be logged in the model. The number of assets a company has an impact on its valuation, or, for example, if the company goes bankrupt and needs to liquidate them. 

Desks, computers, and other matches are often assets and not expenses, and these are to be logged on this sheet. 

While most software companies don't need to pay a lot of attention to this section, eCommerce platforms do. Working Capital is critical for them because they might need to pay suppliers before they collect sales revenue. 

All I've done for our template is to implement an automatic laptop buying system. So, every time the number of employees increases, the model estimates that you'll need to buy a new computer for them. Neat, right? 

You can, of course, add other purchases of your own. 

All of this stuff gets consolidated on your Monthly Financial Statement sheet. These sheets provide you a summary of your gross margin, net income per month, cash in the bank, and so on. Taxes are also automatically estimated here. 

All this stuff gets consolidated on the Annual Sheet, and that's where you have your official growth estimations. 

Now I added a few sheets of my own, let me tell you about those.

Team and Salaries Sheet 

This sheet lets you automatically estimate future hires without having to dig directly on the SG&A sheet. 

You may, for example, define your dev team, how often do you expect to hire new team members, and how often do you plan to adjust their salary. 

It's designed with employee categories in mind, so you shouldn't add each employee by name, instead, classify your team in each one of these buckets. 

Make sure you use company cost for their salary, not actual salary paid out to them. In other words, include your payroll costs here, so that you don't have to deal with those on a separate line

KPIs Sheet

I've also added charts and a KPI sheet that lets you visually check the results of the model, as well as giving you some critical insights like an estimate of the capital you need to raise. Whether you can reach profitability in the projected five years, the model includes. 

Projections sheet

This is where the magic happens, and this is where we're going to be working today. Watch the video above for the step-by-step breakdown where I am going to build a Website Sales model that will let you project the scale of your sales expenses. 

As I mentioned, we are making this already-built model available for purchase for just $49. This model is also included in our Founder's Edition Plan, which provides access to all the features in Slidebean, and monthly consulting calls with our team, including yours truly. 

In our Discord, We also have a financial Model channel so you can exchange ideas and ask questions to the community. I am also going to be answering questions for the next hour. I do that after each video goes live. Make sure you subscribe to get notified when that happens. 

Subscribe to our channel