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

Obsah

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

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. O učení se finančnímu modelování toho bylo napsáno hodně; většina tréninkových modelů finančního modelování je však 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ž 6000 slov a její dokončení trvalo tři 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

Zadejte e-mailovou adresu Pokračováním výše uvedeného kroku vyjadřujete souhlas s našimi podmínkami použití a zásadami ochrany osobních údajů.

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í

Jak sestavit finanční model?
# 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í systém v podstatě znamená vytváření základních výkazů, jako je výkaz zisku a ztráty, rozvaha a peněžní toky, pomocí různých modulů / plánů. Klíčovým zaměřením je připravit každé prohlášení krok za krokem a po dokončení připojit všechny podpůrné programy k základním příkazům. Chápu, že to teď nemusí být jasné; uvědomíte si však, ž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.
Různé plány jsou 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.
Různé plány jsou po dokončení propojeny s hlavními příkazy.
V této příručce k finančnímu modelování sestavíme krok za krokem integrovaný ekonomický 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 minulý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íže uvedené 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 toto je práce, kterou musíte pro každou společnost udělat pouze jednou, a také vyplnění historických údajů pomůže analytikovi pochopit trendy a finanční výkazy.
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 ;-))

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

Colgate Income Statement s historickým 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 elementární analýza nebo Ratio Analysis něco nového, doporučuji přečíst si trochu 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 úsilí o vertikální analýzu ve výkazu zisku a ztráty se často označuje jako analýza marží, protože poskytuje různé marže týkající se prodeje.

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 lepší marže, které vzrostly 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 zvýšeným „dalším nákladům“. 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 vypočítanými k různým datům s použitím měny s různou mocí nákupu. Tato analýza ve skutečnosti indexuje zprávy 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 metod.

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ším poměrem likvidity je aktuální poměr, poměr Acid test (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

Současný poměr Colgate je pro všechny roky významnější než 1,0. To znamená, že oběžná aktiva jsou významnější než oběžná pasiva a možná má Colgate dostatečnou likviditu.
Rychlý poměr Colgate je v rozmezí 0,6-0,7; to znamená, že Colgates Cash a Obchodovatelné cenné papíry mohou platit až 70% současných závazků. To vypadá na dobrou situaci pro Colgate.
Cyklus inkasa peněz se snížil ze 43 dnů v roce 2009 na 39 dní v roce 2013. Je to dáno především zkrácením 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á vynikající 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. Sazby, 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ž naznačuje 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 výši příjmových toků, je nesmírně důležitý. Existuje tolik způsobů, jak vytvořit plán příjmů, jako existuje 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 cena na trhu zásadní proměnnou. (Například pro společnost se strategií snižování cen nebo s nejlepším hráčem na trhu s prémiovými cenami) 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í rozhodující 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 poháněny 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 zásadní prodej služeb, softwaru a upgradů. Klíčové je modelování instalované základny (nové přírůstky do podlahy, otěr v zemi, 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; jako další informace však společnost 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 těchto funkcích, budeme na základě těchto dostupných údajů promítat budoucí prodeje 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ý prvek. Nyní můžeme předpokládat procento růstu tržeb na základě historických trendů a promítnout výnosy pod každou část. 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 z jiného harmonogramu: Tento přístup je ve většině případů skutečně minimální přijatelný 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 citlivostní analýzu 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 pět 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í marži 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 výrazného ovladače se obvykle předpokládají ve stálých částkách
Zajistěte, aby byly předpoklady 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; je však zahrnuta 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. Dále si všimněte, že níže uvedená čísla jsou 1) Odpisy 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. Běžnou praxí je podívat se na tiskové zprávy, projekce managementu, MD&A, abychom pochopili pohled 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 složité 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. Místo toho rozmístili 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 se zde dělí na dvě části - 1) odpisy z aktiva vylepšení budovy, které jsou již uvedeny v 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.
Vypočítáváme odpisy z majetkových příspěvků z každého roku.
Také odpisy v prvním roce jsou vyděleny dvěma, protože 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 (další zařízení) Jakmile zjistíme skutečné 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í v rozvaze, když společnost získá jinou společnost. Promítání dobré vůle pro budoucí roky je obvykle komplikované.
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ů nepromítá dobrou vůli; jen to udržují jako konstantní, 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ě společnosti Colgate 10K, většina nehmotného majetku s omezeným životem souvisí s akvizicí společnosti Sanex
„Přírůstky nehmotného majetku“ se také komplikovaně projektují
Zpráva Colgate 10K nám poskytuje podrobnosti o příštích pěti letech amortizačních nákladů.
Tyto odhady použijeme v našem finančním modelu Colgate 2013 - 10K, strana 61

Krok 6C - Koncová č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. To je, když se připravujeme na „zbytky“, které nemají konkrétní předpovědi pro předpovědi. V případě společnosti Colgate byly dalšími dlouhodobými položkami (zbytky) odložená daň z příjmů (závazky a aktiva), ostatní investice a další 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, vrátíme se zpět a znovu se podíváme 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 primární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. Akciový plán akcionářů.

# 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 akcionářů, dividendy, zpětný odkup akcií, opční výnosy 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 společnost Colgate odkoupila akcie, jak vidíme výše.
Naplňte odkup akcií Colgate (v milionech) v listu Excel.
Propojte historický zředěný EPS z výkazu zisku a ztráty
Na historickou částku zpětně odkoupeného by se mělo 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

Společnost Colgate neučinila žádné oficiální oznámení o tom, kolik akcií má v úmyslu odkoupit. Jedinou informací, že jejich 10K hlásí akcie, je, že povolili zpětný 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.
Skutečná 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, vidíme 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 údaje o možnostech 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; vzhledem k nedostatku dat však nepředstavuji žádné další problémy s opcemi. Právě jsme vzali tyto jako nula, 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

Prognóza 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%. Předpokládejme výplatní poměr 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áře. 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 hrubých procent (skutečných)
Vypočítejte průměrné základní a zředěné akcie
Referenční projektované akcie k výkazu 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 vážil průměrný počet akcií a účinek akciových opcí na historické roky.

Krok 10B - Propojte emise akcií a zpětné odkupy akcií z rozvrhu 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ěrné podíly,

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. Plánu dluhu. Do této fáze existuje jen pár neúplných věcí

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

Stupeň 12A - Vytvořte splátkový kalendář

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 by si Colgate chtěla každý rok ponechat minimálně 500 milionů dolarů.

Přeskočte dlouhodobé vystavení dluhu / splátky, hotovost k dispozici pro sekci revolvingových úvěrů a revolvery zatím z 10K reportu Colgate; zaznamenáváme dostupné podrobnosti o revidovaném úvěrovém nástroji Colgate 2013 - 10K, strana 35

V dalších informacích o dluhu jsou poskytnuty také závazky splácení dlouhodobého 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 diskreční výpůjčky / přehrání.

Pomocí vzorce pro vyplacení hotovosti, jak je znázorněno níže, vypočítejte diskreční 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 - Čerpání dluhů a revolverů na hlavní peněžní toky

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

Vymezte aktuální čá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í v oblasti investičního bankovnictví. Jedná se primárně o 99 kurzů balíčku Investiční bankovnictví. Tento kurz začíná základy a přenese vás na pokročilou úroveň práce v oblasti investičního bankovnictví. Tento kurz je rozdělen do pěti čá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ále?

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 buďte opatrní. Šťastné učení!