Flyttande medelvärde I det här exemplet lär du dig hur du beräknar glidande medelvärdet för en tidsreaktor i Excel. Ett glidande medel används för att jämna ut oegentligheter (toppar och dalar) för att enkelt kunna känna igen trender. 1. Låt oss först titta på våra tidsserier. 2. Klicka på Dataanalys på fliken Data. Obs! Det går inte att hitta knappen Data Analysis Klicka här för att ladda verktyget Analysis ToolPak. 3. Välj Flytta medelvärde och klicka på OK. 4. Klicka i rutan Inmatningsområde och välj intervallet B2: M2. 5. Klicka i rutan Intervall och skriv 6. 6. Klicka i rutan Utmatningsområde och välj cell B3. 8. Skriv en graf över dessa värden. Förklaring: Eftersom vi ställer intervallet till 6 är det rörliga genomsnittet genomsnittet för de föregående 5 datapunkterna och den aktuella datapunkten. Som ett resultat utjämnas toppar och dalar. Diagrammet visar en ökande trend. Excel kan inte beräkna det rörliga genomsnittet för de första 5 datapunkterna eftersom det inte finns tillräckligt med tidigare datapunkter. 9. Upprepa steg 2 till 8 för intervall 2 och intervall 4. Slutsats: Ju större intervall desto mer topparna och dalarna utjämnas. Ju mindre intervallet desto närmare de rörliga medelvärdena ligger till de faktiska datapunkterna. Möjlig medelprognos införande. Som du kan gissa vi tittar på några av de mest primitiva tillvägagångssätten för prognoser. Men förhoppningsvis är dessa åtminstone en värdefull introduktion till några av de datorproblem som är relaterade till att implementera prognoser i kalkylblad. I den här venen fortsätter vi med att börja i början och börja arbeta med Moving Average prognoser. Flyttande medelprognoser. Alla är bekanta med att flytta genomsnittliga prognoser oavsett om de tror att de är. Alla studenter gör dem hela tiden. Tänk på dina testresultat i en kurs där du ska ha fyra tester under semestern. Låt oss anta att du fick en 85 på ditt första test. Vad skulle du förutse för ditt andra testresultat Vad tycker du att din lärare skulle förutsäga för nästa testresultat Vad tycker du att dina vänner kan förutsäga för nästa testresultat Vad tror du att dina föräldrar kan förutsäga för nästa testresultat Oavsett om alla blabbing du kan göra för dina vänner och föräldrar, de och din lärare är mycket troliga att vänta på att du får något i området 85 du bara fick. Nåväl, nu kan vi anta att trots din egen marknadsföring till dina vänner överskattar du dig själv och räknar att du kan studera mindre för det andra testet och så får du en 73. Nu är vad alla berörda och oroade kommer att Förutse att du kommer att få ditt tredje test Det finns två väldigt troliga metoder för att utveckla en uppskattning oavsett om de kommer att dela den med dig. De kan säga till sig själva: "Den här killen sprider alltid rök om hans smarts. Hes kommer att få ytterligare 73 om han är lycklig. Kanske kommer föräldrarna att försöka vara mer stödjande och säga, Quote, hittills har du fått en 85 och en 73, så kanske du ska räkna med att få en (85 73) 2 79. Jag vet inte, kanske om du gjorde mindre fester och werent vaggar vassan överallt och om du började göra mycket mer studerar kan du få en högre poäng. quot Båda dessa uppskattningar flyttade faktiskt genomsnittliga prognoser. Den första använder endast din senaste poäng för att förutse din framtida prestanda. Detta kallas en rörlig genomsnittlig prognos med en period av data. Den andra är också en rörlig genomsnittlig prognos men använder två dataperioder. Låt oss anta att alla dessa människor bråkar på ditt stora sinne, har gett dig en puss och du bestämmer dig för att göra det bra på det tredje testet av dina egna skäl och att lägga ett högre poäng framför din quotalliesquot. Du tar testet och din poäng är faktiskt en 89 Alla, inklusive dig själv, är imponerade. Så nu har du det sista testet av terminen som kommer upp och som vanligt känner du behovet av att ge alla till att göra sina förutsägelser om hur du ska göra på det sista testet. Jo, förhoppningsvis ser du mönstret. Nu kan du förhoppningsvis se mönstret. Vilken tror du är den mest exakta visselpipan medan vi arbetar. Nu återvänder vi till vårt nya rengöringsföretag som startas av din främmande halvsyster, kallad Whistle While We Work. Du har några tidigare försäljningsdata som representeras av följande avsnitt från ett kalkylblad. Vi presenterar först data för en treårs glidande medelprognos. Posten för cell C6 ska vara Nu kan du kopiera den här cellformeln ner till de andra cellerna C7 till och med C11. Lägg märke till hur genomsnittet rör sig över de senaste historiska data men använder exakt de tre senaste perioderna som finns tillgängliga för varje förutsägelse. Du bör också märka att vi inte verkligen behöver göra förutsägelser för de senaste perioderna för att utveckla vår senaste förutsägelse. Detta är definitivt annorlunda än exponentiell utjämningsmodell. Ive inkluderade quotpast predictionsquot eftersom vi kommer att använda dem på nästa webbsida för att mäta förutsägelse validitet. Nu vill jag presentera de analoga resultaten för en tvåårs glidande medelprognos. Posten för cell C5 ska vara Nu kan du kopiera den här cellformeln ner till de andra cellerna C6 till och med C11. Lägg märke till hur nu endast de två senaste bitarna av historiska data används för varje förutsägelse. Återigen har jag inkluderat quotpast predictionsquot för illustrativa ändamål och för senare användning i prognosvalidering. Några andra saker som är viktiga att märka. För en m-period som rör genomsnittlig prognos används endast de senaste datavärdena för att göra förutsägelsen. Inget annat är nödvändigt. För en m-period rörande genomsnittlig prognos, när du gör quotpast predictionsquot, märka att den första förutsägelsen sker i period m 1. Båda dessa problem kommer att vara väldigt signifikanta när vi utvecklar vår kod. Utveckla den rörliga genomsnittsfunktionen. Nu behöver vi utveckla koden för den glidande medelprognosen som kan användas mer flexibelt. Koden följer. Observera att inmatningarna är för antalet perioder du vill använda i prognosen och en rad historiska värden. Du kan lagra den i vilken arbetsbok du vill ha. Funktion MovingAverage (Historical, NumberOfPeriods) Som enstaka deklarering och initialisering av variabler Dim-objekt som variant Dim-teller som integer Dim-ackumulering som enstaka Dim HistoricalSize som heltal Initialiserande variabler Counter 1 ackumulering 0 Bestämning av storleken på Historisk matris Historisk storlek Historisk. Count för Counter 1 till NumberOfPeriods Ackumulera lämpligt antal senast tidigare observerade värden ackumulering ackumulering historisk (historicalSize - numberOfPeriods Counter) MovingAverage Accumulation NumberOfPeriods Koden förklaras i klassen. Du vill placera funktionen i kalkylbladet så att resultatet av beräkningen visas där den ska gilla följande. Skapa en enkel flyttning Detta är en av följande tre artiklar om tidsserieanalys i Excel Översikt över rörelsegraden Det rörliga genomsnittet är en statistisk teknik som används för att släta ut kortsiktiga fluktuationer i en serie data för att lättare kunna identifiera långsiktiga trender eller cykler. Det rörliga genomsnittet kallas ibland som ett rullande medelvärde eller ett löpande medelvärde. Ett rörligt medelvärde är en serie siffror, som var och en representerar medelvärdet av ett intervall av specificerat antal tidigare perioder. Ju större intervallet desto mer utjämning uppstår. Ju mindre intervallet desto mer är det glidande medlet liknar den faktiska dataserien. Flytta medelvärden utföra följande tre funktioner: Utjämning av data, vilket innebär att dataens passform anpassas till en rad. Minskar effekten av tillfällig variation och slumpmässigt brus. Markera outliers över eller under trenden. Det rörliga genomsnittet är en av de mest använda statistiska teknikerna inom industrin för att identifiera datatrender. Till exempel ser försäljningscheferna vanligtvis tre månaders glidande medelvärden av försäljningsdata. Artikeln kommer att jämföra två månaders, tre månaders och sex månaders enkla glidande medelvärden av samma försäljningsdata. Det rörliga genomsnittet används ganska ofta i teknisk analys av finansiella data som aktieavkastning och ekonomi för att lokalisera trender i makroekonomiska tidsserier såsom anställning. Det finns ett antal variationer av det rörliga genomsnittet. De vanligaste anställda är det enkla glidande medlet, det vägda glidande medlet och det exponentiella glidande medlet. Att utföra varje av dessa tekniker i Excel kommer att beskrivas i detalj i separata artiklar i den här bloggen. Här är en kort översikt över var och en av dessa tre tekniker. Enkelt rörligt medelvärde Varje punkt i ett enkelt glidande medelvärde är medelvärdet av ett angivet antal tidigare perioder. Denna bloggartikel kommer att ge en detaljerad förklaring av genomförandet av denna teknik i Excel. Viktad Flytta Genomsnittlig Poäng i det viktade glidande medlet representerar också ett genomsnitt av ett angivet antal tidigare perioder. Det vägda glidande medlet applicerar olika viktning till vissa tidigare perioder, ganska ofta får de senaste perioderna större vikt. En länk till en annan artikel i den här bloggen, som ger en detaljerad förklaring av genomförandet av denna teknik i Excel, är följande: Exponentiella rörliga medelpunkter i exponentiell glidande medelvärde representerar också ett genomsnitt av ett visst antal tidigare perioder. Exponentiell utjämning gäller viktningsfaktorer till tidigare perioder som minskar exponentiellt och når aldrig noll. Som ett resultat tar exponentiell utjämning hänsyn till alla tidigare perioder istället för ett angivet antal tidigare perioder som det vägda glidande medlet gör. En länk till en annan artikel i den här bloggen som ger en detaljerad förklaring av genomförandet av denna teknik i Excel är följande: Nedan beskrivs 3-stegs processen för att skapa ett enkelt glidande medelvärde av tidsseriedata i Excel Steg 1 8211-graf Den ursprungliga data i en tidsserie-plot Linjediagrammet är det vanligaste Excel-diagrammet för att gradera tidsseriedata. Ett exempel på ett sådant Excel-diagram som används för att plotta 13 perioder med försäljningsdata visas på följande sätt: Steg 2 8211 Skapa det rörliga genomsnittet i Excel Excel tillhandahåller verktyget Flyttande medel i menyn Dataanalys. Verktyget Moving Average skapar ett enkelt glidande medelvärde från en dataserie. Dialogrutan Flyttande medel ska fyllas i enligt följande för att skapa ett glidande medelvärde för de föregående 2 dataperioderna för varje datapunkt. Utgången för 2-års glidande medelvärde visas enligt följande tillsammans med formlerna som användes för att beräkna värdet för varje punkt i glidande medelvärdet. Steg 3 8211 Lägg till den rörliga genomsnittsserien i diagrammet. Dessa data ska nu läggas till i diagrammet som innehåller den ursprungliga tidslinjen för försäljningsdata. Uppgifterna kommer helt enkelt att läggas till som en ytterligare dataserie i diagrammet. För att göra det, högerklicka var som helst på diagrammet och en meny kommer dyka upp. Hit Välj data för att lägga till den nya serien av data. Den glidande genomsnittsserien kommer att läggas till genom att fylla i dialogrutan Redigera serier enligt följande: Diagrammet som innehåller den ursprungliga dataserien och den data8217s 2-intervallet enkelt glidande medelvärde visas som följer. Observera att den glidande medellinjen är ganska lite jämnare och råa data8217s avvikelser över och under trendlinjen är mycket tydligare. Den övergripande trenden är nu också mycket tydligare. Ett 3-intervall glidande medelvärde kan skapas och placeras på diagrammet med samma procedur som följer: Det är intressant att notera att 2-intervallet enkelt glidande medelvärde skapar en jämnare graf än det 3-intervallet enkelt glidande medelvärdet. I detta fall kan 2-intervallet enkelt glidande medelvärde vara det mer önskvärt än 3-intervallet glidande medelvärdet. Som jämförelse beräknas ett 6-intervall enkelt glidande medelvärde och läggas till i diagrammet på samma sätt som följer: Som förväntat är 6-intervallet enkelt glidande medelvärde signifikant jämnare än 2 eller 3-intervallet enkla glidande medelvärden. En mjukare graf passar bättre en rak linje. Analys av prognosnoggrannhet Noggrannhet kan beskrivas som godhet med passform. De två komponenterna av prognosticitetsnoggrannheten är följande: Prognos Bias 8211 Tendensen av en prognos att vara konsekvent högre eller lägre än de verkliga värdena för en tidsserie. Prognosförskjutning är summan av allt fel dividerat med antalet perioder enligt följande: En positiv bias indikerar en tendens att underskatta. En negativ bias indikerar en tendens att överskatta. Bias mäter inte noggrannhet eftersom positivt och negativt fel avbryter varandra. Prognosfel 8211 Skillnaden mellan de faktiska värdena för en tidsserie och de prognostiserade värdena för prognosen. De vanligaste åtgärderna för prognosfel är följande: MAD 8211 Mean Absolute Deviation MAD beräknar det genomsnittliga absoluta värdet av felet och beräknas med följande formel: Medelvärdet av felets absoluta värden eliminerar avbrytande effekten av positiva och negativa fel. Ju mindre MAD, desto bättre är modellen. MSE 8211 Mean Squared Error MSE är ett populärt mått på fel som eliminerar avbrytande effekten av positiva och negativa fel genom att summera kvadraterna för felet med följande formel: Stora felvillkor tenderar att överdriva MSE eftersom felvillkoren är alla kvadrerade. RMSE (Root Square Mean) reducerar detta problem genom att ta kvadratroten av MSE. MAPE 8211 Mean Absolute Percent Error MAPE eliminerar också avbrytande effekten av positiva och negativa fel genom att summera de absoluta värdena för felvillkoren. MAPE beräknar summan av procentuella felvillkor med följande formel: Genom att summera procentfelter kan MAPE användas för att jämföra prognosmodeller som använder olika måttmått. Beräkna Bias, MAD, MSE, RMSE och MAPE i Excel För enkla rörliga medelvärdena beräknas MAD, MSE, RMSE och MAPE i Excel för att utvärdera 2-intervallet, 3-intervallet och 6-intervallet enkelt rörelse genomsnittlig prognos erhållen i denna artikel och visas som följer: Det första steget är att beräkna E t. E t 2. E t, E t Y t-act. och summera dem enligt följande: Bias, MAD, MSE, MAPE och RMSE kan beräknas enligt följande: Samma beräkningar görs nu för att beräkna Bias, MAD, MSE, MAPE och RMSE för det 3-intervalla enkelt glidande medlet. Samma beräkningar görs nu för att beräkna Bias, MAD, MSE, MAPE och RMSE för 6-intervallet enkelt glidande medelvärde. Bias, MAD, MSE, MAPE och RMSE sammanfattas för 2-intervall, 3-intervall och 6-intervall enkla glidande medelvärden enligt följande. 3-intervallet enkelt glidande medelvärde är den modell som passar bäst den aktuella data. 160 Excel Master Series Blog Directory Statistiska ämnen och artiklar i varje TopicExcel Försäljnings prognos för Dummies Cheat Sheet När du börjar lära prognoser, är it8217s ofta en bra idé att luta sig på Excel-verktygen i additionsdatan Data Analysis. Men deras räckvidd är ganska begränsad och för länge kommer du sannolikt att hitta dig själv att utnyttja Excel8217s arbetsbladsfunktioner direkt. När du hittar dig själv med all inferensiell statistik som följer med LINEST-funktionen, vet du att it8217s är dags att lägga ut din baslinje för en formell prognos. 6 Inmatningsverktyg för Excel-data Analysverktyget Data Analysis-tillägget, tidigare kallat Analysis ToolPak, matar in formulär på dina uppdrag så att du kan koncentrera dig på what8217s pågår med dina data. Den har tre olika verktyg som är direkt användbara vid prognoser rörande medelvärde, exponentiell utjämning och regression samt flera andra som kan vara till hjälp. Här är en lista över några av de verktyg som ingår i tillägget Data Analysis. Det finns faktiskt tre olika ANOVA-verktyg. Ingen är särskilt användbar för prognoser, men alla verktyg kan hjälpa dig att förstå dataset som ligger till grund för din prognos. ANOVA-verktygen hjälper dig att skilja mellan exempel, till exempel, gör människor som bor i Tennessee som ett visst bilmärke bättre än dem som bor i Vermont. Det här verktyget är viktigt, oavsett vilken metod du använder för att skapa en prognos. Om du har mer än en variabel kan det berätta hur starkt de två variablerna är relaterade (plus eller minus 1,0 är stark, 0,0 betyder inget förhållande). Om du bara har en variabel kan det berätta hur stark en tidsperiod är relaterad till en annan. Använd verktyget Beskrivande statistik för att ta hand om saker som genomsnittet och standardavvikelsen för dina data. Att förstå denna grundläggande statistik är viktig så att du vet vad som händer med dina prognoser. Detta verktygsnamn låter obehagligt och skrämmande, vilket verktyget inte är. När du bara har en variabel, t. ex. försäljning eller omsättning, ser du till ett tidigare faktiskt värde för att förutsäga nästa (kanske föregående månad eller samma månad året innan). Allt detta verktyg gör är att justera nästa prognos genom att använda felet i föregående prognos. Ett glidande medelvärde visar genomsnittet av resultat över tiden. Den första kan vara genomsnittet för januari, februari och mars det andra skulle då vara genomsnittet för februari, mars och april och så vidare. Denna prognosmetod tenderar att fokusera på signalen (vad som verkligen händer i baslinjen) och för att minimera bruset (slumpmässiga fluktuationer i baslinjen). Regression är nära relaterad till korrelation. Använd det här verktyget för att förutse en variabel (t. ex. försäljning) från en annan (t. ex. datum eller annonsering). Det ger dig ett par siffror att använda i en ekvation, som försäljning 50000 (10 datum). 4 Excel-prognosfunktioner Excel har många bra verktyg för försäljningsprognoser. Att veta följande funktioner är till hjälp för att få dina uppgifter i ordning. Kolla in följande praktiska prognosfunktioner. Arbetsbladets version av korrelationsverktyget för dataanalys. Skillnaden är att CORREL omberäknar när ingångsdata ändras, och korrelationsverktyget gör det inte. Exempel: CORREL (A1: A50, B1: B50). CORREL ger dig bara en korrelation, men korrelationsverktyget kan ge dig en hel matris av korrelationer. Du kan använda den här funktionen istället för regressionsverktyget för dataanalys. (Funktionsnamnet är en förkortning av linjär uppskattning.) För enkel regression, välj ett intervall av två kolumner och fem rader. Du behöver matris-skriv in den här funktionen. Skriv till exempel LINEST (A1: A50, B1: B50,, TRUE) och tryck sedan på CtrlShiftEnter. Denna funktion är praktisk eftersom den ger dig prognosvärden direkt, medan LINEST ger dig en ekvation som du måste använda för att få prognosen. Använd till exempel TREND (A1: A50, B1: B50, B51) där du förutspår ett nytt värde på grundval av vad som är i B51. FORECAST-funktionen liknar TREND-funktionen. Syntaxen är lite annorlunda. Använd till exempel FORECAST (B51, A1: A50, B1: B50) där du förutser ett nytt värde baserat på värdet i B51. Dessutom hanterar FORECAST endast en prediktor, men TREND kan hantera flera prediktorer. Vad du kommer ut ur Excel LINEST Funktion för försäljning prognos Excel8217s LINEST funktion är ett praktiskt verktyg för försäljning prognoser. Att veta vad du kan göra med det kommer att göra dina prognoser ansträngningar enkelt arbete. Här är en snabb sammanfattning på Excel8217s LINEST-funktion, rad för rad:
No comments:
Post a Comment