《Excel在财务管理中高级应用》第7章课件

1、学习目标学习完本章后，学生应当能够独立运用分析工具、函数、规划求解等工具进行财务预测模型设计，包括：一元线性回归模型、一元非线性回归模型、多元线性回归模型，并在设计过程中绘制相应的拟合图形。第7章 财务预测模型设计第1页，共49页。第1单元 预测模型设计分析工具财务预测，是指对企业未来的收入、成本、利润、现金流量及融资需求等财务指标所作的估计和推测。财务预测是编制投资和融资计划的基础，是公司制订成长战略的基本要素。Excel的数据分析工具库提供了3种统计观测分析工具，它们是移动平均法、指数平滑法和回归分析法。第2页，共49页。 移动平均法移动平均法是一种改良的算术平均法，是一种最简单的自适应预

2、测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。第3页，共49页。移动平均法根据预测时使用的各元素的权重不同，可以分为：简单移动平均和加权移动平均。第4页，共49页。 指数平滑法指数平滑法是在移动平均法基础上发展起来的一种时间序列分析预测法，它是通过计算指数平滑值，配合一定的时间序列预测模型对现象的未来进行预测。其原理是任一期的指数平滑值都是本期实际观察值与前一期指数平滑值的加权平均。第5页，共49页。 回归分析预测

3、法 回归分析预测法是通过研究两组或两组以上变量之间的关系，建立相应的回归预测模型，对变量进行预测的一种预测方法。第6页，共49页。第2单元 预测模型设计函数法Excel提供了关于估计线性模型和指数模型参数的几个预测函数。线性模型和指数模型的数学表达式如下：线性模型：y = mx + b 或 y = m1x1 + m2x2 + + b第7页，共49页。 指数模型： 式中，y为因变量；x是自变量；m、m1、.、mn-1、mn、b分别为预测模型的待估计参数。Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、FORECAST函数、SLOPE函数和INTERCEPT函数，

4、它们所使用的参数都基本相同，如表71、72、73所示。第8页，共49页。 表71 预测函数的参数及含义第9页，共49页。 表72 回归统计值返回的顺序第10页，共49页。第11页，共49页。知识点讲解 LINEST函数 LINEST函数是常用的统计函数之一，它是使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。函数语法：LINEST(known_ys,known_xs,const,stats)Known_ys 是关系表达式 y = ax + b 中已知的 y 值集合。第12页，共49页。 如果数组 known_ys 在单独一

5、列中，则 known_xs 的每一列被视为一个独立的变量。 如果数组 known-ys 在单独一行中，则 known-xs 的每一行被视为一个独立的变量。Known_xs是关系表达式 y = ax + b 中已知的可选 x 值集合。数组 known_xs 可以包含一组或多组变量。如果只用到一个变量，只要 known_ys 和known_xs 维数相同，它们可以是任何形状的区域。如果用到多个变量，则 known_ys 必须为向量（即必须为一行或一列）。第13页，共49页。 如果省略 known_xs，则假设该数组为 1,2,3,.，其大小与 known_ys 相同。Const 为一逻辑值，用于指

6、定是否将常量 b 强制设为 0。 如果 const 为 TRUE 或省略，b 将按正常计算。 如果 const 为 FALSE，b 将被设为 0，并同时调整 m 值使 y = mx。Stats 为一逻辑值，指定是否返回附加回归统计值。 如果 stats 为 TRUE，则 LINEST 函数返回附加回归统计值，这时返回的数组为 mn,mn-1,.,m1,b;sen,sen-1,.,se1,seb;r2,sey;F,df;ssreg,ssresid。 如果 stats 为 FALSE 或省略，LINEST 函数只返回系数 m 和常量 b。第14页，共49页。 函数说明：可以使用斜率和 y 轴截距描

7、述任何直线： 斜率 (a)：通常记为 a，如果需要计算斜率，则选取直线上的两点，(x1,y1) 和 (x2,y2)；斜率等于 (y2 - y1)/(x2 - x1)。第15页，共49页。Y 轴截距 (b)：通常记为 b，直线的 y 轴的截距为直线通过 y 轴时与 y 轴交点的数值。直线的公式为 y = ax + b。如果知道了 a 和 b 的值，将 y 或 x 的值代入公式就可计算出直线上的任意一点。还可以使用 TREND 函数。当只有一个自变量 x 时，可直接利用下面公式得到斜率和 y 轴截距值： 第16页，共49页。 斜率：=INDEX(LINEST(known_ys,known_xs),

8、1) Y 轴截距：=INDEX(LINEST(known_ys,known_xs),2)第17页，共49页。 数据的离散程度决定了 LINEST 函数计算的精确度。数据越接近线性，LINEST 模型就越精确。LINEST 函数使用最小二乘法来判定最适合数据的模型。 第18页，共49页。 回归分析时，Microsoft Excel 计算每一点的 y 的估计值和实际值的平方差。这些平方差之和称为残差平方和 (ssresid)。然后 Microsoft Excel 计算总平方和 (sstotal)。当 const = TRUE 或被删除时，总平方和是 y 的实际值和平均值的平方差之和。当 const

9、 = FALSE 时，总平方和是 y 的实际值的平方和（不需要从每个 y 值中减去平均值）。回归平方和 (ssreg) 可通过公式 ssreg = sstotal - ssresid 计算出来。残差平方和与总平方和的比值越小，判定系数 r2 的值就越大，r2 是表示回归分析公式的结果反映变量间关系的程度的标志。r2 等于 ssreg/sstotal。 第19页，共49页。第20页，共49页。第21页，共49页。 LOGEST 函数 LOGEST 函数是在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。因为此函数返回数值数组，故必须以数组公式的形式输入。 此曲线的公式为

10、： y = b*mx 或y = (b*(m1x1)*(m2x2)*_)（如果有多个 x 值）其中因变量 y 是自变量 x 的函数值。m 值是各指数 x 的底，而 b 值是常量值。注意：公式中的 y、x 和 m 均可以是向量，LOGEST 函数返回的数组为 mn,mn-i,.,m1,b。第22页，共49页。函数语法：LOGEST(known_ys,known_xs,const,stats)Known_ys 满足指数回归拟合曲线 y=b*mx 的一组已知的 y 值。如果数组 known_ys 在单独一列中，则 known_xs 的每一列被视为一个独立的变量。如果数组 known-ys 在单独一行中

11、，则 known-xs 的每一行被视为一个独立的变量。Known_xs 满足指数回归拟合曲线 y=b*mx 的一组已知的 x 值，为可选参数。第23页，共49页。 known_xs 数组可以包括一组或多组自变量。如果仅使用一个变量，那么只要 known_xs 和 known_ys 具有相同的维数，则它们可以是任何形状的区域。如果使用多个变量，则 known_ys 必须是向量（即具有一列高度或一行宽度的单元格区域）。如果省略 known_xs，则假设该数组为 1,2,3,.，其大小与 known_ys 相同。 Const 为一逻辑值，用于指定是否将常数 b 强制设为 1。第24页，共49页。如果

12、 const 为 TRUE 或省略，b 将按正常计算。如果 const 为 FALSE，则常量 b 将设为 1，而 m 的值满足公式 y=mx。 Stats 为一逻辑值，指定是否返回附加回归统计值。 如果 stats 为 TRUE，函数 LOGEST 将返回附加的回归统计值，因此返回的数组为 mn,mn-1,.,m1,b;sen,sen-1,.,se1,seb;r 2,sey; F,df;ssreg,ssresid。 如果 stats 为 FALSE 或省略，则函数 LOGSET 只返回系数 m 和常量 b。第25页，共49页。 函数说明： 由数据绘出的图越近似于指数曲线，则计算出来的曲线就越

13、符合原来给定的数据。正如 LINEST 函数一样，LOGEST 函数返回一组描述数值间相互关系的数值数组，但 LINEST 函数是用直线来拟合数据，而 LOGEST 函数则以指数曲线来拟合数据。当仅有一个自变量 x 时，可直接用下面的公式计算出斜率 (m) 和 y 轴截距 (b) 的值。 第26页，共49页。斜率 (m)：INDEX(LOGEST(known_ys,known_xs),1)Y 轴截距 (b)：INDEX(LOGEST(known_ys,known_xs),2) 可用 y=b*mx 公式来预测 y 的值，但是 Microsoft Excel 另外提供了可以预测因变量 y 值的 G

14、ROWTH 函数。 对于返回结果为数组的公式，必须以数组公式的形式输入。 当需要输入一个数组常量（如 known_xs）作为参数时，以逗号作为同一行中数据的分隔符，以分号作为不同行数据的分隔符。第27页，共49页。第28页，共49页。第29页，共49页。TREND函数TREND函数是返回一条线性回归拟合线的值。即找到适合已知数组 known_ys 和 known_xs 的直线（用最小二乘法），并返回指定数组 new_xs 在直线上对应的 y 值。函数语法：TREND(known_ys,known_xs,new_xs,const)Known_ys 是关系表达式 y = mx + b 中已知的 y

15、 值集合。第30页，共49页。如果数组 known_ys 在单独一列中，则 known_xs 的每一列被视为一个独立的变量。如果数组 known-ys 在单独一行中，则 known-xs 的每一行被视为一个独立的变量。 Known_xs 是关系表达式 y = mx + b 中已知的可选 x 值集合。 数组 known_xs 可以包含一组或多组变量。如果只用到一个变量，只要 known_ys 和 known_xs 维数相同，它们可以是任何形状的区域。如果用到多个变量，known_ys 必须为向量（即必须为一行或一列）。第31页，共49页。 如果省略 known_xs，则假设该数组为 1,2,3,

16、.，其大小与 known_ys 相同。New_xs 为需要函数 TREND 返回对应 y 值的新 x 值。New_xs 与 known_xs 一样，每个独立变量必须为单独的一行（或一列）。因此，如果 known_ys 是单列的，known_xs 和 new_xs 应该有同样的列数。如果 known_ys 是单行的，known_xs 和 new_xs 应该有同样的行数。 如果省略 new_xs，将假设它和 known_xs 一样。 如果 known_xs 和 new_xs 都省略，将假设它们为数组 1,2,3,.，大小与 known_ys 相同。第32页，共49页。Const 为一逻辑值，用于指

17、定是否将常量 b 强制设为 0。如果 const 为 TRUE 或省略，b 将按正常计算。如果 const 为 FALSE，b 将被设为 0（零），m 将被调整以使 y = mx。第33页，共49页。函数说明：可以使用 TREND 函数计算同一变量的不同乘方的回归值来拟合多项式曲线。例如，假设 A 列包含 y 值，B 列含有 x 值。可以在 C 列中输入 x2，在 D 列中输入 x3，等等，然后根据 A 列，对 B 列到 D 列进行回归计算。 对于返回结果为数组的公式，必须以数组公式的形式输入。 当为参数（如 known_xs）输入数组常量时，应当使用逗号分隔同一行中的数据，用分号分隔不同行中

18、的数据。第34页，共49页。第35页，共49页。FORECAST函数FORECAST函数根据已有的数值计算或预测未来值。此预测值为基于给定的 x 值推导出的 y 值。已知的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。函数语法：FORECAST(x,known_ys,known_xs)X 为需要进行预测的数据点。Known_ys 因变量数组或数据区域。Known_xs 为自变量数组或数据区域。第36页，共49页。函数说明：如果 x 为非数值型，函数 FORECAST 返回错误值 #VALUE!。 如果 known_ys 和

19、 known_xs 为空或含有不同个数的数据点，函数 FORECAST 返回错误值 #N/A。 如果 known_xs 的方差为零，函数 FORECAST 返回错误值 #DIV/0!。 第37页，共49页。第38页，共49页。SLOPE函数SLOPE函数是返回根据 known_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的重直距离与水平距离的比值，也就是回归直线的变化率。函数语法：SLOPE(known_ys,known_xs)Known_ys 为数字型因变量数据点数组或单元格区域。Known_xs 为自变量数据点集合。第39页，共49页。函数说明：参数

20、可以是数字，或者是包含数字的名称、数组或引用。 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。 如果 known_ys 和 known_xs 为空或其数据点个数不同，函数 SLOPE 返回错误值 #N/A。 第40页，共49页。第41页，共49页。INTERCEPT函数INTERCEPT函数是利用现有的 x 值与 y 值计算直线与 y 轴的截距。截距为穿过已知的 known_xs 和 known_ys 数据点的线性回归线与 y 轴的交点。当自变量为 0（零）时，使用 INTERCEPT 函数可以决定因变量的值。函数语法：INTERCEPT(known_ys,known_xs)Known_ys 为因变的观察值或数据集合。Known_xs 为自变的观察值或数据集合。第42页，共49页。函数说明：参数可以是数字，或者是包含数字的名称、数组或引用。 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。 如果 known_ys 和 known_xs 所包含的数据点个数不相等或不包含任何数据点，则函数 INTERCEPT 返回错误值 #N/A。 第43页，共49页。第44页，共