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

第7章 物流管理中的Excel应用o现代物流是人们为满足某种需要而组织社会物质运动的总称，是货物流动、信息传递、价值增值等等的过程，功能整合、过程整合和资源整合是物流理念最本质的核心内容。o企业物流管理是企业对所需原材料、燃料、设备工具等生产资料进行有计划的组织采购、供应、保管、合理使用等各项工作的总称。o第三方物流是物流的实际需求方（第一方）和物流的实际供给方（第二方）之外的第三方，通过合同全部或者部分利用第二方的资源向第一方提供的物流服务。o 本章运用各种分析方法，借助Excel的计算工具，对供应商选择进行了层次分析，对基本形式的经济批量控制作了包括直观图解在内的各种求解，规划求解方法用在了物料消耗控制问题，对供需相等、供需不等、转运问题和多目标运输问题设置了规划求解模型和求解，对配货中心做了合理选址，并提出了图论中最大流问题的Excel解决方案。第7章 物流管理中的Excel应用现代物流是人们为满足某种需17.1 7.1 运用层次分析法选择供应商运用层次分析法选择供应商【例7.1】用层次分析法作出供应商的排序。现用质量、价格、服务与交货期四个指标用来评价供应商，目前有四个供应商即S1、S2、S3、和S4可以考虑，评价尺度与层次建立如下所示。指标间评价的评分如图所示。要求应用简化的层次分析法给出供应商的层次。（例题数据参见“第7章数据资料”之“供应商层次排序”工作表）这里要说明的是：指标间的评分是买方进行一系列两两比较来确定指标的相对重要性。7.1 运用层次分析法选择供应商【例7.1】用层次分析法作出2用简化的层次分析法作出供应商的排序，具体做法 见图：第一步：完成两两判断矩阵，即矩阵中的对角线上的数据都是1，其余数据为相应判断值的倒数。第二步：计算各指标权重，权重提供了对每个指标相对重要性的测度。用简化的层次分析法作出供应商的排序，具体做法 见图：3第三步：计算供应商间各指标的两两比较矩阵。就是对四个供应商就每个指标进行两两比较，这个过程与建立指标的两两比较矩阵的步骤一样。唯一的区别是对每一个指标都有相应的比较矩阵。决策者首先就某一指标对供应商进行两两比较，然后对其他三个指标重复上述过程。以价格指标的供应商两两比较矩阵为例，其中单元格B36为供应商S1在价格指标上的权重，B36“=AVERAGE(B31/B35,C31/C35,D31/D35,E31/E35)”，这里平均数的计算包括了第二步中的b)、c)两项计算。见图：第三步：计算供应商间各指标的两两比较矩阵。就是对四个供应商就4第四步：供应商层次总排序。层次分析法的最后一步可以总结为图。总排序结果是通过对各供应商各指标下的权重，用对应指标权重求加权平均的到的。比较各供应商权重的大小得出供应商层次排序。见图。认为应当选择供应商S1，其次是S4。运用层次分析法有很多优点，其中最重要的一点就是简单明了。层次分析法不仅适用于存在不确定性和主观信息的情况，还允许以合乎逻辑的方式运用经验、洞察力和直觉。也许层次分析法的最大优点是提出了层次本身，它使得买方能够认真地考虑和衡量指标的相对重要性。供应商确定以后，还要根据合作情况对其作出评估，完善合作关系，建立采购认证体系，实现供应物流的质量管理。第四步：供应商层次总排序。层次分析法的最后一步可以总结为图。57.2经济批量与物料消耗控制 经济批量法通过研究物资订购费用、存储费用与订购次数、订购数量之间的关系，对订购费用与存储费用进行最优化决策。它的使用前提有以下几项假设：一定时期内企业订购某种物资的总量是一定的；对各种物料的需求是相互独立的，不考虑物料之间的联系；物料需求具有连续性，且相对稳定；库存一旦低于订货点，企业能立即发出订货。求经济订购批量即是求以何批量进行订购时年库存总成本最少。7.2.1经济批量的公式求解对基本经济订购批量模型：TC=年存储费用+年订货费用TC式对订货量求导，并令导数为零，可得出总成本函数值的最小的订货量，即经济订购批量点，公式如下：7.2经济批量与物料消耗控制 经济批量法通过研究6【例7.2】某厂全年需要某种材料10000件，每批订货成本250元，价格每件10元，存储费用率为12.5，。求：（1）经济订购批量（2）订购间隔期（3）年最低订储总费用（4）采用定量订购方式，定购周期为4天，保险储备量60件，则库存量应降至什么水平提出订货？（注：一年以360天计）（例题数据参见“第7章数据资料”之“经济批量”工作表）一般的解法的公式：订购间隔期：定购次数=10000/2000=5次，订购间隔期=360/5=72天年最低订储总费用：年最低订储总费用=2505+2000/2100.125=1250+1250=2500元订购点=（定量库存控制公式）=平均每日需用量 备用天数+保险储备量 =10000/3604+60=171件从上述计算还可获知，经济订购批量时，订购成本和存储成本达到相同。【例7.2】某厂全年需要某种材料10000件，每批订货成本27公式求解在Excel上的设置和公式输入见图。以“每次订货量”为最初始的未知量，其自定义函数公式输入见图中的编辑栏。计算所得数值：每年订购5次，每次2000件，此时最小的年存货费用总额为2500元。公式求解在Excel上的设置和公式输入见图。以“每次订货量”87.2.2 经济批量的规划求解有了Excel规划求解工具，可以避开公式求导而直接求得经济批量。沿用上例，其做法：第一步：做设置如图上方，注意以“全年订货次数”为最初始的未知量，其余与图中公式解法的输入相同。第二步：进入“工具”“规划求解”，在弹出的对话框中作设置见图下方：以“年存货费用总额”作为目标函数，要求为最小值；以“全年订货次数”作为未知量，置于“目标单元格”；约束条件中要求目标单元格非负，且为整数。第三步：按“求解”，即可得结果见图，结果是相同的。7.2.2 经济批量的规划求解有了Excel规划求解工具，可97.2.3 经济批量的图解法为了直观地表示各类费用和经济批量、最佳订购次数之间的关系，同时避开总费用函数式的建立和最小值的导数求解，可以利用Excel表格作经济批量的图解法。沿用上例，具体做法是：第一步：最佳经济批量的表格计算，用自定义函数在各单元格中键入：在A列规则地输入订货次数若干项；每次订货额全年需要量/全年订货次数 B2“=10000/A2”;平均存货额每次订货额/2 C2“=B2/2”年保管费总额平均存货额单价存储费用率 D2“=C2*10*0.125”年订货费用全年订货次数单次订货费 E2“=A2*250”年存货费用总额年保管费总额+年订货费用F2“=D2+E2”；见图。7.2.3 经济批量的图解法为了直观地表示各类费用和经济批量10第二步：选定B2：F2，按住F2右下角小十字往下拖曳填充至第25行，可得各列计算值。见图：第二步：选定B2：F2，按住F2右下角小十字往下拖曳填充至第11第三步：鼠标选定后三列，在工具栏选取“图表向导”在图表类型“折线图”，“下一步”确认，立即可得“经济批量的图解”，并可用鼠标在图上移动进行观察。从图上可以看出：总费用最小的年订货次数为5，对应的最佳经济批量为2000件，总费用为2500元，此时年保管费用等于年订货费用。随着年订货次数的变化，年存货费用总额的变化趋势一目了然，便于在其他附加条件的限制下进行权衡决策。第三步：鼠标选定后三列，在工具栏选取“图表向导”在图127.2.4 物料消耗控制o【例7.3】保达制造公司有一批长度为5米的钢管（数量充分多），为制造零件的需要，要将其截成分别为1400厘米、950厘米、650厘米的短料，而且三种管料要按 2：4：1的比例配套生产。（例题数据参见“第7章数据资料”之“合理截料”工作表）o把一根钢管截成几段需要的短料时，一般要产生残料。例如，把5米长的钢管截成1400厘米的3根和650厘米的1根，要剩残料150厘米；如果截成1400厘米的2根和950厘米的2根，要剩残料300厘米。现在的问题是如何截分才能使截下来的三种短料，既能配套，又能使残料最少。o根据各种可能列出8种截法（残料明显多的就不再列出来了）。挑选其中一种省料的截法当然可以使残料最少，但是不能满足配套要求。所以必须同时采取若干种截法，配合起来，在完成配套要求的条件下，使总残料最少。7.2.4 物料消耗控制【例7.3】保达制造公司有一批长度13第7章物流管理中的Excel课件14具体设置见图。计算结果：每批取3根，分别按照1、4、6截取，则可满足上述要求，即：4段1400cm、8段950cm、2段650cm，而且残料最小，仅为500厘米。具体设置见图。计算结果：157.3运输问题的最优决策7.3.1供需相等的运输问题求解 1.1.运输问题的基本模型运输问题的基本模型 【例7.4】利民运输公司要从甲城调出蔬菜2000吨，从乙城调出蔬菜1100吨，分别供应A地1700吨、B地1100吨、C地200吨、D地100吨，单位运输费用已知。假定运费与运量成正比，则如何安排调拨运输计划，能使得总的运输费用为最小？上述问题可以归结为这样的线性规划模型：7.3运输问题的最优决策7.3.1供需相等的运输问题求解【16计算过程的设置见图：计算过程的设置见图：17计算过程的设置见图：计算的结果见图：计算过程的设置见图：计算的结果见图：182.2.运输问题的适当转折运输问题的适当转折【例7.5】（发车送料问题）荣城运输公司从中心制造地点向四个位于城区北、东、南、西方向的分配点运送材料。该公司的卡车用于从制造地点向分配点运送材料。其中有每车能装5吨的大型卡车，运输能力为每天9车次；有每车能装2吨的中型卡车，运输能力为每天12车次，有每车能装1吨的小型卡车，运输能力为每天5车次。某天，城区北、东、南、西四个分配点分别需要材料14吨、10吨、20吨、8吨。每种卡车向各分配点送料一次的费用见下表。公司应如何安排才能在满足需求的条件下使得运料的总费用为最少？（例题数据参见“第7章数据资料”之“卡车运料”工作表）2.运输问题的适当转折【例7.5】（发车送料问题）荣城运19第7章物流管理中的Excel课件20计算过程的设置见图：计算过程的设置见图：21计算结果见图：对话框中按“求解”得到一最小运费的运输方案，满足所有约束要求。最小的总运费：873元。计算结果见图：对话框中按“求解”得到一最小运费的运输方案，满227.3.2供需不等的运输问题求解o【例7.6】设有三个煤矿供应煤炭给四个地区。各煤矿的产量、各地区的需求量以及从各煤矿运送煤炭到各地区的单价如表。试求出将产量分配完，又使总运费最低的煤炭调运方案。（例题数据参见“第7章数据资料”之“供需变动”工作表）单位：（万元/万吨）煤矿地区 甲乙丙丁产量（万吨）A1613221750B1413191560C192023（900）50最低需求（万吨）3070010最高需求（万吨）507030不限7.3.2供需不等的运输问题求解【例7.6】设有三个煤矿供应23第7章物流管理中的Excel课件24计算过程的设置与结果见图：计算过程的设置与结果见图：257.3.3 转运问题【例7.7】已知甲乙两处分别有70吨和55吨物资外运，A、B、C三处各需要物资35、40、50吨，物资可以直接目的地，也可以经中转站和其他地点转运，已知各处间距离（公里）如表。，试制定一个最优分配方案，使总运费最少。（例题数据参见“第7章数据资料”之“转运问题”工作表）7.3.3 转运问题【例7.7】已知甲乙两处分别有70吨和526解：由于问题中所有的产地、销地和中转站都是产地，也是销地，所以可以看做是一个七个产地和销地的产销平衡的运输模型。计算步骤为：1.构建扩大了的单位运价表；2.设定运量限制。转出量限制：由于都可以做为产地，所以甲、乙两地能够转出的量是全部的产量，中转站的转出量也是全部的产量，A、B、C的转出量是保留下各自的需求后的剩余量；转入量限制：由于都可以做为销地，所以甲、乙两地能够转入的量是除自己生产的其余的量，中转站的转入量也是全部的产量，A、B、C的转入量是全部可能的产量；3.构建运输模型表。设定从产地到销地的运量为xij0，其中i,j=1,74.构建目标函数。5.求解。解：由于问题中所有的产地、销地和中转站都是产地，也是销地，所27利用EXCEL实现据有转运问题的运输问题，其步骤如下：第一步：打开“第7章 数据资料”，选择“转运问题”工作表。例题所给条件分布在单元格A3:N10之中；第二步：构建扩大的单位运价模型表和运量限制。1.如图所示，在单元格A13：H20中构建单位运价表。利用EXCEL实现据有转运问题的运输问题，其步骤如下：282.设定转出量和转入量限制。设定转出量：在单元格I13中输入“转出量”，在I14:I20中输入“125、125、125、125、90、85、75”，形成转出量限制；设定转入量：在单元格A21中输入“转入量”，在B21:H21中输入“55、70、125、125、125、125、125”，形成转入量限制；如图所示：2.设定转出量和转入量限制。29第三步：构造运输模型表构建运输模型表模板：选择单元格A13:I21，复制粘贴到单元格A24：I32，删除B25：I32内数值，形成空表；定义约束条件：在单元格I25中输入“=SUM(B25:H25)”，选中单元格I25，按住鼠标左键拖至I31；在单元格B32中输入“=SUM(B25:B31)”，选中单元格B32，按住鼠标左键拖至H32；定义目标函数：在单元格I32中输“=SUMPRODUCT(B25:H31,B14:H20)”；运输模型表如图所示：第三步：构造运输模型表30第四步：线性规划求解选择“工具规划求解”，弹出规划求解对话框，如图所示定义各选项。第四步：线性规划求解31求解，得到线性规划求解结果，如图所示。确定最优方案：在单元格A35:I43中构建最终运输方案表格，如图选择线性规划结果，即为最优运输方案。得甲分配给T2中转站40个单位，分配给C销地30个单位；乙分配给T1中转站55个单位；T1中转站分配给A销地35个单位，C销地20个单位；T2中转站分配给B销地40个单位。求解，得到线性规划求解结果，如图所示。确定最优方案：在单元格327.3.4 多目标运输问题求解o【例7.8】华蔚麻纺公司要从四个生产地向三个需求地发送纺织品，发送每件纺织品的的单价、各生产地的产出量和各地需求量见图单元格A1：E6 所示。现要满足下列要求给出发送货物的方案。由于产品供不应求，所以要从每个产地运出所有的物资，这是绝对约束；其余多个目标和优先级为：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,2,3,)7.3.4 多目标运输问题求解【例7.8】华蔚麻纺公司要从四33无目标线性规划求解过程与结果，如图所示。可以看到，无目标下的运输方案，实际运出量等于生产量，总运费为1100元。无目标线性规划求解过程与结果，如图所示。可以看到，无目标下34第7章物流管理中的Excel课件35具体在Excel模板上的设置步骤见图：具体在Excel模板上的设置步骤见图：36话框中按“求解”得到多目标运输规划的满意解，见下图：需求地B1得到120件，B2得到70件，B3得到110件，x42=0，总费用为1160元，超过无目标运输规划60元满足所有目标约束要求。话框中按“求解”得到多目标运输规划的满意解，见下图：需求地377.4 配货中心的合理选址o【例7.9】兴华公司考虑在四个城市：北京、上海、广州和武汉选择设立配货中心库房，这些库房负责向华北、华中和华南地区发送货物。每个库房每月可发送货物1000件。在北京、上海、广州和武汉设库房每月的成本分别为9万元、5万元、7万元、4万元。华北、华中和，华南地区各地区每月平均需求量分别为1100件、700件、800件。且上海比广州优先考虑。各城市向各地区发送货物的单位费用见表：单位费用华北华中华南固定成本（元）北京20040050090000上海30025045050000广州60040025070000武汉30015020040000需求1100700800公司希望在满足地区需求的前提下使每月总成本最小；要求库房分散在各城市并且不超过3个。上海和广州两者首选上海。（例题数据参见“第7章数据资料”之“库房设立”工作表）7.4 配货中心的合理选址【例7.9】兴华公司考虑在四个城市38上述问题可以归结为这样的线性规划模型：令aij为i建库城市向j地区的单位发货费用（元/件），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城建库；上述问题可以归结为这样的线性规划模型：39具体在Excel模板上的设置步骤见图：具体在Excel模板上的设置步骤见图：40具体在Excel模板上的设置步骤见图：话框中按“求解”，得到一最小运费的运输方案，满足所有约束要求，应当在北京、上海、武汉三城市建立库房，发送方案见图7-39，此时总费用为725000元。具体在Excel模板上的设置步骤见图：话框中按“求解”，得到417.5最大流问题o最大流问题是一类应用极为广泛的问题，是图论中的一种理论与方法，研究网络上的一类最优化问题。例如在交通运输网络中有人流、车流、货物流，供水网络中有水流，金融系统中有现金流，通讯系统中有信息流，等等。o最大流问题的研究密切了图论和运筹学，特别是与线性规划的联系，开辟了图论应用的新途径。o网络最大流问题也可以用EXCEL来实现。7.5最大流问题最大流问题是一类应用极为广泛的问题，是图论中42【例7.10】下图是描述一个水渠系统，其中R1、R2、R3代表三个水库，A、B、C、D、E、F代表水渠的交汇点，T表示水渠终点的一个城市，水渠各段每日允许通过的最大流量（千立方米）分别见表。城市水资源管理部门希望制定一个方案，使每天输送这到城市的水流量最大。ABCDEFTR17365-A6045-D120R2405060B705545E190R3-8070C-7090F130【例7.10】下图是描述一个水渠系统，其中R1、R2、R3代43解：此问题可以利用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”代表结点DT间的最大允许的流量为120；各结点间的容量矩阵如图：解：此问题可以利用EXCEL中提供的规划求解实现44第三步：构建最大流量求解表构造表格：选择单元格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)”；最大流量求解表如图所示：第三步：构建最大流量求解表45第四步：线性规划求解。选择“工具规划求解”，弹出规划求解对话框，并如图所示定义各选项。求解，得到最大流量线性规划求解结果，如图。第四步：线性规划求解。求解，得到最大流量线性规划求解结果，如46如上图所示可知，最大流量为395，最大流量安排方案，如图：如上图所示可知，最大流量为395，最大流量安排方案，如图：47