Excel-财务应用--经济价值最优的生产决策问题

1、Excel 财务应用 经济价值最优的生产决策问题人们在各种生产活动和经济活动中，总是希望最大限度的利用资源和设备，完成最佳的任务；或者在统筹安排下，在最短的时间内，使用最少的人力、物力和投资，即利用最小的资源消耗，完成规定的任务。因此，就需要管理者做出相应的生产决策。生产决策是根据企业的经营战略方案和企业内外经营环境的状况，来确定企业的生产方向、生产目标、生产方针以及生产方案的过程或职能。下面就利用线性规划，解决企业中最优经济价值的生产决策问题。在使用线性规划做出分析决策之前，需要先加载规划求解宏，以便日后使用。依次单击Office按钮和【Excel选项】按钮，在弹出的对话框中，选择【加载项】

2、选项卡，并单击【转到】按钮，即可弹出【加载宏】对话框。然后，在该对话框中，启用【规划求解】复选框，如图9-1所示。启用图9-1 加载规划求解宏提 示在【加载宏】对话框中单击【确定】按钮之后，系统将自动弹出安装过程对话框，安装完成之后重新启动计算机，即可在【数据】选项卡的分析组中，添加【规划求解】按钮。例如，某钢厂生产螺纹钢和带钢两种类型的钢材，已知生产一吨螺纹钢需要A种原材料2吨，B种原材料6吨；生产一吨带钢需要A种原材料5吨，B种原材料3吨，而工厂要求所用的A、B两种原料分别不能超过10吨和18吨。每吨螺纹钢可以获得4万元的利润，售出每吨带钢可以获得3万元的利润，要求计算生产螺纹钢和带钢两种

3、产品各多少吨时，产值最大？在利用线性规划模型求解时，任何一个线性规划模型都包含有表示要做决策的决策变量、表述这些决策变量的可行值、现值的约束条件和表示问题完全绩效测度的目标函数。因此，可以将问题中的已知条件、约束值以及所求变量都输入到Excel工作表中，如图9-2所示。输入条件图9-2 输入已知条件、约束值和变量上图所表述内容是对该问题在Excel工作表中的数学描述。其中，生产每吨螺纹钢和带钢所需要的两种原材料和销售产品产生的利润，均属于已知条件；工厂要求使用的两种原材料吨数为约束条件；而绿色单元格区域则表示问题中需要计算的两个变量值，默认情况下，其值为0。规划模型的创建完成之后，需要计算原材

4、料A和原材料B的合计值。选择原材料A合计值所对应的单元格，即E4单元格，单击【函数库】组中的【数学和三角函数】下拉按钮，在其列表中选择SUMPRODUCT函数。然后，在弹出的【函数参数】对话框中，分别设置参数Array1和Array2的数据范围为C4:D4和C7:D7，如图9-3所示。单击【确定】按钮，即可在原材料A合计值所对应的单元格中，计算出其结果为0，如图9-4所示。输入设置选择 图9-3 设置函数参数 图9-4 原材料A的合计值技 巧另外，选择原材料A所对应的单元格后，也可以直接在【编辑栏】中输入“=SUMPRODUCT(C4:D4,C7:D7)”公式，得出计算结果。选择原材料B合计值

5、所对应的单元格，即E5单元格，再次选择SUMPRODUCT函数，并设置函数参数，或者在【编辑栏】中，输入“=SUMPRODUCT(C5:D5,C7:D7)”公式，按Enter键，即可计算原材料B的合计值，如图9-5所示。输入图9-5 原材料B的合计值在计算原材料A和原材料B的合计值时，由于其计算结果与产量值相关，而产量的默认值为0，因此，两种原材料的合计值也为0。在计算两种原材料的合计值时，利用了SUMPRODUCT函数。该函数可以在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。其语法格式为：SUMPRODUCT (array1, array2, array3,)其中，参数arra

6、y1, array2, array3,为2到255个数组，其相应的元素需要进行相乘并求和。因此，计算两种原材料的合计值时，其遵循的公式为：原材料合计值=每吨螺纹钢使用的吨数螺纹钢的产量每吨带钢使用的吨数带钢的产量。然后，需要确定目标函数值，选择目标值所对应的单元格，即G7单元格，在【编辑栏】中，输入“=SUMPRODUCT(C6:D6,C7:D7)”公式，即可计算出目标函数值，如图9-6所示。效果显示输入图9-6 目标函数值提 示目标函数值是描述问题所求的目标函数的计算结果，在本例中，表示工厂的最大利润。合计值和目标函数值计算完成之后，即可开始使用规划求解宏对该问题进行计算。选择最大利润值所对

7、应的单元格，即G7单元格，并选择【数据】选项卡，单击【分析】组中的【规划求解】按钮，在弹出的【规划求解参数】对话框中，设置【可变单元格】的范围为C7:D7，如图9-7所示。然后，单击【添加】按钮，在弹出的【添加约束】对话框中，分别设置【单元格引用位置】和【约束值】的数据范围，如图9-8所示。添加约束设置单击设置 图9-7 设置可变单元格 图9-8 添加约束在【规划求解参数】对话框中，主要包含的各部分内容以及按钮的功能作用如下所示：l 设置目标单元格在【设置目标单元格】文本框中，输入目标单元格的单元格引用（用于表示单元格在工作表上所处位置的坐标集）或名称（代表单元格、单元格区域、公式或常量值的单

8、词或字符串。）。目标单元格中必须包含公式。其中，在该栏中，还包含3个单选按钮。若要使目标单元格的值尽可能大，请选择【最大值】单选按钮。若要使目标单元格的值尽可能小，请选择【最小值】单选按钮。要使目标单元格为确定值，请选择【值为】单选按钮，然后在文本框中输入数值。l 可变单元格输入每个可变单元格的名称或引用，用逗号分隔不相邻的引用，用冒号分隔相邻的单元格。可变单元格必须直接或间接与目标单元格相关。最多可以指定200个可变单元格。另外，如果要使“规划求解”根据目标单元格自动建议可变单元格，请单击【推测】按钮。l 约束在【约束】文本框中，输入要应用的约束条件，其中，约束条件是“规划求解”中设置的限制

9、条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。其中，在该栏的右侧主要包含3个按钮。若单击【添加】按钮，可以添加约束条件。单击【更改】按钮，可以更改约束条件。单击【删除】按钮，可删除约束条件。l 选项单击该按钮，弹出【规划求解选项】对话框。在其中可加载或保存规划求解模型，并对求解过程的高级功能进行控制。l 全部重设单击该按钮，即可清除规划求解中的当前设置，将所有的设置恢复为初始值。提 示单击【选项】上方的【关闭】按钮，则可以关闭【规划求解参数】对话框，此时不对问题进行求解，但保留通过【选项】、【添加】、【更改】或【删除】按钮所做的更改。在【规划求解参数

10、】对话框中，单击【选项】按钮，弹出【规划求解选项】对话框。在该对话框中，分别启用【采用线性模型】和【假定非负】复选框，如图9-9所示。启用图9-9 规划求解选项的设置在【规划求解选项】对话框中，包含多个文本框、复选框以及单选按钮的设置，可以设置规划求解过程的一些高级功能，每一选项都有默认设置，可以满足大多数情况下的要求，其功能作用如下所示：l 最长运算时间该项用于限制求解过程中所花费的最长时间。在该文本框中，可以输入的最大值为32,767（秒），默认值100（秒）。l 迭代次数该选项可以通过限制中间计算的次数来限制求解过程花费的时间。在该文本框中，可以输入的最大值为32767（次），其默认值为

11、100（次）。l 精度该文本框可以输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值或上下限。需要注意的是，精度值必须表示为小数（0和1之间），输入数字的小数位越多，精度越高，如0.0001比0.01的精度高。l 允许误差在该文本框中，可以输入满足整数约束条件、且可以被接受的目标单元格求解结果，与真实的最佳结果之间的百分偏差。该选项只应用于具有整数约束条件的问题，设置的允许误差值越大，求解过程就越快。l 收敛度该文本框用于输入收敛度数值。当最近五次迭代后，目标单元格中数值的相对差别小于【收敛度】文本框中的数值时，规划求解便会停止运行。该选项只应用于非线性规划求解问题，并且必

12、须表示为小数（0和1之间）。输入数字的小数位数越多，收敛度就越小，收敛度的值越小，得到结果所需的时间就越长。l 采用线性模型当模型中的所有关系都是线性的，并且希望解决线性优化问题时，启用该复选框可以加速求解进程。l 自动按比例缩放如果启用该复选框，那么当输入和输出值量级相差很大时（例如，基于百万美元的投资将利润百分比最大化），可以自动按照比例缩放数值。l 假定非负启用该复选框，则对于在【添加约束】对话框的【约束】框中没有设置下限的所有可变单元格，规划求解将假定其下限为0（零）。l 显示迭代结果如果启用该复选框，则每进行一次迭代后都将中断规划求解，并显示当前的迭代结果。l 估计该栏可以指定在每个

13、一维搜索中用来得到基本变量初始估计值的逼近方案。其中，若选择【正切函数】单选按钮，可以使用正切向量线性外推；若选择【二次方程】单选按钮，则使用二次方程外推法，提高非线性规划问题的计算精度。l 导数在该栏中，可以指定用于估计目标函数和约束函数的差分方案。其中，【向前差分】单选按钮用于解决大多数约束条件数值变化相对缓慢的问题；而【中心差分】单选按钮用于解决约束条件变化迅速，特别是接近限定值的问题。虽然此选项要求更多的计算，但在规划求解不能返回有效解时也可能会有一定帮助。l 搜索该栏用于指定每次迭代使用的算法，以便确定搜索方向。其中，选择【牛顿法】单选按钮，可以使用准牛顿法迭代，其需要的内存比共轭法多，但所需的迭代次数少；若选择【共轭法】单选按钮，则使用共轭法进行迭代，该方法比牛顿法需要的内存少，但需要的迭代次数较多。l 装入模型单击【装入模型】按钮，即可弹出【装入模型】对话框，用户可以在该对话框中指定对要加载模型的引用。l 保存模型单击该按钮，即可弹出【保存模型】对话框，该对话框用于指定保存模型的位置。只有当需要在工作表上保存多个模型时，单击该按钮，系统将自动保存第一个模型。接下来，单击【规划求解选项】对话框中的【确定】按钮，返回【规划求解参数】对话框，并单击【求解】按钮，在弹出的【规划求解结果】对话框中，选择【保存规划求解结果】单选按钮，如