Finanční modelování v aplikaci Excel (bezplatný průvodce krok za krokem + šablona)

Finanční modelování v aplikaci Excel je proces vytváření finančního modelu, který představuje transakci, operaci, fúzi, akvizici, finanční informace k analýze toho, jak může změna v jedné proměnné ovlivnit konečný výnos, aby bylo možné rozhodnout o jedné nebo více výše uvedené finanční transakce.

Co je finanční modelování v aplikaci Excel?

Finanční modelování v aplikaci Excel je všude na webu a o učení se finančnímu modelování toho bylo napsáno hodně, nicméně většina školení o finančním modelování je přesně stejná. To jde nad rámec obvyklého gýče a zkoumá se praktické finanční modelování, které používají investiční bankéři a analytici výzkumu.

V této příručce Excel pro bezplatné finanční modelování si vezmu příklad Colgate Palmolive a připravím úplně integrovaný finanční model od nuly.

Tato příručka obsahuje více než 6 000 slov a její dokončení trvalo 3 týdny. Uložte si tuto stránku pro budoucí použití a nezapomeňte ji sdílet :-)

NEJDŮLEŽITĚJŠÍ - Stáhněte si šablonu Excel Colgate Financial Modeling a postupujte podle pokynů

Stáhněte si šablonu finančního modelu Colgate

Naučte se krok za krokem finanční modelování v aplikaci Excel

Finanční modelování v školení Excel - Nejprve si přečtěte

Krok 1 - Stáhněte si šablonu finančního modelu Colgate. Tuto šablonu budete pro výuku používat

Stáhněte si finanční model společnosti Colgate

Krok 2 - Vezměte prosím na vědomí, že získáte dvě šablony - 1) Nevyřešený finanční model Colgate Palmolive 2) Vyřešený finanční model Colgate Palmolive

Krok 3 - Budete pracovat na nevyřešené šabloně finančního modelu Colgate Palmolive . Postupujte podle pokynů krok za krokem a připravte plně integrovaný finanční model.

Krok 4 - Šťastné učení!

Obsah

Udělal jsem pro vás snadné navigaci v obsahu, abyste mohli provádět finanční modelování

# 1 - Finanční model společnosti Colgate - historický
# 2 - Poměrová analýza Colgate Palmolive
# 3 - Promítání výkazu zisku a ztráty
# 4- Plán pracovního kapitálu
# 5 - Odpisový plán
# 6 - Časový plán amortizace
# 7 - Další dlouhodobý plán
# 8 - Vyplňování výkazu zisku a ztráty
# 9 - Akciový plán akcionářů
# 10 - Sdílí nevyřízený plán
# 11 - Vyplňování výkazů o peněžních tocích
# 12 - Doporučený plán dluhů a úroků
Kurz finančního modelování
Zdarma finanční modely

Pokud jste ve finančním modelování nováčkem, podívejte se na tuto příručku Co je finanční modelování?

Jak vytvořit finanční model v aplikaci Excel?

Podívejme se na to, jak je finanční model postaven od nuly. Tento podrobný průvodce finančním modelováním vám poskytne průvodce vytvořením finančního modelu krok za krokem. Primární přístup v této příručce k finančnímu modelování je modulární. Modulární přístup v podstatě znamená, že sestavujeme základní výkazy, jako je výkaz zisku a ztráty, rozvaha a peněžní toky, pomocí různých modulů / plánů. Klíčovým cílem je připravit každý výpis krok za krokem a po dokončení připojit všechny podpůrné plány k základním příkazům. Chápu, že to od nynějška nemusí být jasné, nicméně si uvědomíte, že je to velmi snadné, jak postupujeme vpřed. Níže vidíte různé plány / moduly finančního modelování -

Vezměte prosím na vědomí následující -

Základními výkazy jsou výkaz zisku a ztráty, rozvaha a peněžní toky.
Mezi další plány patří plán odpisů, plán provozního kapitálu, plán nehmotných aktiv, plán vlastního kapitálu akcionářů, plán dalších dlouhodobých položek, plán dluhů atd.
Další plány jsou propojeny s hlavními příkazy po jejich dokončení
V této příručce k finančnímu modelování sestavíme krok za krokem integrovaný finanční model Colgate Palmolive od nuly.

# 1 - Finanční modelování v aplikaci Excel - Promítněte historické

Prvním krokem v Průvodci finančním modelováním je příprava Historiků.

Krok 1A - Stáhněte si 10K zprávy společnosti Colgate

„Finanční modely jsou připravovány v excelu a první kroky začínají znalostmi, jak si toto odvětví v posledních letech vedlo. Pochopení minulosti nám může poskytnout cenné postřehy týkající se budoucnosti společnosti. Prvním krokem je proto stažení všech finančních údajů společnosti a jejich vyplnění do listu aplikace Excel. Pro Colgate Palmolive si můžete stáhnout výroční zprávy Colgate Palmolive z jejich sekce Vztahy s investory. Jakmile kliknete na „Výroční zpráva“, najdete následující okno -

Krok 1B - Vytvoření listu historických finančních výkazů

Pokud si stáhnete 10 tis. Z roku 2013, všimnete si, že jsou k dispozici pouze údaje za dva roky účetní závěrky. Pro účely finančního modelování v aplikaci Excel je však doporučeným souborem dat mít posledních 5 let účetní závěrky. Stáhněte si poslední 3 roky výroční zprávy a vyplňte historické.
Mnohokrát se tyto úkoly zdají příliš nudné a zdlouhavé, protože formátování a uvedení aplikace Excel do požadovaného formátu může vyžadovat spoustu času a energie.
Neměli bychom však zapomínat, že se jedná o práci, kterou musíte pro každou společnost provést pouze jednou, a také vyplnění historických údajů pomůže analytikovi pochopit trendy a finanční výkaz
Takže to prosím nevynechávejte, stáhněte si data a vyplňte data (i když máte pocit, že to je práce osla ;-))

Pokud chcete tento krok přeskočit, můžete si přímo stáhnout historický model Colgate Palmolive zde.

Colgate Income Statement s historicky osídleným

Historická data rozvahy Colgate

# 2 - Poměrová analýza

Druhým krokem ve finančním modelování v aplikaci Excel je provedení Ratio Analysis.

Klíčem k učení se finančnímu modelování v aplikaci Excel je schopnost provádět základní analýzu. Pokud je pro vás fundamentální analýza nebo Ratio Analysis něco nového, doporučuji přečíst si něco na internetu. Mám v úmyslu provést hloubkovou analýzu poměru v jednom ze svých nadcházejících příspěvků, zde je však rychlý snímek poměrů Colgate Palmolive

DŮLEŽITÉ - Vezměte prosím na vědomí, že jsem aktualizoval poměrovou analýzu Colgate v samostatném příspěvku. Podívejte se prosím na tuto komplexní analýzu poměru.

Krok 2A - Vertikální analýza Colgate

Ve výkazu zisku a ztráty je vertikální analýza univerzálním nástrojem pro měření relativního výkonu firmy z roku na rok z hlediska nákladů a ziskovosti. Mělo by být vždy zahrnuto jako součást jakékoli finanční analýzy. Zde se procenta počítají ve vztahu k čistému prodeji, který se považuje za 100%. Toto vertikální analytické úsilí ve výkazu zisku a ztráty se často označuje jako analýza marží, protože přináší různé marže ve vztahu k prodeji.

Výsledky vertikální analýzy

Zisková marže se zvýšila o 240 bazických bodů z 56,2% v roce 2007 na 58,6% v roce 2013. Důvodem je především snížení nákladů na prodej
Provozní zisk nebo EBIT rovněž vykázal zlepšení marží, čímž se zvýšil z 19,7% v roce 2007 na 22,4% v roce 2012 (nárůst o 70 bazických bodů). To bylo způsobeno snížením prodejních obecných a administrativních nákladů. Upozorňujeme však, že marže EBIT se v roce 2013 snížily na 20,4% kvůli nárůstu „ostatních nákladů“. Podívejte se také na rozdíl mezi EBIT a EBITDA
Čistá zisková marže se zvýšila z 12,6% v roce 2007 na 14,5% v roce 2012. Zisková marže v roce 2013 se však snížila na 12,9%, zejména kvůli zvýšeným „dalším nákladům“.
Zisk na akcii se od FY2007 do FY2012 neustále zvyšoval. V EPS za rok 2013 však došlo k mírnému poklesu
Pamatujte také, že odpisy a amortizace jsou uvedeny samostatně ve výkazu zisku a ztráty. Je zahrnuta v nákladech na prodej

Krok 2B - Horizontální analýza společnosti Colgate

Horizontální analýza je technika používaná k hodnocení trendů v čase výpočtem procentního zvýšení nebo snížení oproti základnímu roku. Poskytuje analytické propojení mezi účty počítanými k různým datům s použitím měny s různými kupními silami. Tato analýza ve skutečnosti indexuje účty a porovnává jejich vývoj v průběhu času. Stejně jako u metodiky vertikální analýzy se objeví problémy, které je třeba prozkoumat a doplnit o další techniky finanční analýzy. Důraz je kladen na hledání příznaků problémů, které lze diagnostikovat pomocí dalších technik.

Podívejme se na horizontální analýzu Colgate

Výsledky horizontální analýzy

Vidíme, že čistý prodej se v roce 2013 zvýšil o 2,0%.
Všimněte si také trendu v nákladech na prodej, vidíme, že nerostly ve stejném poměru jako tržby.
Tato pozorování jsou velmi užitečná, když provádíme finanční modelování v aplikaci Excel

Krok 2C - Ukazatele likvidity společnosti Colgate

Ukazatele likvidity měří vztah likvidnějších aktiv podniku (těch, které jsou nejsnadněji směnitelné za hotovost) k krátkodobým závazkům. Nejběžnější ukazatele likvidity jsou: Aktuální poměr Poměr testu na kyselinu (nebo rychlé aktivum) Cash Ratios
Poměry obratu jako obrat účtů pohledávek, obrat zásob a obrat závazků

Klíčové vlastnosti ukazatelů likvidity

Aktuální poměr společnosti Colgate je po všechny roky vyšší než 1,0. To znamená, že oběžná aktiva jsou větší než oběžná pasiva a Colgate má možná dostatečnou likviditu
Rychlý poměr Colgate se pohybuje v rozmezí 0,6-0,7, což znamená, že Colgates Cash a obchodovatelné cenné papíry mohou platit až 70% aktuálních závazků. Vypadá to jako rozumná situace pro Colgate.
Cyklus inkasa hotovosti se snížil ze 43 dnů v roce 2009 na 39 dní v roce 2013. Důvodem je především zkrácení doby inkasa pohledávek.

Podívejte se také na tento podrobný článek o cyklu převodu hotovosti

Krok 2D - Poměry provozní ziskovosti společnosti Colgate

Poměr ziskovosti je poměr schopnosti společnosti generovat výdělky v poměru k tržbám, aktivům a vlastnímu kapitálu

Klíčové vlastnosti - Poměr ziskovosti společnosti Colgate

Jak vidíme z výše uvedené tabulky, Colgate má ROE blížící se 100%, což znamená skvělé výnosy pro držitele akcií.

Krok 2E - Analýza rizik společnosti Colgate

Prostřednictvím Analýzy rizik se snažíme zjistit, zda budou společnosti schopny splácet své krátkodobé a dlouhodobé závazky (dluh). Vypočítáváme pákové poměry, které se zaměřují na dostatečnost aktiv nebo generování z aktiv. Poměry, na které se díváme, jsou

Poměr dluhu k vlastnímu kapitálu
Poměr dluhu
Poměr krytí úroků
Poměr dluhu k vlastnímu kapitálu se neustále zvyšuje na vyšší úroveň 2,23x. To znamená zvýšenou finanční páku a rizika na trhu
Poměr krytí úroků je však velmi vysoký, což znamená menší riziko nesplácení úroků.

# 3 - Finanční modelování v aplikaci Excel - Promítněte výkaz zisku a ztráty

Třetím krokem ve finančním modelování je předpovědi výkazu zisku a ztráty, kde začneme s modelováním položek prodeje nebo výnosů.

Krok 3A - Projekce výnosů

Pro většinu společností jsou výnosy základní hnací silou ekonomické výkonnosti. Dobře navržený a logický model příjmů, který přesně odráží typ a množství toků příjmů, je nesmírně důležitý. Existuje tolik způsobů, jak navrhnout harmonogram výnosů, kolik je podniků. Mezi běžné typy patří:

Růst prodeje: Předpoklad růstu prodeje v každém období definuje změnu oproti předchozímu období. Jedná se o jednoduchou a běžně používanou metodu, ale nenabízí žádný pohled na komponenty nebo dynamiku růstu.
Inflační a objemové / směšovací efekty: Místo jednoduchého předpokladu růstu se použije cenový inflační faktor a objemový faktor. Tento užitečný přístup umožňuje modelování fixních a variabilních nákladů ve společnostech vyrábějících více produktů a bere v úvahu pohyby cena vs. objem.
Jednotkový objem, změna objemu, průměrná cena a změna ceny: Tato metoda je vhodná pro podniky, které mají jednoduchý produktový mix; umožňuje analýzu dopadu několika klíčových proměnných.
Velikost a růst trhu v dolarech: Podíl na trhu a změna podílu - Užitečné pro případy, kdy jsou k dispozici informace o dynamice trhu a kde tyto předpoklady pravděpodobně budou pro rozhodnutí zásadní. Například telekomunikační průmysl
Velikost a růst jednotkového trhu: Toto je podrobnější než předchozí případ a je užitečné, když je klíčovou proměnnou cena na trhu. (Například pro společnost se strategií snižování cen nebo s nejlepším hráčem s vynikajícími cenami na trhu) např. Trh s luxusními automobily
Objemová kapacita, míra využití kapacity a průměrná cena: Tyto předpoklady mohou být důležité pro podniky, kde je pro rozhodnutí důležitá výrobní kapacita. (Například při nákupu další kapacity nebo k určení, zda by rozšíření vyžadovalo nové investice.)
Dostupnost produktu a cena
Příjmy byly taženy investicemi do kapitálu, marketingu nebo výzkumu a vývoje
Výnosy založené na instalované základně (pokračující prodej dílů, jednorázových položek, servisu a doplňků atd.). Mezi příklady patří klasické podniky s břitvou a podniky, jako jsou počítače, kde je důležitý prodej služeb, softwaru a upgradů. Klíčové je modelování instalované základny (nové přírůstky do základny, odírání v základně, pokračující výnosy na zákazníka atd.).
Zaměstnaní: Například výnosy firem poskytujících profesionální služby nebo prodejních firem, jako jsou makléři. Modelování by se mělo zaměřit na čisté personální obsazení, výnosy na zaměstnance (často na základě fakturovatelných hodin). Podrobnější modely budou zahrnovat senioritu a další faktory ovlivňující ceny.
Na základě obchodu, zařízení nebo náměstí: Maloobchodní společnosti jsou často modelovány na základě obchodů (staré obchody plus nové obchody v každém roce) a výnosů na obchod.
Na základě faktoru obsazenosti: Tento přístup je použitelný pro letecké společnosti, hotely, kina a další podniky s nízkými mezními náklady.

Promítání výnosů společnosti Colgate

Podívejme se nyní na zprávu Colgate 10K 2013. Bereme na vědomí, že ve výkazu zisku a ztráty společnost Colgate neposkytla segmentové informace, nicméně jako doplňkové informace Colgate poskytla některé podrobnosti o segmentech na stránce 87 Zdroj - Colgate 2013 - 10K, Strana 86

Vzhledem k tomu, že nemáme žádné další informace o segmentech, budeme na základě těchto dostupných údajů promítat budoucí prodej společnosti Colgate. Pro odvození prognóz použijeme přístup k růstu prodeje napříč segmenty. Viz níže uvedený obrázek. Vypočítali jsme meziroční tempo růstu pro každý segment. Nyní můžeme předpokládat procento růstu tržeb na základě historických trendů a projektovat výnosy v každém segmentu. Celkové čisté prodeje jsou součtem souhrnu orálního, osobního a domácího ošetření a segmentu výživy zvířat.

Krok 3B - Projekce nákladů

Procento výnosů: jednoduché, ale nenabízí žádný pohled na jakoukoli páku (úspora z rozsahu nebo zátěž s pevnými náklady)
Jiné náklady než odpisy jako procento z výnosů a odpisy ze samostatného plánu: Tento přístup je ve většině případů skutečně přijatelný pro minimum a umožňuje pouze částečnou analýzu provozní páky.
Variabilní náklady na základě tržeb nebo objemu, fixní náklady na základě historických trendů a odpisy ze samostatného harmonogramu: Tento přístup je minimem nezbytným pro analýzu citlivosti ziskovosti na základě více scénářů výnosů

Projekce nákladů pro Colgate

Pro promítnutí nákladů bude užitečná vertikální analýza provedená dříve. Podívejme se na vertikální analýzu -

Vzhledem k tomu, že jsme již předpovídali tržby, všechny ostatní náklady představují určité rozpětí tohoto prodeje.
Přístup spočívá v převzetí pokynů z historických marží nákladů a výdajů a poté předpovědi budoucí marže.
Například náklady na prodej se za posledních 5 let pohybovaly v rozmezí 41% - 42%. Můžeme se podívat na prognózu marží na tomto základě.
Stejně tak se prodejní, všeobecné a administrativní náklady historicky pohybovaly v rozmezí 34–36%. Na tomto základě můžeme předpokládat budoucí rozpětí nákladů SG&A. Stejně tak můžeme pokračovat v další sadě výdajů.

Pomocí výše uvedených okrajů můžeme najít skutečné hodnoty výpočty zpět.

Pro výpočet rezervy na daně používáme předpoklad efektivní daňové sazby

Všimněte si také, že řádek „Úrokové náklady (výnosy)“ nedokončujeme, protože v pozdější fázi budeme mít přehled o příjmech znovu.
Úrokové náklady a úrokové výnosy.
Rovněž jsme nepočítali odpisy a amortizaci, které již byly zahrnuty do nákladů na prodej.
Tím je vyplněn Výkaz zisku a ztráty (alespoň prozatím!)

# 4- Finanční modelování - plán pracovního kapitálu

Nyní, když jsme dokončili výkaz příjmů, je čtvrtým krokem ve finančním modelování pohled na plán pracovního kapitálu.

Níže jsou uvedeny kroky, které je třeba dodržet pro plán pracovního kapitálu

Krok 4A - Propojte čistý prodej a náklady na prodej

Krok 4B - Odkaz na údaje z rozvahy týkající se provozního kapitálu

Odkazujte na minulá data z rozvahy
Vypočítejte čistý pracovní kapitál
Přijďte na zvýšení / snížení pracovního kapitálu
Všimněte si, že jsme do pracovního kapitálu nezahrnuli krátkodobý dluh a hotovost a peněžní ekvivalenty. Dluhu a hotovosti a peněžním ekvivalentům se budeme věnovat samostatně.

Krok 4C - Vypočítejte obratové poměry

Vypočítejte historické poměry a procenta
Použijte koncový nebo průměrný zůstatek
Oba jsou přijatelné, pokud je zachována dlouhá konzistence

Krok 4D - Naplňte předpoklady pro budoucí položky pracovního kapitálu

Určité položky bez zjevného ovladače se obvykle předpokládají ve stálých částkách
Zajistěte, aby předpoklady byly přiměřené a v souladu s danou činností

Krok 4E - P rojektujte budoucí zůstatky provozního kapitálu

Krok 4F - Výpočet změn pracovního kapitálu

Získejte hotovostní toky na základě jednotlivých řádkových položek
Zajistěte, aby značky byly přesné!

Krok 4G - Propojte předpokládaný pracovní kapitál s rozvahou

Krok 4H - Propojte pracovní kapitál s výkazem peněžních toků

# 5 - Finanční modelování v aplikaci Excel - plán odpisů

Po dokončení harmonogramu pracovního kapitálu je dalším krokem v tomto finančním modelování projekt Capex společnosti Colgate a promítnutí čísel odpisů a aktiv. Colgate 2013 - 10 tis., Strana 49

Odpisy a amortizace nejsou poskytovány jako samostatná řádková položka, jsou však zahrnuty v nákladech na prodej
V takových případech se prosím podívejte na výpisy peněžních toků, kde najdete výdaje na odpisy a amortizaci. Také si povšimněte, že níže uvedené obrázky jsou 1) amortizace 2) amortizace. Jaké je tedy číslo odpisu?
Konečný zůstatek pro PPE = Počáteční zůstatek + Capex - Odpisy - Úprava prodeje majetku (rovnice BASE)

Krok 5A - Propojte údaje o čistém prodeji v plánu odpisů

Nastavte řádkové položky
Referenční čistý prodej
Vstup minulých kapitálových výdajů
Přijďte na Capex jako% z čistého prodeje

Krok 5B - Prognóza položek kapitálových výdajů

Pro predikci kapitálových výdajů existují různé přístupy. Jedním společným přístupem je podívat se na tiskové zprávy, projekce managementu, MD&A, abychom porozuměli pohledu společnosti na budoucí kapitálové výdaje
Pokud společnost poskytla pokyny ohledně budoucích kapitálových výdajů, můžeme tato čísla převzít přímo.
Pokud však čísla Capex nejsou přímo k dispozici, můžeme to hrubě vypočítat pomocí Capexu jako% z prodeje (jak je uvedeno níže)
Použijte svůj úsudek založený na znalostech oboru a dalších rozumných faktorech

Krok 5C - Odkaz na minulé informace

Použijeme Konečný zůstatek pro PPE = Počáteční zůstatek + Capex - Odpisy - Úprava pro prodej majetku (BASE rovnice)
Je velmi obtížné sladit minulé PP&E kvůli přepracování, prodeji aktiv atd
Doporučuje se proto neslučovat minulé OOP, protože by to mohlo vést k určitému zmatku.

Zásady odpisování společnosti Colgate

Bereme na vědomí, že společnost Colgate výslovně neposkytla podrobný rozpis aktiv. Raději seskupili veškerý majetek do pozemků, budov, strojů a dalšího vybavení
V rozsahu je také uvedena doba použitelnosti strojů a zařízení. V tomto případě budeme muset udělat nějaké dohady, abychom dospěli k průměrné době životnosti aktiv
Pokyny pro dobu použitelnosti také nejsou poskytovány pro „Jiná zařízení“. Budeme muset odhadnout životnost ostatních zařízení

Colgate 2013 - 10 tis., Strana 55

Níže je uveden rozpis podrobností o pozemcích, budovách a zařízeních v letech 2012 a 2013

Colgate 2013 - 10 tis., Strana 91

Krok 5D - Odhadněte rozpad stavebních strojů a zařízení (PPE)

Nejprve najděte váhy aktiv současného OOP (2013)
Budeme předpokládat, že tyto váhy aktiv PPE 2013 budou pokračovat dál
Tuto váhu aktiv používáme k výpočtu rozpadu odhadovaných kapitálových výdajů

Krok 5E - Odhadněte odpisy aktiv

Upozorňujeme, že odpisy pozemků nepočítáme, protože půda není odepisovatelným aktivem
Pro odhad odpisů z vylepšení budovy nejprve využijeme níže uvedenou strukturu.
Odpisy jsou zde rozděleny na dvě části - 1) odpisy z aktiva vylepšení budovy, které jsou již uvedeny na rozvaze 2) odpisy z budoucích vylepšení budovy
Pro výpočet odpisů ze stavebních vylepšení uvedených na aktivu používáme jednoduchou přímou metodu odpisování
Pro výpočet budoucích odpisů nejprve provedeme Capex pomocí funkce TRANSPOSE v aplikaci Excel
Z každého roku počítáme odpisy z majetkových příspěvků
Také odpisy v prvním roce jsou vyděleny 2, jak předpokládáme konvence pro nasazení aktiv v polovině roku

Celkové odpisy zlepšení budovy = odpisy z aktiva vylepšení budovy již uvedené na rozvaze + odpisy z budoucích vylepšení budovy Výše uvedený postup pro odhad odpisů se používá k výpočtu odpisů 1) Výrobní zařízení a stroje a 2) ostatní zařízení je uvedeno níže.

Jiné typy zařízení

Celkový odpis společnosti Colgate = Odpisy (vylepšení budovy) + Odpisy (Stroje a zařízení) + Odpisy (ostatní zařízení) Jakmile zjistíme celkové hodnoty odpisů, můžeme to uvést do ZÁKLADNÍ rovnice, jak je uvedeno níže

Díky tomu získáváme údaje o koncovém čistém PP&E pro každý z let

Krok 5F - Propojte čisté PP&E s rozvahou

# 6 - Časový plán amortizace

Šestým krokem v tomto finančním modelování v aplikaci Excel je prognóza amortizace. Zde musíme vzít v úvahu dvě široké kategorie - 1) Goodwill a 2) Ostatní nehmotná aktiva.

Krok 6A - Předpověď dobré vůle

Colgate 2013 - 10 tis., Strana 61

Goodwill přichází do rozvahy, když společnost získá jinou společnost. Obvykle je velmi obtížné promítnout dobrou vůli pro budoucí roky.
Goodwill však každoročně podléhá testům na snížení hodnoty, které provádí sama společnost. Analytici nejsou v pozici, aby takové testy prováděli a připravovali odhady znehodnocení
Většina analytiků neprojevuje dobrou vůli, pouze to udržuje jako konstantní a to je to, co uděláme také v našem případě.

Krok 6B - Prognóza ostatních nehmotných aktiv

Jak je uvedeno ve zprávě Colgate 10K, většina nehmotného majetku s omezeným životem souvisí s akvizicí společnosti Sanex
„Přírůstky nehmotného majetku“ je také velmi obtížné promítnout
Zpráva Colgate 10K nám poskytuje podrobnosti o příštích 5 letech amortizačních nákladů.
Tyto odhady použijeme v našem finančním modelu Colgate 2013 - 10K, strana 61

Krok 6C - Konečná čistá nehmotná aktiva jsou propojena s „Ostatní nehmotná aktiva“

Krok 6D - propojte odpisy a amortizaci s výkazy peněžních toků

Krok 6E - Propojte Capex a dodatek k nehmotným aktivům s výkazy peněžních toků

# 7 - Další dlouhodobý plán

Dalším krokem v tomto finančním modelování je příprava dalšího dlouhodobého harmonogramu. Toto je plán, který připravujeme pro „zbylé“, kteří nemají konkrétní předpovědi pro předpovídání. V případě společnosti Colgate byly dalšími dlouhodobými položkami (zbytky) odložené daně z příjmu (závazky a aktiva), ostatní aktiva a ostatní závazky.

Krok 7A - Odkaz na historická data z rozvahy

Vypočítejte také změny v těchto položkách.

Krok 7B - Prognóza dlouhodobých aktiv a pasiv

Udržujte dlouhodobé položky konstantní po projektované roky v případě, že nejsou viditelné ovladače
Propojte předpokládané dlouhodobé položky s rozvahou, jak je uvedeno níže

Krok 7C - Odkaz na další dlouhodobé položky v rozvaze

Krok 7D - Propojte dlouhodobé položky s Výkazem peněžních toků

Vezměte prosím na vědomí, že pokud bychom dlouhodobá aktiva a pasiva ponechali konstantní, pak by změna plynoucí do výkazu peněžních toků byla nulová.

# 8 - Finanční modelování v aplikaci Excel - Vyplňování výkazu zisku a ztráty

Než se v tomto finančním modelování založeném na Excelu posuneme dále, ve skutečnosti se vrátíme zpět a podíváme se na Výkaz zisku a ztráty
Vyplňte historický základní vážený průměr akcií a zředěný vážený průměr počtu akcií
Tyto údaje jsou k dispozici ve zprávě Colgate 10K

Krok 8A - Odkaz na základní a naředěné akcie

V této fázi předpokládejme, že budoucí počet základních a zředěných akcií zůstane stejný jako v roce 2013.

Krok 8B - Výpočet základního a zředěného zisku na akcii

Díky tomu jsme připraveni přejít na náš další plán, tj. Plán vlastního kapitálu.

# 9 - Finanční modelování - harmonogram vlastního kapitálu

Dalším krokem v tomto finančním modelování v aplikaci Excel Training je podívat se na plán vlastního kapitálu. Primárním cílem tohoto plánu je promítnout položky související s akciemi, jako je vlastní kapitál, dividendy, zpětný odkup akcií, výnosy z opcí atd. Zpráva společnosti Colgate 10K nám poskytuje podrobnosti o běžných akcích a aktivitách v oblasti akcií v minulých letech, jak je uvedeno níže. Colgate 2013 - 10 tis., Strana 68

Krok 9A - Odkup akcií: Vyplňte historická čísla

Historicky Colgate koupila zpět akcie, jak vidíme výše.
Naplňte odkup akcií Colgate (miliony) v listu aplikace Excel.
Propojte historický zředěný EPS z výkazu zisku a ztráty
Na vyplacenou historickou částku je třeba odkazovat z výkazů peněžních toků

Podívejte se také na Accelerated Share Repurchase

Krok 9B - Odkup akcií: Výpočet násobku PE (násobek EPS)

Vypočítejte implicitní průměrnou cenu, za kterou společnost Colgate historicky provedla zpětný odkup akcií. To se počítá jako zpětně odkoupená částka / počet akcií
Vypočítejte násobek PE = implicitní cena akcie / EPS

Krok 9C - Zpětný odkup akcií: Hledání zpětně odkoupených akcií společnosti Colgate

Colgate neučinila žádné oficiální oznámení o tom, kolik akcií zamýšlí odkoupit. Jedinou informací, že jejich akcie 10K vykazují, je, že povolily odkup až 50 milionů akcií. Colgate 2013 - 10 tis., Strana 35

Abychom zjistili počet zpětně odkoupených akcií, musíme předpokládat částku zpětného odkupu akcií. Na základě historické částky zpětného odkupu jsem vzal toto číslo jako 1 500 milionů USD pro všechny budoucí roky.
Abychom zjistili počet zpětně odkoupených akcií, potřebujeme předpokládanou implicitní cenu akcií potenciálního zpětného odkupu.
Implikovaná cena akcie = předpokládaný PE multiplex EPS
Budoucnost zpětného odkupu PE lze předpokládat na základě historických trendů. Upozorňujeme, že společnost Colgate odkoupila akcie v průměru v rozmezí PE 17x - 25x
Níže je snímek společnosti Reuters, který nám pomáhá ověřit rozsah PE pro Colgate

www.reuters.com

V našem případě jsem předpokládal, že všechny budoucí zpětné odkupy Colgate budou na PE násobku 19x.
Pomocí PE 19x můžeme najít implikovanou cenu = EPS x 19
Nyní, když jsme našli implicitní cenu, můžeme najít počet odkoupených akcií = částka $ použitá pro zpětný odkup / implicitní cena

Krok 9D - Možnosti akcií: Naplňte historická data

Ze souhrnu kmenových akcií a vlastního kapitálu víme počet opcí uplatněných každý rok.

Kromě toho máme také výnosy z opcí z výkazů peněžních toků (přibližně)

Díky tomu bychom měli být schopni najít efektivní realizační cenu

Colgate 2013 - 10 tis., Strana 53

Pamatujte také na to, že akciové opce mají smluvní podmínky na šest let a trvají tři roky. Colgate 2013 - 10 tis., Strana 69

S těmito údaji vyplňujeme data opcí níže. Rovněž si všimneme, že vážená průměrná realizační cena akciových opcí pro rok 2013 byla 42 $ a počet uplatnitelných opcí byl 24,151 milionu Colgate 2013 - 10K, strana 70

Krok 9E - Možnosti akcií: Najděte výtěžek opcí

Uvedením těchto čísel do níže uvedených údajů o opcích si všimneme, že výtěžek opce je 1 014 miliard dolarů

Krok 9F - Možnosti akcií: Prognóza Omezená data jednotky akcií

Kromě opcí na akcie jsou zaměstnancům k dispozici jednotky s omezeným skladem s váženým průměrným obdobím 2,2 roku Colgate 2013 - 10K, strana 81

Vyplnění těchto dat v datové sadě Možnosti Pro zjednodušení jsme nepředpokládali vydání opcí (vím, že to není správný předpoklad, nicméně vzhledem k nedostatku dat již neberu žádná další vydání opcí. Právě jsme se rozhodli tyto jsou nulové, jak je zvýrazněno v šedé oblasti výše. Dále se předpokládá, že omezené skladové jednotky budou do budoucna 2,0 milionu.

Podívejte se také na metodu Treasury Stock

Krok 9G - Dividendy: Předpovězte dividendy

Předpověď odhadovaných dividend pomocí výplatního poměru dividend
Opravená dividenda převyšuje výplatu na akcii
Ze zpráv 10K extrahujeme všechny minulé informace o dividendách
S informacemi o vyplacených dividendách můžeme zjistit poměr výplaty dividend = vyplacené dividendy celkem / čistý příjem.
Vypočítal jsem poměr výplat dividend společnosti Colgate, jak je vidět níže - Bereme na vědomí, že poměr výplat dividend byl zhruba v rozmezí 50% - 60%. Udělejme předpoklad výplatního poměru dividend ve výši 55% v budoucích letech.
Můžeme také propojit předpokládaný čistý příjem z výkazu zisku a ztráty
Pomocí promítaného čistého příjmu i výplatního poměru dividend můžeme zjistit celkovou výplatu dividend

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

S prognózou zpětného odkupu akcií, výtěžku opcí a vyplacených dividend jsme připraveni dokončit Akciový plán akcionářů. Propojte je a najděte Konečný zůstatek vlastního kapitálu pro každý rok, jak je uvedeno níže.

Krok 9I - Propojte konec vlastního kapitálu akcionáře s rozvahou

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

# 10 - Sdílí nevyřízený plán

Dalším krokem v tomto online finančním modelování v tréninku aplikace Excel je podívat se na plán sdílení akcií Shares. Shrnutí nevyřízeného harmonogramu akcií

Základní akcie - skutečné a průměrné
Zachyťte minulé efekty opcí a kabrioletů podle potřeby
Zředěné akcie - průměr
Referenční akcie odkoupené a nové akcie z uplatněných opcí
Výpočet předpokládaných základních akcií (skutečných)
Vypočítejte průměrné základní a zředěné akcie
Odkazujte předpokládané akcie na výkaz zisku a ztráty (odvolání sestavení výkazu zisku a ztráty!)
Zadejte historické sdílené nevyřízené informace
Poznámka : Tento plán je běžně integrován do akciového plánu

Krok 10A - Zadejte historická čísla z 10K přehledu

Na vydané akcie (skutečná realizace opcí) a zpětně odkoupené akcie lze odkazovat z harmonogramu akcií akcionáře
Vstup také vážil průměrný počet akcií a účinek akciových opcí na historické roky.

Krok 10B - Propojte emise akcií a odkupy akcií z harmonogramu akciových akcií.

Základní akcie (končící) = Základní akcie (začínající) + emise akcií - akcie odkoupeny zpět.

Krok 10C - Najděte základní vážený průměr akcií,

najdeme průměrně dva roky, jak je uvedeno níže.
Přidejte také účinek opcí a omezených akciových jednotek (na které se odkazuje z akciového plánu akcionáře), abyste našli Zředěný vážený průměr akcií.

Krok 10D - Propojte základní a zředěné vážené akcie s výkazem zisku a ztráty

Nyní, když jsme vypočítali zředěný vážený průměr akcií, je čas, abychom to stejné aktualizovali ve výkazu zisku a ztráty.
Propojte předpokládané zředěné vážené průměrné akcie v oběhu s výkazem zisku a ztráty, jak je uvedeno níže

Tímto dokončujeme harmonogram sdílení akcií a čas přejít na naši další sadu prohlášení.

# 11 - Vyplňování výkazů o peněžních tocích

Je pro nás důležité, abychom plně vyplnili výkazy peněžních toků, než přejdeme k dalšímu a konečnému harmonogramu v tomto finančním modelování, tj. K rozpisu dluhů.

Výkaz zisku a ztráty - úrokové náklady / výnosy jsou v této fázi neúplné
Rozvaha - hotovost a dluhové položky jsou v této fázi neúplné

Krok 11A - Výpočet peněžních toků pro finanční aktivity

Podívejte se také na hotovostní tok z financování

Krok 11B - Najděte čisté zvýšení (snížení) hotovosti a peněžních ekvivalentů

Krok 11C = Vyplňte výkazy peněžních toků

Na konci roku najdete hotovost a peněžní ekvivalenty na konci roku.

Krok 11D - Propojte peníze a peněžní ekvivalenty s rozvahou.

Nyní jsme připraveni postarat se o náš poslední a poslední plán, tj. Plán dluhu a úroků

# 12 - Finanční modelování v aplikaci Excel - plán dluhů a úroků

Dalším krokem v tomto online finančním modelování je dokončení plánu dluhů a úroků. Shrnutí dluhu a úroků - harmonogram

Krok 12A - Nastavte plán dluhu

Uveďte peněžní toky dostupné pro financování
Uveďte všechny zdroje kapitálu a použití hotovosti

Krok 12B - Výpočet peněžních toků ze splácení dluhu

Odkaz na počáteční hotovostní zůstatek z rozvahy
Odečtěte minimální zůstatek hotovosti. Předpokládali jsme, že Colgate by si každý rok chtěla ponechat minimálně 500 milionů dolarů.

Přeskočit dlouhodobé emise / splácení dluhů, hotovost pro revolvingový úvěr a sekci revolverů Prozatím ze zprávy Colgate 10K si všimneme dostupných podrobností o revolvingovém úvěru Colgate 2013 - 10K, strana 35

V dodatečných informacích o Dluhu jsou také uvedeny závazky k dlouhodobému splácení dluhu. Colgate 2013 - 10 tis., Strana 36

Krok 12C - Výpočet konečného dlouhodobého dluhu

Používáme výše uvedený plán splácení dlouhodobého dluhu a počítáme konečný zůstatek splátek dlouhodobého dluhu

Krok 12D - Propojte splácení dlouhodobého dluhu.

Krok 12E - Vypočítejte libovolné výpůjčky / výplaty

Pomocí vzorce pro výplatu hotovosti, jak je znázorněno níže, vypočítejte libovolné výpůjčky / výplaty.

Krok 12F - Výpočet úrokových nákladů z dlouhodobého dluhu

Vypočítejte průměrný zůstatek pro revolvingový úvěr a dlouhodobý dluh
Na základě informací uvedených ve zprávě 10K vytvořte přiměřený předpoklad pro úrokovou sazbu
Vypočítejte celkové úrokové náklady = průměrný zůstatek dluhu x úroková sazba

Najděte celkové úrokové náklady = úrok (revolvingový úvěr) + úrok (dlouhodobý dluh)

Krok 12G - Propojte čerpání jistiny a revolveru s peněžními toky

Krok 12H - Referenční aktuální a dlouhodobý k rozvaze

Vymezte současnou část dlouhodobého dluhu a dlouhodobého dluhu, jak je uvedeno níže

Propojte revolvingový úvěr, dlouhodobý dluh a aktuální část dlouhodobého dluhu s rozvahou

Krok 12I - Výpočet úrokového výnosu pomocí průměrného hotovostního zůstatku

Krok 12J - Propojte úrokové výdaje a úrokové výnosy s výkazem zisku a ztráty

Proveďte kontrolu rozvahy: Celková aktiva = pasiva + vlastní kapitál

Krok 12K - Proveďte audit rozvahy

Pokud dojde k nějaké nesrovnalosti, musíme model auditovat a zkontrolovat případné chyby propojení

Doporučený kurz finančního modelování

Doufám, že se vám líbil průvodce Excel pro bezplatné finanční modelování. Pokud se chcete naučit finanční modelování v aplikaci Excel prostřednictvím našich odborných video přednášek, můžete se také podívat na naše školení investičního bankovnictví. Jedná se primárně o 99 kurzů balíčku Investiční bankovnictví. Tento kurz začíná od základů a zavede vás na pokročilou úroveň práce v oblasti investičního bankovnictví. Tento kurz je rozdělen do 5 částí -

Část 1 - Školení investičního bankovnictví - základní kurzy
(26 kurzů)
Část 2 - Školení modelování pokročilého investičního bankovnictví
(20 kurzů)
Část 3 - Doplňky investičního bankovnictví
(13 kurzů)
Část 4 - Kurzy nadace investičního bankovnictví
(23 kurzů)
Část 5 - Měkké dovednosti pro investiční bankéře
(17 kurzů)

Stažení finančních modelů

Finanční model Alibaba
Box IPO finanční model
Šablony pro finanční modelování
Kurz bankovního finančního modelování

Co dál?

Pokud jste se dozvěděli něco nového nebo jste si užili toto finanční modelování založené na Excelu, zanechte prosím níže komentář. Dej mi vědět, co si myslíš. Mnohokrát děkuji a dávejte pozor. Šťastné učení!