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. Er is veel geschreven over het leren van financiële modellering; de meeste trainingsonderdelen voor financiële modellen zijn echter 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 zal ik een voorbeeld nemen van Colgate Palmolive en een volledig geïntegreerd financieel model vanaf nul voorbereiden.

Deze gids is meer dan 6000 woorden en kostte me drie 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

Voer e-mailadres in Door met bovenstaande stap verder te gaan, gaat u akkoord met onze gebruiksvoorwaarden en ons privacybeleid.

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

• Hoe bouw je een financieel model?
• # 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. Het modulaire systeem betekent in wezen het opbouwen van kernoverzichten zoals resultatenrekening, balans en kasstromen met behulp van verschillende modules / schema's. De belangrijkste focus is om elke verklaring stap voor stap voor te bereiden en alle ondersteunende programma's na voltooiing aan de kernverklaringen te koppelen. Ik kan begrijpen dat dit nu misschien niet duidelijk is; u zult zich echter 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 verschillende schema's zijn het afschrijvingsschema, het werkkapitaalschema, het immateriële activa-schema, het vermogensschema van de aandeelhouders, het schema voor andere langetermijnitems, het schuldschema, enz.
• De verschillende schema's zijn na voltooiing gekoppeld aan de kernuitspraken.
• In deze handleiding voor financiële modellen bouwen we een stapsgewijs geïntegreerd economisch model van Colgate Palmolive vanaf nul.

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

De eerste stap in de 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.
• U moet echter niet vergeten dat dit het werk is dat u slechts één keer voor elk bedrijf hoeft te doen, en bovendien helpt het invullen van de historische gegevens een analist om de trends en financiële overzichten te begrijpen.
• Sla dit dus niet over, download de gegevens en vul de gegevens in (zelfs als u denkt dat dit ezelswerk 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 elementaire 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

Wat de resultatenrekening betreft, 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 marge-analyse genoemd, omdat het de verschillende marges met betrekking tot verkoop oplevert.

Verticale analyseresultaten

• De winstmarge was met 240 basispunten gestegen van 56,2% in 2007 tot 58,6% in 2013. Dit is voornamelijk het gevolg van lagere verkoopkosten.
• De bedrijfswinst of EBIT liet ook verbeterde marges zien, 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 hogere "Overige kosten". Bekijk ook het verschil tussen EBIT versus EBITDA
• De nettowinstmarge is gestegen van 12,6% in 2007 tot 14,5% in 2012. De winstmarge in 2013 is echter gedaald tot 12,9%, voornamelijk als gevolg van hogere "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 de valuta met verschillende koopkracht. In feite indexeert deze analyse de rapporten en vergelijkt ze de evolutie ervan in de tijd. Net als bij de verticale analysemethodologie, zullen er problemen aan de oppervlakte 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 methoden 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 Sales.
• Deze observaties zijn buitengewoon 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 (die het gemakkelijkst in contanten kunnen worden omgezet) en de kortlopende verplichtingen. De meest voorkomende liquiditeitsratio's zijn de huidige ratio, Acid Test (of Quick Asset) ratio Cash Ratios.
• Omzetratio's zoals debiteurenomzet, voorraadomzet en crediteurenomzet

Belangrijkste hoogtepunten van liquiditeitsratio's

• De huidige ratio van Colgate is voor alle jaren significanter dan 1,0. Dit impliceert dat vlottende activa belangrijker zijn dan kortlopende verplichtingen, en misschien heeft Colgate voldoende liquiditeit.
• De snelle verhouding van Colgate ligt tussen 0,6 en 0,7; dit betekent dat Colgates Cash en verhandelbare effecten maar liefst 70% van de kortlopende verplichtingen kunnen betalen. Dit lijkt een goede situatie te zijn voor Colgate.
• De geldinzamelingscyclus was afgenomen van 43 dagen in 2009 tot 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 die dichter bij de 100% ligt, wat een uitstekend rendement betekent voor de aandeelhouders.

Stap 2E - Risicoanalyse van Colgate

Door middel van risicoanalyse proberen we te peilen of de bedrijven hun korte en lange termijn verplichtingen (schulden) zullen kunnen betalen. We berekenen hefboomratio's die gericht zijn op de toereikendheid van activa of het genereren van activa. De tarieven 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 wijst op minder risico 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 bedragen van de inkomensstromen nauwkeurig weerspiegelt, is uitermate belangrijk. Er zijn net zoveel manieren om een ​​inkomstenplanning te maken 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.
• Inflatoire 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.
• Eenheidsmarktomvang en -groei: dit is gedetailleerder dan het voorgaande geval en is handig wanneer prijsstelling in de markt een cruciale variabele is. (Voor een bedrijf met een prijsverlagingsstrategie, bijvoorbeeld, of een eersteklas premium geprijsde nichespeler), bijvoorbeeld de luxeautomarkt
• Volumecapaciteit, capaciteitsbenutting en gemiddelde prijs: deze aannames kunnen belangrijk zijn voor bedrijven waar productiecapaciteit essentieel 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 geïnstalleerde basis (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 essentieel zijn. Het modelleren van de installed base staat centraal (nieuwe toevoegingen aan de vloer, verloop in de grond, doorlopende 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: detailhandelbedrijven worden vaak gemodelleerd op basis van winkels (oude winkels plus nieuwe winkels in elk jaar) en omzet per winkel.
• Op basis van bezettingsfactor: 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 resultatenrekening geen segmentale informatie heeft verstrekt; Als extra informatie heeft Colgate echter enkele details van segmenten verstrekt op pagina 87 Bron - Colgate 2013 - 10K, pagina 86

Aangezien we geen verdere informatie hebben over de functies, 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 element berekend. Nu kunnen we uitgaan van een omzetgroeipercentage op basis van de historische trends en de opbrengsten onder elk onderdeel 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 afschrijving als percentage van de inkomsten en afschrijving volgens een ander 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

Kostenramingen 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 vijf jaar tussen 41% -42% gelegen. Op basis hiervan kunnen we de marges voorspellen.
• Evenzo lagen de verkoop-, 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 zijn opgenomen in de verkoopkosten.
• Hiermee is de resultatenrekening (voorlopig in ieder geval!)

# 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 prominente 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 het werkkapitaalschema, 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 regel vermeld; het is echter inbegrepen in de kostprijs van de verkoop
• Raadpleeg in dergelijke gevallen het kasstroomoverzicht waar u de afschrijving en amortisatiekosten kunt vinden. Merk ook op dat de onderstaande cijfers 1) Afschrijving 2) Afschrijving zijn. Dus wat is het afschrijvingsnummer?
• Eindsaldo voor PPE = Beginsaldo + Kapitaalinvesteringen - Afschrijving - Aanpassing voor activumverkopen (BASE-vergelijking)

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 - Prognose van de investeringsuitgaven

• Er zijn verschillende benaderingen om de kapitaaluitgaven te voorspellen. Een veel voorkomende praktijk is om naar de persberichten, managementprojecties, MD&A te kijken om de visie van het bedrijf op toekomstige kapitaaluitgaven te begrijpen.
• Als het bedrijf richtlijnen heeft gegeven voor toekomstige kapitaaluitgaven, kunnen we die cijfers rechtstreeks opnemen.
• 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 + Kapitaalinvesteringen - Afschrijving - Aanpassing voor activumverkopen (basisvergelijking)
• Het is ingewikkeld 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 te verzoenen, 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 in plaats daarvan alle activa in land, gebouw, machines en andere uitrusting gestopt
• Ook de gebruiksduur van machines en uitrusting is binnen bereik. 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 de opsplitsing 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 onderverdeeld 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 staan ​​vermeld, 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 activa-bijdragen van elk jaar.
• Ook wordt de afschrijving in het eerste jaar gedeeld door twee, aangezien we uitgaan van de halverwege het jaar 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 (extra uitrusting) Zodra we de werkelijke 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 meestal ingewikkeld 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 de positie 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, wat we ook in ons geval 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 ingewikkeld om te projecteren
• Het 10K-rapport van Colgate geeft ons de details van de afschrijvingskosten voor de komende vijf jaar.
• 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 wanneer we ons voorbereiden op 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 investeringen 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 lange termijn items constant voor geprojecteerde 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, zullen we teruggaan naar de resultatenrekening
• Vul de historische gewogen gewogen gemiddelde aandelen en het gewogen gemiddelde aantal 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 primaire en verwaterde aandelen hetzelfde zal blijven 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, dat wil zeggen, het Aandeelhoudersvermogensschema.

# 9 - Financiële modellering - Schema van het eigen vermogen van de aandeelhouders

De volgende stap in deze Financiële Modellering in Excel Training is om te kijken naar het Aandeelhoudersvermogensschema. Het primaire doel van dit programma is om project equity-gerelateerde items zoals Eigen vermogen, dividenden, Inkoop van eigen aandelen, Option opbrengsten, etc. 10K verslag van Colgate geeft ons de details van gewone aandelen en ingekochte eigen aandelen activiteiten in de afgelopen jaren, zoals hieronder weergegeven . Colgate 2013 - 10K, pagina 68

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

• Historisch gezien heeft Colgate aandelen ingekocht, 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 historische teruggekochte bedrag moet worden gerefereerd aan de hand van 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 het aantal ingekochte / 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 het aantal aandelen dat ze van plan zijn terug te kopen. De enige informatie die hun 10K-rapportaandelen geven, is dat ze een terugkoop van maximaal 50 miljoen aandelen hebben goedgekeurd. Colgate 2013 - 10K, pagina 35

• Om het aantal ingekochte aandelen te bepalen, moeten we uitgaan van het Inkoopbedrag. Op basis van het historische terugkoopbedrag heb ik dit aantal voor alle toekomstige jaren als $ 1.500 miljoen genomen.
• Om het aantal teruggekochte aandelen te vinden, hebben we de verwachte impliciete aandelenprijs van de mogelijke terugkoop nodig.
• Werkelijke 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 ingekocht tegen een gemiddelde PE-bandbreedte van 17x - 25x
• Hieronder ziet u de momentopname van Reuters die ons helpt bij het valideren van het PE-assortiment voor Colgate

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 zien = $ bedrag gebruikt voor terugkoop / impliciete prijs.

Stap 9D - Aandelenopties: vul historische gegevens in

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

Daarnaast hebben we ook de optieopbrengst van de kasstroomoverzichten (ongeveer)

• Hiermee zouden we een 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 opties-gegevens 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 de gewogen gemiddelde periode van 2,2 jaar. Colgate 2013 - 10.000, Page 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 aanname is; vanwege een gebrek aan gegevens neem ik echter geen optieproblemen meer op. als 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-methode.

Stap 9G - Dividenden: voorspel de dividenden

• Voorspelling van geschatte dividenden met behulp van de Dividend Payout Ratio.
• 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 uitgaan van een dividenduitkeringsratio van 55% in de komende jaren.
• 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 optie-opbrengsten en de uitgekeerde dividenden, zijn we klaar om het Aandeelhoudersvermogensschema in te vullen. Koppel deze allemaal aan elkaar 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 ruwe percentages (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 uitstekende 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
• De input woog een gemiddeld aantal aandelen en het effect van aandelenopties voor de historische jaren.

Stap 10B - Koppel uitgiften en terugkopen van aandelen uit 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 de 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 verder gaan met ons volgende en laatste schema in deze financiële modellering, dwz het schuldschema. Tot nu toe zijn er maar een paar onvolledige zaken

• 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

Graad 12A - Stel een schuldoverzicht op

• Verwijs naar 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 gaan ervan uit dat Colgate elk jaar minimaal $ 500 miljoen zou willen behouden.

Sla langetermijnschuldenuitgifte / -terugbetalingen over, contanten beschikbaar voor doorlopende kredietfaciliteit en revolver-sectie voor nu Uit Colgate's 10K-rapport; we noteren de beschikbare details op Revolved Credit Facility Colgate 2013 - 10K, pagina 35

Ook wordt in aanvullende informatie over schulden de gecommitteerde schuldaflossingen op lange termijn verstrekt. 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 opgenomen leningen / playdowns.

Gebruik de cash sweep-formule, zoals hieronder weergegeven, om de discretionaire leningen / afbetalingen 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 - Principal Link-schulden en revolveropnames naar cashflows

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 schuld 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 enige 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 met de basis en neemt je mee naar het gevorderde niveau van Investment Banking Job. Deze cursus is verdeeld in vijf delen -

• Deel 1 - Training in investeringsbankieren - Kerncursussen
  (26 cursussen)
• Deel 2 - Geavanceerde training voor investeringsbankieren
  (20 cursussen)
• Deel 3 - Add-ons voor investeringsbankieren
  (13 cursussen)
• Deel 4 - Investment Banking Foundation Courses
  (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. Hartelijk dank en pas op. Veel plezier met leren!