How to forecast inventories as an FP&A pro so that you balance working capital and growth

In this guide I walk you step‑by‑step through practical inventory forecasting techniques you can apply today using Excel.

If you work in FP&A or run a small retail business, these methods will help reduce stockouts, avoid excess inventory, and free up cash tied to slow‑moving SKUs.

Why forecasting fails (and how to fix it first)

Forecasting problems usually come from messy data and not from bad software. Forecasting errors of 30% are common when your spreadsheet contains duplicates, missed promotion flags, or inconsistent date periods.

Before you run any forecast, do this three‑step cleanup:

Gather 6–12 months of sales history for each SKU.
Remove or adjust one‑time spikes (bulk orders, giveaways, data errors).
Convert all data to consistent periods (weekly or monthly).

Clean data converts noisy sales numbers into a reliable trend—the starting point for every forecast.

Method 1: Moving average (simple, explainable)

Use moving averages for steady sellers. It smooths random fluctuations and is easy to explain to stakeholders.

Example: January = 100, February = 120, March = 110.

A 3‑month moving average = (100 + 120 + 110) / 3 = 110 units.

In Excel use the AVERAGE formula.

Pro: simple and stable. Con: it lags sudden changes and misses seasonality.

Method 2: Exponential smoothing (more responsive)

When demand shifts month‑to‑month, exponential smoothing reacts faster by giving more weight to recent observations.

Choose a smoothing factor alpha (0–1). A higher alpha makes the forecast more responsive; a lower alpha makes it smoother.

Conceptually: forecast ≈ (weight_recent × most_recent_sales) + (weight_previous × previous_sales).

For example, with alpha = 0.2 you place more weight on the latest data and less on older points.

Use exponential smoothing for SKUs that show gradual shifts or short‑term trends where you want the forecast to adapt quickly.

Method 3: ABC analysis (prioritize your effort)

If you stock dozens or hundreds of SKUs, not every item deserves the same forecasting effort. ABC analysis ranks SKUs by annual consumption value (units × unit cost), sorted from largest to smallest.

Category A: top ~70–80% of value. Tight forecasting (weekly), possibly exponential smoothing.
Category B: next ~15–25%. Moderate attention (monthly/quarterly).
Category C: remaining. Simple methods and infrequent reviews.

In my sample data the top 4–5 SKUs account for ~89% of annual value. They get prioritized forecasting resources.

Method 4: Seasonality index (for predictable peaks)

For seasonal items (e.g., air conditioners), adjust forecasts with a seasonality index. Steps:

Compute monthly averages for each month across multiple years (e.g., average of all Januarys).
Compute the overall average across all months and years.
Seasonality index for a month = (month_specific_average) / (overall_average).
Forecast for a month = baseline average units × seasonality index for that month.

Interpretation:

Index = 1 is average;

>1 means above average demand (summer for ACs),

<1 means below average.

Safety stock, lead time and reorder point

Forecasting tells you expected demand. But you must plan for uncertainty. Use safety stock to guard against demand spikes and supplier delays.

Safety stock formula (one practical form shown here):

Safety stock = (maximum daily sales × maximum lead time) − (average daily sales × average lead time).

Example:

Average daily sales = 15 units, maximum daily sales = 18 units, lead time = 10 days.

Safety stock = (18 × 10) − (15 × 10) = 30 units.

Reorder point = (average daily sales × lead time) + safety stock = (15 × 10) + 30 = 180 units.

Also account for supplier constraints: minimum order quantities, shipping schedules, production caps, and cadence (weekly/monthly deliveries).

Connecting sales → production → inventory

Your inventory forecast is a node in a chain:

Sales forecast drives what you plan to sell.
Production forecast schedules what gets made.
Inventory forecasting balances the two so the right products are in the right place at the right time.

If sales forecasts are too high you overproduce and tie up cash. If too low you underproduce and lose sales. The goal is to be close enough to avoid chronic overstocks or stockouts.

Your FP&A inventory forecasting playbook

Start with clean data (6–12 months per SKU).
Use moving averages for steady sellers.
Upgrade to exponential smoothing for shifting demand.
Layer in ABC analysis to focus effort where it matters.
Apply seasonality index for predictable peaks.
Always calculate safety stock, lead times, and reorder points.

Join the Finance Alliance community to network with other CFOs and finance leaders, get answers to all your questions, share insights, and grow your connections.