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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2016/5/21,#,任务,11,产品生产方案的优化设计,在,限定的生产资源条件下以最低的成本获取最大的利润，就是企业生产的主要目的，因此在进行产品生产之前，企业的生产部门需要对产品的成本、生产时间、销售利润及其他方面的因素进行进行综合评估，从而找到一个能够使生产成本最小而利润最大的生产方案。,企业产品生,产,产方案优化设计效果,3,11.2,任务分析,11.3,产品生产方案优,化,化设计,拓 展 实,训,训,综 合 实,践,践,11.1,任务情境,目 录,11.1,任务情境,公司新上三种产,品,品的生产，,经,经考察和初,步,步试生产得,知,知：生产产,品,品,1,、产品,2,、产品,3,的成本分别,为,为,55,元、,85,元和,115,元，每生产,一,一件产品的,生,生产时间分,别,别耗费,3,分钟、,6,分钟、,7,分钟，销售,一,一件产品分,别,别获利,50,元、,70,元、,95,元。现在研,究,究决定，企,业,业为这三种,产,产品最多投,入,入,15 000,元的原材料,经,经费，机器,设,设备每天运,转,转时间不能,超,超过,15,个小时，同,时,时根据市场,需,需求产品,1,每天产量不,得,得少于,60,件，产品,2,每天产量不,得,得少于,45,件，产品,3,每天产量不,得,得少于,60,件。生产部,主,主任要求刚,工,工作不久的,小,小张立即设,计,计出下一步,最,最优的生产,方,方案，也就,是,是根据现有,的,的生产条件,和,和市场需求,如,如何分配三,种,种产品的生,产,产比例才能,获,获得最大的,利,利润。这么,多,多复杂的条,件,件，单靠直,观,观的判断很,难,难找到最优,的,的方案，这,可,可愁坏了小,张,张，他知道,公,公司的小王,懂,懂得多，就,去,去请教，小,王,王一看，简,单,单！很快就,解,解决了。小,王,王是怎么帮,助,助小张快速,解,解决的呢？,让,让我们也跟,他,他学一招吧,！,！,知识目标和,能,能力目标,知识目标：,掌握,Excel,加载附加分,析,析工具的方,法,法。,掌握规划求,解,解的使用方,法,法。,掌握函数,SUMPRODUCT,（）的使用,方,方法。,能力目标：,能够根据实,际,际规划问题,进,进行分析，,建,建立规划模,型,型。,能够应用规,划,划求解的方,法,法求得规划,问,问题的最优,解,解。,5,11.2,任务分析,这是在生产管,理,理和经营决,策,策过程中，,如,如何合理地,利,利用有限的,人,人力、物力,、,、财力等资,源,源，得到最,佳,佳的经济效,果,果，即达到,产,产量最高、,利,利润最大、,成,成本最小、,资,资源消耗最,少,少等目标的,问,问题，这类,问,问题可以统,称,称为规划问,题,题，解决这,样,样的规划问,题,题应用,Excel,的规划求解,工,工具，可以,方,方便快捷地,得,得到问题的,最,最优解。,经过分析，设计,出,出这三种产,品,品的最优生,产,产方案需要,进,进行以下工,作,作：,根据问题的,描,描述建立规,划,划模型。,根据规划模,型,型建立工作,表,表,加载规划求,解,解工具,应用规划求,解,解工具求解,分析规划求,解,解结果。,6,11.3,产品生产方,案,案优化设计,7,11.3.3,分析规划求,解,解结果,11.3.1,建立问题的,规,规划模型,11.3.2,应用规划求,解,解求得最优,生,生产方案,11.3.1,建立问题的,规,规划模型,1.,分析问题并,将,将生产条件,整,整理如下表,所,所示,8,11.3.1,建立问题的,规,规划模型,2.,将约束条件,整,整理如下表,所,所示,9,11.3.1,建立问题的,规,规划模型,3.,建立问题的,规,规划模型,假设如果在,现,现有条件下,获,获得最大利,润,润时产品,1,、,产,产,品,品,2,、,产,产,品,品,3,每,天,天,的,的,产,产,量,量,应,应,为,为,X1,、,X2,、,X3,，,总,总,利,利,润,润,为,为,S,，,则,则,可,可,以,以,根,根,据,据,上,上,表,表,的,的,约,约,束,束,条,条,件,件,建,建,立,立,规,规,划,划,模,模,型,型,如,如,下,下,：,：,10,11.3,产,品,品,生,生,产,产,方,方,案,案,优,优,化,化,设,设,计,计,11,11.3.1,建,立,立问,题,题,的,的,规,规,划,划,模,模,型,型,11.3.3,分,析,析规,划,划,求,求,解,解,结,结,果,果,11.3.2,应,用,用,规,规,划,划,求,求,解,解,求,求,得,得,最,最,优,优,生,生,产,产,方,方,案,案,11.3.2,应,用,用,规,规,划,划,求,求,解,解,求,求,得,得,最,最,优,优,生,生,产,产,方,方,案,案,12,1.,加,载,载,规,规,划,划,求,求,解,解,工,工,具,具,步,骤,骤,1,：,：,单,击,击,窗,窗,口,口,左,左,上,上,角,角,的,的,【,【,文,文,件,件,】,】,按,按,钮,钮,，,，,在,在,弹,弹,出,出,的,的,菜,菜,单,单,中,中,执,执,行,行,【,【,选,选,项,项,】,】,命,命,令,令,。,。,11.3.2,应,用,用,规,规,划,划,求,求,解,解,求,求,得,得,最,最,优,优,生,生,产,产,方,方,案,案,13,1.,加,载,载,规,规,划,划,求,求,解,解,工,工,具,具,步,骤,骤,2,：,：,弹,出,出,【,【,Excel,选,项,项,】,】,对,对,话,话,框,框,，,，,单,单,击,击,【,【,加,加,载,载,项,项,】,】,选,选,项,项,，,，,然,然,后,后,在,在,【,【,管,管,理,理,】,】,下,下,拉,拉,列,列,表,表,框,框,中,中,的,的,选,选,择,择,【,【,Excel,加,载,载,项,项,】,】,选,选,项,项,，,，,单,单,击,击,【,【,转,转,到,到,】,】,按,按,钮,钮,，,，如,左,图,所,所示,，,，,弹,弹,出,出,【,【,加,加,载,载,宏,宏,】,】,对,对,话,话,框,框，,如,右,右,图,图所示。,11.3.2,应,用,用,规,规,划,划,求,求,解,解,求,求,得,得,最,最,优,优,生,生,产,产,方,方,案,案,14,1.,加,载,载,规,规,划,划,求,求,解,解工,具,具,步,骤,骤,3,：,：,在【加载宏】对,话,话框中勾选【规,划,划求解加载项】,复,复选项，然后单,击,击【确定】按钮。,加载规划求解加载宏,后,后，在【数据】,选,选项卡的【分析,】,】选项组中就出,现,现【规划求解】,命,命令了，如,下,图所示。,11.3.2,应用规划求解求,得,得最优生产方案,15,2.,建立工作表,步骤,1：,新建工作簿“产,品,品生产方案优化,设,设计,.xlsx,”，把,Sheet1,重命名为“规划,求,求解确定最优生,产,产方案”。,步骤,2：,输入规划求解的,基,基本项目及约束,条,条件，并对工作,表,表格式化，添加,边,边框和底纹修饰,使,使数据更清晰，如,下,图所示。,11.3.2,应用规划求解求,得,得最优生产方案,16,2.,建立工作表,步骤,3：,输入计算毛利合,计,计公式,毛利合计,=,单位利润,产量,在,C7,单元格,中,输入公式：“,=C5*C6,”,，,按回车键计算产品,1,的毛利合计，如,下,图所示。向右拖动填,充,充柄，复制公式,到,到,E7,单元格，计算产,品,品,2,和产品,3,的毛利合计值。,11.3.2,应用规划求解求,得,得最优生产方案,17,2.,建立工作表,步骤,4：,输入计算实际生,产,产成本公式,实际生产成本,=,产品,1,每件耗费成本,产量,+,产品,2,每件耗费成本,产量,+,产品,3,每件耗费成本,产量，,选中,C15,单元格，输入以,下,下函数：,=SUMPRODUCT(C3:E3,C6:E6),。其功能等价于,公,公式：,=C3*C6+D3*D6+E3*E6,。,按,Enter,键计算实际生产,成,成本。,11.3.2,应用规划求解求,得,得最优生产方案,18,2.,建立工作表,步骤,4：,输入计算实际生,产,产成本公式如图,所,所示。,11.3.2,应用规划求解求,得,得最优生产方案,19,2.,建立工作表,步骤,5：,输入计算实际生,产,产时间公式,实际生产时间,=,产品,1,每件生产时间,产量,+,产品,2,每件生产时间,产量,+,产品,3,每件生产时间,产量,选中,C16,单元格，输入以,下,下函数：,=SUMPRODUCT(C4:E4,C6:E6),其功能等价于公,式,式,=C4*C6+D4*D6+E4*E6,。按,Enter,键计算实际生产,时,时间。,11.3.2,应用规划求解求,得,得最优生产方案,20,2.,建立工作表,步骤,5：,输入计算实际生,产,产时间公式,如图所示,11.3.2,应用规划求解求,得,得最优生产方案,21,2.,建立工作表,步骤,6：,输入计算利润的,公,公式,利润,=,产品,1,毛利合计,+,产品,2,毛利合计,+,产品,3,毛利合计,选中,E12,单元格，插入求,和,和函数：,=SUM(C7:E7),，按,Enter,键计算利润。,步骤,7：,为了和最优解比,较,较利润大小，输,入,入约束条件中产,量,量最小值即,60,、,45,、,60,作为产量初值，,观,观察记录利润结,果,果为“,11850,”，如图所示。,11.3.2,应用规划求解求,得,得最优生产方案,22,3.,应用规划求解，,求,求得利润最大化,生,生产方案,步骤,1：,选中目标单元格,C17,，切换到【数据,】,】选项卡，单击,【,【分析】选项组,下,下的【规划求解,】,】按钮，如,下,图所示，弹出【规划,求,求解参数】对话,框,框，如,下,图所示。,11.3.2,应用规划求解求,得,得最优生产方案,23,3.,应用规划求解，,求,求得利润最大化,生,生产方案,步骤,2,：,设置目标函数,11.3.2,应用规划求解求,得,得最优生产方案,24,3.,应用规划求解，,求,求得利润最大化,生,生产方案,步骤,3：,设置可变单元格,，,，即决策变量,步骤,4：,设置约束条件,添加实际成本约,束,束条件，实际生,产,产成本应小于等,于,于生产成本约束,。,。单击【约束】,文,文本框旁的【添,加,加】按钮，弹出,【,【添加约束】对,话,话框，如,下,图所示，单击【单元,格,格引用位置】文,本,本框，在工作表,中,中选择,C15,单元格，在中间,的,的下拉列表中选,择,择【,=,】选项，单击右,侧,侧的【约束值】,文,文本框，在工作,表,表中选择,C11,单元格。单击【,添,添加】按钮，继,续,续弹出【添加约,束,束】对话框，添,加,加其他条件。,11.3.2,应用规划求解求,得,得最优生产方案,25,3.,应用规划求解，,求,求得利润最大化,生,生产方案,步骤,4：,设置约束条件,添加实际生产时,间,间约束条件，实,际,际生产时间应小,于,于等于生产时间,约,约束。在弹出的,【,【添加约束】对,话,话框中，按上述,方,方法设置实际生,产,产时间约束条件如,下,图所示。然后单击【,添,添加】按钮，再,次,次弹出【添加约,束,束】对话框。,11.3.2,应用规划求解求,得,得最优生产方案,26,3.,应用规划求解，,求,求得利润最大化,生,生产方案,步骤,4：,设置约束条件,添加产量约束条,件,件,产品产量应大于,等,等于产量约束值,且,且必须为整数。,11.3.2,应用规划求解求,得,得最优生产方案,27,3.,应用规划求解，,求,求得利润最大化,生,生产方案,步骤,4：,设置约束条件,所有约束条件添,加,加完成后单击【,确,确定】按钮，返,回,回【规划求解参,数,数】对话框，如,下,图所示。在【约束】,列,列表框中