Demand forecasting – Part 2: [.blue]How to use Excel to predict future order volumes[.blue]

In the first of this 2-part blog series, you learned how important it is for eCommerce brands to predict demand for their products. This follow-up blog reveals practical steps you can take on Excel to forecast order volumes.

In eCommerce, demand forecasting is the process of predicting the future demand for products, whether these be new items or those that are a staple within your inventory. Knowing how many orders customers might place of a particular product and when is key to ensuring your stock is optimised to meet demand, particularly during peak trade periods. 

One of the best ways to forecast demand is by using historical sales data. Follow the simple step-by-step guide below on how to use simple Excel formulas to predict future order volumes: 

1. Gather historical order volume data

The first step in forecasting order volumes is to gather your historical sales data, which should include the number of orders received each month over a specified period. It’s essential to gather data from the same period each year to account for seasonality. For example, if you’re forecasting order volumes for the next 12 months, make sure you gather the number of orders you have received from the last 12 months. 

2. Create an Excel spreadsheet

Open a new Excel spreadsheet and enter your historical sales data into the first column. In a separate column, create a formula to calculate the monthly average. If you have 12 months of data, divide the total number of orders received by 12 to get the average. In the example below, we have collected the historical sales data from one of the eCommerce brands that work with Zendbox.

3. Don’t forget to take seasonality into account

To account for seasonality, create a new column and calculate the percentage change from the order volume total for each month. For example, if the average order volume for January 2022 was 100 and the order volume for January 2023 is 110, the percentage change would be 10%. Repeat this calculation for each month. Use the following formula to calculate seasonality in Excel:

[.blue]= (Order volume for the month – Average order volume) / Average order volume[.blue]

4. Forecast future order volumes

To forecast future order volumes, you’ll need to use your seasonality data to estimate future sales. You can do this by multiplying the average sales volume by the percentage change for each month. If, for instance, the average sales volume is 100 and the percentage change for January 2022 is 10%, the estimated sales volume for January 2023 would be 110. Repeat this calculation for each month. Use the following formula to forecast future order volumes in Excel:

[.blue]= Order volume for the month * (1 + Seasonality percentage change)[.blue]

5. Add a trendline

To create a visual representation of your demand forecast, you can create an Excel chart and add a trendline to it. You can do this by highlighting the data, selecting the “Insert” tab, followed by “Insert Line or Area Chart”.

Select the chart that appears, followed by the “+” in the top right corner, which will pull up a dropdown list of the “Chart Elements”. 

Select “Trendline” from the list and a window will appear prompting you to select the desired data series you wish to plot as a trendline (in this scenario, it would be the coming year). Select the year and then select “OK”. The trendline will show you the predicted trend in order volumes over the next 12 months.

6. Refine your demand forecast

Finally, refine your forecast by adjusting the trendline as necessary. For example, if you have a new product launch or a major marketing campaign planned, adjust the trendline to account for the expected increase in sales as a result of these business activities. 

Optimise your eCommerce operations

Accurately forecasting demand for your products can make a world of difference in helping to optimise your eCommerce operations for the benefit of your business and your customers. Without it, you’ll never know exactly how much stock to reorder from your suppliers. This is particularly important if you work with a 3PL provider, who ultimately needs to know how many orders you anticipate receiving during a particular period to ensure they are fully prepared to fulfil those orders at the speed, accuracy, and quality that you and your customers desire. 

By following the practical steps above and using some simple Excel formulas, you can make informed business decisions and work more collaboratively with third-party suppliers based on accurate predictions. Regularly updating your forecasts and making strategic adjustments within your business will only help you stay ahead of the competition and grow. 

For more useful insights on how to manage your inventory and prepare for your busiest trade periods, check out our blog hub or get in touch with our friendly team.