Challenge #14: Warehouse Distribution

[GeneR]

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.

[TaraM]

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.

Thanks!!

[alex]

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

1. a warehouse report that provides detail by product (Total Assigned, # Store Receiving Product, Total Remaining Product, etc.)
2. a store report that provides data on total orders filled (what is going on in Houston?)
3. an item report that provides data on the items that were left unfulfilled
4. an expanded answer that includes columns for unfulfilled request amount, % assigned, etc.

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.

[Wadim]

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.

Cheers.

[ankit_mandal]

Sharing my solution here. :)

[mceleavey]

I didn't use the macro approach, instead using the running total and multi-row formula approach:

Spoiler

First I joined the data sets together on the key fields and sorted by item and priority.
I then created a running total of the "Required" column. I then amended the negative values in the running total column to be zero. Once I had this, I used a simple multi-row formula to say if the "Remaining" field was zero take the remaining figure from the row above, else take the "Required" value.

[SeanAdams]

Thank you for the challenge. 

@GeneR, @TaraM - would you mind taking a look at the macro solution provided - seems to be the solution to weekly challenge 11?

My solution attached - slightly different approach to the others:

Spoiler

Essentially there are 3 populations here:
- rows where they can be completely assigned within the stock levels available
- rows where we need partial assignment
- rows where we have nil assignment.

Approach:
-I used a running total followed by a formula to assign those products I could completely assign within stock levels.
-Then sorted filtered to show only the rows with 0 assigned 
-Then did a unique to only take the first row by priority
    Note: - by definition the first row will be partially assigned (the one returned by the unique); and all other rows for this product will be nil assigned.
    - Use a formula on the first row (the unique row) to get the partial assignment amount

Then union these 3 sets back together again.   Job done and super-fast (and no need to use multi-row formulas or macros)

[Laurap1228]

I solved this with an Iterative Macro!  I know it was possible to solve this challenge without a macro, but I wanted to practice my macro building skills.

Spoiler

My macro:

My final workflow:

[NicoleJohnson]

Second iterative macro = WAY better than the first! Although still feel a little like I'm throwing darts it. With a blindfold. :)

My solution attached, seems pretty similar to @Laurap1228's solution.

Spoiler