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

《Excel-财务应用--经济价值最优的生产决策问题.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

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工作表中的数学描述。其中，生产每吨螺纹钢和带钢所需要的两种原材料和销售产品产生的利润，均属于已知条件；工厂要求使用的两种原材料吨数为约束条件；而绿色单元格区域则表示问题中需要计

4、算的两个变量值，默认情况下，其值为0。规划模型的创建完成之后，需要计算原材料A和原材料B的合计值。选择原材料A合计值所对应的单元格，即E4单元格，单击【函数库】组中的【数学和三角函数】下拉按钮，在其列表中选择SUMPRODUCT函数。然后，在弹出的【函数参数】对话框中，分别设置参数Array1和Array2的数据范围为C4:D4和C7:D7，如图9-3所示。单击【确定】按钮，即可在原材料A合计值所对应的单元格中，计算出其结果为0，如图9-4所示。输入设置选择图9-3设置函数参数图9-4原材料A的合计值技巧另外，选择原材料A所对应的单元格后，也可以直接在【编辑栏】中输

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

6、返回乘积之和。其语法格式为：SUMPRODUCT(array1,array2,array3,…)其中，参数array1,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添加约束在【规划求解参数】对话框中，主要包含的各部分内容以及按钮的功能作用如下所示：