产销预测模型.xls

定定量量需需求求预预测测模模型型1 1、简简单单移移动动平平均均法法下面给出某一企业某月的销售情况，并以不同周期进行了简单平均预测。该模型可由于生产、销售等环节的预测，只要在“实际销量”一栏输入数，就能自动生成各周期预测数值。SFt=（Sat+Sa(t-1)+SA(t-2)+)/n可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。日期实际销量（SAt）预测值（SFt）预测值（SFt）改变该列数据即可预测周期 n=3预测周期 n=412022232041920.6666666752320.3333333320.2562020.666666672172120.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.7526182121.75272019.6666666720.25282319.3333333319.75292320.3333333320.25302422212 2、加加权权移移动动平平均均法法加权平均法是给每一个参与预测的数据赋以权重，一般较靠近预测期的实际值所占的权重较大。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“权重指数”栏中的权重值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值和均方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次实际产量（Sat）预测值（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.0666667113.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.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.82547109115.8666667121.47548110115.6119.2549118113.8333333119.650101116.6121.22551108113117.352106111.7116.653、一次平滑指数预测法一次平滑指数法是基于某一波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次实际产量（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.31888481711745.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.63381893211847.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.11316434710944.8115.06789864811043.6113.84073924911844111.90444355010147.2111.14266615110840.4113.88559975210643.2108.73135984 4、二二次次平平滑滑指指数数法法二次平滑指数法是基于某两个波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次实际产量（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.880845021410631.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.557932412911735.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.180.756034884512136.378.843803794611233.681.147985574710932.780.54115455481103378.977434284911835.477.855537995010130.379.319546025110832.475.740006125210631.874.687527465 5、线线性性回回归归预预测测法法线性回归预测假设数据与月份或周次等存在线性关系，并试图获得这种关系的数学函数，根据开发式算法得到以下的运算方式，从而得到最接近这种关系的函数。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。周次(x)实际产量（y）预测值x*y1100114.05878081002111113.97502772223120113.89127473604123113.80752164925112113.72376855606122113.64001547327123113.55626238618124113.47250929929110113.388756199010119113.305003119011118113.2212499129812101113.1374968121213108113.0537437140414106112.9699906148415106112.8862375159016114112.8024844182417117112.7187313198918107112.6349782192619121112.5512251229920112112.467472224021109112.3837189228922110112.2999658242023118112.2162128271424101112.1324597242425108112.0487066270026106111.9649535275627106111.8812004286228114111.7974473319229117111.7136942339330110111.6299411330031119111.546188368932118111.4624349377633101111.3786818333334108111.2949287367235106111.2111756371036106111.1274225381637114111.0436694421838117110.9599163444639107110.8761632417340121110.7924101484041112110.708657459242109110.624904457843117110.5411509503144107110.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.3333333318.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.428571432322.3333333319.2857142922.422.3333333318.7142857121.421.518.2857142919.820.8333333317.8571428619.820.666666671820.820.8333333318 定定量量需需求求预预测测模模型型下面给出某一企业某月的销售情况，并以不同周期进行了简单平均预测。该模型可由于生产、销售等环节的预测，只要在“实际销量”一栏输入数，就能自动生成各周期预测数值。可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。加权平均法是给每一个参与预测的数据赋以权重，一般较靠近预测期的实际值所占的权重较大。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“权重指数”栏中的权重值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值和均方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。预测值（SF）预测值（SF）预测周期（n）预测周期n=5预测周期n=63456110.3实际值方差110.3109.6166667预测值方差n=3110.96110.7833333预测值方差n=4111.32111.7666667预测值方差n=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.5166667107.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.98112.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.1703859367.0622315666.6373389468.3024033665.2214420265.0528652164.4717191364.1230314865.8338188967.5802913366.948174868.72890488一次平滑指数法是基于某一波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。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.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.7469544-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.939281224-1.45720016106.63230781.194438604-0.8743200960.320118508106.96669840.133756250.1920711050.325827355111.4047681.7752278520.1954964131.970724265112.96284460.6232306311.1824345591.80566519二次平滑指数法是基于某两个波动值（平滑指数）而进行的预测，一般情况下“平滑指数”值越低，所预测结果就越稳定。以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。113.03795690.0300449011.0833991141.113444015112.9059806-0.0527904980.6680664090.615275911114.86487960.7835595820.3691655461.152725128111.5123233-1.3410225120.691635077-0.649387435110.0040551-0.603307276-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.567209752116.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.3127909110.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.915107562-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.443538325106.4875275-0.660991466-0.866122995-1.527114461(x*y)x*yX2X2153249801996014823049162536496481线性回归预测假设数据与月份或周次等存在线性关系，并试图获得这种关系的数学函数，根据开发式算法得到以下的运算方式，从而得到最接近这种关系的函数。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。100121144169196225256289324361400441484529576625676729784841900961102410891156122512961369144415211600168117641849193620252116220923042401250026012704实际值方差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下面给出某一企业某月的销售情况，并以不同周期进行了简单平均预测。该模型可由于生产、销售等环节的预测，只要在“实际销量”一栏输入数，就能自动生成各周期预测数值。可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。可根据后面算出的方差值和均方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。权重指数a1a2a3a4a50.81.11.20.80.91.21.40.60.70.81.31.60.60.70.81.11.327 实际值均方差5.1961524237.507888322 预测值均方差n=32.7400526136.881495734 预测值均方差n=42.6232605165.40053802 预测值均方差n=52.3239057684.49844015 预测值均方差n=62.120952652实际值均方差6.582347617预测值均方差3.538632701以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。最终预测值平滑指数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以下模型只需改变“实际产量”一栏中的数值和根据实际改变“平滑指数”栏中的值，或改变“首次假设预测值”中的值，就能自动得到预测结果。该模型可用于企业生产、销售等环节预测。可根据后面算出的方差值来判断预测值与实际值的接近程度，可根据后面算出的方差值来判断预测值与实际值的接近程度，预测值均方差与实际值均方差差异越小，预测值越可靠。114.1514009113.5212565116.0176047110.8629358109.0111154106.7535072105.0827606107.3833067111.0476922111.3870389115.238137117.9653292112.7385243110.6111379107.9686309105.8826402107.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