Voortschrijdend gemiddelde betekent dat we het gemiddelde berekenen van de gemiddelden van de gegevensset die we hebben, in Excel hebben we een ingebouwde functie voor de berekening van het voortschrijdend gemiddelde die beschikbaar is op het tabblad Gegevensanalyse in de analyse-sectie, er is een invoerbereik en uitvoer voor nodig bereik met intervallen als uitvoer, berekeningen op basis van louter formules in Excel om het voortschrijdend gemiddelde te berekenen is moeilijk, maar we hebben een ingebouwde functie in Excel om dit te doen.
Wat is het voortschrijdend gemiddelde in Excel
Voortschrijdend gemiddelde is een veelgebruikte techniek in tijdreeksanalyse die wordt gebruikt om de toekomst te voorspellen. De voortschrijdende gemiddelden in een tijdreeks worden in feite geconstrueerd door gemiddelden te nemen van verschillende opeenvolgende waarden van gegevens uit een andere tijdreeks.
Er zijn drie soorten voortschrijdende gemiddelden, namelijk eenvoudig voortschrijdend gemiddelde, gewogen voortschrijdend gemiddelde en exponentieel voortschrijdend gemiddelde in Excel.
# 1 - Eenvoudig voortschrijdend gemiddelde in Excel
Een eenvoudig voortschrijdend gemiddelde helpt bij het berekenen van het gemiddelde van het laatste aantal perioden van een gegevensreeks. Stel dat er prijzen van n periode worden gegeven. Vervolgens wordt het eenvoudig voortschrijdend gemiddelde gegeven als
Eenvoudig voortschrijdend gemiddelde = [P1 + P2 + …………. + Pn] / n
# 2 - Gewogen voortschrijdend gemiddelde in Excel
Het gewogen voortschrijdend gemiddelde geeft het gewogen gemiddelde van de afgelopen n perioden weer. De weging neemt af met elk gegevenspunt van de vorige tijdsperiode.
Gewogen voortschrijdend gemiddelde = (prijs * wegingsfactor) + (prijs vorige periode * wegingsfactor-1)
# 3 - Exponentieel voortschrijdend gemiddelde in Excel
Het is vergelijkbaar met een eenvoudig voortschrijdend gemiddelde dat trends over een bepaalde periode meet. Terwijl een eenvoudig voortschrijdend gemiddelde een gemiddelde van bepaalde gegevens berekent, hecht exponentieel voortschrijdend gemiddelde meer gewicht aan de huidige gegevens.
Exponentieel voortschrijdend gemiddelde = (K x (C - P)) + P
Waar,
- K = exponentiële afvlakconstante
- C = huidige prijs
- P = exponentieel voortschrijdend gemiddelde van vorige perioden (eenvoudig voortschrijdend gemiddelde gebruikt voor berekening van eerste perioden)
Hoe het voortschrijdend gemiddelde in Excel te berekenen?
Hieronder ziet u het voorbeeld van een voortschrijdend gemiddelde in Excel.
U kunt deze voortschrijdend gemiddelde Excel-sjabloon hier downloaden - Voortschrijdend gemiddelde Excel-sjabloonVoorbeeld # 1 - Eenvoudig voortschrijdend gemiddelde in Excel
Voor het berekenen van het eenvoudige voortschrijdend gemiddelde hebben we de verkoopgegevens van een bedrijf van januari tot december genomen voor het jaar 2018. Ons doel is om de gegevens glad te strijken en het verkoopcijfer in januari 2019 te kennen. We zullen hier het voortschrijdend gemiddelde van drie maanden gebruiken.
Stap 1: Het voortschrijdend gemiddelde van januari, februari en maart wordt berekend door de som van de verkoopcijfers van de maanden te nemen en deze vervolgens te delen door 3.
Stap 2: Als u in de hoek van de D5-cel selecteert en vervolgens sleept en neerzet, krijgt u het voortschrijdend gemiddelde voor de resterende perioden. Dit is de vultoolfunctie van Excel.
De verkoopvoorspelling voor januari 2019 is 10456,66667.
Stap 3: Nu plotten we het verkoopcijfer en het voortschrijdend gemiddelde in de lijngrafiek om het verschil in trend te begrijpen. Dit kunt u doen vanaf het tabblad Invoegen. Eerst hebben we de gegevensreeksen geselecteerd en vervolgens uit de sectie Grafieken onder invoegen de lijngrafiek.
Na het maken van de grafieken is te zien dat de grafiek met het voortschrijdend gemiddelde veel vloeiender is dan de oorspronkelijke gegevensreeks.
Voorbeeld # 2 - Eenvoudig voortschrijdend gemiddelde via het tabblad Gegevensanalyse in Excel
- Onder het tabblad Gegevens onder de groep Analyse moeten we op Gegevensanalyse klikken. Hieronder volgt de screenshot.
- Vanuit data-analyse kan het voortschrijdend gemiddelde worden opgevraagd.
- Nadat we op het voortschrijdend gemiddelde hebben geklikt, hebben we het verkoopcijfer als invoerbereik geselecteerd.
- Op de labels in de eerste rij wordt geklikt om Excel te laten begrijpen dat de eerste rij de labelnaam heeft.
- Interval 3 is geselecteerd omdat we een voortschrijdend gemiddelde van drie jaar willen.
- We hebben het outputbereik geselecteerd met aangrenzend aan het verkoopcijfer.
- We willen ook de grafiekuitvoer zien waarin we het verschil tussen de werkelijke en de voorspelde kunnen zien.
Deze grafiek toont het verschil tussen het werkelijke en het voorspelde voortschrijdend gemiddelde.
Voorbeeld # 3 - Gewogen voortschrijdend gemiddelde in Excel
We gebruiken het driejaars gewogen voortschrijdend gemiddelde en de formule staat in de schermafbeelding.
Nadat we de formule hadden gebruikt, kregen we het voortschrijdend gemiddelde voor een periode.
We hebben het voortschrijdend gemiddelde voor alle andere perioden verkregen door waarden in de volgende cellen te slepen en neer te zetten.
De prognose voor januari 2019 is 10718,33
Nu hebben we de lijngrafiek genomen om de afvlakking van gegevens te zien. Hiervoor hebben we onze maand de voorspelde gegevens geselecteerd en vervolgens een lijngrafiek ingevoegd.
Nu zullen we onze voorspelde gegevens vergelijken met onze werkelijke gegevens. In de onderstaande screenshots kunnen we gemakkelijk het verschil zien tussen de werkelijke gegevens en de voorspelde gegevens. De grafiek bovenaan is de werkelijke gegevens en de grafiek hieronder is het voortschrijdend gemiddelde en de voorspelde gegevens. We kunnen zien dat de grafiek met het voortschrijdend gemiddelde aanzienlijk is afgevlakt in vergelijking met de grafiek die de feitelijke gegevens bevat.
Voorbeeld # 4 - Exponentieel voortschrijdend gemiddelde in Excel
De formule voor exponentieel voortschrijdend gemiddelde is S t = α.Y t-1 + (1- α) S t-1 …… (1)
Waar,
- Y t-1 = werkelijke waarneming in de t-1e periode
- S t-1 = eenvoudig voortschrijdend gemiddelde in de t-1e periode
- α = afvlakkingsfactor en varieert tussen 0,1 en 0,3. Hoe groter de waarde van α, hoe dichter de grafiek bij de werkelijke waarden ligt en hoe lager de waarde van α, hoe vloeiender de grafiek zal zijn.
Eerst berekenen we het eenvoudige voortschrijdend gemiddelde zoals eerder weergegeven. Daarna passen we de formule toe die wordt gegeven in vergelijking (1). Voor het vaststellen van de α-waarde voor alle volgende waarden hebben we op F4 gedrukt.
We krijgen de waarden door te slepen en neer te zetten in de volgende cellen.
Nu willen we de vergelijking zien tussen de werkelijke waarden, het eenvoudig voortschrijdend gemiddelde en het exponentieel voortschrijdend gemiddelde in Excel. We hebben dit gedaan door een lijndiagram te maken.
Uit de bovenstaande schermafbeelding kunnen we het verschil zien tussen het werkelijke verkoopcijfer, het eenvoudig voortschrijdend gemiddelde en het exponentieel voortschrijdend gemiddelde in Excel.
Dingen om te onthouden over het voortschrijdend gemiddelde in Excel
- Het eenvoudig voortschrijdend gemiddelde kan worden berekend met behulp van een GEMIDDELDE functie in Excel
- Zwevend gemiddelde helpt bij het gladmaken van de gegevens
- Seizoensgemiddelden worden vaak een seizoensindex genoemd
- Het exponentieel voortschrijdend gemiddelde in Excel geeft meer gewicht aan de recente gegevens dan het eenvoudig voortschrijdend gemiddelde. Daarom is de afvlakking in het geval van het exponentieel voortschrijdend gemiddelde in Excel meer dan die van het eenvoudig voortschrijdend gemiddelde.
- In bedrijven zoals een aandelenmarkt helpt het voortschrijdend gemiddelde de handelaar om de trend gemakkelijker te identificeren.
