Hey there,
I need to create a template for less experienced excel users that will help our managers better manage purchasing. As an end product, I need to calculate weeks of supply, which requires a minimum of 12 weeks of inventory/sales data per item. The idea is that they will download a sales report and an inventory report (separately) every week and drop that in sales and inventory report data dump tabs - the output page will update with as much automation as possible. I'm not sure how to build this out, considering we are bringing in new, unique inventory items on a weekly basis and want to display only those items with a minimum of 12 weeks of historical inventory data, or, 12 unique instances of that item being dropped in the data tab.
So I was hoping to get any and all suggestions on the best way to build out an output tab that spits out only those items that have been in inventory for minimum of 12 weeks, based on someone dropping in new inventory data every week, whether that be in one data dump tab (ideal) or a separate tab for each week.
Again, please note this will be for managers that have very limited experience with excel, so the hope is that they can download inventory, copy, paste and output page displays the updated information (well, as close to that as possible).
Thanks!
what are your thoughts on:
1) create a data dump tab with a column for hardcoded date that corresponds with that weekly inventory data
2) on the output tab either extract through formula/vba or drop in unique values (can train them to just remove duplicates)
3) have a column that counts unique instances of an item appearing in data dump tab
4) have that column set to filter greater than 12
5) pull in all the other data needed for formula through sumifs, index/match, etc (need rolling 12 weeks sales data to pull in - can just use reference dates)
Bookmarks