How to Build a Clean Data Foundation for Planning

A planning data foundation is the set of clean, consistent, owned data that every forecast, stock target and supply plan rests on, and building one is usually the most valuable thing a planning team can do before buying a tool or hiring a data scientist. Forecasts built on history that includes stockouts, returns and one-off orders will be biased. Safety stock built on lead times that nobody has checked in two years will be wrong. Purchase suggestions built on units of measure that do not match the supplier's will be confusing at best. This guide explains what data planning needs, how to clean and structure it, which quality checks to run, how a bad lead time distorts safety stock in a checked example, how to assign ownership and how to keep the data clean over time. It is part of demand planning and inventory optimization, follows inventory forecasting software and inventory management software, leads to demand planning software and demand forecasting in Excel, and builds on data quality, single source of truth and data governance. Finance context is in cash flow management.

The six datasets planning needs

Item master. One record per item: identifier, description, category, unit of measure, pack size, status (active, new, discontinued), cost, price, weight and dimensions, shelf life and planning parameters such as minimum order quantity and order multiple.

Location master. Warehouses, stores and channels with identifiers, type, region and any capacity or constraints.

Supplier and lead time data. Suppliers by item, stated lead times, minimum quantities, transport modes, calendars and reliability.

Sales history. Units sold by item, location, channel and customer, by day or week, with price and promotion flags, and with returns shown separately.

Inventory and orders. On-hand and available stock by location, snapshots over time, open purchase orders with expected dates, open sales orders and transfers.

Events and calendar. Promotions, launches, price changes, holidays, closures, stockout periods, supply disruptions and any other event that affects history or the forecast.

Cleaning the sales history

Raw sales history is not demand. It reflects what customers were able to buy at the prices and promotions in place. Several adjustments are common. Remove or net out returns so that they do not distort the series. Flag periods of stockout, where sales understate demand, and estimate what would have sold from similar periods or nearby weeks. Flag promotion periods so that models do not treat them as the baseline. Identify one-off orders, such as a large project sale, and decide whether they belong in the forecast. Handle bundles and kits consistently, by recording demand at the component level where needed. Combine items that were replaced by successors so that history follows the product. Document every adjustment and keep the raw data alongside the adjusted data.

Master data checks

A short set of checks catches most problems. Completeness: is every active item populated for the fields planning needs? Validity: are units of measure, costs and lead times in plausible ranges? Consistency: do items have the same identifier in the sales, inventory and purchasing systems? Uniqueness: are there duplicates? Timeliness: when was each field last confirmed? Plausibility: are there items with zero cost, negative stock or lead times of zero days? Report each check as a percentage of items passing, track it monthly and assign someone to fix failures. For example, if lead times are populated for 4,600 of 5,000 active items, completeness is 4,600 / 5,000 = 92 percent, and the 400 missing items default to some system value that may be wrong.

A lead time example

Lead time has a direct effect on safety stock and reorder points, so a wrong value spreads. Suppose the stated lead time in the system for an item is 21 days, but actual receipts over the past year averaged 28 days (illustrative). The stated figure understates actual lead time by 7 days, or 7 / 21 = 33 percent. Safety stock depends on the square root of lead time, and the ratio of the correct to the stated value is the square root of 28 / 21, which is the square root of 1.333, or 1.155. Safety stock calculated with the stated lead time is therefore about 15 percent too low. Check: 28 / 21 = 1.3333, and the square root of 1.3333 is 1.1547. The reorder point, which includes demand during lead time, is understated by more. If daily demand is 100 units, demand during lead time is 2,100 units on the stated figure and 2,800 on the actual, a gap of 700 units. Check: 100 × 21 = 2,100, and 100 × 28 = 2,800. Every order triggered on the stated reorder point arrives about 7 days after the stock has run low, which shows up as stockouts that the forecast gets blamed for. Compare stated and actual lead times for the top items by value every quarter, and update the master data when they differ.

Reconciling sources

Planning data usually comes from several systems: the ERP, the ecommerce platform, the warehouse system, spreadsheets and customer portals. Agree which system is the source of truth for each field, and build the planning dataset from those sources, not from ad hoc copies. Reconcile totals regularly: units sold in the planning data against the finance ledger, inventory in the planning data against the warehouse count, open orders against purchasing. Differences should be investigated and explained. See single source of truth.

Ownership and rules

Data quality improves when someone owns it. Assign a data owner for each dataset, for example purchasing for supplier data and lead times, product management for the item master, finance for costs, and planning for events and history adjustments. Write down the rules: who can create or change an item, which fields are mandatory, how changes are approved and how quickly new items must be set up. Give planners a simple way to report errors and get them fixed. Track the time to fix. See data governance.

A simple pipeline

Whether you use a spreadsheet, a database or a data platform, the pattern is the same. Extract the data from the source systems on a schedule. Load it into a staging area unchanged. Run the quality checks and stop or flag if they fail. Apply the cleaning rules to produce the planning dataset. Store each month's snapshot so that you can reproduce past forecasts. Publish the dataset to the tools that use it. Log each run. Even a small team can run this with scripts and a shared folder, provided the steps are written down and repeatable. See analytics automation: what to automate first.

Monitoring over time

A foundation decays without maintenance. Track a handful of quality measures each month: completeness of key fields, the share of items with lead times verified in the past year, the number of duplicate or inactive items still in the plan, the reconciliation differences between systems and the number of manual corrections to history. Add a review at each planning cycle of the largest data issues found. When a forecast or inventory problem occurs, ask whether the data contributed, and fix the root cause. See data quality.

Quick wins

Start with the items that matter most. Verify the master data for the top 20 percent of items by value, which often covers most of the sales. Compare stated and actual lead times for those items and correct them. Flag stockout periods in the history for the same items. Remove duplicates and discontinued items from the active list. Fix units of measure mismatches between suppliers and the system. These steps take days, not months, and usually improve forecasts and policies noticeably.

Common mistakes

Treating sales history as demand without adjusting for stockouts and promotions biases forecasts. Leaving lead times unchanged for years distorts safety stock. Building the dataset by hand each month creates errors and delays. Having no owner for the data means nobody fixes it. Assuming a new tool will clean the data wastes the investment. Overwriting raw data with adjusted data removes the ability to audit.

FAQs

What data do I need for demand planning?

Item and location master data, supplier and lead time data, sales history, inventory and orders, and a calendar of events.

How do I clean sales history for forecasting?

Net out returns, flag stockouts and promotions, handle one-off orders and product replacements, and keep raw and adjusted data separate.

Why do lead times matter so much?

Safety stock and reorder points depend on them. A lead time that is too short leads to orders placed late and stockouts.

Who should own planning data?

Each dataset needs a named owner, with written rules for changes, and planning should have a simple route to report and track errors.

How often should I check data quality?

Run automated checks on every data load and review quality measures monthly.