close

網路模型

利用Excel求解網路模型,有「一箭穿心」的樂趣,因為,它穿過蜿蜒曲折網路,從交相連結的網路路徑,撥出層層疊疊雲霧,找到簡單直接明確路徑,體驗到魔術般、不可思議的Excel運算。

360px-Ba_model_1000nodes.png

由於Excel的網路求解模型太「輕快」了,這種不可思議反而讓人對它的建置過程感覺抽象、難解,本文稍後會詳細解說這一建置過程,包括:

1)如何把複雜的網路節點排列成T×4矩陣」

2如何為節點的流出或流入設定正確限制值

3)如何叫出Excel附加軟體Solver(規劃求解)進行設定

下例是Practical Management Science Winston and Albright, 2018)第241頁的例子。如圖11所示,網路總共有7個節點123節點工廠45倉儲中心67為市場經銷商圖中123工廠節點標示S的數據是工廠的產能(合計600噸45倉儲中心的節點標示T=0表示不留庫存67經銷商的節點標示D的數據市場需求量,也是產品最後要運到市場的數量(580噸)。

圖11

D2.png

讀者可以將上述例子想像是台商在海外布局的經營模式,12是台商在大陸的工廠,3是台灣的生產工廠,兩個倉儲中心45,一個在美西、另一個在美東, 67是在美國的兩個經銷商據點。台商如何將123三個工廠生產的產品以最便宜的運送成本送到(美國)6、7兩個經銷據點呢??

如上一篇文章(採購標案)提到的,網路模型的每一節點(node)都可以有流出(量)流入(量),本例還沒這麼複雜,產品由工廠生產,經由倉儲中心,最後要送到市場銷售,生產產品不會有後送情形。亦即,經銷商不會把貨物回送給倉儲中心,倉儲中心不會再後送給工廠,它們的路徑方向(箭頭)是單向。但是,工廠卻可以直送經銷商,而且工廠之間、倉儲中心之間、經銷商之間,彼此可以互相調(運)貨,它們的箭頭路徑是雙向的。

工廠之間互相運貨主要是為了集結貨品,一起裝箱上貨櫃輪,它比各別裝箱運輸還能節省成本。倉儲中心之間可能因倉庫容量或運費問題,從某個倉儲中心集結,再送到另一個倉儲中心,比分別送到兩個倉儲中心還省錢。

本例雖然還不算最複雜的網路模型,但箭頭路徑不少,還有若干雙向的箭頭路徑,這些路徑都會有淨流量   net flow的計算。此外每一路徑的裝載(流量Flow)有限量(200噸),每噸運費資料如下:

圖12(空格表示兩地沒有船班或者產品不能回送)

D3.png

如果完成Excel建模,在Solver規劃求解做完設定,按「求解」,13左邊Excel表格顯示的T×4矩陣」,第四欄是求解答案,標示0Excel不建議的路徑。有標示數值(流量)者,它的起點與目的地(節點)所串連的路徑,才是Excel建議運費最低的路徑,我把它用紅線標出,放置13邊。

圖13

Flow1.png

T×4矩陣」標示由1工廠生產180噸運送到3工廠3工廠生產80送到4倉儲中心,另生產20加上由1工廠送來的180,合計200送到5倉儲中心2工廠直接送160噸到6經銷商,另外生產120噸送到4倉儲中心,最後兩個倉儲中心都將產品運送到6經銷商,滿足它所需的400噸外,再由6經銷商調貨(180)到7經銷商,此一結果運費成本最低(合計$3260,圖13沒有列出)。

知道上述結果,接下來再解說T×4矩陣」的Excel的表格排列設計與運算,亦即:

1把複雜的網路節點排列成T×4矩陣:第一欄為「起點」,第二欄為「目地」第三欄為運費取自圖12從橫排到右欄的兩個節點,空格表示兩地沒有船班往來,所以,節點沒有列示在第一二欄,第四欄為運載量(流量),也是決策變數,這是Solver將要求解的欄位,每一流量值不能超過200噸。

2工廠、倉儲中心、經銷商的流出量或流入量設定限制值:這是Excel試算表I6、I7、I8欄位(工廠)與I12、I13欄位(倉儲中心)必須計算淨流出量I17、I18欄位(經銷商)必須計算淨流入量

表11

flow2.png

上述欄位的輸入公式與上一篇文章(採購標案)的表10-2類似,必須用到OutflowInflow的計算,或者@Sumif

工廠與倉儲中心使用淨流出量Outflow-Inflow 或是 =SUMIF(起點,H6,流量)-SUMIF(目地,H6,流量)

同理,經銷商使用淨流入量Inflow - Outflow或是  =SUMIF(目地,H17,流量)-SUMIF(起點,H17,流量)

總運費成本則是運費與流量的相乘,使用@sumproduct就可以算出來。

3Excel附加軟體Solver(規劃求解)的設定:把Excel表格工廠、倉儲中心、經銷商的流出量或流入量設定限制值:放入Solver的限制條件框,如下圖。

圖14

flow3.png

想一想:將圖11的網路排列成表11的試算表,按下Solver,最省錢的運輸路線(圖13)就規劃出來了,真是一箭穿心,很有樂趣。假設此一路徑各節點有下列變化,將相關數值輸入模型,立即得出最佳解的答案,請讀者不妨自己練習。

1.   從1工廠到各節點的運費,因船公司的競爭而減少一半。

2.  運費條件不變情況下,貨運裝載量的限制提高一倍,此時,6、7經銷商的需求增加一倍,公司在2、3工廠找外發工廠,等同兩地產能各增加一倍。

3.  船運公司罷工,4 倉儲中心到6經銷商的運輸路線被迫關閉。

 

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 mchung 的頭像
    mchung

    洪明洲教授部落格

    mchung 發表在 痞客邦 留言(0) 人氣()