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

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

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

图16-82　设置公式

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

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

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

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

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

图16-85　设置约束

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

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

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

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

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

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

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

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

图16-88　规划求解结果

图16-89　运算结果报告

图16-90　选择散点图效果

图16-91　选择数据源

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

图16-92　图表效果

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

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

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

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

图16-94　选择趋势线类型

图16-95　设置周期和公式

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

图16-96　图表最终效果