Financiële modellering in Excel (gratis stapsgewijze handleiding + sjabloon)

Financiële modellering in Excel is het proces van het bouwen van een financieel model om een transactie, operatie, fusie, overname en financiële informatie weer te geven om te analyseren hoe een verandering in een variabele het uiteindelijke rendement kan beïnvloeden om een beslissing te nemen over een of meer van de bovengenoemde financiële transacties.

Wat is financiële modellering in Excel?

Financiële modellering in Excel is overal op internet te vinden en er is veel geschreven over het leren van financiële modellering, maar de meeste trainingsonderdelen voor financiële modellering zijn precies hetzelfde. Dit gaat verder dan het gebruikelijke gebrabbel en verkent praktische financiële modellering zoals gebruikt door investeringsbankiers en onderzoeksanalisten.

In deze gratis Excel-gids voor financiële modellen neem ik een voorbeeld van Colgate Palmolive en zal ik een volledig geïntegreerd financieel model vanaf nul voorbereiden.

Deze gids is meer dan 6000 woorden en kostte me 3 weken om te voltooien. Bewaar deze pagina voor toekomstig gebruik en vergeet hem niet te delen :-)

HET BELANGRIJKSTE - Download de Excel-sjabloon voor Colgate Financial-modellering om de instructies te volgen

Download de sjabloon voor het financiële model van Colgate

Leer stap voor stap financiële modellering in Excel

Financiële modellering in Excel-training - Lees mij eerst

Stap 1 - Download de sjabloon voor het financiële model van Colgate. U gebruikt dit sjabloon voor de zelfstudie

Download het financiële model van Colgate

Stap 2 - Let op: u krijgt twee sjablonen - 1) Onopgelost Colgate Palmolive Financial Model 2) Opgelost Colgate Palmolive Financial Model

Stap 3 - U werkt aan de Unsolved Colgate Palmolive Financial Model Template . Volg de stapsgewijze instructies om een volledig geïntegreerd financieel model voor te bereiden.

Stap 4 - Veel plezier met leren!

Inhoudsopgave

Ik heb een gemakkelijk te navigeren inhoudsopgave voor u gemaakt om deze financiële modellering te doen

# 1 - Het financiële model van Colgate - Historisch
# 2 - Verhoudingsanalyse van Colgate Palmolive
# 3 - Projecteren van de resultatenrekening
# 4- Werkkapitaalschema
# 5 - Afschrijvingsschema
# 6 - Aflossingsschema
# 7 - Ander langetermijnprogramma
# 8 - De resultatenrekening invullen
# 9 - Schema voor het eigen vermogen van de aandeelhouders
# 10 - Uitstaand schema voor aandelen
# 11 - Invullen van de kasstroomoverzichten
# 12 - Schema voor schulden en rente aanbevolen
Cursus financiële modellering
Gratis financiële modellen

Als u nieuw bent bij financiële modellering, bekijk dan deze gids over Wat is financiële modellering?

Hoe bouw je een financieel model in Excel?

Laten we eens kijken hoe een financieel model vanaf nul wordt opgebouwd. Deze gedetailleerde gids voor financiële modellen biedt u een stapsgewijze handleiding voor het maken van een financieel model. De primaire benadering die in deze financiële modelleringsgids wordt gevolgd, is modulair. De modulaire aanpak betekent in wezen dat we kernverklaringen zoals resultatenrekening, balans en kasstromen opbouwen met behulp van verschillende modules / schema's. De belangrijkste focus is om elke verklaring stap voor stap voor te bereiden en alle ondersteunende schema's na voltooiing aan de kernverklaringen te koppelen. Ik kan begrijpen dat dit vanaf nu misschien niet duidelijk is, maar u zult zich realiseren dat dit heel gemakkelijk is naarmate we verder gaan. U kunt hieronder verschillende schema's / modules voor financiële modellering zien -

Let op het volgende -

De kernverklaringen zijn de resultatenrekening, de balans en de kasstromen.
De aanvullende schema's zijn het afschrijvingsschema, het werkkapitaalschema, het immateriële activa-schema, het eigen vermogen-schema van de aandeelhouders, het schema voor andere langetermijnitems, het schuldschema, enz.
De aanvullende schema's zijn na voltooiing gekoppeld aan de kernuitspraken
In deze gids voor financiële modellen bouwen we een stapsgewijs geïntegreerd financieel model van Colgate Palmolive vanaf nul.

# 1 - Financiële modellering in Excel - Projecteer de historieken

De eerste stap in Financial Modelling Guide is het voorbereiden van de Historicals.

Stap 1A - Download de 10K-rapporten van Colgate

“Financiële modellen worden in Excel opgesteld en de eerste stappen beginnen met weten hoe de industrie het de afgelopen jaren heeft gedaan. Inzicht in het verleden kan ons waardevolle inzichten opleveren met betrekking tot de toekomst van het bedrijf. Daarom is de eerste stap om alle financiële gegevens van het bedrijf te downloaden en deze in een Excel-sheet te vullen. Voor Colgate Palmolive kunt u de jaarverslagen van Colgate Palmolive downloaden van hun Investor Relation Section. Zodra u op "Jaarverslag" klikt, ziet u het onderstaande venster -

Stap 1B - Maak het werkblad Historische financiële overzichten

Als u 10K van 2013 downloadt, zult u zien dat er slechts twee jaar aan financiële overzichten beschikbaar zijn. Voor financiële modellering in Excel is de aanbevolen dataset echter de financiële overzichten van de laatste 5 jaar. Download de laatste 3 jaar van het jaarverslag en vul het historische.
Vaak lijken deze taken te saai en vervelend, omdat het veel tijd en energie kan kosten om het Excel in het gewenste formaat te formatteren en te plaatsen.
Men moet echter niet vergeten dat dit het werk is dat u slechts één keer voor elk bedrijf hoeft te doen en dat het vullen van de historische gegevens een analist helpt de trends en het financiële overzicht te begrijpen
Sla dit dus niet over, download de gegevens en vul de gegevens in (zelfs als u denkt dat dit ezelwerk is ;-))

Als u deze stap wilt overslaan, kunt u het Colgate Palmolive Historical Model hier rechtstreeks downloaden .

Colgate resultatenrekening met historisch ingevuld

Historische balansgegevens van Colgate

# 2 - Verhoudingsanalyse

De tweede stap in Financiële modellering in Excel is het uitvoeren van een verhoudingsanalyse.

Een sleutel tot het leren van financiële modellering in Excel is het kunnen uitvoeren van fundamentele analyses. Als de fundamentele analyse of Ratio Analyse iets nieuws voor je is, raad ik je aan om wat op internet te lezen. Ik ben van plan een diepgaande ratio-analyse te maken in een van mijn aankomende berichten, maar hier is een korte momentopname van de Colgate Palmolive-ratio's

BELANGRIJK - Houd er rekening mee dat ik de verhoudingsanalyse van Colgate in een apart bericht heb bijgewerkt. Bekijk alstublieft deze uitgebreide ratio-analyse.

Stap 2A - Verticale analyse van Colgate

Op de resultatenrekening is de verticale analyse een universeel instrument om de relatieve prestaties van het bedrijf van jaar tot jaar te meten in termen van kosten en winstgevendheid. Het moet altijd worden opgenomen als onderdeel van een financiële analyse. Hier worden percentages berekend ten opzichte van de netto-omzet die als 100% wordt beschouwd. Deze verticale analyse-inspanning in de winst-en-verliesrekening wordt vaak margeanalyse genoemd, omdat het de verschillende marges oplevert in relatie tot de verkoop.

Verticale analyseresultaten

De winstmarge is met 240 basispunten gestegen van 56,2% in 2007 tot 58,6% in 2013. Dit is voornamelijk te wijten aan lagere verkoopkosten.
De bedrijfswinst of EBIT liet ook verbeterde marges zien, waardoor ze toenamen van 19,7% in 2007 tot 22,4% in 2012 (een stijging van 70 basispunten). Dit was het gevolg van lagere algemene en administratieve kosten voor Verkoop. Merk echter op dat de EBIT-marges in 2013 zijn gedaald tot 20,4% als gevolg van een stijging in “Overige kosten”. Bekijk ook het verschil tussen EBIT versus EBITDA
De nettowinstmarge steeg van 12,6% in 2007 tot 14,5% in 2012. De winstmarge in 2013 daalde echter tot 12,9%, voornamelijk als gevolg van gestegen "overige kosten".
De winst per aandeel is gestaag gestegen van FY2007 tot FY2012. Er was echter een lichte daling in de winst per aandeel van FY2013
Merk ook op dat de afschrijving en amortisatie afzonderlijk worden vermeld in de resultatenrekening. Het is inbegrepen in de verkoopkosten

Stap 2B - Horizontale analyse van Colgate

Horizontale analyse is een techniek die wordt gebruikt om trends in de tijd te evalueren door procentuele verhogingen of verlagingen te berekenen ten opzichte van een basisjaar. Het biedt een analytische koppeling tussen rekeningen die op verschillende datums zijn berekend met behulp van valuta met verschillende koopkracht. Deze analyse indexeert in feite de rekeningen en vergelijkt de evolutie ervan in de tijd. Net als bij de verticale analysemethodologie, zullen er problemen naar boven komen die moeten worden onderzocht en aangevuld met andere financiële analysetechnieken. De focus ligt op het zoeken naar symptomen van problemen die met aanvullende technieken kunnen worden vastgesteld.

Laten we eens kijken naar de horizontale analyse van Colgate

Resultaten horizontale analyse

We zien dat de netto-omzet in 2013 met 2,0% is gestegen.
Let ook op de trend in de verkoopkosten, we zien dat ze niet in dezelfde verhouding zijn gegroeid als de verkoop.
Deze observaties zijn uitermate handig terwijl we financiële modellering doen in Excel

Stap 2C - Liquiditeitsratio's van Colgate

Liquiditeitsratio's meten de relatie tussen de meer liquide activa van een onderneming (de activa die het gemakkelijkst in contanten kunnen worden omgezet) en de kortlopende verplichtingen. De meest voorkomende liquiditeitsratio's zijn: Current ratio Acid test (of quick asset) ratio Cash ratio's
Omzetratio's zoals debiteurenomzet, voorraadomzet en crediteurenomzet

Belangrijkste hoogtepunten van liquiditeitsratio's

De huidige verhouding van Colgate is voor alle jaren groter dan 1,0. Dit impliceert dat de vlottende activa groter zijn dan de kortlopende verplichtingen en dat Colgate misschien over voldoende liquiditeit beschikt
Quick Ratio van Colgate ligt in het bereik van 0,6-0,7, dit betekent dat Colgates Cash en verhandelbare effecten maar liefst 70% van de kortlopende verplichtingen kunnen betalen. Dit lijkt een redelijke situatie te zijn voor Colgate.
De geldinzamelingscyclus is afgenomen van 43 dagen in 2009 naar 39 dagen in 2013. Dit is voornamelijk het gevolg van de verkorting van de incassoperiode voor vorderingen.

Bekijk ook dit gedetailleerde artikel over Cash Conversion Cycle

Stap 2D - Operationele winstgevendheidsratio's van Colgate

Winstgevendheidsverhoudingen zijn het vermogen van een bedrijf om inkomsten te genereren in verhouding tot de omzet, activa en eigen vermogen

Belangrijkste hoogtepunten - Winstgevendheidsratio's van Colgate

Zoals we in de bovenstaande tabel kunnen zien, heeft Colgate een ROE van dichter bij 100%, wat een groot rendement voor de aandeelhouders betekent.

Stap 2E - Risicoanalyse van Colgate

Door middel van risicoanalyse proberen we te peilen of de bedrijven in staat zullen zijn om hun korte en lange termijn verplichtingen (schulden) te betalen. We berekenen hefboomratio's die gericht zijn op de toereikendheid van activa of het genereren van activa. Verhoudingen die worden bekeken, zijn

Verhouding schuld / eigen vermogen
Schuldgraad
Rentedekkingsratio
De schuld / eigen vermogenratio is gestaag gestegen tot een hoger niveau van 2,23x. Dit duidt op een verhoogde financiële hefboomwerking en risico's in de markt
De rentedekkingsratio is echter erg hoog, wat betekent dat er minder risico is op wanbetaling van rentebetalingen.

# 3 - Financiële modellering in Excel - Projecteer de resultatenrekening

De derde stap in Financiële modellering is het voorspellen van de resultatenrekening, waarbij we beginnen met het modelleren van de items Verkoop of Opbrengsten.

Stap 3A - Inkomstenprognoses

Voor de meeste bedrijven zijn inkomsten een fundamentele motor van economische prestaties. Een goed ontworpen en logisch verdienmodel dat het soort en de omvang van de inkomstenstromen nauwkeurig weergeeft, is uiterst belangrijk. Er zijn net zoveel manieren om een inkomstenschema op te stellen als er bedrijven zijn. Enkele veel voorkomende soorten zijn:

Omzetgroei: veronderstelling van omzetgroei in elke periode bepaalt de verandering ten opzichte van de voorgaande periode. Dit is een eenvoudige en veelgebruikte methode, maar biedt geen inzicht in de componenten of dynamiek van groei.
Inflatie- en volume / mix-effecten: in plaats van een eenvoudige groei-aanname worden een prijsinflatiefactor en een volumefactor gebruikt. Deze nuttige benadering maakt het mogelijk vaste en variabele kosten in multi-productbedrijven te modelleren en houdt rekening met prijs- versus volumebewegingen.
Eenheidsvolume, verandering in volume, gemiddelde prijs en verandering in prijs: deze methode is geschikt voor bedrijven met een eenvoudige productmix; het maakt analyse van de impact van verschillende sleutelvariabelen mogelijk.
Omvang en groei van de dollarmarkt: marktaandeel en verandering in aandeel - Nuttig voor gevallen waarin informatie beschikbaar is over de marktdynamiek en waar deze veronderstellingen waarschijnlijk fundamenteel zijn voor een beslissing. Bijvoorbeeld de telecomsector
Omvang en groei van de markt per eenheid: dit is gedetailleerder dan het voorgaande geval en is handig wanneer prijsstelling in de markt een belangrijke variabele is. (Bijvoorbeeld voor een bedrijf met een prijsverlagingsstrategie of een eersteklas premium geprijsde nichespeler), bijvoorbeeld luxeautomarkt
Volumecapaciteit, capaciteitsbenutting en gemiddelde prijs: deze aannames kunnen belangrijk zijn voor bedrijven waar productiecapaciteit belangrijk is voor de beslissing. (Bijvoorbeeld bij de aankoop van extra capaciteit of om te bepalen of de uitbreiding nieuwe investeringen vereist.)
Productbeschikbaarheid en prijzen
De omzet werd gedreven door investeringen in kapitaal, marketing of R&D
Opbrengsten gebaseerd op installed base (doorlopende verkoop van onderdelen, wegwerpartikelen, service en add-ons, enz.). Voorbeelden zijn onder meer klassieke bedrijven met scheermesjes en bedrijven zoals computers waar de verkoop van service, software en upgrades belangrijk zijn. Het modelleren van de geïnstalleerde basis is essentieel (nieuwe toevoegingen aan de basis, verloop in de basis, voortdurende inkomsten per klant, enz.).
Werknemer gebaseerd: bijvoorbeeld inkomsten van professionele dienstverleners of verkoopgebaseerde bedrijven zoals makelaars. Modellering moet gericht zijn op het netto personeelsbestand, de omzet per werknemer (vaak gebaseerd op factureerbare uren). Meer gedetailleerde modellen omvatten anciënniteit en andere factoren die van invloed zijn op de prijsstelling.
Op basis van winkel, faciliteit of vierkante meters: Retailbedrijven worden vaak gemodelleerd op basis van winkels (oude winkels plus nieuwe winkels in elk jaar) en omzet per winkel.
Op basis van bezettingsgraad: deze benadering is van toepassing op luchtvaartmaatschappijen, hotels, bioscopen en andere bedrijven met lage marginale kosten.

De inkomsten van Colgate voorspellen

Laten we nu eens kijken naar het Colgate 10K 2013-rapport. We merken op dat Colgate in de winst-en-verliesrekening geen segmentale informatie heeft verstrekt, maar als aanvullende informatie heeft Colgate enkele details van segmenten verstrekt op pagina 87 Bron - Colgate 2013 - 10K, pagina 86

Aangezien we geen verdere informatie hebben over de segmenten, zullen we de toekomstige verkopen van Colgate projecteren op basis van deze beschikbare gegevens. We zullen de benadering van verkoopgroei in alle segmenten gebruiken om de prognoses af te leiden. Zie onderstaande afbeelding. We hebben het groeipercentage op jaarbasis voor elk segment berekend. Nu kunnen we uitgaan van een verkoopgroeipercentage op basis van de historische trends en de inkomsten onder elk segment projecteren. De totale netto-omzet is de som van het segment Oral, Personal & Home Care en Pet Nutrition.

Stap 3B - Kostenprognoses

Percentage inkomsten: eenvoudig maar biedt geen inzicht in enige hefboomwerking (schaalvoordelen of vaste kosten
Andere kosten dan afschrijvingen als een percentage van de inkomsten en afschrijvingen volgens een afzonderlijk schema: deze benadering is in de meeste gevallen echt het minimum dat aanvaardbaar is en staat slechts een gedeeltelijke analyse van de operationele hefboomwerking toe.
Variabele kosten op basis van omzet of volume, vaste kosten op basis van historische trends en afschrijving volgens een apart schema: deze benadering is het minimum dat nodig is voor gevoeligheidsanalyse van winstgevendheid op basis van meerdere omzetscenario's

Kostenprojecties voor Colgate

Voor het voorspellen van de kosten zal de eerder uitgevoerde verticale analyse nuttig zijn. Laten we de verticale analyse nog eens bekijken -

Aangezien we al verkopen hebben voorspeld, zijn alle andere kosten enkele marges van deze verkoop.
De benadering is om de richtlijnen te nemen van de historische kosten- en kostenmarges en vervolgens de toekomstige marge te voorspellen.
De verkoopkosten hebben bijvoorbeeld de afgelopen 5 jaar tussen 41% -42% gelegen. Op basis hiervan kunnen we de marges voorspellen.
Evenzo lagen de verkoopkosten, algemene en administratieve kosten historisch in de orde van grootte van 34% -36%. Op deze basis kunnen we uitgaan van de toekomstige VAA-kostenmarge. Evenzo kunnen we doorgaan voor een andere reeks uitgaven.

Met behulp van de bovenstaande marges kunnen we de werkelijke waarden vinden door berekeningen achteraf.

Voor het berekenen van de voorziening voor belastingen gebruiken we de aanname van het effectieve belastingtarief

Houd er ook rekening mee dat we de rij 'Rentelasten (inkomsten)' niet voltooien, aangezien we de resultatenrekening in een later stadium opnieuw zullen bekijken.
Rentelasten en rente-inkomsten.
We hebben ook geen afschrijvingen en amortisatie berekend die al in de verkoopkosten zijn opgenomen.
Hiermee is de resultatenrekening (voorlopig althans!)

# 4 - Financiële modellering - Schema voor werkkapitaal

Nu we de resultatenrekening hebben voltooid, is de vierde stap in financiële modellering het bekijken van het werkkapitaalschema.

Hieronder staan de stappen die moeten worden gevolgd voor het werkkapitaalschema

Stap 4A - Koppel de netto-omzet en de verkoopkosten

Stap 4B - Raadpleeg de balansgegevens met betrekking tot werkkapitaal

Verwijs naar de gegevens uit het verleden van de balans
Bereken het netto werkkapitaal
Kom tot een toename / afname van het werkkapitaal
Merk op dat we kortlopende schulden en geldmiddelen en kasequivalenten niet in het werkkapitaal hebben opgenomen. We zullen schulden en geldmiddelen en kasequivalenten afzonderlijk behandelen.

Stap 4C - Bereken de omzetratio's

Bereken historische ratio's en percentages
Gebruik het eind- of gemiddelde saldo
Beide zijn acceptabel zolang de consistentie behouden blijft

Stap 4D - Vul de aannames in voor toekomstige werkkapitaalitems

Van bepaalde items zonder een duidelijke driver wordt meestal uitgegaan van constante bedragen
Zorg ervoor dat de aannames redelijk zijn en in lijn met het bedrijf

Stap 4E - P roject de toekomstige werkkapitaalsaldi

Stap 4F - Bereken de veranderingen in werkkapitaal

Kom tot cashflows op basis van afzonderlijke regelitems
Zorg ervoor dat de borden nauwkeurig zijn!

Stap 4G - Koppel het verwachte werkkapitaal aan de balans

Stap 4H - Koppel werkkapitaal aan het kasstroomoverzicht

# 5 - Financiële modellering in Excel - Afschrijvingsschema

Met de voltooiing van de werkkapitaalplanning, is de volgende stap in deze financiële modellering het project de Capex van Colgate en de projectafschrijvingen en activa. Colgate 2013 - 10K, pagina 49

Afschrijvingen en amortisatie worden niet op een aparte post vermeld, maar worden opgenomen in de kostprijs van de verkopen
Bekijk in dergelijke gevallen het kasstroomoverzicht waar u de afschrijvings- en amortisatiekosten vindt. Merk ook op dat de onderstaande cijfers 1) afschrijving 2) afschrijving zijn. Dus wat is het afschrijvingsnummer?
Eindsaldo voor PBM = Beginsaldo + Capex - Afschrijving - Aanpassing voor activumverkopen (BASISvergelijking)

Stap 5A - Koppel de netto-omzetcijfers in het afschrijvingsschema

Stel de regelitems in
Referentie netto-omzet
Voer eerdere investeringsuitgaven in
Aankomen bij Capex als% van de netto-omzet

Stap 5B - Voorspel de investeringsuitgaven

Er zijn verschillende benaderingen om de kapitaaluitgaven te voorspellen. Een veel voorkomende benadering is om te kijken naar de persberichten, managementprojecties, MD&A om de visie van het bedrijf op toekomstige kapitaaluitgaven te begrijpen.
Als het bedrijf richtlijnen heeft gegeven voor toekomstige kapitaaluitgaven, kunnen we die cijfers direct nemen.
Als de capex-cijfers echter niet direct beschikbaar zijn, kunnen we deze grofweg berekenen met capex als% van de omzet (zoals hieronder gedaan)
Gebruik uw oordeel op basis van branchekennis en andere redelijke drijfveren

Stap 5C - Verwijs naar eerdere informatie

We gebruiken Eindsaldo voor PPE = Beginsaldo + Capex - Afschrijving - Aanpassing voor activumverkopen (BASISvergelijking)
Het is erg moeilijk om eerdere materiële vaste activa te verzoenen vanwege herformuleringen, verkopen van activa, enz
Het wordt daarom aanbevolen om de oude persoonlijke beschermingsmiddelen niet met elkaar in overeenstemming te brengen, aangezien dit tot enige verwarring kan leiden.

Afschrijvingsbeleid van Colgate

We merken op dat Colgate niet expliciet een gedetailleerde opsplitsing van de activa heeft verstrekt. Ze hebben liever alle activa in land, gebouwen, machines en andere uitrusting gestopt
Ook de gebruiksduur van machines en uitrusting wordt binnen bereik geleverd. In dit geval zullen we wat giswerk moeten doen om tot de gemiddelde resterende levensduur van de activa te komen
Er worden ook geen richtlijnen voor de gebruiksduur gegeven voor "Overige apparatuur". We zullen de gebruiksduur van andere apparatuur moeten inschatten

Colgate 2013 - 10K, pagina 55

Hieronder vindt u het uiteenvallen van de eigendoms-, installatie- en uitrustingsdetails van 2012 en 2013

Colgate 2013 - 10K, pagina 91

Stap 5D - Schat het uiteenvallen van materiële vaste activa (PBM)

Zoek eerst de vermogensgewichten van de huidige persoonlijke beschermingsmiddelen (2013)
We gaan ervan uit dat deze vermogenswegingen van 2013 PPE zullen blijven bestaan
We gebruiken deze vermogensgewichten om de verdeling van de geschatte kapitaaluitgaven te berekenen

Stap 5E - Schat de afschrijving van activa

Houd er rekening mee dat we de afschrijving van grond niet berekenen, aangezien grond geen af te schrijven actief is
Voor het schatten van de afschrijving uit Bouwverbeteringen maken we allereerst gebruik van onderstaande opbouw.
De afschrijving is hier opgesplitst in twee delen: 1) afschrijving van de activa voor gebouwverbeteringen die al op de balans staan 2) afschrijving van de toekomstige gebouwverbeteringen
Voor het berekenen van de afschrijving van gebouwverbeteringen die op het actief vermeld staan, gebruiken we de eenvoudige lineaire afschrijvingsmethode
Om toekomstige afschrijving te berekenen, transponeren we eerst de capex met behulp van de TRANSPONEREN-functie in Excel
We berekenen de afschrijving uit activabijdrage van elk jaar
Ook wordt de afschrijving in het eerste jaar gedeeld door 2, aangezien we uitgaan van de halfjaarlijkse afspraak voor de inzet van activa

Totale afschrijving van gebouwverbetering = afschrijving van de activa voor gebouwverbeteringen die al op de balans staan + afschrijving van de toekomstige gebouwverbeteringen Het bovenstaande proces voor het schatten van de afschrijving wordt gebruikt om de afschrijving te berekenen van 1) productieapparatuur en 2) andere apparatuur als hieronder weergegeven.

Andere soorten apparatuur

Totale afschrijving van Colgate = afschrijving (gebouwverbeteringen) + afschrijving (machines en uitrusting) + afschrijving (andere uitrusting) Zodra we de totale afschrijving hebben gevonden, kunnen we dat in de BASE-vergelijking zetten zoals hieronder weergegeven

Hiermee krijgen we de Ending Net PP & E-cijfers voor elk van de jaren

Stap 5F - Koppel de netto PP&E aan de balans

# 6 - Aflossingsschema

De zesde stap in deze financiële modellering in Excel is het voorspellen van de afschrijving. We hebben hier twee brede categorieën om te overwegen: 1) Goodwill en 2) Overige immateriële activa.

Stap 6A - Goodwill voorspellen

Colgate 2013 - 10K, pagina 61

Goodwill komt op de balans wanneer een bedrijf een ander bedrijf overneemt. Het is normaal gesproken erg moeilijk om de goodwill voor toekomstige jaren te projecteren.
Goodwill wordt echter jaarlijks onderworpen aan testen op bijzondere waardeverminderingen die door de onderneming zelf worden uitgevoerd. Analisten zijn niet in staat om dergelijke tests uit te voeren en schattingen van bijzondere waardeverminderingen op te stellen
De meeste analisten projecteren geen goodwill, ze houden dit gewoon constant en dit is wat we in ons geval ook zullen doen.

Stap 6B - Voorspelling van andere immateriële activa

Zoals opgemerkt in het 10K-rapport van Colgate, houdt het grootste deel van de immateriële levensduur verband met de overname van Sanex
"Toevoegingen aan immateriële activa" zijn ook erg moeilijk te projecteren
Colgate's 10K-rapport geeft ons de details van de komende 5 jaar van afschrijvingskosten.
We zullen deze schattingen gebruiken in ons Financieel Model Colgate 2013 - 10K, pagina 61

Stap 6C - Het beëindigen van netto immateriële activa is gekoppeld aan de 'Overige immateriële activa'

Stap 6D - Koppel afschrijving en amortisatie aan kasstroomoverzichten

Stap 6E - Koppel Capex & Toevoeging aan immateriële activa aan kasstroomoverzichten

# 7 - Ander langetermijnprogramma

De volgende stap in deze Financiële Modellering is het opstellen van de Overige Lange Termijnplanning. Dit is het schema dat we voorbereiden voor de “restjes” die geen specifieke drijfveren hebben voor prognoses. In het geval van Colgate waren de overige langetermijnposten (restanten) uitgestelde winstbelastingen (passiva en activa), overige activa en overige passiva.

Stap 7A - Verwijs naar de historische gegevens van de balans

Bereken ook de wijzigingen in deze items.

Stap 7B - Voorspel de activa en passiva op lange termijn

Houd de items op lange termijn constant voor verwachte jaren in het geval er geen zichtbare stuurprogramma's zijn
Koppel de verwachte langetermijnposten aan de balans zoals hieronder weergegeven

Stap 7C - Verwijs naar andere langetermijnposten naar de balans

Stap 7D - Koppel de lange termijn items aan het kasstroomoverzicht

Houd er rekening mee dat als we de activa en passiva op lange termijn als constant hebben gehouden, de verandering die naar het kasstroomoverzicht vloeit, nul zou zijn.

# 8 - Financiële modellering in Excel - De resultatenrekening invullen

Voordat we verder gaan in deze op Excel gebaseerde financiële modellering, gaan we terug en kijken we opnieuw naar de resultatenrekening
Vul de historische gewogen gewogen gemiddelde aandelen en het gewogen gemiddelde aantal verwaterde aandelen in
Deze cijfers zijn beschikbaar in het 10K-rapport van Colgate

Stap 8A - Verwijs naar de gewone en verwaterde aandelen

Ga er in dit stadium van uit dat het toekomstige aantal gewone en verwaterde aandelen hetzelfde blijft als in 2013.

Stap 8B - Bereken de gewone en verwaterde winst per aandeel

Hiermee zijn we klaar om over te gaan naar ons volgende schema, namelijk het Aandeelhoudersvermogensschema.

# 9 - Financiële modellering - Aandeelhoudersvermogensschema

De volgende stap in deze Financiële Modellering in Excel-training is om te kijken naar het Aandeelhoudersvermogensschema. Het primaire doel van dit schema is om aandelengerelateerde items zoals eigen vermogen, dividenden, aandeleninkoop, optieopbrengsten, enz. Te projecteren . Colgate 2013 - 10K, pagina 68

Stap 9A - Inkoop van aandelen: vul de historische cijfers in

Historisch gezien heeft Colgate aandelen teruggekocht, zoals we hierboven kunnen zien.
Vul de teruggekochte aandelen van Colgate (miljoenen) in het Excel-blad in.
Koppel de historische verwaterde WPA aan de resultatenrekening
Het historisch teruggekochte bedrag moet worden verwezen naar de kasstroomoverzichten

Bekijk ook Versnelde terugkoop van aandelen

Stap 9B - Inkoop van aandelen: Bereken het PE-veelvoud (EPS-veelvoud)

Bereken de impliciete gemiddelde prijs waartegen Colgate in het verleden aandelen heeft ingekocht. Dit wordt berekend als Ingekochte hoeveelheid / Aantal aandelen
Bereken het PE-veelvoud = geïmpliceerde aandelenkoers / EPS

Stap 9C - Inkoop van aandelen: zoeken naar het teruggekochte aandeel van Colgate

Colgate heeft geen officiële aankondiging gedaan van hoeveel aandelen ze van plan zijn terug te kopen. De enige informatie die hun 10K-aandelen melden, is dat ze toestemming hebben gegeven voor een terugkoop van maximaal 50 miljoen aandelen. Colgate 2013 - 10K, pagina 35

Om het aantal teruggekochte aandelen te achterhalen, moeten we uitgaan van het Inkoopbedrag. Op basis van het historische terugkoopbedrag heb ik dit aantal voor alle toekomstige jaren op $ 1.500 miljoen gesteld.
Om het aantal teruggekochte aandelen te vinden, hebben we de verwachte impliciete aandelenprijs van de potentiële terugkoop nodig.
Impliciete aandelenprijs = veronderstelde PE multiplex EPS
Op basis van historische trends kan worden uitgegaan van toekomstige terugkopen PE multiple. We merken op dat Colgate aandelen heeft teruggekocht tegen een gemiddelde PE-range van 17x - 25x
Hieronder vindt u de momentopname van Reuters waarmee we het PE-bereik voor Colgate kunnen valideren

www.reuters.com

In ons geval ben ik ervan uitgegaan dat alle toekomstige terugkopen van Colgate tegen een PE-veelvoud van 19x zullen plaatsvinden.
Met behulp van de PE van 19x kunnen we de impliciete prijs vinden = EPS x 19
Nu we de impliciete prijs hebben gevonden, kunnen we het aantal teruggekochte aandelen vinden = $ bedrag dat is gebruikt voor de terugkoop / impliciete prijs

Stap 9D - Aandelenopties: vul historische gegevens in

Uit het overzicht van gewone aandelen en eigen vermogen weten we het aantal opties dat elk jaar wordt uitgeoefend.

Daarnaast hebben we ook de Optie-opbrengst uit de kasstroomoverzichten (ongeveer)

Hiermee zouden we de effectieve uitoefenprijs moeten kunnen vinden

Colgate 2013 - 10K, pagina 53

Merk ook op dat de aandelenopties contractuele voorwaarden hebben van zes jaar en worden verworven over drie jaar. Colgate 2013 - 10K, pagina 69

Met deze gegevens vullen we de Optiegegevens zoals hieronder beschreven. We merken ook op dat de gewogen gemiddelde uitoefenprijs van aandelenopties voor 2013 $ 42 was en dat het aantal uitoefenbare opties 24,151 miljoen was. Colgate 2013 - 10K, pagina 70

Stap 9E - Aandelenopties: zoek de optieopbrengst

Als we deze cijfers in onze onderstaande optiegegevens opnemen, merken we op dat de opbrengst van de optie $ 1,014 miljard is

Stap 9F - Aandelenopties: prognose van beperkte voorraadgegevens

Naast de aandelenopties worden er Restricted Stock Units gegeven aan de werknemers met een gewogen gemiddelde periode van 2,2 jaar Colgate 2013 - 10.000, Pagina 81

Deze gegevens in de gegevensset Opties vullen Voor de eenvoud hebben we de uitgifte van opties niet geprojecteerd (ik weet dat dit niet de juiste veronderstelling is, maar vanwege een gebrek aan gegevens neem ik in de toekomst geen optie-uitgiften meer. deze zijn nul, zoals aangegeven in het grijze gebied hierboven. Bovendien wordt verwacht dat de restricted stock units in de toekomst 2,0 miljoen zullen bedragen.

Bekijk ook de Treasury Stock Method

Stap 9G - Dividenden: voorspel de dividenden

Voorspel geschatte dividenden met behulp van de dividenduitkeringsratio
Uitgaven vast dividend Uitbetaling per aandeel
Uit de 10K-rapporten halen we alle informatie uit het verleden over dividenden
Met de informatie over uitgekeerde dividenden kunnen we de uitbetalingsratio van het dividend vinden = totale betaalde dividenden / netto-inkomen.
Ik heb de uitbetalingsratio van Colgate voor dividenden berekend zoals hieronder te zien is - We merken op dat de uitbetalingsratio van dividenden in het algemeen tussen de 50% en 60% ligt. Laten we aannemen dat de dividenduitkeringsratio in de komende jaren 55% zal bedragen.
We kunnen ook het verwachte nettoresultaat koppelen aan de resultatenrekening
Als we zowel het verwachte nettoresultaat als de uitbetalingsratio voor dividenden gebruiken, kunnen we de totale uitbetaalde dividenden vinden

Stap 8H - Voorspelling van het eigen vermogen in zijn geheel

Met de prognose van de inkoop van eigen aandelen, de opbrengst van opties en de uitgekeerde dividenden, zijn we klaar om het Aandeelhoudersvermogensschema in te vullen. Koppel al deze punten om de eindbalans voor elk jaar te vinden, zoals hieronder weergegeven.

Stap 9I - Koppel het einde van het eigen vermogen van de aandeelhouders aan de balans

Stap 9J - Koppel dividenden, aandeleninkoop en opties gaan naar CF

# 10 - Uitstaand schema voor aandelen

De volgende stap in deze online financiële modellering in Excel-training is het bekijken van het Shares Oustanding-schema. Overzicht van het uitstaande schema van de aandelen

Basisaandelen - actueel en gemiddeld
Leg waar nodig de effecten van opties en converteerbare obligaties uit het verleden vast
Verwaterde aandelen - gemiddeld
Ingekochte referentieaandelen en nieuwe aandelen van uitgeoefende opties
Bereken voorspelde basisaandelen (werkelijk)
Bereken gemiddelde gewone en verwaterde aandelen
Verwijs geprojecteerde aandelen naar de winst-en-verliesrekening (herinner je de opbouw van de winst-en-verliesrekening!)
Voer historische aandelen openstaande informatie in
Opmerking : dit schema is doorgaans geïntegreerd met het aandelenprogramma

Stap 10A - Voer de historische cijfers van het 10K-rapport in

Uitgegeven aandelen (daadwerkelijke realisatie van opties) en ingekochte aandelen kunnen worden geraadpleegd in het Aandeelhoudersoverzicht
Ook woog de input een gemiddeld aantal aandelen en het effect van aandelenopties voor de historische jaren.

Stap 10B - Koppel uitgiften en terugkopen van aandelen vanuit het aandelenkapitaalschema.

Gewone aandelen (einde) = gewone aandelen (begin) + aandelenemissies - ingekochte aandelen.

Stap 10C - Vind de gewogen gemiddelde aandelen,

we vinden gemiddeld twee jaar zoals hieronder weergegeven.
Voeg ook het effect toe van opties en voorwaardelijk toegekende aandelen (waarnaar wordt verwezen in het schema van het eigen vermogen van de aandeelhouders) om de verwaterde gewogen gemiddelde aandelen te vinden.

Stap 10D - Koppel gewone en verwaterde gewogen aandelen aan de resultatenrekening

Nu we de verwaterde gewogen gemiddelde aandelen hebben berekend, is het tijd om deze bij te werken in de resultatenrekening.
Koppel het verwachte verwaterde gewogen gemiddelde aantal uitstaande aandelen aan de resultatenrekening zoals hieronder weergegeven

Hiermee vullen we het Shares Oustanding-schema in en hebben we tijd om naar onze volgende set verklaringen te gaan.

# 11 - Invullen van de kasstroomoverzichten

Het is belangrijk voor ons om de kasstroomoverzichten volledig in te vullen voordat we naar ons volgende en laatste schema in deze financiële modellering gaan, dwz het schuldschema Tot dit stadium zijn er slechts een paar dingen die onvolledig zijn

Resultatenrekening - rentelasten / -baten zijn in dit stadium onvolledig
Balans - geldmiddelen en schulden zijn in dit stadium onvolledig

Stap 11A - Bereken de cashflow voor financieringsactiviteiten

Bekijk ook Cashflow uit financiering

Stap 11B - Vind de netto toename (afname) in Cash & Cash Equivalents

Stap 11C = Vul de kasstroomoverzichten in

Vind de geldmiddelen en kasequivalenten aan het einde van het jaar.

Stap 11D - Koppel de geldmiddelen en kasequivalenten aan de balans.

Nu zijn we klaar om te zorgen voor ons laatste en laatste schema, dat wil zeggen schulden- en renteschema

# 12- Financiële modellering in Excel - Schema voor schulden en rente

De volgende stap in deze online financiële modellering is het invullen van het schuld- en renteschema. Samenvatting van de schuld en rente - Schema

Stap 12A - Stel een schuldoverzicht op

Raadpleeg de cashflow die beschikbaar is voor financiering
Verwijs naar alle bronnen van eigen vermogen en het gebruik van contanten

Stap 12B - Bereken de cashflow op basis van de terugbetaling van schulden

Verwijs naar het beginkassaldo van de balans
Trek een minimum kassaldo af. We zijn ervan uitgegaan dat Colgate elk jaar minimaal $ 500 miljoen zou willen behouden.

Langlopende schulduitgifte / aflossingen overslaan, contanten beschikbaar voor doorlopende kredietfaciliteit en revolversectie voorlopig Uit het 10K-rapport van Colgate zien we de beschikbare details op de doorlopende kredietfaciliteit Colgate 2013 - 10K, pagina 35

In aanvullende informatie over schulden wordt ook de gecommitteerde schuldaflossing op lange termijn vermeld. Colgate 2013 - 10K, pagina 36

Stap 12C - Bereken de laatste langlopende schuld

We gebruiken het bovenstaande schema voor de aflossing van langlopende schulden en berekenen het eindsaldo van de aflossingen op lange termijn

Stap 12D - Koppel de aflossingen op de lange termijn.

Stap 12E - Bereken de discretionaire leningen / aflossingen

Gebruik de cash sweep-formule zoals hieronder weergegeven om de discretionaire leningen / aflossingen te berekenen.

Stap 12F - Bereken de rentelasten van de langlopende schuld

Bereken het gemiddelde saldo voor doorlopende kredietfaciliteit en langlopende schulden
Maak een redelijke veronderstelling voor een rentetarief op basis van de informatie in het 10K-rapport
Bereken totale rentelasten = gemiddeld schuldsaldo x rentetarief

Vind de totale rentelasten = rente (doorlopende kredietfaciliteit) + rente (langlopende schuld)

Stap 12G - Koppel hoofdschuld & revolveropnames aan kasstromen

Stap 12H - Referentie huidige en lange termijn naar balans

Baken het huidige deel van de langlopende schulden en de langlopende schulden af zoals hieronder weergegeven

Koppel de doorlopende kredietfaciliteit, de langlopende schulden en het huidige deel van de langlopende schulden aan de balans

Stap 12I - Bereken de rente-inkomsten met behulp van het gemiddelde kassaldo

Stap 12J - Koppel rentelasten en rente-inkomsten aan de resultatenrekening

Voer de balanscontrole uit: totale activa = passiva + eigen vermogen

Stap 12K - Controleer de balans

Als er een discrepantie is, moeten we het model controleren en controleren op eventuele koppelingsfouten

Aanbevolen cursus financiële modellering

Ik hoop dat je genoten hebt van de gratis Excel-gids voor financiële modellen. Als u financiële modellering in Excel wilt leren door middel van onze deskundige videocolleges, kunt u ook kijken naar onze Investment Banking-training. Dit zijn voornamelijk 99 cursussen Investment Banking-trainingsbundel. Deze cursus begint bij de basis en neemt je mee naar het gevorderde niveau van Investment Banking Job. Deze cursus is opgedeeld in 5 delen -

Deel 1 - Opleiding investeringsbankieren - Kerncursussen
(26 cursussen)
Deel 2 - Geavanceerde training voor investeringsbankieren
(20 cursussen)
Deel 3 - Add-ons voor investeringsbankieren
(13 cursussen)
Deel 4 - Opleidingen voor de basis van Investment Banking
(23 cursussen)
Deel 5 - Zachte vaardigheden voor investeringsbankiers
(17 cursussen)

Financiële modellen downloaden

Alibaba financieel model
Box IPO Financieel Model
Sjablonen voor financiële modellen
Financiële modellering van het bankwezen

Wat nu?

Als je iets nieuws hebt geleerd of genoten hebt van deze op Excel gebaseerde financiële modellering, laat dan hieronder een reactie achter. Laat me weten wat je denkt. Veel dank en wees voorzichtig. Veel plezier met leren!