How to Forecast Your Sales

Introduction

Sales forecasting is an important basic business skill. If you don’t now how many sales your business can or will bring in, how will you be able to make important business decisions like whether to expand or what inventory to buy?

For this reason, we’re going to look at a few sales forecasting techniques. Forecasting sales works differently if you’re in a retail or product-based business than if you’re in a service-based business.

For services, like providing insurance, you can use Opportunity Stages Forecasting, also called Weighted Pipeline Forecasting. This method assigns a percentage of successful sale to each step of the sales cycle, so that you can predict how much money you’ll bring in based on your likelihood of completing that sale. If this sounds confusing, don’t worry – we’ll cover it.

For retail or product-based businesses you can use regression analysis or time series analysis. These are mathematical ways of calculating your sales based on the previous data that you have collected. We’ll go over how to perform each kind as well.

Before You Begin

You’ll need a few things ready before you begin forecasting your sales. The first is data! If you’re not tracking your sales right now, you’ll have no data available in which to forecast your future sales. A good Point of Sale (POS) system can help you collect this information, or you might be storing it in Excel or other places.

The important thing is to collect a baseline. A year of sales data would be best, but if you have only 3 months of data you can begin your forecasting and then adjust as you collect more information up to the full 12 months. I wouldn’t begin sales forecasting until you have a full year of data to go on first.

Next, you’ll need the awareness that forecasting is never perfect. You’ll always be over, or under, but you’ll never be right on. This is important to keep in mind. After we do our forecasts, we’ll go over some ways to improve the accuracy but please keep an open-mind.

Opportunity Stages Forecasting

First we’ll start with opportunity stages forecasting. Opportunity stages are the stages that each sale you make goes through. For example, you might classify your sales into the following category:

Leads are all of the people you might contact in a year. Let’s say you’re a real estate agency and you have 500 leads. Only 100 of those leads might actually be people who are interested in buying a home right now – prospects.

Of those 100 prospects, let’s say 50 of them are ready to buy now (10% of your leads.) These 50 are Opportunities. You visit homes with 30 of them (Proposal stage). 25 of them commit and 20 homes eventually sell. (Perhaps the others fell through on financing.)

Out of your 500 leads, you made 20 sales. Your sales funnel (the pyramid you see above) narrowed at each step of the process. Let’s look at the percentages:

• Leads – 500 / 100%
• Prospects – 100 / 20%
• Opportunities – 50 / 10%
• Proposal – 30 / 6%
• Commitment – 25 / 5%
• Sale – 20 / 4%

You can see of your 500 leads only 4% of them will convert to a sale. However, your chances of getting a sale increase as each person moves through the sale cycle:

• Leads – 20 / 500 = 4% of a sale
• Prospects – 20 / 100 = 20% of a sale
• Opportunities – 20 / 50 = 40% of a sale
• Proposal – 20 / 30 = 67% of a sale
• Commitment – 20 / 25 = 80% of a sale
• Sale – 20 / 20 = 100% chance of a sale

Now in order to compute your projected sales you take the dollar value of each potential sale in the pipeline, and multiply it by the chances of making the sale. For example, let’s say you have 100 leads. 25 of them want houses in the $50-100,000 range, 50 want houses in the 100-200K range, 20 want houses in the 200-300K and 5 want houses in the 300-500K range.

We’ll use the midpoint of those sales as the sale amount, so our chart looks like this:

If you managed to sell every lead you would have sold a combined $16.4m. Not bad! But of course we know most leads don’t pan out. So 3 months in, here’s where your leads are:

So after 3 months you’ve closed on one property worth $150,000. The other properties are in various stages of the sales cycle.

Next step, we multiply the value of each part of the pipeline by the potential value if that sale were to finish. So, for example, we converted about 4% of our leads to sales. So we multiply 9 x $75,000 = 675,000 x 0.04 (or 4%) = $27,000. So the value of our 9 leads on $75,000 houses is $75,000.

On the other end of the scale, we had one sale for a $150,000 house. Since the sale is already made, our conversion rate is 100%. 1 x $150,000 = $150,000 x 1.0 or 100% = $150,000.

When we complete the chart with all of our values we end up with this:

So the total value of your sales pipeline is $2,699,750. This is from our original total value of $16 million, based on our ability to sell these houses.

When you have more advanced data available, you can calculate different success rates for the prices of the homes. In this example, we assumed that your chances of closing a $75,000 from a lead were the same (4%) as closing a $400,000 house but that may not actually be true. It might be that your opportunity stages are different depending on the specific segment you’re targeting.

Good luck!

Length of sales cycle

The Length of Sales Cycle approach takes the Opportunity Stage process we worked with above but instead of calculating the likelihood of a deal closing based on the stage that it’s in (like Opportunity, Proposal or Commitment), the Length of Sales Cycle method uses the amount of time it takes for a sale to close.

For example, returning to our real estate agent example. They had 5 houses sell within 30-80 days:

• House 1: 34 days
• House 2: 30 days
• House 3: 47 days
• House 4: 54 days
• House 5: 79 days

If you add up the number of days (34+30+47+54+79 = 244) and divide by the number of houses (5) you get 48.8 days. This is how long it takes you to move through the 5 stages on average. Obviously the more houses you sell and the more data you have, the more accurate your average number will be.

Now, you can take that 48.8 number and use it to calculate the likelihood that a house will sell. So if a sale has been in progress for 30 days, 30/48.8 = there’s a 61% chance you’ll close the deal. As you can see, some sales cycles are more predictable. Real estate can be very variable, while software sales are much more “regular” – once a customer contacts you, you know there’s usually a defined period of time for them to make their choice of vending and finish the sale.

Regression analysis

Regression analysis is a more mathematical way of forecasting. Regression analysis allows you to extrapolate a known value to figure out an unknown value. For a simple regression, if we’re looking at the number of sales calls and the number of sales they make, we can put it in a chart like this:

When we chart this, we end up with the following graph:

In order to chart the relationship between these two numbers we use the formula Y = a + bX, where Y is the dependent value, and X is the independent value. We adjust the X (the number of calls) and see how it influences the Y.

Excel allows us to easily calculate the regression by selecting the Linear Trendline under the “Add Chart Element” menu. It looks like this:

Under the formatting options, I ticked “Show equation on chart”, which tells me the formula is y = 0.1903x + 0.3862.

Let’s predict a new number of calls:

Y = 0.1903(80) + 0.3862

Y = 15.61

So we know that if we make 80 calls we can expect to have 15 or 16 sales.

Correcting Our Predictions Over Time

Whenever you make predictions over time, you need to look at how your predictions “turned out.” There are a couple of tools you can use to adjust your prediction: Mean Absolute Demand (MAD) and Mean absolute percentage error (MAPE).

To calculate MAD you take your forecasted results and subtract them from the actual results. Then you take the average of those values. It’s important that you not use the plus or minus signs to calculate the difference.

Returning to our sales call example above:

So we know our MAD is 1. Our prediction is usually off by one sale.

MAPE is similar to MAD but instead of telling you in sales how far your forecast is off it will tell you the percentage. You can calculate the percentage difference using the formula (Actual Sales – Number of Sales Predicted) / Number of Sales Predicted. For example, for the column of 12 calls made, we have (4 – 3) / 4 = 25% different.

Adding a MAPE column to our chart we see the following:

This tells us that the MAD is 1 and the MAPE is 12.125%. We would need to adjust our forecasts by about 12% (in this case, down) in order to ensure that we’ve accurately predicted the right number.

Other Sources for Data

You might not have past sales data, or might want to compare your data with others in your industry. For example, retail stores might want to look at the average sales per square foot in similar businesses and compare them to their own.

Similarly, restaurants will calculate an average revenue per table and it might be helpful to understand those numbers. We talk about this in the Financial Monitoring when we note the Gross Profit Margin of different industries.

One source that everyone in Iowa has access to is ReferenceUSA. You can do a search for businesses in your area and compare the data. For example, A&W Restaurants has a location in Sigourney. They estimate the location is under 1500 square feet and that this location might do $576,000 in sales volume.

I have questions

Sales forecasting can be a bit confusing when you first start out. Please get in touch if you have questions about how to forecast your sales and we'll sit down with you to get a system in place that works for you. You can call us at 641-622-2288 or email me (Dustin MacDonald, Executive Director) at execdir@sigourney.com.