实验六 季节因素分析.doc

实验六 季节因素分析091230126 琚锦涛一、 实验目的及要求(一) 目的1) 掌握利用EXCEL的命令进行四项移动平均法运算2) 掌握利用EXCEL的命令进行二项移动平均法运算3) 学会剔除长期趋势4) 学会消除不规则变动的影响，求季节比率5) 掌握利用EXCEL的命令计算消除季节变动前后的趋势值(二) 内容及要求利用乘法模型确定季节变动趋势因子二、 实验内容19971998199920004年合计同季平均数季节比率（%）一10121618561479.15194346二151820247719.25108.8339223三172024268721.75122.9681979四111418206315.7589.0459364合计5364788828370.75100(一) 四项移动平均法测长期趋势，得出如下表格：年份季节销售量（y)四项平均移动二项平均移动(TC)199711021531713.2513.541113.7514.125199811214.514.87521815.2515.6253201616.54141717.25199911617.51822018.51932419.519.754182020.520001182121.2522421.521.7532622420(二) 剔除长期趋势，得出如下表格：年份季节销售量（y)四项平均移动二项平均移动(TC)SR(%)=y/TC199711021531713.2513.5125.925925941113.7514.12577.87610619199811214.514.87580.6722689121815.2515.625115.23201616.5121.21212124141717.2581.15942029199911617.51888.8888888922018.519105.263157932419.519.75121.51898734182020.587.8048780520001182121.2584.7058823522421.521.75110.344827632622420(三) 消除不规则变动的影响，求出季节比率，如下表所示：一二三四全年合计1997125.925925977.87610619203.8020321199880.67226891115.2121.2121212812438104199988.88888889105.2631579121.518987387.80487805403.4759122200084.70588235110.3448276195.0507099四年合计254.2670401330.8079855368.6570345246.84040451200.572465同季平均数84.75568005110.2693285122.885678282.28013484100.0477054季节比率%84.7152663110.2167492122.82708382.2409015400(四) 计算消除季节变动后销售量：年份季节销售量（y)四项平均移动二项平均移动(TC)SR(%)=y/TC季节比率S(%)消除季节变动后销售量（y/S)199711084.715266311.80424785215110.216749213.6095467531713.2513.5125.9259259122.82708313.8405957241113.7514.12577.8761061982.240901513.37533976199811214.514.87580.6722689184.715266314.1650974221815.2515.625115.2110.216749216.33145613201616.5121.2121212122.82708316.283053794141717.2581.1594202982.240901517.0231597199911617.51888.8888888984.715266318.8867965622018.519105.2631579110.216749218.1460623332419.519.75121.5189873122.82708319.539664554182020.587.8048780582.240901521.8869196120001182121.2584.7058823584.715266321.2476461322421.521.75110.3448276110.216749221.7752747932622122.82708321.1679699342082.240901524.31879957(五) 根据表格中的数据进行回归统计：SUMMARY OUTPUT回归统计Multiple R0.977354R Square0.955220841Adjusted R Square0.95202233标准误差0.818534881观测值16方差分析dfSSMSFSignificance F回归分析1200.092255200.092255298.64544597.71512E-11残差149.3799909170.669999351总计15209.4722459Coefficients标准误差t StatP-valueLower 95%Upper 95%下限 95.0%上限 95.0%Intercept11.191896110.42924331326.073547972.8782E-1310.2712607612.1125314510.2712607612.11253145X Variable 10.7671418590.04439128717.281361237.71512E-110.6719320190.86235170.6719320190.8623517RESIDUAL OUTPUTPROBABILITY OUTPUT观测值预测 Y残差标准残差百分比排位Y111.95903796-0.154790116-0.1957436343.12511.80424785212.726179820.8833669221.1170832869.37513.37533976313.493321680.347274040.43915389615.62513.60954675414.26046354-0.88512378-1.11930496321.87513.84059572515.0276054-0.862507984-1.09070560428.12514.16509742615.794747260.5367088340.67870830734.37516.28305379716.56188912-0.278835328-0.35260804640.62516.3314561817.32903098-0.305871282-0.38679702446.87517.0231597918.096172840.7906237180.99980259553.12518.146062331018.8633147-0.71725237-0.90701905859.37518.886796561119.63045656-0.090792007-0.11481325765.62519.539664551220.397598421.4893211951.88335760771.87521.167969931321.164740280.0829058510.10484062578.12521.247646131421.93188214-0.156607342-0.19804165184.37521.775274791522.69902399-1.531054065-1.93613193290.62521.886919611623.466165850.8526337141.07821885496.87524.31879957得出季节变动后的时间序列的趋势方程为SUMMARY OUTPUT回归统计Multiple R0.762087271R Square0.580777008Adjusted R Square0.550832509标准误差3.12170835观测值16方差分析dfSSMSFSignificance F回归分析1189.0066176189.006617619.395114960.00060019残差14136.43088249.745063025总计15325.4375Coefficients标准误差t StatP-valueLower 95%Upper 95%下限 95.0%上限 95.0%Intercept11.351.637037676.9332552396.94609E-067.83890340914.861096597.83890340914.86109659X Variable 10.7455882350.1692984054.4039885280.000600190.382479271.10869720.382479271.1086972RESIDUAL OUTPUTPROBABILITY OUTPUT观测值预测 Y残差标准残差百分比排位Y112.09558824-2.095588235-0.694856683.12510212.841176472.1588235290.7158242869.37511313.586764713.413235294162512414.33235294-3.332352941-1.10494402721.87514515.07794118-3.077941176-1.02058598728.12515615.823529412.1764705880.7216757134.37516716.569117653.430882353162517817.31470588-3.314705882-1.09909260246.87518918.06029412-2.060294118-0.68315383153.125181018.805882351.1941176470.39594640359.375181119.551470594.4485294121.47504663765.625201220.29705882-2.297058824-0.76166044571.875201321.04264706-3.042647059-1.00888313878.125201421.788235292.2117647060.7333785684.375241522.533823533.4661764711.1493173390.625241623.27941176-3.279411765-1.08738975396.87526得出原时间序列的趋势方程：由趋势方程可求出相应的趋势值：年份季节消除季节变动后趋势值消除季节变动前趋势值1997111.9590379612.09558824212.7261798212.84117647313.4933216813.58676471414.2604635414.332352941998111.9590379612.09558824212.7261798212.84117647313.4933216813.58676471414.2604635414.332352941999111.9590379612.09558824212.7261798212.84117647313.4933216813.58676471414.2604635414.332352942000111.9590379612.09558824212.7261798212.84117647313.4933216813.58676471414.26046354