第3章 教学课件 第3章 例题解答 企业管理的一个基本方面就是为未来制定规划。一个企业要取得长远的成功,它的管 理决策部门必须能够较为准确地预测未来动态并以此为依据制定合理的策略。例如,要制 订某种产品下月的生产计划,管理者必须对该产品下月的销量、原材料成本等做出正确的预 测。然而,这是一件非常困难的事情,因为没有人能够准确地预测未来。但是,根据历史销 售数据的变化规律和趋势,选择合适的统计预测方法可以对未来的销量做出较好的预测。 利用数据查询和分类汇总分析的一些方法,可以从历史销售数据中,汇总过去每一段时 间的销量。将历史销量按月(年、季度、周、日等时间段)排列,构成月(年、季度、周、日等时间 段)销量时间序列。通过对这些时间序列的分析,则可以确定销量的一般水平,以及是否存 在上升或下降的趋势,是否存在季节性的波动等信息。 在了解时间序列的相关概念及组成,并且获得时间序列的相关信息后,本章主要介绍4 种类型的时间序列的建模预测方法,包括围绕一个水平上下波动的时间序列,有线性趋势的 时间序列,非线性趋势的时间序列,既存在趋势又存在季节性波动的时间序列。 3.时间序列预测概述 1 观看视频 时间序列是一个变量在一定时间段内的不同时间点上观测值的集合。这些观测值按时 间顺序排列,时间点之间的间隔相等,可以是年、季度、月、周、日或其他时间段。常见的时间 序列有按年、季度、月、周、日、时段统计的商品销量、销售额或库存量,按年统计的一个省市 或国家的生产总值、人口出生率等。 预测方法可分为定性方法和定量方法。定性方法是基于专家判断的预测方法,在此不 做讨论。定量方法包括时间序列外推预测法和因果预测法。时间序列外推预测法认为,一 个时间序列在过去观测值中表现出来的变化规律或趋势将会延续到未来。因此,这种方法 致力于找出时间序列观测值中的变化规律与趋势,然后通过对这些规律或趋势的外推来确 定未来的预测值。而时间序列因果预测法则注重于寻找时间序列因变量观测值与自变量观 测值之间的函数依赖关系,然后利用这种函数关系和自变量的预计值来确定因变量的预 测值。 1.时间序列的成分 3.1 时间序列通常包括4种成分:趋势成分、季节成分、循环成分和不规则成分。 1. 趋势成分 趋势成分显示了一个时间序列在较长时期的变化趋势。通过观察,可以看出这种趋势 是上升的还是下降的,是线性的还是非线性的。例如,一个国家的国内生产总值、居民收入 水平和社会商品零售总额等都呈现出逐渐增长的趋势。 2. 季节成分 季节成分反映了时间序列在一年中有规律的变化,它是由一年中的特殊季节或节假日 引起的,每年重复出现。这种成分在许多工商业时间序列中存在。例如,烟花爆竹的销售额 在春节期间非常高;电的需求量在冬季增大,春季减少,夏季增大,秋季又减少。我们所指的 季节并非一定是自然的春夏秋冬四季,它可以是一年中的12 个月或其他的时间段。对于集 中在一年中某段时间内销售的商品来说,如烟花爆竹,也许季节性的差异可以用两个季节来 描述:一是春节那个月的高峰期;二是一年中其他的月份。季节成分也可以用来描述任何 持续时间小于一年的、有规律的、重复的变化。例如,每天的交通流量数据显示一天内的“季 节”变化:上、下班时刻出现高峰,中午和傍晚出现中等流量,午夜至清晨出现小流量。 3. 循环成分 循环成分反映了时间序列在超过一年的时间段内有规律的变化,即时间序列在数年的 时间内呈现规律的变化。循环变动通常是由经济状况的变动引起的,波峰出现在经济扩张 期,波谷出现在经济收缩期。包含循环成分的时间序列由于跨越较长的时间段,而且循环的 长度是变化的,因此难以预测,此处不讨论。 4. 不规则成分 不规则成分指的是不能归因于上述3种成分的时间序列的变化。这种成分是由那些影 响时间序列短期的、不可预期的或不重复出现的随机因素引起的,是不可预测的。大多数预 测方法通过平均或平滑来消除不规则成分。商业领域内所有的时间序列都包含不规则成 分,因而呈现随机起伏的形态。 3.2 时间序列预测的步骤 1. 时间序列预测分为4个步骤。分析时间序列包含的成分,确定时间序列的类型;选择适 合此类时间序列的预测方法,在Excel工作表中建立预测模型;评价模型的准确性,确定最 优模型参数;在最优模型参数的基础上计算预测值。 (1)确定时间序列的类型。 时间序列的类型由它所包含的成分决定。如前文所述,所有的时间序列都包含不规则 成分,而循环成分由于其复杂性本书不做讨论,因此需要确定的只有趋势成分和季节成分。 ①趋势成分。 可以根据时间序列观测值,绘制时间序列观测值随时间变化的曲线图。通过在曲线图 中加入趋势线来判断时间序列是否存在趋势成分,以及这种成分是线性的还是非线性的。 图3-1~图3-3显示了几种可能趋势的图形。 71 图3-1 没有趋势的时间序列 图3-2 线性趋势的时间序列 图3-3 非线性趋势的时间序列 ② 季节成分。 要确定季节成分,至少需要两年的数据,而且时间序列观测值的时间间隔必须小于一 年,如季度、月、周或天。为了观察季节成分,可以把两年的数据以两条曲线的方式绘制在以 一年为时间轴的图中。图3-4所示的销售额时间序列明显存在季节成分,即销售额在夏季 增加,冬季减少。 图3-4 含有季节成分的时间序列 72 (2)选择合适的方法建立预测模型。 如果一个时间序列既没有趋势成分,也没有季节成分,那么可以使用移动平均或指数平 滑预测方法;如果一个时间序列含有趋势成分,则可以使用趋势预测方法;如果一个时间序 列含有季节成分,则可以使用季节指数预测方法。相关方法的模型在后续章节中详细介绍。 (3)评价模型准确性,确定最优模型参数。 许多预测模型都会用到一些参数。例如,移动平均预测模型中的移动平均跨度,指数平 滑预测模型中的平滑常数,等等。选择不同的参数值会得到不同的预测值,从而影响预测的 准确性。如果用Ft 表示时刻 t 的预测值,Yt 表示时刻 t 的观测值,那么预测模型在时刻 t 造成的预测误差是 t=Yt-Ft (3-1) 式(3-1)是单个预测值的误差。要得到(e) 预测模型的总体预测误差,一种可用的方法是计 算均方误差(MSE )。它等于时间序列每一个时刻预测误差的二次方的均值,计算公式 如下: MSE= 1Σ(n) t= 1Σ(n) Yt-Ft23 e2==()(2) MSE 越小,模型预测越准确。因此(n) 这(1) 一步的(n) 目(t) 标(1) 是找出使MSE极小的模型参数。可(t) 用的方法包括规划求解法、查表法、图表法、公式法等。这些方法都将在后续章节中详细 介绍。 (4)按要求进行预测 。 求出最优模型参数后,就可以在此基础上计算未来时期的预测值 。 3.移动平均预测和指数平滑预测 2 移动平均模型和指数平滑模型适用于既没有趋势成分,也没有季节成分的时间序列的 预测,即这两个模型适用于围绕一个水平上下波动的时间序列的预测。 3.1 移动平均预测 2. 移动平均(MovingAverage)预测将时间序列中最近几个时期的观测值加以平均,以使 观看视频 每一个观测值所包含的随机因素在一定程度上相互抵消,从而得到时间序列观测值的稳定 水平。可以把这个平均数作为下一个时期的预测值。例如,用第1~3个月实际销量的平均 数作为第4个月销量的预测值,而用第2~4个月实际销量的平均数作为第5个月销量的预 测值,以此类推。因此,预测值的计算公式为 1 N (3) Ft+1= N ΣYt-i+13 = 式中,移动平均跨度N的选择应该使MSE尽可(i) 能(1) 小。 下面通过一个示例来说明在Excel工作表中,如何建立和使用移动平均预测模型。 【例3-1】某汽油批发商在过去12 周内汽油的销量如表3-1所示。利用移动平均预测 模型,并在Excel工作表中建立该模型预测第13 周的汽油销量。 73 74 表3-1 12周内汽油的销量单位:L 周销 量周销 量 1 17 7 22 2 21 8 18 3 19 9 22 4 23 10 20 5 18 11 17 6 20 12 22 【解】 使用式(3-3)和表单控件建立“移动平均预测”模型,在不同的移动平均跨度下求 出每周的汽油销量预测值。 (1)确定时间序列的类型,判断所选择的预测模型是否合适。 将表3-1中的数据输入Excel工作表的单元格A1:B13中,利用这些数据绘制折线图。 选中“数据系列”后,右击,在弹出的快捷菜单中选择“添加趋势线”,即可得到汽油销量的线 性趋势线,如图3-5所示。从图3-5中可以看出,汽油销量的趋势线几乎是水平的,也就是 说,汽油销量时间序列不包含趋势成分,而是围绕一个稳定的水平上下波动。因此,可采用 移动平均预测模型进行预测。 图3-5 加入趋势线的汽油销量变化 (2)建立移动平均预测模型,如图3-6所示。 在单元格H1中假定移动平均跨度等于3,这意味着从第4周开始有预测值。因此,在 单元格C5中输入计算平均值的公式“=AVERAGE(B2:B4)”,并复制到单元格C6:C13 中,得到各月汽油销量的估计测值。在单元格D5中输入公式“=B5-C5”,并复制到单元格 D6:D13中,得到每个月的预测误差。在单元格E5中输入公式“=(B5-C5)^2”,并复制到 单元格E6:E13 中,得到每个月的误差二次方。最后在单元格H2 中输入公式“= AVERAGE(E2:E13)”,得到MSE。 接下来需要确定使MSE 极小的移动平均跨度。根据Excel公式的特性,如果计算 MSE的公式(单元格H2中的公式)是以移动平均跨度(单元格H1中的值)为自变量,那么 只需要修改单元格H1中的值,就可以在单元格H2中得到相应的MSE。但是从上述计算 75 图3-6 汽油销量时间序列移动平均预测模型 过程中可以看出,MSE的计算与移动平均跨度(单元格H1中的值)没有关系。也就是说, 这个模型只能计算移动平均跨度等于3时的MSE,而不能得到随移动平均跨度变化的 MSE。因此需要对这个模型做一些修改,即在单元格C5、D5、E5中分别输入公式“=IF(A5 <=$H$1,"",AVERAGE(OFFSET(C5,- $H$1,-1,$H$1,1)))”“=IF(C5 ="","",B5-C5)”“=IF(C5="","",D5^2)”,并将这些公式分别复制到单元格C2:C14、 D2:D13、E2:E13中。 单元格C5中公式的含义如下:首先用IF()函数判断A5(周次)是否小于或等于H1 (移动平均跨度),如果是,则C5(预测值)为空白,即这一周没有预测值;如果不是,则C5应 该有预测值,这个预测值仍然用AVERAGE()函数计算,只不过参与平均的范围是一个由 OFFSET()函数求出的可变区域。OFFSET()函数的功能是以指定的范围为参照系,通过 给定偏移量得到新的范围。返回(求出)的范围可以为一个单元格或单元格区域。并可以指 定返回的行数或列数。OFFSET()函数需要5个参数,第1个参数是作为参照系的基准位 置;第2个参数是相对于这个基准位置向上(用负数表示)或向下(用正数表示)偏移的行数; 第3个参数是相对于这个基准位置向左(用负数表示)或向右(用正数表示)偏移的列数,这 样就确定了返回数据区域的起始位置;第4个参数是要返回数据范围的行数;第5个参数是 要返回数据范围的列数。事实上,前3个参数指定了要返回数据范围的起始单元格。具体 到这个示例中,单元格C5中的OFFSET()函数返回的是以单元格C5为起点,向上偏移3 行,再向左偏移1列得到的单元格B2开始的,3行1列的一个范围(即单元格B2:B4),即前 3周的观测值。当单元格H1中的值(即移动平均跨度)变为2时,这个范围会变成B3:B4, 即前两周的观测值。这正是移动平均模型的预测公式所要求的观测值。 单元格D5和E5中公式的含义是:如果单元格C5(即预测值)是空白,那么单元格D5 和E5也是空白,否则单元格D5和E5分别计算预测误差和误差二次方。 这样就得到了能够随移动平均跨度变化的预测模型。 (3)确定合适的移动平均跨度。 在单元格H1中制作一个2~6,以1为步长变化的数值调节钮,控制单元格H1中移动 平均跨度的变化。具体方法是:在“文件”选项卡中选择“选项”,在弹出的“Excel选项”对话 框中左侧选中“自定义功能区”,在“自定义功能区”窗格中的“从下列位置选择命令”下拉列 表中选择“主选项卡”,在右侧窗格中勾选“开发工具”复选框,单击“确定”按钮,即可在 Excel的菜单栏显示“开发工具”选项卡。选中“开发工具”→“控件”→“插入”→“表单控件” →“数值调节钮(窗体控件)”,在单元格H1 中按住鼠标左键拖曳绘制数值调节钮,右击,在 弹出的快捷菜单中选择“设置控件格式”,在“设置控件格式”对话框中做如图3-7所示的设 置。单击“确定”按钮,即可直观地看出移动平均跨度取2~6中任何一个整数时的MSE 。 对这些MSE 作比较,即可得出使MSE 极小的最优移动平均跨度。 另外一个求最优移动平均跨度的方法是利用Excel的规划求解工具。这是一个从函数 值所要达到的目标出发,反过来确定为达到这个目标自变量应取什么值的工具。规划求解 的步骤是:在“文件”选项卡中选择“选项”,在弹出的“Excel选项”对话框中左侧选择“加载 项”,单击“转到”按钮,弹出如图3-8所示的“加载宏”对话框。 图3-7 “设置控件格式”对话框图3-8 “加载宏”对话框 在“加载宏”对话框中勾选“规划求解加载项”复选框,单击“确定”按钮,将会在“数据”选 项卡下方的“分析”组中出现“规划求解”选项。单击“规划求解”,在弹出的对话框中做如 图3-9所示的设置,单击“求解”按钮,就会在单元格H1 中显示出使H2 中的MSE 极小的 整数跨度。在本例中,最优移动平均跨度是5。 注意:“ 规划求解”工具不一定每次都能找到整数解。 (4)在最优移动平均跨度的基础上,预测各周的汽油销量。 将单元格C2:C14 中的数据作为新系列复制粘贴到如图3-5所示的图表中,即可在 图3-5中添加一条预测值曲线,结果如图3-10 所示。这条曲线中第5周的值突然下降,这 是因为当移动平均跨度为5时,前5周没有预测值而造成的,但这并不影响观察预测值曲 线。可以看出,它是一条波动较小的曲线,代表了汽油销量的平均水平。 图3-10 还是一个可调图形,它直观地表现了不同移动平均跨度下MSE 和预测值的变 化。其中包括一个对移动平均跨度进行调节的数值调节钮,以及一个显示移动平均跨度值 的文本框和其对应MSE 的文本框。这两个文本框的值分别等于单元格G6 和G7,单元格 G6 中的公式是“=" 移动平均跨度="&H1”,单元格G7 中的公式是“="MSE=" &ROUND(H2,2)”。 76 图3-9 “规划求解参数”对话框 图3-10 汽油销量时间序列观测值及其移动平均预测值 这里主要通过式(3-3)和表单控件建立“移动平均预测”模型,在不同的移动平均跨度下 求出每周(包括第13 周)的预测值。 除这种方法外,还可以利用“数据分析”中的“移动平均”宏来进行预测,但是这种方法只 能得到指定移动平均跨度下的预测值。在其所生成的预测值数据的基础上不能计算出最优 77 移动平均跨度。 2.指数平滑预测 3.2 在移动平均预测模型中,计算移动平均数时每个观测值都使用相同的权数,即认为时间 观看视频序列在其跨度内各个时期的观测值对下一时期值的影响是相同的。而一种更合理的认识 是,越近时期的观测值对下一时期值的影响越大,越远时期的观测值对下一时期值的影响越 小。因此,最近时期的观测值应取最大权数,较远时期观测值的权数应依次递减,所有权数 相加等于1。为移动平均预测模型中的每一个观测值加上不同的权数,得到加权移动平均 (WeightedMovingAverage)预测模型。 加权移动平均预测的一种特殊方法是指数平滑(ExponentialSmoothing)预测法。这 种方法将过去所有时期的观测值的加权移动平均数作为下一时期的预测值,计算公式如下: Ft+1=αYt+α(1-α)Yt-1+ α (1-α)2Yt-2+ … (3-4) 式中, α 被称为平滑常数(0≤ α ≤1 )。 容易证明,式(3-4)中的权数随着期数的前推而减小,并且权数之和等于1。 α >α(1-1-2> α (α)… 35) α)> α (α)1-3>( α+α(1-1-2+ α (α)=( α)+ α (α)1-3+ …1 36) 因此,指数平滑预测法在预测时更重视近期的观测数据,而 α 决定了这种重视的程度, α 越大近期观测值对预测的影响越大, α 越小近期观测值对预测的影响越小。 式(3-4)可以变换为以下两种迭代公式: Ft+1=αYt+ (1-α)Ft (3-7) 或 Ft+1=Ft+α() 38) Yt-Ft( 这两个公式更容易实现指数平滑预测值的计算。式(3-7)说明第t+1期的预测值等于 第 t 期的观测值和预测值的加权平均值,观测值和预测值的权数分别为 α 和1- α 。 式(3-8)说明第t+1期的预测值等于以第t期预测误差乘以 α 作为修正值,对第t期的预测 值加以修正而得到的结果。如果时间序列有较大的随机波动,说明大多数预测误差是由随 机因素引起的,此时应选择较小的平滑常数,这样可以减小由随机因素引起的预测误差对下 期预测值的影响。反之,如果时间序列有较小的随机波动,则应选择较大的平滑常数,这样 做的好处在于,当出现预测误差又允许预测迅速反映引起误差的变化时,可以迅速调整预测 值。在实际模型中,平滑常数的选择应遵循使MSE 极小的原则。 在利用式(3-7)或式(3-8)计算预测值时,有了α、Yt 和Ft 就可以计算出Ft+1,那么Ft 从 何而来呢? 是根据Yt-1 和Ft-1 计算得到的,而Ft-1又是根据Yt-2 和Ft-2 计算得到的,以此 类推,一直到第一个时刻,即必须知道初始的预测值F1。在没有F1 的情况下,可以令F1 为 空,F2=Y1,然后利用式(3-7)或式(3-8)依次往下计算。 下面通过一个示例说明在Excel工作表中,如何通过式(3-7)或式(3-8)与一维模拟运算 表相结合来建立“指数平滑预测”模型,以便找到最优的平滑常数。 【例3-2】利用例3-1的数据在Excel工作表中建立一个指数平滑预测模型来预测 第13 周的汽油销量。 78 【解】 (1)输入观测值。 在例3-1中,我们已经观察到汽油销量时间序列不包含趋势成分和季节成分,因此可以 用指数平滑预测法进行预测。在Excel工作表的单元格A1:B13 内输入1~12 周的汽油销 量,如图3-11 所示。 图3-11 汽油销量时间序列指数平滑预测模型 (2)给出任意的一个平滑常数并计算均方误差 3 。 )。在单元格C3 中输入公式“B2 在单元格F1 中输入一个任意的平滑常数(如0.=,作(”) 为 第2周的预测值(F2 =$F$1*B3+(” )。在单元格C4 中输入公式“1-$F$1)*C3。 在例3-1中,我们计算MSE 时,是利用其定义进行的。即先计算出每一时期的预测误 差二次方,再将它们加以平均。利用SUMXMY2() 函数和COUNT() 函数计算均方误差。 SUMXMY2() 函数的功能是返回两数组中对应数值之差的二次方和。它需要2个参数,第 1个参数是第一个数组或数值区域,第2个参数是第二个数组或数值区域。COUNT() 函数 的功能是计算某一范围内包含数字的单元格的个数。因此在单元格F2 中输入公式“= SUMXMY2(B2:B13,C2:C13)/COUNT(C2:C13)”。 (3)确定最优平滑常数。 利用规划求解工具计算出使MSE 极小的平滑常数,按照图3-12 所示进行设置,求解后 在单元格中即可得到最优平滑常数。 另外一个求最优平滑常数的方法是查表法,即构造一个不同平滑常数和MSE 的对照 表,在这个表中找出MSE 的极小值,然后找出其所对应的平滑常数。构造平滑常数和MSE 的对照表可以利用模拟运算表功能,即在单元格E8:F16 内将MSE(单元格F2)关于平滑常 数(单元格F1)做一个一维灵敏度分析。用MIN() 算出MSE 的极小值,再利用INDEX() 函数和MATCH() 函数的组合来查表找到使MSE 达到极小的平滑常数 α 。 MATCH() 函数的功能是查找指定数值在指定范围内的位置。它需要3个参数,第1 个参数是要查找的指定数值(或其所在的单元格), 第2个参数是指定的范围,第3个参数是 匹配类型(0、1、-1)。如果第3个参数为0表示精确匹配,即要查找的指定数值必须出现在 79 图3-12 平滑常数规划求解参数对话框 指定范围内,此时MATCH() 函数返回的是指定数值在指定范围内的序号;如果第3个参 数为1表示要查找的指定数值可能不在指定范围内,同时指定范围内的数字是递增序列,此 时MATCH() 函数返回的是小于或等于指定数值的最大值在指定范围内的序号;如果第3 个参数为-1表示要查找的指定数值可能不在指定范围内,同时指定范围内的数字是递减 序列,此时MATCH() 函数返回的是大于或等于指定数值的最小值在指定范围内的序号。 在单元格F4 中输入公式“=MIN(F9:F16),(”) 求出MSE 极小值。在单元格F5 中输入 公式“=INDEX(E9:E16,MATCH(F4,F9:F16,0))”。其中的MATCH() 函数找出单元格 F4 中的MSE 极小值(6.F16 中的序号,这个序号等于3,然后 INDEX() 9511201)在单元格F9: 函数再在单元格E9:E16 内找出序号等于MATCH() 函数计算结果所对应的单 元格的值0.即MSE 极小值所对应的平滑常数。如果想提高平滑常数的精度,可将模拟 3, 运算表自变量值的间隔变小,例如, 27 到0.01,单元格F5 中的最优平滑常 从0.34 间隔为0. 数将变为0. 28 。 (4)预测第13 周的汽油销量。 3, 在单元格A14 中输入13,同时将单元格F1 中开始设置的数值更改为0.否则预测值 将产生一定的误差,再将单元格C13 中的公式复制到单元格C14,求出第13 周的汽油销量 预测值。也可以用单元格F5 中的最优平滑常数在单元格F6 中输入第13 周的预测公式。 利用单元格A2:C14 中的数据可以绘制汽油销量观测值曲线和指数平滑预测值曲线。 在其中添加一个对平滑常数进行调节的微调项,以及显示平滑常数值和MSE 值的文本框, 得到如图3-13 所示的可调图形。这里的微调项有一点特殊,它控制的是一个小数。而 80 Excel提供的微调项只能控制在0~30000 中变化的整数,所以在制作时利用间接控制的技 术,即用微调项控制单元格G1 中的数字在1~8,以1为步长变化,再在单元格F1 中输入公式“,(”) 这样单元格F1 中的数字就在0.8,1为步长变化。 =G1/101~0.以0. 图3-13 汽油销量时间序列观测值及其指数平滑预测值 3.趋势预测 3 3.1 线性趋势预测 3. 观看视频 对于含有线性趋势成分的时间序列,预测变量随时间的推移递增或递减,可以将预测变 量在每一个时期的值Yi 和其对应时期Xi 之间的线性依赖关系表示为 Yi=a+bXi+εi (3-9) 式中, ε 表示随机因素。由于其不可预测,因此线性趋势方程可表示为 Fi=a+bXi (3-10) 只要能确定截距 a 和斜率 b ,对于每一个Xi ,就能求出其对应的预测值Fi 。截距 a 和斜率 b 的确定仍应遵循使MSE 极小的原则。 【例3-3】某航空公司10 年间的年总收入数据如表3-2所示,试建立线性趋势预测模 型并预测第11 年的年总收入。 表3- 2 某航空公司10 年间的年总收入数据 年序号总收入(万元) 年序号总收入(万元) 1 2428 6 4264 2 2951 7 4738 3 3533 8 4920 4 3618 9 5318 5 3616 10 6715 81 【解】 (1)用4种不同的方法建立该线性趋势预测模型。 方法一:利用添加趋势线的方法预测。 将总收入数据输入Excel工作表的单元格A1:B11 中,然后利用这些数据绘制出总收 入数据变化折线图,并在图中添加一条趋势线,结果如图3-14 所示。 图3-14 某航空公司年收入时间序列观测值及其趋势线 从图3-14 中可以看出,总收入数据呈线性递增的趋势。图中的公式是年总收入的线性 趋势方程。R2近似等于0.说明该趋势方程较好地表示了总收入数据和时间之间的线性 93, 9, 依赖关系,因此可以用线性趋势方程进行预测。一般情况下,如果R2大于0.则用线性趋 势来做预测;如果R2 9, 如果R2小于0.5, 小于0.则线性预测结果仅供参考; 就需要寻找其他 非线性模型进行预测。 从线性趋势图上可以看出参数a、 b 和R2的值,可以用a+b×A12 的值计算第11 周的 年总收入预测值。 方法二:利用线性趋势方程做预测。 在单元格A12 中输入序号“11”,在单元格I2中输入公式“=INTERCEPT(B2:B11, A2:A11)”,算出线性趋势预测模型的参数a(截距); 在单元格I3中输入公式“=SLOPE (B2:B11,A2:A11)”,算出线性趋势预测模型的参数b(斜率)。在单元格C2 中输入公式 “=$I$2+$I$3*A2,(”) 并复制到单元格C3:C12 内,即可求出第11 年的总收入预测值, 如图3-15 中的单元格C12 所示。 此外,获得线性趋势预测模型的参数 a 和b,还可以使用Excel的内建函数LINEST(), 该函数可以同时获得参数 a 和b。LINEST() 函数是一个数组函数,它的输出为一系列回归 分析结果,包括回归系数的值、标准误差、判定系数等一系列统计量(详见Excel的帮助文 件)。通常只用LINEST() 函数计算回归系数,在此情况下,它的参数设置与INTERCEPT() 和 SLOPE() 函数相同。具体方法如下。 同时将两个相邻单元格H7 和I7选中,输入公式“=LINEST(B2:B11,A2:A11)”后按 Ctrl+Shift+Enter组合键完成数组函数的输入。这时H7 和I7两个单元格的编辑栏中都 会出现相同的公式“=LINEST(B2:B11,A2:A11)”。而在单元格H7 和I7中会分别得到 斜率 b 和截距 a 的值400.6和2006.93 。注意,由于Excel把LINEST() 函数的输出格式设 82 置成 b 在前、 a 在后,从左到右排列,因此必须在两个左右并列的单元格中建立此公式。 方法三:利用El的内建函数FORECAST() 计算预测值。 FORECAST() 函(x) 数(c) 需要3个参数,(e) 第1个参数是一个新的时间点,第2个参数是一组 已知的预测变量观测值,第3个参数是对应时间点的集合。因此在单元格D2 中输入公式 “=FORECAST(A2,$B$2:$B$11,$A$2:$A$11),(”) 并复制到单元格D3:D12 内, 如图3-15 中D列单元格所示。实际上要求的预测值是单元格D12 的值,但是这个方法可 以求出每个月的预测值。 方法四:利用Excel的内建函数TREND() 计算预测值。 TREND() 函数需要3个参数,第1个参数是一组已知的预测变量观测值,第2个参数 是对应时间点的集合,第3个参数是一个新的时间点。因此在单元格E2 中输入公式“= TREND($B$2:$B$11,$A$2:$A$11,A2),(”) 并复制到单元格E3:E12 内,结果如 图3-15 中E列单元格所示。 图3-15 某航空公司年收入时间序列 (2)利用单元格B2:C12 内的数据可以绘制线性趋势预测图形。 从图3-15 可以看出,以上几种方法所得到的预测值大致相同。我们可以取一列(C列、 D列或E列)与观测值(B列)绘制如图3-16 所示的线性趋势预测图,并可对最后一个预测 点做必要的格式设置,使其明确地显示未来的预测值,这是添加趋势线方法所无法实现的。 图3-16 某航空公司年总收入时间序列观测值及其趋势预测值 83 对于具有线性趋势预测模型的数据,也可以用第4章介绍的线性回归分析方法进行 预测。 3.2 非线性趋势预测 3. 前文所讨论的线性趋势预测方法是一种简单而有效的方法,但是在很多实际问题中,时 观看视频间序列更多呈现出非线性的变化趋势,这时候如果坚持用线性趋势预测方法进行预测则无 法取得最佳效果,必须采用非线性趋势预测方法。 简单地说,非线性趋势预测可以采用添加趋势线的方法,根据观测值的特点,趋势线可 以选择指数曲线、对数曲线、幂函数曲线和多项式曲线等。如果数据与这些趋势线曲线不相 符,也可以用其他函数曲线方程做趋势预测。下面通过一个示例介绍这两种方法的使用。 【例3-4】某食品公司以往6年的年销售额数据如表3-3所示,针对该数据表,建立时 间序列趋势预测模型,并预测该公司第7年的年销售额。 表3- 3 某食品公司年销售额数据 年序号年销售额(元) 年序号年销售额(元) 1 23100 4 92000 2 57300 5 160000 3 59000 6 220000 【解】 (1)用两种不同的方法建立非线性趋势预测模型 。 方法一:利用添加趋势线的方法预测 。 ① 输入各年销售额,绘制趋势图。 将各年销售额输入到Excel工作表的单元格A1:B7 中,然后利用这些数据绘制出年销 售额变化折线图。在图中添加一条合适的趋势线,如图3-17 所示。 图3-17 某食品公司年销售额及其趋势线 从图3-17 中可以看出,年销售额呈指数递增的趋势。图中的公式是年销售额的指数趋 84 85 势方程。R2近似等于0.96,说明该趋势方程较好地表示了年销售额数据和时间之间的依赖 关系,因此可以用它来做预测。 注意,该指数趋势线是以e为底,对应的公式为 Fi =aebXi (3-11) ② 从指数趋势图中可以看出参数a、b 和R2的值,将参数a 和b 的值代入指数模型 式(3-11)即可得到第7年的年销售额预测值,如图3-18中单元格C8所示。 图3-18 某食品公司年销售额时间序列 方法二:利用Excel内建函数LOGEST()的方法预测。 LOGEST()函数是一个数组函数,该函数可以同时获得参数a 和b。LOGEST()函数计 算最符合数据的指数拟合曲线,并返回描述该曲线的数值数组。因为此函数返回数值数组, 所以必须以数组公式的形式输入。此曲线的公式为 Yi =bmXi +εi (3-12) 式中,因变量Yi 与自变量Xi 是指数函数关系;m 值是各指数Xi 的底;b 是常量。 该函数带有4个参数,格式为LOGEST(known_ys' ,known_xs' ,const,stats),第1个参 数为满足指数回归拟合曲线Yi =bmXi 的一组已知的Yi 值;第2个参数为满足指数回归拟 合曲线Yi =bmXi 的一组已知的Xi 值;第3个参数为一逻辑值,是可选参数,用于指定是否 将常量b 强制设为1,如果const为TRUE 或省略,则b 将按正常计算;如果const为 FALSE,则常量b 将设为1,而m 的值满足公式Yi=bmXi ;第4个参数为一逻辑值,指定是 否返回附加回归统计值。在本例中具体操作如下: 同时将两个相邻单元格F3和G3选中,输入公式“=LOGEST(B2:B7,A2:A7,TRUE, FALSE)”,按Ctrl+Shift+Enter组合键完成数组函数的输入。这时单元格F3和G3的编 辑栏中都会出现相同的公式“=LOGEST(B2:B7,A2:A7,TRUE,FALSE)”,而在单元格 F3和G3中会分别得到参数m 和b 的值1.526和18113.53。注意,由于Excel把LOGEST()函 数的输出格式设置成m 在前、b 在后,从左到右排列,因此必须在两个左右并列的单元格中 建立此公式。 利用单元格F3和G3中得到的参数值,利用式(3-12),在单元格D2中输入公式“=$G $3*$F$3^A2”,并复制到单元格D3:D8中,即可得到预测值,如图3-18中D列所示。 从图3-18中可以看出,两种预测方法得到的数据之间有一定的误差,这是因为同样是 指数预测模型,但底数不一样导致的差异。通过单元格F5:F6中计算的MSE可以看出,由 式(3-12)得到的预测结果比由式(3-1)得到的预测结果精度稍高一点,这是因为式(3-12)选 择的是最适合的底数。 重叠 ( 。 2)利用单元格B2:D8 内的数据绘制如图3-19 所示的图形,预测值1和预测值2几乎 图3-19 某食品公司年销售额时间序列观测值及其非线性趋势预测值 3.季节指数预测 4 观看视频 许多时间序列不仅含有趋势成分,还含有季节成分。例如,用电量会在夏季和冬季上 升,而在春季和秋季下降,但是长期来看,它可能是增加的。因此,对于这一类型的时间序 列,首先需要剔除季节影响,然后再看这个消除季节影响的时间序列是否存在长期趋势。如 果存在线性趋势,那么就可以用线性趋势预测模型进行预测,然后用估计的季节影响对预测 值作出调整。事实上,这是一种对时间序列进行分解的方法,一般建立在下列乘法模型的基 础上: Yt=Tt×St×I(3-13) 式中,Tt 表示趋势成分,St 表示季节成分,It 表示不规(t) 则成分。 由于不规则成分的不可预测性,因此预测值可表示为趋势成分和季节成分的乘积。季 节指数预测法的一般步骤如下: (1)计算每一季(每季度、每月等)的季节指数St 。 (2)用时间序列的每一个观测值除以适当的季节指数,消除季节影响。 (3)为消除了季节影响的时间序列建立适当的趋势模型并用这个模型进行预测。 (4)用预测值乘季节指数,计算最终的带季节影响的预测值。 季节指数预测法是根据呈现季节变动的时间序列观测值,用求算术平均值的方法计算 各月或各季的季节指数,据此达到预测目的的一种方法 。 计算平均季节指数预测法的一般过程如下 。 (1)求出各年同月或同季观察值的平均数(用 A 表示)。 (2)求历年间所有月份或季度的平均值(用 B 表示)。 (3)计算各月或各季度的季节指数,即C=A/B。 86 (4)根据未来年度的全年趋势预测值,求出各月或各季度的平均趋势预测值,然后乘以 相应季节指数,即得到未来年度内各月和各季度包括季节变动的预测值。 下面举例说明怎样在Excel工作表中建立和使用季节指数模型。 【例3-5】某工厂以往4年各季度的电视机销量如表3-4所示。数据具有明显的季节 性波动,试用季节指数预测法预测第5年各个季度的电视机销量[1]。 表3- 4 以往4年内各季度的电视机销量 年季度销量(万台) 年季度销量(万台) 1 4.8 1 6 1 2 4.1 3 2 5.6 3 6 3 7.5 4 6.5 4 7.8 1 5.8 1 6.3 2 2 5.2 4 2 5.9 3 6.8 3 8 4 7.4 4 8.4 【解】 (1)输入观测值并绘制电视机销量变化图。 在Excel工作表单元格A1:C17 内输入电视机销量,如图3-20 所示。图中隐藏了 第6~13 行。把每一年的数据作为一个单独的数据系列绘制在图3-21 中。可以看出,以往 4年的数据呈现出规律性的变化,即第一、二季度减少,第三、四季度增加。从图3-22 中也 可以看出这种规律,并且销量逐年递增。但是要判断这个时间序列存在什么样的趋势,必须 等消除了季节影响之后才能得出结论。由于存在季节成分,所以可以用季节指数法进行 预测。 图3-20 电视机季度销量时间序列 (2)计算季节指数 。 将每年各季度的销量平均值放置在单元格J2:J5中,在单元格J2中输入公式“ = [1] 戴维R. 安德森,等.商务与经济统计[M]张建华,等译.北京:机械工业出版社, . 2000:575. 87 图3-21 电视机销量的年度图形 图3-22 电视机销量时间序列图 AVERAGE(C2,C6,C10,C14)”,并复制到单元格J3:J5 中。在单元格K2 中输入公式“= AVERAGE(C2:C17),(”) 并选中单元格K2:K5,,(”) 将它们合并成一个单元格,且将数值居中显 示。在单元格L2 中输入公式“=J2/$K$2并复制到单元格L3:L5 中,得到4个季度的 季节指数,结果如图3-23 所示。 图3-23 电视机销量的季节指数 (3)消除季节影响。 选中单元格E2:E5,输入公式“=C2:C5/$L$2:$L$5按Ctrl+Shift+Enter组合,(”) 键,并将公式复制到单元格E6:E17 中,得到消除了季节影响的各个季度的趋势值。 利用单元格C2:C17 和E2:E17 内的数据绘制出图3-24 。从图中可以看出,消除季节 88 影响的销量值体现了电视机销量的稳定水平,即在一定程度上消除了电视机销量时间序列 的不规则成分。 图3-24 电视机销量时间序列及其消除季节影响的电视机销量变化图 (4)进行趋势预测。 在单元格F2:F5 中输入公式“=FORECAST(D2:D5,$E$2:$E$17,$D$2:$D $1117)”,然后按Ctrl+Shift+Enter组合键,并将公式复制到单元格F6:F21 中,其中单元 格F18:F21 中的数值即为第5年各季度的趋势预测值。 (5)还原季节影响。,(”) 在单元格G2:G5 中输入公式“=F2:F5*$L$2:$L$5然后按Ctrl+Shift+Enter 组合键,并将公式复制到单元格G6:G21 中,得到带季节成分的各季度的销量预测值,其中 单元格G18:G21 中的数值即为第5年各季度的带季节影响的预测值,结果如图3-25 所示。 图3-25 电视机销量时间序列季节指数预测模型 89