Can someone please help me with how to calculate a forward weeks of supply? I have monthly inventory data as well as a sales history and forecast. This is needed for hunderds of items so a simple calculation would be great. Thanks in advance.

# How to calculate forward weeks of supply?

## Answers

If you have a sales forecast you can forget all about history. The forecasters took history into account, and you aren't permitted a different view at this stage of planning. If they want to sell 1000 units then 1000 units must be available to be sold. No argument. No fault of yours if they were wrong.

The difficulty is in the difference of planning periods. The sales forecast may be for a month or a quarter but you need to come up with a weekly supply figure. It's unlikely that the weekly demand will be simply an average of the sales forecast. In some industries differences can be very, very substantial. You try to compensate for them by your buffer stock and, if possible, by looking at your replenishing cycle (the time it takes for an order to arrive). So, here you have the basics already.

You inventory at the beginning of the week should be equal to the sales forcast average for that week plus a buffer the size of which depends upon circumstances. Call this number "optimal stock level". The quantity to be ordered at the end of the week is equal to the optimal stock level minus actual stock.

If your replenishing time is longer than a week you must calculate the optimal stock level for the duration of the replenishing time and include on order quantities in the actual stock number. If you can't place weekly orders work out the required supply based on your order frequency and then divide the numbers you come up with into weeks, just as you did when converting monthly sales forecasts into weekly supply requirements.

### Discussion

Anyway, I wouldn't know how to make a formula for that. I would use a maro which calculates the reach daily when you open the workbook. It would be a pity to try to develop such an idea only to have your management reject the idea of a macro-enabled workbook. It would also be easier if you could provide a copy of your workbook for testing with actual numbers.