Excel decision making

**wrigh2uk** · 08-29-2012, 04:34 AM

Hi Guys

I need some help in conquering something I've been working on for a while. I'm in the process of creating an ordering spreadsheet for my business. To simplify things my business owns various warehouses and branches, so essentially we source to ourselves. We have multiple warehouse's and this is where my problem arises. There is a rule when a branch requests stock from a warehouse.

Example

Branch A : Needs 100 Qty of product X

Warehouse1: has 70 Qty of X
Warehouse2 has 50 Qty of X
Warehouse3 has 100 Qty of X
Warehouse4 Has 60 Qty of X

I need a solution that suggests using the 70 units from Warehouse1 and using Warehouse2 to fill the deficiency to make 100 units total. The logic must also indicate how many units to take from the warehouse. In this case it would be the whole stock from warehouse1 and a partial amount warehouse2. The stock is based on free stock so there won't be anything in transit etc.

The logic gets more complicated when meeting a branch request goes through a logical process like below.

If WH1 can meet the need of the branch = ok
If WH2 can meet the need of the branch = ok
If WH1 combined with W2 can meet the need of the branch = ok
If WH1 combined with W3 can meet the need of the branch = ok

etc

I think you can see where this is going.

Only 2 warehouse's in total can be used to meet the branch requests. WH1 and WH2 must be given every chance meet the request before the logic decides only WH3 or/and WH4 can meet the branches request.

My spreadsheet is in line format so I could have 50 products in column A, the level of stock I need to order in column B, the warehouse levels in columns C-F and the last columns G-J. each column in G-J would represent a warehouse and qty to request from this warehouse. If the complete request could not be fulfilled then G-J columns would show blank.

Any light shed on this would be greatly appreciated.

**Alf** · 08-29-2012, 01:36 PM

Excel has an add-in tool called solver that can be used for decision making. Perhaps you could use that?

Alf

**tigeravatar** · 08-29-2012, 01:58 PM

wrigh2uk,

Welcome to the forum!
So in your example, if Warehouse 2 only had 20 of X, then you would only want results from WH1 and WH3 and ignore WH2 completely?

**shg** · 08-29-2012, 02:00 PM

Posting a workbook with one or more worked examples is likely to get you faster assistance.

**wrigh2uk** · 08-30-2012, 04:07 AM

Hi guys thanks for your replies.

Alf - I did take a look into Solver but it doesn't seem like it would be that best function for solving mulitiple decisions ? Maybe I'm wrong. I've now edited my intial post to include a simple version of my ordering sheet. Perhaps you could take a look just to see if it's something solver would be useful for.

tigeravatar - If warehouse 2 had 20 of product X I would use WH1 and WH3 most definitely. I may have described it different intially in my first post, apologies. As the warehouses are in different locations we ideally are trying to lower haulage cost by making less deliveries. The reason WH1 and WH2 are the primary warehouse's are because they are closet to the main branch. However if in the scenario you just mentioned where it is alot more logical to use WH1 and WH3 instead of using WH1, WH2 and WH3, the order form would suggest using WH1 and WH3. The tricky part is, if you can combine WH1 & WH2 to fulfill an order but you could fill that order by using either WH3 or WH4 individually then you would use WH1 and WH2. WH1 & WH2 are so close to the branch in comparision to the other warehouse's that the cost is cheaper in this scenario

Shg- I have now attached my workbook to my inital post

Thanks everyone

**tigeravatar** · 08-30-2012, 12:46 PM

wrigh2uk,

Attached is a modified version of your provided workbook. It ended up being rather complicated because the actual values are decimals and you have them formatted as number to display no digits, so the rounding can make the results look weird in a couple of rows (for example, if you still need 0.3 of something from WH2, WH2 will display 0 instead of blank, because you need more than 0, but not enough to make it a 1).

Anyway, it uses two helper columns in AG and AH
In AG4 and copied down is this formula:

Please Login or Register  to view this content.

In AH4 and copied down is this formula:

Please Login or Register  to view this content.

And then the monster. In AC4 and copied over and down to AF263 is this formula:

Please Login or Register  to view this content.

I manually checked several rows and they all produced correct results* (see rounding note above).

**wrigh2uk** · 08-31-2012, 03:34 AM

tigeravatar, you my friend are a genius. I never even considered that type of approach. If you have a moment could you explain to me the solution particular the 'monster' formula, looked over it a few times, lost

Thanks alot

**tigeravatar** · 08-31-2012, 10:49 AM

wrigh2uk,

The first helper formula returns either a blank (all warehouses unable to meet the demand, or there was no demand), or a number 1 through 4. This number represents how many warehouses are needed to meed the demand.

The second helper formula returns either a blank (for same reasons listed prior) or one of the three following: WH1 (because WH1 can meet the demand by itself), WH2 (because WH2 can meet the demand by itself), or WH1&2 (because together they can meet the demand).

The monster formula uses the information from the two helpers. It first looks to see if the demand can be met (makes sure the helpers aren't blank). If the demand can't be met, then all warehouses will return a blank. Next it checks if the 2nd helper is blank. If that helper is blank, it means that WH1 and 2 can't meet the demand, so it uses the first helper column to determine how many warehouses are needed, and then uses the warehouse amounts in columns Y:AB to determine which warehouses have the most inventory and then uses those warehouses to meet the demand. If the 2nd helper is not blank though, it will only focus on WH1 & 2 to meet the demand as appropriate.

That's the formula in a nutshell without getting into specifics. Specifics would be a pretty massive wall of text that frankly, I don't really feel like typing out, lol. The basic description should be enough combined with the Excel Help for each individual function used (like Max, Large, Index, etc). That being said, I do realize it is a monster formula and not a simple one at that. Anyways, I hope that helps