Weeks of Supply Formula

[slash32487]

Im trying to figure out a formula for a weeks of supply report.

data i have.
Last weeks sales units
Last 13 weeks sales units
Sales units year to Date
Shipment Average units last 13 weeks
Total units shipped last 13 weeks
On hand units
Work in progress units

anyone have any recommendations

 

[JonXL]

What information do you want to grab from the reports and can you give some examples of the format your data are in?

 

[slash32487]

looking to generate a weeks of supply report base off sales and shipments

 

[JonXL]

What is the current method you are using to calculate WOS manually?

We can convert that to an Excel formula.

 

[Qroozn]

The bit you are missing here is your Future requirements. (Open Order Bank).
I would suggest you work out the Average Weekly Usage based on 13 weeks of usage (known), and then divid your SOH by this .

SOH weeks = SOH / Average Weekly Usage based on 13 weeks of usage (known).
SOO (stock on order) weeks = (SOH+WIP - SOH) / Average Weekly Usage based on 13 weeks of usage (known).

I am curious why your WIP and SOH are the same.. does then mean you have no WIP?
reporting wise you may choose to either include the WIP in your SOH Weeks , or not, or perhaps half of it.

 

[slash32487]

in order to patch it for now how what would be the formula for the following.

weekly average bought averaged with weekly average purchased = 1 week. assume that number is 10 and i have 100 units in stock then in the wos column it should say 10.

right now what i did was create a new column average out bought and sold. then for weeks of supply i divided by the average number.

not sure if that makes any sense but i need to submit it today. i would like to fix it by next week

 

[slash32487]

i saw this thread. im trying to understand the code to adopt it to my sheet. can someone walk me through it

https://www.mrexcel.com/forum/excel-questions/504568-formula-calcing-weeks-supply.html