本章重点介绍了财务工作中的一些预测和规划求解问题,例如资金需要量的预测、营业费用线性预测法、销量与利润总额回归分析以及利润最大化规划求解等实际问题。在介绍这些实例的同时,介绍了Excel中的与数据预测相关的函数SLOPE、INTERCEPT、LINEST以及GROWTH等,同时也介绍了数组公式相关知识,最后介绍了如何在Excel 2016中安装加载项,以及使用“回归”和“规划求解”数据分析工具。

接下来,我们以某企业成本最小化规划求解和成本趋势分析以例,进一步巩固本章所学的知识点。打开实例文件“生产成本预测模型.xlsx”工作簿。

步骤01:在单元格F3中输入公式“=B3*E3”,然后复制公式至单元格F4和F5中,如图16-82所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-82 设置公式

步骤02:设置公式计算实际销售利润和生产时间。根据利润最大化求解过程中学习的知识,在单元格B13、B14和B15中输入公式,计算实际销售利润、生产时间和每天最低生产成本,如图16-83所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-83 计算实际销售利润、生产时间和每天最低生产成本

步骤03:打开“规划求解参数”对话框。设置目标单元格为单元格B15,选中“最小值”单选按钮,设置可变单元格为E3:E5,如图16-84所示。

步骤04:参照利润最大化规划求解中对各变量添加约束的过程,再次对各变量设置约束,设置好的约束如图16-85所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-84 设置目标单元格和可变单元格

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-85 设置约束

步骤05:在“规划求解参数”对话框中单击“求解”按钮,如图16-86所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-86 单击“求解”按钮

步骤06:在“规划求解结果”对话框中选中“运算结果报告”,单击“确定”按钮,如图16-87所示。

步骤07:返回工作表中,规划求解结果如图16-88所示。

步骤08:Excel会在工作簿中插入一个新工作表,并显示运算结果报告,如图16-89所示。

步骤09:在“图表”组中单击“散点图”下三角按钮,从展开的下拉列表中选择适当的子图表类型,如图16-90所示。

步骤10:在“数据”组中单击“选择数据”按钮,打开“选择数据源”对话框,设置“图表数据区域”为单元格A10:F19,单击“确定”按钮,如图16-91所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-87 “规划求解结果”对话框

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-88 规划求解结果

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-89 运算结果报告

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-90 选择散点图效果

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-91 选择数据源

步骤11:创建的散点图效果如图16-92所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-92 图表效果

步骤12:切换到“图表工具–设计”选项卡,单击“图表布局”中的“添加图表元素”下拉按钮,然后单击“趋势线”下三角按钮,从下拉列表中单击“其他趋势线选项”,如图16-93所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-93 单击“其他趋势线选项”选项

步骤13:在“设置趋势线格式”对话框中单击选中“线性”单选按钮,如图16-94所示。

步骤14:在“趋势预测”区域设置向前周期数为2,勾选“显示公式”复选框,如图16-95所示。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-94 选择趋势线类型

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-95 设置周期和公式

步骤15:添加趋势线后的图表最终效果如图16-96所示。趋势线向上推了2个周期,并且显示了公式“y=-108x+18750”。

Excel 实战:成本趋势分析和最小化规划求解-Excel22

图16-96 图表最终效果