Excel data anlyseren en forecast maken

[robbieboss]

Mijn vraag
Binnen de organisatie draaien we omzet op basis van impressies (display). Een campagne kan starten op 5 mei 2022 en eindigen op 6 augustus 2022, maar kan ook onbeperkt doorlopen (geen einddatum). Er hangt een CPM tarief aan en een aantal impressies. Ik heb dus in de cellen de volgende gegevens.

Klantnaam
Campagnenaam
Startdatum
Einddatum (indien van toepassing)
Aantal impressies per hele maand (een campagne kan ook twee weken duren en dan zijn het de impressies voor die 2 weken)
CPM tarief

Ik wil op basis van deze gegevens per kwartaal kunnen zien wat de omzet is van de nieuwe campagnes in een specifiek kwartaal en hoeveel omzet we in het komende kwartaal kunnen verwachten van deze nieuwe campagnes (soms zit er al een einddatum aan welke halverwege het komende kwartaal valt). En ik wil kunnen zien hoeveel campagnes er zijn gestopt in een specifiek kwartaal en wat dat voor de omzet voor het komende kwartaal betekend.

Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
Op basis van de data heb ik zelf getracht de gewenste data er uit te halen, maar nog niet gelukt. Ik heb via Google meerdere bronnen geraadpleegd, maar niks toont mij waar ik naar op zoek ben helaas.

 

[AlexCEL]

Kennelijk heb je al een Excel-bestand gemaakt. Plaats eens een representatief voorbeeld hiervan bij de vraag, ook met wat gewenste uitkomsten.

Nu is er geen zinnig antwoord te geven.

 

[robbieboss]

Ik heb even een voorbeeld in Excel gemaakt met data. Ik probeer dus op basis van deze data per kwartaal in te kunnen zien wat de verwachtingen zijn. Er zijn campagnes welke starten in Q1 en stoppen in Q2 en niet een hele maand lopen. Als er geen einddatum is dan staan de impressies per maand in kolom E. Mocht de campagne een vaste looptijd hebben dan staat het aantal over de hele periode in kolom D.

Het kan zijn dat een campagne zonder einddatum opgezegd wordt en dus een einddatum krijgt. Dan moet idealiter bij invoeren van een einddatum er ook berekend worden tot welk kwartaal de omzet loopt en hoeveel er nog verwacht wordt in het laatste kwartaal op basis van de einddatum.

Is dit iets duidelijker?

 

[AlexCEL]

Dit verduidelijkt al het e.e.a. Nog 1 verzoek: kun je voor dit voorbeeld ook (handmatig) de gewenste uitkomsten eens invullen?

Dat is belangrijk bij het testen van mogelijke oplossingen.

 

[robbieboss]

Yes, ik heb nu van de eerste twee regels de gewenste uitkomst neergezet. Wat je ziet gebeuren is dat de campagne van Klant A op 10 januari is gestart en dat de looptijd 30000 impressies is. De campagne valt in Q1 en Q2. In dit geval heb ik dus handmatig gekeken wat de omzet in januari, februari en maart zou zijn en die bij elkaar opgeteld. En dat ook zo voor Q2 de resterende dagen gedaan.

Omdat er een einddatum in zit heb ik voor omzetverwachting Q2 gekeken naar aantal dagen in april en dan dus gekeken naar het aantal impressies wat hij in die periode nog kan uitserveren.

Voor klant B zonder einddatum is het iets eenvoudiger. Want daar gaat het vooral om de startmaand en het aantal dagen in die eerste maand en wat dat dan pro rato is.

Dat is dus nog een addertje onder het gras. Een klant kan een campagne hebben van 10000 impressies per maand zonder einddatum, maar als hij start op 15 januari dan is het niet zo dat hij de hele 10000 krijgt omdat dat voor een gehele maand zou zijn.

 

[AlexCEL]

Hoe kom je tot die omzetten? Ik kan er werkelijk geen touw aan vast knopen.

 

[roeljongman]

de tarieven zijn per 1000 cpm, dat is zo'n online marketing weetje.
30000 impressies leveren maar 375 euro op (of 374,99 in dit voorbeeld)

 

[robbieboss]

Hoe kom je tot die omzetten? Ik kan er werkelijk geen touw aan vast knopen.

Sorry, had dat inderdaad moeten verduidelijken wat roeljongman zegt. Het gaat inderdaad om een CPM tarief wat je betaald per 1000 impressies. Dus wanneer iemand 8000 impressies per maand heeft afgenomen, dan betaald hij daar 80 euro voor. Dus per kwartaal is dat dan 240 euro.

Dus als iemand 8000 impressies per maand afneemt en start op 15 mei dan zoek ik dus eigenlijk een formule die het volgende voor mij oplost. Hij weet dat de startdatum in Q2 ligt en daar de omzet in gezet moet worden en dat de eerste maand niet volledig is. Hij moet de 8000 impressies dus idealiter delen door het aantal dagen van die maand zodat je impressies per dag hebt en dan maal het aantal resterende dagen wat hij heeft in die maand mei. En dan moet hij ook weten dat juni ook in Q2 valt en dat dus bij omzet Q2 optellen bij elkaar.

Als hij geen einddatum heeft dan moet hij voor Q3 en Q4 dus voor de volle drie maanden doorrekenen in die kwartalen.

Tegelijkertijd zijn er ook campagnes met een vaste looptijd, bijvoorbeeld startdatum 10 januari en einddatum 10 april. In dat geval moet er dus automatisch berekend worden voor die regel wat de omzet Q1 en omzet Q2 is, maar voor Q3 en Q4 moet hij niks uitrekenen want dan is de campagne al afgelopen.

Ik hoop dat dit het nog verder verduidelijkt.

 

[AlexCEL]

Ik ben aan het puzzelen, op zich wel leuke uitdaging. Nog eventjes geduld.

 

[snb]

Zo dus:

 

[robbieboss]

Zo dus:

Gaaf, ja dit komt echt op 95% al! Ik zie alleen dat bijvoorbeeld bij klant A dat volgens mijn berekening de gehele looptijd 91 dagen is, dus dan zou ik in Q2 uitkomen op een iets ander bedrag. De start en einddatum zijn ook dagen die tellen want het is vanaf en t/m. Dus dat zou voor klant A in Q1 iets minder omzet zijn en Q2 iets meer omzet volgens mij eigen handmatige berekening. Namelijk Q1 EUR 333,- en Q2 EUR 41,- of misschien doe ik wel iets fout haha.

En ik probeerde om Q1 2023 er achter te zetten met de datum 1-1-2023, alleen als ik dan van klant B zonder einddatum de formule doortrek naar K4 kom ik op een iets lager bedrag uit, namelijk 240 in Q4 en 235 in Q1 2023.

Maar ik vind dit al echt geweldig, mega bedankt al.

 

[cow18]

@snb
365.25/12 = 30.44, niet 30.7 of hebben ze in nederland langere dagen dan hier ?

 

[robbieboss]

Ah vandaar, er wordt een gemiddelde aantal dagen per maand gepakt. Is er geen manier om voor de maanden in het kwartaal het exacte aantal dagen te berekenen? Al is het verschil minimaal natuurlijk. En hoe zou ik het kunnen doortrekken naar 2023?

 

[roeljongman]

is dat echt zo belangrijk die exacte dagen? ik zou gewoon met maand is 30 dagen werken dan is elk kwartaal 90 dagen en kun je gewoom 1/12 voor maand 3/12 (1/4) voor kwartaal berekenen en alleen de start en eind maand exacte dagen.

je stelde zelf al een vraag over waarom Q1 23 uitkomt op 235 ipv 240 zoals Q4 dat komt doordat q4 92 dagen heeft (2 lange maanden en 1 korte) en Q1 90 dagen (2 lange maanden en 1 extra korte)

Er is geen kwartaal gelijk in aantal dagen en de omzet bij langlopende campagnes dus ook niet maar de verschillen zijn marginaal. en daardoor eerder vragen oproepen dan nauwkeurigheid geven.
Exacte dagen is nuttig als je de campagne voor 1 miljoen euro loopt want dan kan een dag meer of minder duizend euro omzet schelen, maar zolang het om tientjes werk per maand gaat is niemand geintresseerd of het nu 39, 40 of 41 euro is voor die maand op basis van echte dagen. Toch? ofwel..

 

[robbieboss]

Je hebt op alle punten gelijk, ik kon in de formule niet terugvinden dat er zo slim gekeken werd naar het aantal dagen in een kwartaal. Maar als ik hem naar de andere kwartalen doortrek dan kom ik inderdaad uit op wat jij aangeeft. Dus schijnbaar wordt er dus inderdaad wel gekeken in de formule naar het aantal dagen in het kwartaal.

Ik ga even de echte data er in plaatsen en kijken of ik dingen tegenkom. Maar ik ben hier al onwijs mee geholpen en dit had ik zelf nooit kunnen maken. Bedankt @snb en ook @AlexCEL. Mocht je iets gemaakt hebben, deel hem dan vooral. Ook voor mij om te leren en voor je genomen moeite. Bedankt!

 

[snb]

Als jij het aantal eenheden per maand opgeeft kan dat natuurlijk nooit kloppen met maanden van variabele lengte.
Daarmee heb je zelf de 'onnauwkeurigheid' geïntroduceerd.
Als je een dagnauwkeurigheid wil -wat in dit geval niet interessant is- moet je ook de eenheden per dag en niet per maand specificeren

 

[robbieboss]

Klopt helemaal. Overigens is het zo dat het aantal eenheden per maand 30.000 is, dan is dat aantal gelijk voor een kortere en langere maand. Dus het systeem zal die 30.000 dan in minder dagen uitleveren. Zou dat nog verschil uitmaken in de formules? Denk het niet toch?

Nogmaals dank!

 

[snb]

Svp niet citeren !

 

[cow18]

ik heb de getallen een beetje afgerond en de eenheden verandert om sneller fouten te zien, maar anders denk ik dat dit een goeie benadering is (99% ipv. 95%)

 

[robbieboss]

Ah mooie aanpassing @cow18, ik zit alleen met een klein probleem en dat is het volgende.

- Klanten met een startdatum en einddatum en een x aantal impressies voor de looptijd gaat goed
- Klanten met een startdatum, zonder einddatum en een x aantal impressies per maand gaat goed

Wat gaat fout

- Klanten met een startdatum en een einddatum en een x aantal impressies per maand komt geen berekening uit. Deze worden nooit zo ingeboekt, maar kunnen door een opzegging wel een einddatum krijgen. In de Excel worden die dan voorzien van een einddatum zodat we weten dat de omzet vanaf moment y stopt.

Is dit iets wat je nog zou kunnen aanpassen?