V Excelu se pro tuto funkci citlivostní analýzy používá výraz „Tabulka dat“. Podle toho, zda měníme jednu nebo dvě proměnné rozlišuje „Tabulku dat s jednou proměnnou“ a „Tabulku dat se dvěma proměnnými“. Tabulka dat s jednou proměnnou umožňuje analyzovat více než jeden vzorec (oproti tabulce dat se dvěma proměnnými, kde lze použít pouze jeden vzorec).
Funkce Tabelovat s jednou proměnnou
Uvedenou funkci naleznete na kartě Data – sekce Prognóze, příkaz Citlivostní analýza – Tabulka dat:
Tabulky dat s jednou proměnnou se vytváří tak, aby vstupní hodnoty byly zapsány buď do sloupce směrem dolů (sloupcová orientace), nebo do řádku (řádková orientace). Vzorce použité v tabulce dat s jednou proměnnou se musí vždy vztahovat ke vstupní buňce.
Vytváříme model tabulky, kde máme v prvním sloupci uvedeny výnosy a do dalších chceme pomocí citlivostní analýzy dopočítat výši hrubého zisku, provizí a odvodů. Z těchto dílčích výpočtů potom nakonec dopočítáme zisk (sloupec E).
Postup vytvoření výchozí tabulky pro analýzu
1. Do tabulky (prvního sloupce nebo řádku) zapíšeme vstupní hodnoty použité pro výpočet.
Seznam hodnot, které chcete nahradit ve vstupní buňce, napíšeme buď do jednoho sloupce ve směru dolů, nebo do jednoho řádku ve směru doprava.
- V našem případě zapíšeme do sloupce A výchozí hodnoty výnosů:
2. Konstanty použité pro výpočty vložíme do buněk mimo tabulku.
- V našem případě zapíšeme do buněk všechny údaje nutné pro výpočet prvních vzorců:
C2: výnosy z tržeb - buňka A9 tabulky)
C3: fixní náklady - pevná hodnoty 150.000,- Kč
C4: pevné odvody - pevná hodnoty 50.000,- Kč
C5: výše provize - pevná sazba 15% (z hrubého zisku)
C6: proměnné odvody - pevná sazba 20% (z hrubého zisku)
3. Nyní do tabulky vložíme vzorové vzorce pro použití funkce.
Jestliže jsou vstupní hodnoty zapsány do sloupce, zadejte vzorec do první buňky vpravo od sloupce hodnot. Všechny další vzorce pak napište ve směru doprava.
Jestliže jsou vstupní hodnoty zapsány do řádku, zadejte vzorec do první buňky pod řádkem hodnot. Všechny další vzorce pak napište pod první vzorec.
- V našem případě zapíšeme do buněk řádku 9 výchozí výpočty pro tento řádek s odkazy na tabulku konstant (C2:C6):
B9 - hrubý zisk : =C2-C3
C9 - provize : =(C2-C3)*C5
D9: - odvody : =C4+(C2-C3)*C6
E9 - zisk : =(C2-C3)*(1-C5-C6)-C4 /lze zapsat i přímo výpočet z tabulky: B9-C9-D9/
Spuštění tabelační funkce citlivostní analýzy
1. Označíme oblast buněk obsahující vzorce a hodnoty, které chcete nahradit. (A9:E19)
2. Pomocí příkazu „Data/Tabulka...“ zobrazíme následující dialogové okno:
Do této tabulky zapisujeme podle následujícího pravidla:
Pokud má tabulka dat sloupcovou orientaci, napište odkaz na vstupní buňku do textového pole „Vstupní buňka sloupce“ .
Pokud má tabulka dat řádkovou orientaci, napište odkaz na vstupní buňku do textového pole „Vstupní buňka řádku“
3. Do příslušného pole zapíšeme odkaz - v našem případě má tabulka sloupcovou orientaci, zapíšeme tedy do „Vstupní buňky sloupce“ odkaz : $C$2
4. Potvrdíme OK a můžeme si prohlédnout výsledek:
Pro kontrolu naleznete uvedenou hotovou tabulku na listu „výsledek“. Všimněte si vzorce/funkce, která je uvedena ve všech vložených buňkách: =TABELOVAT(;C2)
V případě řádkové orientace proměnné by byla výsledná funkce: =TABELOVAT(C2;)
V příštím článku se zaměříme na funkci Tabelovat se dvěma proměnnými.