实验四金融统计学 6页

实验四动态数列指标在金融中的计算和应用二学号：2013014874姓名：孙影莉专业：统计学131实验要求根据2001年至2015年环比CPI指数完成下而的实验要求，并撰写实验报告。（1）按月平均杳看环比CPI是否具有季节性；a）求出各年每个月的平均数；b）求出总的月平均数；c）计算季节比率；d）绘制季节比率图；（2）按移动平均趋势剔除法杳看环比CPI是否具有季节性（按照除法剔除趋势值）；a）先按照12个月计算移动平均数；b）对12个刀移动平均数再做两项移动平均求得趋势T；c）求岀各月的季节比率；d）求消除不规则变动因素后的季节比率；e）调整季节比率；f）绘制调整后季节比率图。二、实验结果（1）按月平均查看环比CPI是否具有季节性;a）求出各年每个月的平均数;200420052006200720082009201020112012201320142015毎月平均申100.2105100.0614100.3802101.0646100.406599.9560100.8650100.1500100.2087100.6072100.0956AVERAGE(K3明99.597899.502699.3856101.761099.7179100.796998.8354100.890899.256399.747898.955998.9768101.199100.7816100.7984100.1664[AVERAGECnumb102.4325100.3793101.1540100.918599.65387100.8856100.3119101.0442>100.85499.740698.918598.796998.979198.738899.307498.645698.9129100.006998.8734299.3385799.3509799.106100.178799.597699.960699.278399.6223100.0289100.036099.800199.7439699.9418299.5548299.6795599.77599.688199.8734100.0387100.021299.559199.920499.8274100.011199.5445999.1438899.9625599.68370299.73199.059398.956498.981699.592299.424799.169798.687699.586899.2123499.7713699.7669899.8668499.26299.703699.931399.3783100.177199.882599.734099.874299.938399.9209799.8918999.9958499.781101.4630101.0880101.3002101.7233101.0481101.4638101.4304101.1071100.3909100.3161100.0957101.091100.7277100.5321100.552499.8340100.3617100.4239100.3114100.2304100.0873100.6239100.3662100.44599.8406100.241799.966299.8330100.0595100.0194100.5702100.013099.7213399.9292999.86269100.06099.472799.613699.948799.935499.163099.8928100.366099.189199.9209799.7213399.6712699.805b）求出总的月平均数;\n200120022003200420052006200720082009201020112012201320142015'100.1765100.35812100.2105100.0614100.3802101.0646100.406599.9560100.8650100.1500100.2087100.6072100.09561'100.1789599.288399.9816899.597899.385699.717998.835499.256399.747898.955998.9768101.199100.7816100.7984100.16644101.2766100.9488799.5026101.7610100.7969100.8908102.4325100.3793101.1540100.918599.65387100.8856100.3119101.0442298.659399.2081599.740698.918598.796998.979198.738899.307498.645698.9129100.006998.8734299.3385799.35097499.754299.67074100.178799.597699.960699.278399.6223100.0289100.036099.800199.7439699.9418299.5548299.67955499.760999.2056799.688199.8734100.0387100.021299.559199.920499.8274100.011199.5445999.1438899.9625599.68370299.142098.4514699.059398.956498.981699.592299.424799.169798.687699.586899.2123499.7713699.76698,,99.866844.99.470199.396599.3329699.703699.931399.3783100.177199.882599.734099.874299.938399.9209799.8918999.99584101.14101.2757101.48209101.4630101.0880101.3002101.7233101.0481101.4638101.4304101.1071100.3909100.3161100.0957100.931100.8513100.88465100.7277100.5321100.552499.8340100.3617100.4239100.3114100.2304100.0873100.6239100.3662100.385100.1178100.6088199.8406100.241799.966299.8330100.0595100.0194100.5702100.013099.7213399.9292999.86269.99.897L.99.8278100.7372599.472799.613699.948799.935499.163099.8928100.366099.189199.9209799.7213399.67126-调整系数总的月平均=AVERAGE(.T10:J14.K3:Vh―1Vig—rl01J.K——■总的月平吗=AVERAGE(J10:J14,K3:W14,X3:X9)山00・0047[c)计算季节比率；季节比率二同月平均值/总平均值2015I.每月平均值.季节比率100.17895100.16644:100.337:=Y3/$X$16*100yy.^03101.04422100.854100.84999.35097499.10699.10199.67955499.77599.77099.68370299.73199.72799.86684499.26299.25799.78199.777101.091101.086100.445100.440100.060100.056■99.80599.800L总的月平吗1200.01111199.954[100.0047Jd）绘制季节比率图；季节比率1ZJ4OO(C刀1U111Z\n月份由上图可以看111CPI有两个高峰值，分別是2月份到3月份Z间有一个高峰值，由于此时\n段是春节，人民准备节Lk9刀份到10J1份Z间冇一个最高的峰值。（2）按移动平均趋势剔除法查看环比CPI是否具冇季节性（按照除法剔除趋势值）；a）先按照12个月让算移动平均数；各地居民消费和商品零售价格指数—指标1价格指数当月（上月=100）101.912月移动平均趋势T（两项平均）除法剔岀趋势（幻i法剔除趋势值（％）2100.2399.441100.21599.5698.8100.1799.599.9100.030030199.47012899■0.58101.1100.099.9605653101.13988421.19二iVERA；｝E(C7:C18)100.93067920.910100.3AVERAGE(numbe1,[number2],...)|)1100.38489850.411199.899.999.9027845499.89711544-0.1112100.199.999.92367278100.17646190.2|199.299.999.9319808799.28825804-0.72101.299.999.93630312101.27663131.3J3■98.6.99.999.9448046398.65925534-1.34799.799.999.94059699.75421767-0.2599.799.999.9405792499.76089975-0.26一99.1100.099.9573718299.14195908-0.9b）对12个月移动平均数再做两项移动平均求得趋势T；各地居民消费和商品零售价格指数1当月（上月=100）101.9居民消晉价格指数i法剔除趋势值（%）12月移动平均趋势T（两项平均）除法剔岀趋势（幻2100.2399.44100.2599.5698.8r100.1799.5'99.9f100.030030199.47012899-0.58101.1'100.099.9605653101.13988421.19100.9(二AVERAGEA(D11:D120.9110100.3r99.9AVERAGEA(value1,alue2]f••・)$8489850.4r1199.899.999.9027845499.89711544-o.1t12100.199.999.92367278100.17646190.2199.299.999.9319808799.28825804・0.72101.299.999.93630312101.27663131.3J398.699.999.9448046398.65925534-1.3499.799.999.94059699.75421767-0.2599.799.999.9405792499.76089975-0.2c）求岀各月的季节比率；季节比率=该月环比CPI值/该月趋势值\n各地居民消费和商品零售价格指数居民消费价格指数5月（上月mo）101.9100.299.4100.299.512月移动平均恥1199.5'；99.9:100.0300301=C10/E10*100Iw-0.51mi1mnnQQ]1m12QSS49M1.1100.999.999.96960369r100.93067920.9100.399.999.91542701100.3848985匚0.499.899.999.90278454T99.89711544V-o.1100.199.999.92367278I100.17646190.299.299.999.9319808799.28825804-0.7101.299.999.93630312L101.276631311.398.699.999.94480463Il98.65925534_-1.399.799.999.940596I99.75421767-0.299.799.999.9405792499.76089975匚-0.2QQ1mnnQQQR7Q7129QQ1A1QRQA2-nq总月份的平均值100.2趋势T（亦页平均）除法剔岀趋势（幻i法剔除趋势值（％）d）求消除不规则变动因索后的季节比率;计算每个月的环比CPI平均值。e）调整季节比率;调整系数=1200%/真实数值屮合计的季节比率调整后的季节比率二调整而的季节比率*调整系数20142015每月平均占季节比率」岂整后的季节比#100.0956100.17895100.337|：100.332J=AA3*松$16[100.7984100.1664499.76399.759—I99.76257187100.3119101.04422100.854100.849100.853124599.3385799.35097499.10699.10199.1046016899.5548299.67955499.77599.77099.773915699.9625599.68370299.73199.72799.7305675699.7669899.86684499.26299.25799.2611760599.9958499.78199.77799.78043527100.0957101.091101.086101.0901675100.3662100.445100.440100.444219299.86269100.060100.056100.059350499.6712699.80599.80099.80370187调整系数"的月平均1200.01111199.9541200'1.000038]00.0047\nf)绘制调整后季节比率图。调整后的季卩比率从上图可以看出在2月份到3月份之间有一个高峰值，9月份到1()月份之间有一个最高的峰值。分析：通过以上的分析，我们可以看出采用剔除趋势值求季节比率和以按月平均法求季节比率所揭示的现象的规律具有一致性，都呈现这样的特点：CPI的季节变动具有明显的季节变化，2月份到3月份之间和9月份到1()月份之间有两个明显的峰值之外，从7月份一直到9刀份CPI达到峰值。九刀份Z后开始逐步下降。发生这种现象的原因可能是在2月份到3月份之间正处于春节期间，中国的传统节|_1,普天同庆，人们会准备节口用的东西此时CPI会明显的增加。而9月份到10月份之间是属于秋季收获的季节，所以此时的CPI也会增加。