Excel waardes extrapoleren

[Bart ®]

Beste medetweakers,

Ik zit hier op mijn werk met een lastige vraag. Ik heb een mooie excelsheet met aantallen storingen per week. Ik wil weten hoeveel storingen ik aan het eind van het jaar kan verwachten. Dat is natuurlijk vrij simpel door het gemiddelde te nemen van de weken tot nu toe, en dat keer 52 te doen.

Echter, er zit in de zomervakantie een dip in het aantal storingen. Grofweg, als we in januari 100 storingen hebben, dan hebben we in februari 95, maart 90, april 80, mei 70, juni 50, juli 30, augustus 30, september 70, oktober 80, november 90, december 95.

Dus ik heb deze kennis, en ik heb een lijst met storingen die er als volgt uitziet:
week aantal
1 34
2 84
3 13
4 42
enz enz

De getallen fluctueren flink, omdat we niet elke week even veel dagen storingsrondes rijden.

Nu zit ik dus te zoeken hoe ik zo accuraat mogelijk kan voorspellen wat ik aan het eind van jaar kan verwachten. Naarmate het jaar verstrijkt wordt de voorspelling natuurlijk steeds nauwkeuriger, maar lineaire extrapolatie is een drama. In het begin van het jaar had ik een veel te hoge voorspelling, want de 'slechte' zomer zat er nog aan te komen. Nu is mijn voorspelling weer veel te laag, want de 'goede' winter zit nog niet in het gemiddelde.

Ik weet wat een gewogen gemiddelde is, maar ik kan mijn vinger er niet op leggen hoe ik dat toe zou moeten passen op mijn cijfers. Als ik simpelweg de getallen van juni 0.5x zou doen, dan kom ik volgens mij onterecht te laag uit. Laat ik de cijfers van juni maar 0.5x meetellen in het gemiddelde, dan kom ik te hoog uit, denk ik.

Kan iemand mij hier uit de brand helpen? Ik snap het niet meer .

[Dido]

Dat gaat je niet op een accurate manier lukken zonder de cijfers van meerdere jaren. Dat is ook precies wat toko's als het CBS doen: die vergelijken de cijfers van maand X met de situatie van maand X van vorig jaar.

Kijk dus of je cijfers kunt vinden van voorgaande jaren, dan kun je per maand inschatten wat de trend is, en zo tot een schatting komen voor de gewenste periode.

[Bart ®]

Die cijfers van voorgaande jaren zijn er helaas niet, ik moet het doen met de cijfers op basis van ervaring. Dat is in ieder geval nauwkeuriger dan weekgemiddelde*52... Ik weet alleen dus niet hoe ik de ervaringscijfers moet verwerken in een berekening.

[Dryjognos]

Met de standaarddeviatie van je aantal storingen per week kan je wel een (brede) band maken waartussen het kan schommelen (gemiddelde plusminus standaardeviatie maal 2 = 95,45% [wikipedia]).

Verder kan ik niet bedenken waarom een educated guess op basis van het gemiddelde, plus de verwachting dat de winter erger is, niet genoeg is voor een voorspelling. Zeker als je maar tot het einde van het jaar hoeft. (?)

Misschien dat je nog bepaalde reeksen in type storingen kan vinden. Storingstype A blijft wel gelijk in het hele jaar, storingtype B (bijna) alleen in de winter.

[Icephase]

Bart ® schreef op maandag 16 juli 2018 @ 12:10:
Die cijfers van voorgaande jaren zijn er helaas niet, ik moet het doen met de cijfers op basis van ervaring. Dat is in ieder geval nauwkeuriger dan weekgemiddelde*52... Ik weet alleen dus niet hoe ik de ervaringscijfers moet verwerken in een berekening.

Hier is ook geen standaardberekening voor. Dit is nou typisch het werk van een financial (of heel misschien business-) controller. O.b.v. eigen ervaring en inzichten een prognose geven. Excel is daarbij een hulpmiddel, maar kan dat zeer zeker niet zelfstandig.

Ik zeg altijd: Excel is heel goed in getallen, maar niet in informatie.

[naitsoezn]

Dit heeft volgens mij vrij weinig met Excel te maken, want ook Excel kan geen informatie tevoorschijn toveren die er niet is zonder aanvullende aannames te doen. Die aannames zul je er zelf in moeten stoppen en de nauwkeurigheid van de voorspellingen zal afhankelijk zijn van de nauwkeurigheid van je aannames.

Om het in ieder geval in de richting van Excel te duwen, kun je een gewogen gemiddelde berekenen met eigen bedachte weegfactoren door een extra kolom te maken naast je week-getallen. Standaard stel je dan een weging van 1 in, en als je denkt dat de getallen van een bepaalde periode minder representatief zijn dan vul je daar een lager getal in. De som van (#storingen * weegfactor) gedeeld door de som van de weegfactoren is dan je voorspelling op basis van gewogen gemiddelde.

[ Voor 43% gewijzigd door naitsoezn op 16-07-2018 12:56 ]

[Bart ®]

Dryjognos schreef op maandag 16 juli 2018 @ 12:49:
Met de standaarddeviatie van je aantal storingen per week kan je wel een (brede) band maken waartussen het kan schommelen (gemiddelde plusminus standaardeviatie maal 2 = 95,45% [wikipedia]).

Verder kan ik niet bedenken waarom een educated guess op basis van het gemiddelde, plus de verwachting dat de winter erger is, niet genoeg is voor een voorspelling.

Dat is ook voldoende, maar hoe verwerk ik de zomerdip...

Zeker als je maar tot het einde van het jaar hoeft. (?)

Contract loopt dan af.

Misschien dat je nog bepaalde reeksen in type storingen kan vinden. Storingstype A blijft wel gelijk in het hele jaar, storingtype B (bijna) alleen in de winter.

Nee, dat is hier niet aan de hand .

Icephase schreef op maandag 16 juli 2018 @ 12:52:
[...]


Hier is ook geen standaardberekening voor. Dit is nou typisch het werk van een financial (of heel misschien business-) controller. O.b.v. eigen ervaring en inzichten een prognose geven. Excel is daarbij een hulpmiddel, maar kan dat zeer zeker niet zelfstandig.

Ik zeg altijd: Excel is heel goed in getallen, maar niet in informatie.

Daar heb je zeker een punt. Het hoeft inderdaad ook niet in Excel. Ik heb toevallig die aantallen storingen in Excel, en mijn financiële overzicht ook. Dus voor mij is het logisch om het in Excel te doen. Maar als het ergens anders in moet / kan, ook prima.

naitsoezn schreef op maandag 16 juli 2018 @ 12:53:
Dit heeft volgens mij vrij weinig met Excel te maken, want ook Excel kan geen informatie tevoorschijn toveren die er niet is zonder aanvullende aannames te doen. Die aannames zul je er zelf in moeten stoppen en de nauwkeurigheid van de voorspellingen zal afhankelijk zijn van de nauwkeurigheid van je aannames.

Om het in ieder geval in de richting van Excel te duwen, kun je een gewogen gemiddelde berekenen met eigen bedachte weegfactoren door een extra kolom te maken naast je week-getallen. Standaard stel je dan een weging van 1 in, en als je denkt dat de getallen van een bepaalde periode minder representatief zijn dan vul je daar een lager getal in. De som van (#storingen * weegfactor) gedeeld door de som van de weegfactoren is dan je voorspelling op basis van gewogen gemiddelde.

Ik ga dat proberen, bedankt!

[ Voor 51% gewijzigd door Bart ® op 16-07-2018 13:02 ]

[HenkEisDS]

Soms is het veel makkelijker om gewoon op ruitjespapier een scatterplot te tekenen en op basis hiervan min/max prognoses te maken voor de toekomst.

En leg je er niet zomaar bij dat er geen cijfers zijn van vorig jaar lijken te zijn. Misschien dat iemand bij accounting dit soort storingsrondjes gewoon heeft bijgehouden.

[Icephase]

Bart ® schreef op maandag 16 juli 2018 @ 12:58:
[...]

Daar heb je zeker een punt. Het hoeft inderdaad ook niet in Excel. Ik heb toevallig die aantallen storingen in Excel, en mijn financiële overzicht ook. Dus voor mij is het logisch om het in Excel te doen. Maar als het ergens anders in moet / kan, ook prima.

[...]

Mijn punt is: je kunt van alles berekenen in Excel, maar Excel gaat je niet vertellen WAT je moet gaan berekenen. Dat moet je zelf bedenken.
Excel als tool is prima hiervoor te gebruiken, maar de formules die je gaat gebruiken zul je zelf moeten kiezen. Er is geen goed of fout in dit geval. Het lijkt erop dat je zelf nog niet zo'n goed idee hebt van wat een reele uitkomst gaat zijn. Desnoods tel je het gemiddelde van mei en juni voor juli en augustus en het gem van de eerste 4 maanden voor september t/m december. Zou dat realistisch zijn?