Exceltip: De toekomst voorspellen

Wist u dat het mogelijk is om de toekomst te voorspellen? U heeft er een speciale formule voor nodig, maar het kan ook magisch (automatisch). Klinkt als Harry Potter toch? Toch kan Excel u op basis van gedetecteerde trendlijnen vertellen en (binnen een in te stellen waarschijnlijkheidsmarge), voorspellen wat de toekomst zal zijn. Dat is handig als u toekomstige verkoopcijfers, voorraadbehoeften of consumptiepatronen wilt weten.

Exceltip: De toekomst voorspellen

In dit voorbeeld gaan we uit van een simpele dataset van een klein snoepwinkeltje op de hoek. Voor het gemak gaan we er vanuit dat het enige dat ze er verkopen zijn koekjes, ijsjes en snoepjes. De dataset ziet er zo uit:

Zoals u kunt zien is de verkoop van snoepjes redelijk stabiel en piekt de verkoop van ijsjes in de zomer, terwijl in de winter juist veel koekjes worden verkocht. We beginnen nu met de basis van voorspellen, met de functie die Excel hiervoor biedt. Deze vorm heet in de formule-vorm ‘VOORSPELLEN.ETS’. Hiermee berekent of voorspelt u een toekomstige waarde op basis van bestaande (historische) waarden door het algoritme ETS (Exponential Triple Smoothing) te gebruiken.
Allereerst willen we een tijdswaarde (x) en een hoeveelheidswaarde (y).
We selecteren daarom nu B1:M1 en B2:M2.

Vervolgens gaan we naar het lint Gegevens en kiezen we Voorspellingsblad.

Er opent nu een nieuw blad en een pop-upvenster. Klik links onder op Opties om meer mogelijkheden zichtbaar te maken, of klik op Maken. Het is onder opties onder meer mogelijk te begin- en einddatum van de voorspelling op te geven en de betrouwbaarheidswaarde in te stellen.

Voorspellen met formules

Excel heeft met de bovenstaande methode kant-en-klaar een lineaire voorspelling gemaakt voor de toekomst op basis van gegevens uit het verleden (lineaire regressie). Maar het heeft hiervoor een algoritme gebruikt. Het is ook mogelijk om dit zonder ETS te doen door middel van een formule. Daarvoor geldt de formule: VOORSPELLEN.LINEAR(x;y-bekend;x-bekend)

De syntaxis van de functie VOORSPELLEN.LINEAR heeft de volgende argumenten:

  • x Deze is vereist. Het is gegevenspunt waarvan u de waarde wilt voorspellen. In ons voorbeeld was dat de cel N1.
  • y-bekend Vereist. De afhankelijke matrix of het afhankelijke gegevensbereik. In ons voorbeeld $B$2: $B$13
  • x-bekend Vereist. De onafhankelijke matrix of het onafhankelijke gegevensbereik. In ons voorbeeld $A$2:$A$13

Naast de lineaire formule, zijn er nog 3 andere voorspel-varianten: VOORSPELLEN.ETS.SEASONALITY, VOORSPELLEN.ETS.CONFINT en VOORSPELLEN.ETS.STAT.
Elk van deze formules kan worden gebruikt na VOORSPELLEN.ETS om iets nader te bepalen. SEASONALITY GEEFT AAN welke automatische seizoensgebondenheid is vastgesteld en gebruikt in VOORSPELLEN.ETS. Deze formule kan ook los van VOORSPELLEN.ETS worden gebruikt, maar de functies zijn gerelateerd omdat de seizoensgebondenheid die met deze functie wordt vastgesteld, identiek is aan de seizoensgebondenheid die wordt gebruikt door VOORSPELLEN.ETS, met dezelfde invoerparameters voor de aanvulling van gegevens.

VOORSPELLEN.ETS.CONFINT geeft aan met welke betrouwbaarheidsinterval de voorspelde waarde op de opgegeven doeldatum is geretourneerd. Deze functie kan worden gebruikt om de nauwkeurigheid van het voorspelde model beter te begrijpen.
De laatste, VOORSPELLEN.ETS.STAT. geeft een statistische waarde die het resultaat is van de voorspelling van een tijdreeks.

Trendlijn

Er is trouwens nog een manier om voorspellingen zichtbaar te maken: de trendlijn. Daarvoor maken we eerst een simpele lijngrafiek door in het lint Invoegen te kiezen voor een Lineaire grafiek.

Voor deze grafiek is het bereik van de tabel iets aangepast. We hebben een aantal lege cellen onder de data 1 januari tot en met 1 april. De grafiek die we nu hebben ziet er zo uit:

Om nu de gegevens voor 1 januari tot en met 1 april te voorspellen, moeten we een Trendlijn toevoegen. Ga daarvoor in het lint Ontwerpen naar Grafiekelement toevoegen => Trendlijn => Lineaire prognose.

De prognose is een stuk conservatiever dan de voorspelling die op basis van de functie VOORSPELLEN werd gedaan. Het valt dus altijd aan te raden goed te overwegen welke vorm van voorspellen u kiest.