How to Forecast Your Sales Through Simple Calculations

Sales forecasting plays an integral role in setting expectations and making plans for your business. It’s your best shot at predicting the future. While the numbers will rarely be 100% right, what you should aim to achieve is to understand the different factors that influence your sales and, as a result, your bottom line.

We created an Excel sheet to help you forecast your sales by using your historical data, based on this formula:

Projected Sales = Trend + Average Seasonality

While our automated sheet does all of the work for you, understanding the calculations gives you a more detailed view of how different factors can influence your final numbers. We’ll go through two examples that break down the process – one simplified version focused only on Trend to help grasp the basics, and one with the full formula. To avoid any confusion, we’ll use relatively small sales numbers.

1. Calculating sales projections when there is no seasonality

 

Let’s assume that your business has been running for the last 2 years with stable growth quarter over quarter and you want to forecast your sales for the next 2 years.

What do you need to start your forecasting process?

 

  • Your sales numbers for the last 8 quarters
  • Our forecasting Excel sheet

Click here to access a copy of our spreadsheet, make your own copy and start populating your sales for the last 8 quarters. But before you start working, you need to learn what each term stands for:

Year:The previous 2 years your business has been operating in + the next 2 years which you want to forecast your sales for.
Quarter:Every year consists of 4 quarters, so the total number of quarters you already have data for are 8. In this example, you want to forecast your sales until quarter 16.
Trend:The gradual change in your sales which moves in a certain direction quarter to quarter, represented by a line or curve on a graph. (We’ll explain how to calculate it.)
Seasonality:The periodic, generally regular and predictable pattern in the levels of business activity where most or all sales come from a specific quarter. (In this example, we will assume that there is no seasonality.)
Sales (projection):The projected sales figures based on the stats you added to the Sales column.

 

How to start your sales forecast process in steps?

 

1. Fill the last 8 quarters with your past sales, as seen below. Once you’ve filled in the cells, the sheet will automatically calculate the Trend for you using this forecasting Excel formula:

 

=FORECAST(B3,$C$3:$C$10,$B$3:$B$10)

 

Sales Forecast Example 1

You’ll notice here that the trend is going up based of the data it has from your past numbers.

2. Assume that seasonality is zero (for explanatory purposes)
By excluding Seasonality from the equation, your projected sales will be a straight line with no fluctuations. We can all agree that this is both unrealistic and unreliable, but it is a helpful way to get to grips with the basics.

 

3. The Sales (projections) column is automatically populated – you can see for Quarter #9 the value is $237.86, and the calculations go on until reaching the value $319.11 for Quarter #16:

Sales Projections = Trend + Seasonality
In this explanatory example, Seasonality = zero
Therefore, Sales Projections = Trend

 

Sales Projection Example 1

The sheet will automatically draw the linear graph for you, which should resemble this one:

Sales Forecast Chart 1

2. Calculating sales projections with seasonality (real-life example)

 

In our second example, let’s work with a bit more data and assume that your business has been operating for 3 years and you want to predict sales for the upcoming 8 quarters.

What do you need to start your forecasting process?

 

  • Your sales numbers for the last 12 quarters
  • Our forecasting Excel sheet

Click here to access a copy of the spreadsheet, make your own version and start populating your sales for the last 12 quarters. Before you start working with the Excel sheet, you need to learn what the new terms stand for:

Moving Average (4):The average of a set of four consecutive quarters (i.e. “Quarter #1 to Quarter #4” or “Quarter #2 to Quarter #5” and so on).
Centred Moving Average:The average of two consecutive Moving Average cells.
Average Quarter:The same quarter throughout the different years – i.e. the first quarter of 2015 (Quarter #1 in our sheet), 2016 (#5) and 2017 (#9).
Average Seasonality:The average of the Seasonality numbers for the same quarter throughout the different years – i.e. average of the Seasonality numbers for Quarter #1, #5 and #9.

 

How to start your sales forecast process, taking seasonality in mind, in steps?

 

1. Fill the last 12 quarters with your past sales, as seen below. The preset formulas will calculate the following:

Moving Average (=average(C3:C6))

Centred Moving Average (=average(D5:D6))

These are the numbers that help refine your actual sales figures from Seasonality so you can calculate your Trend more accurately – otherwise Seasonality will appear twice in your formulas and your numbers won’t be precise. Check the table below to see how the numbers are updated:

 

Sales Forecast Example 2 Stage 1

 

2. Calculate Seasonality in your market by looking at the difference between your actual sales and the numbers in the Trend column. If the numbers have a negative value, then your actual sales numbers haven’t reached the projected figures, and vice versa – a positive value means your business has hit the target and gone above.

 

Sales Forecast Example 2 Stage 2

 

3. Calculate your Average Seasonality for Average Quarter #1 by taking the Seasonality numbers for Quarter #1, #5 and #9 and finding their average. Here’s the formula:

=average(G3,G7,G11)

In our spreadsheet, the result is ‘-$17.31’, and you can see how the formula has also been applied to all of the other quarters.

 

4. Finally, the sheet will calculate your sales projections with the formula we mentioned in the beginning:

Projected Sales = Trend + Average Seasonality

=F3+$J$21

Sales Forecast Example 2 Stage 3

 

And there you have it – you’ve just finished your sales forecast. In the Sales (projections) column, you can see for Quarter #13 the value is $356.31, and so forth, until you reach the final quarter of your timeline. In this case, it’s Quarter #20 with a value of $457.63. You can make a forecast for even longer periods by adding more quarters and dragging down the formulas down to more cells in the Trend and Sales (projection) columns.

The sheet will automatically draw the graph for you, which looks far more realistic than the linear projection of our first example:

Sales Forecast Chart 2

In order to utilise this tool as much as possible, keep updating it over time with new sales numbers. This way you can analyse the performance of your business and make the necessary adjustments to your strategy, such as securing finance to grow or sustain your business.

If you take these insights and include them in your strategy, you will be better equipped to make the right calls when faced with critical decisions for your business – and that’s what successful management is all about!

Business growth awaits

You might also like:

Beginner’s Guide: 6 Branding Tips Every Small Business Should Know

Beginner’s Guide: 6 Branding Tips Every Small Business Should Know

Having a brand might sound like a luxury only the big names can afford, but this is far from the truth. A brand has less…

How Entrepreneurs Successfully Lead Change in Their Organisations

How Entrepreneurs Successfully Lead Change in Their Organisations

Transformational change is a problem that challenges many organisations. According to a study by McKinsey & Co, less than a third of participating executives could…

The 8 Steps to Your End-Of-Year Business Review [Checklist]

The 8 Steps to Your End-Of-Year Business Review [Checklist]

While handling the micro-tasks, a team may neglect the need to stop and evaluate the impact of their projects. An end-of-year business review is meant…