Complex supply chain question

[excelguylondon]

I am trying to create a network optimizer that will tell me which warehouse shall I keep my goods.

Factories --> F1,F2,F3,F4,F5,F6,F7
Warehouses --> W1,W2,W3,W4,W5,W6,W7
Destination --> D1,D2,D3,D4,D5,D6,D7

Now there are 3 modes of transportation -- T1,T2,T3. There are costs associated with each of the modes of transportation and each warehouse will have some warehouse storage charges.

The user will choose the Factory and Destination. My excel should populate what warehouse and what transportation will be most efficient for the transport of goods.

Any suggestions on how do I do this?

 

[Gerald Higgins]

Hi, welcome to the board.

I don't think this is really an Excel question, it's more of a maths question.

Forgetting about Excel for a minute, do you know how to do the calculation of which is the best option(s) to select ?
If YES, please describe that calculation, and someone here may be able to build an Excel formula or function that does that.
If you DON'T know how to do it, I suggest you do some research on this first.

 

[AhoyNC]

Maybe the resources listed below will be of help.

Transportation Problem in Excel - EASY Excel Tutorial
https://www.utdallas.edu/~scniu/OPRE-6201/documents/TP1-Formulation.pdf
https://www.youtube.com/watch?v=BuGbHaAfKuo
http://personal.maths.surrey.ac.uk/st/J.F/chapter7.pdf