Excel & Frontline Solver - SUMPRODUCT function (Network Optimization)

Network Optimization Problems

a. 

The manufacturer shipped 10 thousand D2 and 30 thousand D5 from F1, 30 thousand D1 and 18 thousand D5 from F2, 14 thousand D2 and 36 thousand from F3, and 42 D3 from F4.

b. 

The manufacturer spend 2,660 thousand on shipping goods.

c.

From Factory  D1  D2  D3  D4  D5  Total

F1                     0    11    0     0   29    40

F2                   30     0     0     0   19    49

F3                     0    13    0   37     0    50

F4                     0     0   41     0     0    41

Demand         30     24  41   37   48 

New Optimal Cost = 30 X 12 + 11 X 16 + 13 X 17 + 41 X 14 + 36 X 15 + 30 X 15 + 18 X 18 = 2663

        According the results at previous questions, the manufacturer should ship rollls from F1 to D1, from F1 and F3 to D2, from F4 to D3, from F3 to D4, and from F1 and F2 to D5. Then, question c want to change the result of D3 and D4. About shipping fee, shipping D4 from F1, F2, and F4 is more expensive than D3., and shipping rolls from F3 is most cheapest. Thus, the manufactory have to consider to ship more 1 thousands rolls form D4 by F3.  The capacity of F3 is 50 thousand, so  D2 have to reduce 1 thousand for F3 and then increas 1 thousand from F1. The capacity of F1 is 40 thousands, so D5 have to reduce 1 thousand from F1 and then increase 1 thousand from F2. The capacity of F2 is 50 thousands, so there is okay that ship 19 thousand rolls to D5 and 30 thousand rolls to D1. About D3, the manufacturer just send 41 thousand from F4. Finally, they can save mony and have minimum-cost: the new optimal cost is 2663 thousand.

Reference:
[1] Powell, Stephen G. Management Science: The Art Of Modeling With Spreadsheets, 4Th Edition. 1st ed. John Wiley & Sons, 2013. Print.