Sales forecasting is the foundation for setting expectations and making plans for your business. While the numbers won’t be able to provide you with all the answers they will be tremendously useful in helping you identify the key factors that influence your sales and, as a result, your bottom line.
We created an Excel sheet as a quick check tool to supplement the tracking done by your accountant or software you have in place. It uses your historical data, based on the following 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 values.
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
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, we’ll work with a bit more data. Let’s 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
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 2016 (Quarter #1 in our sheet), 2017 (#5) and 2018 (#9). |
| Average Seasonality: | The average of the Seasonality values for the same quarter throughout the different years—i.e. average of the Seasonality values for Quarter #1, #5 and #9. |
How to start your sales forecast process, taking seasonality in mind?
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 the calculations won’t be precise. Check the table below to see how the cells 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 Seasonality value is negative, then your actual sales 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 more realistic than the linear projection of our first example:
Keep in mind that predictions won’t be 100% accurate all of the time with any type of forecasting method, because your business doesn’t operate in an isolated environment. For example, your sales might start dropping because customers have turned to a new competitor that wasn’t on your radar.
By tracking both projected and actual sales, a forecasting tool can signal when it’s time to start investigating. If you find and diagnose discrepancies early enough, you could learn valuable information and apply it to your next move — and that’s what successful management is all about!”
Forecast predicting sudden cash flow changes?
If you’re based in the UK and meet the below criteria, we can help. To find out more, visit our unsecured loan page.
A better take on business loans
Originally published May 8 2019 , updated February 24 2020
