Ing. Radek Káňa - Finanční modelování v MS Excel
Finanční modelování v MS Excel
Praha 20. 11. 2014
© Controller Institut, 2014
Ing. Radek Káňa
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Cíl semináře Seznámit se s postupy a principy tvorby finančního/obchodního modelu společnosti. Získat praktické zkušenosti s tvorbou finančního/obchodního modelu organizace od jeho specifikace a projektování přes vlastní tvorbu modelu až po jeho testování. Zvládnutí metody DSP (Dynamic spreadsheet planning)
2
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Obsah semináře A ) Zahájení. Principy tvorby finančního / obchodního modelu. Analýza zadání; vytvoření struktury modelu a základních vazeb. Vytvoření listu předpokladů a plánovací matice.
9:00-10:30
B ) Zpracování historie.
11:00-12:30
C) Modelování předpovědi.
13:30-15:00
D) Konsolidace historie a plánu + Diskuse.
15:30-17:00
3
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Fáze tvorby modelu
Specifikace modelu Projektování modelu Vlastní tvorba modelu Testování modelu Analýzy a dokumentace
4
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Specifikace modelu Jaký je účel modelu a jaké jsou očekávané výstupy? Jaké jsou vstupy do modelu a jejich zdroje (data a předpoklady)? Jaký obchodní model má být v modelu použit? Jaká je požadovaná úroveň detailu? Jaká je požadovaná úroveň flexibility modelu – jaké parametry/struktury musí být snadno upravitelné? Jaká je struktura a délka modelované časové osy? Kdo a jak bude model používat; Jaké jsou znalosti uživatelů? Jak často bude model používán a jak dlouho? Jak rychle je model požadován?
5
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Makroekonomické ukazatele
Specifikace modelu Zákazníci
Nová konkurence
Inflace, HDP, vývoj mezd, kurz Kč
Trh
Konkurence
Substituty
Prodejní kanál
Region
Prodej Objem produkce
Obchodní model Tržby
Mix výrobků
Hrubé tržby
Cena za jednotku
Slevy Prodej Tržby
Daně Čisté Tržby
Náklady
Náklady
Variabilní náklady Hrubá marže
Zisk
Cena komodit Dodavatelé
Provozní náklady Čistá marže Fixní náklady
Zisk
EBIT
6
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Odhad nebo předpověď ? Klíč k úspěšným prognózám/předpovědím je v identifikování pouze jedné proměnné, od které se budou odvíjet všechny ostatní odhady. Klíčovou proměnnou většinou představuje objem prodejů. Správná předpověď/prognóza/forecast vyžaduje velký podíl odborných znalostí zatímco odhad vyžaduje jednoduchou logiku a matematiku
7
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Modelování historie Tržby Skutečnost
Prodané množství Skutečnost
Cena za jednotku Skutečnost
Pánovaný nárůst cen
Tržby Plán
Prodané množství Plán
Cena za jednotku Plán
Modelování historie ověří správnost logických vazeb v modelu a zaručí kontinuitu mezi historií a plánem 8
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Metody projekce budoucího vývoje Indexy Manuální vstupy -kvalifikovaný odhad
Trendy
Budoucnost
9
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Projektování modelu
Metoda • Finanční kalkulačka • Tabulkový procesor (EXCEL) • Speciální software / HW (Enterprise Resource Planning ERP)
• Klasické tabulky a vzorce • Zápis VBA • Matice a oblasti • Kombinace výše uvedených metod
Nástroj 10
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Metody tvorby modelu v MS Excel Klasické tabulky a vzorce
Zápis VBA
DSP
• Jednoduchost • Rychlost • Ad hoc řešení
• Robustnost • Eliminace linkovacích chyb • Zpracování velkého množství dat
• Robustnost & Flexibilita • Rychlost tvorby a úprav • Redukce chyb a jejich rychlé dohledání
• Linkovací chyby • Náročnost pozdější úpravy
• Náročnost na zdroje • Nemožnost auditingu • Malá flexibilita
• Netradiční metoda • Znalosti maticových vzorců
11
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Zdrojová data Zdroje dat • Interní / Externí • Provozní / Finanční • Exaktní / Odhadovaná
Provozní data x Finanční data • V případě, že interní provozní data se nerovnají finančním výsledkům, je třeba: • Kvantifikovat rozdíly • Identifikovat důvody rozdílů • Harmonizovat provozní data na finanční výsledek 12
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Tvorba modelu Při vlastní tvorbě modelu je výhodné rozdělit model na 3 základní bloky. První blok obsahuje předpoklady a zdrojová data, druhý blok tvoří analytická část modelu zahrnující klíčové vzorce a výpočty a poslední část tvoří prezentační vrstva obsahující výstupy modelu.
Výstupy
Vzorce a výpočty Vstupy, zdrojová data a předpoklady 13
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Pravidla při tvorbě modelu Vstupy, zdrojová data a předpoklady • Jasně oddělit vstupy a předpoklady od výpočtů. • Vstupy používané v několika částech modelu zadávat pouze jednou. • Čitelně popsat a barevně označit buňky pro zadávání vstupů. • Používat nástroj validace vstupů / podmíněné formátování • Pro důležité předpoklady a proměnné používat pojmenované oblasti. 14
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Pravidla při tvorbě modelu Vzorce a výpočty • • • • •
Zabezpečit list s výpočty proti přepsání Dodržovat zápis zleva doprava a z vrchu dolů Vytvářet univerzální vzorce Označit nekonzistenci ve vzorcích (Historie / Plán) Dodržovat konzistentní úpravu a design jednotlivých pracovních listů • Používat stejná časová data ve sloupcích pracovních listů (sloupec K = rok 2010, L=2011 atd.) • Používat křížové kontroly. 15
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Pravidla při tvorbě modelu Výstupy • Nastavit výstupy jasně a jednoznačně (grafy tabulky) • Připravit všechny klíčové předpoklady v tisknutelném formátu • Vytvářet verze modelů a zaznamenávat hlavní změny mezi verzemi. • Umožnit porovnání vůči výsledkům předchozí verze modelu.
16
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Testování modelu
Funkčnost
Stabilita
Logika
Zero case – zadání hodnot klíčových ukazatelů a předpokladů na úroveň výchozího období pro plánování Extrémní hodnoty Kontrola „zapomenutých“ hodnot Kontrola integrity vzorců
17
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Využití modelu Po dokončení a otestování modelu máme k dispozici výkonný pracovní nástroj, který může poskytnout silnou podpodu při finančním rozhodování v mnoha oblastech: Plánování Rozpočtování Strategie
Odpovědi na „Co když“ analýzy (what if)
Citlivostní analýzy Procentická změna zisku/marže v závislosti na procentické změně cen vstupů
Analýzy struktury predikované hrubé marže (Vliv objemu, ceny, mixu)
Scénáře (Optimistický, Realisticky, Pesimistický)
Trendy
Identifikace korelací
Vývoj trhu
Benchmarking
Risk management a evaluace rizik
Vývoj profitability výrobků a služeb
Při použití modelovací techniky na principu vzájemně propojených matic a pojmenovaných oblastí je možné vytvořit rozsáhlou datovou oblast, se kterou dokáže pracovat kontingenční tabulka. Získáme tak v jednou okamžiku plánované výstupy nejenom za celou společnost, ale i výsledky pro výrobky, balení, segmenty nebo prodejní kanály.
18
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Dokumentace modelu Správce modelu by měl udržovat záznam o všech relevantních upravách, které byly ve finančním modelu udělány, aby bylo možné zajistit kontrolu provedených změn a jejich dokumentaci.
Platí to jak pro dobu tvorby modelu, tak i pro samotné používání hotového modelu, kdy je třeba zaznamenávat jak úpravy modelu, tak i změny vstupních parametrů.
U modelů, které mají být použity vícekrát, je vhodné vytvořit i stručný popis a návod na používání modelu.
19
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Zdroje www.financialmodelingguide.com www.spreadsheetzone.com www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp spreadsheets.about.com spreadshetpage.com en.wikibooks.org/wiki/Financial_Modelling_in_Microsoft_Excel
20
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Funkce
Nástroje
Textové funkce
Kontingenční tabulka
Informační funkce
Maticové vzorce
Statistické funkce (sezónalita)
Citlivostní analýza (řešitel)
Odkaz na buňky v kontingenční tabulce
Správce názvů
Vyhledávací funkce a funkce pro odkazy
Vlastní formát čísla
21
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Funkce excelu • • • • • • • • •
INDEX (odkaz;řádek;sloupec;oblast) INDEX POZVYHLEDAT (co;prohledat;[shoda]) MATCH NEPŘÍMÝ.ODKAZ (odkaz;a1) INDIRECT LINREGRESE (pole_y;[pole_x];[b];[stat]) LINEST POLÍČKO (informace;[odkaz]) CELL T(hodnota) T ČÁST(text;start;počet_znaků) MID NAJÍT(co;kde;start) FIND ZPRAVA / ZLEVA (text;počet znaků) RIGHT/LEFT
22
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Jména a oblasti F3 = vložit název Ctrl + F3 = správce názvů pravý Alt + F3 = nový název Místo pro zadávání názvů oblastí. Jména oblastí a konstant definovaných na pracovním sešitu se zobrazí po nastavení lupy nižší než 40%
23
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Maticové vzorce Výhody použití maticových vzorců • Konzistence – v dané oblasti je pouze jeden vzorec • Bezpečnost – Není možné přepsat samostatnou buňku v maticovém vzorci. Pro úpravu maticového vzorce musí být označena celá oblast se vzorcem. • Menší velikost souboru Nevýhody použití maticových vzorců • Při zadávání maticových vzorců je třeba neustále pamatovat na použítí kombinace kláves CTRL+SHIFT+ENTER. • Ostatní uživatelé nemusí rozumět vzorcům. Maticové vzorce jsou relativně málo dokumentované. • V závislosti na rychlosti systému, velké maticové vzorce mohou snížit rychlost výpočtu.
Pravidla při práci s maticovými vzorci: • Vybrat oblast buněk před zadáním maticového vzorce. • Není možné změnit obsah jednotlivých buněk v rámci maticového vzorce. • Je možné smazat nebo přesunout celý maticový vzorec, ale není možné přesunout nebo smazat jenom jeho část.Při úpravě maticového vzorce je nutné nejdříve smazat stávající vzorec a pak vytvořit nový.
24
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Excel Name manager -Správce oblastí (Obr.1) Add-in Name manager+(Obr.2)
Obr.2
Obr.1
http://www.jkp-ads.com/officemarketplacenm-en.asp
25
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Kontingenční tabulky • Získání dat z kontingenční tabulky GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...) • ZÍSKATKONTDATA(datové_pole;kontingenční_tabulka;pole1;položka1; pole2;položka2;…)
26
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Kontingenční tabulky • Úprava vstupních dat pomocí kontingenční tabulky a jejich následný převod do formátu datového pole vhodného pro zpracování další kontingenční tabulkou. • Nastavení kontingenční tabulky do tabulkového formátu • Odstranění všech mezisoučtů • Ctrl + C Zkopírování kontingenční tabulky • ALT + E (Vložit); Alt + S (Vložit jinak) ; Alt + V (Jako oblast hodnot) • Nechat označenou celou nově vytvořenou oblast hodnot. • Ctrl + G (Go to) ; Alt + S ( Special); Alt + K (Blanks); • Do jedné z označených buněk napsat “=“ bez uvozovek a zmáčknout šipku nahoru a enter. (Celá oblast se vyplní hodnotou buňky, která je v předchozím řádku a stejném sloupci.
27
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Funkce a nástoje Excelu Dimenze Brand Attribute Ryzlink rýnskýJakostní
Colour White
Product_Name Channel_ P10 HoReCa
Hodnoty Package_ Bottle
Tetrapack
Modern tradeBottle
Tetrapack
Other
Bottle
Values Sum of Volume
Year_ 2009 2010 2011 2012 2009 2010 2011 2012 2009 2010 2011 2012 2009 2010 2011 2012 2009 2010
925 837 797 758 397 416 467 517 11 842 10 717 10 207 9 697 1 715 1 800 2 018 2 236 81 73
Sum of Net_Revenue 4 040 234 3 760 060 3 682 331 3 597 005 1 946 798 2 101 729 2 423 114 2 760 948 51 985 780 48 019 820 46 661 840 45 211 816 6 790 797 7 284 115 8 341 847 9 438 823 317 164 294 740
Brand Attribute Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní Ryzlink rýnskýJakostní
Kontingenční tabulka v tabulární podobě bez mezisoučtů
Souvislá datová oblast vhodná jako vstup pro kontingenční tabulku Colour White White White White White White White White White White White White White White White White White White
Product_Name Channel_ Package_ P10 HoReCa Bottle P10 HoReCa Bottle P10 HoReCa Bottle P10 HoReCa Bottle P10 HoReCa Tetrapack P10 HoReCa Tetrapack P10 HoReCa Tetrapack P10 HoReCa Tetrapack P10 Modern tradeBottle P10 Modern tradeBottle P10 Modern tradeBottle P10 Modern tradeBottle P10 Modern tradeTetrapack P10 Modern tradeTetrapack P10 Modern tradeTetrapack P10 Modern tradeTetrapack P10 Other Bottle P10 Other Bottle
Year_
Sum of Volume 2009 2010 2011 2012 2009 2010 2011 2012 2009 2010 2011 2012 2009 2010 2011 2012 2009 2010
925 837 797 758 397 416 467 517 11 842 10 717 10 207 9 697 1 715 1 800 2 018 2 236 81 73
28
© Controller Institut, 2014
www.controlling.cz
Ing. Radek Káňa - Finanční modelování v MS Excel
Modelový příklad Výrobní a obchodní vinařská firma Široký sortiment vína Čtyři druhy balení Pět prodejních kanálů Plán na 3 roky dopředu po hrubou marži Cena se navyšuje o inflaci každý rok od 1. ledna
29
© Controller Institut, 2014
www.controlling.cz
