"Controlling i Rachunkowość Zarządcza", sierpień 2007

Problem

W jaki sposób do modelowania wyników kalkulacji można wykorzystywać funkcjonalność "TABELI" w programie Excel?. Proszę o praktyczny przykład takiego wykorzystania.

Rozwiązanie

W pracy nad projektami, wyceną ich efektywności, analizą rentowności, kalkulacje sprowadzają się do wyliczenia jednej podstawowej wartości i ewentualnie innych dodatkowych. Mogą to być np.: marża, NPV, EVA, EBITDA itd. Wartość każdego wskaźnika wynika z przyjętych założeń i zbudowanego modelu zależności między poszczególnymi założeniami. Po skalkulowaniu docelowych wskaźników decydenci chcą znać wiele wariantów kalkulacji, zmieniając poszczególne założenia.
Bardzo przydatną w tym celu funkcjonalnością jest "TABELA" (w menu Dane). Umożliwia ona elastyczną prezentację skalkulowanej wielkości w zależności od dwóch założeń, których zmienna wielkość jest prezentowana w wierszu i w kolumnie.

Dla przykładu przyjrzyjmy się prostemu modelowi kalkulacji kosztów finansowania inwestycji kredytem bankowym. Celem nadrzędnym jest skalkulowanie wielkości wydatków związanych z finansowaniem inwestycji na poziomie 150 tys. PLN. Ta wielkość jako założenie główne (wpisywane do arkusza jako wartość) została podświetlona na żółto. Podobnie zostały zaprezentowane wszystkie wielkości, które są założeniami , a nie wynikiem jakichkolwiek formuł.

Zadanie: zbadanie zmienności ogólnej wartości wydatków na spłatę raty kapitałowej i odsetek w zależności od oprocentowania kredytu i od udziału środków własnych w inwestycji (w przykładzie jest to wartość 63 750 PLN).

TWORZENIE DYNAMICZNIE ZMIENIAJĄCEJ SIĘ TABELI

KROK 1
W lewym górnym rogu obszaru, w którym będzie umieszczona tabela (w przykładzie E23), wskazujemy komórkę, w której jest skalkulowana wartość, której zmianę chcemy obserwować w zależności od przyjętych założeń (w przykładzie I19 - suma wydatków z 4 kwartałów)

KROK 2
W komórkach na prawo od wstawionej formuły umieszczane są wartości jednego z dwóch parametrów, które wpływają na wartość wydatków kredytowych (oprocentowanie kredytu).
W komórkach w dół od wstawionej formuły umieszczane są wartości drugiego parametru, wpływającego na wartość wydatków kredytowych (udział środków własnych w inwestycji).

KROK 3
Zaznaczamy obszar, obejmujący dotychczas wstawione parametry (w przykładzie jest to obszar E23:I27)

KROK 4
Z menu głównego wybieramy Dane, Tabela

KROK 5
Pojawia się okno dialogowe Tabela.
W polu "Wierszowa komórka wejściowa" okna dialogowego wskazujemy poziomy parametr wpływający na wielkość wydatków kredytowych (w przykładzie jest to oprocentowanie kredytu w stosunku rocznym (C10).
W polu "Kolumnowa komórka wejściowa" okna dialogowego wskazujemy pionowy parametr wpływający na wielkość wydatków kredytowych (w przykładzie są to środki własne (C8).

Zatwierdzamy przyciskiem OK.
Na zaznaczonym obszarze ukazują się wartości wydatków kredytowych zmieniające się w zależności od zmiany obu wskazanych parametrów

Na przecięciu parametrów o wartościach identycznych jak w założeniach do przykładu uzyskujemy dokładnie wartość, jaka wynika z kalkulacji. W ten sposób sprawdzamy poprawność funkcjonowania Tabeli.

Oba parametry mogą być ręcznie zmieniane w zależności od potrzeb modelowania poszczególnych wariantów.

Narzędzie ułatwia oglądanie różnych wariantów kalkulacji na jednym arkuszu w bardzo przejrzystej formie.