基于Excel的混合产品最优化模型设计.docVIP

基于Excel的混合产品最优化模型设计 企止管理信怠化项;对话框，单击左侧;加载项;标签，在右侧单击;转到;按钮，打(3)利用图表求最优解，如图4所示。开;加载宏;对话框，勾选;规划求解加载项;复选框，单击;确定;其中:按钮，即可在工具栏的;数据;选项卡中出现;分析;选项组，上面K3=E4/B4;14 =E4/C44; K6=E5/B5 ,17 =E5/C5, K9=E5*B4-就有了;规划求解;按钮。E4*B5,KIO=E4*C5.-E5*C4,L9=K9/Kl 1, LlO=KIO/KI I 2案例选中该模型相关指标，绘制如图5所示的XY散点图。某公司生产和销售甲与乙两种产品，它们的生产与销售相关啪;啪;副m阳w出mωω数据如表1所示。\ 褒1J在公司甲、乙丙种产品生产与销售相关戴据\\ 甲产品乙产品\、山∞mω mm飞\、单价(元If牛)11.∞ 17.∞ 匡孟翠固mw\?当飞\单位可变成本(元/件)6.∞ 10.∞ 睛∞;阁。;咽;\ 飞\\ 固定成本(兀)310.∞ 710.∞ 飞\\ aω 回割揭.. 人工小时/件85 60 ; ; 一一一一-机器小时/件76 120 图5XY敌点图此外，该公司每月生产能力在人工与机器两种资源上的限制此H才人工小时约束与机器小时约束的交点就是最优销售i注。分别为28595人工小时与34创6饥器小时。假定所有产品只要生产出来都能按预定的单价销售出去。试确定在满足生产能力限制条件下，为了使公司利润达到最大，两种产品各应生产多少数量。3 模型设计(1)将相关指标输人Excel工作表中，建立最优模型。如图1所示。其中:产品销售数量假设为l困6相关指标需要量:04=B4哈B$3+C4咽C$305=B5嗡B$3+C5哈C$3总成本:B9=B3咱7+B8，复制公式至C9即可。(4)模拟运算不同生产量的总成本。将相关指标摸入表格中，销售收入:BIO=B伊凹，复制公式至CIO即可。建立模拟运算模型，如图6、图7所示。利润:BII=BIO-B9，复制公式至Cll即可。#039;悔自9，E10)) 总利润，011=Bll+Cll 产寸τγ丁丁I-，.，世‘lt;??.明1. -’.’ 幢UI??lXIV攫国~CWD 肝0IW1VOIlt;lt;Ilt;llOIlllIV王二=-#039;O??咽·描@主铲二3画E啤E宙ifs??3雹;;絮.;恒#039;E 匾回S回HllSSlt;:0 JU ’;; 瑾巫Drnu 回1.优模型假设团2jllll设置图7模拟运算表(2)规划求解:选中Oll单元格，单击;工具;菜单，选择;规划求解;命令，在弹出的;规划求解参数;对话框中作如图2所示的设置。其中:设置目标单元格应选择;最大值;单选按钮;可变单元格是曰:C3;在约束条件中应设置销售数量必须大于等于零，而且假设为回8模拟运算表对话框整数，需要量要小于等于可提供量(有限的生产能力)。单击;求解;按钮。生成的结果如图3所示。选中模拟运算表的任一单元格，单击..数据;菜单，选择;模拟运算表;命令，在弹出的;模拟运算表;对话框中，作如图8所示的设置即可。这样就可动态地显示出不同销售量下的总利润值。主要鲁考文献阻四.，，#039;;∞;JO 面厂:::J[1]刘继伟，杨桦.Excel在财务管理中的应用[M).北京:清华大学出版社，2010. 图3模型·忧解图4图解穰型CHINA MANAGEMENT lNFORMA 1丁ONlZATION I 45 ???????????????????????????????????????????????????????????????????????????????????????????????????????挱?????????????????????????????????????????????????焹ド??????????????澴??ブ??????????????嬱??捈泛???????汏??晈汯???????紶????????汥??????????侣?????????汏泛??????????????????????????????????????????????ァ??????????????????????????????????????巁??????????????????????????㏄????????侣湩?乁??????????????????????????ア??????????緈???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????前?????????????????