Financijsko modeliranje u Excelu (besplatni vodič korak po korak + predložak)

Sadržaj

Što je financijsko modeliranje u Excelu?

Financijsko modeliranje u Excelu je postupak izgradnje financijskog modela koji predstavlja transakciju, operaciju, spajanje, pripajanje, financijske informacije kako bi se analiziralo kako promjena jedne varijable može utjecati na konačni povrat, kako bi se donijela odluka o jednom ili više spomenute financijske transakcije.

Što je financijsko modeliranje u Excelu?

Financijsko modeliranje u programu Excel svugdje je na webu. Puno je napisano o učenju financijskog modeliranja; međutim, većina obuka za financijsko modeliranje je ista. To nadilazi uobičajene nesuglasice i istražuje praktično financijsko modeliranje kakvo koriste investicijski bankari i analitičari istraživanja.

U ovom besplatnom vodiču za besplatno financijsko modeliranje uzet ću primjer Colgatea Palmolivea i pripremiti ću potpuno integrirani financijski model od nule.

Ovaj vodič sadrži više od 6000 riječi, a trebalo mi je tri tjedna da ga ispunim. Spremite ovu stranicu za buduću upotrebu i ne zaboravite je podijeliti :-)

NAJVAŽNIJE - preuzmite obrazac Colgate Financial modeling Excel kako biste slijedili upute

Preuzmite predložak financijskog modela Colgate

Naučite financijsko modeliranje korak po korak u Excelu

Financijsko modeliranje u programu Excel Training - Pročitajte me prvo

1. korak - preuzmite predložak financijskog modela Colgate. Ovaj ćete predložak koristiti za poduku

Preuzmite Colgateov financijski model

Unesite adresu e-pošte Nastavkom gornjeg koraka pristajete na naše Uvjete korištenja i Pravila o privatnosti.

Korak 2 - Imajte na umu da ćete dobiti dva predloška - 1) Nerazriješeni financijski model Colgate Palmolive 2) Riješeni financijski model Colgate Palmolive

Korak 3- Radit ćete na neriješenom predlošku financijskog modela Colgate Palmolive . Slijedite upute korak po korak kako biste pripremili potpuno integrirani financijski model.

Korak 4 - Sretno učenje!

Sadržaj

Omogućio sam vam jednostavnu navigaciju kroz ovaj financijski model

Kako izgraditi financijski model?
# 1 - Colgateov financijski model - povijesni
# 2 - Analiza omjera Colgate Palmolive
# 3 - Projektiranje bilansa uspjeha
# 4- Raspored obrtnog kapitala
# 5 - Raspored amortizacije
# 6 - Raspored amortizacije
# 7 - Ostali dugoročni raspored
# 8 - Popunjavanje računa dobiti i gubitka
# 9 - Raspored dioničkih kapitala
# 10 - Dijeli izvanredan raspored
# 11 - Popunjavanje izvještaja o novčanom tijeku
# 12 - Preporučuje se raspored dugova i kamata
Tečaj financijskog modeliranja
Besplatni financijski modeli

Ako ste novi u financijskom modeliranju, pogledajte ovaj vodič o tome što je financijsko modeliranje?

Kako izgraditi financijski model u Excelu?

Pogledajmo kako se financijski model gradi od nule. Ovaj detaljni vodič za financijsko modeliranje pružit će vam korak po korak vodič za stvaranje financijskog modela. Primarni pristup u ovom vodiču za financijsko modeliranje je modularni. Modularni sustav u osnovi znači izgradnju temeljnih izvještaja poput Bilansa uspjeha, Bilance stanja i Novčanih tijekova pomoću različitih modula / rasporeda. Ključni fokus je pripremiti svaku izjavu korak po korak i povezati sve prateće programe sa temeljnim izjavama po završetku. Mogu razumjeti da to možda sada nije jasno; međutim, shvatit ćete da je to vrlo lako dok idemo naprijed. U nastavku možete vidjeti razne programe / module modula financijskog modeliranja -

Imajte na umu sljedeće -

Temeljni izvještaji su račun dobiti i gubitka, bilanca stanja i novčani tijekovi.
Različiti rasporedi su raspored amortizacije, raspored obrtnog kapitala, raspored nematerijalnih ulaganja, dionički kapital, raspored ostalih dugoročnih stavki, raspored duga itd.
Različiti rasporedi povezani su sa temeljnim izjavama nakon njihovog dovršenja.
U ovom priručniku za financijsko modeliranje izradit ćemo korak po korak integrirani ekonomski model Colgate Palmolive od nule.

# 1 - Financijsko modeliranje u Excelu - Projekt Povijesti

Prvi korak u Vodiču za financijsko modeliranje je priprema Historysa.

Korak 1A - Preuzmite Colgate-ova 10K izvješća

“Financijski modeli pripremljeni su u izvrsnosti, a prvi koraci započinju znanjem kako je industrija radila proteklih godina. Razumijevanje prošlosti može nam pružiti dragocjene uvide vezane za budućnost tvrtke. Stoga je prvi korak preuzeti sva financijska izvješća tvrtke i ista popuniti u excel listu. Za Colgate Palmolive možete preuzeti godišnja izvješća Colgatea Palmolivea iz njihovog Odjela za odnose s investitorima. Jednom kada kliknete na „Godišnje izvješće“, naći ćete prozor kao što je prikazano dolje -

Korak 1B - Stvorite radni list povijesnih financijskih izvještaja

Ako preuzmete 10 000 iz 2013., primijetit ćete da su dostupni samo podaci o financijskim izvještajima za dvije godine. Međutim, u svrhu Financijskog modeliranja u Excelu, preporučeni skup podataka je imati posljednjih 5 godina financijskih izvještaja. Molimo preuzmite posljednje 3 godine godišnjeg izvješća i popunite povijesno.
Mnogi se puta ovi zadaci čine previše dosadni i dosadni jer formatiranje i stavljanje Excela u željeni format može potrajati puno vremena i energije.
Međutim, ne treba zaboraviti da je to posao koji morate obaviti samo jednom za svaku tvrtku, a također popunjavanje povijesnih podataka pomaže analitičaru da razumije trendove i financijske izvještaje.
Stoga, nemojte ovo preskočiti, preuzmite podatke i popunite ih (čak i ako smatrate da je ovo magareće djelo ;-))

Ako želite preskočiti ovaj korak, ovdje možete izravno preuzeti Colgate Palmolive Historical Model.

Izvještaj o dobiti Colgatea s povijesno naseljenim

Povijesni podaci Colgate-ove bilance

# 2 - Analiza omjera

Drugi korak u financijskom modeliranju u Excelu je provođenje analize omjera.

Ključ učenja Financijskog modeliranja u Excelu je mogućnost provođenja temeljne analize. Ako su elementarna analiza ili Ratio Analysis nešto novo za vas, preporučujem vam da malo pročitate na internetu. Namjeravam napraviti detaljnu analizu omjera u jednom od svojih nadolazećih postova, međutim, evo kratke snimke omjera Colgate Palmolive.

VAŽNO - Imajte na umu da sam ažurirao Ratio analizu Colgatea u zasebnom postu. Molimo pogledajte ovu sveobuhvatnu analizu omjera.

Korak 2A - Vertikalna analiza Colgatea

Na računu dobiti i gubitka vertikalna analiza univerzalni je alat za mjerenje relativnog učinka tvrtke iz godine u godinu u smislu troškova i profitabilnosti. Uvijek ga treba uključiti u bilo koju financijsku analizu. Ovdje se izračunavaju postoci u odnosu na neto prodaju, koja se smatra 100%. Ovaj napor vertikalne analize u računu dobiti i gubitka često se naziva analizom marže jer daje različite marže u vezi s prodajom.

Rezultati vertikalne analize

Marža dobiti porasla je za 240 baznih bodova s 56,2% u 2007. na 58,6% u 2013. To je prije svega zbog smanjenih troškova prodaje
Operativna dobit ili EBIT također su pokazali poboljšane marže, povećavajući se sa 19,7% u 2007. na 22,4% u 2012. (rast od 70 baznih bodova). To je bilo zbog smanjenih općih i administrativnih troškova prodaje. Međutim, imajte na umu da su se EBIT marže smanjile u 2013. na 20,4% zbog povećanih "Ostalih troškova". Također, provjerite razliku između EBIT-a i EBITDA-e
Marža neto dobiti porasla je s 12,6% u 2007. na 14,5% u 2012. Međutim, marža dobiti u 2013. smanjila se na 12,9%, ponajprije zbog povećanih „ostalih troškova”.
Zarada po dionici neprestano se povećavala od 2007. do 2012. godine. Međutim, došlo je do laganog pada u EPS-u za poslovnu 2013. godinu
Također imajte na umu da su amortizacija odvojeno navedena u računu dobiti i gubitka. Uključeno je u trošak prodaje.

Korak 2B - Horizontalna analiza Colgatea

Horizontalna analiza tehnika je koja se koristi za procjenu trendova tijekom vremena izračunavanjem postotka povećanja ili smanjenja u odnosu na baznu godinu. Pruža analitičku vezu između računa izračunatih na različite datume koristeći valutu s različitim moćima kupnje. Zapravo, ova analiza indeksira izvješća i uspoređuje njihov razvoj tijekom vremena. Kao i kod metodologije vertikalne analize, pojavit će se pitanja koja treba istražiti i nadopuniti drugim tehnikama financijske analize. Fokus je na traženju simptoma problema koji se mogu dijagnosticirati pomoću dodatnih metoda.

Pogledajmo horizontalnu analizu Colgatea.

Rezultati horizontalne analize

Vidimo da je neto prodaja porasla za 2,0% u 2013. godini.
Također, imajte na umu trend troškova prodaje. Vidimo da nisu porasli u istom omjeru koliko ima Prodaja.
Ova su zapažanja izuzetno korisna dok se u Excelu bavimo financijskim modeliranjem.

Korak 2C - Odnosi likvidnosti Colgatea

Pokazatelji likvidnosti mjere odnos likvidnije imovine poduzeća (one koja se najlakše može pretvoriti u novac) s tekućim obvezama. Najčešći pokazatelji likvidnosti su trenutni omjer, omjer kiselinskog testa (ili brze imovine) Gotovinski omjeri.
Omjeri prometa poput prometa potraživanja, prometa zaliha i prometa obveza

Ključni istaknuti pokazatelji likvidnosti

Trenutni omjer Colgatea značajniji je od 1,0 za sve godine. To implicira da su obrtna sredstva značajnija od tekućih obveza, a možda Colgate ima dovoljnu likvidnost.
Brzi omjer Colgate-a je u rasponu od 0,6-0,7; to znači da Colgates Cash i tržišni vrijednosni papiri mogu platiti čak 70% tekućih obveza. Ovo izgleda kao dobra situacija za Colgatea.
Ciklus naplate novca smanjio se s 43 dana u 2009. na 39 dana u 2013. To je prije svega zbog smanjenja razdoblja naplate potraživanja.

Također, pogledajte ovaj detaljan članak o ciklusu pretvorbe gotovine.

Korak 2D - Odnosi profitabilnosti poslovanja Colgatea

Profitabilnost omjerava sposobnost tvrtke da generira zaradu u odnosu na prodaju, imovinu i kapital

Ključne točke - Omjer profitabilnosti Colgatea

Kao što možemo vidjeti iz gornje tablice, Colgate ima ROE od blizu 100%, što podrazumijeva izvrsne povrate vlasnicima kapitala.

Korak 2E - Analiza rizika Colgatea

Analizom rizika pokušavamo utvrditi hoće li tvrtke moći platiti svoje kratkoročne i dugoročne obveze (dug). Izračunavamo omjere poluge koji su usredotočeni na dostatnost imovine ili generiranje iz imovine. Stope koje se gledaju su

Odnos duga i kapitala
Omjer duga
Omjer pokrivenosti kamatama
Odnos duga i kapitala neprestano se povećavao na višu razinu od 2,23x. To znači povećanu financijsku polugu i rizike na tržištu
Međutim, omjer pokrivenosti kamatama vrlo je visok, što ukazuje na manji rizik od neplaćanja kamata.

# 3 - Financijsko modeliranje u Excelu - Projektirajte račun dobiti i gubitka

Treći korak u financijskom modeliranju je predviđanje računa dobiti i gubitka, pri čemu ćemo započeti s modeliranjem stavki prodaje ili prihoda.

Korak 3A - Projekcije prihoda

Za većinu tvrtki prihodi su temeljni pokretač ekonomskih performansi. Izuzetno je važan dobro osmišljen i logičan model prihoda koji točno odražava vrstu i iznose tokova dohotka. Postoji toliko načina za izradu rasporeda prihoda koliko i tvrtki. Neke uobičajene vrste uključuju:

Rast prodaje: Pretpostavka rasta prodaje u svakom razdoblju definira promjenu u odnosu na prethodno razdoblje. Ovo je jednostavna i često korištena metoda, ali ne nudi uvid u komponente ili dinamiku rasta.
Učinak inflacije i volumena / mješavine: Umjesto jednostavne pretpostavke rasta, koriste se faktor inflacije cijene i faktor volumena. Ovaj koristan pristup omogućuje modeliranje fiksnih i varijabilnih troškova u tvrtkama s više proizvoda i uzima u obzir kretanje cijene i količine.
Jedinični volumen, promjena u količini, prosječna cijena i promjena u cijeni: Ova metoda prikladna je za tvrtke koje imaju jednostavnu kombinaciju proizvoda; omogućuje analizu utjecaja nekoliko ključnih varijabli.
Veličina i rast dolarskog tržišta: Tržišni udio i promjena udjela - korisno u slučajevima kada su dostupne informacije o dinamici tržišta i gdje će ove pretpostavke vjerojatno biti ključne za odluku. Na primjer, industrija telekoma.
Veličina i rast jediničnog tržišta: Ovo je detaljnije od prethodnog slučaja i korisno je kad su cijene na tržištu presudna varijabla. (Na primjer, za tvrtku koja ima strategiju sniženja cijena ili najboljeg proizvođača niša s premium cijenama), npr. Tržište luksuznih automobila
Kapacitet volumena, stopa iskorištavanja kapaciteta i prosječna cijena: Ove pretpostavke mogu biti važne za tvrtke u kojima je proizvodni kapacitet ključan za odluku. (Na primjer, pri kupnji dodatnih kapaciteta ili za utvrđivanje hoće li proširenje zahtijevati nova ulaganja.)
Dostupnost proizvoda i cijene
Prihod je bio potaknut ulaganjem u kapital, marketing ili istraživanje i razvoj
Prihod temeljen na instaliranoj bazi (kontinuirana prodaja dijelova, jednokratnih proizvoda, usluga i dodataka, itd.). Primjeri uključuju klasične tvrtke s britvicama i tvrtke poput računala u kojima je prodaja usluge, softvera i nadogradnji ključna. Modeliranje instalirane baze je ključno (novi dodaci podu, istrošenost tla, kontinuirani prihodi po kupcu, itd.).
Na temelju zaposlenika: Na primjer, prihodi tvrtki s profesionalnim uslugama ili tvrtki sa sjedištem na prodaji, kao što su brokeri. Modeliranje bi se trebalo usredotočiti na neto osoblje, prihod po zaposleniku (često na temelju naplativih sati). Detaljniji modeli uključivat će staž i druge čimbenike koji utječu na cijene.
Zasnovane na prodavaonicama, objektima ili kvadraturi: Trgovinske tvrtke često se modeliraju na temelju trgovina (stare trgovine plus nove trgovine svake godine) i prihoda po trgovini.
Na temelju faktora popunjenosti: Ovaj pristup primjenjiv je na zrakoplovne tvrtke, hotele, kina i druga poduzeća s niskim graničnim troškovima.

Projektiranje prihoda Colgatea

Pogledajmo sada izvještaj Colgate 10K 2013. Primjećujemo da u računu dobiti i gubitka Colgate nije pružio segmentarne informacije; međutim, kao dodatne informacije, Colgate je pružio neke detalje o segmentima na stranici 87 Izvor - Colgate 2013 - 10K, Stranica 86

Budući da nemamo nikakve daljnje informacije o značajkama, na temelju ovih dostupnih podataka projicirat ćemo buduću prodaju Colgatea. Za izvođenje predviđanja upotrijebit ćemo pristup rastu prodaje u svim segmentima. Molimo pogledajte donju sliku. Izračunali smo međugodišnju stopu rasta za svaki element. Sada možemo pretpostaviti postotak rasta prodaje na temelju povijesnih trendova i projicirati prihode po svakom dijelu. Ukupna neto prodaja zbroj je segmenta oralne, osobne i kućne njege i prehrane kućnih ljubimaca.

Korak 3B - Projekcije troškova

Postotak prihoda: Jednostavan, ali ne nudi uvid u bilo koju polugu (ekonomija razmjera ili teret s fiksnim troškovima
Troškovi koji nisu amortizacija kao postotak prihoda i amortizacija iz različitog rasporeda: Ovaj je pristup u većini slučajeva zaista minimalno prihvatljiv i dopušta samo djelomičnu analizu operativne poluge.
Varijabilni troškovi na temelju prihoda ili obujma, fiksni troškovi na temelju povijesnih trendova i amortizacija iz zasebnog rasporeda: Ovaj pristup je minimum potreban za analizu osjetljivosti profitabilnosti na temelju više scenarija prihoda

Projekcije troškova za Colgate

Za projiciranje troškova, korisna će biti prethodno napravljena vertikalna analiza. Pogledajmo vertikalnu analizu -

Budući da smo već predvidjeli prodaju, svi ostali troškovi su neke marže ove prodaje.
Pristup je uzeti smjernice iz povijesnih marži troškova i troškova, a zatim prognozirati buduću maržu.
Primjerice, troškovi prodaje u zadnjih su pet godina bili u rasponu od 41% -42%. Na temelju toga možemo prognozirati marže.
Isto tako, prodajni, opći i administrativni troškovi u povijesti su bili u rasponu od 34% -36%. Na toj osnovi možemo pretpostaviti buduću maržu PSA troškova. Isto tako, možemo nastaviti s drugim skupom troškova.

Koristeći gornje margine, stvarnim vrijednostima možemo pronaći povratne izračune.

Za izračun pričuve za porez koristimo pretpostavku o efektivnoj poreznoj stopi.

Također imajte na umu da ne popunjavamo redak „Rashodi od kamata (prihod)“ jer ćemo naknadno pregledati račun dobiti i gubitka.
Rashodi od kamata i prihodi od kamata.
Također nismo izračunali amortizaciju koja je već uključena u trošak prodaje.
Ovim se dovršava račun dobiti i gubitka (barem zasad!)

# 4- Financijsko modeliranje - raspored obrtnih sredstava

Sad kad smo dovršili račun dobiti i gubitka, četvrti korak u financijskom modeliranju je pregled rasporeda obrtnih sredstava.

Ispod su koraci koje treba slijediti za raspored obrtnih sredstava

Korak 4A - Povežite neto prodaju i troškove prodaje

Korak 4B - Upućivanje na podatke bilance koji se odnose na obrtni kapital

Pozovite se na prošle podatke iz bilance
Izračunajte neto obrtni kapital
Doći do povećanja / smanjenja obrtnog kapitala
Imajte na umu da u obrtni kapital nismo uključili kratkoročni dug i novac i novčane ekvivalente. Dugom i novcem i novčanim ekvivalentima bavit ćemo se odvojeno.

Korak 4C - Izračunajte omjere prometa

Izračunajte povijesne omjere i postotke
Upotrijebite završnu ili prosječnu ravnotežu
Obje su prihvatljive dok se održava dosljednost

Korak 4D - Popunite pretpostavke za buduće stavke obrtnog kapitala

Određeni predmeti bez istaknutog pokretača obično se pretpostavljaju u konstantnim iznosima
Osigurajte da su pretpostavke razumne i u skladu s poslovanjem

Korak 4E - Izradite buduće bilance obrtnog kapitala

Korak 4F - Izračunajte promjene u obrtnom kapitalu

Dolazak u novčane tokove na temelju pojedinačnih stavki retka
Osigurajte da su znakovi točni!

Korak 4G - Povežite predviđeni obrtni kapital s bilancom

Korak 4H - Povežite obrtni kapital s Izvještajem o novčanom tijeku

# 5 - Financijsko modeliranje u Excelu - Raspored amortizacije

Po završetku rasporeda obrtnog kapitala, sljedeći korak u ovom Financijskom modeliranju je projekt Capex Colgate i projiciranje podataka o amortizaciji i imovini. Colgate 2013 - 10K, Stranica 49

Amortizacija nije navedena kao posebna stavka; međutim, on je uključen u trošak prodaje
U takvim slučajevima, pogledajte izvještaje o novčanom tijeku u kojima ćete pronaći trošak amortizacije. Također imajte na umu da su donje brojke 1) amortizacija 2) amortizacija. Pa koji je amortizacijski broj?
Završni saldo za OZO = Početno stanje + Kapital - Amortizacija - Prilagodba za prodaju imovine (BASE jednadžba)

Korak 5A - Povežite podatke o neto prodaji u Rasporedu amortizacije

Postavite stavke retka
Referentna neto prodaja
Ulaz u prošli kapitalni izdatak
Dolazak u Capex kao% neto prodaje

Korak 5B - Predviđanje stavki kapitalnih izdataka

Za predviđanje kapitalnih izdataka postoje različiti pristupi. Jedna uobičajena praksa je da se pogledaju priopćenja za javnost, projekcije upravljanja, MD&A kako bi se razumio pogled tvrtke na buduće kapitalne izdatke
Ako je tvrtka pružila smjernice o budućim kapitalnim izdacima, tada možemo izravno uzeti te brojeve.
Međutim, ako Capexovi brojevi nisu izravno dostupni, tada ih možemo grubo izračunati koristeći Capex kao% prodaje (kao što je učinjeno u nastavku)
Koristite svoju prosudbu na temelju industrijskog znanja i drugih razumnih pokretača.

Korak 5C - Referenca o prošlim informacijama

Za PPE koristit ćemo Završni saldo = Početno stanje + Kapital - Amortizacija - Prilagodba za prodaju imovine (BASE jednadžba)
Komplicirano je uskladiti prošle PP&E zbog prepravki, prodaje imovine itd.
Stoga se preporučuje da se ne miri prošla OZO jer to može dovesti do određene zabune.

Politika amortizacije Colgatea

Primjećujemo da Colgate nije izričito pružio detaljni razlaz imovine. Umjesto toga, svu imovinu udružili su u zemljište, zgrade, strojeve i ostalu opremu
Također, u korisnom je vijeku trajanja strojeva i opreme. U ovom ćemo slučaju morati napraviti neka nagađanja kako bismo došli do prosječnog vijeka trajanja imovine
Također, smjernice za vijek trajanja nisu predviđene za "Ostalu opremu". Morat ćemo procijeniti vijek trajanja druge opreme

Colgate 2013 - 10K, Stranica 55

Ispod je pregled detalja o nekretninama, postrojenjima i opremi za 2012. i 2013. godinu

Colgate 2013 - 10K, Stranica 91

Korak 5D - Procjena raspada nekretnina i opreme (OZO)

Prvo pronađite težine imovine trenutne OZO (2013)
Pretpostavit ćemo da će se ove težine imovine iz PPE-a za 2013. nastaviti nastaviti
Ovu težinu imovine koristimo za izračunavanje raščlanjivanja procijenjenih kapitalnih izdataka

Korak 5E - Procijenite amortizaciju imovine

Napominjemo da ne izračunavamo amortizaciju zemljišta jer zemljište nije imovina koja se amortizira
Za procjenu amortizacije iz poboljšanja zgrada prvo koristimo donju strukturu.
Amortizacija je ovdje podijeljena u dva dijela - 1) amortizacija iz sredstava za poboljšanje zgrada koja su već navedena u bilanci, 2) amortizacija iz budućih poboljšanja zgrada.
Za izračunavanje amortizacije iz poboljšanja zgrada navedenih u imovini koristimo jednostavnu metodu amortizacije Ravne crte.
Za izračun buduće amortizacije prvo transportiramo Capex pomoću funkcije TRANSPOSE u Excelu.
Amortizaciju izračunavamo iz doprinosa imovine svake godine.
Također, amortizacija u prvoj godini podijeljena je s dvije jer pretpostavljamo da je sredinom godine propisano raspoređivanje imovine.

Ukupna amortizacija poboljšanja zgrade = amortizacija od poboljšanja zgrade Asset koji je već naveden na bilanci stanja + amortizacija iz budućih poboljšanja zgrada Gornji postupak za procjenu amortizacije koristi se za izračunavanje amortizacije 1) Proizvodne opreme i strojeva i 2) ostale opreme kao prikazano ispod.

Ostale vrste opreme

Ukupna amortizacija Colgate-a = amortizacija (poboljšanja zgrada) + amortizacija (strojevi i oprema) + amortizacija (dodatna oprema) Nakon što saznamo stvarne podatke o amortizaciji, možemo to staviti u osnovnu jednadžbu kao što je prikazano dolje

Ovim dobivamo završne neto PP&E podatke za svaku od godina

Korak 5F - Povežite neto PP&E s bilancom

# 6 - Raspored amortizacije

Šesti korak u ovom financijskom modeliranju u Excelu je predviđanje amortizacije. Ovdje moramo razmotriti dvije široke kategorije - 1) dobru volju i 2) ostale nematerijalne uloge.

Korak 6A - Predviđanje dobre volje

Colgate 2013 - 10K, Stranica 61

Goodwill dolazi u bilancu kad poduzeće stekne drugo poduzeće. Obično je složeno projicirati Goodwill za buduće godine.
Međutim, Goodwill se svake godine podvrgava testovima umanjenja vrijednosti koje provodi sama tvrtka. Analitičari nisu u mogućnosti provoditi takva ispitivanja i pripremati procjene umanjenja vrijednosti.
Većina analitičara ne projicira dobru volju; oni to drže kao konstantu, što ćemo i učiniti u našem slučaju.

Korak 6B - Predviđanje ostale nematerijalne imovine

Kao što je navedeno u Colgateovom izvještaju 10K, većina nematerijalnog životnog vijeka povezana je s akvizicijom Sanexa
"Dodaci nematerijalnim materijalima" također su složeni za projiciranje
Izvješće Colgate-a od 10 tisuća pruža nam detalje o sljedećih pet godina troškova amortizacije.
Te ćemo procjene koristiti u našem Financijskom modelu Colgate 2013 - 10K, Stranica 61

Korak 6C - Završetak neto nematerijalnih ulaganja povezan je s „Drugom nematerijalnom imovinom“.

Korak 6D - povežite amortizaciju s izvještajima o novčanom tijeku

Korak 6E - Povežite kapital i dodatak s nematerijalnim ulogama s izvještajima o novčanom toku

# 7 - Ostali dugoročni raspored

Sljedeći korak u ovom financijskom modeliranju je priprema drugog dugoročnog rasporeda. Tada se pripremamo za "ostatke" koji nemaju određene pokretače za predviđanje. U slučaju Colgatea, ostale dugoročne stavke (ostaci) bili su odgođeni porezi na dobit (obveze i imovina), ostala ulaganja i ostale obveze.

Korak 7A - Upućivanje na povijesne podatke iz bilance

Također izračunajte promjene u tim stavkama.

Korak 7B - Predviđanje dugoročne imovine i obveza

Držite dugoročne stavke konstantnim tijekom predviđenih godina u slučaju da nema vidljivih pokretača
Povežite predviđene dugoročne stavke s bilancom stanja kao što je prikazano u nastavku

Korak 7C - Uključivanje ostalih dugoročnih stavki u bilancu

Korak 7D - Povežite dugoročne stavke s Izvještajem o novčanom tijeku

Imajte na umu da ako dugoročnu imovinu i obveze držimo konstantnima, promjena koja teče u izvještaju o novčanom tijeku bila bi nula.

# 8 - Financijsko modeliranje u Excelu - Popunjavanje računa dobiti i gubitka

Prije nego što krenemo dalje u ovom financijskom modeliranju temeljenom na Excelu, vratit ćemo se i pregledati račun dobiti i gubitka
Popunite povijesni osnovni ponderirani prosječni udio i razrijeđeni ponderirani prosječni broj dionica
Te su brojke dostupne u Colgateovom 10K izvješću

Korak 8A - Navedite osnovne i razrijeđene dionice

U ovoj fazi pretpostavimo da će budući broj primarnih i razrijeđenih dionica ostati isti kao u 2013. godini.

Korak 8B - Izračunajte osnovnu i razrijeđenu zaradu po dionici.

Ovim smo spremni prijeći na sljedeći raspored, tj. Na Dionički plan kapitala.

# 9 - Financijsko modeliranje - Dionički plan kapitala

Sljedeći korak u ovom financijskom modeliranju u programu Excel Training je razmatranje rasporeda dioničkih kapitala. Primarni cilj ovog rasporeda je projicirati stavke povezane s glavnicom, kao što su dionički kapital, dividende, otkup dionica, prihodi od opcija, itd . Izvješće Colgate-a od 10 tisuća pruža nam detalje o uobičajenim dionicama i trezorskim dionicama u proteklim godinama, kao što je prikazano u nastavku . Colgate 2013 - 10K, Stranica 68

Korak 9A - Podijelite otkup: popunite povijesne brojeve

Povijesno gledano, Colgate je otkupio dionice, kao što možemo vidjeti gore navedeni raspored.
Otkupite Colgate-ove dionice (milijune) u excel listu.
Povežite povijesni razrijeđeni EPS iz bilansa uspjeha
Na povijesni iznos otkupljenih proizvoda treba se pozivati iz izvještaja o novčanom tijeku.

Također, pogledajte Ubrzani otkup dionica.

Korak 9B - Otkup dionica: Izračunajte višestruki PE (EPS višestruki)

Izračunajte impliciranu prosječnu cijenu po kojoj je Colgate u prošlosti obavljao otkup dionica. To se izračunava kao Otkupljeni iznos / Broj dionica.
Izračunajte PE višestruko = implicirana cijena dionice / EPS

Korak 9C - Otkup dionica: Pronalaženje otkupa dionice Colgate

Colgate nije službeno objavio koliko dionica namjerava otkupiti. Jedina informacija da su njihove dionice izvijestile 10 tisuća su da su odobrili otkup do 50 milijuna dionica. Colgate 2013 - 10K, Stranica 35

Da bismo pronašli broj otkupljenih dionica, moramo pretpostaviti iznos otkupa dionica. Na temelju povijesnog iznosa otkupa, uzeo sam ovaj broj poput 1500 milijuna dolara za sve buduće godine.
Da bismo pronašli broj otkupljenih dionica, potrebna nam je predviđena implicirana cijena dionice potencijalnog otkupa.
Stvarna cijena dionice = pretpostavljeni PE multiplex EPS.
Može se pretpostaviti da će se višestruko otkupiti PE višestruko na temelju povijesnih trendova. Primjećujemo da je Colgate otkupio dionice u prosječnom opsegu PE od 17x - 25x
Ispod je snimka Reutersa koja nam pomaže da potvrdimo PE raspon za Colgate

www.reuters.com

U našem slučaju pretpostavio sam da će svi budući otkupi Colgatea biti PE višestruki od 19x.
Koristeći PE od 19x, možemo pronaći impliciranu cijenu = EPS x 19
Sad kad smo pronašli impliciranu cijenu, možemo vidjeti broj otkupljenih dionica = iznos koji se koristi za otkup / impliciranu cijenu.

Korak 9D - Dionice: popunite povijesne podatke

Iz sažetka redovnih dionica i dioničkog kapitala znamo koliko se opcija svake godine koristi.

Osim toga, imamo i mogućnost prihoda od izvještaja o novčanom tijeku (približno)

Ovim bismo trebali biti u mogućnosti pronaći učinkovitu cijenu udara.

Colgate 2013 - 10K, Stranica 53

Također imajte na umu da opcije dionica imaju ugovorne uvjete od šest godina i prsluk dulji od tri godine. Colgate 2013 - 10K, Stranica 69

Ovim podacima popunjavamo podatke o mogućnostima kako je navedeno u nastavku. Također primjećujemo da je ponderirana prosječna udarna cijena dionica za 2013. godinu iznosila 42 USD, a broj opcija koje se mogu iskoristiti bio je 24,151 milijuna Colgate 2013 - 10 000 , Stranica 70

Korak 9E - Dioničke opcije: pronađite prihode od opcije.

Stavljajući ove brojeve u naše podatke o opcijama u nastavku, napominjemo da prihod od opcije iznosi 1,014 milijardi USD.

Korak 9F - Mogućnosti dionica: Podaci o ograničenim zalihama s prognozom

Uz opcije dionica, postoje ograničene jedinice dionica koje se daju zaposlenicima s ponderiranim prosječnim razdobljem od 2,2 godine Colgate 2013 - 10.000, Stranica 81

Popunjavanje ovih podataka u skup podataka Opcija Radi jednostavnosti, nismo projicirali izdavanje opcija (znam da ovo nije ispravna pretpostavka; međutim, zbog nedostatka podataka, ne vodim više nijednu problematiku opcija. Upravo smo ih prenijeli kao nula, što je istaknuto u sivoj zoni iznad. Osim toga, predviđa se da će ograničene jedinice zaliha prema naprijed iznositi 2,0 milijuna.

Također, pogledajte metodu trezorskih dionica.

Korak 9G - Dividende: Predvidite dividende

Predviđajte procijenjene dividende koristeći omjer isplate dividende.
Fiksni trošak dividende po isplati po dionici
Iz 10K izvješća izdvajamo sve prošle informacije o dividendama.
Pomoću podataka o isplaćenim dividendama možemo saznati omjer isplate dividende = Ukupne isplaćene dividende / neto prihod.
Izračunao sam omjer isplate dividendi Colgatea kako je prikazano u nastavku - Primjećujemo da se omjer isplate dividende uglavnom kretao u rasponu od 50% -60%. Pretpostavimo da će omjer isplate dividende iznositi 55% u budućim godinama.
Također možemo povezati projicirani neto prihod s računom dobiti i gubitka.
Koristeći i projicirani neto prihod i omjer isplate dividende, možemo pronaći ukupnu isplaćenu dividendu.

Korak 8H - Predvidite račun kapitala u cijelosti

S prognozom otkupa dionica, prihoda od opcija i isplaćenih dividendi, spremni smo dovršiti Dionički plan dionica. Povežite sve ove kako biste pronašli Završni kapitalni bilans za svaku godinu, kao što je prikazano u nastavku.

Korak 9I - Povežite završni dionički kapital s bilancom

Korak 9J - Povežite dividendu, otkup dionica i opcije, a prelazi na CF

# 10 - Dijeli izvanredan raspored

Sljedeći korak u ovom mrežnom financijskom modeliranju u programu Excel jest pregled raspona dionica. Sažetak rasporeda neizmirenih dionica

Osnovne dionice - stvarne i prosječne
Snimite prošle učinke opcija i kabrioleta po potrebi
Razrijeđene dionice - prosjek
Referentne dionice otkupljene i nove dionice iz izvršenih opcija
Izračunajte predviđene sirove postotke (stvarne)
Izračunajte prosječne osnovne i razrijeđene dionice
Upućivanje predviđenih dionica na račun dobiti i gubitka (podsjetimo na izvještaj o dobiti izrađen!)
Ulazni povijesni podaci dijele nepodmirene informacije
Napomena : Ovaj je raspored obično integriran s rasporedom kapitala

Korak 10A - Unesite povijesne brojeve iz 10K izvješća

Na izdane dionice (stvarna realizacija opcija) i otkupljene dionice može se uputiti iz rasporeda dioničara
Ulazni podaci ponderirali su prosječni broj dionica i učinak opcija dionica za povijesne godine.

Korak 10B - Povežite izdavanja i otkupe dionica iz Dionica glavnice.

Osnovne dionice (završetak) = Osnovne dionice (početak) + Izdavanja dionica - Dionice su otkupljene.

Korak 10C - Pronađite osnovne ponderirane prosječne udjele,

nalazimo u prosjeku dvije godine, kao što je prikazano u nastavku.
Također, dodajte učinak opcija i ograničenih dionica (navedeno u rasporedu dioničara) kako biste pronašli razrijeđene ponderirane prosječne dionice.

Korak 10D - Povežite osnovne i razrijeđene ponderirane dionice s računom dobiti i gubitka

Sad kad smo izračunali razrijeđeni ponderirani prosječni udio, vrijeme je da iste ažuriramo u računu dobiti i gubitka.
Povežite predviđene razrijeđene ponderirane prosječne udjele u bilansu uspjeha kako je prikazano u nastavku

Ovim dovršavamo raspored dionica i vrijeme da prijeđemo na sljedeći skup izjava.

# 11 - Popunjavanje izvještaja o novčanom tijeku

Važno nam je u potpunosti ispuniti izvještaje o novčanom tijeku prije nego što prijeđemo na sljedeći i konačni raspored u ovom Financijskom modeliranju, tj. Na rasporedu duga. Do ove faze postoji samo nekoliko nepotpunih stvari

Izvještaj o dobiti - trošak / prihod od kamata u ovoj su fazi nepotpuni
Bilans stanja - stavke gotovine i duga u ovoj su fazi nepotpune

Korak 11A - Izračunajte novčani tijek za financiranje aktivnosti

Također, pogledajte Novčani tijek iz financiranja

Korak 11B - Pronađite neto povećanje (smanjenje) novca i novčanih ekvivalenata

Korak 11C = Ispunite izvještaje o novčanom tijeku

Pronađite novac na kraju godine i novčane ekvivalente na kraju godine.

Korak 11D - Povežite novac i novčane ekvivalente s bilancom.

Sada smo spremni voditi računa o svom posljednjem i konačnom rasporedu, tj. Rasporedu dugova i kamata

# 12- Financijsko modeliranje u Excelu - Raspored duga i kamata

Sljedeći korak u ovom internetskom financijskom modeliranju je popunjavanje rasporeda duga i kamata. Sažetak duga i kamata - raspored

Ocjena 12A - Postavite raspored duga

Navedite novčani tok dostupan za financiranje
Navedite sve izvore glavnice i uporabe novca

Korak 12B - Izračunajte novčani tijek od otplate duga

Pogledajte početni saldo gotovine u bilanci
Odbiti minimalni saldo gotovine. Pretpostavili smo da bi Colgate želio zadržati najmanje 500 milijuna dolara svake godine.

Za sada preskočite izdavanje / otplate dugoročnog duga, gotovinu dostupnu za revolving kreditnu liniju i odjeljak Revolver za sada iz Colgate-ovog 10K izvještaja; bilježimo dostupne detalje o Revolved Credit Facility Colgate 2013 - 10K, Stranica 35

Također se u dodatnim informacijama o dugu navodi i počinjena dugoročna otplata duga. Colgate 2013 - 10K, Stranica 36

Korak 12C - Izračunajte krajnji dugoročni dug.

Koristimo gore navedeni raspored otplate dugoročnog duga i izračunavamo krajnji saldo otplate dugoročnog duga.

Korak 12D - Povežite dugoročnu otplatu duga.

Korak 12E - Izračunajte diskrecijsko posuđivanje / preračunavanje.

Koristeći formulu razmjene gotovine, kao što je prikazano u nastavku, izračunajte diskrecijske zajmove / isplate.

Korak 12F - Izračunajte rashode kamata iz dugoročnog duga

Izračunajte prosječni saldo za revolving kreditnu liniju i dugoročni dug
Napravite razumnu pretpostavku za kamatnu stopu na temelju podataka danih u 10K izvješću
Izračunajte ukupni trošak kamata = prosječno stanje duga x kamata

Pronađite ukupni trošak kamata = Kamata (revolving kreditna linija) + kamata (dugoročni dug)

Korak 12G - Povlačenje duga i revolvera na glavnoj poveznici s novčanim tokovima

Korak 12H - Referenca trenutne i dugoročne bilance stanja

Razgraničite trenutni udio dugoročnog duga i dugoročnog duga kako je prikazano u nastavku

Povezati revolving kreditnu liniju, dugoročni dug i trenutni dio dugoročnog duga s bilancom

Korak 12I - Izračunajte prihod od kamata koristeći prosječni novčani saldo

Korak 12J - Povežite trošak kamata i prihod od kamata s bilansom uspjeha

Izvršite provjeru bilance: Ukupna imovina = Obveze + Dionički kapital

Korak 12K - Revizija bilance

Ako postoji neko odstupanje, tada moramo revidirati model i provjeriti ima li pogrešaka u povezivanju

Preporučeni tečaj financijskog modeliranja

Nadam se da ste uživali u besplatnom vodiču za financijsko modeliranje Excel. Ako želite naučiti financijsko modeliranje u Excelu kroz naša stručna video predavanja, možete pogledati i naš trening o investicijskom bankarstvu. Riječ je prvenstveno o 99 kolegija paketa obuke za investicijsko bankarstvo. Ovaj tečaj započinje s osnovama i vodi vas do napredne razine posla za investicijsko bankarstvo. Ovaj tečaj podijeljen je u pet dijelova -

Dio 1 - Obuka za investicijsko bankarstvo - Osnovni tečajevi
(26 tečajeva)
Dio 2 - Obuka za napredno modeliranje investicijskog bankarstva
(20 tečajeva)
Dio 3 - Dodaci za investicijsko bankarstvo
(13 tečajeva)
Dio 4 - Tečajevi zaklade za investicijsko bankarstvo
(23 tečaja)
Dio 5 - Soft Skills za investicijske bankare
(17 tečajeva)

Preuzimanje financijskih modela

Financijski model Alibabe
Okvir IPO financijski model
Predlošci financijskog modeliranja
Tečaj bankarskog financijskog modeliranja

Što dalje?

Ako ste naučili nešto novo ili ste uživali u ovom financijskom modeliranju temeljenom na Excelu, ostavite komentar u nastavku. Javi mi što misliš. Puno hvala i čuvajte se. Sretno učenje!