Excel 财务应用 平均数趋势预测长期销售额

Excel 财务应用 平均数趋势预测长期销售额

平均数趋势整理法是根据历史上各期的实际资料求出波动周期同期平均数或各年的月（或季）平均数。然后利用求出的各期平均数建立趋势预测模型，求出各期趋势值。之后，以同期平均数除以各期趋势值，在此基础上计算季节或波动指数。最后结合季节或波动指数和趋势预测模型进行预测的方法。

1．计算季度合计值与平均值

某公司2005年到2007年各季度销售业绩如图8-96所示，试用平均数赵趋势整理法预测2008年6月至9月的销售业绩。首先，在工作表中，输入相应的数据信息，如图8-96所示。

图8-96 基础数据及计算模型 图8-97 计算合计值与平均值

分别在F3、G3、B6和G6单元格中，输入“=SUM(B3:E3)”、“=AVERAGE(B3:E3)”、“=AVERAGE(B3:B5)”和“=AVERAGE(G3:G5)”公式，即可计算出年合计、季度平均和三年平均值。然后，拖动复制到相应单元格，得到结果如图8-97所示。

2．最小二乘法模型

最小二乘法是处理各种观测数据进行测量平差的一种基本方法。如果以不同精度多次观测一个或多个未知量，为了求定各未知量的最可靠值，各观测量必须加改正数，使其各改正数的平方乘以观测值的权数的总和为最小，因此称最小二乘法。

选择“季平均销售额”、“ty ”和“t 2”所对应的单元格，分别输入“=G3”、“=C16*B16”和“=POWER(B16,2)”公式，并将公式拖动复制到相应单元格中，如图8-98所示。

图8-98 计算季平均销售额等 图8-99 计算合计值

选择C19单元格，输入“=SUM(C16:D18)”公式，并拖动此公式至该行的其他单元格中，如图8-99所示。

选择C20至C21和G20至G21单元格区域，分别输入“=C19/3”、“=E19/G19”、“=C20+G21/2”和“=C21/4”公式，即可计算出各季节趋势参数系数，如图8-100所示。

图8-100 季节趋势模型参数系数计算

计算

输入基

本信息 计算

计算

3．计算预测值和比值

选择B8和B9单元格，分别输入“=$G$20+B7*$G$21”和“=B6/B8”公式，即可计算出2006年各季度趋势值和比值，如图8-101所示。

计算

计算

图8-101 2006年各季度预测值及比值图102 计算合计值

选择F8和F9单元格，分别输入“=SUM(B8:E8)”和“=SUM(B9:E9)”公式，计算合计值，如图8-102所示。

4．进行预测

在进行预测前需要首先对季节指数进行修正。在上述比值中，其和为4.007753，本来4个季度的和应该是4，因此对季度指数进行修正时，应输入公式“=4/4.007753”，即可得到修正指数，如图8-103所示。

计算计算

图8-103 计算修正指数图8-104 计算季节指数

选择“季节指数”所以应的单元格，输入“=B9*$H$9”公式，并拖动复制到相应单元格，即可得到如图8-104所示的季节指数。

根据使用趋势季节模型Yt=(568.0052+13.84375)*季节指数来预测，2008年6月的预测值计算公式为“=($G$20+$G$21*B11)*B10”公式，然后复制此公式至该行的其他单元格中，即可计算出2008年的各季度的预测值，如图8-105所示。

预测

计算

图8-105 进行预测