《物流数据分析》课件-任务5.4 仓库订货决策

任务5.4仓库订货决策【任务目的】1．掌握经济订货批量模型的原理和求解步骤；2．熟练运用Excel进行规划求解建模并求解经济批量；3．掌握Excel模拟运算表的使用。【知识链接】仓储管理中随着仓库物资的不断出库，需要对库存物资进行订货补充，如果一次性订货量过大，会大大增加库存物资管理成本，而一次订货量过小，又要满足需求必然会增加订货的次数，而次数的增加又会增加订货的成本，因此，在仓库订货中，存在一个最经济的订货量，这个订货量叫做经济订货批量（EOQ）。假设在不允许缺货的情况下，年仓储总成本、保管成本、订货成本、购置成本与订购批量之间的关系可按图来表述。仓储管理中的定量订货法是指当库存量下降到预定的最低库存量（这个最低库存量叫做订货点）时，按规定数量（一般以经济批量为标准）进行订货补充的一种库存控制方法。在仓储管理中，仓储总成本由物品总价值、订货成本、储存成本三个部分组成。用公式表达为TC——总成本D——商品的年需求量C——商品的单价Q*——经济订货批量S——每次订货的费用H——单位商品的储存成本【工作任务】某零售商的电冰箱年需求量为16000件，每次订货成本为600元，单位存货的年储存成本为30元，现需确定以下问题：1．每次订货量为600时的订货成本、年储存成本、年总成本；2．经济订货批量(EOQ)及EOQ下的年订货成本、年储存成本、年总成本；3．当订货量在200~1500范围内变化时，以100为步长进行变化时，年订货量、年订货成本、年储存成本以及年总成本的变化情况，并用图表进行展示。【实施步骤】1．输入数据、建立模型2．经济订货批量求解3．建立模拟运算表4．制作成本随订货批量变化的图表1．输入数据、建立模型新建一个工作表，输入如图所示的基础数据。在C7单元中输入公式“=C3*C2/C6”，在C8单元格中输入公式“=C4*C6/2”，在C9单元格中输入公式“=C7+C8”；在C11单元格中输入公式“=SQRT(2*C2*C3/C4)”，在C12单元格中输入公式“=C3*C2/C11”，在C13单元中输入公式“=C4*C11/2”，在C14单元格中输入公式“=C12+C13”，如图所示。计算结果显示，当订货量为600的时候，订货成本为16000、年储存成本为9000、年总成本为25000。2．经济订货批量求解选择“文件”选项卡，选择“选项”，进入“Excel选项”对话框，如图所示。在“Excel选项”对话框中切换到“加载项”页，点击下方“转到”按钮，在“加载项”对话框中勾选“规划求解加载项”，如左图所示，点击“确定”完成加载。单击“数据”选项卡下“分析”组中的“规划求解”，在“规划求解参数”对话框中设置目标单元格为“$C$14”，目标取“最小值”，“通过更改可变单元格”设置为“$C$11”，其余参数取默认值，如右图所示。单击“求解”即可完成计算，计算结果如图所示：经济订货批量（EOQ）为800，在该订货批量下，年订货成本为12000，年储存成本为12000，年总成本为24000。3．建立模拟运算表在单元格F2到I2分别输入“订货量”、“年订货成本”、“年储存成本”、“年总成本”；在F3单元格中输入公式“=C6”、在G3单元格中输入公式“=C7”、在H3单元格中输入公式“=C8”，在I3单元格中输入公式“=C9”；在F4单元中输入值“200”，然后纵向填充至F17，步长值为100，如图所示。选定区域F3:I17，选择“数据”选项卡下的“模拟分析”下拉列表中的“模拟运算表”，在“模拟运算表”对话框中“输入引用列的单元格”中输入“$C$6”，如图所示。单击“确定”可计算出在不同订货批量下的年订货成本、年储存成本、年总成本计算结果，如图所示。可以观察到，当订货批量取800的时候，年总成本最低，这与上一步规划求解的结果一致。思考与点拨：模拟运算表的作用是什么？Excel中的模拟运算表分为单变量模拟运算表和双变量模拟运算表，前者用于分析一个变量值的变化对公式运算结果的影响，后者分析两个变量值同时变化对公式运算结果的影响。本任务中仅有订货量一个变量，属于单变量模拟运算表。任务中我们还发现，规划求解只给出了满足目标的最优订货批量，但模拟运算表还能直观看出不同订货批量下的仓储成本变化趋势。4．制作成本随订货批量变化的图表选定区域F2:I2以及区域F4:I17，单击“插入”选项卡下“图表”组中“带直线的散点图”，插入图表后将图表标题更改为“仓储成本随订货批量变化趋势”，如图所示。【任务小结】经济订货批量（EOQ）是实现仓储总成本最小的订货批量，任务中仓储总成本由订货成本和储存成本两个效益背反的部分构成，通过Excel的规划求解功能来自动调整订货批量的值来观察仓储总成本是否达到最小，当仓储总成本达到最小值时的订货批量即为经济订货批量，其计算结果与套用公式计