Excel Inventory Forecasting: Tips, Tricks, & Techniques

By Marcel Deer
Nevena Radulović - Editor for Egnition
Edited by Nevena Radulović

Updated July 19, 2023.

A laptop showing the Microsoft Excel logo on the screen as the program for inventory forecasting

Accurate inventory forecasting can help you reduce inventory waste and prevent obsolete inventory in your Shopify store while promoting financial health by ensuring adequate stock levels are always available.

Inventory forecasting, or e-commerce forecasting, helps e-commerce store owners better manage their inventory by predicting future product demand based on real-time inventory trends and historical metrics, such as past sales data.

To reap the benefits of accurate inventory forecasting, such as supply level optimization and refined pricing and sales strategy, you can try what many brands do and use Microsoft Excel to conduct your inventory forecasts.

However, while Excel can give you useful insights into sales trends and demand changes, it can be difficult to figure out exactly how to forecast inventory in Excel—but we bring you useful techniques and tips to make the process easier.

» Supercharge your Shopify inventory forecasting

3 Techniques for Creating an Inventory Forecast in Excel

Excel is a popular tool for demand forecasting because it's cost-effective and can use conditional formatting. Although it can be error-prone and a bit time-consuming, it can still be an effective inventory forecasting tool that can ultimately help you improve your inventory turnover if you know how to use it properly.

1. Moving Average Technique

The moving average method can help you predict your future inventory demand by looking at the average value of past buyer trends and patterns over a given period. This method "moves" because it takes new data into consideration. For example, if you choose to calculate your average over a 3-month period, this method will use the data from January, February, and March. But next month, the data from February, March, and April will be used instead.

Using an average gives you a baseline and helps combat fluctuations that might give you an incorrect reflection of your inventory demands. You can use this formula to calculate your moving averages:

Moving average = Select the revenue earned from the current month + X number of prior months + apply this formula: =AVERAGE(Data Range)

Screenshot of calculating moving averages for inventory forecasting in Microsoft Excel

2. Linear Regression Technique

The linear regression technique predicts demand by comparing two different variables—for example, the number of umbrellas sold vs. rainfall amounts. In this example, if you're forecasting umbrella sales, you'd use the rainfall amount as your independent variable and compare it to the number of umbrellas sold over a certain period to predict future demand more accurately.

To employ linear regression in Excel, follow these steps:

  1. Create three columns and choose one to contain each of the 12 months.
  2. Screenshot of Microsoft Excel table containing a date range
  3. Fill the other two columns with data related to the variables you wish to examine (e.g., rainfall in millimeters and the number of umbrellas sold).
  4. Screenshot of filling data into a table before converting it into a chart
  5. Select the two columns with data and insert a scatter chart into your spreadsheet.
  6. Screenshot of inserting a scatter chart into Microsoft Excel
  7. Add a linear trendline to the data points.
  8. Screenshot of inserting a linear trendline into a scatter chart while doing inventory forecasting
  9. Your finished graph should resemble this.
  10. Example of a scatter chart with a linear trendline to conduct linear regression inventory forecasting

3. Exponential Smoothing Technique

Exponential smoothing predicts demand by smoothing out historical data, such as seasonality patterns and confidence intervals. Follow these steps to implement this technique yourself:

  1. Go to the Data menu in Excel.
  2. Screenshot to locate the Data tab in Microsoft Excel
  3. Select Forecast Sheet.
  4. Screenshot to locate the Forecast Sheet function in Microsoft Excel
  5. Pick a suitable chart (e.g., column or line chart).
  6. Screenshot of choosing a chart type while conducting inventory forecasting in Microsoft Excel
  7. Select your end forecast date.
  8. Screenshot of setting an end period for inventory forecasting in Microsoft Excel
  9. Click Create to generate a worksheet with a sales forecast.
  10. Screenshot of creating an inventory forecasting chart in Microsoft Excel after inserting the necessary information

» For more accurate forecasts, consider your pipeline inventory as well

3 Tips to Optimize Excel Inventory Forecasting

When using Excel for inventory forecasting, the data isn't always perfect, as it can only give you static information. However, you can make the most of Excel's features by optimizing your inventory forecasting using the following tips:

1. Customize Visual Data

Excel offers several customization options that you can use to optimize your inventory forecasting and avoid having to get rid of obsolete inventory. For example, you can customize the visual timeline for specific ranges with color coding and other visual cues that make it easier to understand the data at a glance.

2. Choose a Suitable Forecast Function

While creating inventory forecasts in Excel, you'll be able to use different forecast functions to guide your forecast predictions, such as:

  • FORECAST: Predicts a future value with linear regression.
  • FORECAST.LINEAR: Has the same purpose as FORECAST, just for newer versions of Excel.
  • FORECAST.ETS: Used for exponential smoothing.
  • FORECAST.ETS.SEASONALITY: Calculates the length of a recurring pattern in a timeline to detect seasonality.
  • FORECAST.ETS. CONFINT: Calculates the confidence interval for a forecasted value.
  • FORECAST.ETS.STAT: Determines a specific value based on exponential smoothing forecasting.

3. Rely on Third-Party Apps and Templates

Finally, you can also leverage Excel's existing functionalities with third-party software and pre-made forecasting templates. You can simplify the whole process by using tools like Asana, Salesforce, and Monday.com or downloading free Excel templates like this one from HubSpot.

A screenshot of Hubspot's template for Excel inventory forecasting

» Get helpful demand insights by tracking your inventory in Shopify

Simplify Your Excel Inventory Forecasting

While Excel is a powerful tool for inventory forecasting, it can also be tedious. However, you can streamline the process and create a more accurate inventory forecast using techniques like linear regression, moving averages, and exponential smoothing. Excel's customizable visual data and third-party software or templates can help you further optimize your inventory forecasting.

Understanding how to properly use Excel for inventory forecasting can help you make better decisions about your stock levels and ensure you have the right products at the right time.

Don't Know what Shopify is?

Start your own online eCommerce business using Shopify platform. Shopify gives you all the tools needed to make the journey easier, faster and more profitable.

Frequent Releases

We work tirelessly to deliver more value to your business. Our roadmap is largely based on the questions you ask. We listen and we deliver as often as we can.

24/7/365 apps support

You can reach out to us via a dedicated support icon in the apps. We're always there. Or simply send us an email with your questions at support@egnition.io

Pin It on Pinterest