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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to calculate forward weeks of supply?
0
Answers
0
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
Thank you but I am more so looking for a simple formula to calculate weeks of supply. For example, I have 100 in inventory and the forecast for the next 5 months are as follows: 25,30,40,20,50. What is a formula I can enter in excel to calculate the weeks of supply that I can copy and use for a list of 200+ items, with all varying ending inventories. This is something I would want to also predict weeks of supply in the future by estimating future ending inventories.
Nicole Aug 31, '17 at 10:25 pm
So you want to calculate the reach, right? For your example the result you want would be something like 13 today, but changed tomorrow. It would be a lot easier if the forecast were made weekly instead of monthly. Is it, by any chance?
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.
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.
Variatus (rep: 4889) Aug 31, '17 at 11:02 pm