EXCEL用于生产决策的线性规划法

1、EXCEL用于生产决策的线性规划法朱建国会计系上海理工大学商学院上海市复兴中路1195号64337978e-mail: HYPERLINK mailto: 摘要：本文将介绍运用微软公司的Excel软件，来解决成本会计中生产决策的线性规划问题。企 业生产两种或两种以上产品时，如果这些产品在企业生产能力、经济资源利用上存在着相互影响 的关系，如何确定生产产量一般可以使用先行规划方法予以解决。一般在解线性规划问题时，多 采用单纯形法，计算过程比较繁琐。微软公司的Excel软件有专用的规划求解加载宏可以利用， 本文就此问题予以探讨。关键词：Excel线性规划 生产决策壹、引言线性规划是运筹学的一个重要

2、组成部分，专门用来对具有线性联系的极值问题进行求解 的一种现代数学方法。所谓“线性”，是指所有变动因素的相互影响是直线关系。即约束条 件和目标函数都是呈线性关系的。在成本管理会计中线性规划研究的问题主要有以下两类：对于已拥有一定数量的人力、物力和财力资源，研究如何合理使用，才能发挥它们的最 大经济效益。对于已确定的一项任务，研究如何统筹安排，才能以最少的人力、物力和财力资源去完 成该项任务。线性规划及其解法一单纯形法的出现，对运筹学的发展起了重大的推动作用。许多实际 问题都可以化成线性规划来解决，而单纯形法有是一个行之有效的算法，但是，单纯形法的 手工计算对于会计人员来讲，仍然是一项比较繁杂的

3、工作。计算机的出现，为会计人员带来 了一项非常有用的工具，使一些大型复杂的实际问题的解决成为现实。微软公司的电子表格软件Excel提供的规划求解加载宏，“加载宏”是为EXCEL提供附 加命令和函数的VBA模块。可以非常方便的完成线性规划的求解任务（事实上该工具也可以 处理其他规划问题）。贰、提出问题企业在资源合理配置，产品品种决策等方面经常要使用线性规划法来求解，在产品生产 安排的决策中，如果企业生产的产品品种有两种以上，且产品之间在资源需求、加工能力、 市场需要等方面有一定的相互影响关系，就可以使用线性规划法进行决策，获得最优的产品 生产数量，取得最大的边际贡献。企业在运用线性规划法进行决策

4、时，一般采用单纯形法进行求解，求解的步骤如下：确定目标函数确定约束条件添加松弛变量列出单纯形计算表经过多次单纯形迭代计算，得到计算结论。一般情况下，仅有两个产品时，手工计算尚可，如果，产品较多的话，计算工作量很大， 只能求助于计算机。微软公司的Excel电子表格软件，专门提供了解决规划求解的工具，可以很方便地完成 不论是几种产品的生产合理安排线性规划求解。Excel的“规划求解”有很强的功能，可以对有多个变量的线性和非线性规划问题进行求 解，省去了人工编制程序和手工计算的麻烦。以下是一个说明如何使用Excel电子表格解决 规划求解的工具，来实现合理安排生产的线性规划求解方法。叁、合理安排生产线

5、性规划求解某公司今年二季度拟生产甲、乙两种产品，其售价、成本及约束条件的资料，如表一所 示：表一某公司基本情况单位名称售价及成本资料约束条件销售单价单位变动 成本固定成本 总额原材料消耗 定额电力消耗 定额市场最大 销售量甲85元55元9800 元2千克/件6千瓦/件无限制乙90元65元4千克/件4千瓦/件500件最高用量2400千克3600千瓦公司管理层希望根据已知的资料，确定甲、乙两种产品的最优生产组合。具体操作步骤如下：在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没有则 需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令，如果在“加载宏” 命令中，没

6、有列出“规划求解”项，单击浏览，确定驱动器、目录、文件名或运行加 载程序，然后进行安装。安装了 “规划求解”之后，在“工具”菜单下可能仍然找 不到“规划求解”，此时您可以选择“工具” * “加载宏”，在打开的“加载宏”对话 框中选中“规划求解”复选框，确定后，就可以将“规划求解”命令添加到“工具” 菜单栏中。如图一所示：图一加载宏在新建工作表中的sheetl工作簿中，按照图二的样式，建立规划求解工作表格， 将相关资料填入规划求解工作表格。图二 规划求解工作表在B3、C3单元格填入初始产量，初始产量是规划求解运算时的初始数据，您可以 填入任何比较接近结论的预计数。在B6单元格中输入公式=B4-B

7、5”确认后，填充到C6单元格，即单价减单位变动 成本，计算两个产品的单位边际贡献。在D8单元格中输入公式“=$B$3*B8+$C$3*C8”确认后向下填充到D9单元格，计算 两种产品预计对原材料和电力的消耗量。在D7单元格中输入公式=B3*B6+C3*C6”计算两种产品预计边际贡献总额。单击“工具” / “规划求解”菜单命令后，显示如图三所示的数据输入窗口。图三规划求解参数在“设置目标单元格”文本框中填入“$D$7”，即边际贡献总额；在“等于”单选框中选择“最大值”；在“约束”文本框中逐条填加约束条件：“$D$8=$E$8” 原材料消耗必需小于限制量；“$D$9=$E$9” 电力消耗必需小于限

8、制量；“$C$3=0”甲产品必需大于或等于零；“$C$3=0”甲产品必需大于或等于零；单击“求解”按钮，系统便开始运算，运算完成后，提示是否要保存规划求解的报 告和保存规划求解的方案以备下次使用。图四所示：图四规划求解结果10,完成后的工作表显示企业生产400件甲产品和300乙产品的产量组合为最佳产量组 合。如图五所示：图五 规划求解结论肆、几个问题的讨论Excel 在“规划求解”中使用的算法是由 Leon lasdon， (University of Texas at Austin) 和Allan Waren(Cleveland State University)改进的通用非线性规划最佳化代

9、码。当“规 划求解选项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值不会影 响最终数值和求解时间。在“规划求解”中可以指定500个约束条件，对每一个可变单元格来 说，分别有一个上、下限，还可另加100个附加约束条件，应用约束条件的单元格不应多于 1000 个。在运行时应注意以下几个问题：若规划求解过程中出现无解时，说明几种产品的产量要达到约束条件是不可能的， 此时，应该注意分析可能存在的原因。各项约束条件是否有冲突有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允 许误差”等取值不当。伍、小结利用Excel电子表格合理安排产品生产的产量是非常简便、快捷，表中数值的排列灵活 多变，可根据用户要求自行设置，且约束条件不受限制(一般情况下)，除了在合理安排产 品的决策可以使用外，对于研究如何合