|
在新的一年、新的季度、甚至每个月，财务部往往会面临如何降低支出、提高收益的问题。例如，某公司每个季度要制定本季度的生产计划，对每月中预计生产的产品数量、生产成本、日常损耗、广告费、售后服务及收益做出预算，如图1所示。  图1 某公司的季度预算 现在公司决定从今年第一季度起要增加对用户的售后服务费用（由原来的2500元/月提高到4000元/月），在总收益基本不变的前提下，生产产品的成本和最大生产数量不可变，只有重新规划日常损耗和广告费两项支出，但这两项支出金额又有一定的条件限制：每月的日常损耗最少为该月生产成本的0.5%，每季度总损耗额不超过该季度产品总成本的0.8%；每月广告费用最低为2000元，每季度广告费用不超过8000元。 在为公司进行第一季度预算时，要求财务部分别就“减少日常损耗”、“减少广告费”和“同时减少日常损耗和广告费”三种情况做出最优的预算并进行比较。 在以前，这样的决策就要进行开会讨论，仔细运算，或许要经过很长时间才会有一个准确的答案。其实，利用Microsoft Excel中的规划求解和方案管理功能，这样复杂的问题就会迎刃而解了。 “规划求解”虽然是Microsoft Excel提供的工具，但并不在菜单中显示，它存在于“加载宏”中。所以需要执行菜单栏中的【工具】|【加载宏】命令，在弹出的“加载宏”对话框中选择“规划求解”复选框，如图2所示，单击【确定】按钮。这样就可以在Microsoft Excel中的【工具】菜单中看到【规划求解】命令了。  图2 加载宏对话框 首先，我们为第一种方案，即“提高售后服务、降低日常耗损”进行预算，操作步骤如下： （1）执行Microsoft Excel【工具】菜单栏中的【规划求解】命令，打开如图3所示的对话框。  图3 规划求解参数对话框 （2）在“设置目标单元格”对话框中键入总收益所在单元格引用“$F$8”做为目标单元格。为使目标单元格中的数据（即总收益）保持最大，单击“最大值”单选框。 （3）在“可变单元格”编辑框中输入每个可变单元格的引用或名称，并用“逗号”分隔不相邻单元格的引用。“可变单元格”必须直接或间接与目标单元格相联系。在本例的第一种方案中为了达到增加“售后服务费”并保持“总收益”不变的目标，要改变“日常损耗”值，所以把“日常损耗”和“售后服务”均作为“可变单元格”，在该编辑框中输入“$C$5:$E$5,$C$7:$E$7”。 （4）添加约束条件。单击“规划求解参数”对话框中的【添加】按钮，打开如图4所示的对话框。  图4 添加约束对话框 在“单元格引用位置”编辑框中输入单元格引用，或单击编辑框右侧按钮，直接在工作表中单击要设置约束条件的单元格；在中间的下拉列表的右箭头中选择运算符；在“约束值”编辑框中键入数值或对单元格的引用。 第一种方案要求：对“日常损耗”的限制条件是每月的日常损耗额不少于该月产品成本的0.5%，每季度总损耗额不超过该季度产品总成本的0.8%。对“售后服务”的限制条件是将每月售后服务费用增加到4000元。 所以约束条件为： （5）到此，规划求解参数已设置完成。单击【求解】按钮使Excel按设定置开始进行求解过程。求解完成后将打开如图5所示的对话框。  图5 规划求解结果 （6）单击【保存方案】按钮，为该方案命名为“方案1”，单击【确定】按钮返回到“规划求解结果”对话框中。 （7）除了将求解的结果保存为方案外，还可以在当前工作簿中生成显示求解结果的报告。在报告中列出用户设置的目标单元格和可变单元格以及它们的初始值、最终结果、约束条件的信息，如图6所示。  图6 运算结果报告 使用同样的方法，为第二种方案进行预算，即“提高售后服务、降低广告费用”。进行规划求解时，在“设置目标单元格”对话框中键入总收益所在单元格引用“$F$8”。为使目标单元格中的数据（即总收益）保持最大，单击【最大值】选项。在“可变单元格”编辑框中输入“$C$6:$E$7”。 因为对“广告费”的限制条件是每月的广告费用不少于2000元，每季度广告费用不超过8000元。对“售后服务”的限制条件是将每月售后服务费用增加到4000元。因此输入如下的约束条件： 同理，如果对“提高售后服务、同时降低日常耗损和广告费用”的第三种方案进行预算时，需要在“可变单元格”编辑框中输入“$C$5:$E$7”。并添加如下的约束条件： 至此，对三种方案的规划求解已得出结论，从每个方案的运算结果报告中可以得出每种方案的具体预算值。下面我们还可以利用Excel中方案管理器比较这三种方案。 执行菜单栏中的【工具】|【方案】命令，打开“方案管理器”，如图7所示。  图7 方案管理器 “方案管理器”中列出了已创建的方案名称，当前方案中的可变单元格和该方案的备注。 创建方案有多种方法，可以在进行规划求解后将当前数据保存为一个方案；也可以在“方案管理器”中单击【添加】按钮，在弹出的对话框（如图8所示）中输入方案名称、可变单元格及备注内容。  图8 添加方案 使用方案的最终目的是要对各个方案进行分析对比和评价。虽然可以在同一张工作表中显示方案结果并逐个进行分析，但对较复杂的方案来说，最好的方法是将所有方案集中到一个工作表中并生成总结报告，使用户可以方便地对照多个方案。 在“方案管理器”中单击【摘要】按钮，打开如图9所示对话框，其中有两个选项——方案摘要和方案数据透视表。  图9 “方案摘要”对话框 选择“方案摘要”选项，在结果单元格中输入目标单元格所在位置，即$F$8，再单击【确定】按钮，生成名为“方案摘要”的工作表，如图10所示。  图10 方案摘要 这样，根据各方案的模拟数据计算出的目标值显示在总结中，便于财务人员比较分析。比较三个方案的结果单元格“$F$8”的数值，可以看出“方案3”效果最好，“方案2”次之，“方案1”对目标值的影响最小。 从每个方案生成的总结报告中，还可以更细致地了解每个方案的具体状况。有了这样的数据，相信您的预算会更有说服力。 |
|
|
