Jak v Excelu vytvořit roční plán osobních financí | Vzor plánu ke stažení

V dnešním videu vytvoříme jednoduchý roční plán rodinných financí. Začneme tím, že naplánujeme očekávané příjmy, následně naplánujeme rodinné výdaje, a to dle výdajových kategorií a nakonec to spojíme do jednoduchého přehledu, který vám ukáže strukturu vašich plánovaných příjmů a výdajů, roční i měsíční plán a předpokládané úspory. Tento plán následně můžete použít jako zdroj pro porovnání ročního plánu rodinných financí s realitou během roku. V předešlém videu jsme si ukázali, jak vytvořit interaktivní přehled osobních financí v Excelu

Excelové soubory ke stažení

Plán rodinných financí – Excelový soubor k dodělání

Pro stažení hotového přehledu vyplňte prosím jméno a email.

Jak v Excelu vytvořit roční plán osobních financí

V excelovém sešitu ke stažení máme předpřipravené tři listy. Na jednom listu, který se jmenuje Příjmy naplánujeme roční příjmy, na dalším listu, který je pojmenovaný jako Výdaje naplánujeme roční výdaje po kategoriích a na třetím listu, který máme nazvaný jako Přehled vytvoříme jednoduchý přehled ročních příjmů, výdajů a úspor.

Toto video slouží jako ukázka toho, jak excelový roční plán rodinných financí vyplňovat a jak si ho můžete dle svých potřeb dodělat. Proto jsou některé jeho části v rychlejším sledu. Pokud potřebujete u některé části více času, například na vyplnění položek, tak video pauzněte, v klidu si část dodělejte a pak se k videu vraťte.

Plán ročních rodinných financí dnes vytvoříme pomocí velmi jednoduchých technik, které zvládne i naprostý začátečník v Excelu. Účelem videa je připravit si plán, který vám poslouží k sestavení ročního plánu. Proto k jeho vytvoření nebudeme používat žádné složité funkce ani pokročilé kombinace funkcí. Sami uvidíte, až si plán doděláte spolu se mnou, že ne vždy musíte být pokročilý uživatel Excelu, abyste dosáhli kýženého výsledku. 

Roční plán osobních financí 1
Obrázek č.1 Hotový přehled plánu osobních financí

Příjmy

A začneme na listu Příjmy. V Excelu ke stažení máme na listu Příjmy předpřipravené záhlaví pro příjmovou tabulku. V záhlaví tabulky jsou měsíce a sloupec Celkem. Nejedná se ale o obyčejnou tabulku, ale o oficiální excelovou tabulku, která je pojmenována jako Příjmy. Do této tabulky můžete vyplnit všechny druhy příjmů, které očekáváte během následujícího roku.

Každá domácnost má nejspíše něco jako hlavní příjem. Do první buňky příjem tedy napíšeme Hlavní příjem 1, vy si to ale můžete pojmenovat dle svého uvážení. Naše fiktivní domácnost má dva dospělé členy, se dvěma příjmy, takže vyplníme i druhý hlavní příjem. V tabulce následně vyplníme hodnoty, které očekáváme, jak u prvního příjmu, tak u druhého příjmu, a to pro celý rok.

Během roku můžeme mít ale klidně další dodatečné příjmy, záleží na situaci každého z nás. Některé domácnosti mají vedlejší příjmy, přídavky, alimenty, bonusy, prémie nebo jakékoliv jiné příjmy. V hotové excelové tabulce ke stažení mám pro inspiraci několik různých příjmů, které se mohou u domácností vyskytnout.

Obrázek č.2 List příjmy v plánu osobních financí

Pro příjmy, které se vás týkají, vyplníte částky pro měsíce, ve kterých daný příjem očekáváte. Poslední, co zbývá doplnit, je řádek celkového součtu. Pod všechny příjmy doplníme řádek celkového součtu (řádek 14), kde sečteme příjmy pro jednotlivé měsíce. Klikneme do první buňky (B14) a buď použijeme funkci SUMA a nebo si pomůžeme klávesovou zkratkou ALT + =, což vloží automatickou sumu

Vzorec protáhneme pro všechny měsíce.

Obrázek č.3 Automatická suma pro sečtení celkových příjmů

Stejně tak máme na konci příjmové tabulky ještě vytvořený sloupec celkového součtu (sloupec N). V tomto sloupci naopak sečteme hodnoty na řádku. Opět můžeme kliknout do první buňky (N4) a zmáčknout klávesovou zkratku ALT+= a vloží se vzorec automatické sumy. Tím, že pracujeme s oficiální excelovou tabulkou, tak není potřeba vzorec protahovat, ale vzorec se automaticky propíše do všech buněk.

Roční plán osobních financí 4
Obrázek č.4 % podíl příjmů na celkových příjmech

V pravém dolním rohu tabulky (N14) se objeví celkový součet všech plánovaných příjmu za celý rok. 

Pokud by vás zajímal podíl jednotlivých příjmů na celkovém příjmu, tak si do tabulky můžete přidat sloupec (sloupec O), který nazveme třeba % podíl a ve kterém použijeme vzorec: celkový příjem na řádku (N4) vydělený buňkou celkového ročního příjmu (N14). Jelikož chceme vzorec stahovat dolů, tak nesmíme zapomenout zafixovat buňku celkového příjmu jedním stisknutím klávesy F4

Vzorec potvrdíme a opět se vzorec propíše na všechny řádky. Musíme jen změnit formát na procenta. Klikneme na záhlaví sloupce, až se kurzor myši změní na černou šipku, zmáčkneme pravé tlačítko myši, tím se označí celý sloupec a změníme formát na procenta s dvěma desetinnými místy. 

Tabulka ročních plánovaných příjmů je hotová.

Výdaje

Stejný princip, jako na listu Příjmy, použijeme i na listu Výdaj. Jelikož výdajů máme ale většinou daleko více než příjmů, tak jednotlivé výdaje rozdělíme do kategorií. Kategorie si můžete upravit podle sebe. V hotovém excelovém souboru ke stažení mám předpřipraveno několik tabulek, které jsou opět ve formátu oficiálních excelových tabulek. Tabulky jsou v záhlaví pojmenované dle kategorie, kterou reprezentují. Stejně tak je pojmenována i celá tabulka. V tabulkách mám pro jednotlivé kategorie předpřipravené nejčastější výdaje. 

Množství tabulek a kategorií závisí na tom, jak detailní roční rozpočet plánujete.

První tabulka (A5:O16) obsahuje výdaje, které jsou spojené s bydlením. Do jednotlivých polí si zase můžete doplnit všechny výdaje, které máte spojené s bydlením a to po jednotlivých měsících. Pokud byste potřebovali v některé kategorii více řádků, tak stačí řádky přidat. Jelikož se jedná o oficiální excelovou tabulku, tak nově přidané řádky budou automaticky doplněné do celkových součtů.

Roční plán osobních financí 5
Obrázek č.5 Tabulky výdajů na listu Výdaj

Další tabulky, které na listu Výdaj jsou, jsou kromě tabulky Bydlení ještě tabulka obsahující výdaje za dopravu, úvěry včetně hypotečního úvěru, vzdělání, jídlo a pití, zábavu, pojištění, investice a souhrnná kategorie ostatní. Vy si samozřejmě můžete tento list upravit a dodělat si tabulky pro další kategorie výdajů. Případně si položky výdajů přeuspořádat do kategorií tak, aby vám to dám to dávalo smysl.

Stejně jako u tabulky Příjmů, máme i zde řádek celkového součtu a sloupec se součty a % podíl, tedy kolika procenty se daný výdaj podílí na celkových výdajích dané kategorie. 

% podíl kategorie na celkových výdajích

Pro lepší pochopení rodinných financí by se nám zde ale hodil ještě jeden sloupec, který by vyjadřoval podíl dané výdajové položky na celkových nákladech. Teď máme spočítaný podíl na kategorii, ale zajímalo by nás, kolik % z celkových výdajů tvoří nájem. 

K tomu si nad tabulkami dopočítáme řádek celkových nákladů za všechny kategorie dohromady. Máte několik možností, jak to udělat. Nejjednodušším způsobem je, že jednotlivé celkové řádky pro všechny kategorie posčítáte. Do buňky B3 byste napsali rovná se a postupně byste sečetli všechny lednové částky. Jelikož jsme ale při vkládání celkových součtů použili automatickou sumu pomocí klávesové kombinace ALT+=, tak  automatická suma nevložila prostou sumu, ale funkci SUBTOTAL.

Obrázek č.6 Funkce AGGREGATE pro celkové výdaje

To se momentálně hodí, jelikož místo sčítání jednotlivých položek můžeme použít funkci AGGREGATE

Funkce AGGREGATE obsahuje rovněž funkci SUMA, ale dovoluje nám ignorovat jiné součty provedené funkcí SUBTOTAL. Napíšeme tedy funkci AGGREGATE, kde jako funkci vybereme součet (9), následuje parametr možnosti, kde vybereme možnost ignorovat jiné součty SUBTOTAL (0), jako matici označíme buňky, které chceme sčítat včetně celkových součtů. Jelikož v budoucnosti můžete kategorie přidávat, tak označím první buňku nad první tabulkou a protáhnu sloupec dolů i za poslední tabulku (B4:B124). V takovém případě, když pod poslední tabulku přidáte další data, tak se součty automaticky zahrnou do celkového součtu.

Funkci potvrdíme a vrátil se součet všech položek ve sloupci, ale bez započítání celkových součtů (B3).

Naše celkové výdaje za všechny kategorie jsou tedy momentálně 798 000 Kč za rok (N3). Tento součet jsme počítali proto, jelikož jsme chtěli dopočítat % podíl výdajů na celku. Stačí tedy kliknout do buňky (P6) a vydělit první výdaj celkovými výdaji (N3). Nesmíme zapomenou tuto buňku celkových výdajů zafixovat klávesou F4. Funkci potvrdíme a funkce se automaticky doplní na všechny řádky. To samé bychom udělali pro všechny ostatní položky v ostatních tabulkách.   

Roční plán osobních financí 7
Obrázek č.7 % podíl výdajů na celkových výdajích

Když máte všechny tabulky plánovaných příjmů i výdajů vyplněné, tak se můžeme vrhnout na poslední list, na kterém si vytvoříme jednoduchý přehled toho, co jsme si právě naplánovali.

Přehled  

Na listu přehled máme předpřipravené záhlaví listu a záhlaví tabulek a grafů, které budeme tvořit. Podíváme se, co máme na hotovém přehledu. Začneme tabulkami.

Tabulka celkových příjmů

Začneme první tabulkou, kde bychom chtěli zobrazit součet příjmů. Nepotřebujeme ale vidět kompletní rozpad po příjmech, tento detail máme na listu Příjem. Spíše nás zajímá součet celkových hlavních příjmů, vedlejších příjmů a ostatní příjmy můžeme schovat do ostatní příjmy. Máme zde dva sloupce, roční příjmy a měsíční. Začneme s ročními příjmy.

Nejjednodušší způsob, jak tuto tabulku vyplnit je jednoduše propojit buňky s listem příjmy. Začneme hlavními příjmy. Hlavní příjmy máme uvedené v tabulce příjmů na stejnojmenném listu. Napíšeme tedy rovná se a proklikneme se na list Příjem, kde nejprve označíme hodnotu celkem u prvního hlavního příjmu (Příjem!N4) a k tomu přičteme celkovou hodnotu druhého hlavního příjmu (Příjem!N5). Potvrdíme a první hodnota je doplněná.

Následuje součet vedlejších příjmů, znovu napíšeme rovná se, a překlikneme se na list Příjmy, kde označíme první vedlejší příjem (Příjem!N6) a k tomu přičteme druhý vedlejší příjem (Příjem!N7). 

Ostatní příjmy sečteme pod jedno souhrnné označení Ostatní. Napíšeme rovná se překlikneme se opět na list Příjmy, kde sečteme celkové hodnoty u ostatních příjmů (Příjem!N8+…+N13). 

V řádku celkem sečteme příjmy (Přehled!B9). Pro kontrolu, že jsme na nic nezapomněli se podíváme, zda celková částka sedí s celkovou částkou na listu Příjem (Příjem!N14). Pokud se částky neshodují, tak jsme někde vynechali v součtu příjem.

Obrázek č.8 Tabulka celkových příjmů

Vedle ve sloupci chceme roční vyjádření převést na měsíční vyjádření. V roce máme dvanáct měsíců, takže stačí, když do první buňky (Přehled!C6) napíšeme rovná se a hodnotu vydělíme číslem 12 (Přehled!B6/12). Výpočet můžeme stáhnout dolů pro všechny ostatní buňky.

Plán osobních financí 1
Obrázek č.9 Tabulka celkových příjmů měsíčně

Tabulka celkových výdajů

Pod tabulkou příjmů máme tabulku celkových výdajů po jednotlivých kategoriích. Kategorie, které máme vypsané v tabulce odpovídají tabulkám, které jsme vyplňovali na listu Výdaj. Zajímají nás jen celkové roční výdaje po kategoriích, takže klikneme do první buňky k bydlení, napíšeme rovná se a znovu propojíme celkový součet u kategorie bydlení s celkovým součtem bydlení na listu Výdaj (Výdaj!N16). 

Potvrdíme a budeme pokračovat kategorií doprava. Znovu napíšeme rovná se, překlikneme se na list Výdaje, najdeme tabulku s výdaji za dopravu a propojíme buňky s celkovým součtem (Výdaj!N25). 

Takto bychom postupně propojili všechny výdajové kategorie.

Když máme hodnoty pro všechny kategorie doplněné, tak vyplníme řádek celkového součtu, což není nic jiného než součet všech výdajů (Přehled!B23). Opět bychom měli zkontrolovat, že se tato celková částka rovná částce celkových součtů na listu Výdaj (Výdaj!N3).

Kromě toho nás vedle ve sloupci opět zajímají výdaje v měsíčním vyjádření, což je opět jen vydělení roční hodnoty hodnotou 12 (Přehled!B14/12). Výpočet stáhneme pro ostatní buňky dolů.

Obrázek č.10 Tabulka celkových výdajů ročně a měsíčně včetně podmíněného formátování

Na původním přehledu jsme měli ještě u měsíčních nákladů pruhové podmíněné formátování, které srovnává výši nákladů. Označíme tedy sloupec s měsíčními hodnotami výdajů a na kartě Domů vybereme Podmíněné formátování. Klikneme na datové pruhy a vybereme Další. Vybereme barevnou výplň pro pruhy. Jelikož bude tato výplň překrývat text, tak bychom měli vybrat nějakou světlou barvu. Ještě změníme směr zobrazení pruhů, a vybereme zprava doleva. Potvrdíme a do tabulky se vložilo podmíněné formátování v podobě datových pruhů.   

Úspory

Zbývá doplnit celkové úspory (Přehled!B25). V ročním vyjádření se jedná o rozdíl mezi celkovými příjmy a celkovými výdaji (Přehled!B9-Přehled!B23). Měsíční úspory jsou následně celkové roční úspory vydělené číslem 12 (Přehled!B25/12). Dále zde máme pole pro doplnění počátečních úspor (Přehled!B28). Řekněme, že na počátku roku budeme mít na spořicím účtu třeba 50 000 Kč. Do tohoto pole tedy napíšeme částku, kterou máme naspořenou na počátku roku. 

To znamená, že na konci roku, pokud dodržíme plán, tak bychom měli mít naspořenou částku (Přehled!B30) ve výši počátek úspor plus roční celkové úspory (Přehled!B28+Přehled!B25). 

Pokud počátek úspor na začátku roku bude nula (Přehled!B28), tak se konec úspor bude rovnat celkovým ročním úsporám (Přehled!B25=Přehled!B30).

Plán osobních financí 3
Obrázek č.11 Celkové úspory na listu Přehled

Graf celkem utraceno z příjmu

Prvním grafem je pruhový graf, který zobrazuje procentuální podíl výdajů na celkových příjmech. Tedy, kolik procent z celkového příjmu se nám podle našeho plánu podaří uspořit na konci roku. Jedná se o jednoduchý skládaný pruhový graf. K jeho vytvoření budeme ale potřebovat pomocný výpočet. 

Plán osobních financí 4
Obrázek č.12 % podíl utraceno z celkových ročních příjmů

Pro pruhový graf potřebujeme spočítat, kolik procent utratíme z celkového příjmu. Výpočet je podíl celkových ročních výdajů vůči celkovým ročním příjmům (Přehled!B23/Přehled!B9). Výpočet potvrdíme a změníme formát na procenta s dvěma desetinnými místy. 

Teď víme, že z ročních příjmů padne na roční náklady celkem 88 %. K vytvoření grafu budeme ale potřebovat ještě jedno číslo a tím je rozdíl 1-spočítané procento (1-Přehled!F8). Dohromady tato dvě procenta dávají 100 %. 

To znamená, že momentálně máme naplánováno, že utratíme 88 % z celkových ročních příjmů a uspoříme tak necelých 12 %. 

Označíme tyto hodnoty a klikneme na kartu Vložení, Doporučené grafy a zde vybereme na kartě Všechny grafy pruhový graf a vybereme 100 % skládaný pruhový graf. Graf se vložil a teď je to jen o formátování grafu. 

Nad grafem máme záhlaví, které označuje, co graf zobrazuje, takže můžeme smazat název grafu, můžeme smazat i pomocné vodicí čáry a jelikož do grafu vložíme popisky, tak můžeme smazat i osy. Zůstane pouze pruhový graf. Klikneme na graf a vybereme Formát datové řady, kde vybereme Šířku mezery a uděláme graf silnější. Ještě upravíme barevnou výplň a ohraničení grafu. Aby bylo vidět, kolik % jsme vyčerpali ze 100 %, tak ohraničíme graf souvislou plnou čárou v nějaké šedé barvě. Následně označíme pouze část úspor a změníme u ní barevnou výplň na bílou. Tím zůstane graf orámovaný, ale barevně bude označená jen část, kterou jsme vyčerpali. Naposledy označíme graf, ale označíme pouze tuto barevnou část a vybereme Přidat popisky dat. Tím se do grafu vloží procenta, která znázorňují, kolik % z příjmů jsme vyčerpali.

Obrázek č.13 Pruhový graf celkem utraceno a uspořeno

Struktura průměrných výdajů

Dalším grafem je koláčový graf, který zobrazuje strukturu průměrných měsíčních výdajů po kategoriích. Na vytvoření grafu stačí označit tabulku, kterou jsme zde vytvořili. Zajímají nás průměrné měsíční náklady, takže označíme položky (Přehled!A14:A22), podržíme klávesu CTRL a označíme i sloupec s hodnotami (Přehled!C14:C22). 

Na kartě Vložení vybereme koláčový graf. Vložil se koláčový graf, který zobrazuje strukturu výdajů. Opět můžeme smazat název grafu, můžeme smazat i legendu. Klikneme na graf a vybereme Přidat popisku dat. Klikneme na popisky a vybereme Formát popisků dat, kde vybereme, že popisky mají zobrazovat procenta a název. Rovněž vybereme, že se mají vždy zobrazit za zakončením. 

Koláčový graf je hotový. 

Obrázek č.14 Koláčový graf struktury výdajů

Sloupcový graf příjmů, výdajů a úspor

Dalším grafem je jednoduchý sloupcový graf, který ukazuje roční příjmy, roční výdaje a úspory. Opět máme vše, co ke grafu potřebujeme spočítané, takže stačí označit pole, která se mají v grafu zobrazit. Označíme buňky s celkovými příjmy (Přehled!A9:B9), držíme CTRL, označíme celkové výdaje (Přehled!A23:B23), držíme CTRL a označíme celkové úspory (Přehled!A25:B25). 

Na kartě Vložení vybereme sloupcový graf. z grafu smažeme název, pomocné vodicí čáry, legendu a smažeme i osu y. Do grafu vložíme popisky dat a rozšíříme sloupce, aby graf vypadal lépe.

Plán osobních financí 7
Obrázek č. 15 Sloupcový graf celkových příjm, výdajů a úspor

Vývoj úspor

Posledním grafem je vývoj celkových úspor po měsících. K tomu potřebujeme dopočítat měsíční úspory. Pomůžeme si pomocným výpočtem. 

Na listu Příjem si pod řádek celkových měsíčních příjmů napíšeme Úspory (Příjem!A16), kde od sebe odečteme měsíční příjmy a náklady. Napíšeme rovná se označíme lednové příjmy (Příjem!B14) a od těch odečteme lednové celkové náklady z listu Výdaj (Výdaj!B3). Vzorec protáhneme pro všechny měsíce. Teď máme měsíční úspory, nesmíme ale zapomenout na to, že v prvním měsíci máme ještě naspořené peníze, takže vytvoříme ještě druhý řádek Úspory celkem (Příjem!A17), kde nasčítáme úspory. Leden bude roven počátečnímu stavu na spořicím účtu (Přehled!B28) plus lednové úspory (Příjem!B16). Únorové úspory budou leden + úspory v únoru (Příjem!B17+Příjem!C16). Teď můžeme vzorec protáhnout pro všechny měsíce. Konečná částka úspor se musí rovnat částce, kterou máme uvedenou na listu Přehled (Příjem!M17=Přehled!B30).

Plán osobních financí 8
Obrázek č.16 Vývoj celkových úspor - dopočítání měsíčních úspor

Teď můžeme označit měsíce v záhlaví (B3:M3), podržet klávesu CTRL, označit řádek nasčítaných úspor (B17:M17) a na kartě Vložení vybrat plošný graf. Graf označíme, zmáčkneme CTRL+X pro vyjmutí a vložíme ho na list Přehled. Odstraníme název grafu, pomocné vodicí čáry a graf vývoje úspor je hotový. 

Plán osobních financí 9
Obrázek č.17 Plošný graf vývoje celkových úspor

Gratuluji, váš osobní plán rodinných financí je hotový. 

MOHLO BY VÁS ZAJÍMAT

Dynamický prodejní report v Excelu | Microsoft 365

V dnešním videu se podíváme na to, jak pomocí dynamických funkcí v Excelu vytvořit plně dynamický prodejní report. Dynamické funkce, které budeme dnes pro vytvoření prodejního reportu

Začínáme s Power BI | Excel a Power BI

V dnešním videu se podíváme na základy práce v Power BI. Power BI je nástroj od společnosti Microsoft, který slouží k interaktivní vizualizaci a analýze dat. Specifikem Power

2 komentáře

  1. 👍💖
    🎉 Všechno nejlepší do Nového roku 2023 s přáním především pevné zdraví, osobní a pracovní úspěchy
    a pokračování v takto vysoké kvalitě akademie excelu.

    Přeje
    Oldřich Svoboda

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

AKADEMIE EXCELU

EXCELOVÝ SOUBOR
KE STAŽENÍ