搜索
通过本章的学习使学生熟练运用Excel软件对物流管理经常用到的模型进行规划求解，提高学生运用现代信息工具解决物流管理中遇到的实际问题的能力。物流系统中许多管理决策问题一般可以形式化表示为一个最优化模型，就是根据一般的理论、方法、设计规范、行业标准等，按具体要求建立一个能体现设计问题的数学模型，再利用计算机和最优化方法自动找出它的最优方案，使问题的解决在某种意义上达到量化的无可争议的程度。目前物流系统中的常见模型：（1）物流需求预测模型；（2）网络物流模型；（3）运输模型等模型。针对上述模型，可采用不同的求解方法进行求解，下面我们主要介绍如何采用Excel软件对建模进行求解。3.1需求预测模型案例1：已知某种物资20032008年的需求量分别为139，142，155，148，160和166，请分别使用算术平均法、移动平均法、加权移动平均法、指数平滑法和长期趋势法预测该物资2009年的需求量。案例2：已知某种物资20062008年各个季度的需求量如表3-1所示，请利用移动平均法(以最近两期的物流需求数据作为预测依据)预测2009年的需求量，并用季节变动预测法预测2009年各个季度的需求量。表3-1案例2数据年份200620072008第一季度45.557.7582.5第二季度432426.25483第三季度286302356.75第四季度29.533.533利用最小二乘法来估计回归方程参数并计算模型的拟合优度的整个求解过程复杂，尤其是多元回归分析手工计算较为困难。利用Excel中的回归分析功能可以将整个求解过程交给计算机处理，从而降低分析的难度，提高预测效率。1.在进行计算前，首先要加载“分析工具库”模块回归分析属于Excel的扩展功能，在使用前需要加载“分析工具库”模块，具体的操作步骤如下：(1)鼠标左键点击菜单栏上的“工具”菜单，在打开的“工具”菜单上点击“加载宏”命令，如图所示。(2)在弹出的“加载宏”对话框中，选中“可用加载宏”区域中的“分析工具库”对应的复选框，然后点击“确定”按钮，如图所示。(3)加载成功后，就可以在“工具”菜单中看到“数据分析”命令，如图所示。图3-1“工具”菜单3-2“加载宏”对话框3-3“数据分析”对话框2.计算步骤（1）录入数据将案例1中2003--2008年的需求量录人到Excel电子表格中的A1：C8区域，如图所示。3-4需求量工作表（2）算术平均法在D1单元格输入“算术平均法预测需求”，在D8单元格输入公式“=AVERAGE(C2：C7)”，按回车键即可得到算术平均法的预测结果。（3）移动平均法以最近两期的物流需求数据作为预测依据。在E1单元格输入“移动平均法预测需求”，在E4单元格输入公式“=AVERAGE(C2：C3)”，按回车键即可得到2005年的需求预测值。左键按住E4单元格右下方的填充柄，向下拉动到E8单元格，即可得到2006-2009年的需求预测值。（4）加权移动平均法1)按公式+1-1=0.6+0.4tttxxx进行预测。2)在F1单元格输入“移动加权平均法预测需求”，在F4单元格输入公式“=0.4*C2+0.6*C3”，按回车键即可得到2005年的需求预测值。左键按住F4单元格右下方的填充柄，向下拉动到F8单元格，即可得到2006-2009年的需求预测值。（5）指数平滑法1)取a等于0.3进行预测。2)在G1单元格输入“指数平滑法预测需求”，在G2单元格输入“139”，在G3单元格输入公式“=0.3*C2+(1-0.3)*G2”，按回车键即可得到2004年的需求预测值。左键按住G3单元格右下方的填充柄，向下拉动到G8单元格，即可得到2005-2009年的需求预测值。（6）长期趋势法1)左键单击菜单栏中的“工具”，在展开的下拉菜单中点击“数据分析”打开“数据分析”对话框，如图所示。选中“回归”后点击“确定”按钮，打开“回归”对话框，如图所示。3-5“数据分析对话框”图3-6“回归”对话框一2)单击“Y值输入区域”对应的参数框右侧的折叠对话框按钮，选择区域C2：C7。单击“X值输人区域”对应的参数框右侧的折叠对话框按钮，选择区域B2：B7。单击“输出区域”对应的参数框右侧的折叠对话框按钮，选择$H$10单元格，如图所示。图3-7“回归”对话框二3)单击“确定”按钮，可以看到回归分析的结果如图所示。其中-10276.93333和5.2分别是和的值。在单元格H1中输入“长期预测法需求”，在单元格H2中输入“=$I$26+$I$27*B2”，按回车键即可得到2003年的需求预测值。左键按住H2单元格右下方的填充柄，向下拉动到H8单元格，即可得到2004-2009年的需求预测值。图3-8“回归”分析结果（7）季节变动预测法1)新建一个工作表，将案例2中2006--2008年的需求量录入到Excel电子表格中的A1：D5区域，如图所示。3-9数据录入工作表2)在A6单元格输入“年度汇总：”，在B6单元格输入公式“=SUM(B2：B5)”，按回车键即可得到2006年的总需求量。左键按住B6单元格右下方的填充柄，向右拉动到D6单元格，即可得到2006--2008年的总需求量。3)在E1单元格输入“平均值：”，在E2单元格输入公式“=AVERAGE(B2：D2)”，按回车键即可得到第一季度的平均需求量。左键按住E2单元格右下方的填充柄，向下拉动到E5单元格，即可得到第二季度到第四季度的总需求量。4)在D7单元格输入“季度平均值：”，在E7单元格输入公式“=AVERAGE(E2：E5)”，按回车键即可得到所有季度的平均需求量。5)在F1单元格输入“S：”，在F2单元格输入公式“=E2／E$7”，按回车键即可得到第一季度的季度指数。左键按住F2单元格右下方的填充柄，向下拉动到F5单元格，即可得到第二季度到第四季度的季度指数。6)在G1单元格输入“2009”，在G2单元格输入公式“=AVERAGE($C$6：$D$6)/4*F2”，按回车键即可得到2009年第一季度的需求预测值。左键按住G2单元格右下方的填充柄，向下拉动到G5单元格，即可得到2009年第二季度到第四季度的需求预测值。3.实验结果与分析(1)记录每种预测方法的预测结果。·1)算术平均法、移动平均法、加权移动平均法、指数平滑法以及长期趋势法预测需求的预测结果如图所示。2)季节变动预测结果如图所示。（2）对比各种预测方法在预测精度上的差异？3.2网络流模型案例：下图是连接某产品产地V1，和销地V7，的交通图。弧(Vi，Vj)表示从Vi到Vj的运输线，弧旁的数字表示这条运输线的最大通过能力Cij。现要求制定一个运输方案，使从V1，运到V7，的产品数量最多。1.关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)开始。）的最优值。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。在创建模型过程中，可以对“规划求解”模型中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。而且约束条件可以引用其他影响目标单元格公式的单元格。使用“规划求解”可通过更改其他单元格来确定某个单元格的最大值或最小值。加载“规划求解”模块具体操作步骤如下：(1)点击“工具”菜单，在打开的“工具”菜单上点击“加载宏”命令。(2)在弹出的“加载宏”对话框中，选中“可用加载宏”区域中的“规划求解”对应的复选框，然后点击“确定”按钮，加载成功后，就可以在“工具”菜单中看到“规划求解”命令。2.计算步骤最大流问题的Excel求解可以按照以下步骤进行：(1)建立问题模型，设过弧的流量为ijf，则1V为sV，7V为tV，模型即为=Maxvvfs.t.23242523234363234546424345254563646456+-0=+--=0++-=0++--=0--=0--=00-++=-0ssssttttttijijffvfffffffffffffffffffffffvffc(2)输入基础数据首先如图所示，在B3：F15区域输入各个节点的距离，其中D4：D15为空白。然后分别定义B4：B15为“从”，C4：C15为“到”，D4：D15为“流量”以及F4：F15为“容量”。最大流基础数据接下来在单元格H3输人“节点”，在单元格I3输入“净流量”；在区域H4：H10输入各节点序列；在单元格I4根据节点相连情况，输入“=D4+D5”，纵向填充至I10，然后输入J5：J9以及K3：K10的相应内容。最后在单元格I15输入“最大流”，J15输入“=D4+D5”，并定义区域$I$4：$I$10为“净流量”，$K$5：$K$9为“供应∕需求”以及$J$15为“最大流”。输人后的结果如图所示。最大流初始值输入(3)规划求解在“工具”菜单下选择“规划求解”子菜单，出现“规划求解参数”对话框后，在“设置目标单元格”处输入“$J$15”，并选择“最大值”，在“可变单元格”处输入“$D$4：$D$15”。在添加约束条件处点击“添加”按钮，出现“添加约束”对话框后，依次填入所有约束条件，具体结果如图所示。最大流规划求解参数最后在“规划求解参数”对话框中点击“选项”按钮，出现“规划求解选项”对话框后，选定“采用线性模型”以及“假定非负”，然后点击“确定”，返回“规划求解参数”对话框，点击“求解”按钮即可求解，见图。最大流规划求解选项3.结果与分析(1)记录实验结果；最终的实验结果如图所示，该网络的最大流是20。(2)分析指出上述项目中哪条路径是瓶颈路径。3.3运输模型案例：某公司有3个分厂，分别设在武汉、东莞和长春。3个分厂生产的产品需要运往华东、华南、华北和华中4个配送中心。3个分厂的每月产能如3-2表所示。某月4个配送中心的需求量如表3-3所示。由于运输里程和交通条件的差异，各分厂到配送中心的运费并不相同，各分厂到各个配送中心的运输费用如表3-4所示。请利用线性规划方法，安排一个合适的物流方案，使得运输费用最低。表3-2产能表单位：吨武汉厂东莞厂长春厂200200240表3-3需求表单位：吨华北华中华东华南160170200100表3-4运费表单位：元∕吨分厂华北华中华东华南武汉厂140026012001500东莞厂245014001600200长春厂1200160017002800请利用线性规划方法，安排一个合适的物流方案，使运输费用最低。1.求解步骤(1)建立数学模型。以1，2，3代替武汉、东莞和长春，a，b，c，d代替华北、华中、华东和华南，设x1a为华北到武汉的运量，其他依此类推，依案例分析可得：111122223333=1400+260+1200+1500+2450+1400+1600+200+1200+1600+1700+2800abcdabcdabcdMinzxxxxxxxxxxxxs.t.111122223333123123123122+++=200+++=200+++=240++=160++=170++=200++=1100,=1,2,3,4;=,,c,dabcdabcdabcdaaabbbcccdddijxxxxxxxxxxxxxxxxxxxxxxxxxijab(2)录入基础数据。将案例中的产能表、需求表和运费表录入Excel工作表中，录入后的效果如图所示。运输问题基础数据在单元格G9输入公式“=SUM(C9：F9)”，按回车键即可得到武汉厂的生产量，然后纵向填充至G11；在单元格C12输入公式“=SUM(C9：C11)”，按回车键即可得到华北的需求量，然后横向填充至F12；在单元格I14输入“=SUMPRODUC