In today’s dynamic business environment, supply chain and demand planning have become critical to successful businesses.
As a result, demand planners and supply chain managers are constantly looking for ways to improve their planning processes to optimize inventory levels, minimize lead times, and improve customer service levels.
Excel is a powerful tool widely adopted for demand planning due to its ease of use and flexibility. This blog post aims to provide insights on effectively utilizing Excel functions for demand planning.
Importance of demand planning for supply chain management
Demand planning is an essential process that helps businesses anticipate customer demand and plan their inventory levels accordingly.
Effective demand planning ensures that the right products are available at the right time, in the right quantity, and at the right location. This helps businesses reduce inventory carrying costs and ensures customer satisfaction by minimizing stockouts and backorders.
Supply chain management involves coordinating all activities producing and delivering goods and services to customers.
Demand planning is a critical component of supply chain management as it helps businesses to optimize their supply chain processes, reducing lead times and improving customer service levels.
How useful is Excel for Demand Forecasting?
Excel is a powerful tool that provides a range of functions that can be used for demand planning. These functions include forecasting, data analysis, what-if analysis, and optimization.
These functions enable demand planners to make data-driven decisions, improve forecast accuracy, and optimize inventory levels to meet customer demand.
In the following sections, we will explore these Excel functions in more detail and provide insights on effectively using them for demand planning.
Excel’s forecasting functions for Demand Forecasting
Excel provides a range of powerful forecasting functions that can be used for demand planning. These functions include moving averages, exponential smoothing, and regression analysis.
Let’s understand how these functions are built, and later we’ll see how to use them with real data:
1. Moving Averages
Moving averages are a simple and commonly used forecasting technique in demand planning. They involve calculating the average of a specific number of periods of historical data, which is then used as the forecast for the next period.
To use moving averages in Excel, you can use the AVERAGE and OFFSET functions to define the period range.
The formula for a 3-period moving average in cell B3 would be =AVERAGE(OFFSET(A3,-2,0,3,1)).
2. Exponential Smoothing
Exponential smoothing is a more sophisticated forecasting technique that assigns more weight to recent data points while assigning decreasing weights to older data points.
This technique assumes that recent data is more relevant to future demand than older data. To use exponential smoothing in Excel, you can use the EXPONENTIAL SMOOTHING function, which allows you to define the smoothing factor.
The formula for exponential smoothing with a smoothing factor of 0.2 in cell B3 would be =EXPONENTIAL_SMOOTHING(A3,B2,0.2).
3. Regression Analysis
Regression analysis is a statistical technique identifying the relationship between two or more variables. In demand planning, regression analysis can be used to determine the factors that influence demand and forecast future demand based on these factors.
To use regression analysis in Excel, you can use the LINEST function, which provides the regression equation coefficients.
The formula for regression analysis with two independent variables (X1 and X2) in cells B3 and C3 would be =LINEST(A3:A8, {X1range, X2range}, TRUE, TRUE).
Choose the appropriate forecasting technique based on the data and the demand pattern. One important point is to use sufficient historical data to ensure accurate forecasts.
Demand planners must regularly review and update forecasts based on new data and changes in demand patterns. They should also use visualizations to better understand the data and forecast results.
Case Study: Demand Planning for an FMCG Company using Excel
Let’s understand the application of these formulae with the help of a quick case study. This case study is about an FMCG company named ABC Foods, for which we have the historical demand, and we will see how future demand can be forecast with the help of Excel functions.
Step 1: Collecting and Analyzing Historical Data
To begin the demand planning process, the supply chain team at ABC Foods collects historical sales data for the past 12 months for its snack products. The data includes monthly sales figures for each product SKU.
| Month | Sales |
| Jan | 150 |
| Feb | 180 |
| Mar | 200 |
| Apr | 170 |
| May | 190 |
| Jun | 220 |
| Jul | 240 |
| Aug | 250 |
| Sep | 210 |
| Oct | 230 |
| Nov | 240 |
| Dec | 260 |
Next, they use Excel’s data analysis functions to analyze the data and identify patterns or trends. Here’s how they do it:
Descriptive Analytics:
To calculate descriptive analysis for the sales data, the team uses the following formula in Excel:
=AVERAGE(B2:B13) – Calculates the average weekly sales
The result will be 204.17. The average sales can help demand planners understand the baseline level of demand for a product and to detect any seasonal or trend changes that may be occurring.
It can also be used as a starting point for forecasting future sales, particularly if the sales history is relatively stable.
=MEDIAN(B2:B13) – Calculates the median weekly sales
The result will be 205. The median sales can provide a more robust measure of central tendency if the sales data has extreme values or outliers.
This can be particularly useful if the demand for a product is subject to occasional spikes or dips that can skew the average.
=STDEV.S(B2:B13) – Calculates the standard deviation of weekly sales
The result will be 34.96. The standard deviation of sales can provide an indication of the variability or volatility of demand. A high standard deviation indicates that demand is more variable, which can make forecasting more challenging.
It can also be useful for determining safety stock levels and identifying potential supply chain risks.
In demand forecasting terms, the average, median, and standard deviation of historical sales data can provide important insights and help demand planners make more accurate and informed decisions while forecasting.
Step 2: Forecasting Future Demand
Once the team has analyzed the historical data, they use Excel’s forecasting functions to predict future demand. Here’s how they do it:
Moving Averages:
To calculate a rolling average of weekly sales for each product SKU, the team uses the following formula in Excel:
=AVERAGE(SalesData[Week1]:SalesData[WeekN]) – Calculates the average weekly sales for the past N weeks
To calculate a 3-month moving average, we’ll start with the third month (March) and calculate the average of the previous three months’ sales. So, in cell B4, we’ll enter the following formula:
=AVERAGE(B2:B4)
This will give us a moving average of 177.67 for March. We’ll then copy this formula down to the rest of the cells in column B to get the moving averages for the remaining months:
| Month | Sales | 3-Month Moving Average |
| Jan | 150 | |
| Feb | 180 | |
| Mar | 200 | 177.67 |
| Apr | 170 | 183.33 |
| May | 190 | 186.67 |
| Jun | 220 | 193.33 |
| Jul | 240 | 216.67 |
| Aug | 250 | 236.67 |
| Sep | 210 | 236.67 |
| Oct | 230 | 233.33 |
| Nov | 240 | 226.67 |
| Dec | 260 | 243.33 |
Exponential Smoothing:
To smooth out any fluctuations in the data and identify underlying trends, the team uses the following formula in Excel:
=EXPONENTIALSMOOTHING(SalesData, Alpha) – Calculates the exponentially smoothed values of the sales data with a specified smoothing factor (Alpha)
To perform exponential smoothing, we’ll first need to determine the smoothing factor, alpha. In exponential smoothing, alpha is the smoothing factor or smoothing constant.
It is a value between 0 and 1 that determines the weight given to the most recent observation in the time series. A larger alpha value assigns more weight to the most recent observation, resulting in a faster response to changes in the data.
Let’s assume alpha is 0.3. To calculate the first forecast, we’ll simply use the first month’s sales figure. So, in cell C4, we’ll enter:
=B2
This gives us a forecast for January of 150. To calculate the forecast for February, we’ll use the following formula in cell C5:
=C4 + 0.3 * (B3 – C4)
| Month | Sales | 3-Month Moving Average | Exponential Smoothing Forecast |
| Jan | 150 | 150.00 | |
| Feb | 180 | 156.50 | |
| Mar | 200 | 177.67 | 179.05 |
| Apr | 170 | 183.33 | 174.94 |
| May | 190 | 186.67 | 183.70 |
| Jun | 220 | 193.33 | 207.62 |
| Jul | 240 | 216.67 | 233.73 |
| Aug | 250 | 236.67 | 246.41 |
| Sep | 210 | 236.67 | 230.49 |
| Oct | 230 | 233.33 | 231.60 |
| Nov | 240 | 226.67 | 237.08 |
| Dec | 260 | 243.33 | 251.56 |
Conclusion
In conclusion, demand planning is a critical process that helps businesses optimize inventory levels, reduce carrying costs, and improve customer satisfaction by ensuring the right products are available at the right time and location.
Excel is a powerful tool for demand planning due to its ease of use and flexibility. Its forecasting functions, including moving averages, exponential smoothing, and regression analysis, enable demand planners to make data-driven decisions, improve forecast accuracy, and optimize inventory levels.
By analyzing historical data and identifying patterns and trends using Excel’s data analysis functions, demand planners can make informed decisions and update forecasts regularly.
