Forecasting met Excel

Maak eenvoudig prognoses in Excel en voorspel toekomstige waarden.

De toekomstvoorspelling wordt des te betrouwbaarder als je daarbij rekening kunt houden met periodieke factoren.

Functie VOORSPELLEN.LINEAR

Met de nieuwe statistische functies laten toekomstige waarden zich gemakkelijk voorspellen op basis van bekende historische gemeten waarden. Neem als voorbeeld de getallen in onderstaande datum-omzet-tabel in het bereik B2:C12. In tien opeenvolgende kwartalen zijn omzetcijfers bekend; gewenst is een voorspelling voor de drie erop volgende kwartalen.

De functie VOORSPELLEN.LINEAR( B15 ; C3:C12 ; B3:B12 ) berekent aan de hand van een reeks bestaande waarden, een toekomstige waarde.

Je kunt met deze functie bijvoorbeeld toekomstige omzetcijfers, voorraadbehoeften of consumptiepatronen voorspellen. De voorspelling gaat met lineaire regressie.

De achtergrond van de berekenwijze is wat in de wiskunde bekend staat als de ‘methode der kleinste kwadraten’. Overigens: De functie kan behalve voor extrapolatie (voorspellen van een toekomstige waarde) evengoed toegepast worden voor interpolatie (bijvoorbeeld om ontbrekende waarden aan te vullen binnen een datumreeks).

In een grafiek kun je de voorspelling zichtbaar maken via een lineaire trendlijn. Deze is in de afbeelding te zien als een gele streepjes lijn in de grafiek.

Niet helemaal nieuw…

De voorspellingsfuncties in Excel zijn niet helemaal nieuw. In voorgaande versies bestaat reeds de functie VOORSPELLEN, welke overeenkomt met de ‘nieuwe’ functie VOORSPELLEN.LINEAR. Omwille van de compatibiliteit is VOORSPELLEN( ) in versie 2016 gehandhaafd, maar eigenlijk dus vervangen door VOORSPELLEN.LINEAR( ).

Gegevensanalyse met een Voorspellingblad

Wel nieuw is de functie VOORSPELLEN.ETS( ) met daarbij nog een aantal verwante functies. Zonder nóg meer in detail te treden komt het erop neer dat deze functies gebruik maken van geavanceerdere wiskundige algoritmen om rekening te kunnen houden met periodieke seizoensinvloeden. Het prettige daarbij is dat deze functies in Excel2016 automatisch gegenereerd worden wanneer je besluit om je gegevensanalyse uit te voeren met gebruikmaking van een zogenoemd ‘Voorspellingblad’.

Voorbeeldgegevens

Bij de eerder gebruikte gegevens zou het kunnen gaan om de omzetcijfers van bijvoorbeeld rosé-wijn, waarvan de verkoop in de zomermaanden hoger zal liggen dan in de winter.

Een caféhouder wil op basis van de bekende gegevens (genoteerd in het bereik B2:C12 ) een betrouwbare voorspelling voor drie erop volgende kwartalen.
In Excel2016 kun je daartoe nu een prognosewerkblad maken.

Prognosewerkblad maken

• Selecteer het bereik B2:C12
• Ga in het lint van Excel2016 naar de tab ‘Gegevens’ en klik op ‘Voorspellingblad’.
• In het dialoogvenster ‘Prognosewerkblad maken’ stel je de nodige parameters in.
  Zo kun je de eigenschap ‘Seizoensgebonden’ handmatig instellen op de waarde ‘4’, als je een jaarlijkse cyclus verwacht en meetwaarden hebt ingevoerd per kwartaal. Geef ook de begin- en einddatum van de prognose aan.
• Klik op de knop ‘Maken’.

Voorspellingblad
Een nieuw werkblad met prognoses worden toegevoegd, met aanduiding van een betrouwbaarheidsinterval van bovengrens en ondergrens, en een grafische weergave van de voorspellingen.

De omzet voor het laatste kwartaal wordt hier berekend met VOORSPELLEN.ETS( ), resulterend in een voorspelling van € 2682,- en bijbehorende marge van om en nabij € 196,- zoals weergegeven door de bovengrens en de ondergrens.

Betrouwbaarheidsmarge

Het blijft een voorspelling, er is geen 100% betrouwbare prognose te maken. Dat wil zeggen dat de wiskundige algoritmen rekening moeten houden met de mogelijkheid dat de voorspelling ernaast zit. Door de parameter ‘Betrouwbaarheidsinterval’ in te stellen op 95%, zorg je dat het rekenmodel slechts 5% kans overlaat dat de werkelijke toekomstige waarde buiten de marge zal liggen.