Sales Forecasting with Incomplete Data: A Practical Guide

The Real Problem: It's Not Your Data, It's Your Method

Most demand planners are waiting until they have better data before building a solid forecast. In the meantime, their inventory has been bankrolling the bad decisions that waiting produces — for months.

That's the real diagnosis. Not the technical one.

The technical one says: "my data has gaps, there are months with no sales, the system records returns incorrectly, I don't have 24 months of history." All of that may be true. But the question nobody asks is: how good does your forecast actually need to be to drive better decisions than the ones you're making today with no forecast at all?

The answer is almost always: not that good.

The problem isn't your data. It's that you're applying methods designed for clean data — complete time series, normal distributions, stationary demand — to a reality that isn't clean. When the method fails, you blame the data. The cycle repeats.

If you want to understand the broader context of why this affects your operation's profitability, the article Demand Planning Software in Mexico: Forecast or Profitability? breaks it down with concrete numbers.

When Is Data Truly Insufficient vs. When Is It an Excuse?

Data is truly insufficient when you have fewer than 8 weeks of history on a SKU, when undocumented stockouts have distorted actual sales, or when a structural business change — a new distribution zone, a supplier switch, a rebrand — makes prior data incomparable.

Everything else is a manageable excuse. Isolated gaps, one atypical month, non-seasonally adjusted data: that's not a data problem. It's a process problem.

The Three Most Common Scenarios at Mid-Size Companies

Scenario 1: Active SKU with incomplete history. You have 7 to 11 months of sales. Enough to detect a trend, not enough to capture full seasonality. Manageable with the methods in this guide.

Scenario 2: New SKU or relaunch. Fewer than 3 months of history. Statistics alone won't cut it here. You need business heuristics and the analogy method covered later.

Scenario 3: Contaminated history. You have 18 or 24 months of data, but some periods include undocumented stockouts, promotions that artificially spiked sales, or blank months caused by ERP issues. This is the scenario most planners face — and the one that causes the most paralysis — even though it has a straightforward technical solution.

---

Diagnose Your Data Before Choosing a Method

Before you touch a formula, you need to know exactly what kind of problem you're dealing with. Not all incomplete data is the same problem.

Diagnostic Map: 4 Types of Incomplete Data

• Isolated gaps: one or two months with no record in a history of 12 or more months. Solution: imputation via interpolation or median of the equivalent period.

• History shorter than 12 months: not enough coverage to capture seasonality. Solution: smoothing methods with conservative parameters plus category-level analogies.

• New SKU: zero proprietary history. Solution: analogy method using a similar SKU plus qualified judgment from the sales team.

• Contaminated data: full history but with values that don't reflect real demand (stockouts, one-off promotions, data entry errors). Solution: outlier cleaning plus stockout correction before applying any method.

How to Identify Outliers and Events That Contaminate Your History

The simplest rule of thumb: any month whose sales exceed 2.5 times the median of the history, or fall below 40% of it, deserves a manual review before entering the model.

In Excel, use =MEDIAN(range) across your history and apply those thresholds as a filter. Don't automatically delete the data point — first confirm whether there was a promotion, a stockout, or a system error. If you can explain the event, adjust the data point to what it would have been without it. If you can't explain it, use the median of the same period from the prior year as a substitute.

---

Lightweight Statistical Techniques That Work with Sparse Data

Simple Exponential Smoothing (SES): Step-by-Step Formula and Excel Example

Simple exponential smoothing is the best method when you have between 6 and 12 months of history with no clear trend or strong seasonality.

The formula: Forecast(t+1) = α × Sales(t) + (1 - α) × Forecast(t)

Where α (alpha) is the smoothing parameter between 0 and 1. An alpha of 0.2 gives more weight to cumulative history — useful for stable demand. An alpha of 0.4 reacts faster to recent changes — useful in volatile retail environments.

In Excel, you can implement it in column C, assuming sales are in column B starting at B2:

• C2 = B2 (the first forecast equals the first actual sale)

• C3 = 0.3*B2 + 0.7*C2 (drag down from there)

Start with α = 0.3 if you have no evidence of recent shifts in demand.

Weighted Moving Average: How to Give More Weight to Recent Months

The weighted moving average is more intuitive and easier to explain to the team. You take the last N periods and assign weights that sum to 1, with higher weight on the most recent.

For 3 months: 50% to the most recent month, 30% to the previous one, 20% to the oldest.

In Excel: = 0.5*B6 + 0.3*B5 + 0.2*B4

Use this method when you have between 3 and 6 months of history and want something the commercial team can validate without needing a statistics lesson.

Basic Linear Regression: When Time Actually Explains the Trend

Linear regression works when your sales show a clear trend — sustained growth or decline — not stationary demand. With 6 or more months of data you can use =LINEST() in Excel, or simply insert a trendline on a chart and enable "show equation."

The equation will give you Sales = a + b × period. That b is your growth or decline rate per period.

Honest caveat: if your sales have no linear trend — they're erratic or seasonal — regression will give you a number that looks precise but is wrong. Always check the R² before using the result.

Which Method to Use Based on Your Data Situation

• Fewer than 3 months of history: don't use statistical methods alone. Go straight to the business heuristics in the next section.

• 3 to 6 months, stable demand: weighted moving average with 3 periods.

• 6 to 12 months, no clear trend: simple exponential smoothing with α between 0.2 and 0.35.

• More than 6 months with a visible trend: linear regression plus manual seasonality adjustment where applicable.

• Full history but contaminated: clean first, then apply SES or moving average.

---

Business Heuristics When Statistics Aren't Enough

When a SKU is new or you have fewer than 3 months of real data, you need information that isn't in your ERP.

The Analogy Method: Use a Similar SKU as a Proxy

Identify a SKU that shares category, channel, price point, and purchase behavior with the new product. It doesn't have to be a perfect match — just the closest one you have.

Take its history from the first 6 months after launch and use it as a baseline. If the new SKU is priced 15% higher, apply a conservative downward correction factor. If it's distributed across more stores, apply a proportional scale factor.

The analogy won't give you an exact forecast. It gives you a credible range for making an initial purchase decision without flying blind.

How to Incorporate Sales Judgment Without Letting It Contaminate the Forecast

The sales team has real information: pipelines, ongoing negotiations, customer behavior in the field. That's valuable. The problem is they also have incentives to over-order and buffer for variability.

The technique: ask sales to estimate across three scenarios — pessimistic, base, optimistic — with a concrete rationale for each. Use the median of the three, not the average. And document the assumption behind the number so it can be audited in the next review. That way, judgment enters the model with traceability — not as a number that appeared out of nowhere.

Free External Data Sources for Market Context

• Government statistical agencies (e.g., INEGI in Mexico): retail sales by category, private consumption indicators.

• Industry retail associations (e.g., ANTAD in Mexico): organized retail sector sales reports.

• Central bank data (e.g., Banxico in Mexico): historical exchange rates, inflation by consumer category — relevant if your costs or prices have an imported component.

• Google Trends: as a relative demand signal for categories where search precedes purchase.

None of these sources replaces your sales data. But when you don't have history, they give you context to calibrate your assumptions.

---

How to Impute Missing Data Without Fabricating Reality

Linear Interpolation in Excel: Formula and When It's Valid

If you have a gap of one or two months in a relatively stable demand series, linear interpolation is valid and fast.

Excel formula to estimate the value of the missing period between two known values:

= Prior_Value + (Subsequent_Value - Prior_Value) × (missing_position / total_steps)

For a one-month gap between two values: = (B4 + B6) / 2 — simply the average of the adjacent months.

When it's not valid: when the gap falls during a major seasonal period (e.g., December, Black Friday, Easter). In those cases, use the median of the same month from prior years if available, or explicitly document that the data point is estimated and adjust your safety stock upward.

Stockout Correction: The Most Underestimated Data Issue

This is the point most planners overlook. If there was a stockout during a period, the sales recorded for that month don't reflect real demand — they reflect how much was available to sell.

Using that contaminated data point in your model understates future demand and generates the next stockout cycle.

The basic correction: if you have data on days with availability during the period, calculate the daily sales rate on days with stock and extrapolate it to the full month.

Estimated demand = (Period sales / Days with stock available) × Total days in period

If you don't have the exact days with stock, a conservative estimate is to assume the stockout lasted between 20% and 35% of the period and adjust accordingly. For more detail on how to measure the dollar impact of these stockouts, see Stockout: What It Is and How to Measure It in Dollars.

---

The Mistake That Turns an Imperfect Forecast into Dead Inventory

A bad forecast with a disciplined review process is more profitable than a "good" forecast that nobody reviews.

That statement is uncomfortable because it inverts where most people focus their energy.

How to Calculate the Real Cost of Your Current Forecast Error

Forecast error has two financial faces that are rarely calculated together:

Cost of excess inventory:

Excess inventory ($) × Annual cost of capital rate × Days of excess / 365

If you have $8M in inventory with 35 days of coverage when your target is 22, and your cost of capital is 18% annually:

$8M × 0.18 × 13/365 = $51,400 in tied-up capital for those 13 days

Cost of a stockout:

Daily sales of the SKU × Days out of stock × Gross margin

To understand how to calculate the full cost of tied-up inventory in your operation — including the real opportunity cost — see How to Calculate the Cost of Your Tied-Up Inventory. And to correctly calibrate how much safety stock you need given your forecast error level, the article How to Calculate Safety Stock walks you through the method step by step.

The Vicious Cycle: Bad Data → Bad Forecast → Bad Decision → Worse Data

Here's the systemic problem nobody names directly: when you make purchasing decisions based on a bad forecast, you generate stockouts or overstock. Those stockouts and overstock situations contaminate your sales history for the next period. The next forecast starts from even worse data.

Each iteration of the cycle makes it harder to escape — not because the data deteriorates on its own, but because today's bad decision becomes tomorrow's bad data point.

Breaking that cycle doesn't require perfect data. It requires a review process that identifies, documents, and corrects the error before it enters the next cycle.

---

When Demand Planning Software Changes the Equation

The techniques in this guide are real and applicable today in Excel. But they have a clear operational ceiling.

Signs That Excel Is No Longer Enough for Your Operation

• You have more than 300 active SKUs and the forecast update process takes more than two days per cycle.

• Your planners spend more time cleaning data and building models than analyzing results and making decisions.

• You have no traceability over which assumptions each forecast used or who approved each adjustment.

• Your average forecast error (MAPE) has gone more than two cycles without improving despite manual adjustments.

• You have multiple channels or store locations and the demand signals vary across them in ways Excel can't efficiently capture.

What Planning Software Should Do with Incomplete Data

Here's what many vendors won't tell you plainly: software doesn't work magic with bad data. What it does do is systematize the corrections you make manually in Excel — inconsistently.

A mature demand planning software should automatically detect stockouts and exclude or correct them before modeling; it should select the most appropriate statistical method per SKU based on that specific SKU's demand pattern; it should incorporate external signals like promotions, seasonality, and macroeconomic variables without requiring the planner to do it manually; and it should generate a replenishment suggestion — not just a forecast number — that accounts for the target service level, supplier lead time, and cost of capital.

The difference isn't forecast precision. It's that the system executes with discipline what in Excel depends on whether the planner has the time and judgment on that specific day.

When your operation reaches that scale, it's worth evaluating demand planning software for mid-size companies that already operates within the context of volatility, channel informality, and atypical seasonality patterns common in the region.

---

What If Your Biggest Problem Isn't the Forecast — It's What You Do with It?

There's a question that rarely gets asked in S&OP meetings: how much time do you invest in improving the forecast versus improving what happens after the forecast?

Because a forecast is only worth as much as the decision it generates.

A forecast with a 22% MAPE that produces the right purchase order — right product, right quantity, right timing — is more valuable than a forecast with an 11% MAPE that nobody executes well because the buyer adjusted the number at the last minute, the supplier delivered late, and nobody updated the system.

Supply chain maturity isn't measured by how precise your forecast is. It's measured by how well that forecast translates into product available for the customer at the moment they need it.

If you want to explore that distinction more deeply, the article What Is the Perfect Order and Why It Matters More Than the Forecast is the logical next read after this one.

The uncomfortable question I'll leave you with: if tomorrow you improved your MAPE by 8 points but changed nothing in your purchasing and replenishment process, how much would your service level actually improve? If the answer is "not much," the forecast isn't your main problem.

---

Frequently Asked Questions

How many months of history do I need at minimum to build a reliable sales forecast?

It depends on the method. With simple exponential smoothing you can work from as few as 4 to 6 months of history for demand without strong seasonality. To fully capture seasonality you need at least 13 to 15 months. If you have fewer than 3 months, statistical methods alone aren't enough — you need to supplement them with the analogy method and calibrated business judgment.

How do I build a sales forecast if my history includes stockouts?

Correct the data before applying any method. Calculate the daily sales rate during periods when stock was available and extrapolate it to the full period: Estimated demand = (Period sales / Days with stock) × Total days. Using actual sales during a stockout understates future demand and perpetuates the out-of-stock cycle.

What's the difference between a simple moving average and a weighted moving average for forecasting?

A simple moving average treats all periods in the range with equal weight. A weighted moving average assigns greater weight to more recent periods, making it more responsive to shifts in demand trends. In variable-demand environments, the weighted version generally produces better results with short histories of 3 to 5 months.

When does it make sense to move from Excel to demand planning software?

When the time spent building and updating the forecast exceeds the time spent analyzing and deciding; when you have more than 300 active SKUs with heterogeneous demand patterns; or when forecast error isn't improving despite systematic manual adjustments. The software doesn't fix bad data on its own, but it does systematize corrections and allows the process to scale without adding headcount.

What demand forecasting methods work for new SKUs with no history?

For new SKUs, the analogy method is the starting point: identify the most similar active SKU by category, price, and channel; take its behavior during the first 6 months after its own launch; and use it as a proxy with correction factors for differences in price, distribution, or positioning. Supplement with sales team scenarios (pessimistic, base, optimistic) and external sources like search trends or category reports from industry associations to contextualize the range.