Finantsmodelleerimine Excelis (samm-sammult tasuta juhend + mall)

Finantsmodelleerimine Excelis on finantsmudeli loomise protsess, mis esindab tehingut, toimingut, ühinemist, omandamist, finantsteavet, et analüüsida, kuidas ühe muutuja muutus võib mõjutada lõplikku tootlust, et teha otsus ühe või mitme muutuja kohta. eelmainitud finantstehingud.

Mis on finantsmodelleerimine Excelis?

Finantsmodelleerimine Excelis on kogu veebis ja finantsmodelleerimise õppimise kohta on palju kirjutatud, kuid enamik finantsmodelleerimise koolitusi on täpselt samad. See ületab tavapärase pätte ja uurib praktilist finantsmudelit, mida kasutavad investeerimispankurid ja uurimisanalüütikud.

Selles tasuta finantsmodelleerimise Exceli juhendis võtan näite Colgate Palmolive'ist ja valmistan täielikult integreeritud finantsmudeli nullist.

See juhend sisaldab üle 6000 sõna ja selle täitmiseks kulus mul 3 nädalat. Salvestage see leht edaspidiseks kasutamiseks ja ärge unustage seda jagada :-)

KÕIGE TÄHTIS - juhiste järgimiseks laadige alla Colgate Financiali modelleerimise Exceli mall

Laadige alla Colgate'i finantsmudeli mall

Õppige Excelis finants-modelleerimist samm-sammult

Finantsmodelleerimine Exceli koolitusel - lugege mind kõigepealt

1. samm - laadige alla Colgate'i finantsmudeli mall. Kasutate seda malli juhendamisel

Laadige alla Colgate'i finantsmudel

2. samm - pange tähele, et saate kaks malli - 1) lahendamata Colgate Palmolive'i finantsmudel 2) lahendatud Colgate Palmolive'i finantsmudel

3. samm - töötate lahendamata Colgate Palmolive'i finantsmudeli malli kallal . Täielikult integreeritud finantsmudeli ettevalmistamiseks järgige samm-sammult juhiseid.

4. samm - head õppimist!

Sisukord

Olen selle finantsmudeli koostamiseks teinud teile hõlpsasti navigeeriva sisukorra

# 1 - Colgate'i finantsmudel - ajalooline
# 2 - Colgate Palmolive'i suhteanalüüs
# 3 - Kasumiaruande projitseerimine
# 4 - käibekapitali ajakava
# 5 - amortisatsiooni ajakava
# 6 - amortisatsioonigraafik
# 7 - muu pikaajaline ajakava
# 8 - Kasumiaruande täitmine
# 9 - aktsionäride omakapitali ajakava
# 10 - aktsiate tasumata ajakava
# 11 - Rahavoogude aruannete täitmine
# 12 - Võlgade ja intresside ajakava on soovitatav
Finantsmodelleerimise kursus
Tasuta finantsmudelid

Kui olete finantsmudelite loomisel alles uus, siis vaadake seda juhendit teemal Mis on finantsmudelid?

Kuidas Excelis finantsmudel üles ehitada?

Vaatame, kuidas finantsmudel nullist üles ehitatakse. See üksikasjalik finantsmudelite juhend annab teile samm-sammulise juhise finantsmudeli loomiseks. Selles finantsmodelleerimise juhendis on esmane lähenemisviis Modular. Modulaarne lähenemine tähendab sisuliselt seda, et erinevate moodulite / ajakavade abil koostame põhilisi aruandeid nagu kasumiaruanne, bilanss ja rahavood. Peamine fookus on iga avalduse samm-sammuline ettevalmistamine ja kõigi toetavate ajakavade ühendamine põhiavaldustega lõpetamisel. Ma saan aru, et see ei pruugi praegu veel selge olla, kuid saate aru, et see on edasiliikumisel väga lihtne. Allpool näete erinevaid finantsmodelleerimise ajakavasid / mooduleid -

Pange tähele järgmist -

Põhiaruanded on kasumiaruanne, bilanss ja rahavood.
Täiendavad graafikud on amortisatsioonigraafik, käibekapitali graafik, immateriaalse põhivara graafik, omakapitali graafik, muude pikaajaliste kirjete ajakava, võlakava jne
Täiendavad ajakavad on nende valmimisel seotud põhiaruannetega
Selles finantsmudeli juhendis ehitame Colgate Palmolive'i samm-sammult integreeritud finantsmudeli nullist.

# 1 - Finantsmudelid Excelis - ajalooliste projektsioon

Finantsmodelleerimise juhendi esimene samm on ajalooliste koostamine.

Samm 1A - laadige alla Colgate'i 10K-aruanded

„Finantsmudelid on koostatud suurepäraselt ja esimesed sammud algavad teadmisest, kuidas tööstusel on viimastel aastatel läinud. Mineviku mõistmine võib anda meile väärtuslikke teadmisi ettevõtte tuleviku kohta. Seetõttu on esimene samm ettevõtte kogu finantsaruande allalaadimine ja selle lisamine Exceli lehele. Colgate Palmolive'i jaoks saate alla laadida Colgate Palmolive'i aastaaruanded nende investorite suhetest. Kui klõpsate nupul „Aastaaruanne”, leiate allpool näidatud akna -

1. samm - looge ajalooliste finantsaruannete tööleht

Kui laadite alla 2013. aasta 10 000, märkite, et saadaval on ainult kahe aasta finantsaruannete andmed. Exceli finantsmudeli modelleerimise eesmärgil on soovitatav andmekogum siiski viimase viie aasta finantsaruanded. Laadige alla aastaaruande viimased 3 aastat ja lisage ajalooline.
Mitu korda tunduvad need ülesanded liiga igavad ja tüütud, kuna Exceli vormindamine ja soovitud vormingusse viimine võib võtta palju aega ja energiat.
Siiski ei tohiks unustada, et see on töö, mida peate iga ettevõtte jaoks tegema ainult ühe korra, ning ajalooliste andmete lisamine aitab analüütikul mõista trende ja finantsaruannet
Nii et ärge jätke seda vahele, laadige need alla ja täitke andmed (isegi kui tunnete, et see on eesli töö ;-))

Kui soovite selle sammu vahele jätta, saate Colgate Palmolive ajaloolise mudeli otse alla laadida siit.

Colgate'i kasumiaruanne koos ajaloolise asustusega

Colgate bilansi ajaloolised andmed

# 2 - suhteanalüüs

Exceli finantsmudelite teine samm on suhteanalüüsi tegemine.

Finantsmudelite õppimise võti Excelis on oskus teha fundamentaalanalüüse. Kui fundamentaalne analüüs või suhteanalüüs on teie jaoks midagi uut, soovitan teil Internetist natuke lugeda. Kavatsen teha ühes oma eelseisvatest postitustest põhjaliku suhteanalüüsi, kuid siin on kiire ülevaade Colgate Palmolive'i suhetest

TÄHTIS - Pange tähele, et olen värskendanud Colgate'i suhteanalüüsi eraldi postituses. Palun vaadake seda põhjalikku suhteanalüüsi.

Etapp 2A - Colgate'i vertikaalne analüüs

Kasumiaruandes on vertikaalne analüüs universaalne vahend ettevõtte suhtelise tootluse mõõtmiseks aastast aastasse kulude ja kasumlikkuse osas. See peaks alati olema osa mis tahes finantsanalüüsist. Siin arvutatakse protsendid netokäibe suhtes, mida peetakse 100% -ks. Seda kasumiaruande vertikaalse analüüsi pingutust nimetatakse sageli marginaalanalüüsiks, kuna see annab müügiga võrreldes erinevad marginaalid.

Vertikaalse analüüsi tulemused

Kasumimarginaal on suurenenud 240 baaspunkti võrra 56,2% -lt 2007. aastal 58,6% -le 2013. aastal. Selle põhjuseks on peamiselt vähenenud müügikulud
Ka ärikasum ehk EBIT on näidanud paranenud marginaale, kasvades seeläbi 19,7% -lt 2007. aastal 22,4% -le 2012. aastal (kasv 70 baaspunkti). Selle põhjuseks olid müügi üld- ja halduskulude vähenemine. Pange tähele, et 2013. aasta EBIT-marginaalid vähenesid muude kulude kasvu tõttu 20,4% -ni. Vaadake ka EBIT ja EBITDA erinevust
Puhaskasumi marginaal kasvas 12,6% -lt 2007. aastal 14,5% -le 2012. aastal. Kuid 2013. aasta kasumimarginaal vähenes 12,9% -ni peamiselt suurenenud muude kulude tõttu.
Kasum aktsia kohta on alates 2007. aastast kuni 2012. aastani pidevalt kasvanud. Kuid 2013. aasta eelarveaasta EPS-is oli väike langus
Pange tähele ka seda, et kulum ja amortisatsioon on kasumiaruandes eraldi välja toodud. See sisaldub müügikuludes

Etapp 2B - Colgate'i horisontaalne analüüs

Horisontaalanalüüs on tehnika, mida kasutatakse trendide hindamiseks ajas, arvutades protsentuaalse tõusu või vähenemise võrdlusaasta suhtes. See annab analüütilise seose eri kuupäevadel arvutatud kontode vahel, kasutades erineva ostujõuga valuutat. Tegelikult indekseerib see analüüs kontosid ja võrdleb nende arengut ajas. Nagu vertikaalse analüüsi metoodika puhul, ilmnevad ka probleemid, mida tuleb uurida ja täiendada teiste finantsanalüüsi tehnikatega. Keskendutakse selliste probleemide sümptomite otsimisele, mida saab diagnoosida lisatehnikate abil.

Vaatame Colgate'i horisontaalset analüüsi

Horisontaalse analüüsi tulemused

Näeme, et müügitulu on 2013. aastal kasvanud 2,0%.
Pange tähele ka müügikulude suundumust, näeme, et need ei ole kasvanud samas proportsioonis nagu müük.
Need tähelepanekud on äärmiselt käepärased, kui teeme finantsmudelit Excelis

2. samm - Colgate'i likviidsussuhted

Likviidsussuhted mõõdavad ettevõtte likviidsemate varade (kõige kergemini rahaks vahetatavate varade) suhet lühiajaliste kohustustega. Levinumad likviidsussuhted on järgmised: Praegune suhe Happetesti (või kiirvarade) suhe Sularaha suhtarv
Käibe suhtarvud nagu ostjate ja võlgade käive, varude käive ja võlgade käive

Likviidsussuhete olulisemad esiletõstmised

Praegune Colgate'i suhe on kõigi aastate jooksul suurem kui 1,0. See tähendab, et käibevarad on lühematest kohustustest suuremad ja võib-olla on Colgate'il piisavalt likviidsust
Colgate'i kiire suhe on vahemikus 0,6-0,7, see tähendab, et Colgates Cash ja Marketable väärtpaberid suudavad maksta kuni 70% lühiajalistest kohustustest. See näib olevat mõistlik olukord Colgate'i jaoks.
Sularaha sissenõudmise tsükkel on vähenenud 43 päevalt 2009. aastal 39 päevale 2013. aastal. Selle põhjuseks on peamiselt nõuete sissenõudmise perioodi vähenemine.

Vaadake ka seda üksikasjalikku artiklit sularaha konverteerimise tsükli kohta

Samm 2D - Colgate'i kasumlikkuse suhe

Kasumlikkuse suhtarvud on ettevõtte võime teenida kasumit müügi, varade ja omakapitali suhtes

Peamised tipphetked - Colgate'i tasuvuse suhe

Nagu näeme ülaltoodud tabelist, on Colgate'i ROE 100% -le lähemal, mis tähendab omakapitali omanikele suurt tootlust.

2. samm - Colgate'i riskianalüüs

Riskianalüüsi kaudu püüame hinnata, kas ettevõtted suudavad tasuda oma lühi- ja pikaajalised kohustused (võlg). Arvutame finantsvõimenduse suhtarvud, mis keskenduvad varade piisavusele või varadest genereerimisele. Vaadatud suhtarvud on

Võlakapitali suhe
Võlasuhe
Intresside katvuse suhe
Võlg omakapitali suhe on pidevalt tõusnud kõrgemale tasemele 2,23x. See tähendab suurenenud finantsvõimendust ja riske turul
Intressikattemäär on siiski väga kõrge, mis tähendab vähem intressi maksehäire riski.

# 3 - Finantsmudelid Excelis - projitseerige kasumiaruanne

Finantsmodelleerimise kolmas etapp on kasumiaruande prognoosimine, kus alustame müügi- või tulumudelite modelleerimisega.

Samm 3A - tulude prognoosid

Enamiku ettevõtete jaoks on tulu majandustulemuste põhiline tõukejõud. Hästi kavandatud ja loogiline tulumudel, mis kajastab täpselt tuluvoogude liiki ja suurust, on äärmiselt oluline. Tulude ajakava koostamiseks on sama palju võimalusi kui on ettevõtteid. Mõned levinumad tüübid on:

Müügikasv: iga perioodi müügikasvu eeldus määratleb muutuse võrreldes eelmise perioodiga. See on lihtne ja tavaliselt kasutatav meetod, kuid ei anna ülevaadet kasvu komponentidest ega dünaamikast.
Inflatsiooniline ja mahu / segusefekt: lihtsa kasvu eelduse asemel kasutatakse hinna inflatsioonitegurit ja mahutegurit. See kasulik lähenemisviis võimaldab modelleerida püsitooteid ja muutuvkulusid mitme tootega ettevõtetes ning võtab arvesse hinna ja mahu muutusi.
Ühiku maht, mahu muutus, keskmine hind ja hinna muutus: see meetod sobib ettevõtetele, kellel on lihtne tootevalik; see võimaldab analüüsida mitme põhimuutuja mõju.
Dollarituru suurus ja kasv: turuosa ja aktsia muutus - kasulik juhtudel, kui on saadaval teavet turu dünaamika kohta ja kui need eeldused on otsuse tegemisel tõenäoliselt olulised. Näiteks telekommunikatsioonitööstus
Osakuturu suurus ja kasv: see on üksikasjalikum kui eelmine juhtum ja on kasulik, kui turu hinnakujundus on peamine muutuja. (Ettevõttele, kellel on näiteks hinnaalandustrateegia või parim tõu parima hinnaga nišimängija) nt luksusautode turg
Maht, võimsuse rakendusaste ja keskmine hind: need eeldused võivad olla olulised ettevõtete jaoks, kus tootmisvõimsus on otsuse tegemisel oluline. (Näiteks täiendava võimsuse ostmisel või selleks, et teha kindlaks, kas laienemine nõuab uusi investeeringuid.)
Toote saadavus ja hinnakujundus
Tulu tingisid investeeringud kapitali, turundusse või teadus- ja arendustegevusse
Tulu põhineb paigaldatud baasil (osade, ühekordselt kasutatavate toodete, teenuste ja lisandmoodulite jätkuv müük). Näidete hulka kuuluvad klassikalised žiletiterad ja ettevõtted nagu arvutid, kus teenuse, tarkvara ja täienduste müük on oluline. Installitud baasi modelleerimine on võtmetähtsusega (baasi uued täiendused, baasi hõõrumine, jätkuvad tulud kliendi kohta jne).
Töötajapõhine: näiteks kutseteenindusettevõtete või müügipõhiste ettevõtete, näiteks maaklerite tulud. Modelleerimine peaks keskenduma personali netosummale, tulule töötaja kohta (tuginedes sageli arveldatavatele tundidele). Üksikasjalikumad mudelid hõlmavad staaži ja muid hinnakujundust mõjutavaid tegureid.
Kaupluse, rajatise või ruutmeetri põhine materjal: jaemüügiettevõtted modelleeritakse sageli kaupluste (vanad kauplused pluss uued kauplused igal aastal) ja kaupluse tulu põhjal.
Hõivatusteguril põhinev: seda lähenemisviisi kohaldatakse lennufirmade, hotellide, kinode ja muude madalate piirikuludega ettevõtete suhtes.

Colgate'i tulude prognoosimine

Vaatame nüüd Colgate 10K 2013 aruannet. Märgime, et kasumiaruandes, Colgate ei esitanud segmendiaruandluse aga kui tükk lisateavet, Colgate on andnud mõned üksikasjad segmentide Page 87 Allikas - Colgate 2013 - 10K, Page 86

Kuna meil pole segmentide kohta täiendavat teavet, prognoosime Colgate edasist müüki nende olemasolevate andmete põhjal. Prognooside saamiseks kasutame segmentide lõikes müügikasvu lähenemist. Palun vaadake allolevat pilti. Oleme arvutanud iga segmendi aastakasvu. Nüüd võime eeldada müügi kasvuprotsenti ajalooliste suundumuste põhjal ja prognoosida tulusid igas segmendis. Netokäive kokku on suukaudse, isikliku ja koduhoolduse ning lemmikloomatoitude segmendi summa.

Etapp 3B - Kulude prognoosid

Tulude protsent: lihtne, kuid ei anna ülevaadet ühestki finantsvõimendusest (mastaabisääst ega püsikulude koormus
Muud kulud kui amortisatsioon protsendina tuludest ja amortisatsioon eraldi ajakava järgi: see lähenemisviis on enamasti enamikul juhtudel vastuvõetav miinimum ja võimaldab ainult osaliselt analüüsida võimendust.
Tuludel või mahul põhinevad muutuvad kulud, ajaloolistel suundumustel põhinevad püsikulud ja amortisatsioon eraldi ajakavast: see lähenemisviis on minimaalne, mis on vajalik tulude tundlikkuse analüüsimiseks mitme tulustsenaariumi põhjal

Colgate'i kuluprognoosid

Kulude prognoosimisel on abiks varem tehtud vertikaalne analüüs. Vaatame vertikaalset analüüsi uuesti -

Kuna oleme juba müüki prognoosinud, on kõik muud kulud selle müügi mõned marginaalid.
Lähenemisviis on võtta suunised ajaloolistest kulude ja kulude marginaalidest ning prognoosida siis tulevast marginaali.
Näiteks viimase 5 aasta jooksul on müügikulu olnud vahemikus 41–42%. Selle põhjal võime vaadata marginaalide prognoosimist.
Samuti on müügi-, üld- ja halduskulud olnud ajalooliselt vahemikus 34–36%. Selle põhjal võime eeldada tulevaste müügi-, üld- ja halduskulude marginaali. Samamoodi võime jätkata veel ühte kulude kogumit.

Kasutades ülaltoodud veeriseid, võime tegelike väärtuste leida tagasiarvutuste abil.

Maksude eraldise arvutamiseks kasutame efektiivse maksumäära eeldust

Pange tähele ka seda, et me ei täida rida „Intressikulud (tulud)“, kuna hiljem vaatame kasumiaruande uuesti läbi.
Intressikulu ja intressitulu.
Samuti ei ole me arvutanud amortisatsiooni, mis on juba müügikuludes kajastatud.
Sellega täidetakse kasumiaruanne (vähemalt esialgu!)

# 4- Finantsmodelleerimine - käibekapitali ajakava

Nüüd, kui oleme kasumiaruande koostanud, on finantsmudelleerimise neljas samm käibekapitali ajakava vaatamine.

Allpool on toodud sammud, mida tuleb käibekapitali ajakava koostamisel järgida

Samm 4A - siduge müügitulu ja müügikulu

Etapp 4B - viitake käibekapitaliga seotud bilansiandmetele

Viidake varasematele andmetele bilansist
Arvutage netokäibekapital
Jõudke käibekapitali suurenemise / vähenemiseni
Pange tähele, et me ei ole käibekapitali arvestanud lühiajalisi võlgu ning raha ja raha ekvivalente. Võlgade ning sularaha ja raha ekvivalentidega tegeleme eraldi.

4. samm - arvutage käibe suhe

Arvutage ajaloolised suhtarvud ja protsendid
Kasutage lõpp- või keskmist saldot
Mõlemad on vastuvõetavad, kuna säilib pikk püsivus

Samm 4D - täitke eeldused tulevaste käibekapitalikirjete kohta

Teatud üksusi, millel pole ilmset juhti, eeldatakse tavaliselt konstantses koguses
Veenduge, et eeldused oleksid mõistlikud ja kooskõlas ettevõttega

4. samm - prognoosige tulevased käibekapitali saldod

Samm 4F - arvutage käibekapitali muutused

Saate rahavoogudeni üksikute reaartiklite põhjal
Veenduge, et märgid oleksid täpsed!

4. samm - siduge prognoositav käibekapital bilansiga

Samm 4H - siduge käibekapital rahavoogude aruandega

# 5 - Finantsmudelid Excelis - amortisatsiooni ajakava

Käibekapitali ajakava täitmisega on selle finantsmudelleerimise järgmine samm Colgate Capexi projekt ning amortisatsiooni ja varade näitajad. Colgate 2013 - 10K, lehekülg 49

Amortisatsiooni ja amortisatsiooni ei esitata eraldi reana, kuid see sisaldub müügihinnas
Sellistel juhtudel vaadake palun rahavoogude aruandeid, kust leiate amortisatsioonikulu. Pange tähele, et allpool toodud arvud on 1) amortisatsioon 2) amortisatsioon. Mis on amortisatsiooninumber?
PPE lõppsaldo = algsaldo + Capex - amortisatsioon - varade müügi korrigeerimine (BASE võrrand)

Samm 5A - linkige amortisatsioonigraafikus olevad netokäibe näitajad

Seadistage reaelemendid
Võrdlusmüük
Varasemate kapitalikulutuste sisestamine
Saabuge Capexi kui% müügitulust

5. samm - prognoosige kapitalikulude kirjed

Kapitalikulude prognoosimiseks on erinevaid lähenemisviise. Üks levinud lähenemisviis on vaadata pressiteateid, juhtimisprognoose, MD & A-d, et mõista ettevõtte vaadet tulevastele kapitalikulutustele
Kui ettevõte on andnud juhiseid tulevaste kapitalikulude kohta, siis võime neid numbreid otse võtta.
Kui aga Capexi numbrid pole otseselt saadaval, saame selle toorelt arvutada, kasutades Capexi kui% müügist (nagu allpool tehtud)
Kasutage oma otsust lähtuvalt valdkonna teadmistest ja muudest mõistlikest teguritest

5. samm - viide varasemale teabele

Me kasutame PPE jaoks lõppbilanssi = algsaldo + Capex - amortisatsioon - vara müügi korrigeerimine (BASE võrrand)
Varasemaid PP&E-sid on väga keeruline ümber korrigeerida, varade müük jne
Seetõttu on soovitatav mitte lepitada kokku varasemaid isikukaitsevahendeid, kuna see võib tekitada segadust.

Colgate amortisatsioonipoliitika

Märgime, et Colgate ei ole otseselt varade üksikasjalikku jaotust esitanud. Nad on koondanud kogu vara maa, ehituse, masinate ja muude seadmete hulka
Samuti on vahemikus ette nähtud masinate ja seadmete kasulik eluiga. Sellisel juhul peame varanduse keskmise kasuliku eluea saavutamiseks natuke ära arvama
Samuti ei ole juhised kasutusea kohta jaotises „Muu varustus”. Peame hindama muude seadmete kasulikku eluiga

Colgate 2013 - 10K, lk 55

Allpool on toodud 2012. ja 2013. aasta materiaalse põhivara üksikasjad

Colgate 2013 - 10K, lehekülg 91

5D etapp - hinnake materiaalse põhivara (PPE) lagunemist

Esmalt leidke praeguse isikukaitsevahendi varakaal (2013)
Eeldame, et need 2013. aasta isikukaitsevahendite varade kaalud jätkuvad ka edaspidi
Me kasutame seda vara kaalu hinnanguliste kapitalikulude jaotuse arvutamiseks

5. etapp - hinnake varade amortisatsiooni

Pange tähele, et me ei arvuta maa amortisatsiooni, kuna maa ei ole amortiseeritav vara
Ehitiste täiustustest tuleneva amortisatsiooni hindamiseks kasutame kõigepealt allolevat struktuuri.
Amortisatsioon jaguneb siin kaheks osaks - 1) amortisatsioon bilansilehel juba loetletud hoonete parenduste varalt 2) amortisatsioon tulevastest hoonete parendustest
Vara nimekirjas olevate hoone parenduste kulumi arvutamiseks kasutame amortisatsiooni lihtsat sirgjoonelist meetodit
Tulevase amortisatsiooni arvutamiseks võtame Capexi kõigepealt üle Excelis funktsiooni TRANSPOSE abil
Arvutame varade sissemakse amortisatsiooni igal aastal
Samuti jagatakse esimese aasta amortisatsioon 2-ga, kui eeldame vara kasutuselevõtu aasta keskpaiga kokkulepet

Ehitise parenduse amortisatsioon = bilansilehel juba loetletud amortisatsioon hoonete parenduste varalt + tulevaste amortisatsioon. Ehitiste täiustused Eespool toodud amortisatsiooni hindamise protsessi kasutatakse 1) tootmisseadmete ja masinate ning 2) muude seadmete näidatud allpool.

Muud tüüpi seadmed

Colgate'i amortisatsioon kokku = amortisatsioon (hoone parendused) + amortisatsioon (masinad ja seadmed) + amortisatsioon (muud seadmed). Kui oleme amortisatsiooni koguarvud välja selgitanud, võime selle panna BASE võrrandisse, nagu allpool näidatud

Sellega saame iga aasta kohta Ending Net PP&E näitajad

5. samm - linkige neto PP&E bilansiga

# 6 - amortisatsioonigraafik

Selle Exceli finantsmudelite kuues samm on amortisatsiooni prognoosimine. Siin on kaks suurt kategooriat - 1) firmaväärtus ja 2) muud immateriaalsed varad.

Samm 6A - firmaväärtuse prognoosimine

Colgate 2013 - 10K, lk 61

Firmaväärtus tuleb bilanssi siis, kui ettevõte omandab teise ettevõtte. Hea tahte prognoosimine järgmisteks aastateks on tavaliselt väga keeruline.
Firmaväärtuse suhtes tehakse igal aastal väärtuse languse teste, mille teeb ettevõte ise. Analüütikud ei saa selliseid katseid läbi viia ja kahjustuste hinnanguid koostada
Enamik analüütikuid ei projitseeri head tahet, vaid hoiavad seda konstantsena ja seda teeme ka meie puhul.

Samm 6B - muu immateriaalse vara prognoosimine

Nagu märgiti Colgate'i 10K aruandes, on suurem osa immateriaalsest piiratud elust seotud Sanexi omandamisega
“Lisandeid immateriaalsetele varadele” on samuti väga keeruline projitseerida
Colgate'i 10K aruanne annab meile üksikasjad järgmise 5 aasta amortisatsioonikulude kohta.
Kasutame neid hinnanguid oma finantsmudelis Colgate 2013 - 10K, lk 61

Samm 6C - Immateriaalse põhivara lõpetamine on seotud jaotisega „Muu immateriaalne vara”

Samm 6D - linkige amortisatsioon rahavoogude aruannetega

Samm 6E - ühendage Capex ja immateriaalse põhivara lisamine rahavoogude aruannetega

# 7 - muu pikaajaline ajakava

Selle finantsmudeli järgmine samm on muu pikaajalise ajakava koostamine. See on ajakava, mille me ette valmistame "vasakpoolseteks", millel pole prognoosimiseks konkreetseid draivereid. Colgate'i puhul olid muud pikaajalised kirjed (ülejäägid) edasilükkunud tulumaksud (kohustused ja varad), muud varad ja muud kohustused.

Samm 7A - Viidake bilansi ajaloolistele andmetele

Samuti arvutage nende üksuste muutused.

Samm 7B - prognoosige pikaajalisi varasid ja kohustusi

Hoidke pikaajalisi esemeid prognoositud aastate jooksul konstantsena, kui draivereid pole näha
Linkige prognoositavad pikaajalised kirjed bilansiga, nagu allpool näidatud

Samm 7C - muude pikaajaliste kirjete viimine bilansile

Samm 7D - siduge pikaajalised kirjed rahavoogude aruandega

Pange tähele, et kui oleme pikaajalisi varasid ja kohustusi konstantsena hoidnud, oleks rahavoogude aruandesse voolav muutus null.

# 8 - Finantsmudelid Excelis - kasumiaruande täitmine

Enne kui selles Exceli-põhises finantsmudelites edasi liikuda, läheme tegelikult tagasi ja vaatame kasumiaruannet uuesti
Täitke ajalooline kaalutud keskmine aktsia ja lahjendatud kaalutud keskmine aktsiate arv
Need arvud on saadaval Colgate'i 10K aruandes

8. samm - viide põhi- ja lahjendatud aktsiatele

Eeldame selles etapis, et põhi- ja lahjendatud aktsiate arv tulevikus jääb samaks, mis oli 2013. aastal.

Etapp 8B - arvutage aktsia põhikasum ja lahjendatud kasum

Sellega oleme valmis liikuma oma järgmise ajakava ehk aktsionäride aktsiagraafiku juurde.

# 9 - finantsmudelid - aktsionäride omakapitali ajakava

Selle Exceli koolituse finantsmudelite järgmine samm on vaadata aktsionäri omakapitali ajakava. Selle ajakava esmane eesmärk on kavandada omakapitaliga seotud objekte, nagu aktsiakapital, dividendid, aktsiate tagasiost, optsioonitulud jne. Colgate'i 10K aruanne annab meile üksikasjad eelmiste aastate aktsiate ja sularahakapitali tegevuste kohta, nagu allpool näidatud. Colgate 2013 - 10K, lehekülg 68

9. samm - aktsiate tagasiost: sisestage ajaloolised numbrid

Ajalooliselt on Colgate aktsiad tagasi ostnud, nagu näeme ülaltoodud ajakava.
Täitke Exceli lehel Colgate'i aktsiate tagasiost (miljonites).
Linkige varasem lahjendatud EPS kasumiaruandest
Tagasiostetud summa tuleks viidata rahavoogude aruannetele

Vaadake ka kiirendatud aktsiate tagasiostmist

Samm 9B - aktsia tagasiost: arvutage PE mitu (EPS mitu)

Arvutage kaudne keskmine hind, mille eest Colgate on ajalooliselt aktsiaid tagasi ostnud. See arvutatakse kui ostetud summa / aktsiate arv
Arvutage PE mitmekordne = eeldatav aktsia hind / EPS

Samm 9C - aktsiate tagasiost: ostetud Colgate'i aktsia leidmine

Colgate ei ole ühtegi ametlikku teadet selle kohta, kui palju aktsiaid nad kavatsevad tagasi osta. Ainus teave selle kohta, et nende 10 000 aruande aktsiat on, on andnud loa osta kuni 50 miljonit aktsiat. Colgate 2013 - 10K, lehekülg 35

Tagasiostetud aktsiate arvu leidmiseks peame eeldama aktsiate tagasiostu summat. Ajaloolise tagasiostusumma põhjal võtsin selle numbri kõigi tulevaste aastate jaoks 1500 miljoni dollarini.
Tagasiostetud aktsiate arvu leidmiseks vajame potentsiaalse tagasiostu eeldatavat kaudset hinda.
Kaudne aktsia hind = eeldatav PE multipleks EPS
Ajalooliste suundumuste põhjal võib eeldada, et tulevane tagasiostetav PE on mitmekordne. Märgime, et Colgate on tagasi ostnud aktsiaid keskmise PE vahemikus 17x - 25x
Allpool on Reutersi hetktõmmis, mis aitab meil kinnitada Colgate'i PE-vahemikku

www.reuters.com

Meie puhul olen eeldanud, et kõik tulevased Colgate'i tagasiostud on 19-kordse PE mitmekordse väärtusega.
Kasutades PE 19x, võime leida kaudse hinna = EPS x 19
Nüüd, kui oleme leidnud kaudse hinna, võime leida tagasiostetud aktsiate arvu = tagasiostuks kasutatud summa $ / kaudse hinna

Samm 9D - aktsiaoptsioonid: täitke ajaloolised andmed

Tavakapitali ja omakapitali kokkuvõttest teame igal aastal kasutatavate optsioonide arvu.

Lisaks on meil ka rahavoogude aruannetest saadud tulu (umbes)

Selle abil peaksime suutma leida tegeliku streigi hinna

Colgate 2013 - 10K, lk 53

Pange tähele ka seda, et aktsiaoptsioonide lepingutingimused on kuus aastat ja need kehtivad üle kolme aasta. Colgate 2013 - 10K, lehekülg 69

Nende andmetega täidame optsioonide andmed vastavalt allpool toodud tingimustele. Samuti märgime, et aktsiaoptsioonide kaalutud keskmine streigihind oli 2013. aastal 42 dollarit ja kasutatavate optsioonide arv 24,151 miljonit Colgate 2013 - 10K, lk 70

9. samm - aktsiaoptsioonid: leidke võimalus

Kui sisestame need numbrid allpool toodud optsiooniandmetesse, märkime, et optsiooni tulu on 1,014 miljardit dollarit

Samm 9F - aktsiaoptsioonid: prognoositud piiratud aktsiaandmete andmed

Lisaks aktsiaoptsioonidele on töötajatele antud piiratud aktsiad, kaalutud keskmise perioodiga 2,2 aastat. Colgate 2013 - 10K, lk 81

Andmete sisestamine optsioonide andmekogusse Lihtsuse huvides ei ole me optsioonide väljaandmist prognoosinud (ma tean, et see pole õige eeldus, kuid andmete puudumise tõttu ei võta ma edasisi optsioonide väljaandmisi edasi. Oleme just võtnud need on null, nagu ülaloleval hallil alal esile tõstetud. Lisaks prognoositakse, et tulevikus on piiratud aktsiaühikuid 2,0 miljonit.

Vaadake ka riigikassa aktsia meetodit

9G samm - dividendid: prognoosige dividende

Prognoosime hinnangulisi dividende, kasutades dividendide väljamakse määra
Fikseeritud dividendi väljamakse aktsia kohta
10K aruannetest võtame välja kogu varasema teabe dividendide kohta
Makstud dividendide teabe põhjal saame teada dividendide väljamaksete suhe = makstud dividendide summa / netotulu.
Olen arvutanud Colgate'i dividendide väljamakse määra, nagu allpool näha - Märgime, et dividendide väljamakse suhe on olnud üldiselt vahemikus 50% -60%. Oletame, et järgmistel aastatel on dividendide väljamakse suhe 55%.
Prognoositavat puhaskasumit saame siduda ka kasumiaruandest
Kasutades nii prognoositud puhaskasumit kui ka dividendide väljamakse suhet, võime leida makstud dividendide kogumahu

8. samm - prognoosige omakapitali kontot tervikuna

Aktsiate tagasiostu, optsioonitulu ja makstud dividendide prognoosiga oleme valmis täitma aktsionäride omakapitali ajakava. Seostage kõik need, et leida iga aasta lõpu omakapitali saldo, nagu allpool näidatud.

9. etapp - linkige aktsionäride omakapitali lõppemine bilansiga

Samm 9J - Dividendide, aktsiate tagasiostu ja optsioonide linkimine jätkub CF-iga

# 10 - aktsiate tasumata ajakava

Selle Exceli koolituse online-rahalise modelleerimise järgmine samm on vaadata aktsiate väljajätmise ajakava. Aktsiate tasumata ajakava kokkuvõte

Põhiaktsiad - tegelikud ja keskmised
Vajadusel jäädvustage optsioonide ja kabriolettide varasemad mõjud
Lahjendatud aktsiad - keskmine
Kasutatud optsioonidest ostetud võrdlusaktsiad ja uued aktsiad
Prognoositavate põhiaktsiate arvutamine (tegelik)
Arvutage keskmine põhi- ja lahjendatud aktsia
Prognoositud aktsiate võrdlus kasumiaruandega (tuletage meelde kasumiaruande koostamine!)
Sisestage ajalooline aktsiate tasumata teave
Märkus . See ajakava on tavaliselt integreeritud omakapitali graafikuga

Samm 10A - sisestage 10K aruande ajaloolised numbrid

Emiteeritud aktsiatele (optsioonide tegelik realiseerimine) ja tagasiostetud aktsiatele saab viidata aktsionäri omakapitali graafikus
Samuti kaaluti sisendis ajalooliste aastate keskmist aktsiate arvu ja aktsiaoptsioonide mõju.

Samm 10B - linkige aktsiate emiteerimine ja tagasiostmine aktsiaaktsiate nimekirjast.

Põhiaktsiad (lõppevad) = põhiaktsiad (algus) + aktsiaemissioonid - tagasiostetud aktsiad.

10. samm - leidke kaalutud keskmise põhiosa,

leiame keskmiselt kaks aastat, nagu allpool näidatud.
Lahjendatud kaalutud keskmise aktsia leidmiseks lisage ka optsioonide ja piiratud aktsiaüksuste mõju (viidatud aktsionäride omakapitali graafikule).

Samm 10D - linkige põhi- ja lahjendatud kaalutud aktsiad kasumiaruandega

Nüüd, kui oleme arvutanud lahjendatud kaalutud keskmise aktsiaid, on meil aeg seda kasumiaruandes värskendada.
Linkige prognoositud lahjendatud kaalutud keskmine käibel olevate aktsiate kasumiaruanne, nagu allpool näidatud

Sellega täidame aktsiate eemaldamise ajakava ja aja, et liikuda oma järgmise avalduste komplekti juurde.

# 11 - Rahavoogude aruannete täitmine

Meie jaoks on oluline rahavoogude aruanne täielikult täita, enne kui läheme üle järgmisele ja viimasele ajakavale selles finantsmudelites, st võla ajakava Kuni selle etapini on ainult paar asja, mis on puudulikud

Kasumiaruanne - intressikulu / -tulu on selles etapis puudulik
Bilanss - sularaha ja võla kirjed on selles etapis puudulikud

Samm 11A - arvutage finantseerimistegevuse rahavoog

Vaadake ka rahastamise rahavoogusid

Samm 11B - leidke raha ja raha ekvivalentide netokasv (vähenemine)

Samm 11C = täitke rahavoogude aruanded

Leidke aasta lõpu raha ja raha ekvivalendid aasta lõpus.

Samm 11D - linkige raha ja raha ekvivalendid bilansiga.

Nüüd oleme valmis hoolitsema oma viimase ja viimase graafiku ehk võla- ja intressigraafiku eest

# 12 - Finantsmudelid Excelis - võlgade ja intresside ajakava

Selle online-finantsmudelleerimise järgmine samm on võlgade ja intresside ajakava täitmine. Võlgade ja intresside kokkuvõte - ajakava

Samm 12A - seadke võlgade ajakava

Vaadake rahastamiseks saadaolevat rahavoogu
Viidake kõigile omakapitali allikatele ja sularaha kasutamisele

Samm 12B - arvutage võlgade tagasimaksmise rahavoog

Vaadake bilansist algse sularaha saldot
Võta maha minimaalne sularaha jääk. Oleme eeldanud, et Colgate soovib igal aastal hoida minimaalselt 500 miljonit dollarit.

Otse Long Term võlakirjade emiteerimise / Tagasimaksed Cash saadaval Korduvkasutusega laenulimiit Facility ja Revolver lõik nüüd Alates Colgate on 10K aruande, märgime olemasolevad üksikasjad keerles Credit Facility Colgate 2013 - 10K, Page 35

Võlgade kohta lisateabe esitatakse ka pikaajalised võlgade tagasimaksed. Colgate 2013 - 10K, lehekülg 36

Samm 12C - arvutage lõppev pikaajaline võlg

Kasutame ülaltoodud pikaajaliste võlgade tagasimaksegraafikut ja arvutame pikaajaliste võlgade tagasimaksete lõppsaldo

Samm 12D - siduge pikaajalised võla tagasimaksed.

Samm 12E - arvutage oma äranägemisel valitud laenud / tagasimaksed

Kasutades allpool näidatud sularaha pühkimise valemit, arvutage oma äranägemisel valitud laenud / tasud.

Samm 12F - arvutage intressikulu pikaajalise võla põhjal

Arvutage keskmise krediidiriski ja pikaajalise võla saldo
Tehke mõistlik eeldus intressimäära kohta, tuginedes 10K aruandes esitatud teabele
Arvutage kogu intressikulu = võlgade keskmine saldo x intressimäär

Leidke kogu intressikulu = intress (pöörlev krediidivõimalus) + intress (pikaajaline võlg)

Samm 12G - ühendage põhivõlg ja Revolveri sissemaksed rahavoogudega

Samm 12H - praeguse ja pikaajalise bilansi võrdlus

Märkige pikaajalise võla ja pikaajalise võla praegune osa, nagu allpool näidatud

Linkige bilansiga pöörlev krediidivõimalus, pikaajaline võlg ja pikaajalise võla praegune osa

Samm 12I - arvutage intressitulu keskmise sularahajäägi abil

Samm 12J - siduge intressikulud ja intressitulud kasumiaruandega

Tehke bilansikontroll: Varad kokku = kohustused + omakapital

Samm 12K - kontrollige bilanssi

Kui esineb mingeid lahknevusi, peame mudeli auditeerima ja kontrollima linkimisvigu

Soovitatav finantsmudeli kursus

Loodan, et teile meeldis tasuta finantsmudelite Exceli juhend. Kui soovite meie ekspertvideo loengute kaudu õppida Excelis finantsmodelleerimist, võite vaadata ka meie investeerimispanganduse koolitust. See on peamiselt 99 kursuse investeerimispanganduse koolituskomplekt. See kursus algab põhitõdedest ja viib teid investeerimispanganduse töö kõrgemale tasemele. See kursus on jagatud viieks osaks -

1. osa - Investeerimispanganduse koolitus - põhikursused
(26 kursust)
2. osa - Investeerimispanganduse modelleerimise täiendkoolitus
(20 kursust)
3. osa - Investeerimispanganduse lisandmoodulid
(13 kursust)
4. osa - Investeerimispanganduse Sihtasutuse kursused
(23 kursust)
5. osa - Pehmed oskused investeerimispankuritele
(17 kursust)

Finantsmudelite allalaadimine

Alibaba finantsmudel
Box IPO finantsmudel
Finantsmudelite mallid
Panganduse finantsmudeli kursus

Mis edasi saab?

Kui õppisite midagi uut või nautisite seda Exceli-põhist finantsmudelit, jätke palun allpool kommentaar. Anna mulle teada, mis sa arvad. Suur tänu ja hoolitsege. Head õppimist!