Financial Model for an Online Store: Launching Online Sales from Scratch and Accounting for Seasonality

In 2023, the share of global online sales in the overall retail trade exceeded 19% and continues to grow. Leading categories for online sales include clothing, electronics, groceries, home goods, and personal care products. From a technical standpoint, creating your own online store has become increasingly accessible, thanks to user-friendly platforms and abundant educational resources. However, to not only establish a hypothetical online clothing store from scratch but also make it profitable, careful attention to the business plan and the calculation of the financial model is essential.

For launching an online store from scratch and achieving commercial success, the assortment matrix will play a key role. Prices of products and their market demand must align with the associated costs. To open an online store, you will need to invest in purchasing goods, advertising, and logistics. Not all products will have equal demand and economic efficiency in online sales. Building a financial model will help you understand which type of online store to open and estimate the potential earnings from online sales in your specific case.

The foundational financial model we construct for any new business is the “3-Statements Model,” encompassing forecasts for three fundamental financial statements: Profit and Loss (P&L), Cash Flow, and the Balance Sheet.
You can find a financial model for an online store at this link, with examples we break down in this article.

Our initial focus lies in calculating the profit and loss, commencing with forecasting revenue. The order in which income is generated is a key distinction among various business models. Therefore, in this article, we delve into the detailed calculation of customer numbers and revenue dynamics for an online store.

Consumer goods trade is always subject to seasonality, at the very least, with a surge in sales before the New Year. Therefore, to ensure the accuracy of the business plan for an online store, it is crucial to integrate seasonality from the outset into the financial model for the products planned in the assortment matrix.

Within the financial model, we depict the entire customer journey to purchase — starting as a visitor to the online store and eventually converting into a buyer. The starting point for calculations in the financial model is the website traffic. It is precisely to this traffic that we apply seasonality.

Determining Seasons

The first step is to identify which months will experience high seasonality for our products and which will have low seasonality. In the input data of the financial model, we create a table numbered from 1 to 12, corresponding to the sequential months, in the first column. In the second column, we note the seasonality level for each month: high, medium, or low.

Organic Traffic Dependence on Seasonality

In the financial model, we assume that organic traffic will be unevenly distributed throughout the year — increasing in high season and decreasing in low season. To reflect this in calculations, we consider 100% traffic during the middle season. For the low season, we set a percentage below 100 for traffic calculation, and for the high season, a percentage above. You can use data from past periods if your online store is already operational or has an offline presence. If you are launching an online store from scratch, factor in seasonality dynamics typical for your industry in the overall market.

Paid Traffic Dependence on Seasonality

Paid traffic for the online store will primarily depend not on the season but on the advertising budget. Therefore, in the financial model, we factor in the impact of seasonality on traffic from paid channels through expenditures on advertising campaigns. In the table, we input amounts for direct marketing for each month in the first year after opening the online store. It is logical that during high season, we will spend more on advertising, and during low season, less.

When constructing the financial model, we trace the user’s path and describe it in numbers. In the case of an online store, the starting point is the number of website visitors.

The user’s first direct interaction with the product occurs when they open the website. For a person to visit the site, they must first learn about it. To achieve this, the company engages various marketing channels, broadly categorized into organic and direct advertising. From a financial model perspective, we directly influence traffic from direct marketing channels through the advertising budget and CPC (Cost per Click) — the cost of a click. In this case, we have a direct correlation between direct marketing expenses and the number of website visitors. It’s not possible to directly tie organic traffic to expenses, but that doesn’t mean organic traffic is free. Expenses for SEO, social media management, brand awareness campaigns, content creation, and publication — all these are necessary for organic traffic. Therefore, when making assumptions about organic traffic in the financial model and forecasting its growth, we must include in the expenditure section the costs that can support these figures.

Organic Channels

Let’s move on to creating an algorithm for calculating organic traffic for the online store in the financial model.

We’ve already defined the seasonality of organic traffic as different percentages from the average season. Now let’s proceed with the other parameters. Set the launch date of the online store and assume the number of website visitors in the first month of operation. Here, you can rely on your existing audience, competitors’ experience, and benchmarks for your industry. The next step is to set the forecasted growth in traffic from year to year.

Paid Channels

To calculate traffic from paid channels, we need to consider two parameters: the budget and the cost per click (CPC). We’ve already determined the budget for the first year in the seasonality table; now, we need to add the planned growth in direct marketing expenses from year to year.

The second parameter for calculating the number of installations from paid channels is the CPC (Cost per Click), which is the cost of a click, i.e., transitioning from an advertisement to the website. Of course, you will only obtain the actual CPC for your online store after launching the advertising campaign. However, at the early stages of project development, you can find a suitable benchmark for the financial model. Marketing agencies and advertising platforms regularly publish CPC benchmarks for various industries and channels. After launching the product and conducting test campaigns, don’t forget to revisit the financial model and adjust the calculations.

Our goal is to create a dynamic model that facilitates calculations for various scenarios at the initial stages of the project. Therefore, we make monthly forecasts on a separate tab, setting all indicators through formulas.

Determining the First Month

To determine the month from which traffic appears in the model, we use the IF function.

If the date value for the column is greater than or equal to the launch date of the application or direct marketing campaigns, we begin calculating the indicator using the formula; otherwise, the cell value is “0”.

In a general sense, the formula in Excel or Google Sheets looks like this:
=IF(E2 (forecast month) >= Projections! $E$13 (start date in input data); formula; 0)

Calculating Traffic

The specific formula for calculating organic traffic would look like this:
=IF(E2 >= start date;
traffic in the first month * (1 + annual growth %) ^
(YEAR(D2) — YEAR(start date)) *
VLOOKUP(MONTH(E2); seasonality table range; 3)
; 0)

It may seem complicated, but breaking it down into parts makes it more understandable. To calculate traffic in a specific month of a particular year, take the traffic in the first month and consider annual growth and seasonality.
To calculate the growth percentage, determine how many years have passed since the launch of the online store. This figure will be the exponent to which we raise the average growth percentage per year.

To consider seasonality, multiply the resulting traffic by the coefficient we specified in the seasonality table for each month. Here, we use the VLOOKUP function to find the value for the corresponding month.

We use a similar formula to calculate the budget for direct advertising in the monthly forecast. After that, to calculate paid traffic, divide the budget for the current month by the CPC specified on the input data tab.

You can download a template of the financial model for an online store, where all the formulas are already outlined, via the provided link.

The key to success for an online store lies in a well-curated assortment matrix and competitive pricing. In the financial model, we create a separate tab for the assortment matrix. For rough planning, you can assume an average product cost and build forecasts based on it. However, I always recommend creating a minimal starting matrix of specific products. Obtain specific price lists from potential suppliers to determine wholesale prices. Additionally, analyze the market to see at what prices identical or similar products are already being sold, allowing you to set retail prices.

This approach ensures a more accurate and realistic financial model, as it considers actual costs and market conditions. It also allows for a better understanding of the profit margins and potential revenue streams based on the selected assortment and pricing strategy.

In the assortment matrix table, we input the names of the products, their prices, and the estimated proportions of each item in total sales.

Pricing can be established by applying markup percentages to the wholesale prices or by analyzing competitors’ prices. In the first scenario, you apply a markup percentage to the wholesale price to determine the retail price. In the second scenario, you manually input retail prices based on an analysis of competitors’ pricing.

This flexible approach allows for a comprehensive analysis of pricing strategies and helps to align the retail prices with market conditions and competition.

Now, we need to connect the online store’s traffic with product purchases from the assortment matrix and calculate the revenue.

In the general overview section with input data, we incorporate conditions for forecasting.

Average Order Value

Based on data from the assortment matrix table, we can calculate the average selling price of one item. However, customers might purchase more than one item in a single order. Therefore, to calculate the average order value, we include the input of the average quantity of items in one order.

Conversion Rate

To transition from traffic to sales, we introduce the conversion rate — the percentage of visitors to the website who make a purchase.

In the monthly forecast tab, we begin by calculating the number of orders for each month. This is done by multiplying the total traffic (previously forecasted) by the conversion rate from the introductory data.

To determine the revenue, we multiply the resulting number of orders by the average order value from the introductory data.

In the end, we have obtained a revenue forecast for the online store based on a specific assortment matrix, two traffic sources, and accounting for sales seasonality.

Now you have a tool that allows you to forecast the revenue of your online store. In this article, we haven’t delved into the cost side in as much detail — for a comprehensive forecast of not just revenue but also profit, it’s necessary to consider expenses such as fulfillment or warehouse costs, logistics, marketing, payroll, and other expenditures that are more standard for any business. A template model for an online store that takes into account both revenues and expenses can be found here.

Choose benchmarks that are realistic for your specific case, consider the results of marketing research (it’s crucial for the financial model to account for competitor analysis, market size assessment, and surveys of potential customers), and play with the figures in the input data. For convenience, you can add charts and additional metrics that, along with the financial forecast, can be incorporated into the business plan for your online store.

For more information on other aspects of financial models, you can refer to my other articles on the blog

A financial model template in Google Sheets or Excel, complete with all the formulas, can be obtained via the provided link.