Finančno modeliranje v Excelu (brezplačni vodnik po korakih + predloga)

Kazalo

Kaj je finančno modeliranje v Excelu?

Finančno modeliranje v Excelu je postopek gradnje finančnega modela, ki predstavlja transakcijo, operacijo, združitev, prevzem, finančne informacije, da se analizira, kako lahko sprememba ene spremenljivke vpliva na končni donos, da se sprejme odločitev o enem ali več omenjene finančne transakcije.

Kaj je finančno modeliranje v Excelu?

Finančno modeliranje v Excelu je povsod po spletu. O učenju finančnega modeliranja je bilo že veliko napisanega; vendar je večina usposabljanj za finančno modeliranje enakih. To presega običajne nesmisle in raziskuje praktično finančno modeliranje, ki ga uporabljajo investicijski bankirji in raziskovalni analitiki.

V tem vodniku za brezplačno finančno modeliranje Excel bom vzel primer Colgate Palmolive in iz nič pripravil popolnoma integriran finančni model.

Ta priročnik obsega več kot 6000 besed, zato sem ga potreboval tri tedne. To stran shranite za poznejšo uporabo in je ne pozabite deliti :-)

NAJPOMEMBNEjše - prenesite predlogo Excel za finančno modeliranje Colgate in sledite navodilom

Prenesite predlogo finančnega modela Colgate

Naučite se finančno modeliranje po korakih v Excelu

Finančno modeliranje v programu Excel Training - Najprej me preberite

1. korak - prenesite predlogo finančnega modela Colgate. To predlogo boste uporabili za vadnico

Prenesite finančni model Colgate

Vnesite e-poštni naslov Če nadaljujete z zgornjim korakom, se strinjate z našimi pogoji uporabe in politiko zasebnosti.

2. korak - Prosimo, upoštevajte, da boste dobili dve predlogi - 1) Nerešeni finančni model Colgate Palmolive 2) Rešeni finančni model Colgate Palmolive

3. korak Delovali boste na nerazrešeni predlogi finančnega modela Colgate Palmolive . Sledite navodilom po korakih, da pripravite popolnoma integriran finančni model.

4. korak - srečno učenje!

Kazalo

Za to finančno modeliranje sem naredil enostavno krmarjenje po vsebini

Kako zgraditi finančni model?
# 1 - Colgateov finančni model - zgodovinski
# 2 - Analiza razmerja Colgate Palmolive
# 3 - Projektiranje izkaza poslovnega izida
# 4- Pregled obratovalnega kapitala
# 5 - Razpored amortizacije
# 6 - Amortizacijski načrt
# 7 - Drugi dolgoročni načrt
# 8 - Izpolnjevanje izkaza poslovnega izida
# 9 - Delniški načrt lastniškega kapitala
# 10 - Deli izjemno časovnico
# 11 - Izpolnjevanje izkazov denarnega toka
# 12 - Priporočen seznam dolgov in obresti
Tečaj finančnega modeliranja
Brezplačni finančni modeli

Če ste nov v finančnem modeliranju, si oglejte ta vodnik o tem, kaj je finančno modeliranje?

Kako zgraditi finančni model v Excelu?

Poglejmo, kako je finančni model zgrajen iz nič. Ta podroben vodnik za finančno modeliranje vam bo ponudil podroben vodnik za ustvarjanje finančnega modela. Primarni pristop v tem priročniku za finančno modeliranje je modularni. Modularni sistem v bistvu pomeni sestavljanje osnovnih izkazov, kot so izkazi poslovnega izida, bilanca stanja in denarni tokovi z uporabo različnih modulov / urnikov. Ključni poudarek je, da vsako izjavo pripravimo korak za korakom in ob zaključku vse podporne programe povežemo z osnovnimi izjavami. Razumem, da to zdaj morda ni jasno; vendar boste spoznali, da je to zelo enostavno, ko gremo naprej. Spodaj si lahko ogledate različne urnike / module finančnega modeliranja -

Prosimo, upoštevajte naslednje -

Temeljni izkazi so izkaz poslovnega izida, bilanca stanja in denarni tokovi.
Različni urniki so amortizacijski načrt, urnik obratnega kapitala, časovni načrt neopredmetenih sredstev, delniški kapital, drugi dolgoročni postavki, dolžniški red itd.
Različni urniki so po zaključku povezani z osnovnimi izjavami.
V tem priročniku za finančno modeliranje bomo iz nič izdelali integrirani ekonomski model Colgate Palmolive.

# 1 - Finančno modeliranje v Excelu - Projektirajte zgodovino

Prvi korak v Vodiču za finančno modeliranje je priprava Zgodovinskega poročila.

Korak 1A - Prenesite poročila podjetja Colgate 10K

»Finančni modeli so pripravljeni v excelu, prvi koraki pa se začnejo z vedenjem, kako je šlo v industriji v zadnjih letih. Razumevanje preteklosti nam lahko prinese dragocen vpogled v prihodnost podjetja. Zato je prvi korak prenesti vse finančne podatke podjetja in jih vnesti v excel list. Za Colgate Palmolive lahko prenesete letna poročila Colgate Palmolive iz njihovega oddelka za odnose z vlagatelji. Ko kliknete »Letno poročilo«, boste našli okno, kot je prikazano spodaj -

Korak 1B - Ustvarite delovni list zgodovinskih računovodskih izkazov

Če prenesete 10.000 za leto 2013, boste opazili, da so na voljo le dve leti računovodskih izkazov. Vendar pa je za namen finančnega modeliranja v Excelu priporočljiv nabor podatkov, da so v zadnjih 5 letih računovodski izkazi. Prenesite zadnja 3 leta letnega poročila in izpolnite zgodovino.
Mnogokrat se zdijo te naloge preveč dolgočasne in dolgočasne, saj lahko traja veliko časa in energije, da formatirate in postavite excel v želeno obliko.
Vendar ne smemo pozabiti, da je to delo, ki ga morate za vsako podjetje opraviti le enkrat, prav tako pa tudi zapolnitev zgodovine analitiku pomaga razumeti trende in računovodske izkaze.
Zato tega ne preskočite, prenesite podatke in jih zapolnite (tudi če menite, da je to oslovsko delo ;-))

Če želite preskočiti ta korak, lahko tukaj neposredno prenesete zgodovinski model Colgate Palmolive.

Izkaz prihodka Colgate z zgodovinsko poseljenimi

Zgodovinski podatki o bilanci stanja Colgate

# 2 - Analiza razmerja

Drugi korak pri finančnem modeliranju v Excelu je izvedba analize razmerja.

Ključ do učenja finančnega modeliranja v Excelu je sposobnost temeljite analize. Če je elementarna analiza ali analiza razmerja za vas nekaj novega, priporočam, da malo preberete na internetu. V enem od prihajajočih objav nameravam narediti poglobljeno analizo razmerja, vendar je tukaj kratek posnetek razmerja Colgate Palmolive.

POMEMBNO - Upoštevajte, da sem analizo razmerja Colgate posodobil v ločeni objavi. Oglejte si to obsežno analizo razmerja.

Korak 2A - Vertikalna analiza Colgatea

V izkazu poslovnega izida je vertikalna analiza univerzalno orodje za merjenje relativne uspešnosti podjetja iz leta v leto glede na stroške in donosnost. Vedno ga je treba vključiti v katero koli finančno analizo. Tu se izračunajo odstotki glede na neto prodajo, ki velja za 100%. Ta napor vertikalne analize v izkazu poslovnega izida se pogosto imenuje analiza marže, saj daje različne marže v zvezi s prodajo.

Rezultati vertikalne analize

Stopnja dobička se je povečala za 240 bazičnih točk s 56,2% v letu 2007 na 58,6% v letu 2013. To je predvsem posledica zmanjšanih stroškov prodaje
Dobiček iz poslovanja ali EBIT je prav tako pokazal izboljšane marže, in sicer z 19,7% v letu 2007 na 22,4% v letu 2012 (povečanje za 70 bazičnih točk). To je bilo posledica zmanjšanih splošnih in administrativnih stroškov prodaje. Upoštevajte pa, da so se marže EBIT v letu 2013 zmanjšale na 20,4% zaradi povečanih "drugih stroškov". Preverite tudi razliko med EBIT in EBITDA
Stopnja čistega dobička se je povečala z 12,6% v letu 2007 na 14,5% v letu 2012. Vendar se je stopnja dobička v letu 2013 zmanjšala na 12,9%, predvsem zaradi povečanih "drugih stroškov".
Čisti dobiček na delnico se je od leta 2007 do leta 2012 nenehno povečeval. Vendar se je v poslovnem letu 2013 nekoliko zmanjšal EPS
Upoštevajte tudi, da sta amortizacija v izkazu poslovnega izida navedena ločeno. Vključeno je v stroške prodaje.

Korak 2B - Horizontalna analiza Colgatea

Horizontalna analiza je tehnika, ki se uporablja za ocenjevanje trendov skozi čas z izračunom odstotka povečanja ali zmanjšanja glede na bazno leto. Zagotavlja analitično povezavo med računi, izračunanimi na različne datume z uporabo valute z različnimi nakupnimi močmi. Ta analiza dejansko indeksira poročila in primerja njihov razvoj skozi čas. Tako kot pri metodologiji vertikalne analize se bodo pojavila vprašanja, ki jih je treba raziskati in dopolniti z drugimi tehnikami finančne analize. Poudarek je na iskanju simptomov težav, ki jih je mogoče diagnosticirati z dodatnimi metodami.

Oglejmo si vodoravno analizo Colgatea.

Rezultati horizontalne analize

Vidimo, da se je neto prodaja v letu 2013 povečala za 2,0%.
Upoštevajte tudi trend stroškov prodaje. Vidimo, da niso zrasli v enakem deležu kot Prodaja.
Ta opažanja so zelo priročna, medtem ko se v Excelu ukvarjamo s finančnim modeliranjem.

Korak 2C - Likvidnostna razmerja Colgate

Likvidnostni količniki merijo razmerje med likvidnejšimi sredstvi podjetja (tistimi, ki jih je najlažje pretvoriti v denar) s kratkoročnimi obveznostmi. Najpogostejši količniki likvidnosti so trenutni količnik, razmerje kislega testa (ali hitro sredstvo) Denarna razmerja.
Razmerja med prihodki, kot so prihodek od prodaje terjatev, promet zalog in prihodek od obveznosti

Ključni poudarki količnikov likvidnosti

Trenutno razmerje Colgate je za vsa leta pomembnejše od 1,0. To pomeni, da so obratna sredstva pomembnejša od kratkoročnih obveznosti in ima Colgate morda dovolj likvidnosti.
Hitro razmerje Colgate je v območju 0,6-0,7; to pomeni, da lahko Colgates Cash in tržni vrednostni papirji plačajo kar 70% tekočih obveznosti. To se zdi dobra situacija za Colgatea.
Cikel izterjave denarja se je zmanjšal s 43 dni v letu 2009 na 39 dni v letu 2013. To je predvsem posledica zmanjšanja obdobja izterjave terjatev.

Oglejte si tudi ta podroben članek o ciklu pretvorbe gotovine.

Korak 2D - Koeficienti donosnosti poslovanja podjetja Colgate

Donosnost razmeri sposobnost podjetja, da ustvari zaslužek glede na prodajo, sredstva in kapital

Ključni poudarki - razmerja donosnosti podjetja Colgate

Kot lahko vidimo iz zgornje tabele, ima Colgate donosnost naložbe približno 100%, kar imetnikom lastniškega kapitala pomeni odlične donose.

Korak 2E - Analiza tveganja Colgate

Z analizo tveganja poskušamo oceniti, ali bodo podjetja sposobna plačevati kratkoročne in dolgoročne obveznosti (dolg). Izračunamo količnike finančnega vzvoda, ki se osredotočajo na zadostnost sredstev ali ustvarjanje sredstev. Cene, ki si jih ogledujemo, so

Razmerje med dolgom in lastniškim kapitalom
Razmerje dolga
Razmerje pokritosti obresti
Razmerje med dolgom in lastniškim kapitalom se je nenehno povečevalo na višjo raven 2,23-krat. To pomeni povečan finančni vzvod in tveganja na trgu
Vendar je razmerje pokritosti obresti zelo visoko, kar kaže na manjše tveganje neplačila obresti.

# 3 - Finančno modeliranje v Excelu - Projecirajte izkaz poslovnega izida

Tretji korak finančnega modeliranja je napoved izkaza poslovnega izida, pri čemer bomo začeli z modeliranjem postavk prodaje ali prihodkov.

Korak 3A - Projekcije prihodkov

Za večino podjetij so prihodki temeljno gonilo gospodarske uspešnosti. Izredno pomemben je dobro zasnovan in logičen model prihodkov, ki natančno odraža vrsto in zneske dohodkovnih tokov. Obstaja toliko načinov, kako ustvariti časovni razpored prihodkov, kot je podjetij. Nekatere pogoste vrste vključujejo:

Rast prodaje: Predpostavka o rasti prodaje v vsakem obdobju opredeljuje spremembo v primerjavi s prejšnjim obdobjem. To je preprosta in pogosto uporabljena metoda, vendar ne ponuja vpogleda v sestavne dele ali dinamiko rasti.
Inflacijski in količinski / mešani učinki: Namesto preproste predpostavke o rasti se uporabljata faktor inflacije cen in količinski faktor. Ta uporaben pristop omogoča modeliranje fiksnih in spremenljivih stroškov v podjetjih z več izdelki in upošteva gibanje cen v primerjavi s količino.
Količina enot, sprememba obsega, povprečna cena in sprememba cene: ta metoda je primerna za podjetja, ki imajo preprosto kombinacijo izdelkov; omogoča analizo vpliva več ključnih spremenljivk.
Velikost in rast dolarskega trga: tržni delež in sprememba deleža - koristno za primere, ko so na voljo informacije o tržni dinamiki in kjer so te predpostavke verjetno bistvene za odločitev. Na primer, telekomunikacijska industrija.
Velikost in rast enotnega trga: to je podrobneje kot v prejšnjem primeru in je koristno, če so cene na trgu ključna spremenljivka. (Na primer za podjetja s strategijo diskontiranja cen ali najboljšega nišnega igralca z najboljšo ceno), npr. Trg luksuznih avtomobilov
Količinska zmogljivost, stopnja izkoriščenosti zmogljivosti in povprečna cena: te predpostavke so lahko pomembne za podjetja, kjer je proizvodna zmogljivost bistvenega pomena za odločitev. (Na primer pri nakupu dodatnih zmogljivosti ali za določitev, ali bi širitev zahtevala nove naložbe.)
Razpoložljivost izdelkov in cene
Prihodke so spodbujale naložbe v kapital, trženje ali raziskave in razvoj
Prihodki glede na nameščeno bazo (nadaljnja prodaja delov, materialov za enkratno uporabo, storitev in dodatkov itd.). Primeri vključujejo klasična podjetja z britvicami in podjetja, kot so računalniki, kjer je prodaja storitev, programske opreme in nadgradenj bistvenega pomena. Modeliranje nameščene baze je ključnega pomena (novi dodatki tal, obraba tal, nadaljnji prihodki na kupca itd.)
Zaposleni: na primer prihodki podjetij za profesionalne storitve ali prodajnih podjetij, kot so posredniki. Modeliranje se mora osredotočiti na neto število zaposlenih, prihodke na zaposlenega (pogosto na podlagi obračunanih ur). Podrobnejši modeli bodo vključevali starost in druge dejavnike, ki vplivajo na oblikovanje cen.
Trgovina, objekt ali kvadratni metri: maloprodajna podjetja so pogosto oblikovana na podlagi trgovin (stare prodajalne in nove trgovine v vsakem letu) in prihodka na trgovino.
Na podlagi faktorjev zasedenosti: ta pristop velja za letalske družbe, hotele, kinodvorane in druga podjetja z nizkimi mejnimi stroški.

Projektiranje prihodkov podjetja Colgate

Oglejmo si zdaj poročilo Colgate 10K 2013. Ugotavljamo, da Colgate v izkazu poslovnega izida ni zagotovil segmentnih informacij; kot dodatne informacije pa je Colgate navedel nekaj podrobnosti o segmentih na strani 87 Vir - Colgate 2013 - 10K, Stran 86

Ker o funkcijah nimamo dodatnih informacij, bomo prihodnjo prodajo Colgate načrtovali na podlagi teh razpoložljivih podatkov. Za pripravo napovedi bomo uporabili pristop rasti prodaje po segmentih. Glejte spodnjo sliko. Za vsak element smo izračunali medletno stopnjo rasti. Zdaj lahko na podlagi preteklih trendov predpostavimo odstotek rasti prodaje in predvidevamo prihodke za vsak del. Skupna neto prodaja je vsota segmenta oralne, osebne in domače nege ter prehrane hišnih ljubljenčkov.

Korak 3B - Projekcije stroškov

Odstotek prihodkov: preprost, vendar ne ponuja vpogleda v vzvod (ekonomija obsega ali breme s fiksnimi stroški
Stroški, ki niso amortizacija kot odstotek prihodkov in amortizacija po drugačnem časovnem razporedu: Ta pristop je v večini primerov resnično minimalno sprejemljiv in omogoča le delno analizo finančnega vzvoda.
Spremenljivi stroški glede na prihodek ali obseg, fiksni stroški, ki temeljijo na preteklih trendih, in amortizacija iz ločenega časovnega razporeda: Ta pristop je najmanj potreben za analizo občutljivosti donosnosti na podlagi več scenarijev prihodkov

Napovedi stroškov za Colgate

Za načrtovanje stroškov bo v pomoč prej opravljena vertikalna analiza. Oglejmo si vertikalno analizo -

Ker smo prodajo že napovedali, so vsi ostali stroški nekaj marž te prodaje.
Pristop je sprejeti smernice iz preteklih marž stroškov in stroškov in nato napovedati prihodnjo maržo.
Na primer, stroški prodaje se v zadnjih petih letih gibljejo med 41% in 42%. Na tej podlagi si lahko ogledamo napovedovanje marž.
Podobno so prodajni, splošni in upravni stroški v preteklosti znašali med 34% in 36%. Na tej podlagi lahko predvidevamo prihodnjo stopnjo PSA-stroškov. Prav tako lahko nadaljujemo z drugo vrsto stroškov.

Z uporabo zgornjih robov lahko z izračuni za nazaj poiščemo dejanske vrednosti.

Za izračun rezervacij za davke uporabljamo predpostavko efektivne davčne stopnje.

Upoštevajte tudi, da vrstice »Odhodki za obresti (dohodek)« ne izpolnimo, saj bomo pozneje pregledali izkaz poslovnega izida.
Odhodki za obresti in dohodki od obresti.
Prav tako nismo izračunali amortizacije, ki je že bila vključena v prodajne stroške.
S tem izpolnite izkaz poslovnega izida (vsaj zaenkrat!)

# 4- Finančno modeliranje - načrt obratovalnega kapitala

Zdaj, ko smo izpolnili izkaz poslovnega izida, je četrti korak v finančnem modeliranju pogled na načrt obratovalnega kapitala.

Spodaj so navedeni koraki, ki jih je treba upoštevati pri seznamu obratnih sredstev

Korak 4A - Povežite neto prodajo in stroške prodaje

Korak 4B - Sklic na podatke bilance stanja v zvezi z obratnimi sredstvi

Sklic na pretekle podatke iz bilance stanja
Izračunajte neto obratna sredstva
Prihod do povečanja / zmanjšanja obratnega kapitala
Upoštevajte, da v obratna sredstva nismo vključili kratkoročnega dolga in denarnih sredstev. Z dolgom in denarnimi sredstvi in njihovimi ustrezniki bomo obravnavali ločeno.

Korak 4C - Izračunajte razmerja prometa

Izračunajte zgodovinska razmerja in odstotke
Uporabite končno ali povprečno stanje
Oba sta sprejemljiva, dokler se ohranja doslednost

Korak 4D - Izpolnite predpostavke za prihodnje postavke obratnega kapitala

Nekateri predmeti brez vidnega voznika so običajno predvideni v konstantnih zneskih
Zagotovite, da so predpostavke razumne in v skladu s poslovanjem

Korak 4E - Predvidite prihodnje bilance obratnega kapitala

Korak 4F - Izračunajte spremembe v obratnem kapitalu

Prihodite do denarnih tokov na podlagi posameznih postavk
Prepričajte se, da so znaki natančni!

Korak 4G - Povežite predvideni obratni kapital s bilanco stanja

Korak 4H - Povežite obratna sredstva z izkazom denarnega toka

# 5 - Finančno modeliranje v Excelu - Razpored amortizacije

Po končanem načrtu obratnega kapitala je naslednji korak tega finančnega modeliranja projekt Capex Colgate in podatki o amortizaciji in sredstvih. Colgate 2013 - 10K, Stran 49

Amortizacija ni navedena kot ločena postavka; vključen pa je v stroške prodaje
V takih primerih si oglejte izkaze denarnih tokov, kjer boste našli odhodke za amortizacijo. Upoštevajte tudi, da so spodnje številke 1) amortizacija 2) amortizacija. Kakšna je torej amortizacijska številka?
Končno stanje za OZO = Začetno stanje + Kapital - Amortizacija - Prilagoditev prodaje sredstev (enačba BASE)

Korak 5A - Povežite podatke o neto prodaji v razporedu amortizacije

Nastavite vrstice
Referenčna neto prodaja
Vhodni pretekli kapitalski izdatki
Prispejte na Capex kot% neto prodaje

Korak 5B - Napoved postavk kapitalskih odhodkov

Za napovedovanje kapitalskih izdatkov obstajajo različni pristopi. Ena pogosta praksa je, da si ogledate sporočila za javnost, projekcije upravljanja, MD&A, da bi razumeli pogled podjetja na prihodnje kapitalske izdatke
Če je podjetje podalo smernice o prihodnjih kapitalskih izdatkih, potem lahko te številke vzamemo neposredno.
Če pa številke Capex niso neposredno dostopne, jih lahko grobo izračunamo z uporabo Capexa kot% prodaje (kot je navedeno spodaj)
Uporabite svojo presojo na podlagi industrijskega znanja in drugih razumnih gonilnikov.

Korak 5C - Referenca o preteklih informacijah

Za osebna zaščitna sredstva bomo uporabili končno stanje = začetno stanje + kapital - amortizacija - popravek prodaje sredstev (enačba BASE)
Usklajevanje preteklih PP&E je zapleteno zaradi preračunov, prodaje sredstev itd.
Zato je priporočljivo, da se pretekla osebna zaščitna oprema ne usklajuje, ker lahko povzroči nekaj zmede.

Amortizacijska politika Colgate

Ugotavljamo, da Colgate ni izrecno navedel podrobne razčlenitve sredstev. Namesto tega so vse premoženje združili v zemljišča, stavbe, stroje in drugo opremo
Poleg tega je na voljo tudi življenjska doba strojev in opreme. V tem primeru bomo morali nekaj ugibati, da bomo prišli do povprečne dobe koristnosti sredstev
Navodila za življenjsko dobo niso predvidena tudi za »Druga oprema«. Oceniti bomo morali življenjsko dobo druge opreme

Colgate 2013 - 10K, Stran 55

Spodaj je razčlenitev podrobnosti o nepremičninah, obratih in opremi za leti 2012 in 2013

Colgate 2013 - 10K, Stran 91

Korak 5D - Ocenite razčlenitev opredmetenih osnovnih sredstev (OZO)

Najprej poiščite uteži sredstev trenutne OZO (2013)
Predvidevali bomo, da se bodo te uteži sredstev iz OZE za leto 2013 še naprej nadaljevale
Te uteži sredstev uporabljamo za izračun razčlenitve ocenjenih kapitalskih izdatkov

Korak 5E - Ocenite amortizacijo sredstev

Upoštevajte, da amortizacije zemljišč ne izračunavamo, saj zemljišče ni sredstvo, ki se amortizira
Za oceno amortizacije iz izboljšav zgradbe najprej uporabimo spodnjo strukturo.
Amortizacija je tukaj razdeljena na dva dela - 1) amortizacija sredstev za izboljšave stavb, ki so že navedene v bilanci stanja, 2) amortizacija prihodnjih izboljšav stavb.
Za izračun amortizacije iz izboljšav zgradb, ki so navedene na sredstvu, uporabljamo preprosto metodo amortizacije naravnost.
Za izračun prihodnje amortizacije najprej prenesemo Capex s funkcijo TRANSPOSE v Excelu.
Amortizacijo izračunamo iz prispevkov sredstev za vsako leto.
Tudi amortizacija v prvem letu se deli z dvema, saj predpostavljamo, da velja sredina leta za uporabo sredstev.

Skupna amortizacija zgradb = amortizacija zgradb Izboljšave sredstev, ki so že navedene v bilanci stanja + amortizacija prihodnjih izboljšav zgradb Zgornji postopek za oceno amortizacije se uporablja za izračun amortizacije 1) Proizvodne opreme in strojev in 2) druge opreme kot prikazano spodaj.

Druge vrste opreme

Skupna amortizacija Colgate = amortizacija (izboljšave zgradb) + amortizacija (stroji in oprema) + amortizacija (dodatna oprema) Ko ugotovimo dejanske vrednosti amortizacije, lahko to damo v osnovno enačbo, kot je prikazano spodaj

S tem dobimo podatke o končni neto PP&E za vsako od teh let

Korak 5F - Povežite neto PP&E z bilanco stanja

# 6 - Amortizacijski načrt

Šesti korak tega finančnega modeliranja v Excelu je napoved amortizacije. Tu moramo upoštevati dve široki kategoriji - 1) dobro ime in 2) druge neopredmetena sredstva.

Korak 6A - Napovedovanje dobre volje

Colgate 2013 - 10K, Stran 61

Dobro ime pride v bilanco stanja, ko podjetje pridobi drugo družbo. Običajno je zapleteno projicirati dobro ime za prihodnja leta.
Vendar se za dobro ime vsako leto opravijo preizkusi oslabitve, ki jih družba opravi sama. Analitiki ne morejo izvajati takšnih testov in pripravljati ocen oslabitev.
Večina analitikov ne napoveduje dobre volje; to ohranjajo le kot stalno, kar bomo storili tudi v našem primeru.

Korak 6B - Napovedovanje drugih neopredmetenih sredstev

Kot je zapisano v Colgatejevem poročilu 10K, je večina končnega neopredmetenega življenja povezana s prevzemom podjetja Sanex
Tudi »dodatki k nematerialnim sredstvom« so zapleteni za načrtovanje
Poročilo podjetja Colgate 10K vsebuje podrobnosti o naslednjih petih letih stroškov amortizacije.
Te ocene bomo uporabili v našem finančnem modelu Colgate 2013 - 10K, Stran 61

Korak 6C - Končna neto neopredmetena sredstva so povezana z »drugimi neopredmetenimi sredstvi«.

Korak 6D - povežite amortizacijo z izkazi denarnih tokov

Korak 6E - Povežite Capex & Addition z neopredmetenimi sredstvi z izkazi denarnega toka

# 7 - Drugi dolgoročni načrt

Naslednji korak tega finančnega modeliranja je priprava drugega dolgoročnega terminskega načrta. Takrat se pripravimo na "ostanke", ki nimajo posebnih gonilnikov za napovedovanje. V primeru Colgate so bile druge dolgoročne postavke (ostanki) odloženi davki od dohodka (obveznosti in sredstva), druge naložbe in druge obveznosti.

Korak 7A - Sklic na pretekle podatke iz bilance stanja

Izračunajte tudi spremembe teh postavk.

Korak 7B - Napoved dolgoročnih sredstev in obveznosti

V primeru, da ne bo vidnih gonilnikov, naj bodo dolgoročne postavke nespremenjene v predvidenih letih
Povežite napovedane dolgoročne postavke z bilanco stanja, kot je prikazano spodaj

Korak 7C - Navedite druge dolgoročne postavke v bilanci stanja

Korak 7D - Povežite dolgoročne postavke z izkazom denarnega toka

Upoštevajte, da če bi dolgoročna sredstva in obveznosti ohranili nespremenjene, bi bila sprememba, ki teče v izkaz denarnega toka, enaka nič.

# 8 - Finančno modeliranje v Excelu - Izpolnjevanje izkaza poslovnega izida

Preden nadaljujemo s tem Excel-tem finančnim modeliranjem, se bomo vrnili in pregledali izkaz poslovnega izida
Popolnite preteklo osnovno tehtano povprečje delnic in razredčeno tehtano povprečno število delnic
Te številke so na voljo v poročilu podjetja Colgate 10K

Korak 8A - Navedite osnovne in popravljene delnice

Na tej stopnji predpostavimo, da bo prihodnje število primarnih in popravljenih delnic ostalo enako kot v letu 2013.

Korak 8B - Izračunajte osnovni in popravljeni dobiček na delnico.

S tem smo pripravljeni preiti na naš naslednji urnik, tj. Delniški načrt delnic.

# 9 - Finančno modeliranje - delniški načrt lastniškega kapitala

Naslednji korak tega finančnega modeliranja v programu Excel Training je preučitev lastniškega kapitala. Primarni cilj tega razporeda je načrtovanje postavk, povezanih z lastniškim kapitalom, kot so lastniški kapital, dividende, odkup delnic, prihodki od opcij itd . Poročilo podjetja Colgate 10K nam ponuja podrobnosti o navadnih delnicah in lastnih delnicah v preteklih letih, kot je prikazano spodaj . Colgate 2013 - 10K, Stran 68

Korak 9A - Skupni odkup: Vnesite zgodovinske številke

V preteklosti je Colgate odkupil delnice, kot lahko vidimo zgoraj.
Odkup delnic Colgate (v milijonih) napišite v list excel.
Povežite zgodovinsko razredčeni EPS iz izkaza poslovnega izida
Zgodovinski znesek odkupljenih je treba navesti v izkazih denarnega toka.

Oglejte si tudi pospešen odkup delnic.

Korak 9B - Ponovni nakup: izračunajte večkratnik PE (EPS večkratnik)

Izračunajte implicitno povprečno ceno, po kateri je Colgate v preteklosti že odkupoval delnice. Ta se izračuna kot odkupljeni znesek / število delnic.
Izračunajte večkratnik PE = implicitna cena delnice / EPS

Korak 9C - Ponovni nakup: Iskanje odkupljenega deleža družbe Colgate

Colgate še ni uradno sporočil, koliko delnic namerava odkupiti. Edini podatek, da imajo njihove 10.000 delnic poročila, je, da so odobrili odkup do 50 milijonov delnic. Colgate 2013 - 10K, Stran 35

Če želimo najti število odkupljenih delnic, moramo prevzeti znesek ponovnega odkupa delnic. Glede na pretekli znesek odkupa sem za vsa naslednja leta vzel to številko v višini 1500 milijonov USD.
Za iskanje števila odkupljenih delnic potrebujemo predvideno implicitno ceno delnice potencialnega odkupa.
Dejanska cena delnice = predpostavljeni PE multiplex EPS.
Na podlagi zgodovinskih trendov lahko predvidevamo večkratni odkup PE večkratnikov. Ugotavljamo, da je Colgate odkupil delnice v povprečnem razponu PE med 17x - 25x
Spodaj je posnetek Reutersa, ki nam pomaga potrditi obseg PE za Colgate

www.reuters.com

V našem primeru sem domneval, da bodo vsi prihodnji odkupi Colgateja večkratnik PE večkratnik 19x.
Z uporabo PE 19x lahko najdemo implicitno ceno = EPS x 19
Zdaj, ko smo našli implicitno ceno, lahko vidimo število odkupljenih delnic = znesek $, uporabljen za odkup / implicitno ceno.

Korak 9D - Možnosti zalog: Vnesite zgodovinske podatke

Iz povzetka navadnih delnic in lastniškega kapitala poznamo število opcij, ki jih lahko uporabimo vsako leto.

Poleg tega imamo tudi izbirne prihodke iz izkazov denarnega toka (približno)

S tem bi morali najti učinkovito udarno ceno.

Colgate 2013 - 10K, Stran 53

Upoštevajte tudi, da imajo delniške opcije pogodbene pogoje šestih let in teče več kot tri leta. Colgate 2013 - 10K, Stran 69

S temi podatki izpolnjujemo podatke o možnostih, kot je opisano spodaj. Ugotavljamo tudi, da je tehtana povprečna udarna cena delniških opcij za leto 2013 znašala 42 USD, število izkoristivih opcij pa 24,151 milijona Colgate 2013 - 10 000 , Stran 70

Korak 9E - Možnosti zalog: poiščite prihodek od možnosti.

Če te številke vključimo v spodnje podatke o možnostih, ugotavljamo, da izkupiček od možnosti znaša 1,014 milijarde USD.

Korak 9F - Možnosti zalog: napovedani podatki o zalogi na zalogi

Poleg delniških opcij obstajajo omejene delnice, ki jih zaposleni prejmejo s tehtanim povprečnim obdobjem 2,2 leta Colgate 2013 - 10K, Stran 81

Popolnjevanje teh podatkov v nabor podatkov Možnosti zaradi poenostavitve nismo predvideli izdaje možnosti (vem, da to ni prava predpostavka; vendar zaradi pomanjkanja podatkov ne obravnavam več nobenih težav z možnostmi. Pravkar smo jih nadaljevali kot nič, kot je označeno v zgornjem sivem območju. Poleg tega naj bi v prihodnje omejene zaloge znašale 2,0 milijona.

Oglejte si tudi metodo zakladnih delnic.

Korak 9G - Dividende: napoved dividend

Napoved ocenjenih dividend z uporabo razmerja izplačila dividende.
Fiksni odhodki za dividende na izplačilo na delnico
Iz poročil 10K izluščimo vse pretekle informacije o dividendah.
Z informacijami o izplačanih dividendah lahko ugotovimo razmerje izplačila dividende = Skupni izplačani dividendi / čisti dohodek.
Izračunal sem razmerje izplačil dividend Colgate, kot je prikazano spodaj - ugotavljamo, da je bilo razmerje izplačil dividend na splošno v območju od 50% do 60%. Predpostavimo, da bo delež izplačil dividend v prihodnjih letih znašal 55%.
Predvideni čisti dohodek lahko povežemo tudi z izkazom poslovnega izida.
Z uporabo tako napovedanega neto dohodka kot razmerja izplačil dividend lahko najdemo skupni znesek izplačanih dividend.

Korak 8H - Napoved lastniškega računa v celoti

Z napovedjo odkupa delnic, izkupička od opcij in izplačanih dividend smo pripravljeni izpolniti delniški načrt lastniškega kapitala. Povežite vse te, da poiščete končni lastniški kapital za vsako leto, kot je prikazano spodaj.

Korak 9I - Povežite končni lastniški kapital z bilanco stanja

Korak 9J - Povezava dividend, odkup delnic in možnosti se nadaljuje na CF

# 10 - Deli izjemno časovnico

Naslednji korak tega spletnega finančnega modeliranja v programu Excel je pogled na časovni razpored delnic. Povzetek časovnega razporeda delnic

Osnovne delnice - dejanske in povprečne
Po potrebi zajemite pretekle učinke opcij in kabrioletov
Razredčene delnice - povprečje
Referenčne delnice odkupljene in nove delnice od uveljavljenih opcij
Izračun napovedanih surovih odstotkov (dejansko)
Izračunajte povprečne osnovne in popravljene delnice
Navedite napovedane delnice na izkaz poslovnega izida (odpoklic izkaza poslovnega izida zgraditi!)
Vnos zgodovinskih delnic neporavnanih informacij
Opomba : Ta razpored je običajno vključen v delniški načrt

Korak 10A - Vnesite zgodovinske številke iz poročila 10K

Na izdane delnice (dejanska realizacija opcij) in odkupljene delnice se lahko sklicujete iz delniškega programa delničarjev
Vložek je tehtal povprečno število delnic in učinek delniških opcij v preteklih letih.

Korak 10B - Povežite izdaje in odkupe delnic s seznama delnic.

Osnovne delnice (konec) = Osnovne delnice (začetek) + Izdaje delnic - delnice odkupljene.

Korak 10C - Poiščite osnovne tehtane povprečne delnice,

najdemo v povprečju dve leti, kot je prikazano spodaj.
Dodajte tudi učinek opcij in omejenih delniških enot (na katerega se sklicuje delniški načrt delnic) in poiščite razredčene tehtane povprečne delnice.

Korak 10D - Povežite osnovne in popravljene ponderirane delnice z izkazom poslovnega izida

Zdaj, ko smo izračunali popravljene povprečne deleže, je čas, da iste posodobimo v izkazu poslovnega izida.
Povežite napovedane popravljene tehtane povprečne delnice v izkazu poslovnega izida, kot je prikazano spodaj

S tem dopolnimo časovni razpored delnic in čas, da se premaknemo na naš naslednji sklop izjav.

# 11 - Izpolnjevanje izkazov denarnega toka

Pomembno je, da v celoti izpolnimo izkaze denarnih tokov, preden preidemo na naslednji in končni načrt v tem finančnem modeliranju, tj. Na seznam dolgov. Do te faze je le nekaj nepopolnih stvari

Izkaz poslovnega izida - odhodki za obresti / prihodki so na tej stopnji nepopolni
Bilanca stanja - denarne in dolžniške postavke so na tej stopnji nepopolne

Korak 11A - Izračun denarnega toka za financiranje dejavnosti

Oglejte si tudi denarni tok iz financiranja

Korak 11B - Poiščite neto povečanje (zmanjšanje) denarnih sredstev in njihovih ustreznikov

Korak 11C = Izpolnite izpiske denarnih tokov

Poiščite denarna sredstva ob koncu leta na koncu leta.

Korak 11D - Povežite denarna sredstva in njihove ustreznike z bilanco stanja.

Zdaj smo pripravljeni poskrbeti za naš zadnji in končni urnik, tj. Za dolg in obresti

# 12- Finančno modeliranje v Excelu - Razpored dolgov in obresti

Naslednji korak tega spletnega finančnega modeliranja je izpolnitev seznama dolgov in obresti. Povzetek dolga in obresti - seznam

Ocena 12A - Nastavite razpored dolgov

Navedite denarni tok, ki je na voljo za financiranje
Navedite vse lastniške vire in uporabo denarja

Korak 12B - Izračunajte denarni tok od odplačila dolga

Sklic na začetno gotovinsko stanje v bilanci stanja
Odštejte minimalno stanje gotovine. Predvidevali smo, da bi Colgate rad zadržal najmanj 500 milijonov USD vsako leto.

Za zdaj preskočite izdajo / odplačilo dolgoročnega dolga, gotovino, ki je na voljo za revolving kreditno posojilo in odsek Revolver iz poročila Colgate's 10K; upoštevamo razpoložljive podrobnosti o Revolved Credit Facility Colgate 2013 - 10K, Stran 35

Dodatne informacije o dolgu vsebujejo tudi prevzeta dolgoročna odplačila dolga. Colgate 2013 - 10K, Stran 36

Korak 12C - Izračunajte končni dolgoročni dolg.

Uporabljamo zgoraj navedeni načrt odplačevanja dolgoročnega dolga in izračunavamo končno stanje dolgoročnih odplačil dolga.

Korak 12D - Povežite dolgoročna odplačila dolga.

Korak 12E - Izračunajte lastna posojila / predvajanja.

Z uporabo formule denarnega pometa, kot je prikazano spodaj, izračunajte najeta posojila / izplačila.

Korak 12F - Izračunajte odhodke za obresti iz dolgoročnega dolga

Izračunajte povprečno stanje za revolving kreditno posojilo in dolgoročni dolg
Na podlagi informacij iz poročila 10K utemeljite predpostavko za obrestno mero
Izračunaj skupne odhodke za obresti = povprečno stanje dolga x obrestna mera

Poiščite skupni odhodek za obresti = obresti (revolving kreditna linija) + obresti (dolgoročni dolg)

Korak 12G - črpanje dolga in revolverja do denarnih tokov

Korak 12H - Referenčni trenutni in dolgoročni bilanci stanja

Razmejite trenutni del dolgoročnega dolga in dolgoročnega dolga, kot je prikazano spodaj

Poveži revolving kreditni instrument, dolgoročni dolg in trenutni del dolgoročnega dolga z bilanco stanja

Korak 12I - Izračun dohodka od obresti z uporabo povprečnega stanja denarja

Korak 12J - Povežite odhodke za obresti in prihodek od obresti z izkazom poslovnega izida

Opravite preverjanje bilance stanja: Skupna sredstva = obveznosti + lastniški kapital

Korak 12K - revidirajte bilanco stanja

Če pride do kakršnih koli neskladnosti, moramo model pregledati in preveriti morebitne napake v povezavi

Priporočen tečaj finančnega modeliranja

Upam, da ste uživali v brezplačnem vodniku Excel za finančno modeliranje. Če se želite naučiti finančnega modeliranja v Excelu prek naših strokovnih video predavanj, si lahko ogledate tudi naše izobraževanje o investicijskem bančništvu. To je predvsem 99 tečajev snopa usposabljanj za investicijsko bančništvo. Ta tečaj se začne z osnovami in vas popelje na napredno raven službe za investicijsko bančništvo. Ta tečaj je razdeljen na pet delov -

1. del - Usposabljanje za investicijsko bančništvo - Temeljni tečaji
(26 tečajev)
2. del - Izobraževanje o naprednem modeliranju investicijskega bančništva
(20 tečajev)
3. del - Dodatki za investicijsko bančništvo
(13 tečajev)
4. del - Tečaji fundacije za investicijsko bančništvo
(23 tečajev)
5. del - Mehke veščine za investicijske bankirje
(17 tečajev)

Prenos finančnih modelov

Finančni model Alibaba
Okvir IPO Finančni model
Predloge za finančno modeliranje
Tečaj bančnega finančnega modeliranja

Kaj je naslednje?

Če ste se naučili kaj novega ali ste uživali v tem finančnem modeliranju na osnovi Excela, pustite komentar spodaj. Sporočite mi, kaj mislite. Najlepša hvala in bodite pozorni. Srečno učenje!