Dynamické grafy ve finančním modelování

Když vytváříte grafy ve finančních modelech nebo sestavách, měli byste stále dodržovat osvědčené postupy a snažit se, aby byly vaše modely tak flexibilní a dynamické, jak jen můžete. Vždy byste měli ve svých modelech co nejvíce propojit, a to platí i pro grafy. Je logické, že když změníte jeden ze vstupů do svého modelu, mělo by se to projevit v datech grafu, stejně jako v názvech a štítcích.

Vytvoření grafu na datech řízených vzorcem

Stáhnout soubor 0901.xlsx . Otevřete jej a vyberte kartu označenou 9-23 a vyzkoušejte si to sami.

Změna rozevíracího seznamu.

Pokud skryjete data ve zdrojovém listu, v grafu se nezobrazí. Vyzkoušejte to skrytím jednoho ze sloupců na listu Finance a zkontrolujte, zda měsíc v grafu zmizel. Možnosti v části Vybrat zdroj dat můžete změnit tak, aby se zobrazovaly skryté buňky.

Propojení názvů grafů se vzorci

Protože jsou všechna data propojena s rozevíracím seznamem, můžete v grafu snadno vytvořit dynamický název vytvořením vzorce pro nadpis a následným propojením tohoto nadpisu s grafem. Následuj tyto kroky:

V buňce A1 tohoto modelu změňte název na následující: = „Náklady na pětileté strategické předpovědi pro call centrum – „&F1.

Ampersand (&) slouží jako spojnice, která spojí text a hodnoty ze vzorců dohromady.

Místo ampersandu můžete také použít funkci CONCATENATE, která funguje velmi podobně spojením singulárních buněk dohromady, nebo funkce TEXTJOIN je novým přírůstkem do Excelu 2016, která spojí velké množství dat.

Když funguje vzorec v buňce A1, musíte propojit nadpis v grafu s buňkou A1.

Klikněte na název grafu.

Tato část může být záludná. Ujistěte se, že jste vybrali pouze název grafu.

Klepněte na řádek vzorců.

Zadejte = a klepněte na buňku A1.

Stiskněte Enter.

Název grafu se změní, aby zobrazoval, co je v buňce A1.

Propojení názvů grafů se vzorci.

Do grafu nelze vložit žádné vzorce. Můžete s ním propojit pouze jednu buňku. Všechny výpočty je třeba provést v jedné buňce a poté je propojit s názvem, jak je znázorněno.

Vytváření dynamického textu

Podívejte se na přehled měsíčního rozpočtu. Již jsme vytvořili vzorce ve sloupcích F a G, které se automaticky aktualizují podle změn dat a zobrazují, jak jsme na tom v porovnání s rozpočtem.

Vytvoření seskupeného sloupcového grafu.

Nyní na základě těchto dat vytvoříte graf a pokaždé, když se čísla změní, budete rádi, když budete mít možnost vidět, kolik řádkových položek překračuje rozpočet. Následuj tyto kroky:

Zvýrazněte data zobrazující hodnoty účtu, skutečné hodnoty a rozpočtu ve sloupcích B, C a D.

Chcete-li vytvořit seskupený sloupcový graf, vyberte první možnost 2-D sloupec v části Grafy na kartě Vložit na pásu karet.

V buňce A1 vytvořte nadpis s dynamickým datem.

Propojte název grafu se vzorcem v buňce A1.

Upravte graf tak, aby byly nadpisy zarovnány vodorovně, a změňte barvy.

Tento graf bude vypadat mnohem lépe, pokud bude seřazen tak, že větší pruhy jsou na levé straně.

Zvýrazněte všechna data včetně nadpisů a klikněte na tlačítko Seřadit (v části Seřadit a filtrovat na kartě Data na pásu karet).

Zobrazí se dialogové okno Seřadit.

Seřadit podle Skutečnosti od největší po nejmenší.

Třídění dat grafu.

Je velmi snadné pokazit vzorce při řazení, takže se ujistěte, že jste před použitím řazení zvýraznili všechny sloupce od sloupců A až G.

Nyní přidejte do grafu nějaký textový komentář. Můžete to udělat přidáním komentáře do jedné buňky, která je dynamicky propojena s hodnotami v modelu a propojit buňku s textovým polem, aby se komentář v grafu zobrazil.

V buňce A15 vytvořte vzorec, který automaticky spočítá, kolik řádkových položek překračuje rozpočet.

Můžete to udělat pomocí vzorce =COUNTA(G3:G12)-COUNT(G3:G12), který vypočítá, kolik neprázdných buněk je ve sloupci G.

Můžete vidět, že dvě řádkové položky překračují rozpočet, takže je převeďte na dynamický text pomocí vzorce =POČET(G3:G12)-POČET(G3:G12)&”Položky nad rozpočet.”

Dokončený graf s dynamickým textovým polem.

Vložte textové pole do grafu stisknutím tlačítka Textové pole ve skupině Text na kartě Vložit na pásu karet.

Klikněte na graf jednou.

Zobrazí se textové pole.

Opatrně myší vyberte vnější část textového pole, stejně jako jste to udělali v poslední části při propojování názvů grafů.

Nyní přejděte do řádku vzorců a zadejte =.

Klikněte na buňku A15 a klikněte na Enter.

Podle potřeby změňte velikost a umístění textového pole.

Otestujte model změnou čísel tak, aby více položek překročilo rozpočet, a ujistěte se, že se změní komentář v textovém poli.