A purchase plan: from forecast to order quantity

Purchase plans are often drawn up on the rule "we buy as much as we bought last month". When demand changes, this gives either a shortage or surplus stock. A good plan is calculated from a demand forecast, the lead time, and the goods on the shelf and on the way. The result is not an approximate but an exact order quantity for each item.

The formula

Order = daily demand × (lead time + period between orders) + safety stock − stock on hand − orders in transit.

If the result is negative, the order is zero. If positive, round it to the pack size (carton, pallet). The period between orders is how often you review the plan: every week or every two weeks.

Each term has a purpose. Daily demand, lead time and period together determine how many days the stock must last. Safety stock covers the forecast error. Goods in the warehouse and on the way already exist and need not be bought a second time. Forget any one of these parts and the plan falls apart.

An example for one item

Over the last three months sales were 420, 480 and 450 units. The average is 450, or 15 units a day. Delivery takes 10 days and we order every 14 days: demand over 24 days is 15 × 24 = 360 units. Safety stock equals six days of demand, that is 90 units. There are 140 units in the warehouse and another 100 on the way.

Order = 360 + 90 − 140 − 100 = 210 units. With packs of 24, 210 ÷ 24 = 8.75, so we order 9 packs, or 216 units.

Adjusting the forecast

• Take into account the days when the item was unavailable. If 450 units were sold last month but the item was out of stock for 6 days, real demand is 450 ÷ 24 = 18.75 units a day. Use 15 and you will order too little every time, and the item will run out again.
• Remove one-off large orders, such as a tender or one big customer, from the average.
• Add seasonality and promotions. If a holiday is near and past years showed demand rising by 20 percent, we get 360 × 1.2 = 432. Then the order is 432 + 90 − 140 − 100 = 282, which is 12 packs (288 units).
• Check the accuracy of the balance. If the recorded 140 units are really 110, the order will be 30 units too small. Even with the right formula, a wrong balance spoils the result.

A routine for the whole range

In Excel, open one row per item: daily demand, lead time, period, safety stock, stock on hand, orders in transit, pack size and calculated order. Vary the review frequency by ABC: A items weekly, B every two weeks, C monthly, in large batches. Compare the total of all orders with the money available. If money falls short, secure the A items first. Also take into account the supplier's minimum order and delivery days: combining several items from one supplier into a single order cuts transport cost, but some items may arrive above plan. Deduct the excess for such items from the next order.

Track the forecast error

Each month compare the forecast with actual sales: error = |actual − forecast| ÷ actual. With a forecast of 360 and actual sales of 395, the error is 35 ÷ 395 = 8.9 percent. If the error always leans one way (below actual every time), the problem is not in the formula but in the input data or the safety stock. For items with a large error, raise the stock or manage them separately as group Z in the XYZ analysis. Calculate the error separately for A items, because their error affects money the most. One table is enough to show a manager: forecast, actual sales and the error percentage.

Conclusion

A purchase plan rests on three things: correct demand, a correct balance and orders in transit that are counted. CountLab confirms balances through stock counts, uses ABC/XYZ analysis to single out the items that need more attention, and gathers the figures a plan needs in the management report.