Inventory forecasting: models, best practices and formulas | Inventoro (2023)

Inventory Management > Inventory forecasting: models, best practices and formulas

Sales forecasting
and inventory optimization

Became a retail mastermind you always wanted to be.

Start 14 day free trial

14 minutes read

Written by: Jul Domingo

Inventory forecasting: models, best practices and formulas | Inventoro (1)

“Managing a small business is difficult. You are in charge of everything, from sales to marketing to recruiting, firing, leasing, and inventory.”

– Jeff Ber, Entrepreneur and Business Management Expert

Inventory forecasts are essential, especially when consumer preferences and supply chains fluctuate so much.

For SMB owners, this unpredictability can cause a few headaches. Some are inconsequential, while others can make or break a season.

To achieve accurate inventory forecasting, you need to glean insights from data analysis, industry trends, market expertise—and perhaps a touch of magic.

Learn what inventory forecasting is, how it works, examples, formulas, some handy tips, and a remarkable way to simplify the process.

What is inventory forecasting?

Inventory forecasting determines the amount of inventory needed to satisfy future customer demand based on the sales forecast over a certain period. It’s a practical solution to manage your purchases better, increase your revenue, and reduce unnecessary costs.

With accurate inventory forecasting, you can keep enough inventory on hand without draining your cash reserves.

Inventory forecasting: models, best practices and formulas | Inventoro (2)

Types of inventory forecasts and examples

The most successful businesses can reduce risks, meet future demands, and avoid future losses—and inventory forecasting is one way to achieve this. However, forecasts don’t have a one-size-fits-all formula.

“Inventory forecasting is challenging because it requires a business to analyze large amounts of data for products that are constantly changing in price and availability.”

– Stephen Curry, CocoSign CEO

Let’s take a look at the main forecasting techniques, which fall into four main categories:

  • Trend forecasting
  • Graphical forecasting
  • Qualitative forecasting
  • Quantitative Forecasting

Trend forecasting

This demand forecasting method uses trend patterns to predict future demand in inventory. It relies on sales history, present purchasing habits of your customers to scale the potential of your products, their needs, and possible cultural shifts.

Leonardo Gomez, founder of RunBall, a dog toy brand, believes trend forecasting is useful for making a lot of futuristic decisions.

Predicting how many stocks to sell will also enable you to estimate how much inventory you will need. Therefore, it depends on the upward and downward slopes of your forecast. Your trend forecast results can also inspire further research to improve inventory strategies.

Here’s an example of trend forecasting:

A small business selling accessories has been running for eight years. Over the years, the business has been profitable. However, there are no plans to expand yet.

Their historical data over the last three years shows that November, December, and January are their top-performing months, while May and June are the least successful.

Using this data, they can develop a trend projection that indicates when they should place their orders for supplies and products. This information can also enable them to set up a marketing campaign during their slowest months next year that would boost sales.

Graphical forecasting

This inventory forecasting method uses a graphical representation to interpret past data. It can be a histogram, a line graph, or a pie chart.

Suppose you analyze data points between customer demand and the supply chain in a forecast period. Using numerical data alone is bland and confusing. The visuals will be more accessible and efficient for the decision-making body.

By creating a graphical forecast, you can get a similar image of data between those periods. It will give you a clearer picture of the data trend as a whole. Developing inventory management procedures will be easier and more comprehensive that way.

Qualitative forecasting

Qualitative forecasting focuses on external influences such as political, economic, and environmental factors. This method is a subjective approach that requires the judgment of an expert. According to

Daniela Sawyer, a business development consultant shares, “Qualitative forecasting is based on non-measurable information. It’s especially critical when a company is just getting started because there isn’t much data.”

Here, we’re talking about current and real-life situations that can affect business operations. Besides expert judgment and market research, another qualitative technique is the Delphi method. Its goal is to come up with a uniform forecast based on the collaborative opinion of various experts. They will answer questionnaires anonymously for several rounds.

The following example illustrates qualitative forecasting:

A sustainable fashion brand wants to increase revenue by expanding their customer base. To conduct market research, they sent surveys to their existing customers. By analyzing the data, they developed customer profiles and discovered that most of their shoppers are millennials and Gen Z.

The company then decided to leverage social media channels, such as TikTok and Instagram, and ads to broaden their reach, including TikTok and Instagram. Finally, using econometric principles, they evaluated the potential impact of a new marketing campaign on future sales—which they can use as the basis for predicting demand forecasts.

Quantitative Forecasting

One method alone cannot give a comprehensive forecast. When you use the qualitative approach alone, the results are not factual enough. Likewise, you cannot solely rely on figures and statistics from quantitative forecasting.

In quantitative forecasting, the basis is historical sales data. The operation from the previous year will be the foundation to calculate the anticipated future sales. Seasonal trends like high fluctuations in Christmas sales also affect the quantitative forecast.

Time-series forecasting is a type of quantitative forecasting, which uses quantitative data to create a model that predicts future trends.

This inventory forecasting model applies to any timeframe, and it’s flexible enough to account for rare events that can affect the business. There are two types: moving average forecasting and exponential smoothing.

In moving average forecasting, you take the demand data from the previous period, calculate the average demand for that period, and use this average to forecast the demand for the future.

For example, if you were to analyze 12 months, the 13th month would be excluded. By next month, the 12th month would be eliminated.

In exponential smoothing, you take into account actual demand for the current period and the previous forecasts. You can tailor the results of your model to fit your company’s market positioning by adding a smoothing constant.

An example of this is during the COVID-19 pandemic, when a company finds it more helpful to incorporate more recent sales data because of its relevance.

Combining inventory forecasting techniques

In 2019, a case study proved that a combination of forecasting methods creates better results.

Combining inventory forecasting models can provide more comprehensive data to predict future sales and estimate the amount of inventory needed to meet demand without the risk of product loss or spoilage.

You can adapt to unexpected changes in consumer demand by using a mix of different methods.

“For our business, we tend to follow the trends for inventory forecasting along with the quantitative methods. This helps us keep a clear and defined track of the data.”

– Leonardo Gomez, RunBall Founder

Inventory forecasting formulas and examples

Different inventory forecasting formulas help identify the optimum stock levels to maintain and the definite time to reorder. This way, you can avoid losses both in surplus and out-of-stock situations.

However, there’s a downside to using formulas: they require a lot of work to determine and manage. Fortunately, modern technology has made it possible for smart inventory forecasting software to provide more precise reorder points by providing integrated predictive analytics and reordering capabilities.

Inventory forecasting: models, best practices and formulas | Inventoro (3)

Enjoy your free trial

Regardless, it might be useful for business owners to become familiar with these concepts:

  • Lead demand and safety stock
  • Ecomonic Order Quantity Model
  • Inventory Forecasting Excel Template

Lead demand and safety stock

Formula 1: Lead time x Average daily sales = Lead demand

Lead time: The number of days between the date of the purchase order and the date of delivery.

To compute the lead time, observe the regular interval of waiting before receiving the supplier’s stocks. For example, if you order today and receive it later within 7 days, your lead time is 7 days.

Lead demand: The total anticipated sales during the lead time. It helps you keep the inventory level needed to avoid running out of stock. The formula to compute for the lead demand will be:

Example:

7 days x 25 units = 175 units

The 175 units represent the number of stocks you need to keep while waiting for the new stocks.

Formula 2: (Max. Daily sales – Ave. daily sales) x (Max. lead time – Ave. lead time) = Safety stock

Safety stock: This is the extra quantity on hand to prevent out-of-stock situations. You can see them idle in the stockroom or warehouse in case of fluctuation in supply and demand.

Example:

Following the same case as your average daily sales and lead time, let’s assume that the maximum daily sales are 30 units and the maximum lead time takes 10 days. The safety stock would be as follows:

(30 days – 25 days) x (10 units – 7 units) = 15 units

Formula 3: (Average daily sales x Lead time) + Safety stock = Reorder point

Reorder point: This point refers to the specific stock levels for inventory replenishment. When the inventory reaches the said quantity, new purchase orders shall be made.

You can compute it either with safety stock or without safety stock. It all depends on the nature of your business and the buying habits of your customers.

Example:

(25 units x 7 days) + 15 = 190 units

The reorder point means that the moment your inventory level is reduced to 190 units, you need to restock immediately.

While the reorder point formula is a classic, it can lead to several problems. When multiple products reach reorder status at once, your system will become overloaded. Moreover, a business will have to devote more resources to restocking inventory because the replenishment items need to be ordered earlier.

Keeping your inventory stocked in a timely manner can prove difficult for SMBs. Investing in inventory forecasting and replenishment software is an easy way to resolve this issue.

Economic order quantity (EOQ) model

The EOQ model aims to reduce expenses, particularly the costs to pay for logistics and warehouse spaces. The larger the quantity you order, the less frequently you need to order, and the more money you save.

However, the EOQ model’s biggest flaw—and this is a big one—is that it assumes constant demand for a business product and quick access to restocking, dismissing seasonal or economic fluctuations.

Here’s how to calculate the EOQ:

√ (2 x Annual demand x Order cost / Holding cost) = EOQ
√ (2 x 25,000 units x $180 / $10) = 948 units (rounded up to the nearest whole unit)

Example: If the annual sales estimated are 25,000 units, the cost to hold your inventory is $10/unit, and the logistic costs to process your order is $180, your EOQ forecast would be 948 units. This only means that your reorder point is 948 units.

Inventory forecasting Excel template

You can create an inventory forecasting Excel worksheet by inputting a series of dates and values in a single worksheet. It’s the most straightforward hack to use your sales data to predict future operations. The following steps will enable Excel to show you the predictive value of your forecast.

  1. Create a New Worksheet. In two separate columns, enter the dates first and the values corresponding to that date next.
  2. Select the records by highlighting cells from both columns.
  3. Click the Data tab, and you will see the Forecast Sheet in the Forecast Group. Select that icon.
  4. When the Forecast Worksheet dialog box appears, choose the visual representation of the forecast you prefer.
  5. Choose the end date in the Forecast End box, and finally, click Create.

However, Excel has plenty of downsides when it comes to demand forecasting. Read more about it here.

8 practical inventory forecasting tips

“After successful demand forecasting, it will be much easier to forecast everything else for your small business.”

Dragan Sutevski, Small Business Consultant

A better forecast will lead to higher profits and lower costs. So take note of these demand forecasting tips to make it more accurate and effective.

  1. Keep up-to-date records. Inaccurate and outdated information skew forecast results. The future depends on your past and current transactions. Remember, historical data are the foundation of forecasting.
  2. Plan ahead of time. Forecasting requires a lot of time and brainstorming. Involve the decision-making body and your customer-facing staff ahead of time and ask for their ideas and suggestions.
  3. Set your targets. Each forecast has a specific goal. The forecast should help you meet your goals, whether it be the launch of a new product, a reduction in future costs, or beating your competition.
  4. Define the forecast period. With past data, decision-makers will be able to draw timely conclusions. However, data and trends are fluctuating, which means forecasts are only valid for a particular period.
  5. Flexible and combined methods. Forecasting involves many unforeseen variables. Put these variables into account by combining different techniques. The more comprehensive the forecast is, the more precise the result can get.
  6. Follow a consistent process. A consistent sales and inventory process is necessary to forecast sales. Trace the comparative output for several cycles.
  7. Review results and observe variance. Compare the prediction against the actual results. Study the factors that resulted in the variance for future reference to help you identify possible deviations in the next forecast.
  8. Consider using forecasting tools and software. Combining forecasting methods helps improve results, but it’s complicated and less efficient than automated forecasting. Automated forecasting reduces tedious work and unnoticeable human errors, which leads to a more accurate forecast.
Inventory forecasting: models, best practices and formulas | Inventoro (4)

Source: LinkedIn

“Small eCommerce enterprises often start with spreadsheets since they are less expensive. However, inventory software management becomes necessary as an eCommerce business grows”

– Gerrid Smith, CEO & Founder of Property Tax Loan Pros

Automated forecasting: the Inventoro way

Remember when we said you might need a bit of magic to help project future demand? Automated forecasting can be your crystal ball, allowing you to see into the future.

Automatic inventory forecasting uses machine learning to enhance the prediction process continuously. “Instead of just tracking your inventory, you’ll be able to estimate appropriate stock levels based on business goals and processes,” Sawyer adds.

Using our cutting-edge machine learning technology, Inventoro helps reduce supply chain network failures and stockouts and increases customer satisfaction and profits. Here’s how:

Sales forecasting: As the methods and formulas above have shown, it’s important to paint a picture of how your sales will be in order to determine your future inventory levels. Inventoro has been developed to deliver forecasts on the same day they are created.

Product segmentation: There’s no need to guess which products are performing well or not. With Inventoro’s product segmentation feature, we separate the winners, chasers, and losers in your inventory so you can better allocate your resources.

Replenishment: Order placement is among the most tedious but necessary parts of managing inventory. But with Inventoro’s replenishment system, you’re able to fully automate this process, saving you valuable time you can devote to other aspects of your business.

Ready to get past the struggle of predicting inventory levels for your business? Contact us today for a free trial.

Sales forecasting
and inventory optimization

Became a retail mastermind you always wanted to be.

Start 14 day free trial

FAQs

What is the best formula to forecast inventory? ›

ROP = (average daily sales x lead time) + safety stock.

The ROP is calculated by multiplying your average daily sales with lead time and adding the result with safety stock.

What are the 4 basic forecasting method? ›

While there are a wide range of frequently used quantitative budget forecasting tools, in this article we focus on four main methods: (1) straight-line, (2) moving average, (3) simple linear regression and (4) multiple linear regression.

What formula below can be used to forecast inventory? ›

Expert-Verified Answer. The formula that can be used to forecast inventory is: (Inventory days / Cost of sales) x 365.

Which is the #1 rule of forecasting? ›

RULE #1. Regardless of how sophisticated the forecasting method, the forecast will only be as accurate as the data you put into it. It doesn't matter how fancy your software or your formula is. If you feed it irrelevant, inaccurate, or outdated information, it won't give you good forecasts!

How to do inventory forecasting in Excel? ›

On the Data tab, in the Forecast group, click Forecast Sheet. In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast. In the Forecast End box, pick an end date, and then click Create.

What are the four inventory models? ›

There are four main types of inventory: raw materials/components, WIP, finished goods and MRO.

What are the three types of forecasting models? ›

There are three basic types—qualitative techniques, time series analysis and projection, and causal models. The first uses qualitative data (expert opinion, for example) and information about special events of the kind already mentioned, and may or may not take the past into consideration.

What are the three inventory models? ›

Three of the most popular inventory control models are Economic Order Quantity (EOQ), Inventory Production Quantity, and ABC Analysis. Each inventory model has a different approach to help you know how much inventory you should have in stock.

What is the best forecast model? ›

Global models with worldwide weather forecasts

The ECMWF is generally considered to be the most accurate global model, with the US's GFS slightly behind.

What are the five 5 steps of forecasting? ›

The major steps that should be addressed in forecasting include: Establishing the business need. Acquiring data. Building the forecasting model. Evaluating the results.

What are the 2 main methods of forecasting? ›

There are two types of forecasting methods: qualitative and quantitative.

What is the forecasting formula? ›

=FORECAST(x, known_y's, known_x's)

The FORECAST function uses the following arguments: X (required argument) – This is a numeric x-value for which we want to forecast a new y-value. Known_y's (required argument) – The dependent array or range of data.

What are six basic steps in the forecasting process? ›

The following slide highlights the six steps of business forecasting process illustrating key headings which includes problem identification, information collection, preliminary analysis, forecasting model, data analysis and performance review.

What is the golden rule of forecasting? ›

The Golden Rule of Forecasting is to be conservative. A conservative forecast is consistent with cumulative knowledge about the present and the past. To be conservative, forecasters must seek out and use all knowledge relevant to the problem, including knowledge of methods validated for the situation.

What are the 2 errors of forecasting? ›

Two of the most common forecast accuracy/error calculations are MAD – the Mean Absolute Deviation and MAPE – the Mean Absolute Percent Error.

What are the key ratios in forecasting? ›

Some of the more popular financial ratios are: Working Capital to Total Assets Ratio, Debt to Equity Ratio, Debt to Total Assets Ratio, Return on Assets, Return on Equity and many more.

How do you calculate forecast accuracy in Excel? ›

The forecast accuracy formula is straightforward : just divide the sum of your errors by the total demand.

What forecasting method does Excel use? ›

Exponential smoothing forecasting in Excel is based on the AAA version (additive error, additive trend and additive seasonality) of the Exponential Triple Smoothing (ETS) algorithm, which smoothes out minor deviations in past data trends by detecting seasonality patterns and confidence intervals.

What is the easiest way to track inventory in Excel? ›

How to Create An Excel Inventory Management System
  1. Create a spreadsheet. To manage your inventory in Microsoft Excel, begin by creating a new spreadsheet. ...
  2. Add any necessary product categories as columns. ...
  3. Add each product that you carry to the spreadsheet. ...
  4. Adjust the quantities as you make sales.

What is the formula of inventory model? ›

The total cost of inventory is the sum of the purchase, ordering and holding costs. As a formula: TC = PC + OC + HC, where TC is the Total Cost; PC is Purchase Cost; OC is Ordering Cost; and HC is Holding Cost.

What are typical inventory models? ›

Inventory models deal with the time at which orders for certain goods are to be placed, and the quantity of the order. The research problem concerns ways of optimizing these decisions, taking into account the cost of obtaining the goods, the cost of holding a unit in inventory, and the cost of shortages.

What is the inventory formula? ›

The first step to calculating beginning inventory is to figure out the cost of goods sold (COGS). Next, add the value of the most recent ending inventory and then subtract the money spent on new inventory purchases. The formula is (COGS + ending inventory) – purchases.

What is Delphi method of forecasting? ›

The Delphi method uses multiple rounds of questionnaires sent to a panel of experts to work toward a mutual agreement or consensus opinion. The participants modify their responses based on the information brought forth by other experts participating in the analysis.

What is the difference between a model and a forecast? ›

Financial forecasting is the process by which a company thinks about and prepares for the future. Forecasting involves determining the expectations of future results. On the other hand, financial modeling is the act of taking a forecast's assumptions and calculating the numbers using a company's financial statements.

What is ABC model in inventory management? ›

ABC analysis is a method in which inventory is divided into three categories, i.e. A, B, and C in descending value. The items in the A category have the highest value, B category items are of lower value than A, and C category items have the lowest value.

What is the EOQ inventory model? ›

The economic order quantity (EOQ) refers to the ideal order quantity a company should purchase in order to minimize its inventory costs, such as holding costs, shortage costs, and order costs.

What is the most common inventory method? ›

The FIFO method is the most popular inventory method because it's the one that most closely matches the actual movement of inventory for most businesses. This method assumes that the first products you acquired will be the first that are sold.

What are the 7 steps in a forecasting system? ›

These seven steps can generate forecasts.
  • Determine what the forecast is for.
  • Select the items for the forecast.
  • Select the time horizon. Interested in learning more? ...
  • Select the forecast model type.
  • Gather data to be input into the model.
  • Make the forecast.
  • Verify and implement the results.

What are the three elements of a good forecast? ›

List the elements of a good forecast. -The forecast should be timely. -The forecast should be accurate. -The forecast should be reliable.

Which is the most accurate forecasting method and why? ›

Of the four choices (simple moving average, weighted moving average, exponential smoothing, and single regression analysis), the weighted moving average is the most accurate, since specific weights can be placed in accordance with their importance.

What are the best time series forecasting models? ›

ARIMA and SARIMA

AutoRegressive Integrated Moving Average (ARIMA) models are among the most widely used time series forecasting techniques: In an Autoregressive model, the forecasts correspond to a linear combination of past values of the variable.

What is the best forecasting method for stocks? ›

Time series analysis will be the best tool for forecasting the trend or even future. The trend chart will provide adequate guidance for the investor. So let us understand this concept in great detail and use a machine learning technique to forecast stocks.

Which moving average is best for forecasting? ›

Forecasting. Hence, the 3-mth weighted moving average has the lowest MAD and is the best forecast method among the three.

What are the 3 forecasting techniques? ›

There are three basic types—qualitative techniques, time series analysis and projection, and causal models.

What are the five methods of forecasting? ›

Top forecasting methods include Qualitative Forecasting (Delphi Method, Market Survey, Executive Opinion, Sales Force Composite) and Quantitative Forecasting (Time Series and Associative Models).

What are the different formulas used in inventory management? ›

Inventory formulas & live inventory calculators
  • Maximum daily use x Maximum lead time in days. - Average daily use x Average lead time in days. Safety stock. ...
  • 2x Annual demand in units x Order cost per Purchase Order. ÷ Annual holding cost per unit. ...
  • Beginning inventory value + Ending inventory value. ÷2. Average inventory value.

What are the most common inventory ratios? ›

Inventory Turnover Ratio is the most commonly used and perhaps most important inventory ratio. It tracks the number of times a business cycles through its inventory in a given time period.

What is a good inventory turnover ratio? ›

For most industries, the ideal inventory turnover ratio will be between 5 and 10, meaning the company will sell and restock inventory roughly every one to two months. For industries with perishable goods, such as florists and grocers, the ideal ratio will be higher to prevent inventory losses to spoilage.

Which type of forecasting is more accurate? ›

While quantitative methods, which rely on historical data, are typically the most accurate forecasting methods, they don't work well for long-term predictions. If you're planning to forecast over several years, try qualitative forecasting methods, which rely on expert opinions instead of company-specific data.

What are the best time series forecasting models for stock market? ›

One of the most common and efficient methodology is ARIMA modeling of TSD. Autoregressive Integrated Moving Average (ARIMA) Model converts non-stationary data to stationary data before working and is one of the most popular models to predict nearly linear time series data.

What 3 moving averages should I use? ›

Common Moving Averages Periods

For identifying significant, long-term support and resistance levels and overall trends, the 50-day, 100-day and 200-day moving averages are the most common.

How do you calculate moving average forecast in Excel? ›

2. Using Data Analysis
  1. Go to Data Tab > Analysis > Data Analysis. ...
  2. The Data Analysis dialog box opens up.
  3. Select Moving averages.
  4. In the moving averages dialog box: ...
  5. Click Okay, and there you have the moving averages calculated.
Feb 23, 2023

What is a 3 month moving average forecast? ›

The three-month moving average represents the trend. From our example we can see a clear trend in that each moving average is $2,000 higher than the preceding month moving average. This suggests that the sales revenue for the company is, on average, growing at a rate of $2,000 per month.

References

Top Articles
Latest Posts
Article information

Author: Kareem Mueller DO

Last Updated: 08/11/2023

Views: 6105

Rating: 4.6 / 5 (46 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Kareem Mueller DO

Birthday: 1997-01-04

Address: Apt. 156 12935 Runolfsdottir Mission, Greenfort, MN 74384-6749

Phone: +16704982844747

Job: Corporate Administration Planner

Hobby: Mountain biking, Jewelry making, Stone skipping, Lacemaking, Knife making, Scrapbooking, Letterboxing

Introduction: My name is Kareem Mueller DO, I am a vivacious, super, thoughtful, excited, handsome, beautiful, combative person who loves writing and wants to share my knowledge and understanding with you.