Excel 财务建模与应用教程第8章 财务预测模型8.1.2财务预测的作用8.1.1财务预测含义与分类8.1.3财务预测的一般程序8.1财务
预测概述8.2财务预测相关函数及应用8.2.1INTERCEPT函数与SLOPE函数（1）INTERCEPT函数语法：INTERC
EPT(Known_y’s, Known_x’s)功能：利用现有的x值与y值计算直线与y轴的截距。截距为穿过已知的known_x''
s和known_y''s数据点的线性回归线与y轴的交点。参数：Known_y''s为数字型因变量数据点数组或单元格区域；Known_x
''s为自变量数据点集合。 这两个参数可以是数字或包含数字的名称、数组或引用；如果数组或引用参数包含文本、逻辑值或空白单元格，则这些
值将被忽略，但包括零值的单元格将计算在内；如果Known_y''s和Known_x''s为空或其数据点个数不同（不匹配），则函数返回错
误值“#N/A”。（2）SLOPE函数语法：SLOPE(Known_y’s, Known_x’s)功能：返回经过给定数据点的线性回
归拟合直线方程的斜率，它是直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。参数：Known_y''s为数字型因变量
数据点数组或单元格区域；Known_x''s为自变量数据点集合。8.2.2CORREL函数语法：CORREL(array1，arra
y2) 功能：返回单元格区域array1 和array2 之间的相关系数。它可以确定两个不同事物之间的关系，例如检测学生的物理与数
学学习成绩之间是否关联。该函数经常与INTERCEPT函数与SLOPE函数一起用，用于计算回归分析中自变量x与因变量y之间的相关系
数。参数：Array1第一组数值单元格区域。Array2第二组数值单元格区域。【例8-1】假设某企业2011-2020年家电销售额
如表8-1所示。运用上述函数进行2021年销售额的预测。（1）根据题意建立如图8-1所示的家电销售额基本数据表格。（2）建立并用I
NTERCEPT、SLOPE与CORREL函数计算截距、斜率与相关系数的模型。如图8-2所示。有关单元格公式如下：C3=SLOPE
(B3:B12,A3:A12)D3=INTERCEPT(B3:B12,A3:A12)E3=CORREL(A3:A12,B3:B12
)C6=2016D6=D3+C3C6（3）假设预测方程为Y=A+BX，Y为销售额，X为年份，则2021年的预测销售额为419.6
7万元。8.2.3FORECAST函数语法：FORECAST(x,known_y''s,known_x''s)功能：利用现有的x值与y
值进行线性回归预测y未来值。可以使用该函数预测未来销售额、库存需求或消费趋势。参数：x为需要进行预测的数据点。Known_y''s为
数字型因变量数据点数组或单元格区域；Known_x''s为自变量数据点集合。如果 x 为非数值型，函数 FORECAST 返回错误值
#VALUE!。如果 known_y''s 和 known_x''s 为空或含有不同数目的数据点，函数 FORECAST 返回错误值
#N/A。如果 known_x''s 的方差为零，函数 FORECAST 返回错误值 #DIV/0!。【例8-2】以【例8-1】数据
资料为例。运用FORECAST函数进行预测。（1）基本数据如图8-1所示。（2）计算结果区如图8-3所示。C3=2016D3=FO
RECAST(C3,B3:B12,A3:A12)（3）2021年预计销售额为419.67万元。8.2.4TREND函数语法：TRE
ND(known_y''s, [known_x''s], [new_x''s], [const])功能：返回一条线性回归拟合线的一组纵坐
标值（y 值），即找到适合给定的数组known_y''s 和 known_x''s 的直线（用最小二乘法），并返回指定数组 new_x
''s 值在直线上对应的 y 值。参数：Known_y''s 为已知关系表达式y=a+bx中已知的y值集合。 如果数组 known_y
''s 在单独一列中，则 known_x''s 的每一列被视为一个独立的变量。 如果数组 known_y''s 在单独一行中，则 kno
wn_x''s 的每一行被视为一个独立的变量。 Known_x''s为已知关系表达式y=a+bx中已知的可选x值集合。 数组 kn
own_x''s 可以包含一组或多组变量。如果仅使用一个变量，那么只要 known_x''s 和 known_y''s 具有相同的维数，
则它们可以是任何形状的区域。如果用到多个变量，则 known_y''s 必须为向量(即必须为一行或一列)。 如果省略 known_
x''s，则假设该数组为 {1,2,3,...}，其大小与 known_y''s 相同。 New_x''为需要函数TREND返回对应y
值的新x值。 New_x''s 与 known_x''s 一样，对每个自变量必须包括单独的一列(或一行)。因此，如果 known_y''
s 是单列的，known_x''s 和 new_x''s 应该有同样的列数。如果 known_y''s 是单行的，known_x''s 和
new_x''s 应该有同样的行数。 如果省略 new_x''s，将假设它和 known_x''s 一样。 如果 known_x''s
和 new_x''s 都省略，将假设它们为数组 {1,2,3,...}，大小与 known_y''s 相同。 const可选的一个逻
辑值，用于指定是否将常量a强制设为 0。 如果 const 为 TRUE 或省略，a将按正常计算。如果 const 为 FALS
E，a将被设为 0(零)，b将被调整以使y=bx。【例8-3】以【例8-1】数据资料为例。运用TREND函数进行预测。（1）新建“
TREND”工作表，存放【例8-1】中基本数据，如图8-1所示。（2）建立如图8-４所示用TREND函数计算预测值的模型。C3=2
016D3 =TREND(B3:B12,A3:A12,C3,TRUE)（3）公式运行的结果如图8-4所示。2021年预测的销售额为
419.67万元。8.2.5LINEST函数语法：LINEST（known_y''s，known_x''s，const，stats）功
能：使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式
的形式输入。参数：①known_y''s是关系表达式y=ax+b中已知的y值集合。如果known_y''s对应的单元格区域在单独一列中
，则known_y''s的每一列被视为一个独立的变量。如果known_y''s对应的单元格区域在单独一行中，则known_y''s的每一
行被视为一个独立的变量。②known_x''s是关系表达式y=ax+b中已知的自变量x的观测值集合。它可以是一个变量（即一元模型）或
多个变量（即多元模型）的集合。如果只用到一个变量，只要known_y''s和known_x''s 维数相同，它们可以是任何形状的选定区
域。如果用到不只一个变量，known_y''s 必须是向量（也就是说，必须是一行或一列的区域）。如果省略 known_x''s，则假设
该数组是 {1,2,3...}，其大小与 known_y''s相同。附加回归统计值返回的顺序如表8-2所示。表8-2中的各参数说明如
表8-3所示。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出。③const是一逻辑值，指明是否强制使常数b为0（线
性模型）或为1（指数模型）。 如果const 为 TRUE或省略，b将被正常计算。如果const为FALSE，b将被设为0（线性模
型）或设为1（指数模型）。表8-2 附加回归统计值返回的顺序【例8-4】以【例8-1】数据资料为例。运用LINEST函数进行预
测。 （1）根据题意及LINEST函数运用的要求建立如图8-5所示的模型。（2）建立计算截距、斜率、相关系数及预测销售额的公式。C
3=INDEX(LINEST(B3:B12,A3:A12,TRUE,TRUE),1,1)D3=INDEX(LINEST(B3:B1
2,A3:A12,TRUE,TRUE),1,2)E3=INDEX(LINEST(B3:B12,A3:A12,TRUE,TRUE),
3,1)D6=C3C6+D3上述公式运行结果如图8-6所示。8.3销售影响因素预测模型【例8-5】某企业2014年-2020年的
广告投入及居民家庭可支配收入的情况如表8-4所示。假设2021年预计广告费投入425万元，居民可支配收入6.5万元。表8-4 某企
业2014—2020年的广告投入、居民家庭可支配收入及销售额的情况 单位：万元（1）根据题意建立如图8-7所示的基本数据表格。（
2）建立如图8-8所示的模型表格，计算预测方程的各参数值。（3）在单元格中输入公式。具体如下：B10 =INDEX(LINEST(
B5:H5,B3:H4,TRUE,TRUE),1,1)，得到系数a1。C10 =INDEX(LINEST(B5:H5,B3:H4,
TRUE,TRUE),1,2)，得到系数a2。D10 =INDEX(LINEST(B5:H5,B3:H4,TRUE,TRUE),1
,3)，得到系数b。E10 =INDEX(LINEST(B5:H5,B3:H4,TRUE,TRUE),3,1)，得到系数 R2。B
14 =B10B12+C10B13+D10，得到2021年销售额预测值为10180万元。8.4成本预测 8.4.1成本预测方法
8.4.2成本预测模型【例8-6】某企业2020年1-10月销售成本与销售量的相关数据如表8-5所示。假设2020年11月销售量为
116件，12月为121件，要求建立模型预测2020年11月与12月的销售成本。（1）根据题意建立基本数据表格，如图8-9所示。（
2）建立进行回归分析与计算的数据表格。如图8-10所示。（3）调用“表单控件”工具命令，在B8单元格由左上向右下拖动鼠标形成“回归
模型”选择下拉框。在右击的快捷菜单中选择“设置控件格式”命令，出现“设置控件格式”对话框，选择“控制”标签，在“数据源区域”输入“
$A$8:$A$9”，“单元格链接”输入“$B$8”，“下拉显示项数”可以输入２或１，如图8-11所示。这样，鼠标点击其他区域后，
控件生效，同时可在B8选择回归模型。在被控件隐藏的B8单元格显示与回归模型对应的序列数字。如图8-12所示。（4）在单元格E9、F
9，G9，H9中输入公式E9=IF($B$8=1,INDEX(LINEST($B$5:$K$5,$B$3:$K$3,TRUE,TR
UE),1,2),INDEX(LINEST($B$5:$K$5,$B$3:$K$4,TRUE,TRUE),1,3))，计算参数b。
F9=IF($B$8=1,INDEX(LINEST($B$5:$K$5,$B$3:$K$3,TRUE,TRUE),1,1),IND
EX(LINEST($B$5:$K$5,$B$3:$K$4,TRUE,TRUE),1,2))，计算参数a1。G9=IF($B$8=
1,0,INDEX(LINEST($B$5:$K$5,$B$3:$K$4,TRUE,TRUE),1,2))，计算参数a2。H9=I
F($B$8=1,INDEX(LINEST($B$5:$K$5,$B$3:$K$3,TRUE,TRUE),3,1),INDEX(L
INEST($B$5:$K$5,$B$3:$K$4,TRUE,TRUE),3,1))，计算相关系数R2。相关参数与系数公式运行结果
如图8-13所示。（5）设置预测销售成本的单元格公式。J9=$E$9+$F$9J8+$G$9J8^2K9=$E$9+$F$9
K8+$G$9K8^2当B8选择一元一次模型时公式运行结果如图8-14所示。当B8选择二元一次模型时公式运行结果如图8-15所示
。图8-14图8-158.5利润预测 8.5.1利润预测概述8.5.2利润预测模型【例8-7】假设某企业甲产品的单位售价为90元
，单位变动成本为60元，固定成本4500元，预计该产品销售量为1000件，可实现利润25500元。试建立模型来分析单位售价、单位变
动成本、固定成本与销售量变化对利润的影响，变化范围设为±50%。②设计单位售价、单位变动成本、固定成本与销售量的“滚动条”控件调用
“表单控件”工具，点击“滚动条”选项，在E3单元格从左上至右下拖曳鼠标形成如图8-18所示的滚动条选项框。右击“滚动条”选项框，执
行“设置控件格式”命令，出现“设置控件格式”对话框，如图8-17所示。在“当前值”后输入10，最小值设为0，最大值设为100，步长
设为1，页步长设为2，单元格链接到$E$3（这样可以将变动的数值隐藏起来），点击确定按钮。在此“滚动条”选项框外点击即可激活，这样
，移动滑块，链接到$E$3单元格的数值将可在0-100之间变动。同理，在E4、E5、E6设置“滚动条”选项框，并将单元格分别链接到
$E$4、$E$5、$E$6，分别实现单价、单位变动成本与固定成本的变动。在D3单元格输入公式“=E3/100-50%”，实现销售
量变动从-50%-+50%。并将D3单元格公式向下填充复制，得到D4、D5、D6公式，分别实现单价、单位变动成本与固定成本的变动幅
度从-50%-+50%。 在C3单元格输入公式“=B3(1+D3)”，得到销售量变化后的数据。将C3单元格公式向下用填充柄复制
到C4、C5、C6单元格。分别得到单价、单位变动成本与固定成本变动后的数据。如图8-18所示。图8-18③建立各因素变化对利润的影
响公式。输入变化前利润公式A9=(B4-B5)B3-B6；输入变化后利润公式B9=(B4(1+D4)-B5(1+D5))B
3(1+D3)-B6(1+D6)，注意此公式考虑了销售量、单价、单位变动成本与固定成本因素对利润影响的百分比。输入变化额C9=
B9-A9；输入变化幅度公式D9=C9/A9；当各因素变化幅度为0时，结果如图8-19所示。（2）确定性条件下多品种利润预测分析模
型 【例8-8】某企业同时生产销售甲产品、乙产品、丙产品三种产品，产品有关资料如表8-6所示。要求建立模型确定利润最大时三种产品的
产量。 表8-6①根据题意建立如图8-20所示的基本数据表格。②建立计算最大利润的线性规划模型。将单元格区域B15:D15作为存放
各产品在各车间所耗费的工时数合计，将单元格区域B17:D17作为可变单元格，存放甲产品、乙产品、丙产品的产销量。单元格B18用于存
放目标利润。规划求解模型如图8-21所示。③在单元格B15中输入公式“=SUMPRODUCT(B6:D6,B17:D17)”, 在
单元格C15中输入公式“=SUMPRODUCT(B7:D7,B17:D17)”，在单元格D15中输入公式“=SUMPRODUCT(
B8:D8,B17:D17)”，分别计算三种产品在A、B、C车间耗费的工时合计数。④在单元格B18中输入公式“=SUMPRODUCT(B17:D17,B3:D3-B4:D4)-B12”，用于计算目标利润。⑤选中目标单元格B18，调用“规划求解”工具，出现“规划求解参数”对话框，在“设置目标单元格”中输入或选中B18,“到”后选择“最大值”，可变单元格选择单元格区域B17:D17，约束条件框中依次点击“添加”按钮，加入约束条件项$B$15<=$B$9,$B$17<=$B$5,$B$17:$D$17设置为INT,即设为整数，$B$17:$D$17>=0，$C$15<=$B$10，$C$17<=$C$5，$D$15<=$B$11，$D$17<=$D$5，本题所有约束条件添加完毕，如图8-22所示。⑥点击“求解”按钮，出现规划求解结果，得到在满足约束条件下企业实现最大利润的最佳产品结构为：甲产品：30000件，乙产品：7500件，丙产品：34000件，最大利润为367500元，如图8-23所示。⑦规划求解结果报告如图8-24所示。 |
|
