Category: Sales Forecasting

Sales

How to Use Regression Analysis to Forecast Sales: A…

There are various ways to understand the effectiveness of your sales team activity or how well sales teams are at driving sales to reach operational and financial goals. Sales forecasting, a method that predicts sales performance based on historical performance, is one way to get this understanding.

Sales forecasting is important because it can help you identify what is going right, as well as what areas of your current strategy need to be adapted and changed to ensure future success.

For example, if your team is consistently below quotas, sales forecasting can help determine where and why these issues are happening. Forecasting can also help you decide on future business endeavors, like when you’d have the revenue to invest in new products or expand your business.

Some forecasting methods involve doing basic math, like adding up month to month sales, and others are more in-depth. Regression analysis is one of these methods, and it requires in-depth statistical analysis.

If you’re anything like me and not at all mathematically inclined, conducting this type of forecast may seem daunting. Thankfully, this piece will give an easy to understand breakdown of regression analysis in sales and guide you through an easy to follow example using Google Sheets.

What is regression analysis?

In statistics, regression analysis is a mathematical method used to understand the relationship between a dependent variable and an independent variable. Results of this analysis demonstrate the strength of the relationship between the two variables and if the dependent variable is significantly impacted by the independent variable.

There are multiple different types of regression analysis, but the most basic and common form is simple linear regression that uses the following equation: Y = bX + a

That type of explanation isn’t really helpful, though, if you don’t already have a grasp of mathematical processes, which I certainly don’t. Let’s take a look at what regression analysis means, in layman’s terms, for sales forecasting.

Independent and dependent variables are still at play here, but the dependent variable is always the same: sales performance. Whether it’s total revenue or number of deals closed, your dependent variable will always be sales performance. The independent variable is the factor you are examining that will change sales performance, like the number of salespeople you have or how much money is spent on advertising.

Sales regression forecasting results help businesses understand how their sales teams are or are not succeeding and what the future could look like based on past sales performance. The results can also be used to predict future sales based on changes that haven’t yet been made, like if hiring more salespeople would increase business revenue.

So, what do these words mean, math wise? Like I said before, I’m not good at math. But, I did conduct a simple sales regression analysis that is easy to follow and didn’t require many calculations on my part. Let’s go over this example below.

How To Use Regression Analysis To Forecast Sales

Let’s say that you want to run a sales forecast to understand if having your salespeople make more sales calls will mean that they close more deals. To conduct this forecast, you need historical data that depicts the number of sales calls made over a certain period. So, mathematically, the number of sales calls is the independent variable, or X value, and the dependent variable is the number of deals closed per month, or Y value.

I made up the data set below to represent monthly sales calls, and a corresponding number of deals closed over a two year period.

So, the overall regression equation is Y = bX + a, where:

  • X is the independent variable (number of sales calls)
  • Y is the dependent variable (number of deals closed)
  • b is the slope of the line
  • a is the point of interception, or what Y equals when X is zero

Since we’re using Google Sheets, its built-in functions will do the math for us and we don’t need to try and calculate the values of these variables. We simply need to use the historical data table and select the correct graph to represent our data. The first step of the process is to highlight the numbers in the X and Y column and navigate to the toolbar, select Insert, and click Chart from the dropdown menu.

demo showing how to create a chart for sales regression forecasting

The default graph that appears isn’t what we need, so I clicked on the Chart editor tool and selected Scatter plot, as shown in the gif below.

create a scatter plot in google sheets demo

After selecting the scatter plot, I clicked Customize, Series, and scrolled down to select the Trendline box (shown below).

display regression line of best fit in scatter plot google sheets

After all of these customizations, I get the following scatter plot.

sales regression scatter plot example

The Sheets tool did the math for me, but the line in the chart is the b variable from the regression equation, or slope, that creates the line of best fit. The blue dots are the y values, or the number of deals closed based on the number of sales calls.

values, or the number of deals closed based on the number of sales calls.

So, the scatter plot answers my overall question of whether having salespeople make more sales calls will close more deals. The answer is yes, and I know this because the line of best fit trendline is moving upwards, which indicates a positive relationship. Even though one month can have 20 sales calls and 10 deals and the next has 10 calls and 40 deals, the statistical analysis of the historical data in the table assumes that, on average, more sales calls means more deals closed.

I’m fine with this data. It means that simply having salespeople make more calls per-month than they have before will increase deal count. However, this scatter plot does not give us the specific forecast numbers that you’ll need to understand your future sales performance. Let’s use the same example to obtain that information.

Let’s say your boss tells you that they want to generate more quarterly revenue, which is directly related to sales activity. You can assume closing more deals means generating more revenue, but you still want the data to prove that having your salespeople make more calls would actually close more deals.

The built-in FORECAST.LINEAR equation in Sheets will help you understand this, based on the historical data in the first table.

I made the table below within the same sheet to create my forecast breakdown. In my Sheets document, this new table uses the same columns as the first (A, B, and C) and begins in row 26.

I went with 50 because the highest number of sales calls made in any given month from the original data table is 40 and we want to know what happens to deal totals if that number actually increases. I could’ve only used 50, but I increased the number by 10 each month to get an accurate forecast that is based on statistics, not a one-off occurrence.

sample data for regression sales forecasting

After creating this chart, I followed this path within the Insert dropdown menu in the Sheets toolbar: Insert -> Function -> Statistical -> FORECAST.LINEAR.

This part gets a little bit technical, but it’s simpler than it looks. The instruction menu below tells me that I’ll obtain my forecasts by filling in the relevant column numbers for the target number of sales calls.

sales forecast equation breakdown in google sheets

Here is the breakdown of what the elements of the FORECAST.LINEAR equation mean:

  • x is the value on the x-axis (in the scatter plot) that we want to forecast, which is the target call volume.
  • data_y uses the first and last row number in column C in the original table, 2 and 24.
  • data_x uses the first and last row number in column B in the original table, 2 and 24.
  • data_y goes before data_x because the dependent variable in column C changes because of the number in column B.

This equation, as the FORECAST.LINEAR instructions tell us, will calculate the expected y value (number of deals closed) for a specific x value based on a linear regression of the original data set. There are two ways to fill out the equation. The first option, shown below, is to manually input the x value for the number of target calls and repeat for each row.

=FORECAST.LINEAR(50, C2:C24, B2:B24)

The second option is to use the corresponding cell number for the first x value and drag the equation down to each subsequent cell. This is what the equation would look like if I used the cell number for 50 in the second data table:

=FORECAST.LINEAR(B27, C2:C24, B2:B24)

To reiterate, I use the number 50 because I want to be sure that making more sales calls results in more closed deals and more revenue, not just a random occurrence. This is what the number of deals closed would be, not rounded up to exact decimal points.

sample regression forecast results

Overall, the results of this linear regression analysis and expected forecast tell me that the number of sales calls is directly related to the number of deals closed per month. If you ask your salespeople to make ten more calls per month than the previous month, the number of deals closed will increase, which will help your business generate more revenue.

While Google Sheets helped me do the math without any further calculations, other tools are available to streamline and simplify this process.

Sales Regression Forecasting Tools

A critical factor in conducting a successful regression analysis is having data and having enough data. While you can add and just use two numbers, regression requires enough data to determine if there is a significant relationship between your variables. Without enough data points, it will be challenging to run an accurate forecast. If you don’t yet have enough data, it may be best to wait until you have enough.

Once you have the data you need, the below list of tools that can help you through the process of collecting, storing, and exporting your sales data.

InsightSquared

InsightSquared is a revenue intelligence platform that uses AI to make accurate forecasting predictions.

While it can’t run a regression analysis, it can give you the data you need to conduct the regression on your own. Specifically, it provides data breakdowns of the teams, representatives, and sales activities that are driving the best results. You can use this insight to come up with further questions to ask in your regression analysis to better understand performance.

demo of data collection software for sales forecasting

MethodData

Since sorting through data is essential for beginning your analysis, MethodData is valuable tool. The service can create custom sales reports based on the variables you need for your specific regression, and the automated processes save you time. Instead digging through your data and cleaning it up enough to be usable, it happens automatically once you create your custom reports.

HubSpot Sales Hub

HubSpot’s Sales Hub automatically records and tracks all relevant sales and performance data related to your teams. Specific items collected include activity reports for sales calls, emails sent, and meetings taken with clients, but you can also create custom reports.

If you want an immediate overview of your sales forecast, the Sales Hub comes with a probability forecast report. It gives a breakdown of how likely it will be that you’ll meet your monthly or quarterly sales goals (shown in the image below). These projections can help you come up with further questions to analyze in your regression analysis to understand what is (or isn’t) going wrong.

Automate.io

If you’re a HubSpot Sales Hub user and you want to use Google Sheets to conduct your regression analysis as I did, Automate.io allows you to sync and export data to external apps, including Google Sheets, eliminating the risks that can sometimes come from a simple copy+paste.

Another factor that can affect your analysis is whether you’re even doing it right. Like I said before, I’m bad at math, so I used an online tool. If you feel confident enough, feel free to use a pen, paper, and a quality calculator to run your analysis by hand.

If you’re like me, using statistical analysis tools like Excel, Google Sheets, RStudio, and SPSS can help you through the process, no hard calculations required. Paired with one of the data export tools listed above, you’ll have a seamless strategy to clean and organize your data and run your linear regression analysis.

Regression Analysis Helps You Better Understand Sales Performance

A regression analysis will give you statistical insight into the factors that influence sales performance.

If you take the time to come up with a viable regression question that focuses on two business-specific variables and use the right data, you’ll be able to accurately forecast expected sales performance and understand what elements of your strategy can remain the same, or what needs to change to meet new business goals.

Sales

A Straightforward Guide to Sales Potential

Sales forecasting can be a tedious process filled with endless rows of data that can start to blur together after you’ve spent one too many days looking at it.

Though it isn’t the most exciting process, preparing an accurate, reliable sales forecast is one of the most important tasks a sales team does each year.

A thorough sales forecast can pinpoint how much reps should bring in on a weekly, monthly, quarterly, and annual basis. However, a sales forecast is only as good as the data that goes into it.

There are various data sources that can contribute to a well-rounded sales forecast, however, one of the most underrated sources is sales potential.

To understand how well your company is positioned for growth, you need insight into your company’s ability to sell to new and existing customers in your product market — that’s where sales potential comes in.

Sales potential can be confused for market potential, however, it is important to understand the difference between the two. As stated above, sales potential shows the maximum amount of sales a company can bring in factoring all potential buyers within their specific product category. Market potential factors in the total sales potential of all competitors within a specific market.

Essentially, sales potential occurs at the company and product level, and market potential occurs at the broad product market level. Knowing your company’s sales potential can help you set well-informed sales targets depending on your company performance and market position.

Now that you know what sales potential is, let’s discuss the key metrics you can leverage to understand it.

How to Calculate Sales Potential

Though there is no single equation that can calculate sales potential, the following metrics can provide valuable insight.

1. Market Penetration

First, take a look at your current customer base, and divide it by the total number of potential buyers within a specific region or demographic. This will give you a rough indication of how much of your product market you have been able to convert.

If this value is low, that’s not necessarily a bad thing. That means your company has potential to tap into a base of buyers who could be a good fit for your product but haven’t made a purchase yet.

If this value is high, it could mean you’ve already saturated your target market, and that it may be a good time to introduce other offerings, or to focus on the upsell to keep buyers engaged.

2. Quantified Purchasing Capacity

What is the maximum number of purchases a customer can make from your company in one year? The answer is known as the quantified purchasing capacity for your business.

As an example, let’s say you work for a supplement company that offers powdered drink supplements. You offer three main products: a morning drink, an afternoon drink, and an evening drink. Each of the products has 30 days worth of servings, and when taken as intended, customers would take one serving of each drink each day.

So reasonably, within a year a customer would purchase three drink canisters per month, for 12 months out of the year, resulting in a total of 36 units each year. Your quantified purchasing capacity would be 36 units per customer per year. If this product costs $30 per unit, and customers by 36 units per year, the beverage company can anticipate bringing in up to $1,080 per customer each year.

You could then take this figure and compare it to the average purchase your customers make. If you find your average customer purchases one drink canister per month, you can adjust your sales forecast to account for those purchasing habits.

To bring in more revenue, you could focus your sales strategy on encouraging existing customers to purchase the entire product line to boost overall sales without having to appeal to a new audience.

3. Competitor Growth Rate

As you do necessary research to understand your company’s sales potential, the growth rates of your competitors should not be underestimated. If your competitors experienced significant growth in a specific product category, their trajectory can provide important information about the consumers you have in common and the potential sales that can be achieved.

If your growth rates are lagging far behind your competitors, that could be a good indication that your company has some internal factors that need to be addressed in order to reach greater sales potential.

Example of Sales Potential

Now let’s walk through a more in-depth example of sales potential for a fictional company called BossCo.

BossCo is a company that creates business management software for creative entrepreneurs. This company offers annual and monthly plans for their signature platform with additional upgrades and exclusive plug-ins customers can purchase to help the software work smoothly and efficiently with other systems.

When looking to understand their sales potential, BossCo factors in the following pieces of data:

  • Seasonality: They notice their subscriber base goes up in the fall and drops in the summer.
  • Existing customer data: The average BossCo customer is subscribed to their monthly software subscription, and has purchased one out of five possible upgrades.
  • Competitor analysis: BossCo currently has one main competitor who has a hold on equal market share, and is growing at a similar rate.
  • Market demographics: BossCo commissioned a study of 1,000 small business owners in their target market, and found only 15% of those surveyed use a platform to oversee and automate business management tasks.

With this data in mind, the BossCo team now understands the needs and habits of their customer base, and is relieved to not be behind the growth curve of their main competitor. With only 15% of their target demographic currently using the type of software they offer, with roughly half of them likely being BossCo customers, BossCo’s team can infer they have tapped into about 7.5% of their target market with room to grow as they expand their offerings and customer base.

Some of the ways BossCo can tap into a greater share of the market is by doing more research to understand the pain points of the 85% of those surveyed who aren’t using business management software, and incorporating tactics to win over these potential buyers into their sales strategy to increase market share.

Forecasting Tools

As your sales team carries out the necessary analysis to build your forecast and understand your company’s sales potential, consider leveraging a tool that can streamline your efforts.

InsightSquared

  • Price: Contact InsightSquared for pricing.

Image Source

InsightSquared has a revenue intelligence platform that can capture relevant sales activity to provide the visibility you need to build a well-rounded sales forecast. Using the power of AI you can view the data you need to understand your company’s sales potential at a glance.

MethodData

  • Price: Plans starting at $50 per month.

MethodData DashboardImage Source

HubSpot users can connect MethodData to their CRM to create custom dashboards and reporting. With custom reports from MethodData, your team can view and process sales data from every angle to create an accurate sales forecast. 

Though not as cut-and-dry as other sales metrics, sales potential is an important data point for mapping out your company’s potential growth trajectory and is a worthwhile addition to your sales forecasting process. For more sales forecasting tips, check out this post.

Translate »