網路模型
利用Excel求解網路模型,有「一箭穿心」的樂趣,因為,它穿過蜿蜒曲折網路,從交相連結的網路路徑,撥出層層疊疊雲霧,找到簡單直接明確路徑,體驗到魔術般、不可思議的Excel運算。
由於Excel的網路求解模型太「輕快」了,這種不可思議反而讓人對它的建置過程感覺抽象、難解,本文稍後會詳細解說這一建置過程,包括:
(1)如何把複雜的網路節點排列成「T×4矩陣」
(2)如何為節點的流出或流入設定正確限制值
(3)如何叫出Excel附加軟體Solver(規劃求解)進行設定
下例是Practical Management Science (Winston and Albright, 2018)第241頁的例子。如圖11所示,網路總共有7個節點:1、2、3節點為工廠,4、5為倉儲中心,6、7為市場經銷商。圖中1、2、3工廠節點標示S的數據是工廠的產能(合計600噸),4、5倉儲中心的節點標示T=0,表示不留庫存,6、7經銷商的節點標示D的數據是市場需求量,也是產品最後要運到市場的數量(580噸)。
圖11
讀者可以將上述例子想像是台商在海外布局的經營模式,1、2是台商在大陸的工廠,3是台灣的生產工廠,兩個倉儲中心4、5,一個在美西、另一個在美東, 6、7是在美國的兩個經銷商據點。台商如何將1、2、3三個工廠生產的產品,以最便宜的運送成本,送到(美國)6、7兩個經銷據點呢??
如上一篇文章(採購標案)提到的,網路模型的每一節點(node)都可以有流出(量)與流入(量),本例還沒這麼複雜,產品由工廠生產,經由倉儲中心,最後要送到市場銷售,生產產品不會有後送情形。亦即,經銷商不會把貨物回送給倉儲中心,倉儲中心不會再後送給工廠,它們的路徑方向(箭頭)是單向的。但是,工廠卻可以直送經銷商,而且工廠之間、倉儲中心之間、經銷商之間,彼此可以互相調(運)貨,它們的箭頭路徑是雙向的。
工廠之間互相運貨主要是為了集結貨品,一起裝箱上貨櫃輪,它比各別裝箱運輸還能節省成本。倉儲中心之間可能因倉庫容量或運費問題,從某個倉儲中心集結,再送到另一個倉儲中心,比分別送到兩個倉儲中心還省錢。
本例雖然還不算最複雜的網路模型,但箭頭路徑不少,還有若干雙向的箭頭路徑,這些路徑都會有淨流量 net flow的計算。此外,每一路徑的裝載(流量Flow)有限量(200噸),每噸運費資料如下:
圖12(空格表示兩地沒有船班或者產品不能回送)
如果完成Excel建模,在Solver(規劃求解)做完設定,按「求解」,圖13左邊Excel表格顯示的「T×4矩陣」,第四欄是求解答案,標示0,Excel不建議的路徑。有標示數值(流量)者,它的起點與目的地(節點)所串連的路徑,才是Excel建議運費最低的路徑,我把它用紅線標出,放置圖13右邊。
圖13
「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
上述欄位的輸入公式與上一篇文章(採購標案)的表10-2類似,必須用到Outflow或Inflow的計算,或者@Sumif:
工廠與倉儲中心使用淨流出量:Outflow-Inflow 或是 =SUMIF(起點,H6,流量)-SUMIF(目地,H6,流量)
同理,經銷商使用淨流入量:Inflow - Outflow或是 =SUMIF(目地,H17,流量)-SUMIF(起點,H17,流量)
總運費成本則是運費與流量的相乘,使用@sumproduct就可以算出來。
(3)Excel附加軟體Solver(規劃求解)的設定:把Excel表格工廠、倉儲中心、經銷商的流出量或流入量設定限制值:放入Solver的限制條件框,如下圖。
圖14
想一想:將圖11的網路,排列成表11的試算表,按下Solver,最省錢的運輸路線(圖13)就規劃出來了,真是一箭穿心,很有樂趣。假設此一路徑各節點有下列變化,將相關數值輸入模型,立即得出最佳解的答案,請讀者不妨自己練習。
1. 從1工廠到各節點的運費,因船公司的競爭而減少一半。
2. 運費條件不變情況下,貨運裝載量的限制提高一倍,此時,6、7經銷商的需求增加一倍,公司在2、3工廠找外發工廠,等同兩地產能各增加一倍。
3. 船運公司罷工,4 倉儲中心到6經銷商的運輸路線被迫關閉。
留言列表