I am trying to create a template so that we can assisted our different departments in ordering the right quantity based on historical sales. This is the method that we have come up with and I am having trouble with coming up with the correct formula or macro give us the final result. I will also be attaching an example so you can follow along with what I am trying to accomplish.
Essentially all items have a Department, Sub Department, Category and Sub Category. What we have done is taken the average quantities sold of an item over the previous 4 weeks by day. What we also have is the average quantities sold of all items under every Department, Sub Department, Category and Sub Category. Now what we would like to determine is, the item most closely matches to either the department, sub department, category or sub category shape of week (items sold on average per day). Once we determine which it most closely matches than I can apply the same proportional percentages of that department, sub department, category and sub category to a simple moving average forecast.
The toughest task is determining whether to use a macro or a formula that would closely match an items average relative to either four of the items attributes with the lowest standard deviations.
Thank you for all your help it is greatly appreciated.
Bookmarks