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

EXCEL用于生产决策的线性规划法
朱建国
会计系
上海理工大学商学院
上海市复兴中路1195号 64337978
e-mail: zhjg@
摘要：本文将介绍运用微软公司的Excel软件，来解决成本会计中生产决策的线性规划问题。

企业生产两种或两种以上产品时，如果这些产品在企业生产能力、经济资源利用上存在着相互影响的关系，如何确定生产产量一般可以使用先行规划方法予以解决。

一般在解线性规划问题时，多采用单纯形法，计算过程比较繁琐。

微软公司的Excel软件有专用的规划求解加载宏可以利用，本文就此问题予以探讨。

关键词：Excel 线性规划生产决策
壹、引言
线性规划是运筹学的一个重要组成部分，专门用来对具有线性联系的极值问题进行求解的一种现代数学方法。

所谓“线性”，是指所有变动因素的相互影响是直线关系。

即约束条件和目标函数都是呈线性关系的。

在成本管理会计中线性规划研究的问题主要有以下两类：
对于已拥有一定数量的人力、物力和财力资源，研究如何合理使用，才能发挥它们的最大经济效益。

对于已确定的一项任务，研究如何统筹安排，才能以最少的人力、物力和财力资源去完成该项任务。

线性规划及其解法—单纯形法的出现，对运筹学的发展起了重大的推动作用。

许多实际问题都可以化成线性规划来解决，而单纯形法有是一个行之有效的算法，但是，单纯形法的手工计算对于会计人员来讲，仍然是一项比较繁杂的工作。

计算机的出现，为会计人员带来了一项非常有用的工具，使一些大型复杂的实际问题的解决成为现实。

微软公司的电子表格软件Excel提供的规划求解加载宏，“加载宏”是为EXCEL提供附加命令和函数的VBA模块。

可以非常方便的完成线性规划的求解任务（事实上该工具也可以处理其他规划问题）。

贰、提出问题
企业在资源合理配置，产品品种决策等方面经常要使用线性规划法来求解，在产品生产安排的决策中，如果企业生产的产品品种有两种以上，且产品之间在资源需求、加工能力、市场需要等方面有一定的相互影响关系，就可以使用线性规划法进行决策，获得最优的产品生产数量，取得最大的边际贡献。

企业在运用线性规划法进行决策时，一般采用单纯形法进行求解，求解的步骤如下：
1.确定目标函数
2.确定约束条件
3.添加松弛变量
4.列出单纯形计算表
5.经过多次单纯形迭代计算，得到计算结论。

一般情况下，仅有两个产品时，手工计算尚可，如果，产品较多的话，计算工作量很大，只能求助于计算机。

微软公司的Excel电子表格软件，专门提供了解决规划求解的工具，可以很方便地完成不论是几种产品的生产合理安排线性规划求解。

Excel的“规划求解”有很强的功能，可以对有多个变量的线性和非线性规划问题进行求解，省去了人工编制程序和手工计算的麻烦。

以下是一个说明如何使用Excel电子表格解决规划求解的工具，来实现合理安排生产的线性规划求解方法。

叁、合理安排生产线性规划求解
某公司今年二季度拟生产甲、乙两种产品，其售价、成本及约束条件的资料，如表一所示：
表一某公司基本情况
公司管理层希望根据已知的资料，确定甲、乙两种产品的最优生产组合。

具体操作步骤如下：
1.在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没有则
需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令，如果在“加载宏”
命令中，没有列出“规划求解”项，单击浏览，确定驱动器、目录、文件名或运行加载程序，然后进行安装。

安装了“规划求解”之后，在“工具”菜单下可能仍然找不到“规划求解”，此时您可以选择“工具”*“加载宏”，在打开的“加载宏”对话框中选中“规划求解”复选框，确定后，就可以将“规划求解”命令添加到“工具”
菜单栏中。

如图一所示：
图一加载宏
2.在新建工作表中的sheet1工作簿中，按照图二的样式，建立规划求解工作表格，
将相关资料填入规划求解工作表格。

图二规划求解工作表
3.在B3、C3单元格填入初始产量，初始产量是规划求解运算时的初始数据，您可以
填入任何比较接近结论的预计数。

4.在B6单元格中输入公式“=B4-B5”确认后，填充到C6单元格，即单价减单位变动
成本，计算两个产品的单位边际贡献。

5.在D8单元格中输入公式“=$B$3*B8+$C$3*C8”确认后向下填充到D9单元格，计算
两种产品预计对原材料和电力的消耗量。

6.在D7单元格中输入公式“=B3*B6+C3*C6”计算两种产品预计边际贡献总额。

7.单击“工具”/“规划求解”菜单命令后，显示如图三所示的数据输入窗口。

图三规划求解参数
8.在“设置目标单元格”文本框中填入“$D$7”，即边际贡献总额；
在“等于”单选框中选择“最大值”；
在“约束”文本框中逐条填加约束条件：
“$D$8<=$E$8”----原材料消耗必需小于限制量；
“$D$9<=$E$9”----电力消耗必需小于限制量；
“$C$3<=$E$10”----乙产品产量必需小于限制量；
“$B$3>=0”----甲产品必需大于或等于零；
“$C$3>=0” ----甲产品必需大于或等于零；
9.单击“求解”按钮，系统便开始运算，运算完成后，提示是否要保存规划求解的报
告和保存规划求解的方案以备下次使用。

图四所示：
图四规划求解结果
10.完成后的工作表显示企业生产400件甲产品和300乙产品的产量组合为最佳产量组
合。

如图五所示：
图五规划求解结论
肆、几个问题的讨论
Excel在“规划求解”中使用的算法是由Leon lasdon，(University of Texas at Austin)
和Allan Waren(Cleveland State University)改进的通用非线性规划最佳化代码。

当“规划求解选项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值不会影响最终数值和求解时间。

在“规划求解”中可以指定500个约束条件，对每一个可变单元格来说，分别有一个上、下限，还可另加100个附加约束条件，应用约束条件的单元格不应多于1000个。

在运行时应注意以下几个问题：
1.若规划求解过程中出现无解时，说明几种产品的产量要达到约束条件是不可能的，
此时，应该注意分析可能存在的原因。

2.各项约束条件是否有冲突
3.有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允
许误差”等取值不当。

伍、小结
利用Excel电子表格合理安排产品生产的产量是非常简便、快捷，表中数值的排列灵活多变，可根据用户要求自行设置，且约束条件不受限制（一般情况下），除了在合理安排产品的决策可以使用外，对于研究如何合理使用企业各项经济资源，以及研究如何统筹安排，以最少的人力、物力和财力资源的消耗都可以参照使用。

采用Excel的“规划求解”方法就使会计人员在实际工作中运用线性规划的操作变得简单快捷，而且，结果准确、直观，成本也较低。

陆、参考文献
1．李定安成本管理研究经济科学出版社 2002年3月版。