Finansiell modellering i Excel (trinnvis gratis guide + mal)

Innholdsfortegnelse

Hva er økonomisk modellering i Excel?

Finansiell modellering i Excel er prosessen med å bygge en finansmodell for å representere en transaksjon, drift, fusjon, anskaffelse, finansiell informasjon for å analysere hvordan en endring i en variabel kan påvirke den endelige avkastningen for å ta en beslutning om en eller flere av nevnte finansielle transaksjoner.

Hva er økonomisk modellering i Excel?

Økonomisk modellering i Excel er overalt på nettet. Det har blitt skrevet mye om å lære økonomisk modellering; Imidlertid er de fleste av de økonomiske modelleringsopplæringene de samme. Dette går utover vanlig gibberish og utforsker praktisk finansiell modellering som brukt av investeringsbankfolk og forskningsanalytikere.

I denne gratis økonomiske modellerings Excel-guiden vil jeg ta et eksempel på Colgate Palmolive og utarbeide en fullt integrert finansmodell fra bunnen av.

Denne guiden er på over 6000 ord og tok meg tre uker å fullføre. Lagre denne siden for fremtidig referanse og ikke glem å dele den :-)

VIKTIGST - Last ned Excel-malen for Colgate Financial-modellering for å følge instruksjonene

Last ned Colgate Financial Model Mal

Lær trinnvis økonomisk modellering i Excel

Økonomisk modellering i Excel-trening - Les meg først

Trinn 1 - Last ned Colgate Financial Model Mal. Du vil bruke denne malen for opplæringen

Last ned Colgates økonomiske modell

Angi e-postadresse Ved å fortsette over trinnet godtar du våre vilkår for bruk og personvernregler.

Trinn 2 - Vær oppmerksom på at du får to maler - 1) uløst Colgate Palmolive Financial Model 2) Løst Colgate Palmolive Financial Model

Trinn 3 - Du skal jobbe med uløst Colgate Palmolive Financial Model Template . Følg trinnvise instruksjoner for å utarbeide en fullt integrert finansmodell.

Trinn 4 - Happy Learning!

Innholdsfortegnelse

Jeg har gjort det enkelt å navigere i innholdsfortegnelsen slik at du kan gjøre denne økonomiske modelleringen

Hvordan bygge en økonomisk modell?
# 1 - Colgates økonomiske modell - historisk
# 2 - Forholdsanalyse av Colgate Palmolive
# 3 - Projisere resultatregnskapet
# 4- Arbeidskapitalplan
# 5 - Avskrivningsplan
# 6 - Avskrivningsplan
# 7 - Annen langsiktig tidsplan
# 8 - Fullføring av resultatregnskapet
# 9 - Aksjeeierplan
# 10 - Aksjer utestående plan
# 11 - Fullføring av kontantstrømoppstillingen
# 12- Gjeld og rente plan anbefales
Finansiell modelleringskurs
Gratis økonomiske modeller

Hvis du ikke er kjent med finansiell modellering, kan du ta en titt på denne guiden på Hva er økonomisk modellering?

Hvordan bygge en økonomisk modell i Excel?

La oss se på hvordan en finansiell modell er bygget fra bunnen av. Denne detaljerte økonomiske modelleringsguiden vil gi deg en trinnvis guide for å lage en finansiell modell. Den primære tilnærmingen i denne økonomiske modelleringsguiden er Modular. Det modulære systemet betyr i hovedsak å bygge kjerneoppgaver som resultatregnskap, balanse og kontantstrømmer ved hjelp av forskjellige moduler / tidsplaner. Hovedfokuset er å forberede hver uttalelse trinn for trinn og koble alle støtteprogrammene til kjerneuttalelsene etter ferdigstillelse. Jeg kan forstå at dette kanskje ikke er klart nå; imidlertid vil du innse at dette er veldig enkelt når vi går videre. Du kan se nedenfor ulike økonomiske modeller / moduler -

Vær oppmerksom på følgende -

Kjerneoppgavene er resultatregnskap, balanse og kontantstrømmer.
De forskjellige tidsplanene er avskrivningsplanen, arbeidskapitalplanen, immateriellplanen, aksjeeierplanen, andre langsiktige poster, gjeldsplanen, etc.
De forskjellige timeplanene er knyttet til kjerneuttalelsene når de er ferdige.
I denne økonomiske modelleringsguiden vil vi bygge en trinnvis integrert økonomisk modell av Colgate Palmolive fra bunnen av.

# 1 - Finansiell modellering i Excel - Prosjekter historikene

Det første trinnet i Financial Modelling Guide er å forberede Historicals.

Trinn 1A - Last ned Colgates 10K-rapporter

“Finansielle modeller er utarbeidet i excel, og de første trinnene begynner med å vite hvordan bransjen har hatt det de siste årene. Å forstå fortiden kan gi oss verdifull innsikt knyttet til selskapets fremtid. Derfor er det første trinnet å laste ned alle finansene til selskapet og fylle ut det samme i et excel-ark. For Colgate Palmolive kan du laste ned årsrapportene om Colgate Palmolive fra deres Investor Relation Section. Når du klikker på "Årsrapport", vil du finne vinduet som vist nedenfor -

Trinn 1B - Opprett regnearket for historiske regnskaper

Hvis du laster ned 10K av 2013, vil du merke at bare to års regnskapsdata er tilgjengelig. For finansmodellering i Excel er det anbefalte datasettet imidlertid å ha de siste 5 årene med regnskap. Last ned de siste 3 årene av årsrapporten og fyll ut historikken.
Mange ganger virker disse oppgavene for kjedelige og kjedelige, da det kan ta mye tid og energi å formatere og sette utmerket i ønsket format.
Man bør imidlertid ikke glemme at dette er arbeidet du må gjøre bare en gang for hvert selskap, og å fylle historiene hjelper en analytiker med å forstå trender og finansregnskap.
Så ikke hopp over dette, last ned dataene og fyll ut dataene (selv om du føler at dette er eselens arbeid ;-))

Hvis du ønsker å hoppe over dette trinnet, kan du laste ned Colgate Palmolive Historical Model direkte her.

Colgate resultatregnskap med historisk befolkning

Colgate Balanse Historiske data

# 2 - Forholdsanalyse

Det andre trinnet i økonomisk modellering i Excel er å utføre forholdsanalyse.

En nøkkel til å lære økonomisk modellering i Excel er å kunne utføre grunnleggende analyse. Hvis elementanalysen eller forholdsanalysen er noe nytt for deg, anbefaler jeg at du leser litt på internett. Jeg har tenkt å ta en grundig forholdsanalyse i et av mine kommende innlegg, men her er et raskt øyeblikksbilde av Colgate Palmolive-forholdet.

VIKTIG - Vær oppmerksom på at jeg har oppdatert forholdsanalysen av Colgate i et eget innlegg. Ta en titt på denne omfattende forholdsanalysen.

Trinn 2A - Vertikal analyse av Colgate

I resultatregnskapet er den vertikale analysen et universelt verktøy for å måle selskapets relative ytelse fra år til år når det gjelder kostnader og lønnsomhet. Det bør alltid inkluderes som en del av enhver økonomisk analyse. Her beregnes prosentandeler i forhold til nettosalg, som anses å være 100%. Denne vertikale analyseinnsatsen i resultatregnskapet blir ofte referert til som marginalanalyse siden den gir de forskjellige marginene angående salg.

Vertikale analyseresultater

Resultatmarginen hadde økt med 240 basispunkter fra 56,2% i 2007 til 58,6% i 2013. Dette skyldes hovedsakelig reduserte salgskostnader
Driftsresultat eller EBIT har også vist forbedrede marginer, og økte fra 19,7% i 2007 til 22,4% i 2012 (en økning på 70 basispunkter). Dette skyldtes reduserte salgs- og administrasjonskostnader. Vær imidlertid oppmerksom på at EBIT-marginene reduserte i 2013 til 20,4% på grunn av økte "Andre utgifter." Sjekk også ut forskjellen mellom EBIT vs EBITDA
Resultatmarginen økte fra 12,6% i 2007 til 14,5% i 2012. Profittmarginen i 2013 gikk imidlertid ned til 12,9%, hovedsakelig på grunn av økte “andre utgifter”.
Resultat per aksje har økt jevnlig fra 2007 til 2007. Imidlertid var det en liten nedgang i EPS for FY2013
Vær også oppmerksom på at avskrivningene er gitt separat i resultatregnskapet. Det er inkludert i salgskostnadene.

Trinn 2B - Horisontal analyse av Colgate

Horisontal analyse er en teknikk som brukes til å evaluere trender over tid ved å beregne prosentvise økninger som utmerker seg eller faller i forhold til et basisår. Det gir en analytisk kobling mellom kontoer beregnet til forskjellige datoer ved bruk av valutaen med forskjellige innkjøpsmakter. I virkeligheten indekserer denne analysen rapportene og sammenligner utviklingen av disse over tid. Som med den vertikale analysemetoden vil det dukke opp problemer som må undersøkes og suppleres med andre økonomiske analyseteknikker. Fokuset er å se etter symptomer på problemer som kan diagnostiseres ved hjelp av flere metoder.

La oss se på den horisontale analysen av Colgate.

Horisontale analyseresultater

Vi ser at nettoomsetningen økte med 2,0% i 2013.
Legg også merke til trenden i salgskostnadene. Vi ser at de ikke har vokst i samme andel som har Salg.
Disse observasjonene er ekstremt praktiske mens vi gjør økonomisk modellering i Excel.

Trinn 2C - Likviditetsforhold for Colgate

Likviditetsforhold måler forholdet mellom virksomhetens mer likvide eiendeler (de som lettest kan konverteres til kontanter) og kortsiktig gjeld. De vanligste likviditetsforholdene er nåværende forhold, syreprøve (eller hurtig aktiva) kontantforhold.
Omsetningsgrad som omsetning av fordringer, lageromsetning og omsetning av gjeld

Viktige høydepunkter i likviditetsforhold

Nåværende forholdstall for Colgate er mer signifikant enn 1,0 i alle år. Dette innebærer at omløpsmidler er viktigere enn kortsiktig gjeld, og kanskje Colgate har tilstrekkelig likviditet.
Den raske forholdet mellom Colgate er i området 0,6-0,7; dette betyr at Colgates kontanter og omsettelige verdipapirer kan betale så mye som 70% av kortsiktig gjeld. Dette ser ut som en god situasjon å være i for Colgate.
Kontantinnkrevingssyklusen hadde gått ned fra 43 dager i 2009 til 39 dager i 2013. Dette skyldes hovedsakelig reduksjonen i fordringstid.

Ta også en titt på denne detaljerte artikkelen om Cash Conversion Cycle.

Trinn 2D - Lønnsomhetsgrader for Colgate

Lønnsomhetsforhold selskapets evne til å generere inntjening i forhold til salg, eiendeler og egenkapital

Viktige høydepunkter - Lønnsomhetsforhold for Colgate

Som vi kan se fra tabellen ovenfor, har Colgate en avkastning på nærmere 100%, noe som innebærer god avkastning til aksjeeierne.

Trinn 2E - Risikoanalyse av Colgate

Gjennom risikoanalyse prøver vi å måle om selskapene vil være i stand til å betale sine kortsiktige og langsiktige forpliktelser (gjeld). Vi beregner giringsgrad som fokuserer på tilstrekkelige eiendeler eller generering fra eiendeler. Priser som blir sett på er

Gjeld til egenkapitalforhold
Gjeldsgrad
Rentedekningsgrad
Gjeld til egenkapitalandel har jevnlig økt til et høyere nivå på 2,23x. Dette betyr økt finansiell gjeldsgrad og risiko i markedet
Rentedekningsgraden er imidlertid veldig høy, noe som indikerer mindre risiko for rentebetaling.

# 3 - Finansiell modellering i Excel - Prosjekter resultatregnskapet

Det tredje trinnet i finansiell modellering er å forutsi resultatregnskapet, der vi begynner med modellering av salgs- eller inntektsartikler.

Trinn 3A - Inntektsframskrivninger

For de fleste selskaper er inntektene en grunnleggende driver for økonomisk ytelse. En veldesignet og logisk inntektsmodell som gjenspeiler typen og mengden av inntektsstrømmer nøyaktig er ekstremt viktig. Det er like mange måter å lage en inntektsplan som det er virksomheter. Noen vanlige typer inkluderer:

Salgsvekst: Forutsetningen for salgsvekst i hver periode definerer endringen fra forrige periode. Dette er en enkel og ofte brukt metode, men gir ingen innsikt i komponentene eller dynamikken i vekst.
Inflasjons- og volum / mikseffekter: I stedet for en enkel vekstforutsetning brukes en prisinflasjonsfaktor og en volumfaktor. Denne nyttige tilnærmingen tillater modellering av faste og variable kostnader i selskaper med flere produkter og tar hensyn til pris mot volumbevegelser.
Enhetsvolum, endring i volum, gjennomsnittspris og prisendring: Denne metoden er passende for bedrifter som har en enkel produktmiks; det tillater analyse av virkningen av flere viktige variabler.
Dollarmarkedsstørrelse og vekst: Markedsandel og endring i andel - Nyttig for tilfeller der informasjon er tilgjengelig om markedsdynamikk, og hvor disse antagelsene sannsynligvis vil være grunnleggende for en beslutning. For eksempel telekomindustrien.
Enhetsmarkedsstørrelse og vekst: Dette er mer detaljert enn forrige tilfelle og er nyttig når priser i markedet er en avgjørende variabel. (For et selskap med en prisdiskonteringsstrategi, for eksempel, eller en best av rase premium-prisede nisjeaktører) f.eks. Luksusbilmarkedet
Volumkapasitet, kapasitetsutnyttelsesgrad og gjennomsnittspris: Disse forutsetningene kan være viktige for bedrifter der produksjonskapasitet er viktig for beslutningen. (Ved kjøp av ekstra kapasitet, for eksempel, eller for å avgjøre om utvidelsen vil kreve nye investeringer.)
Produkttilgjengelighet og priser
Inntektene ble drevet av investeringer i kapital, markedsføring eller FoU
Inntektsbasert på installert base (fortsatt salg av deler, engangsutstyr, service og tillegg osv.). Eksempler inkluderer klassiske barberbladvirksomheter og virksomheter som datamaskiner der salg av service, programvare og oppgraderinger er viktig. Modellering av den installerte basen er nøkkelen (nye gulvtillegg, slitasje i bakken, fortsatte inntekter per kunde osv.).
Ansattbasert: For eksempel inntekter fra profesjonelle tjenestebedrifter eller salgsbaserte selskaper som meglere. Modellering bør fokusere på netto bemanning, inntekt per ansatt (ofte basert på fakturerbare timer). Mer detaljerte modeller vil omfatte ansiennitet og andre faktorer som påvirker prisingen.
Butikk, anlegg eller firkantet opptak: Butikkbedrifter modelleres ofte basert på butikker (gamle butikker pluss nye butikker hvert år) og inntekt per butikk.
Tilstedeværelsesfaktorbasert: Denne tilnærmingen gjelder for flyselskaper, hoteller, kinoer og andre virksomheter med lave marginalkostnader.

Projiserende Colgate-inntekter

La oss nå se på Colgate 10K 2013-rapporten. Vi bemerker at Colgate ikke har gitt segmentinformasjon i resultatregnskapet; som et tilleggsinformasjon har Colgate imidlertid gitt noen detaljer om segmenter på side 87 Kilde - Colgate 2013 - 10K, Side 86

Siden vi ikke har ytterligere informasjon om funksjonene, vil vi projisere det fremtidige salget av Colgate på grunnlag av disse tilgjengelige dataene. Vi vil bruke salgsveksttilnærmingen på tvers av segmenter for å utlede prognosene. Se bildet nedenfor. Vi har beregnet vekstraten fra år til år for hvert element. Nå kan vi anta en salgsvekstprosent basert på de historiske trendene og projisere inntektene under hver del. Total nettoomsetning er summen av Oral, Personal & Home Care og Pet Nutrition Segment.

Trinn 3B - Kostnadsprognoser

Prosent av inntekter: Enkel, men gir ingen innsikt i noen innflytelse (stordriftsfordel eller fast kostnad
Andre kostnader enn avskrivninger i prosent av inntektene og avskrivninger fra en annen tidsplan: Denne tilnærmingen er egentlig det minste akseptable i de fleste tilfeller, og tillater bare delvis analyse av driftsinnflytelse.
Variable kostnader basert på inntekt eller volum, faste kostnader basert på historiske trender og avskrivninger fra en egen tidsplan: Denne tilnærmingen er det minste nødvendige for sensitivitetsanalyse av lønnsomhet basert på flere inntektsscenarier

Kostnadsprognoser for Colgate

For å projisere kostnadene vil den vertikale analysen som er gjort tidligere være nyttig. La oss ta en titt på den vertikale analysen -

Siden vi allerede har prognostisert salg, er alle de andre kostnadene noen marginer av dette salget.
Tilnærmingen er å ta retningslinjene fra de historiske kostnads- og utgiftsmarginene og deretter forutsi fremtidsmarginen.
For eksempel har salgskostnadene vært i området 41-42% de siste fem årene. Vi kan se på prognoser for marginene på dette grunnlaget.
Likeledes har salg, generelle og administrative utgifter historisk vært i området 34-36%. Vi kan anta den fremtidige SG&A utgiftsmarginen på dette grunnlaget. På samme måte kan vi fortsette med et annet sett med utgifter.

Ved hjelp av marginene ovenfor kan vi finne de faktiske verdiene ved å beregne tilbake.

For å beregne avsetningen for skatt bruker vi antatt effektiv skattesats.

Vær også oppmerksom på at vi ikke fullfører raden "Rentekostnad (inntekt)", ettersom vi vil ta om resultatregnskapet på et senere tidspunkt.
Renteutgifter og renteinntekter.
Vi har heller ikke beregnet avskrivninger, som allerede er inkludert i salgskostnadene.
Dette fullfører resultatregnskapet (i hvert fall foreløpig!)

# 4 - Finansiell modellering - Arbeidskapitalplan

Nå som vi har fullført resultatregnskapet, er det fjerde trinnet i finansiell modellering å se på arbeidskapitalplanen.

Nedenfor er trinnene som skal følges for arbeidskapitalplanen

Trinn 4A - Koble nettosalget og salgskostnaden

Trinn 4B - Henvisning til balansedataene knyttet til arbeidskapital

Henvis til tidligere data fra balansen
Beregn netto arbeidskapital
Ankom en økning / reduksjon i arbeidskapitalen
Vær oppmerksom på at vi ikke har inkludert kortsiktig gjeld og kontanter og kontantekvivalenter i arbeidskapitalen. Vi vil håndtere gjeld og kontanter og kontantekvivalenter hver for seg.

Trinn 4C - Beregn omsetningsforholdene

Beregn historiske forhold og prosenter
Bruk slutt- eller gjennomsnittsbalansen
Begge er akseptable ettersom lang konsistens opprettholdes

Trinn 4D - Fyll forutsetningene for fremtidige arbeidskapitalposter

Enkelte gjenstander uten en fremtredende driver antas vanligvis med faste beløp
Sørg for at forutsetningene er rimelige og i tråd med virksomheten

Trinn 4E - Foreta fremtidige arbeidskapitalsaldoer

Trinn 4F - Beregn endringene i arbeidskapital

Ankom kontantstrømmer basert på individuelle ordrelinjer
Forsikre deg om at skiltene er nøyaktige!

Trinn 4G - Koble opp den forventede arbeidskapitalen til balansen

Trinn 4H - Koble arbeidskapital til kontantstrømoppstillingen

# 5 - Finansiell modellering i Excel - Avskrivningsplan

Etter at arbeidskapitalplanen er fullført, er neste trinn i denne økonomiske modelleringen prosjektet Capex of Colgate og prosjektet avskrivning og eiendeler. Colgate 2013 - 10K, Side 49

Avskrivninger er ikke gitt som en egen ordrelinje; det er imidlertid inkludert i salgskostnadene
I slike tilfeller kan du ta en titt på kontantstrømoppstillingene der du finner avskrivningskostnadene. Vær også oppmerksom på at nedenstående tall er 1) Avskrivninger 2) amortiseringer. Så hva er avskrivningsnummeret?
Sluttsaldo for PPE = begynnelsesaldo + Capex - Avskrivninger - Justering for aktivesalg (BASE-ligning)

Trinn 5A - Koble nettosalgstallene i avskrivningsplanen

Sett opp ordrelinjene
Referanse Netto salg
Angi tidligere kapitalutgifter
Ankom Capex som% av nettoomsetningen

Trinn 5B - Prognoser kapitalutgiftspostene

For å forutsi kapitalutgiftene er det forskjellige tilnærminger. En vanlig praksis er å se på Pressemeldinger, Management Projections, MD&A for å forstå selskapets syn på fremtidige kapitalutgifter
Hvis selskapet har gitt veiledning om fremtidige investeringer, kan vi ta disse tallene direkte.
Men hvis Capex-tallene ikke er direkte tilgjengelige, kan vi beregne det grovt med Capex som% av salget (som gjort nedenfor)
Bruk din vurdering basert på bransjekunnskap og andre rimelige drivere.

Trinn 5C - Referanse tidligere informasjon

Vi vil bruke Ending Balance for PPE = Begynnelsessaldo + Capex - Avskrivninger - Justering for aktivesalg (BASE-ligning)
Det er komplisert å avstemme tidligere PP&E på grunn av omskrivninger, salg av eiendeler osv.
Det anbefales derfor å ikke forene tidligere PVU, da det kan føre til forvirring.

Avskrivningspolicy for Colgate

Vi bemerker at Colgate ikke eksplisitt har gitt en detaljert oppdeling av eiendelene. De har i stedet forenet alle eiendeler i land, bygg, maskiner og annet utstyr
Også brukstid for maskiner og utstyr er gitt innen rekkevidde. I dette tilfellet må vi gjøre noen gjetninger for å komme til gjennomsnittlig levetid igjen for eiendelene
Veiledning for brukstid er ikke gitt for “Annet utstyr”. Vi må estimere levetiden for annet utstyr

Colgate 2013 - 10K, side 55

Nedenfor er oppdelingen av eiendoms-, anleggs- og utstyrsdetaljer for 2012 og 2013

Colgate 2013 - 10K, side 91

Trinn 5D - Beregn bruddet på eiendommer og utstyr (PPE)

Finn først eiendelvektene til gjeldende PPE (2013)
Vi vil anta at disse aktiva vektene av 2013 PPE vil fortsette fremover
Vi bruker denne aktivitetsvekten til å beregne fordelingen av estimerte kapitalutgifter

Trinn 5E - Beregn avskrivning av eiendeler

Vær oppmerksom på at vi ikke beregner avskrivning av jord, ettersom land ikke er en avskrivbar eiendel
For å estimere avskrivninger fra forbedringer av bygninger, bruker vi først strukturen nedenfor.
Avskrivninger her er delt i to deler - 1) avskrivninger fra bygningsforbedringsaktiva som allerede er oppført i balansen, 2) avskrivninger fra fremtidige bygningsforbedringer.
For å beregne avskrivningene fra bygningsforbedringer som er oppført på eiendelen, bruker vi den enkle avskrivningsmetoden.
For å beregne fremtidig avskrivning transponerer vi først Capex ved hjelp av TRANSPOSE-funksjonen i Excel.
Vi beregner avskrivningen fra aktiva bidrag fra hvert år.
Det første års avskrivning er også delt på to da vi antar midtårskonvensjonen for aktivedistribusjon.

Total avskrivning av bygningsforbedring = avskrivning fra bygningsforbedringer Eiendom som allerede er oppført på balansen + avskrivninger fra fremtiden Byggforbedringer Ovennevnte prosess for å estimere avskrivninger brukes til å beregne avskrivning av 1) Produksjonsutstyr og maskiner og 2) annet utstyr Vist under.

Andre typer utstyr

Total avskrivning av Colgate = Avskrivning (forbedringer av bygninger) + Avskrivninger (maskiner og utstyr) + Avskrivning (tilleggsutstyr) Når vi har funnet ut de reelle avskrivningstallene, kan vi sette det i BASE-ligningen som vist nedenfor

Med dette får vi Ending Net PP&E tall for hvert av årene

Trinn 5F - Koble netto PP&E til balansen

# 6 - Avskrivningsplan

Det sjette trinnet i denne økonomiske modelleringen i Excel er å forutsi amortisering. Vi har to brede kategorier å vurdere her - 1) Goodwill og 2) Andre immaterielle eiendeler.

Trinn 6A - Prognoser for goodwill

Colgate 2013 - 10K, side 61

Goodwill kommer i balansen når et selskap kjøper et annet selskap. Det er vanligvis komplisert å projisere goodwill for fremtidige år.
Goodwill er imidlertid gjenstand for verdifallstester årlig, som utføres av selskapet selv. Analytikere er ikke i stand til å utføre slike tester og utarbeide estimater for verdifall.
De fleste analytikere anslår ikke goodwill; de holder bare dette som konstant, noe vi også vil gjøre i vårt tilfelle.

Trinn 6B - Prognoser for andre immaterielle eiendeler

Som nevnt i Colgates 10K-rapport, er størstedelen av det endelige livets immaterielle relatert til Sanex-oppkjøpet
“Tillegg til immaterielle eiendeler” er også komplisert å projisere
Colgates 10K-rapport gir oss detaljene for de neste fem årene av amortiseringskostnader.
Vi vil bruke disse estimatene i vår økonomiske modell Colgate 2013 - 10K, side 61

Trinn 6C - Avslutte netto immaterielle eiendeler er knyttet til "Andre immaterielle eiendeler."

Trinn 6D - koble avskrivninger og kontantstrømoppstillinger

Trinn 6E - Koble Capex og tillegg til immaterielle eiendeler til kontantstrømsoppgaver

# 7 - Annen langsiktig tidsplan

Det neste trinnet i denne økonomiske modelleringen er å utarbeide den andre langsiktige tidsplanen. Dette er når vi forbereder oss på "restene" som ikke har spesifikke drivere for prognoser. For Colgate var de andre langsiktige varene (rest) utsatt skatt (gjeld og eiendeler), andre investeringer og andre forpliktelser.

Trinn 7A - Henvis til de historiske dataene fra balansen

Beregn også endringene i disse elementene.

Trinn 7B - Prognoser langsiktige eiendeler og gjeld

Hold de langsiktige varene konstante i forventede år i tilfelle ingen synlige drivere
Koble de forventede langsiktige postene til balansen som vist nedenfor

Trinn 7C - Henvisning til andre langsiktige poster til balansen

Trinn 7D - Koble langsiktige poster til kontantstrømoppstilling

Vær oppmerksom på at hvis vi har holdt de langsiktige eiendelene og forpliktelsene like konstante, vil endringen som strømmer til kontantstrømoppstillingen være null.

# 8 - Økonomisk modellering i Excel - Fullføring av resultatregnskapet

Før vi går videre i denne Excel-baserte økonomiske modelleringen, vil vi gå tilbake og se på resultatregnskapet
Fyll ut det historiske grunnveide gjennomsnittlige aksjene og utvannet vektet gjennomsnittlig antall aksjer
Disse tallene er tilgjengelige i Colgates 10K-rapport

Trinn 8A - Referanse til de grunnleggende og utvannede aksjene

Anta på dette stadiet at det fremtidige antallet primære og utvannede aksjer vil være det samme som i 2013.

Trinn 8B - Beregn grunnleggende og utvannet fortjeneste per aksje.

Med dette er vi klare til å gå videre til neste plan, dvs. aksjeeierens egenkapitalplan.

# 9 - Finansiell modellering - Aksjonærplan

Det neste trinnet i denne økonomiske modelleringen i Excel-opplæring er å se på aksjeeierens egenkapitalplan. Det primære målet for denne planen er å projisere aksjerelaterte poster som aksjeeier, utbytte, tilbakekjøp av aksjer, aksjeinntekter osv. Colgates 10K-rapport gir oss detaljene om aksje- og statskasseaksjoner de siste årene, som vist nedenfor . Colgate 2013 - 10K, side 68

Trinn 9A - Tilbakekjøp av aksjer: Befolk de historiske tallene

Historisk sett har Colgate tilbakekjøpt aksjer, som vi kan se tidsplanen ovenfor.
Befolk Colgates aksjer tilbakekjøp (millioner) i excel-arket.
Koble historisk utvannet EPS fra resultatregnskapet
Den historiske tilbakekjøpte beløpet skal refereres fra kontantstrømsoppgavene.

Ta også en titt på Accelerated Share Repurchase.

Trinn 9B - Tilbakekjøp av aksjer: Beregn PE-multiple (EPS-multiple)

Beregn den underforståtte gjennomsnittskursen som Colgate har gjort tilbakekjøp av aksjer historisk. Dette beregnes som tilbakekjøpt beløp / antall aksjer.
Beregn PE-multiple = Implisitt aksjekurs / EPS

Trinn 9C - Tilbakekjøp av aksjer: Finne Colgates andel tilbakekjøpt

Colgate har ikke gitt noen offisiell kunngjøring om hvor mange aksjer de har til hensikt å kjøpe tilbake. Den eneste informasjonen om at deres 10K rapporterer aksjer er at de har godkjent et tilbakekjøp på opptil 50 millioner aksjer. Colgate 2013 - 10K, Side 35

For å finne antall tilbakekjøpte aksjer, må vi anta tilbakekjøpsbeløpet. Basert på det historiske tilbakekjøpsbeløpet, har jeg tatt dette tallet som $ 1500 millioner for alle de kommende årene.
For å finne antall tilbakekjøpte aksjer trenger vi den anslåtte implisitte aksjekursen for den potensielle tilbakekjøpet.
Faktisk aksjekurs = antatt PE multiplex EPS.
Fremtidige tilbakekjøp av PE flere kan antas basert på historiske trender. Vi bemerker at Colgate har tilbakekjøpt aksjer til et gjennomsnittlig PE-område på 17x - 25x
Nedenfor er øyeblikksbildet fra Reuters som hjelper oss med å validere PE-serien for Colgate

www.reuters.com

I vårt tilfelle har jeg antatt at alle fremtidige tilbakekjøp av Colgate vil være på et PE-multiplum på 19x.
Ved hjelp av PE på 19x kan vi finne den implisitte prisen = EPS x 19
Nå som vi har funnet den implisitte prisen, kan vi se antall tilbakekjøpte aksjer = $ beløp brukt til tilbakekjøp / underforstått pris.

Trinn 9D - Aksjealternativer: Befolk historiske data

Fra sammendraget av aksje og egenkapital vet vi antall opsjoner som utøves hvert år.

Dessuten har vi også Opsjonsinntektene fra kontantstrømsoppgavene (ca.)

Med dette skal vi kunne finne en effektiv innløsningspris.

Colgate 2013 - 10K, Side 53

Vær også oppmerksom på at aksjeopsjonene har kontraktsvilkår på seks år og opptjenes over tre år. Colgate 2013 - 10K, Side 69

Med disse dataene fyller vi opp alternativdataene nedenfor. Vi bemerker også at den veide gjennomsnittlige innløsningskursen for aksjeopsjoner for 2013 var $ 42, og antall opsjoner som var utøvd var 24,151 millioner Colgate 2013 - 10 000 , side 70

Trinn 9E - Aksjealternativer: Finn opsjonen fortsetter.

Ved å legge disse tallene inn i våre opsjonsdata nedenfor, bemerker vi at opsjonen til opsjonen er $ 1.014 milliarder.

Trinn 9F - Aksjealternativer: Prognosebegrensede aksjeenhetsdata

I tillegg til aksjeopsjonene er det gitt begrensede aksjeenheter til de ansatte med en vektet gjennomsnittlig periode på 2,2 år Colgate 2013 - 10K, Side 81

Fyll ut disse dataene i Options-datasettet For enkelhets skyld har vi ikke anslått utstedelse av opsjoner (jeg vet at dette ikke er den riktige antagelsen, men på grunn av mangel på data tar jeg ikke flere alternativproblemer fremover. Vi har nettopp tatt disse som null, som fremhevet i det grå området ovenfor. I tillegg forventes de begrensede aksjeenhetene å være 2,0 millioner fremover.

Ta også en titt på Treasury Stock Method.

Trinn 9G - Utbytte: Prognoser utbyttet

Prognosert estimert utbytte ved bruk av utbytteforholdet.
Fast utbytteutbetaling per aksjeutbetaling
Fra 10K-rapportene trekker vi ut all tidligere informasjon om utbytte.
Med informasjon om utbetalt utbytte kan vi finne utbytteutbytte = totalt utbetalt utbytte / nettoinntekt.
Jeg har beregnet utbytteutbyttet til Colgate som vist nedenfor - Vi bemerker at utbytteutbyttet har stort sett vært i området 50-60%. La oss anta utbyttet på 55% i de kommende årene.
Vi kan også koble den anslåtte nettoinntekten fra resultatregnskapet.
Ved å bruke både den anslåtte nettoinntekten og utbyttet, kan vi finne det totale utbetalte utbyttet.

Trinn 8H - Prognosekapital i sin helhet

Med prognosen for tilbakekjøp av aksjer, inntekter og utbetalt utbytte, er vi klare til å fullføre aksjeeierplanen. Koble alle disse sammen for å finne den endelige aksjebalansen for hvert år, som vist nedenfor.

Trinn 9I - Koble sluttaksjonens egenkapital til balansen

Trinn 9J - Link utbytte, tilbakekjøp av aksjer og opsjoner går til CF

# 10 - Aksjer utestående plan

Det neste trinnet i denne online økonomiske modelleringen i Excel-opplæring er å se på Shares Oustanding Schedule. Oppsummering av aksjene

Grunnleggende aksjer - faktiske og gjennomsnittlige
Ta tak i tidligere effekter av opsjoner og konvertibler etter behov
Fortynnede aksjer - gjennomsnitt
Referanse Aksjer tilbakekjøpt og nye aksjer fra utøvde opsjoner
Beregn forventede rå prosenter (faktisk)
Beregn gjennomsnittlig grunnleggende og utvannet aksjer
Referanse projiserte aksjer til resultatregnskapet (husk resultatregnskapet bygge opp!)
Inndatahistorikk deler utestående informasjon
Merk : Denne tidsplanen er ofte integrert med egenkapitalplanen

Trinn 10A - Skriv inn de historiske tallene fra 10K-rapporten

Utstedte aksjer (faktisk realisering av opsjoner) og tilbakekjøpte aksjer kan refereres fra aksjeeierens egenkapitalplan
Inngangen veide et gjennomsnittlig antall aksjer og effekten av aksjeopsjoner for de historiske årene.

Trinn 10B - Koble utstedelser og tilbakekjøp fra aksjeplanen.

Grunnleggende aksjer (slutt) = grunnleggende aksjer (begynnelse) + aksjeutstedelser - aksjer tilbakekjøpt.

Trinn 10C - Finn de grunnleggende vektede gjennomsnittlige aksjene,

vi finner et gjennomsnitt på to år, som vist nedenfor.
Legg også til effekten av opsjoner og begrensede aksjeenheter (referert fra aksjeeierens egenkapitalplan) for å finne de utvannede vektede gjennomsnittlige aksjene.

Trinn 10D - Koble grunnleggende og utvannede vektede aksjer til resultatregnskapet

Nå som vi har beregnet det utvannede vektede gjennomsnittlige aksjene, er det på tide for oss å oppdatere det samme i resultatregnskapet.
Koble prognostisert utvannet vektet gjennomsnittlig utestående aksjer til resultatregnskapet som vist nedenfor

Med dette fullfører vi Oustanding-planen for aksjer og tid til å gå til vårt neste sett med uttalelser.

# 11 - Fullføring av kontantstrømoppstillingen

Det er viktig for oss å fullføre kontantstrømsoppgavene før vi går til vår neste og siste tidsplan i denne økonomiske modelleringen, dvs. gjeldsplanen. Frem til dette stadiet er det bare et par ufullstendige ting

Resultatregnskap - rentekostnad / inntekt er ufullstendig på dette stadiet
Balanse - kontanter og gjeldsposter er ufullstendige på dette stadiet

Trinn 11A - Beregn kontantstrøm for finansieringsaktiviteter

Sjekk også ut kontantstrøm fra finansiering

Trinn 11B - Finn nettoøkning (reduksjon) i kontanter og kontantekvivalenter

Trinn 11C = Fullfør kontantstrømsoppgavene

Finn utgangen av kontanter og kontantekvivalenter på slutten av året.

Trinn 11D - Koble kontanter og kontantekvivalenter til balansen.

Nå er vi klare til å ta vare på vår siste og siste plan, dvs. gjeld og rente

# 12- Økonomisk modellering i Excel - gjeld og rente

Det neste trinnet i denne online økonomiske modelleringen er å fullføre gjelds- og renteplanen. Sammendrag av gjeld og renter - tidsplan

Grad 12A - Sett opp en gjeldsplan

Henvis til kontantstrømmen som er tilgjengelig for finansiering
Henvis til alle aksjekilder og bruk av kontanter

Trinn 12B - Beregn kontantstrøm fra tilbakebetaling av gjeld

Henvis til begynnelsessaldoen fra balansen
Trekk minstekontantsaldo. Vi har antatt at Colgate ønsker å beholde minimum 500 millioner dollar hvert år.

Hopp over langsiktig gjeldsutstedelse / tilbakebetaling, kontanter tilgjengelig for revolverende kredittfasiliteter og revolver-seksjonen for nå Fra Colgates 10K-rapport; vi legger merke til tilgjengelige detaljer på Revolved Credit Facility Colgate 2013 - 10K, Side 35

Ytterligere informasjon om gjeld er også gitt de forpliktede langsiktige tilbakebetalinger av gjeld. Colgate 2013 - 10K, side 36

Trinn 12C - Beregn den endelige langsiktige gjelden.

Vi bruker den langsiktige gjeldsplanen for tilbakebetaling av gjeld og beregner den endelige saldoen på langsiktig gjeldsnedbetaling.

Trinn 12D - Koble langsiktig tilbakebetaling av gjeld.

Trinn 12E -Beregn skjønnsmessige lån / avspillinger.

Beregn diskresjonære lån / nedbetalinger ved hjelp av kontantsveipformelen, som vist nedenfor.

Trinn 12F - Beregn rentekostnaden fra den langsiktige gjelden

Beregn gjennomsnittsbalansen for revolverende kredittfasilitet og langsiktig gjeld
Gjør en rimelig antagelse om en rentesats basert på informasjonen gitt i 10K-rapporten
Beregn total renteutgift = gjennomsnittlig gjeldsbalanse x rentesats

Finn total renteutgift = rente (revolverende kredittfasilitet) + rente (langsiktig gjeld)

Trinn 12G - Hovedkobling av gjeld og revolverutnyttelse til kontantstrømmer

Trinn 12H - Referansestrøm og lang sikt til balansen

Avgrens den nåværende delen av langsiktig gjeld og langsiktig gjeld som vist nedenfor

Koble revolverende kredittfasilitet, langsiktig gjeld og nåværende del av langsiktig gjeld til balansen

Trinn 12I - Beregn renteinntekten ved hjelp av den gjennomsnittlige kontantsaldoen

Trinn 12J - Koble renteutgifter og renteinntekter til resultatregnskapet

Utfør balansekontrollen: Totale eiendeler = Gjeld + Aksjeandel

Trinn 12K - Revidere balansen

Hvis det er noe avvik, må vi revidere modellen og se etter koblingsfeil

Anbefalt kurs for økonomisk modellering

Jeg håper du likte den gratis økonomiske modellerings Excel-guiden. Hvis du ønsker å lære økonomisk modellering i Excel gjennom våre ekspertvideoforelesninger, kan du også se på vår Investment Banking Training. Dette er primært 99 kurs Investment Banking opplæringspakke. Dette kurset starter med det grunnleggende og tar deg til det avanserte nivået i Investment Banking Job. Dette kurset er delt inn i fem deler -

Del 1 - Opplæring i investeringsbank - kjernekurs
(26 kurs)
Del 2 - Avansert modellering av investeringsbankvirksomhet
(20 kurs)
Del 3 - Tillegg for investeringsbank
(13 kurs)
Del 4 - Kurs for investeringsbankstiftelse
(23 kurs)
Del 5 - Myke ferdigheter for investeringsbankfolk
(17 kurs)

Nedlasting av økonomiske modeller

Alibaba Financial Model
Boks IPO finansiell modell
Maler for økonomiske modeller
Banking Financial Modelling Course

Hva nå?

Hvis du har lært noe nytt eller likte denne Excel-baserte økonomiske modelleringen, kan du legge igjen en kommentar nedenfor. Fortell meg hva du tenker. Tusen takk, og pass på. Glad læring!