A New Sales Forecast Template for Consumer Hardware Startups (Spreadsheet Included)

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!

How to Price Your New Consumer Hardware

This essay originally appeared in Hardware Massive Resources.

A brief intro to “pricing”

You’ve seen “Early Bird Prices” offering 50% discounts for consumer hardware products that are on the cusp of launch. As you browse Amazon in any given category, such as drones, you will see a variety of products with similar specs, but available at a wide spectrum of prices. So, how does any company decide what their product is worth to a customer? 

I work at Indiegogo, a crowdfunding platform for new hardware products, and my job is to advise entrepreneurs on their launch strategy. Pricing is a key component of that conversation:

Product Price x Units Sold = Total Sales

The total amount of funds raised is the center of success for a pre-order campaign, because it reflects sales volume, which demonstrates market demand for a product. These funds also provide capital to build a business with. The higher the total funds raised, the more successful a product is. 

Word on the street is that pricing as low as possible is critical to selling as many units as possible. Low prices are used as an incentive to consumers, to convince them to buy here and now. But, lowering prices does not always make sense if it results in inadequate funds for production, or if it does not match the positioning in the market.

This article takes a practitioner’s approach to pricing, focused on what’s profitable and feasible, rather than on pricing theory. The best place to start your pricing decisions is based on your own operations and customers.

Your price should mirror your product positioning

Pricing is not always about covering costs of production. At the core of your pricing decision should be identifying which set of customers will ultimately be your paying customers. 

These are some types of pricing that might be applicable for your new product launch:

Price point$$$$$$$$$
Pricing typeEconomic efficiencyMarket penetrationPremiumMonopolistic
Example of situation to use I can produce and distribute this product at a much lower price that my competitors, and match or better their quality.I want to get this new product into the hands of as many customers as possible. I might be able to recoup margin through value added services, subscription fees, accessories, repeat purchases, or volume.Customers have trust issues with product quality in this category, and my product promises reliable and transparent practices. We sell more than just a product.My product’s core technology is unreplicable and patented, so we are the only company who can offer this product.
Example productOnion Omega 2 Linux computer for $5Crazybaby Air Wireless Earbuds for $99Z CAM S1 Professional VR Camera for $1,500OSVehicle World’s First Open Source Vehicle for $19,000

Common mistake #1: I want to sell as many units as possible, so I will adopt a “low price strategy” for my product.

This is always going to be a mistake for your bottom line. You will not leave enough margin for yourself to effectively pay for marketing and growth efforts, and there will be operational costs that you can’t anticipate right way. During your customer servicing, you might have shipped a product late to a customer, and the customer may want a refund because of the delay. Or, after the product is shipped, the customer may not want the product anymore. Returns are something you won’t be able to accurately anticipate, and will impose financial loss for you. There are other costs that you may not be able to forecast, such as credit card chargebacks, or a shipping price spikes from your service provider. 

As a baseline, think of the 20/80 rule: your product should cost 20% of the selling price. Mark up your product 4x to start, and start whittling down your price from there. Keep reading for my take on how to build up your cost spreadsheet below.

Common mistake #2: I have a premium product in a new category, so I can charge a high price point and customers will understand why.

Premium positioning is not achievable right off the bat – it usually requires extensive user education through awareness campaigns to make it happen (this awareness can be driven by the industry at large, by influential parties, or paid for by your own marketing campaigns). Companies who may succeed with this type of pricing strategy are those who are offering an innovative product with massively improved user experience in an existing category, or they are carving out an entirely new category. However, this doesn’t always work, as the customers must be willing to pay comparable sums for substitute experiences.

Let’s imagine that you invented the first robot that can help you fold your laundry at home, and you want to charge $10,000 per unit. Unless you are substituting a service that is worth that amount, or unless customers understand exactly how easy it is to use this product and can imagine how much value this laundry-folding machine will bring to them, you can’t necessarily offer this price to consumers. This will take a lot of convincing through videos, demonstrations, and third-party validation to get the word out, and showcase to users why they would pay the equivalent price of a car for this robot.

Common mistake #3: I will sell my hardware for a really low price, and then make my money by selling the software or monetizing my app.

Think twice about this strategy. Unless the app experience is providing a core feature for the product, it will be hard to monetize the app. In the case of a home security camera, you won’t want to charge much of a premium on top of data storage costs that you’ll incur, since it will make your product unappealing. If you’re selling an IoT wearable product that unlocks gamification features through the app, you may want to rethink the value that is really being offered through the software. 

As corny as it sounds, think of this as as analogous to selling pizza: you won’t convince a user to pay for features that they don’t want, just as someone won’t come in and add extra toppings if they don’t want to eat it.

Building your cost spreadsheet

A common mistake that hardware teams make when looking at costs is not accounting for operational and marketing costs. These calculations should be built into your retail price calculation, as these are the funds you will need to continue to grow your business. 

Here’s are some of the items you should be aware of that will cost you money. As an example, we’ll imagine that this is the cost spreadsheet for a wireless earbud product that retails for $100:

Wireless earbuds ($100 MSRP)

Type of costWhat this includesExpected margin costDollar cost
COGS (Cost of Goods Sold)BoM (Electronic, mechanical bill of materials), including the cost of components and assembly costs. Don’t forget to factor in your fixed costs for tooling and production, especially if it’s your first run and you are cash-tight.20-30%$20
Shipping & logisticsCosts associated with warehousing your product, forwarding your freight to warehouses, shipping products from your production port to your destination, and shipping to customers’ doors.10-20%$10
Channel marginThese are the fees you will pay to your channels of distribution, whether it’s third party sales distributors, offline retail stores, or online retail stores.Variable based on product category and channel
(Example: typically 10-20% for online retail, and 30%-50% for offline retail.)
Marketing marginWhat you will pay via direct marketing or overall cost to invest in marketing your product. For example, it might cost you 15-25%$20

For this wireless earbud product sold at $100, subtracting all of the costs above, the company can expect to net a gross margin of $30 per pair sold, which equals a 30% gross margin:

$30 net margin / $100 retail price = 30% gross margin

Maintaining a 30% gross margin per unit is a great path toward profitability, as there will be other costs incurred as part of the business. This can include hiring new talent, paying office rent, and more.

Offer sensible disounts

So now that you’ve got an idea of what price to set for your product, how do you know when and how much to discount?

Discounting is a bit of an art. Once you’ve accounted for your core costs of business, it’s a matter of making sure that your discounts are valuable to the right people, at the right time. Let’s say that you’re selling a connected ski goggle: you’ll probably anticipate an increase in demand during snow season in December, or when people are planning to ski. Based on that, it might make sense to discount the product to incentivize purchases during the winter season. It may be tempting to discount on Mother’s Day, Independence Day, and every major shopping event, but you should avoid discounting too frequently, or else you’ll train your potential customers to undervalue your product.

Here are some basic principles to consider when deciding when and how much to discount:

Pricing factorsWhy this mattersConsiderations
Your customer’s price elasticity Will your customers care if your price is lowered, or does it not impact their purchase decision?There are some product categories where dropping the price doesn’t necessarily incentivize someone to complete the purchase. A good example of this is a refrigerator, a product that you only need to purchase once every few years, so regular discounts don’t make sense.  
Frequency of discountingSeasonality is a big factor for many products. Think about which holidays and market penetration opportunities you have to drive up order volume with promotional incentives.Don’t provide huge price cuts regularly. It’s tempting to lower the price every holiday. Instead, consider offering free accessories, and save big discounts for big holiday pushes, like Black Friday, or another relevant season for you.
Tradeoff between margin and volumeLowering your price can bring you bigger boosts in sales volume, but at a trade-off of how much cash you can take home.  For any discounting in which you’re going to break even (i.e. not make any money, but not lose money either), be sure that you’re pursuing a worthwhile opportunity. Once you set a low price, it will be tracked online in articles and on deal sites, which will make it harder to increase the price later on.

Final thoughts

We’ve gone through and covered the basics of what to consider when setting your price, how to build up your cost spreadsheet, and then when to change the price for customers.

The above materials are great to consider when you’re presenting a price to consumers, but distributor and B2B pricing (where your customers are businesses) is going to be more complex. Where pricing gets challenging is factoring in the intangibles, such as the marketing value that a third-party channel might bring in, or access to a specific group of customers with a specific retailer. You may consider offering a better price to different partners based on the value they can bring to your brand.

My best advice to you is to experiment with price points, but remember not to sell yourself short. You’ve got a long-term business to build, and you will need the right amount of capital to reinvest into it. 

Pricing isn’t just about making money for your business–it plays into the market positioning of your product as well. If you find that you aren’t able to sell units, try to adjust the price as a lever for marketing optimization. Often, consumer hardware can just be too expensive for mass consumers, or the price point isn’t right for a specific audience. Always talk to your customers and see who your paying customers are, so that you can adjust your product for them. Sometimes, it might mean going back to your manufacturer and working on a cost-down plan so that you can lower the price and sell ten times more units.