Here is a new challenge for this week, it is a two part challenge so next week’s challenge will be a continuation. The link to the solution for last week’s challenge is HERE.
This week we are looking at a retail distribution analysis. We need to allocate products from the warehouse to stores based on priority. I have seen this challenge solved both with and without the use of an iterative macro.
The use case:
A retail chain has 25 stores carrying variety of items. Not every store carries the same items and each has its own level of prioritization within the chain and different required stock levels. There is a central warehouse that contains all of the available items.
The objective is to distribute items from the warehouse to each store, filling the available stock at each store in order of the store's priority.
Good luck, I look forward to hearing your feedback. Thank you for playing along.
It's leap day! Two solutions have been uploaded.
Two solutions for this one.
@GeneR says : The first is a solution without a macro and is in my opinion a more straight forward approach to solving the problem. I included the second macro approach because it is an excellent example of how to utilize an iterative macro.
I selected the iterator method and I also went back and put my old CFO hat on for this one. I had a bit of extra time so I went ahead and added branches to the workflow to calculate other stats that I would need to know if I ran the warehouse.
But first, since there were 215 rows to compare, I added a branch that automated the process to let me know if there was a row in my results that didn’t match the answer that was posted.
Then, I created
The warehouse is totally out of 4 products and item 3 has the most stock remaining. Was too much of item 3 ordered or does it have a long lag time to receive product? The largest deficit was with items 6 and 9. Items 9, 1 and 3 were requested by the fewest stores while items 2 and 7 were requested by all but 1 location for each item.
All the extra data was created using just a few sort, join, filter, formula and summarize tools and goes a long way to provided value to the data.
Yea nice challenge, but you uploaded a completly wrong file, "challenge_11_solution_2.yxzp 26535 KB" is a solution for a complete different challenge.
Don't get me wrong, I like those challenges but there is no challenge without misstakes.
This is really grinding my gears, I would like to see a iterative solution.
I didn't use the macro approach, instead using the running total and multi-row formula approach:
Thank you for the challenge.
My solution attached - slightly different approach to the others: