Finansiel modellering i Excel (trin for trin gratis guide + skabelon)

Indholdsfortegnelse

Hvad er finansiel modellering i Excel?

Finansiel modellering i Excel er processen med at opbygge en finansiel model til at repræsentere en transaktion, operation, fusion, erhvervelse, finansiel information for at analysere, hvordan en ændring i en variabel kan påvirke det endelige afkast for at træffe en beslutning om en eller flere af førnævnte finansielle transaktioner.

Hvad er finansiel modellering i Excel?

Finansiel modellering i Excel er overalt på nettet. Der er skrevet meget om læring af økonomisk modellering; dog er de fleste af de økonomiske modelleringselementer de samme. Dette går ud over det sædvanlige gibberish og udforsker praktisk økonomisk modellering som brugt af investeringsbankfolk og forskningsanalytikere.

I denne gratis økonomiske modellerings Excel-guide vil jeg tage et eksempel på Colgate Palmolive og udarbejde en fuldt integreret økonomisk model fra bunden.

Denne guide er på over 6000 ord og tog mig tre uger at gennemføre. Gem denne side til fremtidig reference og glem ikke at dele den :-)

VIGTIGST - Download Colgate Financial-modellerings Excel-skabelonen for at følge instruktionerne

Download Colgate Financial Model Template

Lær trin for trin økonomisk modellering i Excel

Finansiel modellering i Excel-træning - Læs mig først

Trin 1 - Download Colgate Financial Model Template. Du bruger denne skabelon til vejledningen

Download Colgates økonomiske model

Indtast e-mail-adresse Ved at fortsætte ovenstående trin accepterer du vores brugsbetingelser og fortrolighedspolitik.

Trin 2 - Bemærk venligst, at du får to skabeloner - 1) uløst Colgate Palmolive Financial Model 2) Løst Colgate Palmolive Financial Model

Trin 3- Du vil arbejde på den uløste Colgate Palmolive Financial Model Template . Følg trinvise instruktioner for at udarbejde en fuldt integreret finansiel model.

Trin 4 - Glad læring!

Indholdsfortegnelse

Jeg har gjort det nemt at navigere i indholdsfortegnelsen, så du kan udføre denne økonomiske modellering

Hvordan bygger man en finansiel model?
# 1 - Colgates økonomiske model - historisk
# 2 - Forholdsanalyse af Colgate Palmolive
# 3 - Fremskrivning af resultatopgørelsen
# 4- Arbejdskapitalplan
# 5 - Afskrivningsplan
# 6 - Afskrivningsplan
# 7 - Andet langtidsplan
# 8 - Udfyldelse af resultatopgørelsen
# 9 - Aktionærplan for aktier
# 10 - Aktier udestående tidsplan
# 11 - Udfyldelse af pengestrømsopgørelser
# 12- Gæld og rente tidsplan anbefales
Finansiel modelleringskursus
Gratis økonomiske modeller

Hvis du er ny inden for finansiel modellering, skal du kigge på denne vejledning om Hvad er finansiel modellering?

Hvordan bygger jeg en finansiel model i Excel?

Lad os se på, hvordan en finansiel model er bygget fra bunden. Denne detaljerede økonomiske modelleringsvejledning giver dig en trinvis vejledning til oprettelse af en finansiel model. Den primære tilgang i denne økonomiske modelleringsvejledning er Modular. Det modulære system betyder i det væsentlige at opbygge kerneopgørelser som resultatopgørelse, balance og pengestrømme ved hjælp af forskellige moduler / tidsplaner. Hovedfokus er at forberede hvert udsagn trin for trin og forbinde alle understøttende programmer til kernedeklarationerne efter afslutningen. Jeg kan forstå, at dette måske ikke er klart nu; dog vil du indse, at dette er meget let, når vi bevæger os fremad. Du kan se nedenfor forskellige finansielle modelleringsplaner / -moduler -

Vær opmærksom på følgende -

Kerneopgørelserne er resultatopgørelse, balance og pengestrømme.
De forskellige tidsplaner er afskrivningsplanen, arbejdskapitalplanen, immaterielle tidsplanen, aktionærens egenkapitalplan, andre langsigtede poster, gældsplanen osv.
De forskellige tidsplaner er knyttet til kernedeklarationerne, når de er afsluttet.
I denne økonomiske modelleringsvejledning bygger vi en trinvis integreret økonomisk model af Colgate Palmolive fra bunden.

# 1 - Finansiel modellering i Excel - Projekter historik

Det første trin i Financial Modelling Guide er at forberede Historicals.

Trin 1A - Download Colgates 10K-rapporter

”Finansielle modeller forberedes i excel, og de første skridt starter med at vide, hvordan branchen har haft det de sidste år. At forstå fortiden kan give os værdifuld indsigt i forbindelse med virksomhedens fremtid. Derfor er det første trin at downloade alle virksomhedens økonomi og udfylde det samme i et excel-ark. For Colgate Palmolive kan du downloade årsrapporter for Colgate Palmolive fra deres afdeling for investorrelationer. Når du klikker på "Årsrapport", finder du vinduet som vist nedenfor -

Trin 1B - Opret regnearket Historiske regnskaber

Hvis du downloader 10K af 2013, vil du bemærke, at der kun er to års regnskabsdata tilgængelige. Med henblik på finansiel modellering i Excel er det anbefalede datasæt dog at have de sidste 5 års regnskaber. Download de sidste 3 år af årsrapporten, og udfyld det historiske.
Mange gange virker disse opgaver for kedelige og kedelige, da det kan tage meget tid og energi at formatere og sætte excel i det ønskede format.
Man skal dog ikke glemme, at dette er det arbejde, du kun skal udføre en gang for hvert selskab, og ved at udfylde historikken hjælper en analytiker med at forstå tendenser og årsregnskaber.
Så spring ikke dette over, download dataene og udfyld dataene (selvom du føler at dette er æselens arbejde ;-))

Hvis du ønsker at springe dette trin over, kan du downloade Colgate Palmolive Historical Model direkte her.

Colgate resultatopgørelse med historisk befolket

Colgate Balance Historiske Data

# 2 - Forholdsanalyse

Det andet trin i finansiel modellering i Excel er at udføre forholdsanalyse.

En nøgle til at lære økonomisk modellering i Excel er at kunne udføre grundlæggende analyse. Hvis elementaranalysen eller forholdsanalysen er noget nyt for dig, anbefaler jeg, at du læser lidt på internettet. Jeg har til hensigt at tage en dybdegående forholdsanalyse i et af mine kommende indlæg, men her er et hurtigt øjebliksbillede af Colgate Palmolive-forholdet.

VIGTIGT - Bemærk, at jeg har opdateret forholdsanalysen af Colgate i et separat indlæg. Se venligst på denne omfattende forholdsanalyse.

Trin 2A - Lodret analyse af Colgate

I resultatopgørelsen er den lodrette analyse et universelt værktøj til måling af virksomhedens relative præstation fra år til år med hensyn til omkostninger og rentabilitet. Det skal altid medtages som en del af enhver økonomisk analyse. Her beregnes procenter i forhold til nettosalget, der anses for at være 100%. Denne vertikale analyseindsats i resultatopgørelsen kaldes ofte marginalanalyse, da den giver de forskellige margener vedrørende salg.

Vertikale analyseresultater

Resultatmargen var steget med 240 basispoint fra 56,2% i 2007 til 58,6% i 2013. Dette skyldes primært faldt salgsomkostninger
Driftsresultat eller EBIT har også vist forbedrede marginer og steg fra 19,7% i 2007 til 22,4% i 2012 (en stigning på 70 basispoint). Dette skyldtes faldende salgs- og administrationsomkostninger. Bemærk dog, at EBIT-marginerne faldt i 2013 til 20,4% på grund af øgede "Andre omkostninger." Tjek også forskellen mellem EBIT vs EBITDA
Nettovinstmarginen steg fra 12,6% i 2007 til 14,5% i 2012. Resultatmargenen i 2013 faldt dog til 12,9%, primært som følge af øgede “andre omkostninger”.
Indtjening pr. Aktie er støt steget fra regnskabsåret 2007 til regnskabsåret 2012. Der var dog en lille nedgang i EPS for FY2013
Bemærk også, at afskrivninger og afskrivninger er særskilt angivet i resultatopgørelsen. Det er inkluderet i salgsomkostningerne.

Trin 2B - Horisontal analyse af Colgate

Horisontal analyse er en teknik, der bruges til at evaluere tendenser over tid ved at beregne procentvise stigninger udmærker sig eller falder i forhold til et basisår. Det giver et analytisk link mellem konti beregnet på forskellige datoer ved hjælp af valutaen med forskellige beføjelser til at købe. I virkeligheden indekserer denne analyse rapporterne og sammenligner udviklingen af disse over tid. Som med den vertikale analysemetode vil problemer dukke op, der skal undersøges og suppleres med andre økonomiske analyseteknikker. Fokus er at lede efter symptomer på problemer, der kan diagnosticeres ved hjælp af yderligere metoder.

Lad os se på den vandrette analyse af Colgate.

Horisontale analyseresultater

Vi ser, at nettosalget steg med 2,0% i 2013.
Bemærk også tendensen i salgsomkostningerne. Vi ser, at de ikke er vokset i samme andel som salg.
Disse observationer er yderst nyttige, mens vi laver økonomisk modellering i Excel.

Trin 2C - Colgate's likviditetsforhold

Likviditetsforhold måler forholdet mellem de mere likvide aktiver i en virksomhed (de der er let at konvertere til kontanter) og kortfristede forpligtelser. De mest almindelige likviditetsforhold er aktuelt forhold, syretest (eller hurtig aktiv) forhold kontantforhold.
Omsætningsforhold som omsætning af tilgodehavender, lageromsætning og omsætning af gæld

Vigtigste højdepunkter i likviditetsforhold

Den nuværende forholdsgrad for Colgate er mere signifikant end 1,0 i alle år. Dette indebærer, at kortfristede aktiver er vigtigere end kortfristede forpligtelser, og måske har Colgate tilstrækkelig likviditet.
Hurtigforholdet mellem Colgate er i området 0,6-0,7; dette betyder, at Colgates Cash og omsættelige værdipapirer kan betale så meget som 70% af kortfristede forpligtelser. Dette ser ud som en god situation at være i for Colgate.
Kontantindsamlingscyklus var faldet fra 43 dage i 2009 til 39 dage i 2013. Dette skyldes primært reduktionen i en tilgodehavendeperiode for tilgodehavender.

Se også denne detaljerede artikel om Cash Conversion Cycle.

Trin 2D - Driftsforhold for Colgate

Rentabilitetsforhold en virksomheds evne til at generere indtjening i forhold til salg, aktiver og egenkapital

Vigtigste højdepunkter - Colgate's rentabilitetsforhold

Som vi kan se fra ovenstående tabel, har Colgate en ROE på tættere på 100%, hvilket indebærer fremragende afkast til kapitalandele.

Trin 2E - Risikoanalyse af Colgate

Gennem risikoanalyse forsøger vi at måle, om virksomhederne er i stand til at betale sine korte og langsigtede forpligtelser (gæld). Vi beregner gearingsforhold, der fokuserer på tilstrækkeligheden af aktiver eller frembringelse fra aktiver. Priser, der ses på er

Gæld til egenkapitalforhold
Gældskvote
Rentedækningsgrad
Gæld til egenkapitalforhold er støt steget til et højere niveau på 2,23x. Dette betyder øget finansiel gearing og risici på markedet
Rentedækningsgraden er dog meget høj, hvilket indikerer mindre risiko for rentebetaling.

# 3 - Finansiel modellering i Excel - Projekter resultatopgørelsen

Det tredje trin i finansiel modellering er at forudsige resultatopgørelsen, hvor vi starter med modellering af salgs- eller indtægtsposter.

Trin 3A - Indtægtsfremskrivninger

For de fleste virksomheder er indtægterne en grundlæggende drivkraft for den økonomiske præstation. En veldesignet og logisk indtægtsmodel, der afspejler typen og størrelsen af indkomststrømme nøjagtigt, er ekstremt vigtig. Der er lige så mange måder at oprette en indtægtsplan på, som der er virksomheder. Nogle almindelige typer inkluderer:

Salgsvækst: Antagelsen om salgsvækst i hver periode definerer ændringen fra den foregående periode. Dette er en enkel og almindeligt anvendt metode, men giver ingen indsigt i komponenterne eller dynamikken i vækst.
Inflations- og volumen / blandingseffekter: I stedet for en simpel vækstantagelse anvendes en prisinflationsfaktor og en volumenfaktor. Denne nyttige tilgang tillader modellering af faste og variable omkostninger i multiproduktvirksomheder og tager højde for pris versus volumenbevægelser.
Enhedsvolumen, volumenændring, gennemsnitspris og prisændring: Denne metode er passende for virksomheder, der har et simpelt produktmix; det tillader analyse af virkningen af flere nøglevariabler.
Dollarmarkedsstørrelse og -vækst: Markedsandel og ændring i andel - Nyttig i tilfælde, hvor der findes information om markedsdynamik, og hvor disse antagelser sandsynligvis vil være grundlæggende for en beslutning. For eksempel telekomindustrien.
Enhedsmarkedsstørrelse og -vækst: Dette er mere detaljeret end det foregående tilfælde og er nyttigt, når prisfastsættelse på markedet er en afgørende variabel. (For et firma med en prisdiskonteringsstrategi for eksempel eller en bedst af racen prissat niche-spiller) f.eks. Markedet for luksusbiler
Volumenkapacitet, kapacitetsudnyttelsesgrad og gennemsnitspris: Disse antagelser kan være vigtige for virksomheder, hvor produktionskapacitet er afgørende for beslutningen. (For eksempel ved køb af ekstra kapacitet eller for at afgøre, om udvidelsen vil kræve nye investeringer.)
Produkt tilgængelighed og priser
Omsætningen var drevet af investering i kapital, markedsføring eller F&U
Omsætningsbaseret på installeret base (fortsat salg af dele, engangsartikler, service og tilføjelser osv.). Eksempler inkluderer klassiske barberbladvirksomheder og virksomheder som computere, hvor salg af service, software og opgraderinger er afgørende. Modellering af den installerede base er nøglen (nye tilføjelser til gulvet, slid i jorden, fortsatte indtægter pr. Kunde osv.).
Medarbejderbaseret: For eksempel indtægter fra professionelle servicevirksomheder eller salgsbaserede virksomheder såsom mæglere. Modellering skal fokusere på netto bemanding, indtægter pr. Medarbejder (ofte baseret på fakturerbare timer). Mere detaljerede modeller inkluderer anciennitet og andre faktorer, der påvirker prisfastsættelsen.
Butik, facilitet eller firkantede optagelser: Detailvirksomheder modelleres ofte ud fra basis af butikker (gamle butikker plus nye butikker hvert år) og indtægter pr. Butik.
Belægningsfaktorbaseret: Denne tilgang gælder for flyselskaber, hoteller, biografer og andre virksomheder med lave marginale omkostninger.

Fremskrivning af Colgate-indtægter

Lad os nu se på Colgate 10K 2013-rapporten. Vi bemærker, at Colgate i resultatopgørelsen ikke har leveret segmentoplysninger; som et stykke yderligere information har Colgate dog givet nogle detaljer om segmenter på side 87 kilde - Colgate 2013 - 10K, side 86

Da vi ikke har yderligere oplysninger om funktionerne, vil vi projicere det fremtidige salg af Colgate på baggrund af disse tilgængelige data. Vi vil bruge salgsvækstmetoden på tværs af segmenter til at udlede prognoserne. Se nedenstående billede. Vi har beregnet vækstraten fra år til år for hvert element. Nu kan vi antage en salgsvækstprocent baseret på de historiske tendenser og projicere indtægterne under hver del. Det samlede nettosalg er summen af mundtlig, personlig pleje og hjemmepleje og ernæring til kæledyr.

Trin 3B - Omkostningsfremskrivninger

Procent af indtægter: Enkel, men giver ingen indsigt i nogen gearing (stordriftsfordele eller faste omkostningsbyrder
Andre omkostninger end afskrivninger i procent af indtægterne og afskrivninger fra en anden tidsplan: Denne fremgangsmåde er virkelig det minimum, der er acceptabelt i de fleste tilfælde, og tillader kun delvis analyse af driftsudnyttelse.
Variable omkostninger baseret på omsætning eller volumen, faste omkostninger baseret på historiske tendenser og afskrivninger fra en separat tidsplan: Denne tilgang er det minimum, der er nødvendigt for sensitivitetsanalyse af rentabilitet baseret på flere indtægtsscenarier

Omkostningsfremskrivninger for Colgate

Ved projicering af omkostningerne vil den vertikale analyse, der er foretaget tidligere, være nyttig. Lad os se på den lodrette analyse -

Da vi allerede har forudsagt salg, er alle de andre omkostninger nogle margener for dette salg.
Metoden er at tage retningslinjerne fra de historiske omkostnings- og udgiftsmargener og derefter forudsige den fremtidige margin.
F.eks. Har salgsomkostningerne været i intervallet 41-42% i de sidste fem år. Vi kan se på prognoser for margenerne på dette grundlag.
Ligeledes har salgs-, generelle og administrative udgifter historisk ligget i intervallet 34-36%. Vi kan antage den fremtidige SG&A omkostningsmargen på dette grundlag. På samme måde kan vi fortsætte med et andet sæt udgifter.

Ved hjælp af ovenstående margener kan vi finde de faktiske værdier ved hjælp af tilbageberegninger.

Til beregning af hensættelsen til skat bruger vi antagelsen om effektiv skattesats.

Bemærk også, at vi ikke udfylder rækken "Renteudgifter (indtægter)", da vi vil få en ny gennemgang af resultatopgørelsen på et senere tidspunkt.
Renteudgifter og renteindtægter.
Vi har heller ikke beregnet afskrivninger, som allerede er inkluderet i salgsomkostningerne.
Dette fuldender resultatopgørelsen (i det mindste i øjeblikket!)

# 4 - Finansiel modellering - Arbejdskapitalplan

Nu hvor vi har afsluttet resultatopgørelsen, er det fjerde trin i finansiel modellering at se på arbejdskapitalplanen.

Nedenfor er de trin, der skal følges for arbejdskapitalplanen

Trin 4A - Forbind nettosalget og salgsomkostningerne

Trin 4B - Henvisning til balancedata relateret til driftskapital

Henvis til tidligere data fra balancen
Beregn netto arbejdskapital
Ankomme til en stigning / fald i arbejdskapitalen
Bemærk, at vi ikke har medtaget kortfristet gæld og likvider i arbejdskapitalen. Vi vil håndtere gæld og likvide beholdninger separat.

Trin 4C - Beregn omsætningsforholdet

Beregn historiske forhold og procenter
Brug slut- eller gennemsnitsbalancen
Begge er acceptable, da lang konsistens opretholdes

Trin 4D - Udfyld antagelserne for fremtidige driftskapitalposter

Visse varer uden en fremtrædende driver antages normalt med konstante beløb
Sørg for, at antagelser er rimelige og i tråd med forretningen

Trin 4E - Foretag de fremtidige driftskapitalbalancer

Trin 4F - Beregn ændringerne i arbejdskapital

Ankomst til pengestrømme baseret på individuelle linjeposter
Sørg for, at skiltene er korrekte!

Trin 4G - Forbind den forventede arbejdskapital til balancen

Trin 4H - Link arbejdskapital til pengestrømsopgørelsen

# 5 - Finansiel modellering i Excel - Afskrivningsplan

Efter afslutningen af arbejdskapitalplanen er det næste trin i denne finansielle modellering projektet Capex of Colgate og projekterer afskrivninger og aktiver. Colgate 2013 - 10K, Side 49

Afskrivninger og afskrivninger leveres ikke som en separat linjepost; det er dog inkluderet i salgsomkostningerne
I sådanne tilfælde skal du se på pengestrømsopgørelserne, hvor du finder afskrivnings- og afskrivningsomkostningerne. Bemærk også, at nedenstående tal er 1) Afskrivninger 2) afskrivninger. Så hvad er afskrivningsnummeret?
Afslutningsbalance for PPE = begyndelsesbalance + Capex - Afskrivninger - Justering for aktivssalg (BASE-ligning)

Trin 5A - Link nettosalget i afskrivningsplanen

Konfigurer linjeposterne
Reference Nettoomsætning
Input tidligere kapitaludgifter
Ankomme til Capex som en% af nettosalget

Trin 5B - Prognoser kapitaludgifterne

For at forudsige kapitaludgifterne er der forskellige tilgange. En almindelig praksis er at se på pressemeddelelser, ledelsesfremskrivninger, MD&A for at forstå virksomhedens syn på fremtidige kapitaludgifter
Hvis virksomheden har givet vejledning om fremtidige kapitaludgifter, kan vi tage disse tal direkte.
Men hvis Capex-numrene ikke er direkte tilgængelige, kan vi beregne det groft ved hjælp af Capex som% af salget (som gjort nedenfor)
Brug din vurdering baseret på branchekendskab og andre rimelige drivere.

Trin 5C - Reference tidligere oplysninger

Vi bruger Ending Balance for PPE = Begyndelsesbalance + Capex - Afskrivninger - Justering for aktivssalg (BASE-ligning)
Det er kompliceret at afstemme tidligere PP&E på grund af omskrivninger, salg af aktiver osv.
Det anbefales derfor ikke at forene tidligere PV, da det kan føre til en vis forvirring.

Colgates afskrivningspolitik

Vi bemærker, at Colgate ikke eksplicit har givet en detaljeret opsplitning af aktiverne. De har i stedet indkapslet alle aktiver i jord, bygning, maskiner og andet udstyr
Der er også brugstid for maskiner og udstyr inden for rækkevidde. I dette tilfælde bliver vi nødt til at gætte noget for at nå den gennemsnitlige brugstid, der er tilbage for aktiverne
Der er heller ikke vejledning til brugstid for "Andet udstyr". Vi bliver nødt til at estimere levetiden for andet udstyr

Colgate 2013 - 10K, side 55

Nedenfor er opdelingen af 2012, 2013, detaljer om ejendom, anlæg og udstyr

Colgate 2013 - 10K, side 91

Trin 5D - estimer opdelingen af ejendomsanlæg og udstyr (PPE)

Find først aktivvægtene for den aktuelle PPE (2013)
Vi antager, at disse aktivvægte i 2013 PPE vil fortsætte fremadrettet
Vi bruger denne aktivvægt til at beregne fordelingen af de anslåede kapitaludgifter

Trin 5E - Anslå afskrivning af aktiver

Vær opmærksom på, at vi ikke beregner afskrivning på jord, da jord ikke er et afskrivbart aktiv
Til beregning af afskrivninger fra bygningsforbedringer bruger vi først nedenstående struktur.
Afskrivninger her er opdelt i to dele - 1) afskrivninger fra bygningsforbedringsaktivet, der allerede er opført på balancen, 2) afskrivninger fra fremtidige bygningsforbedringer.
Til beregning af afskrivninger fra bygningsforbedringer, der er angivet på aktivet, bruger vi den enkle afskrivningsmetode.
Til beregning af fremtidig afskrivning transponerer vi først Capex ved hjælp af TRANSPOSE-funktionen i Excel.
Vi beregner afskrivningen fra aktivbidrag for hvert år.
Også det første års afskrivning divideres med to, da vi antager, at midtårskonventionen for aktivudbredelse.

Samlet afskrivning af bygningsforbedring = afskrivning fra bygningens forbedringer Aktiv, der allerede er opført på balancen + afskrivninger fra fremtiden Bygningsforbedringer Ovenstående proces til beregning af afskrivninger bruges til at beregne afskrivningen på 1) Produktionsudstyr og maskiner og 2) andet udstyr som vist nedenfor.

Andre typer udstyr

Samlet afskrivning af Colgate = Afskrivning (bygningsforbedringer) + Afskrivninger (Maskiner og udstyr) + Afskrivninger (ekstraudstyr) Når vi først har fundet ud af de reelle afskrivningstal, kan vi sætte det i BASE-ligningen som vist nedenfor

Med dette får vi Ending Net PP&E tal for hvert af årene

Trin 5F - Link nettopP&E til balancen

# 6 - Afskrivningsplan

Det sjette trin i denne økonomiske modellering i Excel er at forudsige afskrivningerne. Vi har to brede kategorier at overveje her - 1) Goodwill og 2) Andre immaterielle aktiver.

Trin 6A - Prognoser for goodwill

Colgate 2013 - 10K, side 61

Goodwill kommer på balancen, når et selskab erhverver et andet selskab. Det er normalt kompliceret at projicere goodwill i de kommende år.
Goodwill underkastes dog årligt værdiforringelsestest, der udføres af virksomheden selv. Analytikere er ikke i stand til at udføre sådanne tests og udarbejde skøn over værdiforringelse.
De fleste analytikere projicerer ikke goodwill; de holder dette bare konstant, hvilket vi også vil gøre i vores tilfælde.

Trin 6B - Prognose for andre immaterielle aktiver

Som bemærket i Colgates 10K-rapport er størstedelen af det endelige liv immaterielle relateret til Sanex-erhvervelsen
“Tilføjelser til immaterielle aktiver” er også komplicerede at projicere
Colgates 10K-rapport giver os detaljerne om de næste fem års afskrivningsomkostninger.
Vi bruger disse skøn i vores finansielle model Colgate 2013 - 10K, side 61

Trin 6C - Afslutte netto immaterielle aktiver er knyttet til "Andre immaterielle aktiver."

Trin 6D - link afskrivninger og pengestrømsopgørelser

Trin 6E - Link Capex og tilføjelse til immaterielle aktiver til pengestrømsopgørelser

# 7 - Andet langtidsplan

Det næste trin i denne økonomiske modellering er at udarbejde den anden langsigtede tidsplan. Dette er når vi forbereder os på "resterne", der ikke har specifikke drivere til prognoser. For Colgate var de øvrige langfristede poster (rester) udskudt skat (forpligtelser og aktiver), andre investeringer og andre forpligtelser.

Trin 7A - Reference til de historiske data fra balancen

Beregn også ændringerne i disse emner.

Trin 7B - Prognoser de langsigtede aktiver og passiver

Hold de langsigtede emner konstante i forventede år, hvis der ikke er nogen synlige drivere
Knyt de forventede langsigtede poster til balancen som vist nedenfor

Trin 7C - Henvisning til andre langfristede poster til balancen

Trin 7D - Forbind de langsigtede poster til pengestrømsopgørelsen

Vær opmærksom på, at hvis vi har holdt de langsigtede aktiver og passiver konstant, ville ændringen, der strømmer til pengestrømsopgørelsen, være nul.

# 8 - Finansiel modellering i Excel - Udfyldelse af resultatopgørelsen

Inden vi går videre i denne Excel-baserede finansielle modellering, vil vi gå tilbage og se på resultatopgørelsen
Udfyld det historiske basisvægtede gennemsnit og det fortyndede vejede gennemsnit af antallet af aktier
Disse tal er tilgængelige i Colgates 10K-rapport

Trin 8A - Henvisning til de basale og fortyndede aktier

Antag på dette tidspunkt, at det fremtidige antal primære og fortyndede aktier forbliver det samme som i 2013.

Trin 8B - Beregn grundlæggende og fortyndet indtjening pr. Aktie.

Med dette er vi klar til at gå til vores næste tidsplan, dvs. Aktionærens egenkapitalplan.

# 9 - Finansiel modellering - Aktionærens egenkapitalplan

Det næste trin i denne økonomiske modellering i Excel-uddannelse er at se på aktionærens aktieplan. Det primære mål med denne tidsplan er at projicere aktierelaterede poster som aktionærers egenkapital, udbytte, aktietilbagekøb, optionsprovenu osv. Colgates 10K-rapport giver os detaljerne om almindelige aktie- og statsejeraktiviteter i de seneste år, som vist nedenfor . Colgate 2013 - 10K, side 68

Trin 9A - Genkøb af aktier: Udfyld de historiske tal

Historisk har Colgate tilbagekøbt aktier, som vi kan se tidsplanen ovenfor.
Befolk Colgates aktietilbagekøb (millioner) i excel-arket.
Link den historisk fortyndede EPS fra resultatopgørelsen
Den historiske tilbagekøbte størrelse skal henvises fra pengestrømsopgørelserne.

Se også på Accelerated Share Repurchase.

Trin 9B - Aktietilbagekøb: Beregn PE-multiple (EPS-multiple)

Beregn den underforståede gennemsnitskurs, som Colgate historisk har tilbagekøbt aktier med. Dette beregnes som det tilbagekøbte beløb / antal aktier.
Beregn PE-multiple = Implicit aktiekurs / EPS

Trin 9C - Aktietilbagekøb: Find Colgates aktie tilbagekøbt

Colgate har ikke givet nogen officiel meddelelse om, hvor mange aktier de agter at tilbagekøbe. De eneste oplysninger om, at deres 10K rapporterer aktier, er, at de har godkendt et tilbagekøb på op til 50 millioner aktier. Colgate 2013 - 10K, Side 35

For at finde antallet af tilbagekøbte aktier skal vi antage aktietilbagekøbsbeløbet. Baseret på det historiske tilbagekøbsbeløb har jeg taget dette tal som $ 1.500 millioner i alle de kommende år.
For at finde antallet af tilbagekøbte aktier har vi brug for den forventede implicitte aktiekurs for den potentielle tilbagekøb.
Faktisk aktiekurs = antaget PE multiplex EPS.
Fremtidig tilbagekøb af PE-multipel kan antages baseret på historiske tendenser. Vi bemærker, at Colgate har tilbagekøbt aktier til et gennemsnitligt PE-interval på 17x - 25x
Nedenfor er snapshotet fra Reuters, der hjælper os med at validere PE-serien til Colgate

www.reuters.com

I vores tilfælde har jeg antaget, at alle fremtidige tilbagekøb af Colgate vil være på et PE-multiplum på 19x.
Ved hjælp af PE på 19x kan vi finde den underforståede pris = EPS x 19
Nu hvor vi har fundet den underforståede pris, kan vi se antallet af tilbagekøbte aktier = $ beløb brugt til tilbagekøb / underforstået pris.

Trin 9D - Aktieoptioner: Udfyld historiske data

Fra oversigten over almindelig aktie og egenkapital kender vi antallet af udnyttede optioner hvert år.

Derudover har vi også optionsprovenuet fra pengestrømsopgørelserne (ca.)

Med dette skal vi være i stand til at finde en effektiv udrykningspris.

Colgate 2013 - 10K, Side 53

Bemærk også, at aktieoptionerne har kontraktmæssige betingelser på seks år og optjenes over tre år. Colgate 2013 - 10K, Side 69

Med disse data udfylder vi indstillingsdataene som nedenfor. Vi bemærker også, at den vejede gennemsnitlige udnyttelseskurs for aktieoptioner for 2013 var $ 42, og antallet af udnyttelige optioner var 24.151 millioner Colgate 2013 - 10K, Side 70

Trin 9E - Aktieoptioner: Find optionen fortsætter.

Ved at sætte disse tal i vores optionsdata nedenfor bemærker vi, at optionens provenu er $ 1.014 milliarder.

Trin 9F - Aktieoptioner: Prognosebegrænsede data om aktieenhed

Ud over aktieoptionerne gives der begrænsede aktieenheder til medarbejderne med en vejet gennemsnitlig periode på 2,2 år Colgate 2013 - 10K, Side 81

Udfylde disse data i indstillingsdatasættet For enkelheds skyld har vi ikke projiceret udstedelse af optioner (jeg ved, at dette ikke er den rigtige antagelse, men på grund af manglende data tager jeg ikke flere optionsproblemer frem. Vi har netop taget disse som nul, som fremhævet i det grå område ovenfor. Derudover forventes de begrænsede lagerenheder at være 2,0 millioner fremadrettet.

Se også på Treasury Stock Method.

Trin 9G - Udbytte: Prognoser udbyttet

Forventet estimeret udbytte ved hjælp af udbytteforholdet.
Fast udbytteudbetaling pr. Aktieudbetaling
Fra 10K-rapporterne udtrækker vi alle tidligere oplysninger om udbytte.
Med oplysningerne om udbetalt udbytte kan vi finde ud af udbytteforholdet = Samlet udbetalt udbytte / nettoindkomst.
Jeg har beregnet udbyttet på Colgate som vist nedenfor - Vi bemærker, at udbyttet i udbyttet stort set har ligget i intervallet 50-60%. Lad os antage udbyttet på 55% i de kommende år.
Vi kan også forbinde den forventede nettoindkomst fra resultatopgørelsen.
Ved hjælp af både den forventede nettoindkomst og udbytteforholdet kan vi finde det samlede udbetalte udbytte.

Trin 8H - Prognosekapital i sin helhed

Med prognosen for aktietilbagekøb, optionsprovenu og udbetalt udbytte er vi klar til at gennemføre aktionærens aktieplan. Forbind alle disse for at finde den endelige aktiesaldo for hvert år som vist nedenfor.

Trin 9I - Forbind afslutning af aktionærens egenkapital til balancen

Trin 9J - Link udbytte, tilbagekøb af aktier og optioner fortsætter til CF

# 10 - Aktier udestående tidsplan

Det næste trin i denne online økonomiske modellering i Excel-træning er at se på Aktiernes Oustanding Schedule. Oversigt over udestående aktier

Grundaktier - faktiske og gennemsnitlige
Fang tidligere effekter af optioner og konvertibler efter behov
Fortyndede aktier - gennemsnit
Reference Aktier tilbagekøbte og nye aktier fra udnyttede optioner
Beregn forventede rå procenter (faktisk)
Beregn gennemsnitlige basale og fortyndede aktier
Reference projekterede aktier til resultatopgørelse (husk resultatopgørelse opbygget!)
Input historisk deler udestående information
Bemærk : Denne tidsplan er almindeligt integreret med aktieplanen

Trin 10A - Indtast de historiske tal fra 10K-rapporten

Udstedte aktier (faktisk realisering af optioner) og tilbagekøbte aktier kan henvises til fra aktionærens egenkapitalplan
Input vægtede et gennemsnitligt antal aktier og effekten af aktieoptioner for de historiske år.

Trin 10B - Link aktieudstedelser og tilbagekøb fra aktieplanen.

Grundlæggende aktier (slutter) = grundlæggende aktier (begynder) + aktieudstedelser - tilbagekøbte aktier.

Trin 10C - Find de grundlæggende vejede gennemsnitlige andele,

vi finder et gennemsnit på to år, som vist nedenfor.
Tilføj også effekten af optioner og begrænsede aktieenheder (der henvises til fra aktionærens egenkapitalplan) for at finde de fortyndede vejede gennemsnitlige aktier.

Trin 10D - Link grundlæggende og fortyndede vægtede aktier til resultatopgørelsen

Nu hvor vi har beregnet det fortyndede vægtede gennemsnit af aktier, er det tid for os at opdatere det samme i resultatopgørelsen.
Forbind forventede fortyndede vejede gennemsnitlige aktier til resultatopgørelsen som vist nedenfor

Med dette fuldfører vi aktieplanen og tidspunktet for at gå til vores næste sæt udsagn.

# 11 - Udfyldelse af pengestrømsopgørelser

Det er vigtigt for os fuldt ud at udfylde pengestrømsopgørelserne, før vi går videre til vores næste og sidste tidsplan i denne finansielle modellering, dvs. gældsplanen. Indtil dette trin er der kun et par ufuldstændige ting

Resultatopgørelse - renteudgifter / indtægter er ufuldstændige på dette stadium
Balance - kontante og gældsposter er ufuldstændige på dette stadium

Trin 11A - Beregn pengestrøm til finansieringsaktiviteter

Tjek også pengestrøm fra finansiering

Trin 11B - Find nettostigning (fald) i likvide beholdninger

Trin 11C = Udfyld pengestrømsopgørelserne

Find likvide midler ved årets udgang ved årets udgang.

Trin 11D - Knyt kontanter og kontantekvivalenter til balancen.

Nu er vi klar til at tage os af vores sidste og sidste tidsplan, dvs. gælds- og renteplan

# 12- Finansiel modellering i Excel - Gælds- og renteoversigt

Det næste trin i denne online finansielle modellering er at gennemføre gælds- og renteoversigten. Oversigt over gæld og renter - tidsplan

Grad 12A - Opsæt en gældsplan

Henvis til pengestrømmen, der er tilgængelig til finansiering
Henvis til alle aktiekilder og anvendelser af kontanter

Trin 12B - Beregn pengestrøm fra tilbagebetaling af gæld

Henvis til den indledende kontantsaldo fra balancen
Træk en minimumsbalance. Vi har antaget, at Colgate gerne vil beholde mindst 500 millioner dollars hvert år.

Spring over langvarig gældsudstedelse / tilbagebetaling, kontanter til rådighed for revolverende kreditfacilitet og revolver-sektion for nu Fra Colgates 10K-rapport; vi bemærker de tilgængelige detaljer om Revolved Credit Facility Colgate 2013 - 10K, Side 35

Yderligere oplysninger om gæld er også de forpligtede langfristede tilbagebetalinger af gæld. Colgate 2013 - 10K, Side 36

Trin 12C - Beregn den langsigtede langsigtede gæld.

Vi bruger den langsigtede tilbagebetalingsplan for gæld, der er angivet ovenfor, og beregner den endelige saldo for langfristet tilbagebetaling af gæld.

Trin 12D - Forbind de langsigtede tilbagebetalinger af gæld.

Trin 12E -Beregn de diskretionære lån / afspilninger.

Brug formlen til kontant sweep, som vist nedenfor, til at beregne de diskretionære lån / nedbetalinger.

Trin 12F - Beregn renteudgifterne fra den langsigtede gæld

Beregn den gennemsnitlige saldo for revolverende kreditfacilitet og langfristet gæld
Gør en rimelig antagelse om en rentesats baseret på oplysningerne i 10K-rapporten
Beregn den samlede renteudgift = gennemsnitlig gældsaldo x rentesats

Find den samlede renteudgift = rente (revolverende kreditfacilitet) + rente (langfristet gæld)

Trin 12G - Hovedforbindelsesgæld og revolverudnyttelse til pengestrømme

Trin 12H - Referencestrøm og lang sigt til balance

Afgræns den aktuelle del af langfristet gæld og langfristet gæld som vist nedenfor

Forbind den revolverende kreditfacilitet, langfristet gæld og den aktuelle del af langfristet gæld til balancen

Trin 12I - Beregn renteindtægten ved hjælp af den gennemsnitlige kontantsaldo

Trin 12J - Knyt renteudgifter og renteindtægter til resultatopgørelsen

Udfør balancekontrollen: Aktiver i alt = forpligtelser + aktionærers egenkapital

Trin 12K - Revision af balancen

Hvis der er uoverensstemmelse, er vi nødt til at revidere modellen og kontrollere for eventuelle sammenkædningsfejl

Anbefalet økonomisk modelleringskursus

Jeg håber, du har haft glæde af den gratis økonomiske modellerings Excel-guide. Hvis du ønsker at lære økonomisk modellering i Excel gennem vores ekspertvideoforelæsninger, kan du også se på vores Investment Banking Training. Dette er primært 99 kurser Investment Banking træningspakke. Dette kursus starter med det grundlæggende og fører dig til det avancerede niveau for Investment Banking Job. Dette kursus er opdelt i fem dele -

Del 1 - Uddannelse i investeringsbank - Kernekurser
(26 kurser)
Del 2 - Avanceret modellering af investeringsbankvirksomhed
(20 kurser)
Del 3 - Add-ons til investeringsbank
(13 kurser)
Del 4 - Kurser for Investment Banking Foundation
(23 kurser)
Del 5 - Bløde færdigheder for investeringsbankfolk
(17 kurser)

Download af økonomiske modeller

Alibaba økonomiske model
Boks IPO finansiel model
Skabeloner for finansiel modellering
Banking Financial Modelling Course

Hvad er det næste?

Hvis du har lært noget nyt eller haft glæde af denne Excel-baserede finansielle modellering, skal du efterlade en kommentar nedenfor. Lad mig vide hvad du tænker. Mange tak, og pas på. Glad læring!