Onlangs kreeg ik het verzoek om een maandelijkse forecast om te bouwen naar een wekelijkse forecast, waarbij wijzigingen in de maandelijkse forecast automatisch doorwerken in de wekelijkse forecast zonder handmatige interventie. Daarvoor gebruiken we de nieuwste mogelijkheden in Excel, te weten dynamische matrixformules.

Uitgangspunt maandelijkse forecast

De gegevens van de maandelijkse forecast worden vastgelegd in de volgende Excel-tabel:

Via Start > Stijlen > Opmaken als tabel is de dataset omgezet naar een Excel-tabel met als naam SalesForecast.

Het aantal dagen (Days) heb ik berekend met de volgende formule:

=LAATSTE.DAG([@Date];0)-[@Date]+1

De Sales per Day wordt als volgt berekend: =[@Sales]/[@Days]

Uitwerking

De lay-out voor het ombouwen naar een wekelijkse tabel ziet er als volgt uit:

In cel G4 geeft u de volgende formule in: =REEKS(54;1;1;1)

Met deze formule maakt u een plaatshouder van één kolom en 54 rijen beginnend met het getal 1 tot en met 54 en oplopend met 1.

In cel H4 voert u de volgende formule in: =REEKS(54;1;0;7). Hiermee wordt een doorlopende reeks (sequence) gemaakt die steeds 7 dagen oploopt beginnend met o en eindigend bij 371.

In cel I4 berekent u de WeekStart van de week met de volgende formule:

=DATUM(JAAR(SalesForecast[@Date]);1;1)-WEEKDAG(DATUM(JAAR(SalesForecast[@Date]);1;1);2)+H4#

De jaarcomponent in de DATUM-functie wordt bepaald door de JAAR-functie toe te passen op een Date-record in de maandtabel Salesforecast. Met de WEEKDAG-functie bepaalt u de weekdag van de weekstart uitgaande van het feit dat de weekdag op maandag begint. Hiervoor wordt de indicator 2 gebruikt.

Om de reeks naar beneden door te laten lopen telt u de waarde in kolom H (sequence) daarbij op, in dit geval H4 gevolgd door het # (hashtag)-teken. Het hashtag-teken zorgt ervoor dat de formule automatisch wordt uitgevuld.

In cel J4 berekent u het einde van de week (WeekEnd) met de volgende formule: =I4#+6. U telt 6 dagen op bij de weekstart. Het hashtag-teken zorgt ervoor dat de formule automatisch naar beneden wordt uitgevuld.

In cel K4 berekent u het begin van de maand (MonthStart) met de volgende formule: =DATUM(JAAR(I4#); MAAND(I4#);1)

Hierbij wordt gerefereerd naar de kolom van de weekstart.

In cel L4 wordt het einde van de maand (MonthEnd) berekend met de formule: =DATUM(JAAR(J4#);MAAND(J4#);1)

De formule refereert naar de kolom van het einde van de week (WeekEnd).

In cel N4 (Days Month 2)berekent u hoeveel dagen in de volgende maand vallen met de formule:

=ALS(K4#<>L4#;DAG(J4#);0)

Als MonthStart niet gelijk is aan MonthEnd wordt de dag berekend van het einde van de week.

In cel M4 (Days Month 1) wordt berekend hoeveel dagen in huidige maand vallen met de formule: =7-N4#

Ten slotte wordt de WeeklySales als volgt berekend:

=M4#*SOM.ALS(SalesForecast[Date];K4#;SalesForecast[SalesPerDay]) +

 N4#*SOM.ALS(SalesForecast[Date];L4#;SalesForecast[SalesPerDay])

De formule berekent de sales van de dagen in de huidige maand en daarbij wordt opgeteld de sales die betrekking heeft op de dagen van de volgende maand.

Met de functie SOM.ALS halen we de SalesPerDay op uit de maandtabel SalesForecast behorende bij de datum van de betrokken datum in MonthStart annex MonthEnd. Deze Sales worden dan vermenigvuldigd met het aantal corresponderende dagen in Days Month 1 annex Days Month 2.

De uiteindelijke tabel ziet er dan (na opmaak) als volgt uit:

Ter controle maken we de volgend aansluiting:

De volgende formules zijn ingevoerd:

• R4 =SOM(SalesForecast [Sales])
• R5 =SOM(O4#)
• R6 =R4-R5

De formule voor de Total Monthly Sales Forecast verwijst naar de kolom Sales in de maandtabel SalesForecast.

De formule voor de Total Weekly Sales Forecast verwijst naar de O4 (WeeklySales) en het hashtag-teken maakt het mogelijk om de gehele reeks te sommeren.

Gebaseerd op kolommen Week en WeeklySales kunt u ten slotte de volgende lijngrafiek aanmaken:

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2023, afl. 2.

Hier leest u meer Excel tips en trucs door Tony De Jonker

Volg CMWeb op LinkedIn!