blog

Vraagvoorspelling in Excel: hoe doe je dat?

Supply chain

Vraagvoorspelling in Excel: hoe doe je dat?
Trendmodellen

Een voorspelling berekend met Microsoft Excel kan even betrouwbaar zijn als een voorspelling gegenereerd met een forecasting pakket. Dat stelt expert Seijer Troost van Troost Forecasting Consultancy. Maar welke methodes kunnen bij vraagvoorspelling met Excel worden gebruikt? En hoe wordt een goed model gekozen?

Seijer betoogt dat goed supply chain management staat of valt met een goede vraagvoorspelling. In zijn vorige expertartikel geeft hij dan ook zeven tips om de vraagvoorspelling te verbeteren. Een de van de belangrijkste tips volgens hem is het zorgen voor goede data. In dit artikel zal hij het gebruik van Excel bij de vraagvoorspelling verder toelichten.

 

Krachtig instrument

Het gebruik van Microsoft Excel om te voorspellen lijkt met de aanwezigheid van forecasting software achterhaald. Toch wordt Excel nog wijd en zijd gebruikt om prognoses te genereren. Niet ten onrechte, want Excel is een krachtig instrument om de benodigde statistische berekeningen uit te voeren. Wel zijn daarbij extra handelingen noodzakelijk; Excel is niet ingericht op het maken van voorspellingen. Investeren in een betrouwbare prognose is noodzakelijk, want een betrouwbare prognose draagt bij aan lagere voorraden en een efficiëntere orderafhandeling. De vraag is echter of om deze reden altijd gekozen moet worden voor een forecasting pakket. Een voorspelling berekend met Excel kan even betrouwbaar zijn als een voorspelling gegenereerd met een forecasting pakket. Voorwaarde is wel, dat u beschikt over de nodige kennis. Het verkrijgen van deze kennis is relatief goedkoop, vergeleken met de aanschaf van de meeste forecasting pakketten.

 

Wat is voorspellen

Voorspellen is het extrapoleren van tijdreeksen met behulp van statische technieken. Deze statistische technieken worden onderscheiden in trendmodellen en in smoothing technieken. Alle technieken maken gebruik van de historische data. Bij trendmodellen zijn alle data even belangrijk, maar smoothing technieken kennen aan de meest recente data meer gewicht toe dan aan data uit het verdere verleden.

 

Subjectieve methoden

Naast statistische technieken gebruikt men ook vaak meer subjectieve methoden om prognoses te maken. Bij deze subjectieve methoden worden orders, persoonlijke inschattingen en indicaties uit de markt meegenomen. Ook target setting is een subjectieve methode. Excel is hiervoor zeer geschikt, maar in dit artikel gaat het met name om de toepassing van statistische technieken.

 

Voor het voorspellen van reeksen met veel nullen, bijvoorbeeld bij het voorraadbeheer van reserve-onderdelen, zijn speciale technieken vereist, welke niet door Excel ondersteund worden. Ook de gespecialiseerde ARIMA modellen, ook wel Box-Jenkins genoemd, kunnen niet in Excel berekend worden.

 

Het berekenen van trendmodellen

Trendmodellen kunt u uitstekend met Excel berekenen. Deze modellen zijn zeer geschikt als er sprake is van een trend, zoals een voortdurende groei. Kijk hiernaast voor voorbeelden van trendmodellen (klik op de illustratie voor een grotere weergave).

 

Excel kent de functies TREND en GROWTH. Alle modellen, met uitzondering van het Polynoom, kunnen met deze twee functies berekend worden. U start met twee reeksen, de tijd en de te voorspellen reeks. De tijd moet daarbij voorgesteld worden als een getal; bijvoorbeeld jan-03, feb-03, mrt-03 … moet worden 1, 2, 3,… Afhankelijk van het gekozen model, moet u vaak nog een transformatie, bijvoorbeeld een logaritme, op de tijd en/of op de te voorspellen reeks toepassen. TREND en GROWTH komen dan automatisch met een voorspelling.

 

Het Polynoom trendmodel kunt u berekenen met behulp van de matrix functies in Excel. In de praktijk zult u dit trendmodel weinig gebruiken, omdat de voorspellingen voor met name de langere termijn vaak erg onrealistisch zijn. In de grafiek hiernaast ziet u een aantal voorbeelden van trendmodellen.

 

Deze grafiek kunt u gebruiken om een model te kiezen. Want uiteindelijk bepaalt u als gebruiker in welke voorspelling u gelooft.

 

Berekenen van smoothing technieken

Excel bevat enkele functies om eenvoudige smoothing technieken te berekenen. Deze eenvoudige technieken zijn geschikt, mits er geen sprake is van een trend. Excel is echter niet geschikt om de complexere smoothing technieken, welke bij trend toegepast moeten worden, te berekenen. De grafiek hiernaast toont voorbeelden van smoothing technieken.

 

Moving Average en Exponential Smoothing zijn functies in Excel, welke gekozen kunnen worden door op Tools en vervolgens op Data Analysis te klikken. De eerste twee technieken kunnen hiermee berekend worden. Holt Exponential Smoothing moet worden gebruikt, indien er sprake is van een trend. Winters Exponential Smoothing is noodzakelijk, indien er naast een trend ook sprake is van seizoensinvloeden. Het is heel lastig om deze technieken met Excel uit te voeren. Maar de trendmodellen zijn dan een goed alternatief. Smoothing technieken, welke met Excel uitgevoerd zijn, geven het resultaat getoond in deze grafiek.

 

U ziet dat de keuze van een verkeerde smoothing techniek kan leiden tot een zeer onrealistische prognose.

 

Rekening houden met seizoenspatronen

Met Excel kan een uitstekende prognose gemaakt worden, mits u rekening houdt met de seizoenspatronen. Echter, in tegenstelling tot veel forecasting software, kunnen deze seizoenspatronen niet automatisch in Excel geschat worden. Daarnaast moet u de data ook corrigeren voor uitschieters. Het schatten van de seizoenspatronen en het corrigeren voor uitschieters gebeurt met behulp van het voortschrijdend gemiddelde. Een uitschieter is in feite een zeer grote afwijking van dit gemiddelde. En het seizoenspatroon is het gemiddelde van alle afwijkingen van het voortschrijdend gemiddelde in dezelfde periode (bijvoorbeeld in januari). Deze berekeningen kunnen uitstekend in Excel uitgevoerd worden.

 

Corrigeren voor werkdagen

In het geval van maandelijkse gegevens moet de data tevens gecorrigeerd worden voor het aantal (werk)dagen in de maand. Dit kunt u doen door bijvoorbeeld de data te delen door het aantal (werk)dagen. De volgorde is daarbij van groot belang. De data moet eerst gecorrigeerd worden voor eventuele werkdagen, want pas daarna is het mogelijk om uitschieters te detecteren en te vervangen. Een goede bepaling van het seizoenspatroon is alleen mogelijk met data, welke al is gecorrigeerd voor uitschieters.

 

Na het voorspellen in Excel moet het seizoenspatroon en het aantal werkdagen weer toegevoegd worden aan de prognose. Hiernaast ziet u een voorbeeld van de prognose met een Logaritme model, waarbij al dan niet rekening is gehouden met uitschieters en seizoenspatroon.

 

Het model inclusief seizoenspatroon benadert beter de werkelijkheid en zal u een betrouwbaardere voorspelling geven.

 

Keuze van het juiste model

Een goed hulpmiddel bij het kiezen van het juiste model is een rapportage over de accuratesse van de prognose. Want, waar bij veel forecasting pakketten het goede model automatisch door de software gekozen wordt, moet de Excel gebruiker zelf een keuze maken. Indien de accuratesse te wensen over laat of verslechtert, zult u het model moeten aanpassen. Een pragmatische instelling is daarbij belangrijk. De belangrijkste producten verdienen het eerst de aandacht, daarna kunt u de overige producten voorzien van een goed model.

 

Documenteer het gebruik

De opzet van Excel nodigt gebruikers uit tot ad-hoc oplossingen. Dit kan zeer snel leiden tot een zodanige wildgroei, dat alleen de vaste gebruiker van de Excel sheet nog inzicht heeft in het gebruik. Het belang van een strakke structuur en een goede documentatie kan dan ook niet genoeg onderstreept worden.

Forecasting pakketten zijn in dit opzicht duidelijk in het voordeel. Ze zijn vaak voorzien van een goede documentatie. En bij problemen kunt u meestal terugvallen op een helpdesk.

 

Wat zijn de alternatieven

Een prognose gemaakt met Excel behoeft wat betreft betrouwbaarheid nauwelijks onder te doen voor een prognose gegenereerd met een forecasting pakket. Om een dergelijke prognose met Excel te kunnen maken is overigens enige kennis van statistische methoden beslist noodzakelijk. Is die kennis niet aanwezig, dan zal deze expertise verkregen moeten worden. Het verkrijgen van deze kennis, bijvoorbeeld door middel van een Excel file met voorbeelden of door een cursus, is echter relatief goedkoop.

Het alternatief voor Excel zijn forecasting pakketten en planningssystemen. Forecasting pakketten zijn specifiek bestemd om ermee te voorspellen. Planningssystemen bevatten modules, waarmee voorspeld kan worden, maar richten zich ook op wat er met de prognose moet gebeuren. Deze alternatieven genereren beiden een goede voorspelling, zonder dat de gebruiker over enige statistische kennis behoeft te beschikken. Forecasting pakketten of planningssystemen zijn over het algemeen duur.

 

Investeren in een betere prognose draagt bij aan lagere voorraden, minder out-of-stocks en een efficiëntere orderafhandeling. De beslissing om hiervoor speciaal een forecasting pakket of planningssysteem aan te schaffen, moet u niet alleen baseren op de te verwachten verbetering van de forecast accuratesse. Gebruiksgemak, efficiëntere en transparantere bedrijfsvoering, besparing op personeelskosten of kwalitatief beter onderbouwde beslissingen zijn andere overwegingen bij de definitieve keuze.

 

Samenvatting

  • Een voorspelling berekend met Excel kan even betrouwbaar zijn als een voorspelling gegenereerd met een forecasting pakket. Voorwaarde is wel, dat u beschikt over de nodige kennis. Het verkrijgen van deze kennis is, vergeleken met de aanschaf van de meeste forecasting pakketten, relatief goedkoop.
  • Een betrouwbare prognose draagt bij aan lagere voorraden, minder out-of-stocks en een efficiëntere orderafhandeling.
  • Trendmodellen en eenvoudige smoothing technieken kunnen uitstekend met Excel berekend worden.
  • Excel is niet geschikt om tijdreeksen met veel nullen te voorspellen.
  • Om een uitstekende prognose te maken, moet u rekening houden met het seizoenspatroon. Dit patroon is uitstekend met Excel te schatten.
  • De rapportage over de accuratesse van de prognose is een goed hulpmiddel bij de keuze van het juiste model.
  • Forecasting pakketten hebben het voordeel van een duidelijke structuur en een goede documentatie.
Reageer op dit artikel