Samenvatting

Elke onderneming investeert regelmatig in duurzame middelen, zoals kantoorinventaris, wagenpark, gebouwen en dergelijke. Het is gebruikelijk om tijdens de jaarlijkse begroting tevens een opgaaf te verstrekken van de investeringen die de onderneming de komende jaren wil verrichten. Op een bepaald moment zal het management een samenvattend overzicht willen hebben van de investeringen, afschrijvingen, rente en boekwaarden over de toekomstige tijdhorizon. Het management wil snel de impact zien op de afschrijvingskosten en rente als bepaalde investeringen worden uitgesteld of eerder worden ingepland. Voor al deze activiteiten kunt u gebruikmaken van de Rekenmodule meerjareninvesteringsplan.

Functionele eisen van een meerjareninvesteringsplan

De applicatie moet voldoen aan de volgende eisen:

• het kunnen bijhouden van een geplande investering per investeringsobject;
• het automatisch berekenen van afschrijvingen, rente en boekwaarden per afdeling;
• het automatisch bijwerken van een samenvattend overzicht per afdeling;
• het dynamisch weergeven van investeringen, afschrijvingen, rente en boekwaarden per afdeling;
• het grafisch in kaart brengen van investeringen, afschrijvingen, rente en boekwaarden;
• de applicatie moet intuïtief te bedienen zijn;
• het model moet inzichtelijk zijn;
• alle handelingen moeten via een controlepaneel te bedienen zijn;
• het model moet met behulp van geavanceerde functies en VBA-macro's kunnen worden geautomatiseerd.

De opzet van de werkmap

Rekenmodule Meerjareninvestering bevat de volgende werkbladen:

• rapportMIP;
• investeringenMIP;
• afschrijvingenMIP;
• afschrcumMIP;
• boekwaardenMIP;
• renteMIP;
• kaplastenMIP;
• termijnMIP;
• codes.

Voor de onderlinge samenhang zie het schema. X 

Het blad investeringenMIP bevat een overzicht van alle activa per object waarin alle relevante gegevens worden geregistreerd. Als zodanig vormt dit blad de database van alle activarecords. De overige bladen, te weten afschrijvingenMIP, afschrcumMIP, boekwaardenMIP, renteMIP, kaplastenMIP en termijnMIP, worden automatisch door de macro aangevuld en bijgewerkt. Het werkblad codes bevat de noodzakelijke parameters. Alle hier genoemde bladen vormen de input voor het dynamische rapport dat in een apart werkblad wordt vastgehouden.

De benodigde gegevens voor de investeringen

In het werkblad investeringenMIP worden per activum/object de volgende gegevens bijgehouden:

• objectnummer: dit kan een oplopend uniek nummer zijn;
• omschrijving activum/object: een duidelijke omschrijving waaruit blijkt wat voor soort activum het is. Zo nodig aanvullen met specifieke kenmerken zoals kentekennummer, garantienummer, locatie en dergelijke;
• categorie: type investering, bijvoorbeeld: gebouwen, inventaris, auto's en dergelijke;
• afschrijvingstermijn: het aantal jaren waarover de investering uitgesmeerd wordt;
• afdeling: de betreffende afdeling waarvoor het object bestemd is;
• begroot bedrag: het bedrag in duizenden euro's;
• startjaar: het jaar waarin het investeringsobject gepland is om aangeschaft te worden;
• offset: is een hulpkolom en hoeft niet door de gebruiker te worden ingevuld, wordt door de macro aangevuld;
• kolommen voor de diverse jaren: ook deze kolommen hoeven niet door de gebruiker te worden ingevuld; ze worden door de macro automatisch ingevuld. Als een object als startjaar 2007 heeft, zal de macro het bijbehorende bedrag in de kolom van 2007 plaatsen.

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

De gebruiker hoeft dus alleen kolommen A tot en met G in te vullen; de rest wordt later door de macro automatisch aangevuld.

Verder komen in het blad nog formules voor in de cellen I1 tot en met U1. De formules die hier bovenaan in het werkblad gepositioneerd zijn, worden later door de macro naar de ondergelegen records gekopieerd en omgezet naar waarden. Hierdoor wordt geheugenruimte bespaard. We lichten de formules verderop afzonderlijk toe.

De afschrijvingen per object per jaar

Het blad afschrijvingenMIP bevat de afschrijvingen per object per jaar en heeft dezelfde indeling als het blad investeringenMIP.

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

Dit blad wordt grotendeels door de macro gecompleteerd. De enige zaken die we van tevoren hebben ingevoerd, zijn de titelhoofden in rij 2 en deze hebben we in feite gekopieerd van het blad investeringenMIP. Verder bevat dit blad nog specifieke formules die we verderop toelichten.

De cumulatieve afschrijvingen in beeld

Het blad afschrcumMIP bevat de cumulatieve waarden van de afschrijvingen en de indeling is analoog aan het blad afschrijvingenMIP.

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

Dit blad wordt grotendeels door de macro gecompleteerd. De enige zaken die van tevoren zijn ingevoerd, zijn de titelhoofden in rij 2 en deze zijn in feite gekopieerd van het blad investeringenMIP. Verder bevat dit blad nog specifieke formules, de toelichting volgt.

De boekwaarden

Het werkblad boekwaardenMIP geeft een overzicht van de boekwaarden en de indeling is analoog aan het blad afschrijvingenMIP.

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

Dit blad wordt grotendeels door de macro gecompleteerd. De enige zaken die van tevoren zijn ingevoerd, zijn de titelhoofden in rij 2 en deze zijn in feite gekopieerd van het blad investeringenMIP. Verder bevat dit blad nog specifieke formules, de toelichting volgt.

De kapitaallasten per object per jaar

Het werkblad kaplastenMIP geeft inzicht in de som van rente en afschrijvingen per object per jaar. De indeling is analoog aan het blad afschrijvingenMIP.

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

Dit blad wordt grotendeels door de macro gecompleteerd. De enige zaken die van tevoren zijn ingevoerd, zijn de titelhoofden in rij 2 en deze zijn in feite gekopieerd van het blad investeringenMIP. Verder bevat dit blad nog specifieke formules, de toelichting volgt.

De termijn per object per jaar

In het blad termijnMIP kunt u per object en per jaar zien welke objecten meegenomen worden voor de berekening. De indeling is analoog aan het blad afschrijvingenMIP.

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

Dit blad wordt grotendeels door de macro gecompleteerd. De enige zaken die van tevoren zijn ingevoerd, zijn de titelhoofden in rij 2 en deze zijn in feite gekopieerd van het blad investeringenMIP. Verder bevat dit blad nog specifieke formules, de toelichting volgt.

De rente per object per jaar

Het blad renteMIP berekent de rente over de boekwaarde per object per jaar. De indeling is analoog aan het blad afschrijvingenMIP.

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

Dit blad wordt grotendeels door de macro gecompleteerd. De enige zaken die van tevoren zijn ingevoerd, zijn de titelhoofden in rij 2 en deze zijn in feite gekopieerd van het blad investeringenMIP. Verder bevat dit blad nog specifieke formules, de toelichting volgt.

De codes

Het blad codes bevat allerlei parameters die van belang zijn voor de calculaties en die gebruikt worden door de macro's.

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

In dit blad komen de volgende formules voor:

[Bekijk grotere afbeelding in apart venster]

Het aanmaken van bereiknamen

Voor de bereiknamen die u in de applicatie dient aan te maken klik hier. X 

CalcAfschrcum	=afschrcumMIP!$A$3:$U$500
CalcAfschrijvingen	=afschrijvingenMIP!$A$3:$U$500
CalcBoekwaarden	=boekwaardenMIP!$A$3:$U$500
CalcKaplasten	=kaplastenMIP!$A$3:$U$500
CalcRente	=renteMIP!$A$3:$U$500
CalcTermijn	=termijnMIP!$A$3:$U$500
FormuleAfschrijvingen	=afschrijvingenMIP!$J$1:$U$1
FormuleAfschrijvingenCum	=afschrcumMIP!$J$1:$U$1
FormuleBoekwaarden	=boekwaardenMIP!$J$1:$U$1
FormuleInvestering	=investeringenMIP!$J$1:$U$1
FormuleKapitaallasten	=kaplastenMIP!$J$1:$U$1
FormuleRente	=renteMIP!$J$1:$U$1
LijstAantalJaren	=codes!$D$1:$D$10
LijstJaren	=codes!$A$1:$A$13
LijstTabs	=codes!$G$1:$H$5
RekenJaar	=codes!$C$1
rente	=rapportMIP!$I$2
Selectie	=codes!$J$1
TellerAantalJaren	=codes!$E$1
TellerJaren	=codes!$B$1
TellerTabs	=codes!$I$1
VoorwaardelijkeOpmaakFormule	=codes!$A$16

Dit gaat via de menuoptie Invoegen – Naam – Definiëren.

Het rapport, de uiteindelijke output

Het blad rapportMIP bevat de uiteindelijke output met tabellen en een dynamische grafiek.

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

Zoals u ziet bevat dit scherm twee rapporten en een dynamische grafiek. Bovenaan kunt u de rentevoet over het geïnvesteerde vermogen invoeren.

In het eerste rapport Meerjaren Investeringsplan 200x-200n kunt u per afdeling de totalen van investering, afschrijving, rente, kapitaallasten en boekwaarde zien. U kunt een nieuwe view van het rapport zien door met behulp van de keuzelijst boven aan het rapport een andere tijdshorizon te kiezen. Het rapport zal zich direct aanpassen. Als u kiest voor bijvoorbeeld het aantal 4, dan zult u uitgaande van boekjaar 2006 vier jaren in het rapport te zien krijgen, te weten 2006, 2007, 2008 en 2009. Daarom zullen de diverse titelhoofden als tijdhorizon 2006-2009 te zien geven.

Als u een nieuwe tijdhorizon hebt geselecteerd aan de hand van de keuzelijst, zal de macro automatisch de berekeningen in de achtergrond uitvoeren en ziet u het dialoogvenster 'Klaar met bijwerken'. X 

Klik op OK om te bevestigen.

Het tweede rapport Meerjaren Investeringsplan Jaarschijven toont de totalen per afdeling en per jaar. Dit rapport werkt in samenhang met de getoonde grafiek rechts bovenaan en de tweede keuzelijst waar u kunt kiezen tussen:

• investeringen;
• afschrijvingen;
• rente;
• kapitaallasten;
• boekwaarden.

Zodra u een keuze hebt gemaakt, past het rapport zich ogenblikkelijk aan.

Als u in het blad investeringenMIP aanpassingen doorvoert voor bepaalde objecten met betrekking tot startjaar en/of investeringsbedragen, kunt u de impact hiervan direct zien in het rapport door op de knop Update te klikken. De macro zal in de achtergrond de berekeningen doorvoeren. Als de macro klaar is, ziet u het dialoogvenster 'Klaar met bijwerken'. X 

Klik op OK om door te gaan.

Het eerste rapport maakt veelvuldig gebruik van de functie SOM.ALS om gegevens uit het juiste bronbestand te sommeren per afdeling. Om de titelhoofden dynamisch te maken, wordt gebruikgemaakt van een tekstfunctie in combinatie met de functie INDEX.

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

Door een slim gebruik van absolute en gemengde referenties in de formules, kunt u snel een basisformule aanmaken in cel E8 en die naar beneden kopiëren, maar ook naar de kolommen F tot en met G. Alleen moet u in die kolommen met behulp van zoeken en vervangen de naam van het blad vervangen door het corresponderende veld.

De algemene titel in cel B4 bevat de volgende formule:

="Meerjaren Investeringsplan "&investeringenMIP!J2&"-"&INDEX(LijstJaren;TellerAantalJaren)

Voor de formules in het tweede rapport 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]

Het maken van de grafiek

Maak een hulpgebied. Voor de inhoud klik hier. X 

Markeer dan het gebied K10 tot en met L18 en roep de Wizard Grafieken op. Kies de volgende grafieksoort. X 

Klik op Volgende om door te gaan. Voor het volgende scherm klik hier. X 

Klik op Volgende om door te gaan. Voor het volgende scherm klik hier. X 

Vul de waarden in zoals in het vorige scherm en klik op Volgende om door te gaan. Voor het volgende scherm klik hier. X 

Klik op Voltooien en plaats de grafiek boven op het hulpgebied dat u aan het begin hebt aangemaakt.

Verwijder de legende Reeks1 en maak de grafiek op qua lay-out en kleuren. Maak de titel dynamisch door met de muis naar de grafiektitel te gaan en daar een keer op te klikken. Ga naar de formulebalk en voer daar een =- teken in en wijs met de muis naar cel K7. U ziet dat de volgende formule wordt gevormd: =rapportMIP!$K$7. Klik op Enter om te bevestigen. Vanaf nu is de titel dynamisch geworden. Telkens als de tweede keuzelijst verandert, verandert de titel mee. Ten slotte plaatst u de grafiek direct naast het eerste rapport en boven het tweede rapport.

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

Werken met de keuzelijsten

De twee keuzelijsten die u in het rapport ziet verschijnen, zijn gemaakt met de menuoptie Beeld – Werkbalken – Formulieren.

Voor een voorbeeld klik hier. X 

Als u op het pictogram Keuzelijst met invoervak klikt, verschijnt op het werkblad een kruisje. Door de linkermuisknop ingedrukt te houden, kunt u op het werkblad het symbool tekenen. Klik vervolgens met de rechtermuisknop op het symbool en uit het dan verschijnende snelmenu kiest u de optie Besturingselement opmaken. Voor de instellingen voor de eerste keuzelijst klik hier. X 

Voor de instellingen voor de tweede keuzelijst klik hier. X 

De formules in de subbladen

Klik op een link voor een toelichting op de formules in de volgende werkbladen:

• investeringenMIP; X 

  Investeringen

  I1	=SOM(VERSCHUIVING(J1;0;0;1;TellerAantalJaren))
  J1	=ALS($G1=J$2;$F1;0)
  K1	=ALS($G1=K$2;$F1;0)
  L1	=ALS($G1=L$2;$F1;0)
  M1	=ALS($G1=M$2;$F1;0)
  N1	=ALS($G1=N$2;$F1;0)
  O1	=ALS($G1=O$2;$F1;0)
  P1	=ALS($G1=P$2;$F1;0)
  Q1	=ALS($G1=Q$2;$F1;0)
  R1	=ALS($G1=R$2;$F1;0)
  S1	=ALS($G1=S$2;$F1;0)
  T1	=ALS($G1=T$2;$F1;0)
  U1	=ALS($G1=U$2;$F1;0)

  Dus in feite is de formule in cel J1 doorgekopieerd naar de cellen K1 tot en met U1.

• afschrijvingenMIP; X 

  Afschrijvingen

  I1	=SOM(VERSCHUIVING(J1;0;0;1;TellerAantalJaren))
  J1	=termijnMIP!J1*afschrijvingenMIP!$F1/afschrijvingenMIP!$D1

  Deze formule wordt doorgekopieerd naar de cellen K1 tot en met U1.

• afschrcumMIP; X 

  Cumulatieve afschrijvingen

  I1	=VERSCHUIVING(I1;0;TellerAantalJaren)
  J1	=SOM(afschrijvingenMIP!$J1:J1)*termijnMIP!J1

  Deze formule wordt doorgekopieerd naar de cellen K1 tot en met U1.

• boekwaardeMIP; X 

  Boekwaarden

  I1	=VERSCHUIVING(I1;0;TellerAantalJaren)
  J1	=investeringenMIP!$I1-afschrcumMIP!J1

  Deze formule wordt doorgekopieerd naar de cellen K1 tot en met U1.

• renteMIP; X 

  Rente

  I1	=SOM(VERSCHUIVING(J1;0;0;1;TellerAantalJaren))
  J1	=rente*boekwaardenMIP!J1

  Deze formule wordt doorgekopieerd naar de cellen K1 tot en met U1.

• kaplastenMIP; X 

  Kapitaallasten

  I1	=SOM(VERSCHUIVING(J1;0;0;1;TellerAantalJaren))
  J1	=afschrijvingenMIP!J1+renteMIP!J1

  Deze formule wordt doorgekopieerd naar de cellen K1 tot en met U1.

• termijnMIP. X 

  Termijn

  I1	Bevat geen formule
  J1	=ALS((J$2)<$G1;0;ALS((J$2)<($G1+$D1);1;0))

  Deze formule wordt doorgekopieerd naar de cellen K1 tot en met U1.

Zoals u eerder hebt gezien, is de indeling van deze bladen vrijwel gelijk aan elkaar.

De toegelichte formules bevinden zich steeds in het bereik I1 tot en met U1. De macro zorgt ervoor dat naderhand de formule naar beneden wordt gekopieerd zo lang er actieve records zijn in kolom F.

Toelichting op de gebruikte macro's

U kunt de macro's bekijken door op Alt+F11 te klikken. Hierdoor komt u terecht bij de Visual Basic Editor. Als u in het projectvenster aan de linkerkant op Modules klikt en dan op MIP, ziet u de macrocode verschijnen.

Voor de hoofdmacro CompleteMIP klik hier. X 

Sub CompleteMIP()
    Application.ScreenUpdating = False
    ClearFormerCalc
    CopyInput
    CompleteCalculation
    FormatSheets
    Sheets("rapportMIP").Select
    Application.ScreenUpdating = True
    MsgBox ("Klaar met bijwerken")
End Sub

.

De macro schakelt alle vensteractiviteiten uit en roept de volgende subroutines op:

• ClearFormerCalc; X 

  Sub ClearFormerCalc()
      Range("CalcAfschrijvingen").ClearContents
      Range("CalcAfschrcum").ClearContents
      Range("CalcBoekwaarden").ClearContents
      Range("CalcRente").ClearContents
      Range("CalcKaplasten").ClearContents
      Range("CalcTermijn").ClearContents
  End Sub

  Met behulp van deze macro worden de voormalige gegevens van diverse bladen eerst gewist. Zoals u ziet, maakt deze macro gebruik van verschillende bereiknamen die we eerder gedefinieerd hebben.

• CopyInput; X 

  Sub CopyInput()
      Sheets("investeringenMIP").Range("A2").CurrentRegion.Copy
      Sheets("afschrijvingenMIP").Paste Destination:=Sheets("afschrijvingenMIP").Range("A2")
      Sheets("afschrcumMIP").Paste Destination:=Sheets("afschrcumMIP").Range("A2")
      Sheets("boekwaardenMIP").Paste Destination:=Sheets("boekwaardenMIP").Range("A2")
      Sheets("renteMIP").Paste Destination:=Sheets("renteMIP").Range("A2")
      Sheets("kaplastenMIP").Paste Destination:=Sheets("kaplastenMIP").Range("A2")
      Sheets("termijnMIP").Paste Destination:=Sheets("termijnMIP").Range("A2")
  End Sub

  De macro kopieert de gegevens van het investeringsblad naar de overige werkbladen. Dit betreft dus alle gegevens in de kolommen A tot en met G.

• CompleteCalculation; X 

  Sub CompleteCalculation()
      CopyFormulas Sheets("investeringenMIP")
      CopyFormulas Sheets("termijnMIP")
      CopyFormulas Sheets("afschrijvingenMIP")
      CopyFormulas Sheets("afschrcumMIP")
      CopyFormulas Sheets("boekwaardenMIP")
      CopyFormulas Sheets("renteMIP")
      CopyFormulas Sheets("kaplastenMIP")
  End Sub

  Deze macro kopieert de formules (I1 tot en met U1) in de diverse werkbladen naar beneden zo lang er bedragen genoemd staan in kolom F. Dit geschiedt met de subroutine CopyFormulas:

  Sub CopyFormulas(oSh As Worksheet)
      Dim oSourceRange As Range
      Dim oTargetRange As Range
       With oSh
          Set oSourceRange = .Range("I1:U1")
          Set oTargetRange = .Range(.Range("I3"), .Range("F2").End(xlDown).Offset(0, 15))
           oSourceRange.Copy Destination:=oTargetRange
          oTargetRange.Formula = oTargetRange.Value
      End With
  End Sub

• FormatSheets. X 

  Sub FormatSheets()
      With Sheets("investeringenMIP")
          .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 35
      End With
      With Sheets("afschrijvingenMIP")
          .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              
  ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 19
      End With
      With Sheets("afschrcumMIP")
          .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 38
      End With
      With Sheets("boekwaardenMIP")
          .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 20
      End With
      With Sheets("renteMIP")
          .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 40
      End With
      With Sheets("kaplastenMIP")
         .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 24
      End With
      With Sheets("termijnMIP")
          .Range("A3:U500").FormatConditions.Delete
          .Range("A3:U500").FormatConditions.Add Type:=xlExpression, Formula1:= _
              ThisWorkbook.Names("VoorwaardelijkeOpmaakFormule")
  .RefersToRange.FormulaLocal
          .Range("A3:U500").FormatConditions(1).Interior.ColorIndex = 43
      End With
  End Sub

  De werkbladen worden voorzien van speciale opmaak (afwisselende kleuren) waardoor het geheel leesbaarder wordt.

Vervolgens keert de macro terug naar het rapport en toont het meldingsvenster.

De macro maakt gebruik van de menuoptie Opmaak – Voorwaardelijke opmaak. Verder verwijst de macro naar het bereik VoorwaardelijkeOpmaakFormule. Hier staat de volgende formule: MOD(ROW(),2)=0.

De hoofdmacro CompleteMIP is verbonden aan de knop Update in het rapport en de keuzelijst waarmee de jaren geselecteerd kunnen worden.

Als u met de rechtermuisknop op een van deze symbolen klikt, kiest u uit het snelmenu Macro toewijzen. Selecteer dan de hoofdmacro CompleteMIP.

Voor een voorbeeld klik hier. X 

Klik dan op OK om te bevestigen.

De meerjareninvesteringsplannen snel vergelijken

Het bijhouden en beheren van meerjareninvesteringsplannen (MIP's) is een belangrijke taak voor planning en control. Hierbij is men vooral gericht op het kunnen vergelijken van verschillende MIP-scenario's en de impact daarvan op afschrijvingskosten en kapitaallasten in de loop der jaren. In het verleden kostte het veel moeite om deze informatie naar boven te halen, omdat de gegevens ontbraken. Bovendien was het een nogal tijdrovende klus. Met behulp van het hier beschreven Excel-programma is het echter zeer eenvoudig en snel uit te voeren.

In dit vervolg op de werkbladen Rekenmodule Meerjareninvestering wordt beschreven hoe u de applicatie kunt bedienen om snel de MIP-bestanden met elkaar te vergelijken.

De eigenschappen van de applicatie

MIP-gegevens vergelijken (werkbladen Rekenmodule Meerjareninvestering vergelijken) is een Excel-applicatie waarmee u snel de verschillende scenario's van MIP-bestanden die aangemaakt zijn met de MIP-sjablonen met elkaar kunt vergelijken en kunt zien wat de impact is op afschrijvingskosten en kapitaallasten in de toekomstige jaarschijven.

De applicatie heeft de volgende kenmerken:

De applicatie is geschreven met de programmeertaal Visual Basic for Applications (VBA).

De procesbeschrijving

Deze Excel-applicatie maakt gebruikt van de MIP-bestanden die aangemaakt zijn met behulp van de werkbladen Rekenmodule Meerjareninvestering vergelijken, een MIP-rekenmodel. In het rekenmodel kunt u de investeringen per object bijhouden waarna automatisch de afschrijvingen en kapitaallasten over de jaren worden berekend. Door het startjaar van een of meer objecten te veranderen kunt u meteen berekenen en aanschouwen wat de impact is op afschrijvingen en kapitaallasten in de komende jaren. Door het bestand onder een andere naam op te slaan, creëert u zo een reeks van verschillende MIP-bestanden met verschillende scenario's. Voor een schematische weergave van het programma klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Het controlepaneel in beeld

Voor het openingsscherm klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Vanuit dit controlepaneel worden alle handelingen verricht.

De MIP-bestanden inlezen

Om de verschillende MIP-bestanden met elkaar te kunnen vergelijken, moet u eerst de volledige bestandsnamen inclusief padnamen invoeren in het controlepaneel. X 
[Bekijk grotere afbeelding in apart venster]

Zorg ervoor dat de bestandsnamen eindigen met xls. Als u dat vergeet, krijgt u een waarschuwing. X 
[Bekijk grotere afbeelding in apart venster]

Overigens hoeven de bestanden niet in dezelfde map te worden opgeslagen. Let er wel op dat u het MIP-bestand van een latere datum onder het hoofdtijdstip t₁ invoert.

Als u dit gedaan hebt, kunt u klikken op de knop MIP-bestanden importeren X .

U ziet dan de tekst 'Even geduld a.u.b. MIP-bestanden worden ingelezen'. Voor een voorbeeldweergave klik hier. X 

Als u klaar bent, krijgt u de melding 'Klaar met importeren! X 

Klik op OK om door te gaan.

De grafieken bekijken

Nadat u de MIP-gegevens hebt geïmporteerd, kunt u direct de impact zien in de bijbehorende grafiek. U kunt de gewenste grafieksoort selecteren door te klikken op een van de keuzerondjes. X 

Vervolgens kunt u de jaarschijven kiezen door die te selecteren in de keuzelijsten. X 

De grafiek wordt dan automatisch aangepast. Naast de grafische afbeelding ziet u ook de totalen van de betreffende selectie. X 

Het bedrag 'Totaal generaal' geeft de totalen weer ongeacht de tijdsperioden. 'Totaal van ... t/m ...' geeft de totalen weer binnen de gekozen jaren.

Rapport verschoven items

Om een rapport aan te maken klikt u op de knop Rapport verschoven items. X 

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

Aan de hand van dit rapport kunt u per investeringsobject zien naar welk jaar de investering is opgeschoven. In het voorbeeld ziet u bij objectnr 400001 dat de investering van 175.000 euro verschoven is van 2006 naar 2007.

Als u op de knop Menu klikt, keert u terug naar het controlepaneel.

De opzet van de werkmap

De werkmap bevat de volgende bladen:

De bereiknamen van de werkmap

De werkmap bevat 25 bereiknamen. X 

Bereiknaam	Bereik
ChartDeltaInvest	=OFFSET(DeltaInvest,VanJaar-1,0):OFFSET(DeltaInvest,TmJaar-1,0)
ChartYears	=OFFSET(StartJaar,VanJaar-1,0):OFFSET(StartJaar,TmJaar-1,0)
Criterium	=Lijst!$F$1
CritV_Items	=Lijst!$A$7:$A$8
DeltaInvest	=Data!$E$2
FileMIP0	=Info!$D$7
FileMIP1	=Info!$D$9
LijstCriterium	=Lijst!$A$1:$C$4
LijstJaren	=Data!$B$2:$B$19
OutputV_Items	=V_Items!$B$3:$T$3
Print_Area	=Schema!$A$1:$O$31
StartAfschr0	=Afschr0!$A$2
StartAfschr1	=Afschr1!$A$2
StartBW0	=Boekw0!$A$2
StartBW1	=Boekw1!$A$2
StartData0	=Data!$C$2
StartData1	=Data!$D$2
StartInvest0	=Invest0!$A$2
StartInvest1	=Invest1!$A$2
StartJaar	=Data!$B$2
StartRente0	=Rente0!$A$2
StartRente1	=Rente1!$A$2
Tekst1	=Info!$C$20
TmJaar	=Info!$E$15
VanJaar	=Info!$E$13

Blad Info

Dit blad bevat keuzelijsten en keuzerondjes. Voor een voorbeeldweergave van de instellingen klik hier. X 

Van:

Tot en met:

Voor de instelling van de keuzerondjes klik hier. X 

Verder zijn de keuzerondjes door middel van een groepsvak met elkaar verbonden.

Voor de formules die in het blad zijn toegepast, klik hier. X 
[Bekijk grotere afbeelding in apart venster]

De flexibele grafiek heeft als oorsprong de volgende instelling:

=SERIES("Verschil",'620_10.xls'!ChartYears,'620_10.xls'!ChartDeltaInvest,1)

Blad V_Items

Dit blad wordt door de macro ingevuld. Voor de indeling klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Bladen Invest, Afschr, Rente en Boekw

Deze bladen zien hebben dezelfde lay-out: ze beginnen in kolom A en eindigen in kolom AD.

Blad Data

Voor de indeling en formules van dit blad klik hier. X 
[Bekijk grotere afbeelding in apart venster]

Blad Lijst

Dit blad bevat de criteria. X 

VBA-statements

In het controlepaneel is de knop MIP-bestanden importeren verbonden aan de VBA-hoofdroutine. X 

Sub ImportMIPs()
  answer = MsgBox("Doorgaan met importeren van MIP bestanden?", vbYesNo)
  If answer <> vbYes Then Exit Sub

  Sheets("Bericht").Select
  Application.ScreenUpdating = False

  InlezenMIPt0
  InlezenMIPt1

Sheets("Info").Select
  Application.ScreenUpdating = True
  MsgBox ("Klaar met importeren!")

End Sub

De hoofdroutine doorloopt twee subroutines. X 

Sub InlezenMIPt0()
  Dim a
  a = Range("FileMIP0")

  Workbooks.Open Filename:=(a)
  ActiveWorkbook.Windows(1).Caption = "MIP0"

  Sheets("investeringenMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Invest0").Select
  Range("StartInvest0").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP0").Activate

  Sheets("afschrijvingenMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Afschr0").Select
  Range("StartAfschr0").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP0").Activate

  Sheets("renteMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Rente0").Select
  Range("StartRente0").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP0").Activate

  Sheets("boekwaardenMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Boekw0").Select
  Range("StartBW0").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP0").Activate

  ActiveWorkbook.Close False
End Sub

Sub InlezenMIPt1()
  Dim b
  b = Range("FileMIP1")

  Workbooks.Open Filename:=(b)
  ActiveWorkbook.Windows(1).Caption = "MIP1"

  Sheets("investeringenMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Invest1").Select
  Range("StartInvest1").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP1").Activate

  Sheets("afschrijvingenMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Afschr1").Select
  Range("StartAfschr1").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP1").Activate

  Sheets("renteMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Rente1").Select
  Range("StartRente1").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP1").Activate

  Sheets("boekwaardenMIP").Select
  Range("A3:AD500").Copy
  ThisWorkbook.Activate
  Sheets("Boekw1").Select
  Range("StartBW1").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Windows("MIP1").Activate

  ActiveWorkbook.Close False
End Sub

In het voorbeeld is uitgegaan van de veronderstelling dat:

• het bereik nooit groter zal zijn dan 500 regels; als dat wel het geval is, moet een hoger getal worden ingegeven;
• er evenveel items zijn op tijdstip t₀ als op tijdstip t₁.

De knop Rapport verschoven items activeert een macro. X 

Sub ExtractData()
  Application.ScreenUpdating = False
  Sheets("V_Items").Select
  ActiveSheet.Unprotect
  Sheets("InvestV").Columns("A:AD").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("CritV_Items"), CopyToRange:=Range("OutputV_Items")
  ActiveSheet.Protect
  Application.ScreenUpdating = True
End Sub

Ook de knop Menu in het blad V_Items activeert een macro. X 

Sub Menu()
  Sheets("Info").Select
End Sub

Blad Bericht

Dit blad bevat de tekst 'Even geduld a.u.b. MIP-bestanden worden ingelezen'. Voor een voorbeeldweergave klik hier. X 

Conclusie

De Rekenmodule Meerjareninvestering laat op een snelle wijze de impact zien van de afschrijvingskosten en rente als bepaalde investeringen worden uitgesteld of eerder worden ingepland. Met deze tool kunt u het management een samenvattend overzicht overhandigen van de investeringen, afschrijvingen, rente en boekwaarden over de toekomstige horizon. Kortom, het bijhouden en beheren van meerjareninvesteringsplannen (MIP's) is een belangrijke taak voor planning en control. In het verleden kostte het veel moeite om deze informatie naar boven te halen, omdat de gegevens ontbraken. Bovendien was het een nogal tijdrovende klus. Met behulp van het beschreven Excel-programma is het echter zeer eenvoudig en snel uit te voeren.

Direct aan de slag met Investeringsselecties: werkbladen in Excel!