excel在經(jīng)濟(jì)和管理中的應(yīng)用Module 1運輸問題建模及決策(2014秋)

1、 title運輸問題建模和優(yōu)化 在供應(yīng)鏈管理中，運輸是一項重要環(huán)節(jié)，包括：在供應(yīng)鏈管理中，運輸是一項重要環(huán)節(jié)，包括：原材料原材料生產(chǎn)商，生產(chǎn)商生產(chǎn)商，生產(chǎn)商倉庫，倉庫倉庫，倉庫零售零售商商 在運輸環(huán)節(jié)中，如何進(jìn)行優(yōu)化安排，降低成本，在運輸環(huán)節(jié)中，如何進(jìn)行優(yōu)化安排，降低成本，是供應(yīng)鏈管理中的一項重要內(nèi)容。是供應(yīng)鏈管理中的一項重要內(nèi)容。 供應(yīng)地點供應(yīng)量供應(yīng)地點供應(yīng)量 需求地點需求量需求地點需求量 從供應(yīng)地點到需求地點的單位運輸成本從供應(yīng)地點到需求地點的單位運輸成本1. 簡單運輸問題網(wǎng)絡(luò)圖 S o urcesD estina tio ns 已知：已知： 供應(yīng)地點供應(yīng)量 需求地點需求量 從供應(yīng)地點到需

2、求地點的單位運輸成本運輸成本運輸成本最小？最??？例1 供需平衡的運輸問題產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300銷量150150200 某公司從兩個產(chǎn)地A1、A2將物品運往三個銷地B1、B2、B3，各產(chǎn)地的產(chǎn)量、各銷地的銷量和各產(chǎn)地分別運往各銷地的單位運費如下表所示。問：應(yīng)如何調(diào)運可使總運輸費用最??？解： 供需平衡問題-設(shè) xij 為從產(chǎn)地Ai運往銷地Bj的運輸量（下標(biāo)i表示源，下標(biāo)j表示目的地），得到下列運輸量表：解-設(shè)產(chǎn)地銷 地產(chǎn)量B1B2B3A1X11X12X13200A2X21X22X23300銷量150150200例1 供需平衡的運輸產(chǎn)地銷 地產(chǎn)量B1B2B3A1X

3、11X12X13200A2X21X22X23300銷量150150200產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300銷量150150200目標(biāo)函數(shù)：Min z= 6x11+ 4x12+ 6x13+ 6x21+ 5x22+ 5x23 s.t. x11+ x12 + x13 = 200 x21 + x22+ x23 = 300 x11 + x21 = 150 x12 + x22 = 150 x13 + x23 = 200 xij0(i=1、2；j=1、2、3）模 型目標(biāo)函數(shù)： Min z= 6x11+ 4x12+ 6x13+ 6x21+ 5x22+ 5x23 s.t. x11+ x1

4、2 + x13 = 200 x21 + x22+ x23 = 300 x11 + x21 = 150 x12 + x22 = 150 x13 + x23 = 200 xij0(i=1、2；j=1、2、3）決策結(jié)果配送方案2. 運輸問題模型的一般形式njjmiiba11 假設(shè) A1, A2,Am 表示某產(chǎn)品的m個產(chǎn)地；B1,B2,Bn 表示某產(chǎn)品的n個銷地；ai表示產(chǎn)地 Ai 的產(chǎn)量；bj 表示銷地 Bj 的銷量；cij 表示把產(chǎn)品從產(chǎn)地 Ai 運往銷地 Bj 的單位運價。問怎樣調(diào)運這些產(chǎn)品才能使總運費最??？ 如果 則稱該運輸問題為產(chǎn)銷平衡問題；否則，稱產(chǎn)銷不平衡。 銷地產(chǎn)地B1 B2 Bn產(chǎn)量

5、A1 A2 Amc11 c12 c1nc21 c22 c2n cm1 cm2 cmna1 a2 am銷量b1 b2 bn 運輸問題數(shù)據(jù)表運輸問題決策變量表 銷地產(chǎn)地B1 B2 Bn產(chǎn)量A1 A2 Amx11 x12 x1nx21 x22 x2n xm1 xm2 xmna1 a2 am銷量b1 b2 bn 設(shè) xij 為從產(chǎn)地 Ai 運往銷地 Bj 的運輸量，根據(jù)這個運輸問題的要求，可以建立運輸決策變量表。 銷地產(chǎn)地B1B2Bn產(chǎn)量A1 c11x11 c12 x12 c1n x1na1 A2 c21 x21 c22 x22 c2n x2na2 Am cm1 xm1 cm2 xm2 cmn xmn

6、am銷量b1b2bn 產(chǎn)銷平衡運輸問題的數(shù)學(xué)模型0, 1, 1.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz例 2 運輸問題Shipping CostFactoryCustomer1Customer2Customer3Outputf1 700.00 900.00 800.00 12f2 800.00 900.00 700.00 15Order Size1089例2 Big M公司在2個工廠生產(chǎn)一系列大型機(jī)器。產(chǎn)品之一是大型機(jī)床?，F(xiàn)收到3位顧客的訂單。這些機(jī)床會被單獨運送。下表給出了單位運輸成本、工廠產(chǎn)量和客戶的訂貨量。求費用最小的配送方案。模 型設(shè)：xij

7、 為工廠到客戶的配送量目標(biāo)函數(shù)：z=700 x11+900 x12+800 x13 +800 x21+900 x22+700 x23 s.t. X11 + x21 + x31 = 12 x21 + x22 + x23 = 15 x11 + x21 = 10 x12 + x22 = 8 x13 + x23 = 9 xij = 0模型的模型的ExcelExcel描述：描述：Excel 求解區(qū)域地址3.供需不平衡的運輸問題例 3 供需不平衡的運輸問題產(chǎn)地銷 地產(chǎn)量B1B2B3A1646300A2655300銷量150150200 某公司從兩個產(chǎn)地A1、A2將物品運往三個銷地B1、B2、B3，各產(chǎn)地的

8、產(chǎn)量、各銷地的銷量和各產(chǎn)地運往銷地的單位運費如下表所示，問：應(yīng)如何調(diào)運可使總運輸費用最小？3.供需不平衡的運輸問題轉(zhuǎn)化為供需平衡轉(zhuǎn)化：產(chǎn)銷平衡產(chǎn)地銷 地產(chǎn)量B1B2B3B4A16460300A26550300銷量150150200100求解轉(zhuǎn)化：產(chǎn)銷平衡 產(chǎn)地銷 地產(chǎn)量B1B2B3B4A16460300A26550300銷量150150200100產(chǎn)地銷 地產(chǎn)量B1B2B3B4A1x11x12x13x14300A2x21x22x23x24300銷量150150200100求解運輸方案產(chǎn)地銷 地產(chǎn)量B1B2B3B4A115015000300=300A200200100300=300供應(yīng)量1501

9、50200100=運輸費用銷量產(chǎn)大于銷運輸問題的數(shù)學(xué)模型0, 1, 1.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz例 4 供需不平衡的運輸問題產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300銷量250200200 某公司從兩個產(chǎn)地A1、A2將物品運往三個銷地B1、B2、B3，各產(chǎn)地的產(chǎn)量、各銷地的銷量和各產(chǎn)地運往銷地的單位運費如下表所示，問：應(yīng)如何調(diào)運可使總運輸費用最??？轉(zhuǎn)化為供需平衡轉(zhuǎn)化：產(chǎn)銷平衡 產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300A3000150銷量250200200銷大于產(chǎn)運輸問題的數(shù)學(xué)模型0, 1, 1.min1

10、111ijmijijinjijminjijijxnjbxmiaxtsxcz例5 供需不平衡的運輸問題銷 地 及 單 位 運 費產(chǎn)地1區(qū)2區(qū)3區(qū)產(chǎn)量山西盂縣1.81.71.554000河北臨城1.61.51.751500需求量300010002000 某單位有3個生活區(qū)。每年分別需要用煤3000、1000、2000噸，由河北臨城、山西盂縣兩處煤礦負(fù)責(zé)供應(yīng)，價格、質(zhì)量相同。供應(yīng)能力分別為1500、4000噸，單位運價如表所示。 由于需大于供，經(jīng)研究決定1區(qū)供應(yīng)量最多可減少300噸，2區(qū)必須滿足需求量，3區(qū)供應(yīng)量不少于1500噸，試求總費用為最低的調(diào)運方案。模 型設(shè)：設(shè)：目標(biāo)函數(shù)：目標(biāo)函數(shù)： s.t

11、.s.t.ExcelExcel求解結(jié)果：求解結(jié)果：目標(biāo)函數(shù)：目標(biāo)函數(shù)：H18：=SUMPRODUCT(C6:E7,C13:E14)約束：約束：F13：=SUM(C13:E13)F14：=SUM(C14:E14)C17：=SUM(C13:C14)D17：=SUM(D13:D14)E17：=SUM(E13:E14)例5 Excel求解運輸問題線性規(guī)劃的一般模型0, 1, 1.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz容量線路 如果從起點i到終點j的路線有容量Lij ，那么可以增加約束 xij = Mij 。 如果起點i不能向終點j送貨，則增加約束 xij =

12、 0 。 例 6如果起點i不能向終點j送貨，則增加約束 xij = 0生產(chǎn)廠需求1 需求2需求3需求4產(chǎn)量F11613221750F21413191560F3192023-50最低需求量3070010最高需求量507030不限生產(chǎn)能力、需求量和單位運輸費用等數(shù)據(jù)如下表所示。注意工廠3不能向需求4供貨。求解最佳配送方案。例6 Excel 求解過程例 7 綜合應(yīng)用一家石油公司，有油田并進(jìn)口原油，有若干個煉油廠和配送中心，由于市一家石油公司，有油田并進(jìn)口原油，有若干個煉油廠和配送中心，由于市場拓展的需要，公司決定新建煉油廠，管理層需要為新煉油廠選址做出決場拓展的需要，公司決定新建煉油廠，管理層需要為

13、新煉油廠選址做出決策。決策的三個主要因素是：策。決策的三個主要因素是：1.1.從油田運送原油到所有煉油廠（含新建煉油廠）的運輸成本；從油田運送原油到所有煉油廠（含新建煉油廠）的運輸成本；2.2.從所有煉油廠（含新建煉油廠）到每一個配送中心的運輸成本；從所有煉油廠（含新建煉油廠）到每一個配送中心的運輸成本；3.3.新煉油廠的運作新煉油廠的運作成本已知。成本已知。例如，勞動力成本、賦稅、能源成本、保險例如，勞動力成本、賦稅、能源成本、保險成本等成本等。管理層需要的數(shù)據(jù)：管理層需要的數(shù)據(jù)：1. 每個新煉油廠地點的選擇帶來的總原油運輸成本；每個新煉油廠地點的選擇帶來的總原油運輸成本；2. 每個新煉油廠

14、地點的選擇帶來的總石油制品運輸成本。每個新煉油廠地點的選擇帶來的總石油制品運輸成本。3. 最終計算出每個備選地點最終計算出每個備選地點“成本合計成本合計”。例 7 綜合應(yīng)用管理層需要的財務(wù)數(shù)據(jù)：管理層需要的財務(wù)數(shù)據(jù)：1. 1. 每個新煉油廠地點的選擇帶來的總原油運輸成本；每個新煉油廠地點的選擇帶來的總原油運輸成本；2. 2. 每個新煉油廠地點的選擇帶來的總石油制品運輸成本。每個新煉油廠地點的選擇帶來的總石油制品運輸成本。表表1 公司生產(chǎn)數(shù)據(jù)公司生產(chǎn)數(shù)據(jù)(要求煉油廠滿負(fù)荷運轉(zhuǎn)要求煉油廠滿負(fù)荷運轉(zhuǎn)) （百萬桶）（百萬桶）煉油廠煉油廠年所需原油量年所需原油量油田油田年原油產(chǎn)量年原油產(chǎn)量R1100F1

15、80R260F260R380F3100R4(新建新建)120F4(進(jìn)口進(jìn)口)120SUM360360表表2 從油田到煉油廠從油田到煉油廠-原油運輸成本數(shù)據(jù)原油運輸成本數(shù)據(jù)油田油田R1R2R3N1N2N3年原油產(chǎn)年原油產(chǎn)量量F124531180F245313460F3573457100F4(進(jìn)口進(jìn)口)235434120煉油廠需求量煉油廠需求量1006080120表表3 從煉油廠到配送中心從煉油廠到配送中心 - 石油制品運輸成本數(shù)據(jù)石油制品運輸成本數(shù)據(jù)煉油廠煉油廠D1D2D3D4煉油廠產(chǎn)量煉油廠產(chǎn)量R17 6 68100R27 5 4760R37 8 4380N18 6 32120N25 4 36

16、N34 3 15配送中心需求量配送中心需求量1008080100表4 備選地點估計運營成本地點運作成本運輸成本（油田到煉油廠）運輸成本（煉油廠到配送中心）成本合計N1620N2573N3530通過標(biāo)簽的移動或復(fù)制選項并勾通過標(biāo)簽的移動或復(fù)制選項并勾選建立副本可以連參數(shù)都一起復(fù)選建立副本可以連參數(shù)都一起復(fù)制制例 8 轉(zhuǎn)運問題建模與決策如圖所示，工廠F1、F2生產(chǎn)的產(chǎn)品通過倉庫H3和H4配送給零售商R5，R6，R7,R8且倉庫沒有留存；已知工廠的產(chǎn)量和零售商的需求量以及配送線路上的單位運輸費用，如何配送，總成本最小，給出配送方案。求解步驟：1. 問題轉(zhuǎn)化為表格。2. 寫出目標(biāo)函數(shù)和約束。3. 使用

17、解題工具求解。例8 求解1.轉(zhuǎn)運站的輸入=輸出2.所有不可配送單元格=0目標(biāo)函數(shù)：目標(biāo)函數(shù)：K15：=SUMPRODUCT(C2:H5,C9:H12)約束：約束： I9：=C9+D9I10：=C10+D10I11：=SUM(E11:H11)I12：=SUM(E12:H12)C13：=C9+C10D13：=D9+D10E13：=E11+E12F13：=F11+F12G13：=G11+G12H13：=H11+H12C11:D12=0E9:H10=0例 9 續(xù)例8如圖所示，在例8的基礎(chǔ)上，如果工廠F2可以直接為R8配送，R7可以為R8配送，配送線路上的單位運輸費用如圖所示，其他條件不變，分別討論能使

18、總成本最小的配送方案。例9-1求解結(jié)果（f2r8）1. 問題轉(zhuǎn)化為表格。2. 寫出目標(biāo)函數(shù)和約束。3. 使用解題工具求解。Excel求解目標(biāo)函數(shù)：目標(biāo)函數(shù)：K15：=SUMPRODUCT(C2:H5,C9:H12)約束：約束： I9：=C9+D9I10：=C10+D10+H10I11：=SUM(E11:H11)I12：=SUM(E12:H12)C13：=C9+C10D13：=D9+D10E13：=E11+E12F13：=F11+F12G13：=G11+G12H13：=H10+H11+H12C11:D12=0E9:H9=0E10:G10=0F2F2向向R8R8送貨送貨Excel求解過程Excel公式：公式：K16：=SUMPRODUCT(C2:H6,C10:H14)I10：=SUM(C10:H10)I11：=SUM(C11:H11)I12: =SUM(C12:H12)I13: =SUM(C13:H13)I14：=H14C15：=