Altijd vooruitkijken: zo maak je een dynamische rolling forecast in Excel

Een rapport dat vaak wordt gebruikt, combineert de realisatie met de forecast voor de komende maanden. Wij leggen uit hoe je zo'n rapportage zelf maakt, op een manier die efficiënt, foutloos en volledig geautomatiseerd is.

Het maken van dergelijke rapporten kost normaal gesproken veel tijd en vereist verschillende handmatige handelingen. In deze bijdrage laten we zien hoe je met een nieuwe opzet van tabellen en dynamische matrixformules een robuust model én een dynamische rolling forecast kunt opzetten.

Opzet van het model

Het model bestaat uit de volgende bladen:

De 2 inputbladen (Actual en Budget) zijn als tabellen opgemaakt, met respectievelijk de tabelnamen Actual en Budget. Ze zien er als volgt uit:

Berekening van de verdelingen

In het blad Rapport worden de bedragen over de dynamische tijdlijn berekend en verdeeld.

Om dit blad te vullen, gaan we als volgt te werk:

In rij 3 voer je de volgende 4 datum-parameters in: StartJaar, StartMaand, Maanden en MaxDateActual. Je geeft de gekleurde inputcellen een bereiknaam die hetzelfde is als de cel links. Je markeert cellen B3-C3 en uit het Menu kies je: Formules > Gedefinieerde namen > Maken o.b.v. selectie > Linkerkolom > OK. Dit doe je ook op voor de overige parameters.
Vervolgens geef je de volgende dynamische matrixformules voor:

o De aanmaak van een horizontale dynamische getallenreeks met volgnummers. Dit is een plaatshouder voor de dynamische tijdlijn per maand. Cel C11 bevat de dynamische matrixformule: =REEKS(1;Maanden;0;1). Vervolgens worden de getallen 0 tot en met 11 weergegeven.

o Het ophalen projectnummers: B14=SORTEREN(UNIEK(Budget[Project])), waarmee alle projectnummers in verticale gesorteerde volgorde uit de Budgettabel worden weergegeven.

o Het maken van een horizontale dynamische tijdlijn: C13=DATUM(StartJaar;StartMaand+C11#;1)

o Het maken van een dynamische horizontale koptekst waaruit je kunt aflezen of het betrokken tijdvak betrekking heeft op Actual of Budget: C12=ALS(C13#<=MaxDateActual;"Actual";"Budget")

o De berekening van de verdeling met behulp van de volgende rechthoekformule: =ALS.FOUT(INDEX(Actual[#Alles];VERGELIJKEN(C13#;Actual[[#Alles];[Datum]];0); VERGELIJKEN(B14#;Actual[#Kopteksten];0));0)*(C13#<=MaxDateActual) + SOMMEN.ALS(Budget[Bedrag];Budget[Project];B14#;Budget[Datum];C13#)*(C13#>MaxDateActual)

o De berekening van de Totalen in horizontale richting: C5 =BYCOL(C14#;SOM)

o De berekening van de Actual totalen in horizontale richting: C7 =C5#*(C12#="Actual")

o De berekening van de Budget totalen in horizontale richting: C9 =C5#*(C12#="Budget")

De dynamische rechthoekformule haalt eerst de Actual-cijfers op met behulp van de INDEX-VERGELIJKEN functies. Als er geen waarde wordt gevonden, wordt er via de ALS.FOUT functie de waarde nul geretourneerd. Deze voorwaarde geldt alleen als de datum van het betrokken tijdvak kleiner of gelijk is aan de datum zoals vermeld in de variabele MaxDateActual.

Lees ook

Power Query voor gevorderden: sales forecast-data helder geanalyseerd

Vervolgens wordt via de SOMMEN.ALS functie de Budget-cijfers opgehaald als de datum van het betrokken tijdvak groter of gelijk is aan de datum zoals vermeld in de variabele MaxDateActual. Het * teken kun je interpreteren als de EN-functie en het + teken kan gezien worden als de OF-functie.

Het dynamisch inkleuren van de totalen, kopteksten Actual-Budget en kopteksten voor de tijdvakken kan je eenvoudig doen met voorwaardelijke opmaak.

Totaal: Markeer het gebied C5-N5. Uit het Menu kies je: Start > Stijlen > Voorwaardelijke opmaak > Markeringsregels voor cellen > Groter dan > 0 > Opmaak > Opvulling > Lichtblauwe kleur > OK.

Koptekst Actual – Budget: Markeer het gebied C12-N12 en uit het Menu kies je: Start > Stijlen > Voorwaardelijke opmaak > Gelijk aan > Actual > Groene opvulling met donkergroene tekst > OK. Vervolgens markeer je het gebied C5=12-N12 en uit het Menu kies je: Start > Stijlen > Voorwaardelijke opmaak > Gelijk aan > Budget > Gele opvulling met donkergele tekst > OK.

Koptekst tijdvakken: Markeer het gebied C13-N13 en kies uit Menu: Start > Stijlen > Voorwaardelijke opmaak > Groter dan > 0 > Opmaak > Aangepaste indeling > Opvulling > Donkerblauw > Lettertype Tekenstijl > Vet > Kleur > Wit > OK.

Zodra je de StartMaand en/of het aantal Maanden verandert, zal daardoor ook de verdelingstabel zich direct en automatisch aanpassen.

Maken van een dynamische grafiek

Je kunt de volgende dynamische grafiek maken in een afzonderlijk blad:

Je moet eerst een aparte plaatshouder maken voor de grafiek. Maak daarvoor de kopteksten: Datum, Actual en Budget (B3-D3).

Je kunt de berekende waarden ophalen uit het vorige blad genaamd Rapport met behulp van de volgende dynamische matrixformules:

Datum: B4 =TRANSPONEREN(Rapport!C13#)
Actual: C4 =TRANSPONEREN(Rapport!C7#)
Budget: D4 =TRANSPONEREN(Rapport!C9#)

Je doorloopt de volgende stappen om de grafiek aan te maken:

Markeer de cellen B3-D15 en selecteer uit het menu: Invoegen > Grafieken > Kolom- of Staafdiagram > 2D-Gegroepeerde kolom.
Verwijder de grafiektitel.
Kies uit het menu: Grafiekontwerp > Grafiekstijlen > Stijl 8.
Klik met de rechtermuisknop op een van de Actual-staven. Uit het verkorte menu kies je: Gegevensreeks opmaken > Opties voor reeks > Primaire as > Overlapping van reeks: 80% > Breedte tussenruimte > 10%.
Klik met de rechtermuisknop op een van de Budget-staven. Uit het verkorte menu kies je: Gegevensreeks opmaken > Opties voor reeks > Primaire as > Overlapping van reeks: 80% > Breedte tussenruimte > 10%.

Zodra je in het verdelingsblad Rapport de StartMaand en/of het aantal Maanden verandert, zal daardoor ook de verdelingstabel zich direct en automatisch aanpassen alsmede de daarbij behorende grafiek.