Excel: bepalen van het optimale investeringsbudget

Tony De Jonker Exce Excel: Vragen en Antwoordenl

Als een bedrijf geen kapitaalrestricties (capital rationing) kent, is het vrij eenvoudig om de investeringsprojecten te selecteren die de voorkeur verdienen. In dit geval selecteren we alle projecten waarvan de interne rentevoet boven de vermogenskostenvoet ligt.

Stel we hebben het volgende overzicht met tien kapitaalprojecten en bijbehorende interne rentevoeten:

We kunnen de tabel sorteren in aflopende volgorde van de interne rentevoet, zodat de hoogste waarden bovenaan komen te staan. Dat kan in Excel met de sorterenfunctionaliteit.

Aangezien we een dynamisch model willen bouwen, laten we de oorspronkelijke tabel intact en maken een nieuwe gesorteerde tabel aan met behulp van de functies GROOTSTE (LARGE), INDEX en VERGELIJKEN (MATCH).

De volgende gegevens zijn hierbij van belang:

De oorspronkelijke tabel à A3 t/m C13
De dynamisch gesorteerde tabel à E3 t/m G13
G1=GROOTSTE($C$4:$C$13;D4)
E1 =INDEX($A$4:$A$13;VERGELIJKEN(G4;$C$4:$C$13;0);1)
F1 =SOM(E$4:E4)
De formules zijn doorgekopieerd naar beneden

De volgende stap is een mastertabel opzetten voor het bepalen van de kapitaalkosten, ook wel geheten de gemiddelde vermogenskostenvoet of weighted average cost of capital (WACC).

Deze tabel ziet er bijvoorbeeld als volgt uit:

Het totale vermogen bestaat uit:

Aandelenkapitaal (common)
Preferent aandelenkapitaal (preferred)
Vreemd vermogen (debt)

Per niveau kunnen omslagpunten (breakpoints) worden berekend:

M4=K4/$J$4
M5=K5/$J$4
M7=K7/$J$7
M9=K9/$J$9

We gaan nu een tabel maken met verschillende waarden:

Kolom O laten we steeds met 100.000 euro oplopen van nul tot 2.500.000 euro. De tabel bevat de volgende formules die naar beneden zijn doorgekopieerd:

P4=ALS(O4*$J$4<=$K$4;$L$4;ALS(O4*$J$4<=$K$5;$L$5;$L$6))
Q4=ALS(O4*$J$7<=$K$7;$L$7;$L$8)
R4=ALS(O4*$J$9<=$K$9;$L$9;$L$10)
S4=$J$4*P4+$J$7*Q4+$J$9*R4
MaxWACC (S31) =MAX(S4:S29)
Omslagpunt Cumulatieve kosten (R33) =INDEX(F4:F13;VERGELIJKEN(S31;G4:G13;-1);1

Aan de hand van de hulptabel kan de maximale WACC worden bepaald en deze kan dienen als opzoekwaarde in de gesorteerde tabel om de bijbehorende cumulatieve kosten erbij op te zoeken. Vervolgens kan dit grafisch worden uitgebeeld.

De grafiek is als volgt tot stand gekomen:

Maak eerst een spreidinggrafiek gebaseerd op de hulptabel met WACC-waarden.
Voeg de IRR-waarden toe door het markeren van F4 t/m G13 en dan uit het Excel-menu te kiezen Bewerken à Kopiëren.
Klik de grafiek aan en selecteer dan uit het menu Bewerken à Plakken Speciaal.
In het dialoogvenster kiest u de volgende instellingen:

Het optimale investeringsbudget zonder kapitaalrestricties is gelijk aan het investeringsbedrag waarbij de gemiddelde vermogenskostenvoet (WACC) het Investerings Opties Schema snijdt.

De grafiek kan verfraaid worden door het toevoegen van een wolkvormige toelichting die verbonden is met de dynamische tekstformule: =”Optimal capital budget at “&TEKST(R33;”€ #.##”)