After you’ve built your eCommerce platform, one of the most important tasks you must consider is to estimate its financial performance. Estimating the business model & scale for an eCommerce platform from scratch is not an easy task, even when you use professional tools or software. That said, it doesn’t have to be complicated — in this <5 min. tutorial, I will show you how to create a complete financial model to project your eCommerce business revenue and profits using just Excel or Google Sheets.
A financial model includes all the expenses related to running an eCommerce business. It will allow you to better assess your investment and profit potential from this venture. In this tutorial, we will walkthrough how to estimate the business scale and forecast profit for an eCommerce platform — if you’d like, skip ahead and save time with our financial model template designed specifically for eCommerce!
Why create an eCommerce financial model?
You need to create a financial model to gain insight and make informed decisions about your e-commerce business. By developing this model, you’ll be able to understand your operating environment, by identifying potential risks and making predictions about the impact of different choices.
First of all, we need to clearly set the goal of this financial model — there are many different types. Fortunately, this financial model template gives you everything you need for all different purposes: from automatically-generated annual statements for accounting to dashboards & summaries for cash flow & revenue projections.
In this case, we are going to focus on estimating the business revenue, from which valuation & accounting statements can be derived. This means our focus will be on operating metrics and growth drivers. It is also important to know that there is a huge difference between private companies and public companies. In this article, I will focus on private companies & startups: it is important to understand that business scale estimation for public companies might be different, mostly in the way that GAAP accounting numbers show financial results that may have little correlation to actual true earnings and cash flows generated by a company.
How-to create an eCommerce financial model in Excel or Google Sheets
One of the key components of any eCommerce business plan is to develop a financial model. A financial model will provide an overall projection of all income & expenses related to running an eCommerce business. This will allow you to better assess your investment and profit potential from your venture, and plan your future roadmap accordingly—
If you’d prefer to watch (rather than read), click below to see the 5-min. video tutorial:
Assumptions Sheet
This is where the magic happens behind the scenes — you can easily control the entire revenue projections component from this sheet of your financial model. The Assumptions sheet is the place to model a) your business’ marketing funnel, as well as b) cost of goods (COGs). This sheet will generate both your projected revenue, as well as your variable expenses (Marketing & COGs).
To start, outline your marketing budget on a monthly basis, and how it will be allocated across various channels (ie. social, search, affiliates). From here, reference industry benchmarks (ie. average per acquisition/click cost, % click-thru or conversion) to predict how many users you will acquire from each channel, based on the marketing spend for that channel (ie. if you spend $100 on Google Ads, with an expected $2 CPC and 10% conversion rate: $100 ÷ $2 CPC × 10% conv. = 5 expected sales). Be sure to include the sources for your assumptions!
UPDATE: The eCommerce Financial Model Template “v3” update includes a section for calculating COGs for up to 6 product lines in cells C101:C129 of the “Assumptions” sheet. Here, you can populate the sales split (% of total sales), product names, prices, and costs (margins are auto-calculated). If you have less product lines, simply leave the name and other values blank. Product names will automatically populate into the Cash Flow Summary and Income Statement sheets.
Once you’ve completed this for all channels, a simple sum of the figures will return your total projected Revenue and Marketing cost — all that’s left is to fill-in your cost of goods (COGs) based on your suppliers / manufacturer’s estimates.
For those using our financial model template, the various acquisition channels and related industry benchmarks are already pre-populated. The template will cover the full gamut of marketing channels: from social media and search to affiliates and influencers. You can easily specify your “channel mix” by changing what % of your marketing spend is allocated to each category, and plug-n-play to view the results!
Salaries & Wages
This sheet lets you automatically track how your team’s headcount affects your expenses. See what your future team looks like and how this affects profitability.
You may define your team by categories and monthly or annual compensation they receive, rather than individual employees. It has been designed with growth in mind, as you have an opportunity to structure deployment of new additions to the team, along with future salary adjustments. Don’t worry about estimating payroll & benefits yourself — we’ve automatically calculated those at the bottom of the sheet (adjust as needed).
This simple sheet will help you quickly forecast future hires: intuitively model the number of employees you’ll need and how much it will cost to maintain them.
Fixed Expenses
Some of your business expenses will fall outside both Salaries & Wages as well as Cost of Goods Sold (COGs) — these fixed operating costs should be accounted for in the accordingly-named sheet of your financial model.
Fixed costs are expenses that do not change based on production levels. This does not mean they don’t change at all, but there is no direct relationship to the number of products produced. Fixed costs include your rent, utilities, and insurance. Enter these expenses in the sheet (some values have been pre-populated for your convenience in our handy template).
Income Statement & Balance Sheets
A financial statement is a formal record of a business’s financial activities. Shareholders (and potential investors) analyze statements to see how the business is doing and how management is leveraging the company’s funds and resources. Businesses prepare three major statements are prepared for every accounting period: the balance sheet, income statement, and cash flow statement.
Our financial model template automatically generates all of these annual statements based on what you enter into the Assumptions sheet, so you can present a convincing & complete overview of your projected &/or past performance.
Cash Flow Summary & Charts
In all of our financial model templates, I always include a “Charts Dashboard” sheet to give you a visual overview of how the model plays out as you tweak your assumptions and plans. This sheet is an easily accessible reference so that you have a picture of how the model performs in real-time. Watch the video tutorial above to see it in action!
What key topics should an eCommerce financial model cover?
Entrepreneurs in the eCommerce space are most curious as to which “must-have” questions should be answered by every financial model. We came up with the shortlist below based on an analysis of 20+ eCommerce businesses we’ve worked with — several of which closed multi-million-dollar deals & investments.
Below are the questions every eCommerce financial model should answer:
- What is the average order value or price?
- What is the expected annual volume (and Y/Y growth)?
- What is the average profit margin?
- How is the channel mix broken down?
- What is the average cost of customer acquisition?
- What is the lifetime value of a customer?
- What is the annual profit & revenue after 3 – 5 years?
Creating an eCommerce financial model? Check this out:
You might be interested in the popular eCommerce Financial Model Template designed by our team at VIP.graphics: cutting-edge templates based on pitch collateral that closed millions in deals & investments for orgs of all sizes: from high-growth startups to Fortune 100 corporations. Super simple to customize in Excel and Google Sheets, this template offers you pre-filled benchmarks & auto-generated sheets to help you project your business: it’s a matter of minutes to create and share this convincing & professional financial model with your stakeholders & investors — learn more here.
