步骤3：检查模型中各单元格的计算公式。
H8单元格中输入公式“=SUM(C8:G8)，并利用填充柄复制到H10。 C11单元格中输入公式“=SUM(C8:C10)”，并利用填充柄复制到G11。 C13单元格中输入公式“{=SUM(C8:G10*C3:G5)}”。
步骤4：设置规划求解的各项参数并求解。
选择“工具”中的“规划求解”选项，设置求解此问题的规划求解的各项参数。其中$C$13为目标函数的因变量，$C$8:$G$10为决策变量，$C$11:$G$11=$C$12:$G$12；$C$8:$G$10＞＝0；$H$8:$H$10＜＝$I$8:$I$10,单击【确定】即可。
4.仿照课本[例9-6]即可。 5．略
35
实验七 财务预测模型的设计和建立
一、实验目的
1．练习掌握预测方法和预测函数，包括INTERCEPT、SLOPE、LINEST、FORCAST、TREND等。
2．掌握利用数据分析工具进行财务预测。 二、实验内容
1．甲企业只生产一种产品，在2010年1月至2011年12月生产的产品数量（单位：件）如表10-10所示。
表10-10 甲企业2010-2011年各月的产品产量
年月 2010年1月 2010年2月 2010年3月 2010年4月 2010年5月 2010年6月 2010年7月 2010年8月 数量 316 428 484 405 368 382 327 460 年月 2010年9月 2010年10月 2010年11月 2010年12月 2011年1月 2011年2月 2011年3月 2011年4月 数量 391 433 428 395 335 320 369 398 年月 2011年5月 2011年6月 2011年7月 2011年8月 2011年9月 2011年10月 2011年11月 2011年12月 数量 442 401 350 327 360 414 480 388 要求：
（1）利用移动平均工具（间隔为3）预测该企业在2012年1月生产的产品数量。
（2）利用指数平滑工具（阻尼系数为0.4）预测该企业在2012年1月生产的产品数量。
2．乙企业将其2011年1月至2011年12月的库存资金占用情况、广告投入的费用、员工薪酬以及销售额等方面的数据做了一个汇总，如表10-11所示。乙企业的管理人员试图根据这些数据找到销售额与其他3个变量之间的关系，以便进行销售额预测并为未来的预算工作提供参考。试利用回归分析工具建立一个合适的回归方程，据此预测乙企业2012年1月的销售额（假设2012年1月乙企业的库存资金额为150万元，广告投入预算为45万元，员工薪酬总额为27万元）。
表10-11 乙企业2011年各月的销售额与影响因素表 月份 1 2 3 库存资金额 （万元） 98.3 67.7 74 广告投入 （万元） 24.8 23.6 33.9 员工薪酬总额 （万元） 21.5 21 22.4 销售额 （万元） 1098.8 826.3 1003.3 36
4 5 6 7 8 9 10 11 12
151 90.8 102.3 115.6 125 137.8 175.6 155.2 174.3 27.7 45.5 42.6 40 45.8 51.7 67.2 65 65.4 24.7 23.2 24.3 23.1 29.1 24.6 27.5 26.5 26.8 1554.6 1199 1483.1 1407.1 1551.3 1601.2 2311.7 2126.7 2256.5 3．丙企业2011年各月冰箱销售额的有关数据如表10-12所示。
表10-12 丙企业2011年各月的冰箱销售额 月份 1 2 3 4 5 6 7 8 9 10 11 12 销售额（万元） 330 300 300 320 350 380 430 480 530 630 730 830 根据以往的经验，该企业的销售额随着时间的推移有可能呈线性变动趋势或指数变动趋势。要求利用有关函数建立一个带有选择销售额变动趋势组合框控件的预测2012年1月的冰箱销售额的模型。 三、实验步骤
1．
（1）操作步骤如下：
①启动Excel电子表，将本例的有关资料录入Excel电子表，并设计模型的结构。
②利用移动平均分析工具对各月生产的产品数量进行预测。方法如下： ●单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“移动平均”，单击【确定】按钮，打开【移动平均】对话框，在对话框的“输入区域”一栏输入“$B$2:$B$25”，在“间隔”栏输入“3”，作为移动平均时间跨度，在“输出区域”一栏输入“$C$2”，然后选中对话框下方的“图表输出”与“标准误差”两个选项，最后单击【确定】按钮，就得到了从2009年3月份开始出现的移动平均预测值和自2009年5月份才有的标准误差。另外，还生成了生产数量的实际值和移动平均预测值对比的图表。
③在单元格E6输入“=C25”。 操作的结果如下图所示。
37
利用移动平均分析工具预测的结果
（2）操作步骤如下：
①启动Excel电子表，将本例的有关资料录入Excel电子表，并设计模型的结构。
②利用指数平滑分析工具对各月生产的产品数量进行预测。方法如下： ● 单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“指数平滑”，单击【确定】按钮，打开【指数平滑】对话框，在对话框的“输入区域”一栏输入“$B$2:$B$25”，在“阻尼系数”栏输入“0.4”，在“输出区域”一栏输入“$C$2”，然后选中对话框下方的“图表输出”选项，最后单击【确定】按钮，就得到了自2009年2月份开始出现的指数平滑预测值。另外，还生成了生产数量实际值和指数平滑预测值对比的图表。
③复制单元格C25，并向下粘贴到单元格C26中，就得到了2011年1月产品数量的预测值。操作的结果如图10-26所示。
利用指数平滑分析工具预测的结果
38
2．
操作步骤如下：
（1）启动Excel电子表，将本例的有关资料录入Excel电子表，设计模型的结构，利用录入的数据分别求得库存资金额与销售额、广告投入与销售额、员工薪酬总额与销售额的回归分析判定系数R2的值。
运用回归分析工具生成员工薪酬总额对销售额影响的回归分析报告，以获取R2的值。方法如下：
● 单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“回归”，单击【确定】按钮，打开【回归】对话框，在对话框的“Y值输入区域”一栏输入“$E$3:$E$14”，在“X值输入区域”一栏输入“$D$3:$D$14”，选中“置信度”复选框并保持系统默认的95%的置信度不变，在“输出区域”一栏输入“$A$16”，最后单击【确定】按钮，这时Excel会自动生成一个回归分析报告，得到的R2的值为0.6543。
采用同样的方法，分别生成广告投入对销售额影响的回归分析报告和库存资金额对销售额影响的回归分析报告，得到R2的值分别为0.8013和0.9032，由此可见，应该选择判定系数最大的回归方程，即自变量为库存资金额、因变量为销售额的方程。
（2）利用生成回归分析报告的方法求得二元回归方程的调整后判定系数。 如果二元方程的自变量是库存资金额和广告投入，方法如下：
● 单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“回归”，单击【确定】按钮，打开【回归】对话框，在对话框的“Y值输入区域”一栏输入“$E$3:$E$14”，在“X值输入区域”一栏输入“$B$3:$C$14”，选中“置信度”复选框并保持系统默认的95%的置信度不变，在“输出区域”一栏输入“$A$76”，最后单击【确定】按钮，生成回归分析报告，得到调整后判定系数R2的值为0.973。
采用同样的方法，当自变量为库存资金额和员工薪酬总额时，生成回归分析报告，得到调整后判定系数R2的值为0.8992。
（3）利用生成回归分析报告的方法求得三元回归方程的调整后判定系数，方法同上。根据生成的回归分析报告，得知调整后判定系数R2的值为0.9698。
通过对判定系数和调整后判定系数的比较，选取系数最大的回归方程，即自变量为库存资金额和广告投入、因变量为销售额的二元回归方程。
（4）由回归分析报告中给出的回归系数值，可建立起销售额预测模型： Y = 8.37X1 + 12.92X2 – 62.65
（5）将2011年1月的库存资金额150万元、广告投入45万元代入方程，即可求得销售额的预测值为1774.15万元。
操作的结果如下图所示。
39