Excel规划求解在经济管理中的应用

尹聪春

［摘要］ 规划求解是在一定的限制条件下，利用科学方法进行运算，使对前景的规划达到最优的方法，是现代管理科学的一种重要手段，是运筹学的一个分支。Excel规划求解在经济管理的很多方面都有应用，但它的应用在一般的教材和Excel的帮助中都没有详细的介绍。本文主要介绍用Excel规划求解工具来解决经济管理中的产品组合及投资效益问题，以供企业的经营管理者借鉴。

［关键词］Excel；规划求解；经济管理；应用

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 04. 021

［中图分类号］F275［文献标识码］A［文章编号］1673 - 0194（2012）04- 0036- 02

1分析工具及相关术语介绍

Ｅxcel规划求解是一组命令的组成部分，这些命令有时也称作假设分析工具。它就是使工作表中的某些单元格得到优化（最大化或最小化）。优化模型包括3部分：目标单元格、可变单元格和约束。

目标单元格代表目的或目标。例如，最大化每月利润、最小化每月成本等。可变单元格是电子表格中可以进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量、每月或每年的存款金额等。约束是置于可变单元格中的限制条件。例如，使用的资源不能超过其生产能力,生产的产量大于等于零等。

2案例资料

（1）某计算机制造企业生产鼠标、键盘和视频游戏操纵杠。各项数据见表1。

每月共提供了 １３ ２００ 个人工工时和 ３ ０００ 小时的机器使用时间。该制造企业如何安排产量能使利润最大？

（2）某企业现有１００ ０００元准备存入银行，可以选择一年期、二年期和三年期存款，3种存款的年利率分别为２．５００％、２．７１４％、２．９３２％。第３年初和第５年初需要使用现金１０ ０００元和２０ ０００元，第４年初有５０ ０００元的现金收入可以存入银行。试在Ｅｘｃｅｌ中建立模型，计算每年年初的到期本金、到期利息和年末现金余额；用规划求解工具求解每年年初每种存款的最优存款额，使第６年末的现金余额最大；把规划求解参数保存在合适的单元格中。

3模型建立１

3.1 计算各项指标

在ＳＨＥＥＴ １工作表中将已知各项指标填入相关单元格中，并进行相关计算。如图１所示。

其中Ｃ７：Ｅ７单元格区域假设为１，Ｃ８＝ＳＵＭ（Ｃ７：Ｅ７*Ｃ６：Ｅ６）、Ｆ３＝ＳＵＭ（Ｃ３：Ｅ３*Ｃ７：Ｅ７）、Ｆ４＝ ＝ＳＵＭ（Ｃ４：Ｅ４*Ｃ７：Ｅ７），这里需要用数组公式进行确定。

3.2 进行规划求解

单击“工具”菜单上的“规划求解”，在弹出的 “规划求解参数” 对话框中作如图如图２的设置。

在“设置目标单元格”框中单击，然后选择利润单元格（单元格 Ｃ８），在“可变单元格”框中单击，指向区域 Ｃ７：Ｅ７，该区域包含各类产品的产量。

添加约束：单击“添加”按钮，在“添加约束”对话框中，在标记为“单元格引用位置”的框中单击，选择区域Ｃ７：Ｅ７，从对话框中部的列表中选择“＞＝”，在标记为“约束值”的框中单击，选择单元格区域Ｃ５：Ｅ５。在“添加约束”对话框中单击“添加”，以输入需求约束。即可。

同理，再输入约束条件，Ｆ３：Ｆ４＜＝Ｇ３：Ｇ４。

在“规划求解选项”对话框中输入所有可变单元格都为非负值的约束，通过单击“规划求解参数”对话框中的“选项”按钮可打开该对话框。

选择“采用线性模型”和“假定非负”选项，然后单击“确定”。

注意：选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采用非负值的可变单元格组合。

选择“采用线性模型”的原因是产品组合问题是一种称为线性模型的特殊规划求解问题。

单击“规划求解选项”对话框中的“确定”后，返回到主“规划求解”对话框，单击“求解”按钮即可，这样，规划求解会迅速找出最佳解决方案，如图３所示。需要选择“保存规划求解解决方案”以将最佳解决方案值保留在电子表格中。

4模型建立２

4.1 计算各项指标

在ＳＨＥＥＴ ２工作表中将已知各项指标填入相关单元格中，并进行相关计算。如图４所示。

注意：第４年可变单元格为２个，第５年可变单元格为１个。自变量总值为１２０ ０００元。

4.2 进行规划求解

根据如前所述的方法，在打开的“规划求解”对话框中作如图５所示的设置即可。求解的结果如图６所示。

到第6年末的本利和为１３６３１７．３８元。这即是最佳的投资方案。

5结语

利用Ｅｘｃｅｌ规划求解工具不仅可以解决经济管理中的产品组合及投资效益问题，还可以求解资金管理、运输管理、选址规划等。

主要参考文献

［１］刘继伟，杨桦．Ｅｘｃｅｌ 在财务管理中的应用［Ｍ］．北京：清华大学出版社，2010.