Financiële modellering in Excel voor Dummy's Cheat Sheet - dummies

Door Danielle Stein Fairhurst

Wanneer u financiële modellen bouwt in Microsoft Excel, zijn functies de naam van het spel. Je moet ook je werk controleren - en het opnieuw controleren - om ervoor te zorgen dat er geen fouten wegschieten. Tot slot, om uw werk snel en eenvoudig te maken, zijn sneltoetsen een redder in nood.

Essentiële Excel-functies voor het bouwen van financiële modellen

Vandaag zijn er meer dan 400 functies beschikbaar in Excel, en Microsoft blijft meer toevoegen met elke nieuwe versie van de software. Veel van deze functies zijn niet relevant voor gebruik in financiën en de meeste Excel-gebruikers gebruiken slechts een zeer klein percentage van de beschikbare functies. Als u Excel gebruikt voor financiële modellering, hebt u op zijn minst een goed begrip nodig van de meest gebruikte functies.

Hoewel er veel en veel meer zijn die u handig vindt bij het bouwen van modellen, vindt u hier een lijst met de meest elementaire functies waar u niet zonder kunt.

Functie	Wat het doet
SUM	Hiermee wordt een reeks cellen opgeteld of samengeteld.
MIN	Berekent de minimum waarde van een celbereik.
MAX	Berekent de maximum -waarde van een celbereik.
GEMIDDELD	Berekent de gemiddelde -waarde van een celbereik.
RONDE	Rondt een enkel getal af op de dichtstbijzijnde gespecificeerde waarde, meestal op een geheel getal.
ROUNDUP	Rondes maximaal een enkel getal tot op de dichtstbijzijnde opgegeven waarde, meestal tot een geheel getal.
ROUNDDOWN	Rondes omlaag een enkel getal tot op de dichtstbijzijnde opgegeven waarde, meestal tot een geheel getal.
ALS	Retourneert alleen een opgegeven waarde als aan een enkele -voorwaarde is voldaan.
IFS	Retourneert een opgegeven waarde als complexe voorwaarden zijn vervuld.
AANTAL.ALS	Telt het aantal waarden in een bereik dat aan een bepaald enkele criterium voldoet.
COUNTIFS	Telt het aantal waarden in een bereik dat voldoet aan meerdere criteria.
SUMIF	Sommeert de waarden in een bereik die aan een bepaald single -criterium voldoen.
SOMMEN	Sommeert de waarden in een bereik die voldoen aan meerdere criteria.
VLOOKUP	Zoekt een bereik op en retourneert de eerste overeenkomstige waarde in een verticale tabel die exact overeenkomt met de opgegeven invoer.
HLOOKUP	Zoekt een bereik op en retourneert de eerste overeenkomstige waarde in een horizontale tabel die exact overeenkomt met de opgegeven invoer. Er wordt een fout geretourneerd als deze de exacte overeenkomst niet kan vinden.
INDEX	Werkt als de coördinaten van een kaart en retourneert een enkele waarde op basis van de kolom- en rijnummers die u invoert in de functievelden.
MATCH	Retourneert de positie van een waarde in een kolom of rij. Modelers combineren vaak MATCH met de INDEX-functie om een opzoekfunctie te maken, die veel robuuster en flexibeler is en minder geheugen gebruikt dan VLOOKUP of HLOOKUP.
PMT	Berekent de totale jaarlijkse betaling van een lening.
IPMT	Berekent de rente component van een lening.
PPMT	Berekent het hoofdbestanddeel van een lening.
NPV	Houdt rekening met de tijdswaarde van geld door de huidige contante waarde van toekomstige kasstromen in de huidige dollars te geven, op basis van het investeringsbedrag en de disconteringsvoet.

Een goede financiële modelleur is veel meer dan alleen het kennen van veel Excel-functies. Een ervaren modeler kan kiezen welke functie het beste is om te gebruiken in welke situatie. Meestal kunt u verschillende manieren vinden om hetzelfde resultaat te bereiken, maar de beste optie is altijd de functie of oplossing die het eenvoudigst, duidelijkst en het gemakkelijkst voor anderen te begrijpen is.

Waar u op moet letten bij het controleren of controleren van een financieel model

Als u Excel al een tijdje gebruikt, wilt u waarschijnlijk liever zelf uw spreadsheets of financiële modellen bouwen. In een zakelijke omgeving krijgen mensen echter zelden deze kans. In plaats daarvan wordt van hen verwacht dat ze een bestaand model overnemen dat door iemand anders is gebouwd.

Misschien stapt u in een rol die u van iemand anders overneemt en is er een bestaand model voor financiële rapportage dat u elke maand moet bijwerken. Of u bent verteld om elk kwartaal verkoopcommissies te berekenen op basis van een monsterlijke spreadsheet met 50 tabbladen waarvan u niet echt tevreden bent. Niet alleen ervaar je de modellen van anderen samen met de inputs, aannames en berekeningen die de oorspronkelijke modelleur heeft ingevoerd, maar je ervaart ook de fouten van de modelman.

Als je de verantwoordelijkheid opneemt voor het model van iemand anders, moet je voorbereid zijn om het over te nemen en het je eigen model te maken. U moet verantwoordelijk zijn voor de werking van dit model en zeker zijn dat het correct werkt. Hier is een lijst met dingen waar u op moet letten wanneer u voor het eerst het financiële model van iemand anders ervaart:

Maak u vertrouwd met het uiterlijk en het gevoel. Kijk door elk blad om te zien welke kleurenschema's zijn gebruikt. Lees alle documentatie door. Is er een sleutel om te helpen zien welke cellen zijn welke? Heeft de modeler onderscheid gemaakt tussen formules en hard-coded aannames?
Bekijk de formules goed. Zijn ze consistent? Bevatten ze hard-gecodeerde waarden die niet automatisch worden bijgewerkt en daardoor fouten veroorzaken?
Voer een foutcontrole uit. Druk op de knop Foutcontrole in het gedeelte Formulecontrole van het tabblad Formules in het lint om in één oogopslag te zien of er Excel-fouten zijn op het werkblad die problemen kunnen veroorzaken.
Controleren op koppelingen naar externe bestanden. Externe koppelingen kunnen een geldig onderdeel zijn van het operationele werkproces, maar u moet weten of dit bestand ingangen krijgt van externe werkmappen om ervoor te zorgen dat niemand per ongeluk blad- of bestandsnamen wijzigt, waardoor er fouten in uw model ontstaan.Zoek naar externe koppelingen door op de knop Koppelingen bewerken te klikken in het gedeelte Verbindingen van het tabblad Gegevens op het lint.
Bekijk de benoemde bereiken. Benoemde bereiken kunnen nuttig zijn in een financieel model, maar ze bevatten soms fouten vanwege overbodige namen en externe links. Bekijk de benoemde bereiken in de Name Manager, die zich in het gedeelte Gedefinieerde namen van het tabblad Formules op het lint bevindt. Verwijder benoemde bereiken die fouten bevatten of niet worden gebruikt, en als ze koppelingen naar externe bestanden bevatten, let dan op en zorg ervoor dat ze nodig zijn.
Controleer automatische berekeningen. Formules moeten automatisch worden berekend, maar soms wanneer een bestand erg groot is of een modelleur de wijzigingen graag handmatig bestuurt, is de berekening ingesteld op handmatig in plaats van automatisch. Als u het woord Berekenen ziet in de statusbalk linksonder, betekent dit dat de berekening is ingesteld op handmatig, dus u staat waarschijnlijk voor een ingewikkeld onderzoek! Druk op de knop Berekeningsopties in het gedeelte Berekening van het tabblad Formules op het lint om te schakelen tussen handmatige en automatische berekening van de werkmap.

Naast deze stappen vindt u hier enkele handige controletools in Excel die u kunt gebruiken om een overgenomen model te controleren, te controleren, te valideren en indien nodig te corrigeren, zodat u zeker kunt zijn van de resultaten van uw financiële model:

Inspecteer werkmap. Leer de verborgen kenmerken van uw model kennen en identificeer potentieel problematische functies die anders erg moeilijk te vinden zouden kunnen zijn met deze weinig bekende tool. Om het te gebruiken, opent u de werkmap en klikt u op de knop Bestand op het lint; Klik op het tabblad Info op de knop Controleren op problemen.
F2: Als de broncellen van een formule zich op dezelfde pagina bevinden, plaatst de F2-sneltoets de cel in de bewerkingsmodus, dus deze snelkoppeling is een goede manier om visueel te zien waar de brongegevens vandaan komen.
Trace Precedents / Dependents: Excel-audithulpmiddelen traceren de relaties visueel met de pijlen van de tracerlijn. U kunt deze hulpprogramma's openen in het gedeelte Formulecontrole van het tabblad Formules op het lint.
Formule evalueren: Splits lange en complexe formules uit met behulp van het hulpprogramma Evalueerformule, in het gedeelte Formulecontrole van het tabblad Formules op het lint.
Foutcontrole hulpmiddelen: Als u een fout maakt - of wat Excel denkt dat een vergissing is - verschijnt er een groene driehoek in de linkerbovenhoek van de cel. Dit gebeurt als u aangrenzende cellen weglaat of als u een invoer invoert als tekst, die eruit ziet als een getal.
Kijkvenster: Als u uitvoercellen hebt die u in de gaten wilt houden, geeft dit hulpmiddel het resultaat van de opgegeven cellen weer in een afzonderlijk venster. U vindt deze tool in het gedeelte Formulecontrole van het tabblad Formules op het lint. Het is handig voor het testen van formules om de impact te zien van een verandering in aannamen op een afzonderlijke cel of cellen.
Formules weergeven: Als u alle formules in één oogopslag wilt bekijken in plaats van de resulterende waarden, drukt u op de knop Formules weergeven in het gedeelte Formulecontrole van het tabblad Formules op het lint (of gebruikt u de sneltoets Ctrl + ').Formules tonen is ook een zeer snelle en gemakkelijke manier om te zien of er hardcodedewaarden bestaan.

Excel-sneltoetsen voor Financial Modelers

Als u veel tijd besteed aan het modelleren in Excel, kunt u uzelf tijd besparen door een aantal sneltoetsen te leren. Veel van de vaardigheden van de modelmaker gaan over snelheid en nauwkeurigheid, en door deze sneltoetsen te oefenen totdat ze spiergeheugen worden, zul je een snellere en nauwkeurigere modeler zijn.

Hier volgt een lijst met de handigste sneltoetsen die deel moeten uitmaken van uw dagelijkse toetsenbordgebruik als u een financieel model bent:

Bewerken
Ctrl + S	Werkmap opslaan.
Ctrl + C	Kopiëren.
Ctrl + V	Plakken.
Ctrl + X	Knippen.
Ctrl + Z	Ongedaan maken.
Ctrl + Y	Opnieuw.
Ctrl + A	Alles selecteren.
Ctrl + R	Kopieer de uiterst linkse cel over het bereik. (U moet eerst het bereik markeren.)
Ctrl + D	Kopieer de bovenste cel omlaag in het bereik. (U moet eerst het bereik markeren.)
Ctrl + B	Vet.
Ctrl + 1	Opmaakvak.
Alt + Tab	Wissel van programma.
Alt + F4	Programma sluiten.
Ctrl + N	Nieuwe werkmap.
Shift + F11	Nieuw werkblad.
Ctrl + W	Werkblad sluiten.
Ctrl + E + L	Een blad verwijderen.
Ctrl + Tab	Van werkmap wisselen.
Navigeren door
Shift + spatiebalk	Markeer rij.
Ctrl + spatiebalk	Markeer kolom.
Ctrl + - (koppelteken)	Geselecteerde cellen verwijderen.
Pijltjestoetsen	Ga naar nieuwe cellen.
Ctrl + Pg omhoog / omlaag omlaag	Van werkblad wisselen.
Ctrl + pijltoetsen	Ga naar einde van het continu bereik en selecteer een cel.
Shift + pijltoetsen	Selecteer bereik.
Shift + Ctrl + pijltoetsen	Selecteer continu bereik.
Startpagina	Ga naar het begin van de regel.
Ctrl + Startpagina	Ga naar cel A1.
In formules
F2	Formule bewerken die cellen voor precedenten laat zien.
Alt + Enter	Start een nieuwe regel in dezelfde cel.
Shift + pijltoetsen	Markeer binnen cellen.
F4	absolute verwijzing wijzigen ("$").
Esc	Een celvermelding annuleren.
ALT + = (gelijkteken)	Som geselecteerde cellen.
F9	Herbereken alle werkmappen.
Ctrl + [	Markeer precedente cellen.
Ctrl +]	Markeer afhankelijke cellen.
F5 + Enter	Ga terug naar de originele cel.

Om de snelkoppeling voor een functie te vinden, drukt u op de Alt-toets en de sneltoetsen geven het lint weer. Als u bijvoorbeeld naar de Name Manager wilt gaan, drukt u op Alt + M + N en het dialoogvenster Name Manager verschijnt.

In de linkerbovenhoek vindt u de werkbalk Snelle toegang. U kunt de snelkoppelingen op de werkbalk Snelle toegang wijzigen door op de kleine pijl rechts van de werkbalk te klikken en te selecteren wat u wilt toevoegen in het vervolgkeuzemenu dat wordt weergegeven. Als u bijvoorbeeld Plakken speciaal toevoegt aan de werkbalk Snelle toegang, kunt u Plakken speciaal openen met de sneltoets Alt + 4. Merk op dat dit alleen werkt als de werkbalk Snelle toegang is aangepast en alles wat u op de vierde positie plaatst wordt geopend met de sneltoets Alt + 4.