+ Reply to Thread
Results 1 to 2 of 2

Automating a Weekly Changing Inventory File

  1. #1
    Registered User
    Join Date
    02-22-2018
    Location
    New York, NY
    MS-Off Ver
    Office 2010
    Posts
    1

    Lightbulb Automating a Weekly Changing Inventory File

    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)
    Last edited by cpnorton3; 02-22-2018 at 01:48 PM. Reason: New idea

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Automating a Weekly Changing Inventory File

    not really sure what your output will look like
    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
    how many SKU's are you talking about

    i find after 10 it starts melding together and page just gets too long (even if you use hyperlinks to bounce around the sheet)

    do you require comparisons between products or just view one product at time?
    ie have a drop down list to select product and file/template for product shows up

    Weeks of supply (WOS) is generally based on forecasts which generally derived by sales history as well as future orders
    not sure about you but if i order something it doesnt just appear next day...lead time for suppliers need to be inserted and in turn safety stock

    just curious if this is all this included into the thought process?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dashboard for weekly sales and inventory
    By mdjaskolka in forum Excel General
    Replies: 5
    Last Post: 04-07-2016, 03:01 PM
  2. Inventory and Weekly sales worksheet
    By hypervenom in forum Excel General
    Replies: 16
    Last Post: 05-26-2015, 03:03 AM
  3. How to open a changing file (weekly)
    By rommel08 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-10-2012, 09:34 AM
  4. Summarize weekly Inventory
    By top1 in forum Excel General
    Replies: 9
    Last Post: 02-08-2010, 07:30 PM
  5. Automating weekly formula changes
    By scaffdog845 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-15-2009, 09:03 AM
  6. Automating a weekly updated database in excel
    By jumpshoot16 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-17-2009, 01:50 PM
  7. Help - Automating a file.. Adding Users , Deleting users, Changing
    By Tim Harding in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-16-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1