第7章物流管理中的Excel课件.ppt

1、第7章 物流管理中的Excel应用o现代物流是人们为满足某种需要而组织社会物质运动的总称，是货物流动、信息传递、价值增值等等的过程，功能整合、过程整合和资源整合是物流理念最本质的核心内容。o企业物流管理是企业对所需原材料、燃料、设备工具等生产资料进行有计划的组织采购、供应、保管、合理使用等各项工作的总称。o第三方物流是物流的实际需求方（第一方）和物流的实际供给方（第二方）之外的第三方，通过合同全部或者部分利用第二方的资源向第一方提供的物流服务。o 本章运用各种分析方法，借助Excel的计算工具，对供应商选择进行了层次分析，对基本形式的经济批量控制作了包括直观图解在内的各种求解，规划求解方法用在

2、了物料消耗控制问题，对供需相等、供需不等、转运问题和多目标运输问题设置了规划求解模型和求解，对配货中心做了合理选址，并提出了图论中最大流问题的Excel解决方案。7.1 7.1 运用层次分析法选择供应商运用层次分析法选择供应商【例7.1】用层次分析法作出供应商的排序。现用质量、价格、服务与交货期四个指标用来评价供应商，目前有四个供应商即S1、S2、S3、和S4可以考虑，评价尺度与层次建立如下所示。指标间评价的评分如图所示。要求应用简化的层次分析法给出供应商的层次。（例题数据参见“第7章数据资料”之“供应商层次排序”工作表）这里要说明的是：指标间的评分是买方进行一系列两两比较来确定指标的相对重要

3、性。 用简化的层次分析法作出供应商的排序，具体做法 见图：第一步：完成两两判断矩阵，即矩阵中的对角线上的数据都是1，其余数据为相应判断值的倒数。第二步：计算各指标权重，权重提供了对每个指标相对重要性的测度。第三步：计算供应商间各指标的两两比较矩阵。就是对四个供应商就每个指标进行两两比较，这个过程与建立指标的两两比较矩阵的步骤一样。唯一的区别是对每一个指标都有相应的比较矩阵。决策者首先就某一指标对供应商进行两两比较，然后对其他三个指标重复上述过程。以价格指标的供应商两两比较矩阵为例，其中单元格B36为供应商S1在价格指标上的权重，B36“=AVERAGE(B31/B35,C31/C35,D31/

4、D35,E31/E35)”，这里平均数的计算包括了第二步中的b)、c)两项计算。见图： 第四步：供应商层次总排序。层次分析法的最后一步可以总结为图。总排序结果是通过对各供应商各指标下的权重，用对应指标权重求加权平均的到的。比较各供应商权重的大小得出供应商层次排序。见图 。认为应当选择供应商S1，其次是S4。运用层次分析法有很多优点，其中最重要的一点就是简单明了。层次分析法不仅适用于存在不确定性和主观信息的情况，还允许以合乎逻辑的方式运用经验、洞察力和直觉。也许层次分析法的最大优点是提出了层次本身，它使得买方能够认真地考虑和衡量指标的相对重要性。供应商确定以后，还要根据合作情况对其作出评估，完善

5、合作关系，建立采购认证体系，实现供应物流的质量管理。7.2经济批量与物料消耗控制 经济批量法通过研究物资订购费用、存储费用与订购次数、订购数量之间的关系，对订购费用与存储费用进行最优化决策。它的使用前提有以下几项假设：一定时期内企业订购某种物资的总量是一定的；对各种物料的需求是相互独立的，不考虑物料之间的联系；物料需求具有连续性，且相对稳定；库存一旦低于订货点，企业能立即发出订货。求经济订购批量即是求以何批量进行订购时年库存总成本最少。 7.2.1经济批量的公式求解对基本经济订购批量模型：TC=年存储费用+年订货费用TC式对订货量求导，并令导数为零，可得出总成本函数值的最小的订货量，即经济订购

6、批量点，公式如下： 单次订货费用一次订货量年需求量年单位存储费用一次订货量2年存储费用率物资单价年需用量每次订购费用单位物资年存储费用年需用量每次订购费用经济批量22【例7.2】某厂全年需要某种材料10000件，每批订货成本250元，价格每件10元，存储费用率为12.5，。求：（1）经济订购批量（2）订购间隔期（3）年最低订储总费用（4）采用定量订购方式，定购周期为4天，保险储备量60件，则库存量应降至什么水平提出订货？（注：一年以360天计）（例题数据参见“第7章数据资料”之“经济批量”工作表）一般的解法的公式：件经济订购批量2000125. 010100002502订购间隔期： 定购次数=

7、10000/2000=5次，订购间隔期=360/5=72天年最低订储总费用：年最低订储总费用=2505+2000/2100.125=1250+1250=2500元订购点=（定量库存控制公式）=平均每日需用量 备用天数+保险储备量 =10000/3604+60=171件从上述计算还可获知，经济订购批量时，订购成本和存储成本达到相同。公式求解在Excel上的设置和公式输入见图。以“每次订货量”为最初始的未知量，其自定义函数公式输入见图中的编辑栏。计算所得数值：每年订购5次，每次2000件，此时最小的年存货费用总额为2500元。7.2.2 经济批量的规划求解有了Excel规划求解工具，可以避开公式求

8、导而直接求得经济批量。沿用上例，其做法：第一步：做设置如图上方，注意以“全年订货次数”为最初始的未知量，其余与图中公式解法的输入相同。第二步：进入“工具”“规划求解” ，在弹出的对话框中作设置见图下方：以“年存货费用总额” 作为目标函数，要求为最小值；以“全年订货次数”作为未知量，置于“目标单元格”；约束条件中要求目标单元格非负，且为整数。第三步：按“求解”，即可得结果见图，结果是相同的。7.2.3 经济批量的图解法为了直观地表示各类费用和经济批量、最佳订购次数之间的关系，同时避开总费用函数式的建立和最小值的导数求解，可以利用Excel表格作经济批量的图解法。沿用上例，具体做法是：第一步：最佳

9、经济批量的表格计算，用自定义函数在各单元格中键入：在A列规则地输入订货次数若干项；每次订货额全年需要量/全年订货次数 B2“=10000/A2”;平均存货额每次订货额/2 C2“=B2/2”年保管费总额平均存货额单价存储费用率 D2“=C2*10*0.125”年订货费用全年订货次数单次订货费 E2“=A2*250” 年存货费用总额年保管费总额+年订货费用F2“=D2+ E2” ；见图。 第二步：选定B2：F2，按住F2右下角小十字往下拖曳填充至第25行，可得各列计算值。见图： 第三步：鼠标选定后三列，在工具栏选取“图表向导”在图表类型 “折线图”， “下一步”确认， 立即可得“经济批量的图解”

10、，并可用鼠标在图上移动进行观察。从图上可以看出：总费用最小的年订货次数为5，对应的最佳经济批量为2000件，总费用为2500元，此时年保管费用等于年订货费用。随着年订货次数的变化，年存货费用总额的变化趋势一目了然，便于在其他附加条件的限制下进行权衡决策。7.2.4 物料消耗控制o【例7.3】保达制造公司有一批长度为5米的钢管（数量充分多），为制造零件的需要，要将其截成分别为1400厘米、950厘米、650厘米的短料，而且三种管料要按 2：4：1的比例配套生产。（例题数据参见“第7章数据资料”之“合理截料”工作表）o把一根钢管截成几段需要的短料时，一般要产生残料。例如，把5米长的钢管截成1400

11、厘米的3根和650厘米的1根，要剩残料150厘米；如果截成1400厘米的2根和950厘米的2根，要剩残料300厘米。现在的问题是如何截分才能使截下来的三种短料，既能配套，又能使残料最少。o根据各种可能列出8种截法（残料明显多的就不再列出来了）。挑选其中一种省料的截法当然可以使残料最少，但是不能满足配套要求。所以必须同时采取若干种截法，配合起来，在完成配套要求的条件下，使总残料最少。具体设置见图。计算结果：每批取3根，分别按照1、4、6截取，则可满足上述要求，即：4段1400cm、8段950cm、2段650cm，而且残料最小，仅为500厘米 。7.3运输问题的最优决策7.3.1供需相等的运输问题

12、求解 1. 1. 运输问题的基本模型运输问题的基本模型 【例7.4】利民运输公司要从甲城调出蔬菜2000吨，从乙城调出蔬菜1100吨，分别供应A地1700吨、B地1100吨、C地200吨、D地100吨，单位运输费用已知。假定运费与运量成正比，则如何安排调拨运输计划，能使得总的运输费用为最小？上述问题可以归结为这样的线性规划模型：计算过程的设置见图：计算过程的设置见图：计算的结果见图：2.2.运输问题的适当转折运输问题的适当转折 【例7.5】（ 发车送料问题）荣城运输公司从中心制造地点向四个位于城区北、东、南、西方向的分配点运送材料。该公司的卡车用于从制造地点向分配点运送材料。其中有每车能装5吨

13、的大型卡车，运输能力为每天9车次；有每车能装2吨的中型卡车，运输能力为每天12车次，有每车能装1吨的小型卡车，运输能力为每天5车次。某天，城区北、东、南、西四个分配点分别需要材料14吨、10吨、20吨、8吨。每种卡车向各分配点送料一次的费用见下表。公司应如何安排才能在满足需求的条件下使得运料的总费用为最少？（例题数据参见“第7章数据资料”之“卡车运料”工作表）计算过程的设置见图：计算结果见图：对话框中按“求解”得到一最小运费的运输方案，满足所有约束要求。最小的总运费：873元。7.3.2供需不等的运输问题求解o【例7.6】设有三个煤矿供应煤炭给四个地区。各煤矿的产量、各地区的需求量以及从各煤矿

14、运送煤炭到各地区的单价如表。试求出将产量分配完，又使总运费最低的煤炭调运方案。（例题数据参见“第7章数据资料”之“供需变动”工作表）单位： （万元/万吨）煤矿地区 甲乙丙丁产量（万吨）A1613221750B1413191560C192023（900）50最低需求（万吨）3070010最高需求（万吨）507030不限计算过程的设置与结果见图：7.3.3 转运问题【例7.7】已知甲乙两处分别有70吨和55吨物资外运， A、B、C三处各需要物资35、40、50吨，物资可以直接目的地，也可以经中转站和其他地点转运，已知各处间距离（公里）如表。，试制定一个最优分配方案，使总运费最少。（例题数据参见“第

15、7章数据资料”之“转运问题”工作表）解：由于问题中所有的产地、销地和中转站都是产地，也是销地，所以可以看做是一个七个产地和销地的产销平衡的运输模型。计算步骤为：1.构建扩大了的单位运价表；2.设定运量限制。转出量限制：由于都可以做为产地，所以甲、乙两地能够转出的量是全部的产量，中转站的转出量也是全部的产量，A、B、C的转出量是保留下各自的需求后的剩余量；转入量限制：由于都可以做为销地，所以甲、乙两地能够转入的量是除自己生产的其余的量，中转站的转入量也是全部的产量，A、B、C的转入量是全部可能的产量；3.构建运输模型表。设定从产地到销地的运量为xij0，其中i,j=1,74.构建目标函数。5.求

16、解。7 , 171ixjij转出量7 , 171jxiij转入量7171minijijijxcZ利用EXCEL实现据有转运问题的运输问题，其步骤如下：第一步：打开“第7章 数据资料”，选择“转运问题”工作表。例题所给条件分布在单元格A3:N10之中；第二步：构建扩大的单位运价模型表和运量限制。1.如图所示，在单元格A13：H20中构建单位运价表。2.设定转出量和转入量限制。设定转出量：在单元格I13中输入“转出量”，在I14:I20中输入“125、125、125、125、90、85、75”，形成转出量限制；设定转入量：在单元格A21中输入“转入量”，在B21:H21中输入“55、70、125、

17、125、125、125、125”，形成转入量限制；如图所示：第三步：构造运输模型表构建运输模型表模板：选择单元格A13:I21，复制粘贴到单元格A24：I32，删除B25：I32内数值，形成空表；定义约束条件：在单元格I25中输入“=SUM(B25:H25)”，选中单元格I25，按住鼠标左键拖至I31；在单元格B32中输入“=SUM(B25:B31)”，选中单元格B32，按住鼠标左键拖至H32；定义目标函数：在单元格I32中输“=SUMPRODUCT(B25:H31,B14:H20)”；运输模型表如图所示：第四步：线性规划求解选择“工具规划求解”，弹出规划求解对话框，如图所示定义各选项 。求解

18、，得到线性规划求解结果，如图所示。确定最优方案：在单元格A35:I43中构建最终运输方案表格，如图选择线性规划结果，即为最优运输方案。得甲分配给T2中转站40个单位，分配给C销地30个单位；乙分配给T1中转站55个单位；T1中转站分配给A销地35个单位，C销地20个单位；T2中转站分配给B销地40个单位。7.3.4 多目标运输问题求解o【例7.8】华蔚麻纺公司要从四个生产地向三个需求地发送纺织品，发送每件纺织品的的单价、各生产地的产出量和各地需求量见图单元格A1：E6 所示。现要满足下列要求给出发送货物的方案。由于产品供不应求，所以要从每个产地运出所有的物资，这是绝对约束；其余多个目标和优先级

19、为：o第一优先级：各销地得到的物资不少于销量的一半；o第二优先级：销地B1的需求全部得到满足。 X42尽可能少。o第三优先级： 总运费最少。o这是典型的多目标运输问题：o（例题数据参见“第7章数据资料”之“多目标运输”工作表）在解决多目标运输问题之前，首先要给出“无目标”的一般运输问题的解决方案，具体过程可参照例7.3，注意供应小于需求时约束条件的设置，不再赘述。设xij 为i生产地向j需求地的发货量（件），(i=1,2,3，4 ， j= 1,2,3,)， aij为i生产地向j需求地的单位发货费用（元/件）， bi为i生产地的可供应量(i=1,2,3，4)， cj为j需求地的需求量，（j= 1

20、,2,3,)无目标线性规划求解过程与结果，如图所示。可以看到， 无目标下的运输方案，实际运出量等于生产量，总运费为1100元。具体在Excel模板上的设置步骤见图：话框中按“求解”得到多目标运输规划的满意解，见下图 ：需求地B1得到120件，B2得到70件，B3得到110件，x42=0，总费用为1160元，超过无目标运输规划60元满足所有目标约束要求。 7.4 配货中心的合理选址o【例7.9】兴华公司考虑在四个城市：北京、上海、广州和武汉选择设立配货中心库房，这些库房负责向华北、华中和华南地区发送货物。每个库房每月可发送货物1000件。在北京、上海、广州和武汉设库房每月的成本分别为9万元、5万

21、元、7万元、4万元。华北、华中和，华南地区各地区每月平均需求量分别为1100件、700件、800件。且上海比广州优先考虑。各城市向各地区发送货物的单位费用见表 ：单位费用华北华中华南固定成本（元）北京20040050090000上海30025045050000广州60040025070000武汉30015020040000需求1100700800公司希望在满足地区需求的前提下使每月总成本最小；要求库房分散在各城市并且不超过3个。上海和广州两者首选上海。（例题数据参见“第7章数据资料”之“库房设立”工作表）上述问题可以归结为这样的线性规划模型：令aij为i建库城市向j地区的单位发货费用（元/件）

22、，bi为i城建库的固定成本(i=1,2,3，4)，cj为各地区的需求量。（j= 1,2,3,)设决策变量： xij 为i建库城市向j地区的发货量，(i=1,2,3，4 ， j= 1,2,3,)设辅助0-1决策变量yi(i=1,2,3，4) yi=1 表示在i城建库，yi=0 表示不在i城建库；具体在Excel模板上的设置步骤见图：具体在Excel模板上的设置步骤见图：话框中按“求解”，得到一最小运费的运输方案，满足所有约束要求，应当在北京、上海、武汉三城市建立库房，发送方案见图7-39，此时总费用为725000元。 7.5最大流问题o最大流问题是一类应用极为广泛的问题，是图论中的一种理论与方法

23、，研究网络上的一类最优化问题。例如在交通运输网络中有人流、车流、货物流，供水网络中有水流，金融系统中有现金流，通讯系统中有信息流，等等。o最大流问题的研究密切了图论和运筹学，特别是与线性规划的联系，开辟了图论应用的新途径。o网络最大流问题也可以用EXCEL来实现。 【例7.10】下图是描述一个水渠系统，其中R1、R2、R3代表三个水库，A、B、C、D、E、F代表水渠的交汇点，T表示水渠终点的一个城市，水渠各段每日允许通过的最大流量（千立方米）分别见表。城市水资源管理部门希望制定一个方案，使每天输送这到城市的水流量最大。 ABCDEFTR17365-A6045-D120R2405060B7055

24、45E190R3-8070C-7090F130解：此问题可以利用EXCEL中提供的规划求解实现步骤如下：第一步：打开“第7章 数据资料”，选择“最大流问题”工作表。第二步：构建各结点间的容量矩阵。在单元格A9：K19区域内构建“各结点间的容量矩阵”空表，在A10:A19中输入“R1、R2、R3、A、B、C、D、E、F、T”做为起点，在B9:K9中输入“R1、R2、R3、A、B、C、D、E、F、T”做为终点；输入每个结点间容量：根据已知条件，按起点到终点的方向，在相应位置输入各结点间允许的最大流量。如在单元格E10中输入“73”代表结点R1A间的最大允许的流量为73，在单元格K16中输入“120

25、” 代表结点DT间的最大允许的流量为120；各结点间的容量矩阵如图 ：第三步：构建最大流量求解表构造表格：选择单元格A9:K19，复制粘贴到单元格A23：k33中；删除单元格B24:K33中的所有数字；定义约束条件：定义结点流出和：在单元格L23中输入“结点流出和”，在单元格L24中输入“=SUM(B24:K24)”，再选中单元格L24，光标移对右下角呈十字时，按住鼠标左键拖至L33；定义结点流入和：在A34中输入“结点流入和”，在单元格B34中输入“=SUM(B24:B33)”，选中单元格B34，光标移对右下角呈十字时，按住鼠标左键拖至K34；定义结点流差：在A35中输入“结点流差”，选中单元格B35:K35输入“=TRANSPOSE(L24:L33)-B34:K34”，同时按“Ctrl+Shift+Enter”确定；定义结点流差限制：在A36中输入“结点流差限制”，在单元格E36:J36中输入“0”；定义目标函数：在单元格L34中输“=SUM(L24:L26)”；最大流量求解表如图所示：第四步：线性规划求解。选择“工具规划求解”，弹出规划求解对话框，并如图所示定义各选项。求解，得到最大流量线性规划求解结果，如图 。如上图所示可知，最大流量为395，最大流量安排方案，如图 ：