Weeks of inventory

[millertime33]

I'm looking to determine the Weeks on Hand (WOH) of inventory based on Quantity on hand (D2) and the forecasts(G2:R2). I don't want to just take an average of forecasts, but I need to take into account that the actual run-out will be sooner than the 'average' run-out due to high seasonality in the summer months. Make sense? Can someone offer some guidance please? Thanks!


A B C D E F G H I J K L
[TABLE="width: 1185"]
<colgroup><col><col><col><col><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD]1 Item[/TD]
[TD]Description[/TD]
[TD]Type[/TD]
[TD]Qnty on hand[/TD]
[TD]MOH[/TD]
[TD]MOH&MOO[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2 12345[/TD]
[TD]wowsers[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[WarPigl3t]

I see you a trying to develop an inventory management database with ordering by forecast ability. I created one before for a Shell gas station. I don't want to go through the trouble of making it a shared document if you are not interested, so let me know if you want it. With this excel file, you would need to inventory every time you get new stock. The first time you use it you would need to inventory once right after a shipment of merchandise came in and once more a few days after to get an average forecast for a few days. You can manually change the forecast if you choose also. I would recommend doing this once every 3 months at least to get a good forecast. When it comes to holidays, you would simply need to take the forecasted quantity and add the percent of increase expected to get a holiday forecast. Its really easy to use. I made it user friendly because the guy I designed it for is not excel savy. Lemme know if you want it.

 

[millertime33]

I'd be happy to see what you have created! Thank you!

 

[WarPigl3t]

https://www.filesanywhere.com/fs/v.aspx?v=8b70658d5966737ab398