This essay originally appeared on Hardware Massive Resources.
5 steps to building your first website sales forecast
Traditional sales forecasts are based on historical data and can be rather complex given different product types. These forecast models are based on “growth rates” that calculate a percentage change over time. If you’re a hardware startup launching a single product, you may find that you have no historical data to use for making assumptions.
In my experience working with over 250 hardware startups at Indiegogo, I’ve found that there is one thing that all hardware startups are doing as they’re building up their sales: digital marketing. Through Facebook ads or other types of digital marketing activities, companies are using direct response marketing to drive visitors to their ecommerce websites in order to drive more sales. Aside from one-off promotional activities or traffic spikes from visibility in the media, there are two things that don’t change drastically from day to day, and that is your website traffic and the conversion rate on your page. The conversion rate refers to the total number of people who purchase a product out of everyone who visited your website.
For hardware startups trying to estimate future sales and establish a plan to continue to grow, I have found that the number of qualified visitors you drive to your checkout page is one of the most accurate ways to determine how much more sales you could potentially drive.
Example: Let’s say you’re currently driving 1,000 visitors to your page, and you manage to convert 1% of them into paying customers – that would be 10 purchases per day. Assuming that you need to sell 20 units per day, you would try to double the traffic you get–that means 2,000 visitors–to acquire 20 paying customers. This is a sign to your team that you need to increase the traffic to your page, or improve the conversion rate by optimizing your page and converting more sales for each ad dollar you spend. You need to start tracking this information in order to understand where you can improve your sales pipeline.
In this forecast, I use a page conversion rate instead of a “growth rate,” which is the more common underlying metric in forecasts. This is because hardware sales are prone to more nuanced data anomalies compared to software or service businesses. Selling 100,000 units in one month does not mean that you will be able to sustain the same volume going into the next month, nor is it helpful to reference during the same period the following year. With these differences in mind, let’s start by understanding the goal of creating a sales forecast.
What is a sales forecast?
A sales forecast is a spreadsheet that you can use to estimate future sales for your company. You can make it as short-term or long-term as you want, and include or exclude as much details as you need. A forecast will help you explain the assumptions behind your valuation to investors, and help you rally for budget for specific initiatives. Even if you’re not presenting a deck to your board soon, it’s important to keep an up-to-date forecast to understand how well you’re trending against your goals.
The goal of this tutorial is to help you build a forecast for your website sales in a single month, so that you have a sheet to track your daily sales with. This template will apply best to startups who don’t have any data, and therefore marketing performance is the best data set you have to build your forecast. At some point you’ll have a fancy, color-coded financial projection spreadsheet. But, for first-timers, I strongly recommend building a forecast that is easy to understand and update, so that you can easily look at it at any point in time to assess business decisions. The best forecasts are made bottoms-up, which means that you calculate any projection trends based on actual sales and results, so it can accurately reflect your business’ unique operations. There is little benefit to running a tops-down forecast when you’re trying to assess actual business numbers.
Example: Tops-down forecasting means to identify the total size of the market, and assume that you will capture any percent of it. You could say that the total size of the market in 2017 was $1 billion, and capturing 1% of the market would yield $10 million in sales for your company in the first year. Without any marketing activities and actual customers, you will capture none of the market, so showing any investor a tops-down forecast is not grounded in reality.
A small set of historical data, a goal, and Excel (or a cloud-based spreadsheet solution like Google Sheets) are all you need to get started. I recommend starting with Google Sheets so that you can update and share with team members at any point in time.
Before we get started, I want to point out that there are no rules for creating a sales forecast. This tutorial is only one variation and tackles a very specific goal.
Let’s get started
[Access the sales forecast template here]
Some tips for using this spreadsheet:
- Don’t edit anything in this template–go to “File”–>”Make a Copy” and edit your own version. On your own version, you’re welcome to make any changes to this outline and use this as your own.
- Cells in yellow are ones you will need to fill out.
- Cells in grey are anchored formulas, so you should not edit these.
- Cells in blue are summations, and are used to emphasize total amounts.
STEP 1: Determine your reporting period, product price, and goal.
- Start Date: Date when your company’s reporting period starts. A common way to track this would be from the start of a quarter to the end.
- Forecast Start Date: Once you have at least two weeks of sales, you can start to make this forecast. This is the date in which you’re sitting down to make this forecast.
- Reporting End Date: The end of your reporting period–many companies will set this as the end of the quarter. In this example, I set the end of a 4-week period to calculate a month.
- Price Per Unit: This number will help us calculate the total sales amount.
STEP 2: Lay out your historical data, which are the units you’ve actually sold.
- Total Site Traffic: Use your site analytics tool to track how much traffic you’re driving to the page.
- Conversion rate: Divide the Total Units / Total Site Traffic so that you can calculate a conversion rate. Depending on the channel you’re selling in, this conversion rate will be different. As a general benchmark, if your page conversion rate is below 1.0%, then you should look out for ways to improve the user checkout experience or your page content.
- Total Units: How many products you sell per day.
- Price/Unit: This field is carried over from your product price set in Step 1. You can override these fields if you have sales on specific dates and lower your price point.
- Total Sales: Total Units x Price/Unit ($)
- Running Total: This is the cumulative sum of your total sales since the start of the reporting period. In order words, this is a snapshot view of how much your total sales are on a specific date.
STEP 3: Calculate your “run rates”, which is how you’re trending.
This is the part where it becomes less of a template, and requires you to think about your business. You’ll need to pick whether which set of performance data you want to use to project future sales with. Based on your historical data, I’ve calculated two averages here:
- =TrimMean(): An average that removes the top 25 data points so you can exclude any outlier data that does not represent the rest of the data well. For example:
In Week 1, we had a day where the page conversion rate was 2.06%, and we drove over $11,960 in sales. This was because a partner put in a larger order and is not a repeatable sales event, so we want to exclude it. Using the TrimMean helps us remove data points like this so that our average trend numbers are more accurate in our forecast. - =Average(): Just your regular average calculation (add up all numbers, divided by how many numbers there are).
In this case, the numbers aren’t too different, so picking any of the two won’t give you wildly different forecast results. In this case, because I know that there are unrepeatable sales events such as the 2.06% conversion rate on January 4th, I’m going to use the TrimMean. This means that in my forecast, I’ll be using the data in Column A to carry across my future sales forecast, which I’ll do in my next step.
Before that, let’s calculate some numbers on what it would take to reach the goal we set up for this period:
- Revenue to Date: This is how much in total sales we’ve generated to date (carried over from the running total in Cell P18).
- Difference to Goal: Subtracting our sales to date from our goal amount of $150,000, we still have $91,037 left to sell.
- Days Remaining: We set our reporting period for 4 weeks, so if today is January 14th, then we only have 14 days left before January 28th.
- Revenue/Day: In order to hit our goal, we need to sell $91,037 in the next 14 days. That’s about $6,503 in sales per day.
STEP 4: Calculate where you’re actually trending to hit, and what you really need to be selling in order to hit your goal.
- Trending (Rows 35-41): This is how much we’ll sell if we keep doing the same things we’ve done in the past. Based on Step 3, we will carry the TrimMean numbers (13 units per day and 1,994 visitors per day) across the rest of the upcoming two weeks. To do this, we will anchor the TrimMean cells (C24, C25, C26) to our forecasting table, and then copy and paste these same numbers across all future dates:
Looking at the “Trending” forecast, we’ll end the period with $111,916 in sales, which does not meet our $150,000 goal. This helps us understand that we need to somehow boost our sales in order to meet our goal. - Goal (Rows 43-49): In Step 3, we calculated that we need an additional $6,503 per day in sales in order to reach our goal. In order to achieve that, we will need to sell 22 units at $299 each. Doing some reverse calculation, we can figure out how much site traffic we need to drive in order to sell 22 units a day:
If we could drive 3,452 visitors and assuming they converted at an average of 0.63% on the page, we could sell 22 units per day and reach our $150,000 goal.
- Difference (Row 51): Now let’s calculate exactly how much we’re short of traffic and sales.
Based on what we’re selling now and where we need to be, it appears we need 1,458 more visitors per day in order to drive an incremental 9 units of sales per day.
Assuming that I am running Facebook ads, I could do a simple calculation as such:
While it’s not always correct to assume that your cost-per-click for any digital ads platform will scale up linearly, this is a great way to provide a ballpark budget to your team. Instead of going to your head of finance and asking for as much money as possible, you could say, “Hey, I think we’re getting a pretty good return on our ad spend, so we should increase our daily ads budget by $700.”
STEP 5: Most people stop after filling out the forecast and consider it an idle spreadsheet, but in order to make this spreadsheet actionable, you should visualize the magnitude of the difference, and draw actionable insights.
First, let’s start by determining what kind of growth rate we’re seeing day-to-day, and week-to-week. Eventually, you could build a monthly and yearly forecast. These will be the numbers that your stakeholders care most about.
Growth rates tell you how much your sales are growing per interval of time, based on where you’re at in the beginning of the period and where you net at the end. Use these growth rates to set goals and show your investors how well you’re doing in the short-term:
- Daily Growth Rate: You started with $2,033 of sales, and plan to sell $111,916 by the end of the period–this means that you increased sales by 15% each day across all 28 days.
- Weekly Growth Rate: An increase from $2,033 of sales to $111,916 at the end of the 4th week means you grew sales by 172.4% per week, across 4 weeks.
Note: The “Actual” column growth rates will be filled in when the period comes to an end and you fill in the actual sales data from the past 4 weeks. These fields will calculate the growth rates on your actual sales.
Lastly, let’s plot these numbers and see how we’re trending:
- Actual (blue): This line represents how much we’ve actually sold to date. As you fill out the forecast, this graph will populate so you can see how much you’re beating or missing your expected sales plan.
- Trending (red): This is how much we’ll sell if we keep up what we’re currently doing. You’ll notice it’s very similar to the blue line, because it’s an extension of what we’re doing right now.
- Plan (dotted, green): This is where we want to be in order to hit our goals. This line is higher than what we’re predicted to sell right now (red line).
And that’s it! You can draw a variety of insights based on the numbers you see, that you can use to inform your marketing and pricing strategies. I’ve included some examples of insights you can glean from this forecast (Row 92).
What’s next
Remember that with hardware sales, especially with a few SKUs to start, you’ll face more seasonality and outlier data compared to other types of companies. For example: when selling on Amazon, there are certain product categories where looking at December sales history is not going to help you forecast how many units you’ll sell in January. Or, if you’re selling a pair of ski goggles, you’ll likely find that November is much better month than June. You’ll need to factor these seasonal trends into your forecast. In this case, website traffic is not the best metric to use.
Eventually, you’ll need to track many, many different channels. You could be selling in over 10 different offline channels and 20 different e-commerce marketplaces at the same time. For hardware startups, the initial P.O. (purchase order) will often be modestly sized, with massive orders for thousands of units coming in later in the year. Within a few month’s time, many retail channels could be shut down, or you could have opened new ones. That’s why it’s important to regularly update the assumptions of your forecast.
Regardless of the channel or number of products you’re tracking, just remember to keep it simple, bottoms-up, and actionable. Separate the forecasts for each channel if you need to–the more lines you can build in with different concrete assumptions, the more accurate your forecast will be.
Happy spreadsheeting!
