What is a Financial Model?

You have a business idea, and your family and friends think it is awesome! That is good news, but have you done any calculations on the profitability of this potential business?

In this post, I am going to show you how to estimate the revenue, cost, and profit of your eCommerce business idea using an Excel template. You can also download my FREE calculator spreadsheet and customize it for your own business.

Business planning and financial modeling is what can shed light on your business ideas. According to Investopedia, “Financial modeling is the process of creating a summary of a company’s expenses and earnings in the form of a spreadsheet that can be used to calculate the impact of a future event or decision.”

Here are the steps that we are going to follow:

  • Market and revenue assumptions: we will start with high-level market assumptions (traffic, spending habits, etc.) and translate them into potential numbers for revenue.
  • Cost assumptions: includes all the operating and capital expenditures that you need to serve the total number of customers you estimated in the previous step.
  • Financial statements: I will show you how to create an Income Statement, a Balance Sheet, and a Cash Flow Statement based on your revenue and cost estimates. But do not worry! If you use my Excel template, these financial statements will be populated automatically based on the inputs from previous steps.
  • In the last step, we can calculate the financial profitability metrics like IRR and NPV. These metrics can show if your business idea will be profitable or not.

Market and Revenue Assumptions

The first step in developing a business plan is to know the market. In our example case, our fictitious company is going to sell yoga mats online in the US market. To understand how much we can sell, we need to know the online market for yoga mats.

One approach that can give us an understanding of online demand for yoga mats is to see how many times people search for it on Google and Bing.

Google provides a few free tools that we can use to estimate the demand for products. We also know that a substantial portion of the market go straight to Amazon to do product search, but in this case, we are going to consider only selling on our online store.

To see the total number of searches in Google Ads Keyword Planner, you need to have your credit card added and have an active Ad campaign running. Otherwise, you can use some other tools like SEO PowerSuite to pull data from Google Ads. There is a free version of SEO PowerSuite that will be more than enough for this purpose.

If you are using SEO PowerSuite, open Rank Tracker tool and select “Keyword Research” >> “Keyword Planner” from the left panel. Then select “Google Ads Keyword Planner” tool and start searching for the keywords related to your product.

seo powersuite rank tracker tool

Add the keywords that could generate sales for your business and put them in the spreadsheet. You can also add the CPC (Cost per Click) that shows how much paid advertising will cost you. (Image below shows an example of keyword research results. [Revenues & Mkt Assumptions])

You can view and download the template MS Excel file from Microsft OneDrive here

google traffic prjection

The next step is to check your competition. Check the companies that are ranking on the first page of Google for your keywords. Check the quality of their backlinks, their domain strength, etc. There are so many tools in the market that you can use.

After finding your main competitors on Google, open Backlink Checker tool from SEO PowerSuite to check their backlinks and domain strength.

For example, yolohayoga.com is one of the competitors in the market. You can see that they have more than 8000 backlinks from over 1 thousand websites. You can also check the “Domain Strength” of their brand in this tool as well. Checking the backlinks and their quality in Backlink Checker tool gives you a good understanding of how hard it is to compete and rank in the first page of Google for your keyword.

seo spyglass backlink profile

Now that we have a good understanding of the competition in the market, we can start setting targets.

One key concept that can help you to create smooth and logical planning is called S-Curve.

S-Curve is a mathematical graph that shows relevant cumulative data for a project. The reason that it is called “S” curve is because it looks like the letter S. It is proven that the normal process of a company growing in a market usually follows an S-Curve pattern. The good thing here is that we can create an S-Curve for our goal by setting expectations at 2 different points during the process.

The target could be defined like this:

“I would like to take 0.5% of the market after 12 months, and 3.5% after 36 months (about 3 years). My goal is 5% market share in 5 years)”

Now, let us map this goal to an S-curve.

  • 0% market share at the start. --> Base = 0
  • 0.5% market share in 12 months --> Point A
  • 3.5% market share in 36 months (about 3 years) --> Point B
  • Goal is 5% market share in 5 years --> Saturation = 5%

Now we can create a smooth monthly plan based on the information above. See [Revenue & Mkt Assumptions] tab in the template file.

S-Curve for market penetration

The [Revenue & Mkt Assumptions] tab in the template file splits total traffic to your site into multiple channels:

  • Organic traffic: coming from search engines for free. Relevant content and SEO can increase this traffic.
  • Referral traffic: is from other websites (Facebook, Twitter, etc.)
  • Paid traffic: traffic from paid advertisements on Google, Bing, etc.
  • Email traffic: coming from your email marketing campaigns.

By using total traffic and reasonable conversion rates, we can estimate total sales and revenue.

Note: On the spreadsheet, yellow-highlighted cells are assumptions and can be changed based on your business needs. But other cells are formulas.

The last part in the [Revenues & Mkt Assumptions] section is converting traffic to sales estimates. You need to set two parameters:

  • Conversion Rate%: what percentage of your site visitors will purchase
  • Average purchase price

Cost Assumptions

In this part, you are going to calculate the costs for setting up and running your business.

Business costs can be divided into OPEX (Operational Expenditure) and CAPEX (Capital Expenditure)

  • OPEX is the money a company spends on an ongoing, day-to-day basis to run a business or system.
  • CAPEX is money that is spent to acquire, repair, update, or improve a fixed company asset, such as a building, business, or equipment.

In our yoga mat business case, the summary of CAPEX and OPEX costs are as follows ([Brief Revenue & Cost] tab):

ecommerce financial model revenue and cost summary

This summary table is based on detail calculations in [Cost Assumptions] section in the spreadsheet.

CAPEX costs can be depreciated based on their useful lives. For example, if the useful life period for a laptop is five years and it costs $1,000, it loses $200 of its value every year until its book value becomes $0 at the end of the 5th year.

The next step is to calculate the [Depreciation & Amortization] tables. These calculations will be useful for populating financial statements.

Financial Statements

Financial statements act like the brain in your business plan and financial model. The input to these statements is the output of revenue and cost that we estimated in previous steps. Now we are going to analyze this data and calculate the profitability of our business idea.

First, let us review the definition of the three major financial statements:

Balance Sheet: is a financial statement that reports a company’s assets, liabilities, and shareholder equity.

Income Statement: also known as profit and loss (P&L) statement provides valuable insights into a company’s operations, the efficiency of its management, underperforming sectors, and its performance relative to industry peers.

Cash Flow Statement: provides aggregate data regarding all cash inflows that a company receives from its ongoing operations and external investment sources. It also includes all cash outflows that pay for business activities and investments during a given period.

For the sake of simplicity, the financial statements that we use in our model assume that you put your own money into the business. So, no loans from banks. In future versions, I will share financial models that use third-party loans as well as your own equity.

The financial statement will be calculated automatically based on the inputs from revenue and cost summary. You only need to review the yellow cells (assumptions) and adjust them based on your business characteristics.

Is your idea profitable?

Now that you are all set to calculate the profitability of your business idea, you can calculate NPV and IRR metrics.

NPV (Net Present Value): is the difference between the present value of cash inflows and the present value of cash outflows over a period.

IRR (Internal Rate of Return): is a metric used in financial analysis to estimate the profitability of potential investments.

Below is the summary of your assumptions and calculations in the profitability calculator:

The Cash Flow chart shows that our business will become profitable after almost 4.5 years and the net present value is around $13k.

You need to note that business planning is a back-and-forth process. That is, based on your initial assumptions for revenue and cost, you will get an NPV and IRR. If it is not what you were looking for, you can get back to your assumptions and see if you can make changes to make your plan more profitable. For example, by removing unnecessary costs or setting more aggressive targets (if possible)

You can also create different scenarios (e.g., optimistic, base, or pessimistic) and change the assumptions based on aggressive or conservative assumptions.

Finally, when you choose a scenario, you need to be committed to reaching the set targets. For example, if your target is to get 5,000 sales from organic traffic after 6 months, you need to work hard on doing SEO, creating great content, and professional product photography that can increase your conversion rate, etc.

A good business plan can show you if you are on track to meet your goals at each step of your journey. Without planning we do not know what good looks like and how we are doing.