扩展案例产品生产方案的优化设计课件

任务11产品生产方案的优化设计

在限定的生产资源条件下以最低的成本获取最大的利润，就是企业生产的主要目的，因此在进行产品生产之前，企业的生产部门需要对产品的成本、生产时间、销售利润及其他方面的因素进行进行综合评估，从而找到一个能够使生产成本最小而利润最大的生产方案。任务11产品生产方案的优化设计在限定的生产资源条企业产品生产方案优化设计效果企业产品生产方案优化设计效果311.2任务分析11.3

产品生产方案优化设计

拓展实训

综合实践11.1任务情境

目录311.2任务分析11.3产品生产方案优化设计拓11.1任务情境

公司新上三种产品的生产，经考察和初步试生产得知：生产产品1、产品2、产品3的成本分别为55元、85元和115元，每生产一件产品的生产时间分别耗费3分钟、6分钟、7分钟，销售一件产品分别获利50元、70元、95元。现在研究决定，企业为这三种产品最多投入15000元的原材料经费，机器设备每天运转时间不能超过15个小时，同时根据市场需求产品1每天产量不得少于60件，产品2每天产量不得少于45件，产品3每天产量不得少于60件。生产部主任要求刚工作不久的小张立即设计出下一步最优的生产方案，也就是根据现有的生产条件和市场需求如何分配三种产品的生产比例才能获得最大的利润。这么多复杂的条件，单靠直观的判断很难找到最优的方案，这可愁坏了小张，他知道公司的小王懂得多，就去请教，小王一看，简单！很快就解决了。小王是怎么帮助小张快速解决的呢？让我们也跟他学一招吧！11.1任务情境

公司新上三种产品的生产，经考察和知识目标和能力目标知识目标：掌握Excel加载附加分析工具的方法。掌握规划求解的使用方法。掌握函数SUMPRODUCT（）的使用方法。能力目标：能够根据实际规划问题进行分析，建立规划模型。能够应用规划求解的方法求得规划问题的最优解。5知识目标和能力目标知识目标：511.2任务分析

这是在生产管理和经营决策过程中，如何合理地利用有限的人力、物力、财力等资源，得到最佳的经济效果，即达到产量最高、利润最大、成本最小、资源消耗最少等目标的问题，这类问题可以统称为规划问题，解决这样的规划问题应用Excel的规划求解工具，可以方便快捷地得到问题的最优解。

经过分析，设计出这三种产品的最优生产方案需要进行以下工作：根据问题的描述建立规划模型。根据规划模型建立工作表加载规划求解工具应用规划求解工具求解分析规划求解结果。611.2任务分析这是在生产管理和11.3产品生产方案优化设计711.3.3分析规划求解结果11.3.1建立问题的规划模型11.3.2应用规划求解求得最优生产方案11.3产品生产方案优化设计711.3.3分析规划求解结果111.3.1建立问题的规划模型1.分析问题并将生产条件整理如下表所示8

11.3.1建立问题的规划模型1.分析问题并将生产条11.3.1建立问题的规划模型2.将约束条件整理如下表所示9

11.3.1建立问题的规划模型2.将约束条件整理如下11.3.1建立问题的规划模型3.建立问题的规划模型

假设如果在现有条件下获得最大利润时产品1、产品2、产品3每天的产量应为X1、X2、X3，总利润为S，则可以根据上表的约束条件建立规划模型如下：1011.3.1建立问题的规划模型3.建立问题的规划模型11.3产品生产方案优化设计1111.3.1建立问题的规划模型11.3.3分析规划求解结果11.3.2应用规划求解求得最优生产方案11.3产品生产方案优化设计1111.3.1建立问题的规划11.3.2应用规划求解求得最优生产方案121.加载规划求解工具步骤1：单击窗口左上角的【文件】按钮，在弹出的菜单中执行【选项】命令。11.3.2应用规划求解求得最优生产方案121.加载规划11.3.2应用规划求解求得最优生产方案131.加载规划求解工具步骤2：弹出【Excel选项】对话框，单击【加载项】选项，然后在【管理】下拉列表框中的选择【Excel加载项】选项，单击【转到】按钮，如左图所示，弹出【加载宏】对话框，如右图所示。11.3.2应用规划求解求得最优生产方案131.加载规划11.3.2应用规划求解求得最优生产方案141.加载规划求解工具步骤3：在【加载宏】对话框中勾选【规划求解加载项】复选项，然后单击【确定】按钮。加载规划求解加载宏后，在【数据】选项卡的【分析】选项组中就出现【规划求解】命令了，如下图所示。11.3.2应用规划求解求得最优生产方案141.加载规划11.3.2应用规划求解求得最优生产方案152.建立工作表步骤1：新建工作簿“产品生产方案优化设计.xlsx”，把Sheet1重命名为“规划求解确定最优生产方案”。步骤2：输入规划求解的基本项目及约束条件，并对工作表格式化，添加边框和底纹修饰使数据更清晰，如下图所示。11.3.2应用规划求解求得最优生产方案152.建立工作11.3.2应用规划求解求得最优生产方案162.建立工作表步骤3：输入计算毛利合计公式毛利合计=单位利润×产量在C7单元格中输入公式：“=C5*C6”，按回车键计算产品1的毛利合计，如下图所示。向右拖动填充柄，复制公式到E7单元格，计算产品2和产品3的毛利合计值。

11.3.2应用规划求解求得最优生产方案162.建立工作11.3.2应用规划求解求得最优生产方案172.建立工作表步骤4：输入计算实际生产成本公式实际生产成本=产品1每件耗费成本×产量+产品2每件耗费成本×产量+产品3每件耗费成本×产量，选中C15单元格，输入以下函数：=SUMPRODUCT(C3:E3,C6:E6)。其功能等价于公式：=C3*C6+D3*D6+E3*E6。按Enter键计算实际生产成本。11.3.2应用规划求解求得最优生产方案172.建立工作11.3.2应用规划求解求得最优生产方案182.建立工作表步骤4：输入计算实际生产成本公式如图所示。

11.3.2应用规划求解求得最优生产方案182.建立工作11.3.2应用规划求解求得最优生产方案192.建立工作表步骤5：输入计算实际生产时间公式实际生产时间=产品1每件生产时间×产量+产品2每件生产时间×产量+产品3每件生产时间×产量选中C16单元格，输入以下函数：=SUMPRODUCT(C4:E4,C6:E6)其功能等价于公式=C4*C6+D4*D6+E4*E6。按Enter键计算实际生产时间。

11.3.2应用规划求解求得最优生产方案192.建立工作11.3.2应用规划求解求得最优生产方案202.建立工作表步骤5：输入计算实际生产时间公式如图所示

11.3.2应用规划求解求得最优生产方案202.建立工作11.3.2应用规划求解求得最优生产方案212.建立工作表步骤6：输入计算利润的公式利润=产品1毛利合计+产品2毛利合计+产品3毛利合计选中E12单元格，插入求和函数：=SUM(C7:E7)，按Enter键计算利润。步骤7：为了和最优解比较利润大小，输入约束条件中产量最小值即60、45、60作为产量初值，观察记录利润结果为“11850”，如图所示。

11.3.2应用规划求解求得最优生产方案212.建立工作11.3.2应用规划求解求得最优生产方案223.应用规划求解，求得利润最大化生产方案

步骤1：选中目标单元格C17，切换到【数据】选项卡，单击【分析】选项组下的【规划求解】按钮，如下图所示，弹出【规划求解参数】对话框，如下图所示。11.3.2应用规划求解求得最优生产方案223.应用规划11.3.2应用规划求解求得最优生产方案233.应用规划求解，求得利润最大化生产方案

步骤2：设置目标函数11.3.2应用规划求解求得最优生产方案233.应用规划11.3.2应用规划求解求得最优生产方案243.应用规划求解，求得利润最大化生产方案

步骤3：设置可变单元格，即决策变量步骤4：设置约束条件①添加实际成本约束条件，实际生产成本应小于等于生产成本约束。单击【约束】文本框旁的【添加】按钮，弹出【添加约束】对话框，如下图所示，单击【单元格引用位置】文本框，在工作表中选择C15单元格，在中间的下拉列表中选择【<=】选项，单击右侧的【约束值】文本框，在工作表中选择C11单元格。单击【添加】按钮，继续弹出【添加约束】对话框，添加其他条件。11.3.2应用规划求解求得最优生产方案243.应用规划11.3.2应用规划求解求得最优生产方案253.应用规划求解，求得利润最大化生产方案

步骤4：设置约束条件②添加实际生产时间约束条件，实际生产时间应小于等于生产时间约束。在弹出的【添加约束】对话框中，按上述方法设置实际生产时间约束条件如下图所示。然后单击【添加】按钮，再次弹出【添加约束】对话框。11.3.2应用规划求解求得最优生产方案253.应用规划11.3.2应用规划求解求得最优生产方案263.应用规划求解，求得利润最大化生产方案

步骤4：设置约束条件③添加产量约束条件产品产量应大于等于产量约束值且必须为整数。11.3.2应用规划求解求得最优生产方案263.应用规划11.3.2应用规划求解求得最优生产方案273.应用规划求解，求得利润最大化生产方案

步骤4：设置约束条件所有约束条件添加完成后单击【确定】按钮，返回【规划求解参数】对话框，如下图所示。在【约束】列表框中可以看到所有添加的条件。11.3.2应用规划求解求得最优生产方案273.应用规划11.3.2应用规划求解求得最优生产方案283.应用规划求解，求得利润最大化生产方案

步骤5：在【规划求解参数】对话框中，单击【求解】按钮，弹出【规划求解结果】对话框，如下图所示11.3.2应用规划求解求得最优生产方案283.应用规划11.3.2应用规划求解求得最优生产方案293.应用规划求解，求得利润最大化生产方案步骤6：在【规划求解结果】对话框中，选择【保留规划求解的解】选项，并在【报告】列表框中选择【运算结果报告】选项，单击【确定】按钮可以看到规划求解后的结果，如下图所示。并生成“运算结果报告1”工作表。11.3.2应用规划求解求得最优生产方案293.应用规划11.3

产品生产方案优化设计3011.3.2应用规划求解求得最优生产方案11.3.1建立问题的规划模型11.3.3分析规划求解结果11.3产品生产方案优化设计3011.3.2应用规划11.3.3分析规划求解结果311．

分析运算结果报告运算结果报告1”工作表11.3.3分析规划求解结果311．分析运算结果报告11.3.3分析规划求解结果321．

分析运算结果报告从运算结果报告1工作表中可以看到：“目标单元格（最大值）”初值“11850”，终值“12350”。“可变单元格”三种产品产量的初值和终值。“约束”列表中，实际生产成本未达到限制值，也就是计划投入成本的还剩余425元，实际生产时间已经达到限制值15小时。根据以上信息可以得出以下结论：生产时间再增加一个小时，让投入的成本达到限制值则会使利润得到更大的提高。11.3.3分析规划求解结果321．分析运算结果报告11.3.3分析规划求解结果332．修改生产时间约束条件为16小时，即960分钟，重新规划求解，结果如图所示。可以看到实际生产时间只要每天增加21分钟，每天利润就又增加12700-12350=350元，实际成本达到14960，几乎达到限制值，这可真正做到了以有限的成本得到最大的利润。11.3.3分析规划求解结果332．修改生产时间约束条件

拓展实训

34实训1：混合饲料的最低成本配方设计要求如下：一家企业要生产某种混合饲料，规定所含营养成分蛋白质至少15%，脂肪至少4.5%，淀粉至少30%，但是维生素D不能超过10%，由四种原料混合而成，其中原料甲成本每吨480元，原料乙成本每吨40元，原料丙成本每吨300元，，原料丁成本每吨450元，每种原料所含的各种营养成分如表所示。设计生产这种混合饲料的配方，既能保证混合饲料的各营养成分含量，又能使成本最低，从而获取更大的利润。拓展实训

34实训1：混合饲料的最低成本配方设计要求

拓展实训

35实训1：混合饲料的最低成本配方设计拓展实训

35实训1：混合饲料的最低成本配方设计

拓展实训

36求解结果拓展实训

36求解结果

拓展实训

37实训2：最低运输成本方案设计要求如下：假设一物流运输公司把A、B、C三家公司生产的某种产品，分别运往青岛、北京、上海、大连4个地区，三家公司的年产量分别为10000吨、12000吨、和13000吨。这种产品在四个地区的需求量分别为5800吨、8000吨、8800吨、和9000吨。假设从各家公司运往四个地区的的成本是已知的，设计物流公司即能满足市场需求和产品生产公司的产量要求又能使运输成本最低的运输方案。也就是求物流公司分别将从三家公司运输多少产品到四个地区，即能满足市场需求和产品生产公司的产量要求又能使运输成本最低。拓展实训

37实训2：最低运输成本方案设计要求如下：

拓展实训

38实训3：最低运输成本方案设计要求如下：

拓展实训

38实训3：最低运输成本方案设计要求如下：

拓展实训

39实训3：企业最佳生产方案设计要求如下：

某企业要同时生产A、B两种产品，企业每月的生产能力是50000小时，生产一件A产品需要10小时，生产一件B产品需要18小时。企业每销售一件A产品得到利润2000元，每销售一件B产品得到利润3000元。生产产品A、B都需要用到一个关键部件X，每件A产品需用6个这种关键部件X，每件B产品需用9个。这种关键部件X由另一家企业提供，每月可以提供27000个，根据以上情况，该如何合理计划每月A、B两种产品的产量企业才能获得最大利润？拓展实训

39实训3：企业最佳生产方案设计要求如下：

综合实践

40对某个生产厂家展开调研，了解企业的产品种类、生产一件产品的耗费时间、销售一件产品的利润、及企业的生产能力、企业为该产品的投资情况等，应用所学到的知识为该企业在有限的生产资源条件限制下，获取更大的利润做些贡献。综合实践

40对某个生产厂家展开调研，任务11产品生产方案的优化设计

在限定的生产资源条件下以最低的成本获取最大的利润，就是企业生产的主要目的，因此在进行产品生产之前，企业的生产部门需要对产品的成本、生产时间、销售利润及其他方面的因素进行进行综合评估，从而找到一个能够使生产成本最小而利润最大的生产方案。任务11产品生产方案的优化设计在限定的生产资源条企业产品生产方案优化设计效果企业产品生产方案优化设计效果4311.2任务分析11.3

产品生产方案优化设计

拓展实训

综合实践11.1任务情境

目录311.2任务分析11.3产品生产方案优化设计拓11.1任务情境

公司新上三种产品的生产，经考察和初步试生产得知：生产产品1、产品2、产品3的成本分别为55元、85元和115元，每生产一件产品的生产时间分别耗费3分钟、6分钟、7分钟，销售一件产品分别获利50元、70元、95元。现在研究决定，企业为这三种产品最多投入15000元的原材料经费，机器设备每天运转时间不能超过15个小时，同时根据市场需求产品1每天产量不得少于60件，产品2每天产量不得少于45件，产品3每天产量不得少于60件。生产部主任要求刚工作不久的小张立即设计出下一步最优的生产方案，也就是根据现有的生产条件和市场需求如何分配三种产品的生产比例才能获得最大的利润。这么多复杂的条件，单靠直观的判断很难找到最优的方案，这可愁坏了小张，他知道公司的小王懂得多，就去请教，小王一看，简单！很快就解决了。小王是怎么帮助小张快速解决的呢？让我们也跟他学一招吧！11.1任务情境

公司新上三种产品的生产，经考察和知识目标和能力目标知识目标：掌握Excel加载附加分析工具的方法。掌握规划求解的使用方法。掌握函数SUMPRODUCT（）的使用方法。能力目标：能够根据实际规划问题进行分析，建立规划模型。能够应用规划求解的方法求得规划问题的最优解。45知识目标和能力目标知识目标：511.2任务分析

这是在生产管理和经营决策过程中，如何合理地利用有限的人力、物力、财力等资源，得到最佳的经济效果，即达到产量最高、利润最大、成本最小、资源消耗最少等目标的问题，这类问题可以统称为规划问题，解决这样的规划问题应用Excel的规划求解工具，可以方便快捷地得到问题的最优解。

经过分析，设计出这三种产品的最优生产方案需要进行以下工作：根据问题的描述建立规划模型。根据规划模型建立工作表加载规划求解工具应用规划求解工具求解分析规划求解结果。4611.2任务分析这是在生产管理和11.3产品生产方案优化设计4711.3.3分析规划求解结果11.3.1建立问题的规划模型11.3.2应用规划求解求得最优生产方案11.3产品生产方案优化设计711.3.3分析规划求解结果111.3.1建立问题的规划模型1.分析问题并将生产条件整理如下表所示48

11.3.1建立问题的规划模型1.分析问题并将生产条11.3.1建立问题的规划模型2.将约束条件整理如下表所示49

11.3.1建立问题的规划模型2.将约束条件整理如下11.3.1建立问题的规划模型3.建立问题的规划模型

假设如果在现有条件下获得最大利润时产品1、产品2、产品3每天的产量应为X1、X2、X3，总利润为S，则可以根据上表的约束条件建立规划模型如下：5011.3.1建立问题的规划模型3.建立问题的规划模型11.3产品生产方案优化设计5111.3.1建立问题的规划模型11.3.3分析规划求解结果11.3.2应用规划求解求得最优生产方案11.3产品生产方案优化设计1111.3.1建立问题的规划11.3.2应用规划求解求得最优生产方案521.加载规划求解工具步骤1：单击窗口左上角的【文件】按钮，在弹出的菜单中执行【选项】命令。11.3.2应用规划求解求得最优生产方案121.加载规划11.3.2应用规划求解求得最优生产方案531.加载规划求解工具步骤2：弹出【Excel选项】对话框，单击【加载项】选项，然后在【管理】下拉列表框中的选择【Excel加载项】选项，单击【转到】按钮，如左图所示，弹出【加载宏】对话框，如右图所示。11.3.2应用规划求解求得最优生产方案131.加载规划11.3.2应用规划求解求得最优生产方案541.加载规划求解工具步骤3：在【加载宏】对话框中勾选【规划求解加载项】复选项，然后单击【确定】按钮。加载规划求解加载宏后，在【数据】选项卡的【分析】选项组中就出现【规划求解】命令了，如下图所示。11.3.2应用规划求解求得最优生产方案141.加载规划11.3.2应用规划求解求得最优生产方案552.建立工作表步骤1：新建工作簿“产品生产方案优化设计.xlsx”，把Sheet1重命名为“规划求解确定最优生产方案”。步骤2：输入规划求解的基本项目及约束条件，并对工作表格式化，添加边框和底纹修饰使数据更清晰，如下图所示。11.3.2应用规划求解求得最优生产方案152.建立工作11.3.2应用规划求解求得最优生产方案562.建立工作表步骤3：输入计算毛利合计公式毛利合计=单位利润×产量在C7单元格中输入公式：“=C5*C6”，按回车键计算产品1的毛利合计，如下图所示。向右拖动填充柄，复制公式到E7单元格，计算产品2和产品3的毛利合计值。

11.3.2应用规划求解求得最优生产方案162.建立工作11.3.2应用规划求解求得最优生产方案572.建立工作表步骤4：输入计算实际生产成本公式实际生产成本=产品1每件耗费成本×产量+产品2每件耗费成本×产量+产品3每件耗费成本×产量，选中C15单元格，输入以下函数：=SUMPRODUCT(C3:E3,C6:E6)。其功能等价于公式：=C3*C6+D3*D6+E3*E6。按Enter键计算实际生产成本。11.3.2应用规划求解求得最优生产方案172.建立工作11.3.2应用规划求解求得最优生产方案582.建立工作表步骤4：输入计算实际生产成本公式如图所示。

11.3.2应用规划求解求得最优生产方案182.建立工作11.3.2应用规划求解求得最优生产方案592.建立工作表步骤5：输入计算实际生产时间公式实际生产时间=产品1每件生产时间×产量+产品2每件生产时间×产量+产品3每件生产时间×产量选中C16单元格，输入以下函数：=SUMPRODUCT(C4:E4,C6:E6)其功能等价于公式=C4*C6+D4*D6+E4*E6。按Enter键计算实际生产时间。

11.3.2应用规划求解求得最优生产方案192.建立工作11.3.2应用规划求解求得最优生产方案602.建立工作表步骤5：输入计算实际生产时间公式如图所示

11.3.2应用规划求解求得最优生产方案202.建立工作11.3.2应用规划求解求得最优生产方案612.建立工作表步骤6：输入计算利润的公式利润=产品1毛利合计+产品2毛利合计+产品3毛利合计选中E12单元格，插入求和函数：=SUM(C7:E7)，按Enter键计算利润。步骤7：为了和最优解比较利润大小，输入约束条件中产量最小值即60、45、60作为产量初值，观察记录利润结果为“11850”，如图所示。

11.3.2应用规划求解求得最优生产方案212.建立工作11.3.2应用规划求解求得最优生产方案623.应用规划求解，求得利润最大化生产方案

步骤1：选中目标单元格C17，切换到【数据】选项卡，单击【分析】选项组下的【规划求解】按钮，如下图所示，弹出【规划求解参数】对话框，如下图所示。11.3.2应用规划求解求得最优生产方案223.应用规划11.3.2应用规划求解求得最优生产方案633.应用规划求解，求得利润最大化生产方案

步骤2：设置目标函数11.3.2应用规划求解求得最优生产方案233.应用规划11.3.2应用规划求解求得最优生产方案643.应用规划求解，求得利润最大化生产方案

步骤3：设置可变单元格，即决策变量步骤4：设置约束条件①添加实际成本约束条件，实际生产成本应小于等于生产成本约束。单击【约束】文本框旁的【添加】按钮，弹出【添加约束】对话框，如下图所示，单击【单元格引用位置】文本框，在工作表中选择C15单元格，在中间的下拉列表中选择【<=】选项，单击右侧的【约束值】文本框，在工作表中选择C11单元格。单击【添加】按钮，继续弹出【添加约束】对话框，添加其他条件。11.3.2应用规划求解求得最优生产方案243.应用规划11.3.2应用规划求解求得最优生产方案653.应用规划求解，求得利润最大化生产方案

步骤4：设置约束条件②添加实际生产时间约束条件，实际生产时间应小于等于生产时间约束。在弹出的【添加约束】对话框中，按上述方法设置实际生产时间约束条件如下图所示。然后单击【添加】按钮，再次弹出【添加约束】对话框。11.3.2应用规划求解求得最优生产方案253.应用规划11.3.2应用规划求解求得最优生产方案663.应用规划求解，求得利润最大化生产方案

步骤4：设置约束条件③添加产量约束条件产品产量应大于等于产量约束值且必须为整数。11.3.2应用规划求解求得最优生产方案263.应用规划11.3.2应用规划求解求得最优生产方案673.应用规划求解，求得利润最大化生产方案

步骤4：设置约束条件所有约束条件添加完成后单击【确定】按钮，返回【规划求解参数】对话框，如下图所示。在【约束】列表框中可以看到所有添加的条件。11.3.2应用规划求解求得最优生产方案273.应用规划11.3.2应用规划求解求得最优生产方案683.应用规划求解，求得利润最大化生产方案

步骤5：在【规划求解参数】对话框中，单击【求解】按钮，弹出【规划求解结果】对话框，如下图所示11.3.2应用规划求解求得最优生产方案283.应用规划11.3.2应用规划求解求得最优生产方案693.应用规划求解，求得利润最大化生产方案步骤6：在【规划求解结果】对话框中，选择【保留规划求解的解】选项，并在【报告】列表框中选择【运算结果报告】选项，单击【确定】按钮可以看到规划求解后的结果，如下图所示。并生成“运算结果报告1”工作表。11.3.2应用规划求解求得最优生产方案293.应用规划11.3

产品生产方案优化设计7011.3.2应用规划求解求得最优生产方案11.3.1建立问题的规划模型11.3.3分析规划求解结果11.3产品生产方案优化设计3011.3.2应用规划11.3.3分析规划求解结果711．

分析运算结果报告运算结果报告1”工作表11.3.3分析规划求解结果311．分析运算结果报告11.3.3分析规划求解结果721．

分析运算结果报告从运算结果报告1工作表中可以看到：“目标单元格（最大值）”初值“11850”，终值“12350”。“可变单元格”三种产品产量的初值和终值。“约束”列表中，实际生产成本未达到限制值，也就是计划投入成本的还剩余425元，实际生产时间已经达到限制值15小时。根据以上信息可以得出以下结论：生产时间再增加一个小时，让投入的成本达到限制值则会使利润得到更大的提高。11.3.3分析规划求解结果321．分析运算结果报告11.3.3分析规划求解结果732．修改生产时间约束条件为16小时，即960分钟，重新规划求解，结果如图所示。可以看到实际生产时间只要每天增加21分钟，每天利润就又增加12700-12350=350元，实际