财务预测

移动平均模型月份 销售额（万元） n=3 n=5 n=71 1012 1043 100 101.674 104 102.675 121 108.33 106.006 118 114.33 109.407 116 118.33 111.80 109.148 121 118.33 116.00 112.009 126 121.00 120.40 115.1410 130 125.67 122.20 119.4311 135 130.33 125.60 123.8612 141 135.33 130.60 126.71移 动 平 均1 2 3 4 5 6 7 8 9 10 11 12050100150数 据 点值实 际 值预 测 值移 动 平 均1 2 3 4 5 6 7 8 9 10 11 12050100150数 据 点值实 际 值预 测 值移 动 平 均1 2 3 4 5 6 7 8 9 10 11 12050100150数 据 点值实 际 值预 测 值移 动 平 均1 2 3 4 5 6 7 8 9 10 11 12050100150数 据 点值实 际 值预 测 值指数平滑模型月份 销售额（万元） 阻尼系数0.2 阻尼系数0.4 阻尼系数0.61 1252 162 125.00 125.00 125.003 175 154.60 147.20 139.804 169 170.92 163.88 153.885 182 169.38 166.95 159.936 202 179.48 175.98 168.767 231 197.50 191.59 182.058 250 224.30 215.24 201.639 235 244.86 236.09 220.9810 180 236.97 235.44 226.5911 206 191.39 202.18 207.9512 235 203.08 204.47 207.17指 数 平 滑1 2 3 4 5 6 7 8 9 10 11 120100200300数 据 点值实 际 值预 测 值指 数 平 滑1 2 3 4 5 6 7 8 9 10 11 120100200300数 据 点值实 际 值预 测 值月份 销售量（万元）1 2252 2623 2654 2695 2826 3027 2828 3509 33510 28011 30612 300指 数 平 滑1 2 3 4 5 6 7 8 9 10 11 120100200300数 据 点值实 际 值预 测 值销售费预测模型销售额 9040 8400 9800 11760 10640 11200 9960 12080销售费用 2460 2250 2680 5320 4940 5120 3850 5630y=ax+ba b R20.620989-2590.190.8098866724.651销 售 费 用 预 测160001400012000100008000600040002000001000200030004000500060007000y = 0.621x - 2590.1895R2= 0.8099销 售 额销售费用销 售 费 用 Linear(销 售 费 用 )12580 14210 14260 148204430 6310 5970 6120销售费预测模型销 售 费 用 预 测160001400012000100008000600040002000001000200030004000500060007000y = 0.621x - 2590.1895R2= 0.8099销 售 额销售费用销 售 费 用 Linear(销 售 费 用 )14260 Residual Plot2000015000100005000000.5114260残差9040 Line Fit Plot10000500000500010000904024602460预 测 24608400 Line Fit Plot150001000050000050001000084002460 2460预 测 24609800 Line Fit Plot150001000050000050001000098002460 2460预 测 246011760 Line Fit Plot1500010000500000500010000117602460 2460预 测 246010640 Line Fit Plot1500010000500000500010000106402460 2460预 测 246011200 Line Fit Plot1500010000500000500010000112002460 2460预 测 24609960 Line Fit Plot1500010000500000500010000996024602460预 测 246012080 Line Fit Plot15000100005000005000100001208024602460预 测 246012580 Line Fit Plot15000100005000005000100001258024602460预 测 246014210 Line Fit Plot15000100005000005000100001421024602460预 测 246014260 Line Fit Plot20000150001000050000020004000600080002460 2460预 测 24608400 Line Fit Plot150001000050000050001000084002460 2460预 测 24609800 Line Fit Plot150001000050000050001000098002460 2460预 测 246011760 Line Fit Plot1500010000500000500010000117602460 2460预 测 246010640 Line Fit Plot1500010000500000500010000106402460 2460预 测 246011200 Line Fit Plot1500010000500000500010000112002460 2460预 测 246014260 Line Fit Plot20000150001000050000020004000600080002460 2460预 测 2460回归分析模型 单位：万元年份 销售收入Y 广告支出X1 居民平均收入X22001 300 9 10002002 360 13 15002003 400 16 18002004 440 20 21002005 390 15 18002006 470 23 22002007 520 28 26002008 590 36 32002009 640 42 4100SUMMARY OUTPUT回归统计Multiple R0.995143R Square 0.990309Adjusted R Square0.987079标准误差 12.54245观测值 9方差分析df SS MS F Significance F回归分析 296456.12 48228.0609306.5738 9.10062E-07残差 6943.8782 157.3130336总计 8 97400Coefficients标准误差 t Stat P-value Lower 95% Upper 95%Intercept229.840915.31031 15.01216395 5.5E-06 192.3779567267.3039X Variable 19.2793842.665126 3.4817806360.013114 2.75805564215.80071X Variable 20.0082260.031372 0.262225250.801918 -0.068536914 0.08499RESIDUAL OUTPUT观测值 预测 Y 残差 标准残差1321.5818-21.5818 -1.9868940862362.8125-2.81254 -0.258931823393.1186 6.88139 0.6335241624432.70417.295934 0.6716885075383.83926.160774 0.5671817886461.36498.635142 0.7949805797511.05238.947663 0.8237522478590.2232-0.22325 -0.0205530769653.3033-13.3033 -1.2247483下限 95.0% 上限 95.0%192.3779567 267.30393472.758055642 15.80071205-0.068536914 0.084989717一元线性回归分析模型月份 作业小时 总成本 SLOPE 2.3111891 10 300 INTERCEPT 267.27272 20 320 FORECAST 729.51053 30 3404 40 3505 50 3706 60 4007 70 4308 80 4509 90 47510 100 50011 110 52512 120 550回归直线 y=ax+ba= 2.31b= 267.27R2= 0.99729.51多元线性回归分析模型月份 广告费X1 销售单价X2 销售量Y1 19000 20 160002 22000 24 190003 21000 22 175004 23000 23 220005 22000 22 180006 19000 21 150007 23000 24 210008 25000 26 220009 24000 24 2100010 23500 27 2350011 25000 26 2400012 27500 25 28000回归直线销售模型： y=a1x1+a2x2+ba1 a2 b R2187.14 1.30 -13441.30 0.91销售量的预测: 已知x1=28000 x2=30y= 5226563.181指数模型月份 生产量(万件)X 生产成本（件/元）Y1 10.21 5642 10.35 5363 10.56 5184 10.93 4875 11.16 4736 11.44 4567 11.55 4468 12.07 4219 12.46 40110 12.94 38911 13.35 39112 13.26 378函数计算结果0.88870321 1792.2940780.00778145 0.0913450080.95832006 0.02882689229.923579 10回归拟合模型月份X 生产成本（件/元）Y1 5642 5693 5734 5785 5836 5887 5928 5959 60010 60411 61012 618预测下一年14月份的生产成本13 62014 62415 62916 634Microsoft Excel 11.0 运算结果报告工作表 财务预测.xls一元非线性规划求解报告的建立: 2009-11-9 10:31:08目标单元格 (最小值)单元格 名字 初值 终值$F$4 SE 8.511988335 8.511988335可变单元格单元格 名字 初值 终值$F$2 a -172.0159985 -172.0159985$F$3 b 27.43479236 27.43479236约束无一元非线性规划求解模型产量(X) 收益(Y) 收益估计值 a -172.016470 1.48 -3.2170545 b 27.4347924640 7.95 5.25303932 SE 8.51198833740 7.28 9.23607761825 6.55 12.2191504875 9.18 13.8334273915 16.67 15.0597674940 14.9 15.7992945955 14.91 16.2336274972 15.81 16.71769961024 19.63 18.14749131055 17.41 18.96571311056 17.42 18.99170541132 22.51 20.89836291136 15.39 20.99513481075 22.53 19.48093561240 24.58 23.39836931253 27.93 23.68449491281 24.51 24.29081341285 20.55 24.37634671319 23.31 25.09281051366 27.01 26.05338181403 30.3 26.78660521407 29.52 26.86471141443 29.39 27.557837816001400120010008006004002000-505101520253035收 益 (Y) 收 益 估 计 值16001400120010008006004002000-505101520253035收 益 (Y) 收 益 估 计 值