.svg)
Download Brouchure
Click the button to download the PDF file.
Download
%202.svg)
Oops! Something went wrong while submitting the form.
Many companies still use Excel for demand forecasting because it feels familiar and convenient. However, choosing the wrong forecasting model or incorrectly setting parameters can unexpectedly lead to inventory losses. Statistical mistakes that slip through can result in inaccurate forecast results being delivered to executives.
While Excel-based demand forecasting involves various challenges, many companies struggle to move away from traditional manual processes. Most either don't know more efficient methods exist or haven't realized the urgent need for change.
This article explores five common errors encountered when forecasting with Excel and considers practical ways to solve these problems effectively.
The most commonly used method in practice is the moving average. When forecasting next month's shipment volume, you average the past three months of shipments. It's simple and intuitive, so many managers forecast this way.
The problem is that forecast results change completely depending on how you set this period. With a three-month window, you're sensitive to recent changes but also capture lots of noise. With a twelve-month window, you get stability but miss rapid market changes. If sales have actually been increasing by 10% monthly for the past six months, a twelve-month moving average won't properly reflect this upward trend and will consistently underforecast.
An even bigger problem occurs when using weighted moving averages. When trying to give higher weight to recent data, if the weights don't sum to one, forecast values become systematically distorted. For example, if you assigned weights of 0.5, 0.3, and 0.3 to the most recent three months, the sum becomes 1.1. When the sum of weights exceeds one (example: 1.1), this method no longer calculates an average but predicts values exceeding the average, creating a very high likelihood of systematic overforecasting. These mistakes are hard to find, and by the time you discover them, you've often already made production plans based on wrong forecasts.
To use exponential smoothing in Excel, you must manually set the alpha value. When alpha approaches zero, forecast values stay fixed to the initial value and don't respond to changes at all. Conversely, when it approaches one, only the most recent value gets reflected, ignoring all past data.
So what's an appropriate alpha value? Generally, between 0.1 and 0.3 is considered stable, but this varies completely by industry and product characteristics. For new products, you should set it high, around 0.5 to 0.9, to track rapid market changes. The question is whether managers can make these judgments properly.
Double or triple exponential smoothing gets even more complex. You need to set trend smoothing constant beta and seasonal index gamma, and forecast results vary tremendously depending on how you combine these values. If you set beta too low, you'll miss upward trends and consistently underforecast.
When forecasting products with seasonality, you must choose between additive and multiplicative models. Additive models assume seasonal variation amplitude remains constant, while multiplicative models assume variation amplitude is proportional to demand level.
Let me give you a real example. Suppose ice cream sales are 1,000 units in summer but only 100 units in winter. Summer variation amplitude is about 500 units, while winter variation amplitude is about 50 units. In this case, variation amplitude is proportional to demand level, so you should use a multiplicative model. But if you choose an additive model, you'll overforecast in winter and underforecast in summer. You also need to set seasonal cycles accurately. If you have monthly data but set the seasonal cycle to 4, you'll search for quarterly patterns and completely miss monthly seasonality.
Ultimately, in Excel, managers must manually set all these parameters. How many months for the moving average period, what alpha value, whether to choose additive or multiplicative—all these decisions fall to people.
What happens when a new manager gets assigned? They copy parameter values the previous person used without verifying why those values were chosen or whether they're still appropriate. When market conditions change but you keep using year-old parameters, forecast accuracy inevitably declines.
Test multiple parameter combinations and compare forecast errors. Calculate the difference between forecasted and actual values using the past twelve months of data, then select the combination with the smallest error.
Excel's forecast sheet function automatically finds optimal parameters. While not perfect, it's more accurate than manual adjustment.
Markets constantly change. Just because a product sold well last summer doesn't guarantee the same this summer. Last summer might have been unusually hot, or perhaps competitors' products had problems that drove customers to our product.
The greater difficulty comes when market size itself expands or contracts. For example, even for products that sell well in summer, if the market is growing 15% annually, relying only on last year's sales will create large forecast errors. In such cases, you need to calculate market growth rate and seasonal factors separately for accurate forecasts. In other words, simple averages or year-over-year comparisons alone can't properly reflect these market changes.
Excel's FORECAST function is convenient but has major limitations. It assumes data increases or decreases in a straight line. Most actual business data is nonlinear.
New product sales increase slowly at first, then surge rapidly when word spreads, then growth slows again as markets saturate. This S-curve pattern—what happens when you forecast it with the FORECAST function? You overforecast initially, underforecast during rapid growth, and overforecast again during saturation.
Seasonality works the same way. Winter coat sales surge in November and plummet in March. When you forecast such dramatic changes with linear functions, you completely miss actual peaks and valleys. Situations repeat where inventory is drastically insufficient or excessively accumulated.
Even more complex cases exist. Sometimes seasonality itself changes over time. For example, suppose air conditioner sales five years ago were 500 units in summer and 100 units in winter. But nowadays, sales are 2,000 units in summer and 400 units in winter. As the market grows, seasonal variation amplitude also increases. Simple seasonal models can't reflect these changes. Applying past patterns directly leaves you severely short during summer peak season.
Fashion and trend products are worse. Styles that sold well last year can be completely rejected this year. As consumer tastes change rapidly, many items become excess inventory when you forecast based only on past data.
What happens when unexpected events like COVID-19 hit? Since no such situations exist in past data, forecasting becomes impossible regardless of which statistical model you use.
In Excel, to reflect such external shocks, managers must manually insert adjustment values. But judging how much to adjust is difficult. Adjust too much and excess inventory accumulates; adjust too little and stockouts occur.
At minimum, view three-month moving averages and year-over-year comparisons together. When the two values differ significantly, it signals market changes. Check month-over-month growth rates monthly to identify trend changes.
When nonlinear trends are clear, try log transformations. Take the logarithm of sales volume, forecast, then transform back exponentially to somewhat reflect exponential growth patterns.
Many cases involve using regression analysis with multiple variables to forecast demand. You might input variables like advertising spend, promotional spend, and pricing to forecast next month's sales.
A common mistake occurs here. When independent variables have high correlations with each other. For example, suppose you included advertising spend X1, promotional spend X2, and total marketing spend X3 as variables. But X3 is the sum of X1 and X2, so they're perfectly correlated.
This makes regression coefficients unstable. Advertising spend coefficients come out negative when theoretically they should be positive, producing strange results. The R² coefficient of determination comes out high, but individual variable significance all comes out low, making it impossible to judge which variables actually have impact.
In practice, people often input variables without checking correlation coefficients individually. When you create a correlation coefficient matrix in Excel, you'll discover several variable pairs showing high correlations above 0.9. Including all such variables makes the forecast model itself unreliable.
One basic regression analysis assumption is that error variance remains constant. But actual data often violates this assumption.
For example, suppose when revenue is one million won, forecast error is ±50,000 won, but when revenue is ten million won, forecast error is ±1 million won. As revenue levels increase, errors also increase. This is called heteroscedasticity.
When heteroscedasticity exists, standard errors are underestimated and confidence intervals are calculated narrower than reality. What appears as a 95% confidence interval is actually only 70%. Reporting with this confidence interval to executives causes risk underestimation.
In Excel, you need to plot residuals versus predicted values to discover heteroscedasticity, but this diagnostic process often gets skipped. Because people extract regression equations and immediately use them for forecasting, they completely miss statistical problems.
When running regression analysis on time-ordered data like monthly sales, autocorrelation problems occur. If this month's error is +100, next month's error tends to be similar at around +95.
When autocorrelation exists, standard errors are underestimated, causing incorrect judgments that statistically insignificant variables are significant. You commit the error of continuing to include variables that actually have no impact because you think they're important.
The Durbin-Watson test can check for autocorrelation, but Excel doesn't have this built-in function. Managers must create formulas themselves, but most don't perform such tests at all.
Before regression analysis, always check the correlation coefficient matrix. If variable pairs have correlation coefficients above 0.8, you should remove or combine one.
Plot residual scatterplots to visually check for heteroscedasticity and autocorrelation. When patterns appear, try log transformations or differencing.
Internal sales data alone has limits for demand forecasting. You absolutely must reference external variables like exchange rates, oil prices, competitor pricing, weather, and consumer sentiment indexes.
However, having managers manually collect this data daily is realistically difficult. You'd need to repeat daily the process of going to the Bank of Korea website to copy exchange rates and paste them into Excel, searching the Korea Meteorological Administration site for temperature and precipitation information to input, and downloading consumer sentiment indexes from Statistics Korea.
One or two variables might be manageable, but manually updating ten or twenty external data sources daily is virtually impossible. Ultimately, you miss important variables and forecast accuracy inevitably declines.
For export-focused manufacturing, exchange rate impact is absolute. When exchange rates change 10%, order volume can change 30%. But if you don't update exchange rate data daily and only update once monthly, forecasts are already stale values based on month-old exchange rates.
When you run regression analysis in Excel, you get an R² value. When this comes out high like 0.95, it's easy to think you have a good model. However, overfitting phenomena can occur where accuracy is high only on training data but accuracy drops sharply on new data.
Overfitting results from excessively fitting only past data. It occurs when you input too many variables or use high-degree polynomial regression. For example, if you run regression analysis on twelve months of data with fifteen variables, you'll fit past data almost perfectly, but next month's forecast will miss badly.
Excel has no function to split data into training and validation sets for cross-validation. Managers must manually divide data and calculate and compare forecast accuracy for each, but such processes rarely occur.
Underfitting is also problematic. When clear seasonal patterns exist but you only use simple moving averages, you completely fail to forecast seasonal peaks and valleys. Winter coats surge in November and plummet in March, but moving averages only show gradual increases and decreases. Inventory management inevitably fails.
Food and beverage industries are heavily influenced by weather. If last summer was two degrees hotter than average, forecasting this year based on those sales creates large errors. You need to quantitatively input the correlation between temperature and sales into your model, but doing such analysis in Excel requires manually inputting meteorological data daily.
For fashion industries, trend data matters. You need to consider unstructured data like social media mentions, search trends, and influencer activity for accurate forecasts. Handling such data in Excel is nearly impossible.
Using the Power Query feature, you can automatically pull data from websites. Once set up, latest data updates with just a refresh.
Split data into 70% for training and 30% for validation. Build models with training data and check accuracy with validation data to prevent overfitting.
When using Excel's FORECAST function, if y-value and x-value range counts differ (#VALUE! error), range mismatch errors occur. These frequently happen when adding/deleting data or copying multiple product data. Even when errors occur like at the 50th out of 500, they're hard to spot immediately, with bigger problems being delayed discovery until executive reporting.
When copying formulas, cell reference errors (missing $ symbols) commonly cause references to shift to A2, A3, A4, etc. Initial reviews show no problems, but as product numbers increase, you end up referencing wrong data, making it difficult to check all formulas.
Demand forecasting involves multiple departments including sales, production, and purchasing, but everyone works on different files. This creates final file confusion like "Demand_Forecast_Final.xlsx" and "Demand_Forecast_Really_Final.xlsx."
For mid-sized companies and larger, multiple departments modify forecast data, creating dozens of file versions monthly, making modification history tracking difficult, with data mismatches ultimately discovered in meetings.
When referencing cells, press F4 to lock as absolute reference ($A$2). After copying formulas, press Ctrl+` (backtick) to display formulas and sample check.
Upload files to OneDrive or Google Drive and work via shared links. Turning on change tracking features lets you see who modified which cells.
We've examined five major errors occurring in Excel-based demand forecasting. We've also provided immediate responses for each error. But honestly, these methods are temporary fixes. Excel's structural limitations cannot be completely resolved with any tips.
Even highly skilled managers make mistakes. Manually adjusting parameters, manually inputting external data, and visually finding statistical errors—as long as people do these, errors will continue occurring. The problem is these errors lead to excess inventory or shortages.
Excel demand forecasting limitations don't stem from individual capability shortfalls. These limitations actually occur because the tool serves a different purpose. Even the most capable person cannot possibly collect millions of external data points and optimize dozens of parameters manually.
AI demand forecasting systems like ImpactivAI Deepflow solve traditional Excel errors.
If you're relying solely on Excel for demand forecasting, now is the time to assess your growth stage. If SKUs exceed 300 or you need more than 10 external variables, you may already be approaching Excel's limits. It's time to move beyond worries about parameter adjustment and data collection to make more accurate decisions with AI-optimized forecasts.
.svg)
.svg)
