产销预测模型.xls

《产销预测模型.xls》由会员分享，可在线阅读，更多相关《产销预测模型.xls（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 定定量量需需求求预预测测模模型型1 1、简简单单移移动动平平均均法法下面给出某一企业某月的销售情况，并以不同周期进行了简单平均预测。该模型可由于生产、销售等环节的预测，只要在“实际销量”一栏输入数，就能自动生成各周期预测数值。SFt=（Sat+Sa(t-1)+SA(t-2)+)/n可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。日期实际销量（SAt）预测值（SFt）预测值（SFt）改变该列数据即可预测周期 n=3预测周期 n=412022232041920.6666666752320.3333333320.2562020.6666666

2、72172120.6666666720.582221.3333333320.759232121.510242221.511222322.512232322.7513232323142122.6666666723152222.3333333322.2516202222.2517232121.5182121.6666666721.5192021.3333333321.5202321.3333333321212421.3333333321.75222422.3333333322232223.6666666722.75242123.3333333323.25252022.3333333322.7526

3、182121.75272019.6666666720.25282319.3333333319.75292320.3333333320.25302422212 2、加加权权移移动动平平均均法法加权平均法是给每一个参与预测的数据赋以权重，一般较靠近预测期的实际值所占的权重较大。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“权重指数”栏中的权重值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值和均方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次实际产量（Sa

4、t）预测值（SF）预测值（SF）改变该列数据即可预测周期n=3预测周期n=41100211131124111112.16666675112115.0666667117.4256108115.3666667119.97112113.8666667118.7758114114.26666671199110115.4666667120.210110115.6666667119.511118114.7333333119.5512101116.8666667121.8513108113117.514106111.7116.6515106108.9333333115.82516114110.0666667

5、113.417117112.7333333117.1518107116.8666667120.219118116.1119.420112117.6333333122.52521109116.6122.07522110116.1333333119.723118113.833333312024101116.6121.22525108113117.326106111.7116.6527106108.9333333115.82528114110.0666667113.429117112.7333333117.1530110116.8666667120.231119117.3120.4532118119

6、.1333333123.77533101120.1666667125.1534108115.4119.52535106111.7118.4536106108.9333333115.82537114110.0666667113.438117112.7333333117.1539107116.8666667120.240119116.1119.441112118.0333333122.87542109116.9666667122.37543117116.4119.92544107116.6333333122.6545116114.7666667119.47546112116.8333333120.

7、82547109115.8666667121.47548110115.6119.2549118113.8333333119.650101116.6121.22551108113117.352106111.7116.653、一次平滑指数预测法一次平滑指数法是基于某一波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测

8、值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次实际产量（Sat）a*上月实际销量预测值1100211140110312044.4106412348108511249.2112.8612244.8116.88712348.8114.928812449.2117.7568911049.6119.854081011944121.5124481111847.6116.90746881210147.2117.74448131310840.4117.84668881410643.2111.10801331510642.4109.8648081611442.4108.31888

9、481711745.6107.39133091810746.8110.03479851912142.8112.82087912011248.4110.49252752110944.8114.69551652211043.6113.61730992311844111.77038592410147.2111.06223162510840.4113.83733892610643.2108.70240342710642.4108.4214422811442.4107.45286522911745.6106.87171913011046.8109.72303153111944112.6338189321

10、1847.6111.58029133310147.2114.54817483410840.4115.92890493510643.2109.95734293610642.4109.17440583711442.4107.90464353811745.6107.14278613910746.8109.88567164012142.8112.7314034111248.4110.43884184210944.8114.66330514311743.6113.5979834410746.8111.75878984512142.8113.85527394611248.4111.113164347109

11、44.8115.06789864811043.6113.84073924911844111.90444355010147.2111.14266615110840.4113.88559975210643.2108.73135984 4、二二次次平平滑滑指指数数法法二次平滑指数法是基于某两个波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后

12、面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次实际产量（Sat）a*上月实际销量（1-a）*上月预测量11003077211133.381.0631203685.8088412336.990.805904511233.694.36916832612236.692.63715315712336.992.65628415812437.292.0929217591103391.273417461011935.786.046954361111835.483.948795671210130.382.108229111310832.475.880845

13、021410631.872.957974761510631.870.64100861611434.269.446211261711735.171.532307771810732.174.866698391912136.375.104768022011233.679.36284462110932.780.33795685221103379.905980612311835.479.464879562410130.381.212323282510832.477.604055092610631.876.307780752710631.874.727455022811434.273.5579324129

14、11735.175.16831469301103377.733384523111935.777.970927263211835.480.666695913310130.382.575730453410832.478.916966983510631.877.427796513610631.875.57804163711434.274.117848173811735.175.457570993910732.177.798468454012136.376.989280454111233.680.28813524210932.780.48075734311735.179.483901894410732

15、.180.756034884512136.378.843803794611233.681.147985574710932.780.54115455481103378.977434284911835.477.855537995010130.379.319546025110832.475.740006125210631.874.687527465 5、线线性性回回归归预预测测法法线性回归预测假设数据与月份或周次等存在线性关系，并试图获得这种关系的数学函数，根据开发式算法得到以下的运算方式，从而得到最接近这种关系的函数。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判

16、断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次(x)实际产量（y）预测值x*y1100114.05878081002111113.97502772223120113.89127473604123113.80752164925112113.72376855606122113.64001547327123113.55626238618124113.47250929929110113.388756199010119113.305003119011118113.2212499129812101113.1374968121213108113.0537437140414

17、106112.9699906148415106112.8862375159016114112.8024844182417117112.7187313198918107112.6349782192619121112.5512251229920112112.467472224021109112.3837189228922110112.2999658242023118112.2162128271424101112.1324597242425108112.0487066270026106111.9649535275627106111.8812004286228114111.79744733192291

18、17111.7136942339330110111.6299411330031119111.546188368932118111.4624349377633101111.3786818333334108111.2949287367235106111.2111756371036106111.1274225381637114111.0436694421838117110.9599163444639107110.8761632417340121110.7924101484041112110.708657459242109110.624904457843117110.54115095031441071

19、10.4573978470845121110.3736447544546112110.2898916515247109110.2061385512348110110.1223854528049118110.0386323578250101109.9548792505051108109.8711261550852106109.7873735512预测值（SFt）预测值（SFt）预测值（SFt）预测周期 n=5预测周期 n=6预测周期 n=72021.420.6666666720.420.8333333317.5714285720.820.8333333318.1428571421.621.333

20、3333318.4285714321.822.1666666718.4285714322.82219.2857142922.622.519.142857142322.8333333319.428571432322.6666666719.285714292222.519.1428571422.221.833333331921.22218.7142857121.821.6666666718.5714285721.421.1666666718.4285714320.821.518.285714292221.833333331922.422.518.857142862322.3333333319.42

21、8571432322.3333333319.2857142922.422.3333333318.7142857121.421.518.2857142919.820.8333333317.8571428619.820.666666671820.820.8333333318 定定量量需需求求预预测测模模型型下面给出某一企业某月的销售情况，并以不同周期进行了简单平均预测。该模型可由于生产、销售等环节的预测，只要在“实际销量”一栏输入数，就能自动生成各周期预测数值。可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。加权平均法是给每一个参与预测的数据

22、赋以权重，一般较靠近预测期的实际值所占的权重较大。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“权重指数”栏中的权重值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值和均方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。预测值（SF）预测值（SF）预测周期（n）预测周期n=5预测周期n=63456110.3实际值方差110.3109.6166667预测值方差n=3110.96110.7833333预测值方差n=4111.32111.7666667预测值方差n

23、=5111.88110.8333333预测值方差n=6110.96111.4333333112.8112.8109.68109.6833333109.42109.4166667105.5108.9166667108.04105.5166667107.96109.7111.84109.9833333111.58110.3113.4113.2333333112.3113.0333333113.44111.3109.98112.85113.22112.0166667109.44110.0333333109.3109.2166667105.5108.8166667108.04105.516666710

24、7.96109.7111.84109.9833333112.54111.05114.5114.1333333115.38115.3112.74111.5333333109.42111.9666667106.58108.9166667108.04106.4166667107.96109.7111.84109.9833333111.58110.3113.72113.4833333112.56113.25113.74111.4833333112.22114.85111.64110.7833333113.48112.7833333111.18113.2112.84110.4333333109.9811

25、2.35112.98112.0166667109.44109.8333333109.3109.2166667（1-a）*上月预测量平滑指数0.4首次假设预测值1106663.6实际值方差43.3273001564.8预测值方差12.5219213967.6870.12868.956870.6540871.91244872.907468870.1444812870.6466887770.7080132666.6648079665.9188847764.9913308664.4347985266.0208791167.6925274766.2955164868.8173098968.1703859

26、367.0622315666.6373389468.3024033665.2214420265.0528652164.4717191364.1230314865.8338188967.5802913366.948174868.72890488一次平滑指数法是基于某一波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预

27、测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。69.5573429365.9744057665.5046434564.7427860764.2856716465.9314029967.6388417966.2633050768.7979830468.1587898367.055273968.3131643466.667898669.0407391668.304443567.142666166.6855996668.331359865.23881588预测值1b（预测值n+1-预测值n（1-b)*预测值2预测值2100101072.868.8114.362.9445.

28、288.224121.80882.979524.93447.91392127.7059042.35884164.7483527.1071936127.96916830.1053057284.264316164.369621888129.23715310.507193932.6217731333.128967063129.55628410.12765241.8773802382.005032638129.2929217-0.1053449591.2030195831.097674624124.2734175-2.0078017150.658604774-1.349196941121.746954

29、4-1.010585239-0.809518164-1.820103403119.3487957-0.959263477-1.092062042-2.051325519112.4082291-2.776226626-1.230795311-4.007021937108.280845-1.650953635-2.404213162-4.055166798104.7579748-1.409148106-2.433100079-3.842248184102.4410086-0.926786462-2.30534891-3.232135373103.64621130.482081064-1.93928

30、1224-1.45720016106.63230781.194438604-0.8743200960.320118508106.96669840.133756250.1920711050.325827355111.4047681.7752278520.1954964131.970724265112.96284460.6232306311.1824345591.80566519二次平滑指数法是基于某两个波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自

31、动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。113.03795690.0300449011.0833991141.113444015112.9059806-0.0527904980.6680664090.615275911114.86487960.7835595820.3691655461.152725128111.5123233-1.3410225120.691635077-0.649387435110.0040551-0.6

32、03307276-0.389632461-0.992939737108.1077808-0.758509738-0.595763842-1.35427358106.527455-0.632130292-0.812564148-1.44469444107.75793240.492190954-0.866816664-0.37462571110.26831471.004152913-0.2247754260.779377487110.73338450.1860279340.4676264920.653654426113.67092731.1750170970.3921926561.56720975

33、2116.06669590.9583074590.9403258511.89863331112.8757305-1.2763861821.139179986-0.137206196111.316967-0.62350539-0.082323718-0.705829107109.2277965-0.835668188-0.423497464-1.259165652107.3780416-0.739901964-0.755499391-1.495401355108.31784820.375922628-0.897240813-0.521318185110.5575710.895889128-0.3

34、127909110.583098217109.8984684-0.2636410180.349858930.086217912113.28928051.3563248020.0517307471.408055549113.88813520.23954190.8448333291.084375229113.1807573-0.282951160.6506251370.367673978114.58390190.5612578380.2206043870.781862224112.8560349-0.6911468040.469117335-0.22202947115.14380380.91510

35、7562-0.1332176820.781889881114.7479856-0.1583272880.4691339280.31080664113.2411545-0.6027324090.186483984-0.416248425111.9774343-0.505488105-0.249749055-0.755237159113.2555380.511241481-0.4531422960.058099185109.619546-1.4543967870.034859511-1.419537275108.1400061-0.59181596-0.851722365-1.4435383251

36、06.4875275-0.660991466-0.866122995-1.527114461(x*y)x*yX2X2153249801996014823049162536496481线性回归预测假设数据与月份或周次等存在线性关系，并试图获得这种关系的数学函数，根据开发式算法得到以下的运算方式，从而得到最接近这种关系的函数。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。10012114416919622525628932436140044148452957662567672978484

37、1900961102410891156122512961369144415211600168117641849193620252116220923042401250026012704实际值方差2.7 实际值均方差1.643168预测值方差n=31.248179804 预测值均方差n=31.11722预测值方差n=41.261538462 预测值均方差n=41.123182预测值方差n=50.935 预测值均方差n=50.966954预测值方差n=60.959347826 预测值均方差n=60.979463预测值方差n=70.463219148 预测值均方差n=70.680602下面给出某一企业

38、某月的销售情况，并以不同周期进行了简单平均预测。该模型可由于生产、销售等环节的预测，只要在“实际销量”一栏输入数，就能自动生成各周期预测数值。可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。可根据后面算出的方差值和均方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。权重指数a1a2a3a4a50.81.11.20.80.91.21.40.60.70.81.31.60.60.70.81.11.327 实际值均方差5.1961524237.507888

39、322 预测值均方差n=32.7400526136.881495734 预测值均方差n=42.6232605165.40053802 预测值均方差n=52.3239057684.49844015 预测值均方差n=62.120952652实际值均方差6.582347617预测值均方差3.538632701以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值

40、均方差差异越小，预测值越可靠。最终预测值平滑指数a110115.8平滑指数b122.584首次假设预测值1129.72272首次假设预测值2134.8130976132.3387902实际值方差132.3661202预测值方差131.5613168130.3905964122.9242205119.926851117.2974702108.4012072104.2256782100.915726699.20887323102.1890111106.9524263107.2925257113.3754923114.7685098以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏

41、中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。114.1514009113.5212565116.0176047110.8629358109.0111154106.7535072105.0827606107.3833067111.0476922111.3870389115.238137117.9653292112.7385243110.6111379107.9686309105.882

42、6402107.79653111.1406692109.9846864114.697336114.9725104113.5484313115.3657641112.6340054115.9256937115.0587922112.8249061111.2221971113.3136372108.2000087106.6964678104.960413(x)2nbyx189888452-0.08375309558201378线性回归预测假设数据与月份或周次等存在线性关系，并试图获得这种关系的数学函数，根据开发式算法得到以下的运算方式，从而得到最接近这种关系的函数。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。a61.50.30.41001043.32730015 实际值均方差6.58234761768.51639985 预测值均方差8.27746337a线性回归方程实际值方差43.3273114.1425339 y=114.1425+（-0.08375）*x预测值方差1.611015实际值均方差6.582348预测值均方差1.269258