How to Forecast Your Sales Through Simple Calculations

EOFY

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
  •  

We've built a free Excel sheet you can use.

Make a private copy of your own and populate it with your sales for the last 8 quarters.

Click for access.

 

Before you begin, here’s 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)

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

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

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
  •  

We've built a free Excel sheet you can use.

Make a private copy of your own and populate it with your sales for the last 12 quarters.

Click for access.

 

Before you begin, here’s 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:

 

2. Calculate Seasonality in your market.

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

 

3. Calculate your Average Seasonality for Average Quarter #1

 
Take the Seasonality numbers for Quarter #1, #5 and #9 and find 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

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 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:
 

 
In order to utilise this tool as much as possible, keep updating it over time with new sales numbers. This way you can analyse performance 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 — and that’s what successful management is all about!