Demand Planning Template (Excel and Google Sheets)

This page lays out the structure of a demand planning Excel template that also works in Google Sheets, so you can build your own in an afternoon. It describes the tabs, the columns on each, the formulas that tie them together and a checked example. It is not a downloadable file, and nothing here depends on special software. It belongs to the wider toolkit for demand planning and inventory optimization. For the method behind the forecast see demand forecasting in Excel, and for the policy calculations see the safety stock formula. The follow-on guides are inventory management software and inventory forecasting software, and the finance context is in cash flow management. This demand planning Excel template is a starting point, and you should adapt it to your products, systems and process.

Tab 1: README and inputs

Keep one tab for instructions and settings. List what each tab does, who updates it, how often, and the date of the last update. Put the settings in labeled cells that other sheets reference: the forecast method parameter (for example, the number of months in the moving average), the service-level z-value, the review period in weeks, the carrying cost rate and the threshold for flagging exceptions. Do not type these numbers into formulas.

Tab 2: History

One row per item and month, or one row per item with months across the top. Columns: Item, Description, Category, then one column per month of units sold. Add a Notes column for stockouts, promotions or one-off orders. Keep this tab as raw data pasted or imported from your system, and do not edit it by hand.

Tab 3: Forecast

One row per item. Columns: Item, Average of last 3 months, Average of last 12 months, Baseline forecast, Adjustment (units), Adjustment reason, Final forecast, Last year same month, Forecast vs last year (percent). The baseline can be a formula such as =AVERAGE() over the last three history cells. The adjustment cell is the only place where judgment enters, and the reason must be filled in whenever it is non-zero. The final forecast is the baseline plus the adjustment.

Tab 4: Inventory policy

One row per item. Columns: Item, Weekly demand (from the forecast), Standard deviation of weekly demand, Lead time (weeks), Review period (weeks), Service-level z, Safety stock, Reorder point, Order-up-to level, On hand, On order, Inventory position, Order quantity suggested.

Formulas, in words: safety stock equals z times the standard deviation times the square root of (lead time plus review period). The reorder point equals weekly demand times lead time plus safety stock. The inventory position equals on hand plus on order. The suggested order is the order-up-to level minus the inventory position when the position is below the reorder point, otherwise zero.

A checked example: weekly demand 120 units, standard deviation 30, lead time 4 weeks, review period 0 (continuous review), z of 1.65 (illustrative). Safety stock is 1.65 × 30 × the square root of 4 = 1.65 × 30 × 2 = 99 units. The reorder point is 120 × 4 + 99 = 480 + 99 = 579 units. With 540 on hand and 100 on order, the position is 640, above 579, so no order is suggested. If on hand fell to 420, the position would be 520, below 579, and an order would be triggered.

Tab 5: Exceptions

A filtered list that shows only items needing attention. Flag an item when the final forecast differs from last year's or the last cycle's by more than the threshold on the inputs tab, when the inventory position is below the reorder point, when cover is below the lead time, or when stock has not moved for a set number of months. Use conditional formatting and a simple count of flags at the top.

Tab 6: Accuracy

Store each month's final forecast and compare with actuals once they are known. Columns: Item, Month, Forecast, Actual, Error, Absolute percentage error. Summarize with the average error for bias and the average absolute percentage error for accuracy. Without this tab, the template cannot improve.

Tab 7: Summary

A single page for leadership: total forecast by category versus last year, inventory value and days of supply, number of items below reorder point, number of exceptions, forecast accuracy and bias. Keep it to what decisions need.

Rules that keep it working

Protect formula cells and leave input cells open. Use named ranges or Tables so that formulas expand when rows are added. Add a check cell on each tab that confirms totals tie to the source. Save a dated copy at each month-end. Record who changed what. If several people need to edit at the same time, use a shared workbook in a cloud service with version history, or move the data to a database.

Want a working version?

If you would like a working version built around your items, systems and planning cycle, with the formulas tested on your data, contact us and describe what you plan today. We can scope a lightweight build or a review of the one you already have.