Financiële modellen zijn een onmisbaar onderdeel van de financiële toolkit van elk bedrijf. Het zijn spreadsheets die de historische financiële gegevens van een bepaald bedrijf gedetailleerd weergeven, de toekomstige financiële prestaties voorspellen en het risico- en rendementsprofiel beoordelen. Financiële modellen zijn doorgaans gestructureerd rond de drie financiële overzichten van de boekhouding, namelijk:winst- en verliesrekening, balans en kasstroomoverzicht. Het management van de meeste bedrijven vertrouwt, althans gedeeltelijk, op de details, aannames en resultaten van financiële modellen, die allemaal van cruciaal belang zijn voor de strategische en kapitaalbesluitvormingsprocessen van genoemde bedrijven.
Dit artikel dient als een stapsgewijze handleiding voor de beginnende en intermediaire financiële professional die de best-practices van experts wil volgen bij het bouwen van financiële modellen. Voor de gevorderde financiële modelleur zal dit artikel ook een selectie van tips en hacks op expertniveau tonen om tijd, output en modelleringseffectiviteit te optimaliseren. Laten we beginnen.
Zoals met alle complexe zaken, is de eerste stap bij het bouwen van een financieel model (“model”) het zorgvuldig opmaken van een blauwdruk. Ongeplande, onverwachte structurele veranderingen halverwege een modelleringsoefening kunnen tijdrovend, verwarrend en foutgevoelig zijn, vooral als de adapter van het model niet dezelfde is als de auteur. Dergelijke uitdagingen kunnen gemakkelijk worden ondermijnd met een beetje toegewijde planningstijd aan het begin van de oefening. Ik raad je aan om je planningsfase als volgt te laten verlopen:
Het duidelijk definiëren van het doel van een model is de sleutel tot het bepalen van de optimale lay-out, structuur en eindresultaten. Neem als onderdeel van dit proces de tijd om ervoor te zorgen dat de belangrijkste belanghebbenden van uw model uw blauwdruk en procesontwerp ondertekenen voordat u begint met bouwen. Dit geeft hen de mogelijkheid om eventuele definitieve voorkeuren of intenties te uiten, waardoor elke "scope creep" (industrietaal) of pijnlijke omleiding op de weg wordt vermeden.
Hoewel het ondergeschikt is aan het einddoel van het model, is het begrijpen van de tijdlijnen voor het bouwen van het model en hoe lang het model zal worden gebruikt ook belangrijke input voor het bepalen van de benadering van de modellering. Modellen met een lange levensduur en een lange levensduur (bruikbare levensduur) worden doorgaans vanaf de basis op maat gemaakt en bevatten enorme hoeveelheden bedieningsdetails, flexibiliteit en gevoeligheidsmogelijkheden. Voor meer directe, kortere operationele of kapitaalprojectmodellen gebruiken modelbouwers vaak geprefabriceerde sjablonen om de bouwsnelheid te maximaliseren en fouten te minimaliseren. Verder zijn modelsjablonen ook bekender en dus gemakkelijker te gebruiken/manipuleren door verschillende belanghebbenden binnen organisaties.
Bij het bepalen van de optimale afweging tussen gewenst detailniveau en herbruikbaarheid van modellen (d.w.z. of het model bedoeld is om te worden herwerkt voor meerdere transactietypes/-doeleinden of in plaats daarvan is ontworpen voor slechts deze eenmalige oefening), een nuttig raamwerk om te beslissen over iemands modelkeuze/-aanpak, die ik heb gevolgd gedurende het grootste deel van mijn carrière, is als volgt:
Nu de blauwdruk/planningsfase is voltooid en de belangrijkste beslissingen zijn genomen, kunnen we nu doorgaan naar de volgende fase van modellering.
Op dit moment zijn we klaar om Excel te openen en na te denken over structurering. Op het hoogst mogelijke niveau kan/moet elk model worden onderverdeeld in drie secties:(a) inputs/drivers, (b) berekeningen (geprojecteerde financiële overzichten) en (c) outputs. Hoe beter men is in het scheiden van deze secties, hoe gemakkelijker het zal zijn om het model te controleren en aan te passen, terwijl fouten worden geminimaliseerd en op tijd geoptimaliseerd.
Ik heb dezelfde structurele aanpak gevolgd voor bijna elk model dat ik heb gebouwd; een aanpak die zowel mijn respectieve belanghebbenden als ik altijd praktisch, verteerbaar en uiteindelijk nuttig hebben gevonden. De secties zijn als volgt:
Ik zal elk van deze secties voor je opsplitsen, één voor één. Als volgt:
De voorpagina is het eerste aanspreekpunt voor uw werk. Hoewel het de eenvoudigste is om te bouwen, laat het, als het goed wordt gedaan, een geweldige eerste indruk achter en legt het duidelijk uit wat er gaat komen. Een eenvoudige, instructieve voorpagina is over het algemeen de beste benadering en omvat doorgaans de volgende secties:
Let op:ik raad aan om de voorpagina altijd te vergrendelen voor iedereen zonder uitdrukkelijke toestemming om wijzigingen aan te brengen, buiten de auteur om.
Direct na het voorblad van het model moet het tabblad stuurprogramma's (invoer) komen . U moet ervoor zorgen dat dit tabblad duidelijk, beknopt en gemakkelijk te begrijpen is, aangezien dit het tabblad is dat niet-financiële operators waarschijnlijk het vaakst zullen manipuleren. Ik raad meestal aan om twee invoersecties op het invoertabblad te implementeren, één voor statisch inputs en de andere voor dynamisch . Door statische invoer Ik bedoel inputs die in de loop van de tijd niet veranderen, zoals de hypothetische "omvang van een elektriciteitscentrale" of "het beginschuldsaldo van een bedrijf"; en door dynamische invoer , ik bedoel inputs die in de loop van de tijd variabel zijn (bijvoorbeeld van maand tot maand of van jaar tot jaar), zoals 'inflatie'-aannames, 'kosten van schulden' of 'omzetgroei'-aannames.
Voorbeeld van een voorbeeld van het tabblad Drivers en veronderstellingen
Binnen beide bovenstaande statische vs. dynamisch invoersecties, raad ik u aan om uw gegevens ook duidelijk in twee soorten te scheiden:(1) hardgecodeerde cijfers die niet veranderen, ongeacht het aannamescenario, en (b) sensibiliserende parameters die verschillende aannamescenario's en uiteindelijk uw gevoeligheidstabellen zullen aansturen . Houd er echter rekening mee dat u nooit volledig weet welke parameters gevoeligheidsparameters zullen vormen en welke u pas in de laatste fasen van het project zult weten. Raadpleeg het volgende artikel voor meer informatie over gevoeligheidsmodellering.
Dit tabblad vertegenwoordigt het hart van het model, waar alle input, aannames en scenario's samenwerken om de financiële prestaties van een bedrijf in de laatste jaren te projecteren. Het is ook buiten dit tabblad dat verschillende op aannames gebaseerde scenario's worden uitgevoerd, evenals het waarderingsdeel van de oefening die zal worden uitgevoerd voorafgaand aan de definitieve strategische beslissing.
Voorbeeld van een voorbeeldmodeltabblad
Geautoriseerde, externe modeloperators gebruiken de Scenario's en gevoeligheden tab vrij vaak, al was het maar om hun keuze uit voorgeprogrammeerde scenario's te selecteren. Om deze reden moet u intuïtief scenario's bouwen, de werkelijke scenario's beschermen tegen externe bewerkingen en voldoende gevarieerde gevoeligheden bouwen zodat het handjevol voorgeprogrammeerde scenario's voldoende zal zijn om een breed beeld van mogelijke resultaten te geven zodra gevoeligheidstabellen (voorbeeld hieronder) zijn ook gebouwd.
Ter overweging, de indeling van de scenario's waarop ik tijdens mijn carrière heb vertrouwd, is als volgt, als slechts één type voorbeeld:
De uitvoer tabbladen zijn de tabbladen die operators van het model het vaakst zullen gebruiken. In de loop der jaren heb ik gemerkt dat ik neig naar ten minste drie uitvoertabs voor middelgrote tot complexe modellen:
Op dit moment is de bouwfase van het model officieel voltooid. We kunnen onze aandacht richten op enkele van de beste praktijken voor modellering op expertniveau waarnaar ik aan het begin van het artikel verwees. Laten we beginnen met formatteren.
Ten eerste is het belangrijk op te merken dat elk bedrijf/groep zijn eigen voorkeuren of interne praktijken kan hebben. Daarom is het tijdens het bouwen belangrijk om eerst in te checken met - en zich te houden aan - het formaat dat uw respectieve bedrijf voorschrijft. Bij gebrek aan bedrijfsspecifieke praktijken, beschrijft de onderstaande inhoud echter de universele taal van Wall Street voor het opmaken van een model.
De eerste en laagst hangende opmaakmethode voor financiële modellering is het gebruik van consistente en herkenbare kleurenschema's om verschillende soorten cellen en gegevens aan te duiden. Als volgt:
Blauw =invoer of hardgecodeerde gegevens, zoals historische waarden, aannames en stuurprogramma's.
Zwart =Formules, berekeningen of verwijzingen die uit hetzelfde blad komen.
Groen =formules, berekeningen en verwijzingen naar andere bladen (houd er echter rekening mee dat sommige modellen deze stap helemaal overslaan en zwart gebruiken voor deze cellen).
Paars =Koppelingen, invoer, formules, verwijzingen of berekeningen naar andere Excel-bestanden (merk nogmaals op dat sommige modellen deze stap helemaal overslaan en ook zwart gebruiken voor deze cellen).
Rood =fout die moet worden opgelost.
Voorbeeld van goed opgemaakte (kleurgecodeerde) financiële samenvatting
Houd er rekening mee dat er geen ingebouwde automatiseringsfunctionaliteit is om uw Excel-spreadsheets te kleuren volgens de bovenstaande universele kleurcoderingsnormen. In plaats daarvan kunt u uw eigen macro('s) ontwerpen om deze resultaten te bereiken en vervolgens snelkoppelingen maken om uw werk automatisch van een kleurcode te voorzien.
Ergens in mijn recente verleden ontving ik van een collega (die ik tot op de dag van vandaag bedank), de volgende macro's (inclusief gedetailleerde instructies), die me sindsdien enkele uren handwerk hebben bespaard. Ik wil ze graag delen, als dat mag.
Instructies voor het maken van macro's (voor zowel Mac- als pc-versies van Excel):
Het vinden van koppelingen naar andere werkmappen en werkbladen is lastig en u zult hoogstwaarschijnlijk VBA moeten gebruiken om dit correct te laten werken. Hier is het basisidee:zoek naar de aanwezigheid van het symbool "!" in elke cel die een formule in uw werkmap bevat en verander vervolgens de letterkleur in groen. U moet dit wijzigen in de VBA-editor en er een for each van maken doorloop alle instanties van "!" u vindt en wijzig vervolgens de letterkleur voor elk van deze.
Houd er rekening mee dat deze snelkoppeling nog steeds niet 100% van de tijd werkt, omdat sommige formules verwijzen naar cellen in andere werkbladen zonder er rechtstreeks naar te linken. Gelukkig zijn groene cellen zeldzamer dan zwarte of blauwe cellen, dus de bovenstaande methode werkt redelijk goed in de meeste modellen (en je kunt de rest van je links naar andere werkbladen handmatig opmaken als ze verschijnen of als je ze tegenkomt).
Ik moedig je aan om bij het modelleren altijd deze ene vraag in je achterhoofd te houden:"Maak ik dit model gemakkelijk controleerbaar?" want voor elke uitgevoerde taak, gecreëerde formule en gebouwde link, zal er altijd een snellere, "vuilere" (in het vakjargon) manier zijn om het werk te doen. Dergelijke hacks en trucs, hoe slim ze op dat moment ook lijken, en vooral na tijdsintervallen, zullen onveranderlijk worden vergeten en leiden tot moeilijk op te sporen fouten. Als u een externe reviewer in gedachten houdt, wordt u door uw proces geleid en kunt u op belangrijke momenten de juiste beslissing nemen.
Hieronder vindt u een reeks best practices voor het bouwen met een auditor-mindset. Als volgt:
U zou slechts één formule per rij moeten hebben, wat betekent dat de formule die in de eerste cel van een bepaalde rij wordt gebruikt, dezelfde formule moet zijn die uniform over de hele rij wordt toegepast. Gebruikers moeten de structuur van uw model begrijpen door naar de eerste cel van elke rij te kijken terwijl ze verticaal naar beneden gaan in uw model.
Hoewel dit in principe eenvoudig is, wordt het vaak genoeg geschonden om verder te benadrukken. Een veelvoorkomend voorbeeld vindt plaats wanneer spreadsheets worden opgesplitst in een groep "historische financiële gegevens" en "prognoses voor het komende jaar" (zie afbeelding hierboven getiteld "Voorbeeld van goed opgemaakte (kleurgecodeerde) samenvatting van financiële gegevens", als referentie ).
Een gemakkelijke manier om deze gevallen aan te pakken, is het gebruik van vlaggen (bijv. 1/0, TRUE /FALSE ) bovenaan de spreadsheet geplaatst en vervolgens verwezen met IF uitspraken door het lichaam van iemands model. Een eenvoudige illustratie hiervan op het werk is als volgt:
Gebruik nooit hardgecodeerde getallen die zijn ingesloten in formules, omdat ze erg moeilijk te herkennen zijn als de gebruiker minder bekend is met het model. Markeer en scheid in plaats daarvan de invoer/hardcodes duidelijk van de formules; beter nog, verzamel alle invoer/hard-codes (indien van toepassing) en aggregeer ze op hetzelfde tabblad. Laat uw formules vervolgens naar wens uit de gewenste cel en uit het juiste tabblad halen/verwijzen.
Het is altijd beter om ingewikkelde formules te vermijden. Verdeel uw formule in plaats daarvan in licht verteerbare stappen. In plaats van één ogenschijnlijk nette rij, zal deze aanpak vaak veel meer rijen creëren, wat resulteert in een grotere spreadsheet; maar een die veel gemakkelijker te volgen en te controleren is door een derde partij.
U moet op tijd nul beslissen wat uw tekenconventie/sleutel zal zijn. Vraag uzelf bij wijze van illustratie af in de ontwerpfase van uw model:"Zullen kosten, uitgaven, inhoudingen, afschrijvingen, CapEx, enz. worden weergegeven als negatieve of positieve getallen?" Mijn persoonlijke voorkeur is om kosten altijd als negatieve getallen weer te geven om twee redenen:(a) de totalen zijn altijd rechte sommen en u minimaliseert gebruikersfouten, en (b) het zal gemakkelijker zijn om fouten op te sporen door alleen de tekens te gebruiken.
Waar mogelijk raad ik ten zeerste aan om uw cellen geen naam te geven, omdat het moeilijk wordt om de broninvoer voor de genoemde cel (bijv. "Inflatie") verderop te vinden. In plaats daarvan raad ik u aan te vertrouwen op de rasterconventie van Excel in uw formules (bijv. eenvoudigweg linken naar cel C4 of locatie, [Tab Name]l'!G21 , als de verwijzing in een ander tabblad of werkmap staat).
Organiseer uw invoer eenvoudig en transparant. Het is mijn aanbeveling dat u alle invoer in een paar stuurprogrammatabbladen consolideert en ernaar verwijst vanuit hun unieke beginpunten in de spreadsheet.
Vermijd koppelingen naar andere bestanden. Het is beter om de relevante gegevens die u nodig heeft uit een ander bestand in te voeren als hardgecodeerde invoer, die u vervolgens indien nodig handmatig bijwerkt. Van cross-linking is bekend dat het grotere Excel-modellen laat crashen of inconsequent wordt bijgewerkt, waardoor moeilijk te traceren fouten ontstaan.
Binnen een langere spreadsheet kunt u rijen/kolommen "groeperen" in plaats van ze te "verbergen".
Deze praktijk is 100% gebaseerd op ervaring. Het is gemakkelijker om een continue array voor gegevens in één grote, aaneengesloten spreadsheet te volgen en te controleren dan over meerdere tabbladen of, erger nog, meerdere spreadsheets die onderling zijn gekoppeld.
Controles zijn de gemakkelijkste manier om snel de integriteit van een model te beoordelen. "Controles" omvatten alles, van ervoor zorgen dat de totalen die daadwerkelijk moeten overeenkomen, tot het ervoor zorgen dat iemands balans daadwerkelijk in evenwicht is. Ik bouw meestal een paar cheques aan de boven- of onderkant van elke spreadsheet en consolideer ze vervolgens in een apart 'controletabblad'. Dit zorgt ervoor dat het gemakkelijk is om een fout in het model te vinden en vervolgens te traceren waar die fout is ontstaan.
Voorbeeld van een balans "cheque"
Houd er rekening mee dat het nooit een goed idee is om alleen te vertrouwen op controles om de integriteit van een model te verifiëren, aangezien controles meestal vrij hoog zijn. Maar het is een goed startpunt.
Deze sectie behandelt een aantal zeer effectieve Excel-best practices voor onze meer geavanceerde gebruikers. Deze kunnen enige aanpassing vergen, maar zouden later enkele uren werk moeten besparen en relatief eenvoudig te implementeren zijn. Ze zijn als volgt, in het kort, bondig, to-the-point opsommingstekens:
XNPV en XIRR om de toepassing van aangepaste__ datums mogelijk te maken naar kasstromen, op weg naar een rendementsanalyse; dit, in tegenstelling tot Excel's NPV en IRR functies, die impliciet uitgaan van equidistante tijdsintervallen voor de berekening.INDEX MATCH functie over de VLOOKUP functie voor het opzoeken van informatie in grote spreadsheets.VLOOKUP is bijna altijd superieur aan IF verklaringen; raak er vertrouwd mee.IFERROR op te nemen in de syntaxis van uw formules.EOMONTH , en IF uitspraken om datums dynamisch te maken.Of je het nu leuk vindt of niet, Excel is alwetend, alomtegenwoordig en almachtig als het gaat om bedrijfsfinanciering, analyse en datagestuurde besluitvorming. En geloof het of niet, het hoeft niet intimiderend of pijnlijk te zijn, zelfs niet voor beginners of niet-ingewijden. Zoals de meeste dingen in het leven, zullen oefening, consistentie en aandacht voor detail (en in het geval van Excel, snelkoppelingen) je het meeste op weg helpen.
Als u eenmaal bekend bent met de toepassing, zult u merken dat het een krachtig hulpmiddel is voor productiviteit en numeriek vertellen, waar u nauwelijks zonder zult kunnen functioneren, zelfs in uw persoonlijke leven. Naarmate je vordert in de verschillende stadia van Excel-vloeiendheid, wens ik je het beste en moedig ik je aan om dit artikel te gebruiken als een praktische gids die je vaak raadpleegt.
10 beste financiële voordelen voor militaire gezinnen
5 van de beste financiële cadeaus voor kleinkinderen
5 best practices voor het betalen van ziekenhuisrekeningen na verzekering
3 best practices voor het jaaroverzicht van eigenaren van kleine bedrijven
Op zoek naar het beste financiële advies? Dit is wie we aanbevelen
Bedelen om geld:de financiële gids om het samen te krijgen
Het beste financiële advies voor uw jaren '20:beheer uw geld als een professional