Finančné modelovanie v programe Excel (Krok za krokom bezplatný sprievodca + šablóna)

Obsah

Čo je finančné modelovanie v programe Excel?

Finančné modelovanie v programe Excel je proces zostavovania finančného modelu, ktorý predstavuje transakciu, operáciu, fúziu, akvizíciu a finančné informácie s cieľom analyzovať, ako môže zmena v jednej premennej ovplyvniť konečný výnos, aby bolo možné rozhodnúť o jednej alebo viacerých vyššie uvedené finančné transakcie.

Čo je finančné modelovanie v programe Excel?

Finančné modelovanie v programe Excel je všade na webe. O učení sa finančnému modelovaniu toho už bolo napísaného veľa; väčšina školení v oblasti finančného modelovania je však rovnaká. Toto ide nad rámec zvyčajného gýča a skúma sa praktické finančné modelovanie, ktoré používajú investiční bankári a analytici.

V tejto Sprievodcovi programom Excel pre bezplatné finančné modelovanie si vezmem príklad od spoločnosti Colgate Palmolive a od začiatku pripravím plne integrovaný finančný model.

Tento sprievodca má viac ako 6 000 slov a jeho vyplnenie mi trvalo tri týždne. Uložte si túto stránku pre ďalšie použitie a nezabudnite ju zdieľať :-)

NAJDôležitejšie - Stiahnite si šablónu Excel pre finančné modelovanie Colgate a postupujte podľa pokynov

Stiahnite si šablónu finančného modelu Colgate

Naučte sa podrobné finančné modelovanie v programe Excel

Finančné modelovanie v programe Excel - Prečítajte si najskôr

Krok 1 - Stiahnite si šablónu finančného modelu Colgate. Túto šablónu budete používať ako príručku

Stiahnite si finančný model spoločnosti Colgate

Zadajte e-mailovú adresu Pokračovaním v predchádzajúcom kroku vyjadrujete súhlas s našimi podmienkami používania a zásadami ochrany osobných údajov.

Krok 2 - Upozorňujeme, že dostanete dve šablóny - 1) Nevyriešený finančný model Colgate Palmolive 2) Vyriešený finančný model Colgate Palmolive

Krok 3 - Budete pracovať na šablóne nevyriešeného finančného modelu Colgate Palmolive . Podľa pokynov krok za krokom pripravte plne integrovaný finančný model.

Krok 4 - Šťastné učenie!

Obsah

Urobil som pre vás ľahký navigačný obsah, aby ste mohli robiť toto finančné modelovanie

Ako zostaviť finančný model?
# 1 - Finančný model spoločnosti Colgate - historický
# 2 - Pomerová analýza Colgate Palmolive
# 3 - Projektovanie výkazu ziskov a strát
# 4- Harmonogram pracovného kapitálu
# 5 - Časový plán odpisovania
# 6 - Časový plán amortizácie
# 7 - Iný dlhodobý plán
# 8 - Vyplnenie výkazu ziskov a strát
# 9 - Program vlastného imania akcionárov
# 10 - Zdieľa nevyrovnaný plán
# 11 - Vyplnenie výkazov peňažných tokov
# 12 - Dlh a úrokový rozvrh odporúčaný
Kurz finančného modelovania
Finančné modely zadarmo

Ak ste v oblasti finančného modelovania nováčikom, pozrite si túto príručku Čo je to finančné modelovanie?

Ako zostaviť finančný model v programe Excel?

Pozrime sa na to, ako je finančný model zostavený úplne od začiatku. Tento podrobný sprievodca finančným modelovaním vám poskytne krok za krokom sprievodcu vytvorením finančného modelu. Primárny prístup použitý v tomto sprievodcovi finančným modelovaním je modulárny. Modulárny systém v podstate znamená vytváranie základných výkazov, ako sú výkaz ziskov a strát, súvaha a peňažné toky, pomocou rôznych modulov / časových plánov. Kľúčovým zameraním je pripraviť každé vyhlásenie krok za krokom a po dokončení spojiť všetky podporné programy s hlavnými vyhláseniami. Chápem, že to teraz nemusí byť jasné; uvedomíte si však, že je to veľmi ľahké, keď sa posunieme vpred. Nižšie môžete vidieť rôzne harmonogramy / moduly finančného modelovania -

Vezmite prosím na vedomie toto -

Základnými výkazmi sú výkaz ziskov a strát, súvaha a peňažné toky.
Rôzne harmonogramy sú harmonogram odpisov, harmonogram pracovného kapitálu, nehmotný majetok, harmonogram vlastného imania akcionárov, harmonogram ďalších dlhodobých položiek, harmonogram dlhov atď.
Rôzne plány sú spojené s hlavnými výkazmi po ich dokončení.
V tomto sprievodcovi finančným modelovaním zostavíme úplne integrovaný ekonomický model Colgate Palmolive od nuly.

# 1 - Finančné modelovanie v programe Excel - Projektujte historické udalosti

Prvým krokom v Sprievodcovi finančným modelovaním je príprava historických materiálov.

Krok 1A - Stiahnite si 10K správy spoločnosti Colgate

„Finančné modely sú pripravené v exceli a prvé kroky začínajú poznávaním toho, ako sa tomuto odvetviu darilo v posledných rokoch. Pochopenie minulosti nám môže poskytnúť cenné postrehy týkajúce sa budúcnosti spoločnosti. Prvým krokom je preto stiahnutie všetkých finančných prostriedkov spoločnosti a ich vyplnenie do tabuľky programu Excel. Pre Colgate Palmolive si môžete stiahnuť výročné správy spoločnosti Colgate Palmolive z ich sekcie vzťahov s investormi. Po kliknutí na „Výročná správa“ nájdete okno, ako je uvedené nižšie -

Krok 1B - Vytvorenie tabuľky historických finančných výkazov

Ak si stiahnete 10 kB z roku 2013, všimnete si, že k dispozícii sú iba údaje za dva roky účtovnej závierky. Avšak na účely finančného modelovania v programe Excel je odporúčaným súborom údajov mať posledných 5 rokov účtovnej závierky. Stiahnite si posledné 3 roky výročnej správy a vyplňte historické údaje.
Mnohokrát sa tieto úlohy zdajú príliš nudné a zdĺhavé, pretože ich formátovanie a vytvorenie súboru Excel v požadovanom formáte môže vyžadovať veľa času a energie.
Nemali by ste však zabúdať, že toto je práca, ktorú musíte vykonať iba raz pre každú spoločnosť a tiež vyplnenie historických údajov pomáha analytikovi pochopiť trendy a finančné výkazy.
Takže to prosím nepreskočte, stiahnite si údaje a vyplňte údaje (aj keď máte pocit, že to je práca somára ;-))

Ak chcete tento krok preskočiť, môžete si priamo stiahnuť historický model Colgate Palmolive tu.

Colgate Výkaz ziskov a strát s historickým počtom obyvateľov

Historické údaje súvahy Colgate

# 2 - Pomerová analýza

Druhým krokom vo finančnom modelovaní v programe Excel je vykonanie pomerovej analýzy.

Kľúčom k učeniu sa finančnému modelovaniu v programe Excel je schopnosť vykonať základnú analýzu. Ak je pre vás elementárna analýza alebo Ratio Analysis niečo nové, odporúčam vám, aby ste si niečo prečítali na internete. V jednom zo svojich nadchádzajúcich príspevkov mám v úmysle uskutočniť hĺbkovú analýzu pomerov, tu je však rýchly prehľad pomerov Colgate Palmolive.

DÔLEŽITÉ - Upozorňujeme, že analýzu pomeru Colgate som aktualizoval v samostatnom príspevku. Prezrite si prosím túto komplexnú analýzu pomerov.

Krok 2A - Vertikálna analýza spoločnosti Colgate

Vo výkaze ziskov a strát je vertikálna analýza univerzálnym nástrojom na meranie relatívnej výkonnosti firmy z roku na rok z hľadiska nákladov a ziskovosti. Mal by byť vždy súčasťou akejkoľvek finančnej analýzy. Tu sa percentá počítajú vo vzťahu k čistému predaju, ktorý sa považuje za 100%. Toto vertikálne analytické úsilie vo výkaze ziskov a strát sa často nazýva analýza marží, pretože poskytuje rozdielne marže týkajúce sa predaja.

Výsledky vertikálnej analýzy

Zisková marža sa zvýšila o 240 bázických bodov z 56,2% v roku 2007 na 58,6% v roku 2013. Je to primárne kvôli zníženiu nákladov na predaj
Prevádzkový zisk alebo EBIT tiež vykázal zlepšenie marží, ktoré sa zvýšili z 19,7% v roku 2007 na 22,4% v roku 2012 (nárast o 70 bázických bodov). To bolo spôsobené znížením všeobecných a administratívnych nákladov na predaj. Upozorňujeme však, že marže EBIT sa v roku 2013 znížili na 20,4% v dôsledku zvýšenia položky „Ostatné náklady“. Skontrolujte tiež rozdiel medzi EBIT a EBITDA
Čistá zisková marža sa zvýšila z 12,6% v roku 2007 na 14,5% v roku 2012. Zisková marža v roku 2013 sa však znížila na 12,9%, predovšetkým v dôsledku zvýšenia „ostatných výdavkov“.
Zisk na akciu sa od FY2007 do FY2012 neustále zvyšoval. V EPS za rozpočtový rok 2013 však došlo k miernemu poklesu
Upozorňujeme tiež, že odpisy a amortizácia sú uvedené vo výkaze ziskov a strát osobitne. Je zahrnutá v nákladoch na predaj.

Krok 2B - Horizontálna analýza spoločnosti Colgate

Horizontálna analýza je technika používaná na hodnotenie trendov v čase výpočtom percentuálneho zvýšenia alebo zníženia vzhľadom na základný rok. Poskytuje analytické spojenie medzi účtami počítanými k rôznym dátumom s použitím meny s rôznymi nákupnými právomocami. Táto analýza v skutočnosti indexuje správy a porovnáva ich vývoj v priebehu času. Rovnako ako v prípade metodiky vertikálnej analýzy, aj tu sa objavia problémy, ktoré je potrebné preskúmať a doplniť o ďalšie techniky finančnej analýzy. Cieľom je hľadať príznaky problémov, ktoré možno diagnostikovať pomocou ďalších metód.

Pozrime sa na horizontálnu analýzu spoločnosti Colgate.

Výsledky horizontálnej analýzy

Vidíme, že čistý predaj sa v roku 2013 zvýšil o 2,0%.
Všimnite si tiež trend v nákladoch na predaj. Vidíme, že nenarástli v rovnakom pomere ako tržby.
Tieto pozorovania sú veľmi užitočné, keď robíme finančné modelovanie v programe Excel.

Krok 2C - Pomery likvidity spoločnosti Colgate

Ukazovatele likvidity merajú vzťah likvidnejších aktív podniku (tých, ktoré sú najľahšie zameniteľné za hotovosť) k súčasným záväzkom. Najbežnejším ukazovateľom likvidity je súčasný ukazovateľ, ukazovateľ hotovostných testov na kyslé testy (alebo rýchle aktíva).
Pomery obratu ako obrat účtov pohľadávok, obrat zásob a obrat záväzkov

Hlavné body ukazovateľov likvidity

Aktuálny pomer Colgate je významnejší ako 1,0 pre všetky roky. To znamená, že obežné aktíva sú významnejšie ako obežné pasíva a možno má Colgate dostatočnú likviditu.
Rýchly pomer Colgate je v rozmedzí 0,6-0,7; To znamená, že Colgates Cash a Obchodovateľné cenné papiere môžu platiť až 70% súčasných záväzkov. To vyzerá na dobrú situáciu pre Colgate.
Cyklus inkasa peňazí sa znížil zo 43 dní v roku 2009 na 39 dní v roku 2013. Je to predovšetkým z dôvodu skrátenia obdobia inkasa pohľadávok.

Prečítajte si tiež tento podrobný článok o cykle konverzie hotovosti.

Krok 2D - Pomery prevádzkovej ziskovosti spoločnosti Colgate

Ziskovosť predstavuje pomer schopnosti spoločnosti generovať príjmy v pomere k tržbám, aktívam a vlastnému imaniu

Hlavné body - Pomery ziskovosti spoločnosti Colgate

Ako vidíme z vyššie uvedenej tabuľky, Colgate má ROE bližšie k 100%, čo znamená vynikajúce výnosy pre majiteľov akcií.

Krok 2E - Analýza rizík spoločnosti Colgate

Prostredníctvom Analýzy rizika sa snažíme zistiť, či budú spoločnosti schopné platiť svoje krátkodobé a dlhodobé záväzky (dlhy). Vypočítavame ukazovatele finančnej páky, ktoré sa zameriavajú na dostatok aktív alebo generovanie z aktív. Sadzby, ktoré sa sledujú, sú

Pomer dlhu k vlastnému imaniu
Pomer dlhu
Pomer krytia úrokov
Pomer dlhu k vlastnému imaniu stabilne stúpal a dosiahol vyššiu úroveň 2,23x. To znamená zvýšenie finančnej páky a rizík na trhu
Pomer krytia úrokov je však veľmi vysoký, čo naznačuje menšie riziko nesplácania úrokov.

# 3 - Finančné modelovanie v programe Excel - Projektujte výkaz ziskov a strát

Tretím krokom vo finančnom modelovaní je prognóza výkazu ziskov a strát, kde začneme modelovaním položiek predaja alebo výnosov.

Krok 3A - Projekcie výnosov

Pre väčšinu spoločností sú výnosy základnou hnacou silou ekonomickej výkonnosti. Dobre navrhnutý a logický model výnosov, ktorý presne odráža typ a množstvo príjmových tokov, je nesmierne dôležitý. Existuje toľko spôsobov, ako vytvoriť rozvrh výnosov, ako aj firiem. Niektoré bežné typy zahŕňajú:

Rast predaja: Predpoklad rastu predaja v každom období definuje zmenu oproti predchádzajúcemu obdobiu. Jedná sa o jednoduchú a bežne používanú metódu, ktorá však neumožňuje nahliadnuť do zložiek alebo dynamiky rastu.
Inflačné a objemové / mixové efekty: Namiesto jednoduchého predpokladu rastu sa používa faktor cenovej inflácie a faktor objemu. Tento užitočný prístup umožňuje modelovanie fixných a variabilných nákladov vo viacproduktových spoločnostiach a zohľadňuje pohyby ceny a objemu.
Jednotkový objem, zmena objemu, priemerná cena a zmena ceny: Táto metóda je vhodná pre podniky, ktoré majú jednoduchý sortiment produktov; umožňuje analýzu vplyvu niekoľkých kľúčových premenných.
Dolárová veľkosť a rast trhu : Podiel na trhu a zmena podielu - užitočné pre prípady, keď sú k dispozícii informácie o dynamike trhu a kde sú tieto predpoklady pravdepodobne základom pre rozhodnutie. Napríklad telekomunikačný priemysel.
Veľkosť a rast jednotkového trhu: Toto je podrobnejšie ako v predchádzajúcom prípade a je užitočné, keď je cena na trhu rozhodujúcou premennou. (Napríklad pre spoločnosť so stratégiou znižovania cien alebo pre najlepšieho hráča s vynikajúcimi cenami pre tento druh plemena) napr. Trh s luxusnými automobilmi
Objemová kapacita, miera využitia kapacity a priemerná cena: Tieto predpoklady môžu byť dôležité pre podniky, kde je výrobná kapacita pre rozhodnutie nevyhnutná. (Napríklad pri nákupe ďalšej kapacity alebo pri zisťovaní, či si rozšírenie bude vyžadovať nové investície.)
Dostupnosť produktu a cena
Príjmy ťahali investície do kapitálu, marketingu alebo výskumu a vývoja
Príjmy založené na nainštalovanej základni (pokračujúci predaj náhradných dielov, spotrebného materiálu, servisu a doplnkov atď.). Príklady zahŕňajú klasické podniky so žiletkami a podniky ako počítače, kde je nevyhnutný predaj služieb, softvéru a upgradov. Kľúčové je modelovanie nainštalovaného základu (nové prírastky do podlahy, odieranie v zemi, pokračujúce výnosy na zákazníka atď.).
Zamestnanec: Napríklad výnosy firiem poskytujúcich profesionálne služby alebo firiem zameraných na predaj, ako sú makléri. Modelovanie by sa malo zamerať na čisté personálne obsadenie, výnosy na zamestnanca (často založené na fakturovateľných hodinách). Podrobnejšie modely budú zahŕňať odpracované roky a ďalšie faktory ovplyvňujúce tvorbu cien.
Založený na predajni, prevádzke alebo na námestí: Maloobchodné spoločnosti sú často modelované na základe obchodov (staré obchody plus nové obchody v každom roku) a výnosov na obchod.
Podľa obsadenosti: Tento prístup je použiteľný pre letecké spoločnosti, hotely, kiná a iné podniky s nízkymi marginálnymi nákladmi.

Projektovanie výnosov spoločnosti Colgate

Pozrime sa teraz na správu Colgate 10K 2013. Upozorňujeme, že vo výkaze ziskov a strát spoločnosť Colgate neposkytla segmentové informácie; ako ďalšia informácia však spoločnosť Colgate poskytla niektoré podrobnosti o segmentoch na stránke 87 Zdroj - Colgate 2013 - 10K, strana 86

Pretože nemáme ďalšie informácie o týchto funkciách, premietneme budúci predaj spoločnosti Colgate na základe týchto dostupných údajov. Na odvodenie prognóz použijeme prístup rastu predaja naprieč segmentmi. Pozrite si nasledujúci obrázok. Vypočítali sme medziročnú mieru rastu pre každý prvok. Teraz môžeme predpokladať percento rastu tržieb na základe historických trendov a projektovať výnosy pod každou časťou. Celkový čistý predaj predstavuje súčet súhrnu segmentu orálnej, osobnej a domácej starostlivosti a výživy domácich miláčikov.

Krok 3B - Projekcie nákladov

Percento výnosov: jednoduché, ale neponúka žiadny pohľad na akýkoľvek vplyv (úspora z rozsahu alebo zaťaženie fixnými nákladmi)
Iné náklady ako odpisy ako percento z výnosov a odpisy z iného harmonogramu: Tento prístup je vo väčšine prípadov skutočne prijateľný z minima a umožňuje iba čiastočnú analýzu prevádzkovej páky.
Variabilné náklady založené na výnosoch alebo objeme, fixné náklady založené na historických trendoch a odpisy zo samostatného harmonogramu: Tento prístup je minimom potrebným na citlivú analýzu ziskovosti na základe viacerých scenárov výnosov

Projekcie nákladov pre Colgate

Pri projektovaní nákladov bude užitočná skôr vykonaná vertikálna analýza. Poďme sa pozrieť na vertikálnu analýzu -

Pretože sme už predpovedali tržby, všetky ostatné náklady sú okrajmi tohto predaja.
Prístup spočíva v prevzatí pokynov z historických nákladových a nákladových marží a následnom predpovedaní budúcej marže.
Napríklad náklady na predaj sa za posledných päť rokov pohybovali v rozmedzí 41% - 42%. Na tomto základe sa môžeme pozrieť na prognózu marží.
Rovnako aj predajné, všeobecné a administratívne náklady sa historicky pohybovali v rozmedzí 34% - 36%. Na tomto základe môžeme predpokladať budúcu maržu výdavkov SG&A. Rovnako môžeme pokračovať v ďalšej skupine výdavkov.

Pomocou vyššie uvedených okrajov môžeme nájsť skutočné hodnoty spätnými výpočtami.

Na výpočet rezervy na dane používame predpoklad efektívnej daňovej sadzby.

Upozorňujeme tiež, že riadok „Úrokové náklady (výnosy)“ nedokončujeme, pretože neskôr budeme mať prehľad o výnosoch k dispozícii.
Úrokové náklady a úrokové výnosy.
Taktiež sme nevypočítali odpisy a amortizáciu, ktoré už boli zahrnuté v nákladoch na predaj.
Týmto sa dokončí výkaz ziskov a strát (aspoň zatiaľ!)

# 4 - Finančné modelovanie - plán pracovného kapitálu

Teraz, keď sme dokončili výkaz ziskov a strát, štvrtým krokom vo finančnom modelovaní je pohľad na plán pracovného kapitálu.

Ďalej sú uvedené kroky, ktoré je potrebné dodržať pri harmonograme prevádzkového kapitálu

Krok 4A - Prepojte čistý predaj a náklady na predaj

Krok 4B - Odkaz na súvahové údaje týkajúce sa prevádzkového kapitálu

Odkazujte na minulé údaje zo súvahy
Vypočítajte čistý pracovný kapitál
Príďte k zvýšeniu / zníženiu prevádzkového kapitálu
Upozorňujeme, že do pracovného kapitálu sme nezahrnuli krátkodobý dlh a hotovosť a peňažné ekvivalenty. Dlhom a hotovosťou a peňažným ekvivalentom sa budeme venovať osobitne.

Krok 4C - Vypočítajte ukazovatele obratu

Vypočítajte historické pomery a percentá
Použite koncový alebo priemerný zostatok
Oba sú prijateľné, pokiaľ je zachovaná dlhá konzistencia

Krok 4D - Vyplňte predpoklady pre budúce položky prevádzkového kapitálu

Určité položky bez výrazného ovládača sa zvyčajne predpokladajú v stálych množstvách
Zaistite, aby boli predpoklady rozumné a v súlade s podnikaním

Krok 4E - Prerozdelenie budúcich zostatkov prevádzkového kapitálu

Krok 4F - Vypočítajte zmeny v pracovnom kapitále

Prichádzajte k peňažným tokom na základe jednotlivých riadkových položiek
Zaistite, aby boli značky presné!

Krok 4G - Prepojte prognózovaný pracovný kapitál s súvahou

Krok 4H - Prepojenie pracovného kapitálu s výkazom peňažných tokov

# 5 - Finančné modelovanie v programe Excel - harmonogram odpisov

Po dokončení harmonogramu pracovného kapitálu je ďalším krokom v tomto finančnom modelovaní projekt Capex spoločnosti Colgate a projektovanie údajov o odpisoch a aktívach. Colgate 2013 - 10 tis., Strana 49

Odpisy a amortizácia sa neposkytujú ako samostatná riadková položka; je však zahrnutá v nákladoch na predaj
V takom prípade si pozrite prehľady o hotovostných tokoch, kde nájdete náklady na odpisy a amortizáciu. Upozorňujeme tiež, že nižšie uvedené údaje sú 1) Odpisy 2) amortizácia. Aké je teda číslo odpisu?
Konečný zostatok pre OOP = Počiatočný zostatok + Capex - Odpisy - Úprava o predaj majetku (BASE rovnica)

Krok 5A - Prepojte údaje o čistom predaji v pláne odpisov

Nastavte riadkové položky
Referenčný čistý predaj
Zadajte minulé kapitálové výdavky
Príďte na Capex ako% z čistého predaja

Krok 5B - Prognóza položiek kapitálových výdavkov

Na predikciu kapitálových výdavkov existujú rôzne prístupy. Bežnou praxou je pozrieť sa na tlačové správy, projekcie riadenia, MD&A, aby ste pochopili pohľad spoločnosti na budúce kapitálové výdavky
Ak spoločnosť poskytla usmernenie týkajúce sa budúcich kapitálových výdavkov, potom môžeme tieto čísla vziať priamo.
Ak však čísla Capex nie sú priamo k dispozícii, môžeme to surovo vypočítať pomocou Capexu ako% z predaja (ako je uvedené nižšie)
Použite svoj úsudok založený na znalostiach odvetvia a iných rozumných faktoroch.

Krok 5C - Odkaz na minulé informácie

Použijeme Konečný zostatok pre PPE = Počiatočný zostatok + Capex - Odpisy - Úprava predaja majetku (BASE rovnica)
Je zložité zosúladiť minulé PP&E z dôvodu prepracovania, predaja majetku atď.
Preto sa odporúča nezmieriť minulé OOP, pretože by to mohlo viesť k určitému zmätku.

Politika odpisovania spoločnosti Colgate

Upozorňujeme, že spoločnosť Colgate výslovne neposkytla podrobné rozdelenie aktív. Namiesto toho zhromaždili všetok majetok do pozemkov, budov, strojov a iného vybavenia
V rámci rozsahu je tiež poskytnutá doba použiteľnosti strojov a zariadení. V takom prípade budeme musieť urobiť nejaké dohady, aby sme dosiahli priemernú dobu použiteľnosti majetku
Pokyny pre dobu použiteľnosti tiež nie sú uvedené v prípade „Ostatné zariadenia“. Budeme musieť odhadnúť životnosť ostatných zariadení

Colgate 2013 - 10 tis., Strana 55

Ďalej uvádzame rozpad podrobností nehnuteľností, strojov a zariadení z rokov 2012 a 2013

Colgate 2013 - 10 tis., Strana 91

Krok 5D - Odhadnite rozpad stavebných strojov a zariadení (OOP)

Najskôr vyhľadajte váhy aktív súčasných OOP (2013)
Budeme predpokladať, že tieto váhy aktív OOP 2013 budú pokračovať ďalej
Tieto váhy aktív používame na výpočet rozdelenia odhadovaných kapitálových výdavkov

Krok 5E - Odhadnite odpisy majetku

Upozorňujeme, že odpisy pôdy nevypočítavame, pretože pôda nie je odpisovateľným majetkom
Na odhadovanie odpisov z vylepšenia budovy najskôr využijeme nižšie uvedenú štruktúru.
Odpisy sa tu delia na dve časti - 1) odpisy z majetku zlepšenia budovy, ktoré sú už uvedené v súvahe, 2) odpisy z budúceho vylepšenia budovy.
Na výpočet odpisov zo zlepšenia budovy, ktoré sú uvedené na majetku, používame jednoduchú lineárnu metódu odpisovania.
Pre výpočet budúcich odpisov najskôr transponujeme Capex pomocou funkcie TRANSPOSE v programe Excel.
Z každého roku počítame odpisy z majetkových príspevkov.
Odpisy v prvom roku sa tiež vydelia dvoma, keďže predpokladáme strednodobú konvenciu nasadenia majetku.

Celkové odpisy vylepšenia budovy = odpisy z vylepšenia budovy, ktoré sú už uvedené v súvahe, + odpisy z budúcich vylepšení budovy Vyššie uvedený postup na odhadovanie odpisov sa používa na výpočet odpisov 1) Výrobných zariadení a strojov a 2) iných zariadení zobrazené nižšie.

Ostatné typy vybavenia

Celkové odpisy spoločnosti Colgate = Odpisy (vylepšenia budov) + Odpisy (stroje a zariadenia) + Odpisy (ďalšie vybavenie) Keď sme zistili skutočné hodnoty odpisov, môžeme ich vložiť do ZÁKLADNEJ rovnice, ako je uvedené nižšie

Vďaka tomu získame údaje o koncovom čistom PP&E pre každý z rokov

Krok 5F - Prepojenie čistých PP&E k súvahe

# 6 - Časový plán amortizácie

Šiestym krokom v tomto finančnom modelovaní v programe Excel je predpoveď amortizácie. Tu musíme vziať do úvahy dve široké kategórie - 1) Goodwill a 2) Ostatné nehmotné statky.

Krok 6A - Prognóza dobrej vôle

Colgate 2013 - 10 tis., Strana 61

Goodwill prichádza do súvahy, keď spoločnosť získa inú spoločnosť. Projektovanie dobrej vôle pre budúce roky je zvyčajne komplikované.
Goodwill však každoročne podlieha testom na zníženie hodnoty, ktoré vykonáva samotná spoločnosť. Analytici nie sú v postavení, aby takéto testy uskutočňovali a pripravovali odhady zníženia hodnoty.
Väčšina analytikov nepremietne dobrú vôľu; len to udržiavajú ako konštantné, čo urobíme aj v našom prípade.

Krok 6B - Prognóza ďalších nehmotných aktív

Ako je uvedené v správe spoločnosti Colgate 10K, väčšina nehmotného majetku s konečnou platnosťou súvisí s akvizíciou spoločnosti Sanex
Projekt „Prírastky nehmotného majetku“ sa tiež komplikuje
Správa Colgate 10K nám poskytuje podrobnosti o nasledujúcich piatich rokoch odpisovania.
Tieto odhady použijeme v našom finančnom modeli Colgate 2013 - 10K, strana 61

Krok 6C - Konečný čistý nehmotný majetok je prepojený s „Ostatným nehmotným majetkom“.

Krok 6D - prepojenie odpisov a amortizácie s výkazmi peňažných tokov

Krok 6E - Prepojte Capex a dodatok k nehmotnému majetku s výkazmi peňažných tokov

# 7 - Iný dlhodobý plán

Ďalším krokom v tomto finančnom modelovaní je príprava iného dlhodobého harmonogramu. To je prípad, keď sa pripravujeme na „zvyšky“, ktoré nemajú konkrétne ovládače prognózovania. V prípade spoločnosti Colgate boli ďalšími dlhodobými položkami (zvyšky) odložená daň z príjmu (pasíva a aktíva), ostatné investície a ďalšie pasíva.

Krok 7A - Odkaz na historické údaje zo súvahy

Vypočítajte tiež zmeny v týchto položkách.

Krok 7B - Predpoveď dlhodobého majetku a záväzkov

Ak nie sú viditeľné ovládače, udržujte dlhodobé položky konštantné po celý plánovaný rok
Prepojte predpokladané dlhodobé položky so súvahou, ako je uvedené nižšie

Krok 7C - Referencia ďalších dlhodobých položiek k súvahe

Krok 7D - Prepojte dlhodobé položky s Výkazom peňažných tokov

Upozorňujeme, že ak by sme udržali konštantné dlhodobé aktíva a pasíva, potom by zmena plynúca do výkazu peňažných tokov bola nulová.

# 8 - Finančné modelovanie v programe Excel - Dokončenie výkazu ziskov a strát

Predtým, ako sa v tomto finančnom modelovaní založenom na programe Excel posunieme ďalej, vrátime sa späť a preskúmame výkaz ziskov a strát
Vyplňte historický základný vážený priemer akcií a zriedený vážený priemer počtu akcií
Tieto údaje sú k dispozícii v správe spoločnosti Colgate 10K

Krok 8A - Odkaz na základné a zriedené akcie

V tejto fáze predpokladajme, že budúci počet primárnych a zriedených akcií zostane rovnaký ako v roku 2013.

Krok 8B - Výpočet základného a zriedeného zisku na akciu.

Týmto sme pripravení prejsť k nášmu ďalšiemu harmonogramu, tj. Plánu vlastného imania akcionárov.

# 9 - Finančné modelovanie - harmonogram vlastného imania

Ďalším krokom v tomto finančnom modelovaní v programe Excel je preskúmanie plánu vlastného imania akcionára. Primárnym cieľom tohto harmonogramu je premietnuť položky súvisiace s akciami, ako je kapitál akcionárov, dividendy, spätné odkúpenie akcií, výnosy z opcií atď . Správa Colgate 10K nám poskytuje podrobnosti o bežných akciách a aktivitách v oblasti akcií v minulých rokoch, ako je uvedené nižšie. . Colgate 2013 - 10 tis., Strana 68

Krok 9A - Odkup podielu: Vyplňte historické čísla

Colgate historicky odkúpila akcie, ako vidíme vyššie.
Vyplňte výkup akcií Colgate (milióny) v hárku programu Excel.
Prepojte historicky zriedený EPS s výkazom ziskov a strát
Na historickú sumu spätne odkúpeného by sa malo odkazovať z výkazov peňažných tokov.

Prezrite si tiež akcelerované spätné odkúpenie akcií.

Krok 9B - Odkup podielu: Vypočítajte násobok PE (násobok EPS)

Vypočítajte implikovanú priemernú cenu, za ktorú Colgate historicky vykonala spätný nákup akcií. Toto sa počíta ako odkúpená suma / počet akcií.
Vypočítajte násobok PE = implikovaná cena akcie / EPS

Krok 9C - Spätné odkúpenie akcií: Nájdenie spätne odkúpenej akcie spoločnosti Colgate

Spoločnosť Colgate nijako oficiálne neoznámila, koľko akcií má v úmysle odkúpiť. Jediné informácie, ktoré oznamujú ich akcie 10K, sú, že povolili odkúpenie až 50 miliónov akcií. Colgate 2013 - 10 tis., Strana 35

Ak chcete zistiť počet spätne odkúpených akcií, musíme predpokladať sumu spätného nákupu akcií. Na základe historickej sumy spätného odkúpenia som vzal toto číslo ako 1 500 miliónov dolárov pre všetky budúce roky.
Na zistenie počtu odkúpených akcií potrebujeme predpokladanú implikovanú cenu akcií potenciálneho spätného odkúpenia.
Skutočná cena akcie = predpokladaný PE multiplex EPS.
Budúce spätné odkúpenie PE možno predpokladať na základe historických trendov. Poznamenávame, že Colgate odkúpila akcie v priemere v rozmedzí PE 17x - 25x
Nižšie je uvedený obrázok agentúry Reuters, ktorý nám pomáha overiť rozsah PE pre Colgate

www.reuters.com

V našom prípade som predpokladal, že všetky budúce spätné odkúpenia spoločnosti Colgate budú násobkom PE 19x.
Pomocou PE 19x nájdeme implikovanú cenu = EPS x 19
Teraz, keď sme našli implicitnú cenu, vidíme počet odkúpených akcií = suma $ použitá na spätný nákup / implikovaná cena.

Krok 9D - Možnosti skladu: Vyplňte historické údaje

Zo súhrnu bežných akcií a vlastného imania akcionárov poznáme počet opcií uplatnených každý rok.

Okrem toho máme k dispozícii aj výnosy z opcií z výkazov peňažných tokov (približne)

Vďaka tomu by sme mali byť schopní nájsť efektívnu štrajkovú cenu.

Colgate 2013 - 10 tis., Strana 53

Upozorňujeme tiež, že opcie na akcie majú zmluvné podmienky šesť rokov a trvajú tri roky. Colgate 2013 - 10 tis., Strana 69

Týmito údajmi vypĺňame údaje Možnosti, ako je uvedené nižšie. Tiež si všimneme, že vážená priemerná realizačná cena akciových opcií pre rok 2013 bola 42 dolárov a počet uplatniteľných opcií bol 24,151 milióna Colgate 2013 - 10 tis.

Krok 9E - Opcie na sklad: Nájdite výťažok z opcie.

Ak uvedieme tieto čísla v našich údajoch o opciách nižšie, všimneme si, že výťažok z opcie je 1 014 miliárd dolárov.

Krok 9F - Možnosti skladu: Prognóza Obmedzené údaje skladovej jednotky

Okrem opcií na sklad sú k dispozícii aj jednotky s obmedzeným skladom, ktoré sa dajú zamestnancom s váženým priemerom za 2,2 roka. Colgate 2013 - 10K, strana 81

Vyplnenie týchto údajov v množine údajov Možnosti Pre zjednodušenie sme nepredpokladali vydanie opcií (viem, že to nie je správny predpoklad); z dôvodu nedostatku údajov však neposúvam žiadne ďalšie problémy s opciami. Práve sme posunuli tieto možnosti ako nula, ako je zvýraznené v šedej oblasti vyššie. Ďalej sa predpokladá, že obmedzené skladové jednotky budú do budúcnosti 2,0 milióna.

Prezrite si tiež metódu Treasury Stock.

Krok 9G - Dividendy: Predpovedajte dividendy

Predpovedajte odhadované dividendy pomocou výplatného pomeru dividend.
Fixná dividenda presahuje výplatu na akciu
Z 10K správ extrahujeme všetky minulé informácie o dividendách.
S informáciami o vyplatených dividendách môžeme zistiť pomer výplaty dividend = celkové vyplatené dividendy / čistý príjem.
Vypočítal som pomer výplaty dividend spoločnosti Colgate, ako je vidieť nižšie - Upozorňujeme, že pomer výplaty dividend bol zhruba v rozmedzí 50% - 60%. Predpokladajme, že výplatný pomer dividend bude v budúcich rokoch 55%.
Môžeme tiež prepojiť predpokladaný čistý príjem z výkazu ziskov a strát.
Pomocou premietaného čistého príjmu a výplatného pomeru dividend nájdeme celkové vyplatené dividendy.

Krok 8H - Prognóza akciového účtu v celom rozsahu

S predpoveďou spätného odkúpenia akcií, výnosov z opcií a vyplatených dividend sme pripravení dokončiť program vlastného imania akcionárov. Prepojte všetky tieto nástroje a nájdite konečný zostatok vlastného imania pre každý rok, ako je uvedené nižšie.

Krok 9I - Prepojenie ukončenia základného imania akcionára s súvahou

Krok 9J - Prepojte dividendy, výkup akcií a opcie pokračuje do CF

# 10 - Zdieľa nevyrovnaný plán

Ďalším krokom v tomto online finančnom modelovaní v rámci školenia v programe Excel je preskúmanie harmonogramu zdieľania akcií. Zhrnutie nesplateného harmonogramu akcií

Základné akcie - skutočné a priemerné
Podľa potreby zachytávajte minulé účinky možností a kabrioletov
Zriedené akcie - priemer
Referenčné akcie odkúpené a nové akcie z uplatnených opcií
Vypočítajte predpokladané hrubé percentá (skutočné)
Vypočítajte priemerné základné a zriedené akcie
Referenčné projektované akcie k výkazu ziskov a strát (stiahnite si zostavený výkaz ziskov a strát!)
Zadajte nevyriešené informácie o historických podieloch
Poznámka : Tento rozvrh je bežne integrovaný do plánu vlastného imania

Krok 10A - Zadajte historické čísla z prehľadu 10 kB

Na vydané akcie (skutočná realizácia opcií) a spätne odkúpené akcie sa dá odkazovať z harmonogramu akcií akcionára
Vstup vážil priemerný počet akcií a vplyv akciových opcií na historické roky.

Krok 10B - Prepojte emisie akcií a spätné odkúpenia akcií z harmonogramu akcií.

Základné akcie (končiace) = Základné akcie (od začiatku) + Emisie akcií - akcie odkúpené späť.

Krok 10C - Nájdite základné vážené priemerné podiely,

nájdeme v priemere dva roky, ako je uvedené nižšie.
Pridajte tiež efekt opcií a obmedzených akciových jednotiek (na ktoré sa odkazuje v pláne vlastného imania akcionára), aby ste našli zriedený vážený priemer akcií.

Krok 10D - Prepojenie základných a zriedených vážených akcií s výkazom ziskov a strát

Teraz, keď sme vypočítali zriedený vážený priemer akcií, je čas, aby sme to isté aktualizovali vo výkaze ziskov a strát.
Prepojte predpokladané zriedené vážené priemerné akcie v obehu s výkazom ziskov a strát, ako je uvedené nižšie

Týmto dokončujeme rozvrh výmeny akcií a čas prejsť na našu ďalšiu skupinu vyhlásení.

# 11 - Vyplnenie výkazov peňažných tokov

Je dôležité, aby sme kompletne vyplnili výkazy peňažných tokov predtým, ako prejdeme k nášmu ďalšiemu a poslednému harmonogramu v tomto finančnom modelovaní, tj. Plánu dlhu. Do tejto fázy je len pár neúplných vecí

Výkaz ziskov a strát - úrokové náklady / výnosy sú v tejto fáze neúplné
Súvaha - hotovosť a položky dlhu sú v tejto fáze neúplné

Krok 11A - Výpočet peňažných tokov pre finančné aktivity

Skontrolujte tiež hotovostný tok z financovania

Krok 11B - Nájdite čisté zvýšenie (zníženie) hotovosti a peňažných ekvivalentov

Krok 11C = Vyplňte výkazy peňažných tokov

Na konci roka nájdete peniaze na konci roka a peňažné ekvivalenty.

Krok 11D - Prepojte peňažné prostriedky a ich ekvivalenty s súvahou.

Teraz sme pripravení postarať sa o posledný a posledný harmonogram, tj Plán dlhov a úrokov

# 12 - Finančné modelovanie v programe Excel - plán dlhov a úrokov

Ďalším krokom v tomto online finančnom modelovaní je vyplnenie rozpisu dlhov a úrokov. Zhrnutie dlhu a úrokov - harmonogram

Stupeň 12A - Vytvorenie rozpisu dlhov

Uveďte peňažné toky dostupné na financovanie
Uveďte referenčné všetky zdroje kapitálu a použitia hotovosti

Krok 12B - Výpočet peňažných tokov zo splácania dlhu

Odpočítajte začiatočný hotovostný zostatok v súvahe
Odpočítajte minimálny hotovostný zostatok. Predpokladali sme, že Colgate by si každý rok chcela ponechať minimálne 500 miliónov dolárov.

Zatiaľ preskočte dlhodobé vystavenie / splácanie dlhu, hotovosť k dispozícii pre revolvingový úver a sekciu revolver Z prehľadu Colgate 10K; berieme na vedomie dostupné podrobnosti o revolvingovom úvere Colgate 2013 - 10K, strana 35

V ďalších informáciách o dlhu sú uvedené aj viazané dlhodobé splátky dlhu. Colgate 2013 - 10 tis., Strana 36

Krok 12C - Vypočítajte konečný dlhodobý dlh.

Používame vyššie uvedený plán splácania dlhodobého dlhu a vypočítame konečný zostatok splátok dlhodobého dlhu.

Krok 12D - Prepojte splácanie dlhodobého dlhu.

Krok 12E - Vypočítajte ľubovoľné výpožičky / prehratia.

Pomocou vzorca na zametanie peňazí, ako je uvedené nižšie, vypočítajte ľubovoľné výpožičky / splátky.

Krok 12F - Výpočet úrokových výdavkov z dlhodobého dlhu

Vypočítajte priemerný zostatok pre revolvingový úver a dlhodobý dlh
Na základe informácií uvedených v 10K správe urobte primeraný predpoklad úrokovej sadzby
Vypočítajte celkové úrokové náklady = priemerný zostatok dlhu x úroková sadzba

Nájdite celkové úrokové náklady = úrok (revolvingový úver) + úrok (dlhodobý dlh)

Krok 12G - Vyčerpanie dlhu a revolvera istiny na peňažné toky

Krok 12H - Referenčný prúd a dlhodobý stav k súvahe

Vymedzte súčasnú časť dlhodobého dlhu a dlhodobého dlhu, ako je uvedené nižšie

Prepojte revolvingový úver, dlhodobý dlh a súčasnú časť dlhodobého dlhu so súvahou

Krok 12I - Výpočet úrokového výnosu pomocou priemerného hotovostného zostatku

Krok 12J - Prepojte úrokové náklady a úrokové výnosy s výkazom ziskov a strát

Vykonajte kontrolu súvahy: Celkové aktíva = Pasíva + Vlastné imanie

Krok 12K - Vykonajte audit súvahy

Ak dôjde k nejakej nezrovnalosti, musíme vykonať audit modelu a skontrolovať prípadné chyby prepojenia

Odporúčaný kurz finančného modelovania

Dúfam, že vás Príručka Excel pre bezplatné finančné modelovanie potešila. Ak sa chcete dozvedieť finančné modelovanie v programe Excel prostredníctvom našich odborných video prednášok, môžete si tiež pozrieť naše školenie o investičnom bankovníctve. Jedná sa predovšetkým o balíček školení o 99 kurzoch Investičné bankovníctvo. Tento kurz začína základom a zavedie vás na pokročilú úroveň práce v investičnom bankovníctve. Tento kurz je rozdelený do piatich častí -

1. časť - Školenie investičného bankovníctva - základné kurzy
(26 kurzov)
2. časť - Školenie o modelovaní pokročilého investičného bankovníctva
(20 kurzov)
3. časť - Doplnky investičného bankovníctva
(13 kurzov)
4. časť - Kurzy nadácie investičného bankovníctva
(23 kurzov)
5. časť - Mäkké zručnosti pre investičných bankárov
(17 kurzov)

Sťahovanie finančných modelov

Finančný model Alibaba
Finančný model IPO
Šablóny finančného modelovania
Kurz bankového finančného modelovania

Čo ďalej?

Ak ste sa dozvedeli niečo nové alebo sa vám páčilo toto finančné modelovanie založené na programe Excel, zanechajte prosím komentár nižšie. Daj mi vedieť čo si myslíš. Veľká vďaka a buďte opatrní. Šťastné učenie!