Samenvatting

De structuur van het model is opgebouwd uit diverse werkbladen. In dit artikel worden de zeven werkbladen waaruit het model bestaat, uitgebreid beschreven en toegelicht met behulp van printscreens. De indeling is als volgt: eerst wordt uitleg gegeven over het werkblad Dates, vervolgens wordt stilgestaan bij de opzet en werking van het werkblad Info, tot slot komen de indeling van de overige werkbladen en de uitleg van de databasewerkbladen aan de orde. Stap voor stap wordt u door de verschillende werkbladen in de databases geloodst.

De werkmap bestaat uit de volgende werkbladen:

• Info;
• Forecast View;
• Input;
• Monthly Forecast;
• Weekly Forecast;
• Weekly Actuals;
• Dates.

Voor de onderlinge samenhang zie het schema. X 

Onderstaand wordt per werkblad de structuur toegelicht.

Uitleg van het werkblad Dates

Het werkblad Dates bevat:

• datatabellen voor het werken met verschillende tijdsperioden;
• bronnen voor de keuzelijsten;
• dynamische teksten;
• resultaat van de gemaakte selecties;
• datatabellen voor het inlezen van week- en maandgegevens.

Datatabellen

De volgende zes datatabellen worden gebruikt in het model:

• Weeknummer-Maandnummer-Offsettabel; X 

  Week-maand-offsettabel

  Deze tabel wordt gebruikt om dynamische periode-omschrijvingen te genereren in de Forecast View en om de bedragen in de overlapping te kunnen berekenen.

  

  Elk weeknummer wordt aan een maandnummer toegewezen volgens het 4-4-5-wekenprincipe. De eerste offset toont hoeveel kolommen naar links een bepaald weeknummer zich bevindt ten opzichte van de huidige week. De tweede offset geeft aan hoeveel kolommen naar rechts een bepaald weeknummer gevonden kan worden vanuit de huidige positie. De derde en laatste offset laat zien hoeveel kolommen naar links een weeknummer zich bevindt vanuit de huidige positie.

• Maandnummer-From Week-To Week; X 

  Maandnummer-From Week-To Week

  Deze tabel toont het verband tussen maandnummer en de bijbehorende weken. De tabel wordt gebruikt in de grafiekenkeuze. Aangezien we twee jaar als tijdsperiode weergeven, zijn de maandnummers doorgenummerd.

  
• Maandnaam-Maandnummer; X 

  Maandnaam-Maandnummer

  De tabel toont de maandnaam en het maandnummer:

  
• Maandnummer-Maandnaam-volgende 11 maanden als naam; X 

  Maandnummer-Maandnaam-volgende 11 maanden als naam

  De tabel ziet er als volgt uit en wordt gebruikt om dynamische periodetitels te kunnen genereren:

  
  [Bekijk grotere afbeelding in apart venster]
• Maandnummer-Maandnaam-volgende 11 maanden als nummer. X 

  Maandnummer-Maandnaam-volgende 11 maanden als nummer

  De tabel ziet er als volgt uit en wordt gebruikt om dynamische periodetitels te kunnen genereren:

  
  [Bekijk grotere afbeelding in apart venster]

Bronnen voor de keuzelijsten

Er zijn twee bronnen voor de keuzelijsten in het werkblad Info:

1. week-maandkeuze; X 

   Week-maandkeuze

   
2. grafiektypekeuze. X 

   Grafiektypekeuze

   

Dynamische teksten

Het model bevat dynamische teksten. X 
[Bekijk grotere afbeelding in apart venster]

Resultaat van de gemaakte keuzes

Voor de formules waarmee het resultaat van de gemaakte keuzes in de werkmap worden berekend, klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Datatabellen

De gegevens hier worden gebruikt als bron voor de dynamische grafieken in het werkblad Info. De data van de verschillende bronwerkbladen worden in een week- en maandformaat vastgehouden. Voor een weekformaat in uitgezoomd aanzicht klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Kolom Y bevat de weeknummers en rij 1 bevat de bereiknamen. Voor de tabel in ingezoomd aanzicht klik hier. X 

De TRANSPONEREN-matrixformules strekken zich uit over het bereik Z2 tot en met AY58. Een TRANSPONEREN-matrixformule wordt ingevoerd door het bereik eerst te markeren (bijvoorbeeld Z2 tot en met Z58), dan de formule in te voeren en dan af te sluiten met Ctrl+Shift+Enter. Deze formule brengt alle horizontale data van het werkblad Weekly Forecast en het werkblad Weekly Actuals over naar dit blad in verticale vorm.

Elke kolom bevat typische bereiknamen met de volgende achtervoegsels:

• FC heeft betrekking op Forecast;
• AC duidt op Actual;
• IC betekent Intercompany;
• In is inkomende stroom;
• Out is uitgaande stroom.

Door een systematisch gebruik van deze achtervoegsels worden de bereiknamen en formules inzichtelijker.

Voor de weekmatrixformules klik hier. X 

Voor het maandformaat in uitgezoomd aanzicht klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Voor het ingezoomde aanzicht klik hier. X 

Ook hier wordt gebruikgemaakt van de TRANSPONEREN-matrixformules. De formules strekken zich uit van Z61 tot en met AY84. Voor de maandmatrixformules klik hier. X 

Daarnaast zijn er nog SOM-formules die verwijzen naar de tabel van het weekformaat. Voor de SOM-formules klik hier. X 
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]

Opzet en werking van het werkblad Info

Het werkblad Info bevat de volgende onderdelen:

• dynamische grafieken;
• keuzelijsten.

Voor de gebruiker lijkt het erop alsof er een grafiek is zich die zich steeds aanpast afhankelijk van de selectie in de keuzelijst. In werkelijkheid zijn het verschillende grafieken met dynamische bereiknamen en verborgen gebieden die door de macro worden aangestuurd. Wanneer het werkblad Info niet beveiligd is en alle rijen zichtbaar zijn, ziet u het werkblad in uitgezoomd aanzicht X .

De keuzelijsten worden toegepast om de volgende selecties te kunnen maken:

• maand/weekaanzicht;
• periode aanzicht (van/tot);
• type grafiek.

Voor een voorbeeld klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Afhankelijk van het gekozen aanzicht en de grafiekselectie zal de VBA-macro verbonden aan de keuzelijsten de betrokken rijen verbergen. Er zijn vijf week- en vijf maandgrafieken.

De keuzelijsten hebben diverse instellingen. X 

1 Aanzicht
Invoerbereik: LView
Koppeling met cel: CtrView

2 Grafiek weergave van maand
Invoerbereik: LMonths
Koppeling met cel: CtrMonths1

3 Grafiek weergave t/m maand
Invoerbereik: LMonths
Koppeling met cel: CtrMonths2

4 Grafiek weergave van week
Invoerbereik: Weeks
Koppeling met cel: CtrWeeks1

5 Grafiek weergave t/m week
Invoerbereik: Weeks
Koppeling met cel: CtrWeeks2

Grafiek selectie maand
Invoerbereik: LChartSelection
Koppeling met cel: CtrChartSelection1

Grafiek selectie week
Invoerbereik: LChartSelection
Koppeling met cel: CtrChartSelection2

De dynamische grafieken maken gebruik van dynamische bereiknamen en hebben de volgende instellingen:

• verwacht en werkelijk saldo per week; X 
  [Bekijk grotere afbeelding in apart venster]

  Forecast
  =SERIES("Forecast",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartBalancesFC,1)

  Actual
  =SERIES("Actual",,'Cashflow Forecast.xls'!ChartBalancesAC,2)

• verwachte uitgaven naar soort en week; X 
  [Bekijk grotere afbeelding in apart venster]

  Creditors
  =SERIES("Creditors",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartCredFC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartCredICFC,2)

  Salaries
  =SERIES("Salaries",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartSalFC,3)

  Pension
  =SERIES("Pension",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartPenlFC,3)

  Taxes
  =SERIES("Taxes",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartTaxFC,5)

  Other Payments
  =SERIES("Other payments",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartOthOutFC,6)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartFinOutFC,7)

• werkelijke uitgaven naar soort en week; X 
  [Bekijk grotere afbeelding in apart venster]

  Creditors
  =SERIES("Creditors",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartCredAC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartCredICAC,2)

  Salaries
  =SERIES("Salaries",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartSalAC,3)

  Pension
  =SERIES("Pension",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartPenlAC,3)

  Taxes
  =SERIES("Taxes",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartTaxAC,5)

  Other Payments
  =SERIES("Other payments",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartOthOutAC,6)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartFinOutAC,7)

• verwachte ontvangsten per soort en week; X 
  [Bekijk grotere afbeelding in apart venster]

  Debtors
  =SERIES("Debtors",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartDebFC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartDebICFC,2)

  Vending machines
  =SERIES("Vending machines",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartVendFC,3)

  Other receipts
  =SERIES("Other receipts",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartOthInFC,4)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartFinInFC,5)

• werkelijke ontvangsten naar soort en week; X 
  [Bekijk grotere afbeelding in apart venster]

  Debtors
  =SERIES("Debtors",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartDebAC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartDebICAC,2)

  Vending machines
  =SERIES("Vending machines",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartVendAC,3)

  Other receipts
  =SERIES("Other receipts",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartOthInAC,4)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartWeeks,'Cashflow Forecast.xls'!ChartFinInAC,5)

• verwacht en werkelijk saldo per maand; X 
  [Bekijk grotere afbeelding in apart venster]

  Forecast
  =SERIES("Forecast",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartBalancesMFC,1)

  Actual
  =SERIES("Actual",,'Cashflow Forecast.xls'!ChartBalancesMAC,2)

• verwachte uitgaven naar soort en maand; X 
  [Bekijk grotere afbeelding in apart venster]

  Creditors
  =SERIES("Creditors",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartCredMFC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartCredMICFC,2)

  Salaries
  =SERIES("Salaries",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartSalMFC,3)

  Pension
  =SERIES("Pension",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartPenMFC,4)

  Taxes
  =SERIES("Taxes",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartTaxMFC,5)

  Other Payments
  =SERIES("Other payments",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartOthOutMFC,6)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartFinOutMFC,7)

• werkelijke uitgaven naar soort en maand; X 
  [Bekijk grotere afbeelding in apart venster]

  Creditors
  =SERIES("Creditors",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartCredMAC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartCredMICAC,2)

  Salaries
  =SERIES("Salaries",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartSalMAC,3)

  Pension
  =SERIES("Pension",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartPenMAC,4)

  Taxes
  =SERIES("Taxes",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartTaxMAC,5)

  Other Payments
  =SERIES("Other payments",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartOthOutMAC,6)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartFinOutMAC,7)

• verwachte ontvangsten naar soort en maand; X 
  [Bekijk grotere afbeelding in apart venster]

  Debtors
  =SERIES("Debtors",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartDebMFC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartDebMICFC,2)

  Vending Machines
  =SERIES("Vending machines",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartVendMFC,3)

  Other receipts
  =SERIES("Other receipts",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartOthInMFC,4)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartFinInMFC,5)

• werkelijke ontvangsten naar soort en maand. X 
  [Bekijk grotere afbeelding in apart venster]

  Debtors
  =SERIES("Debtors",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartDebMAC,1)

  Intercompany
  =SERIES("Intercompany",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartDebMICAC,2)

  Vending Machines
  =SERIES("Vending machines",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartVendMAC,3)

  Other receipts
  =SERIES("Other receipts",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartOthInMAC,4)

  Finance
  =SERIES("Finance",'Cashflow Forecast.xls'!ChartMonths,'Cashflow Forecast.xls'!ChartFinInMAC,5)

Indeling van de werkbladen

De indeling en layout van de werkbladen Forecast View, Input, Monthly Forecast, Weekly Forecast en Weekly Actuals zijn aan elkaar gelijk voor de kolommen A tot en met E. De rijen zijn beschreven tot en met rij 63. Voor een weergave van de indeling klik hier. X 

Het werkblad Forecast View

Voor de formules in het werkblad Forecast View klik hier. X 
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]
[Bekijk grotere afbeelding in apart venster]

Bereiknaam:
Offset1

Bereiknaam:
Offset2

De bereiknamen Offset1 en Offset2 worden gebruikt bij de berekening van de overlapping.

Het werkblad Input

Naast de gewone SOM-formules komen in het werkblad Input nog de volgende formules voor:

• formules om de weeknummers te bepalen; X 

  Cel	Formule
  E3	="Act wk "&Weeknbr-1
  E4	=ALS(Weeknbr=1;'Weekly Actuals'!$E$4;VERSCHUIVING('Weekly Actuals'!$D$47;0;Weeknbr))
  F3	="wk "&Weeknbr
  G3	=ALS(Weeknbr>52;"wk 1";"wk "&SOM(Weeknbr+1))
  H3	=ALS(Weeknbr>52;"wk 2";ALS(Weeknbr>51;"wk 1";"wk "&SOM(Weeknbr+2)))
  I3	=ALS(Weeknbr>52;"wk 3";ALS(Weeknbr>51;"wk 2";ALS(Weeknbr>50;"wk 1";"wk "&SOM(Weeknbr+3))))
  J3	=ALS(Weeknbr>52;"wk 4";ALS(Weeknbr>51;"wk 3";ALS(Weeknbr>50;"wk 2";ALS(Weeknbr>49;"wk 1";"wk "&SOM(Weeknbr+4)))))

• formules om de saldi over te brengen naar de volgende weken; X 

  Cel	Formule
  L4	=VERSCHUIVING(J47;0;-Offset2)
  F4	=E47
  G4	=H47
  H4	=I47
  I4	=J47

• formules om de saldi over te brengen naar de volgende maanden; X 

  Cel	Formule
  M4	=L47
  N4	=M47
  O4	=N47
  P4	=O47
  Q4	=P47
  R4	=Q47
  S4	=R47
  T4	=S47
  U4	=T47
  V4	=U47

• formules om de overlapping te berekenen; X 

  De overlappingformules zijn analoog aan die in het werkblad Forecast. In K10 staat de formule:

  =ALS(ISFOUT(SOM(VERSCHUIVING(J10;0;Offset1;1;Offset2)));0;SOM
  (VERSCHUIVING(J10;0;Offset1;1;Offset2)))

  Deze formule is in kolom K naar de overige relevante cellen naar beneden gekopieerd.

• formules om de maanden te bepalen; X 

  De headers inclusief de zichtbaar gemaakte hulprijen zien er als volgt uit:

  
  [Bekijk grotere afbeelding in apart venster]

  Rij 1 bevat de hulprij met volgnummers 3 tot en met 13.

  Rij 2 bepaalt welke weeknummers in welke maand vallen. In L2 staat de volgende formule:

  =VERT.ZOEKEN(L5;ListWeeks;2;0)&"- "&VERT.ZOEKEN(L5;ListWeeks;3;0)

  Deze formule is doorgekopieerd naar cellen M2 tot en met V2.

  Rij 3 bepaalt de maanden. In L3 staat de volgende formule:

  =VERT.ZOEKEN(VERT.ZOEKEN(Weeknbr;ListWeekNbrs;2;0);ListMonths;L1;0)

  Deze formule is doorgekopieerd naar cellen M3 tot en met V3.

  Rij 5 bepaalt de doorlopende volgnummers van de maanden. Januari van het daaropvolgend jaar krijgt volgnummer 13. In L5 staat de volgende formule:

  =VERT.ZOEKEN(VERT.ZOEKEN(Weeknbr;ListWeekNbrs;2;0);ListMonths2;L1;0)

  Deze formule is doorgekopieerd naar cellen M5 tot en met V5.

• overige formules. X 

  In de onderliggende bladen komen nog hulpformules voor om te bepalen tot wanneer data zijn ingevoerd.

  Cel	Formule
  Monthly Forecast - cel A1 (NbrMonths)	=AANTAL(E7:AB7)
  Weekly Forecast - cel A1 (NbrWeeksFC)	=ALS(AANTAL(E7:BH8)<=0;0;AANTAL(E7:BH8))
  Weekly Actuals - cel A1 (NbrWeeksAC)	=AANTAL(E7:BI7)

Uitleg van de databasewerkbladen

Alle gegevens worden in de bijbehorende databases opgeslagen, te weten:

• Monthly Forecast;
• Weekly Forecast;
• Weekly Actual.

De headers van de Monthly Forecast omvatten data van twee jaar en de Weekly Forecast/Weekly Actual heeft betrekking op 52 weken van het eerste jaar en 5 weken in het nieuwe jaar.

De Monthly Forecast bevat een hulprij 2 om het beginsaldo in rij 4 te berekenen. Voor een weergave klik hier. X 
[Bekijk grotere afbeelding in apart venster]

De formule in cel E2 luidt als volgt:

=ALS(VERT.ZOEKEN(OffsetActual;ListWeekNbrs;2;0)>=VERT.ZOEKEN
(E3;ListMonths3;2;0);1;0)

Deze formule is doorgekopieerd naar de cellen F2 tot en met P2.

De formule voor het beginsaldo staat in cel E4:

=ALS(E2=1;VERSCHUIVING(StartInputWeeklyAC;-1;VERT.ZOEKEN(VERT.ZOEKEN
(E3;ListMonths3;2;0);ListWeeks;2;0);1;1);MFC!E4)

Deze formule is doorgekopieerd naar de cellen F4 tot en met P4.

Conclusie

Met behulp van de werkbladen van het model bent u in staat de vinger aan de pols te houden bij een liquiditeitsplanning. Het model geeft nauwgezet per week en maandelijks de forecasts aan. Alle gegevens kunt u terugvinden en raadplegen in de desbetreffende databases.