A New Sales Forecast Template for Hardware Startups

Written by Sandy Diao

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 and make assumptions about growth. 

Through my work with more than 250 hardware startups at Indiegogo, I have found one thing in common with all hardware startups as they are building up sales: digital marketing. Through Facebook ads or other types of digital marketing activities, companies are driving visitors to their website e-commerce pages and motivating customers to purchase. 

But aside from promotional activities or traffic spikes from media visibility, there are two things that don’t change drastically daily: website traffic and the page conversion rate, which is the total number of people who make a purchase compared to website visitors.

For hardware startups trying to get a pulse on how to estimate future sales and establish a growth plan, the number of qualified visitors driven to the checkout page is one of the most accurate ways to determine potential sales.           

Example: You’re currently driving 1,000 visitors to your page with a conversion rate of one percent, yielding 10 purchases per day. Your daily sales goal is 20 purchases per day. The difference in actual vs. goal is a sign that you need to increase the traffic to your page or improve the conversion rate by optimizing the site and converting more sales for each ad dollar you spend. In order to fully understand how you can improve sales, this information needs to start being tracked. 

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 different types of seasonal and data anomalies compared to other types of businesses, such as software or services. Selling 100,000 units in one month does not mean that you will be able to sustain the same amount of growth going into the next month, or in the same time next year. Now, let’s understand the goals of our forecast so we can build it to reach them.

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 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 support your budget for specific initiatives. Even if you are not presenting a deck to your board right away, it’s important to keep an up-to-date forecast to understand how well you’re trending towards your goals. 

The goal of this tutorial is to help you build a forecast for your website sales in a single month, giving you a sheet to track your daily sales. This template will apply best to startups who don’t have any data, and therefore rely on marketing performance build the forecast. Over time, you’ll have a fancy, color-coded financial projection spreadsheet. But for start-ups, I strongly recommend building a forecast that is easy to understand and update, so that you can review at any point to assess business decisions related to sales. 

The best forecasts are made bottoms-up, which means that projection trends are calculated by actual sales and results, reflecting a true way of business operations. There is little benefit from tops-down forecasting when you’re trying to track actual business metrics. 

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 accurate.

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 rules to using this spreadsheet:

  • Don’t edit anything in this template–go to File --> Make a Copy and edit your own version.
  • 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. 

Graph 1
  • Start Date: Record when your company’s reporting period begins. 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 sold. 

Graph 2
  • 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%, then you should look 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 other 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.

Trim Mean

This is the part where it becomes less of a template and requires more thought to determine what performance data you will use to project future sales. Based on 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 not representing the rest of the data well. For example: 
Graph 4

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 very different, so picking either 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 TrimMean. This means that I will 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:

Graph 5
  • 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 trending to hit and what you need to be selling in order to hit your goal.

  • Trending (Rows 35-41): This is how much we will 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) through the next 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:
Graph 6

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 boost our sales.

Graph 7
  • Goal (Rows 43-49): In Step 3, we determined that we need an additional $6,503 in daily sales to reach our goal. 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:        
Graph 8

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. 

Graph 9
  • Difference (Row 51): Now let’s calculate exactly how much we’re short of traffic and sales.
Graph 10

Based on what we’re selling now and where we need to be, it appears we need 1,458 more visitors per day to drive an incremental 9 units of sales per day.        

Assuming I am running Facebook ads, I could do a simple calculation as such:

Graph 11

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. Instead of asking for as much money as possible, this allows you to say, “We’re getting a pretty good return on our ad spending, and I would like to increase in our daily ads budget by $700.” 

STEP 5: Most people stop with the forecast, but to make this spreadsheet actionable, you should visualize the magnitude of the difference, and draw actionable insights.

Let’s start by determining what kind of daily and weekly growth rate we see. This will eventually allow you to build a monthly and yearly forecast, providing stakeholders the numbers they care most about. 

Growth rates tell you how much your sales are growing per interval of time. Use these growth rates to set goals and show investors how well you’re doing in the short-term.

EXAMPLE

Graph 12
  • Daily Growth Rate: You started with $2,033.00 in sales, and plan to sell $111,916.00 by the end of the period. This shows an increase in sales by 15% each day.
  • Weekly Growth Rate: An increase of $2,033 to $111,916 in sales at the end of the fourth week means an increase of 172.4% each week.

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. These fields will calculate the growth rates on your actual sales.

Now, let’s plot these numbers and see how we’re trending:

Graph 13
  • Actual (blue): This line represents how much we’ve 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 will be sold if the current plan remains the same. It will be similar to the blue line, because it represents an extension of what is currently being done.
  • Plan (dotted, green): This is where we want to be to hit our goals. This line is higher than what is predicted to sell right now (red line).

That’s it! Based on the numbers, you can make better, more informed marketing and pricing strategies. I’ve included some examples of insights you can glean from this forecast (Row 92).

What’s next?

Remember, with hardware sales, and especially with a few SKUs to start, you’ll face more seasonality and outlier data compared to other sales types. When selling on Amazon, there are some product categories where using December sales history is not going to help you forecast how many units you’ll sell in January. For example, 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 different channels. You could be selling in more than 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 months, 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, remember to keep it simple, bottoms-up, and actionable. Separate the forecasts for each channel if you need to. The more lines you can create with different concrete assumptions, the more accurate your forecast will be. 

Happy spread sheeting!

 

About The Author

Sandy Diao

Sandy Diao

Director of Strategic Programs at Indiegogo

Sandy is a full-stack marketer with experience driving consumer adoption for emerging technologies. Previously, she led global expansion for a Sequoia-funded startup and pioneered a native advertising platform on Pinterest.

Share Article:

Sign up to the Hardware Massive newsletter

Related Articles