Bezpłatny fragment - Excel Analiza „What If” i Analiza warunkowa

1. Wstęp

Niniejsza publikacja jest kolejną z serii popularyzującej wykorzystanie Excela oraz danych w plikach JPK w codziennej pracy księgowego. Podobnie jak w innych moich publikacjach, także i do tej dołączony jest plik Excela z narzędziem, które można wykorzystać w pracy w każdej firmie. Narzędzie to, zostało dokładnie tutaj przedstawione i opisane. Zarówno w załączonym do niniejszej publikacji pliku Excela, jak wszystkich innych plikach dołączonych do moich publikacji, wszystkie użyte w przedstawionym narzędziu formuły Excela są odkryte, by mogły być elementem poszerzania wiedzy o zastosowaniach arkusza kalkulacyjnego w praktyce. Formuły te można modyfikować i analizować, poszerzając swoją wiedzę o Excelu. Można też inspirować się prezentowanym narzędziem we własnych rozwiązaniach.

Tym razem zajęłam się analizą What If, która staje się coraz bardziej popularna i coraz częściej jest wykorzystywana w pracy księgowego, analityka, czy zarządu. Jest to typ analizy dynamicznej, tzn. takiej gdzie zmieniane są parametry wyliczeń, dając natychmiastowe wyniki, oraz odpowiedź na pytanie: co jeśli?

Dodatkowo, na potrzeby analizy What If, omówiłam funkcjonalność Excela: „Analiza warunkowa”, która uzupełnia prezentowane przeze mnie zagadnienie, wskazując na jej praktyczne aspekty.

Przesłaniem wszystkich moich książek jest dostarczenie księgowym nie tylko tanich narzędzi analitycznych i sprawozdawczych, ale także inspirowanie moich czytelników do poznawania i stosowania Excela w ich pracy. Analiza What If, z reguły jest oferowana w ramach drogich funkcjonalności Business Intelligence, na które niestety nie stać wiele firm. Dzięki tej publikacji, każdy może poznać na czym polega analiza What If, pozyskać gotowe do użycia w swojej firmie narzędzie, a także zainspirować się do projektowania własnych, podobnych rozwiązań, dedykowanych do potrzeb konkretnej firmy. Co najważniejsze — można skorzystać z tego rozwiązania nie ponosząc wielkich wydatków.

Przedstawiony w niniejszej publikacji raport jest zasilany danymi z e-sprawozdania finansowego.

2. Czym jest analiza What If

Oczywistym jest, że w zarządzaniu przedsiębiorstwem dużą pomocą są różnorodne analizy, które są wyliczane w oparciu o tzw. dane ex post, czyli dane zbierane z dalszej lub bliższej przeszłości. Mogą to być różnorodne raporty, zestawienia czy sprawozdania.

Jednak by podjąć trafną decyzję zarządczą potrzebne są czasami odpowiedzi na pytania typu „co jeśli”. Przykładami tego typu analiz (pytań) mogą być:

— Jaki będzie przychód jeśli zostanie zmniejszona cena jednostkowa i skorygowany koszt?

— Jaki należy zaangażować koszt, aby nie przekroczyć przyjętego pułapu podatku dochodowego?

— Jak wpłynie na przychód zwiększenie wynagrodzenia i zmniejszenie zakupu usług obcych i w jakich granicach kwot tych kosztów należy się poruszać, aby nie wygenerować straty?

— Jaki będzie wynik finansowy, gdy przychód i koszt wzrosną o tyle samo, ile wzrosły rok do roku w poprzednich latach?

Takich pytań można mnożyć wiele, a odpowiedzi na nie, na pewno mogą ułatwić decyzje zarządcze. Zarząd nie chce być zaskakiwany wynikiem finansowym, a chciałby określać granice dla kosztów i przychodów, w jakich jednostka powinna się poruszać.

Analiza What If pozwala w czasie rzeczywistym uzyskiwać odpowiedzi na ważne pytania związane z prowadzeniem firmy, a dotyczące finansów podmiotu.

Podstawą analizy typu What If są parametry, które można zmieniać płynnie zgodnie z prowadzoną analizą. Chcąc zaprojektować potrzebny raport, należy ustalić jakie wartości nas interesują i jakie czynniki na nie mogą wpływać. W dalszej części publikacji przedstawiony jest przykład analizy, gdzie w zależności od kosztów ogólnych, bądź szczegółowych, można kontrolować kwotę podatku dochodowego, jaki będzie do zapłacenia. Są to istotne kwestie, ponieważ pozwalają przewidzieć skutki niektórych decyzji finansowych. Pamiętajmy, że rozważnie zaangażowany koszt w przedsiębiorstwo w bliższej lub dalszej przyszłości, powinien przynieść zysk i rozwój firmy, więc czasami lepiej więcej zainwestować w firmę niż płacić wyższe podatki.

Przystępując do projektowania analizy What If, przede wszystkim trzeba zastanowić się, jaki obszar finansów będzie nas interesował. Oczywistym jest, że najważniejszy dla przedsiębiorcy jest zysk na przedsięwzięciu, ale na ten zysk wpływa wiele czynników. Do tych czynników można zaliczyć np. koszty, jakie trzeba ponieść, aby w ogóle móc zrealizować dane przedsięwzięcie. Nie jest dobrze, gdy nasze wyliczenia są oderwane od rzeczywistości biznesowej i najlepiej analizę prowadzić na danych ex post, z otwartymi do zmian niektórymi czynnikami.

Na przykład punktem wyjścia będzie sprawozdanie finansowe z poprzedniego roku. Analizując koszty, interesować nas będą na pewno koszty stałe, czyli te, którymi trudno jest manipulować, (chociaż czasami i one mogą być poddane redukcji) Przyjmując koszty stałe, jako czynnik niezmienialny, należy otworzyć do zmian koszty zmienne. Kolejnym elementem analizy jest cel, jakim jest osiągnięcie konkretnego zysku na przedsięwzięciu. Mając tak zbudowany punkt wyjścia (PW), można rozpocząć analizę:

Koszty stałe (zdefiniowane) + koszty zmienne + wynik finansowy = przychód (cel).

Kalibrując koszty zmienne w kontekście zaplanowanego celu, jakim jest określony przychód można zaplanować poszczególne koszty zmienne i w ten sposób przygotować plan (budżet) na kolejny okres działalności.

Dla ułatwienia prowadzenia analizy What If, można przygotować różne scenariusze badań tak, aby ostatecznie móc wybrać najlepszy wariant analizy (najlepszy scenariusz). W tym miejscu pojawia się pytanie: a jak ocenić, który wariant analizy będący wynikiem określonego scenariusza będzie najlepszy?

Odpowiedź powinna wynikać z konkretnej sytuacji w przedsiębiorstwie, specyfiki branży i szczegółów dotyczących realizacji projektów.

Ten najlepszy scenariusz można zapisać w formie planu, jako celu do wykonania w następnym okresie, którym może być rok, kwartał, czy półrocze.

3. Przykład i omówienie narzędzia w Excelu

Jak to zostało napisane we wstępie, do niniejszej publikacji dołączone jest narzędzie zaprojektowane w Excelu, za pomocą którego można prowadzić analizę What If dla obszaru finansowo — księgowego (rys. 1).

Jak to zostało już wcześniej wspomniane, narzędzie to, jest zasilane danymi z e-sprawozdania finansowego, a dokładnie bilansem oraz rachunkiem zysków i start w wersji porównawczej. Za pomocą prezentowanego narzędzia, można prowadzić analizę What If oraz analizę wskaźnikową. Na rys. 2 przedstawione jest środowisko analizy What If, które jest opisane w dalszej części, w podrozdziale 3.3.

Prezentowane narzędzie w Excelu, składa się z sześciu arkuszy:

— Start,

— Dashboard,

— Wskaźniki,

— Bilans,

— RZiS,

— XML.

Jak to już zostało wcześniej wspomniane, wyliczenia realizowane są na podstawie danych z bilansu i rachunku zysków i strat w wersji porównawczej, które są pobierane z e-sprawozdania finansowego. Co do zasady, e-sprawozdanie finansowe jest generowane po zakończeniu roku obrotowego w ramach rozliczenia przez jednostkę roku obrotowego (podatkowego) do urzędów (KRS, KAS). Jednak nic nie stoi na przeszkodzie, aby skorzystać z funkcjonalności generowania e-sprawozdania finansowego, jaka jest zaimplementowana w programach księgowych, do zasilania raportu danymi na przykład co miesiąc, co kwartał, czy raz na pół roku.

3.1. Źródło danych

Dołączony do niniejszej publikacji plik Excela z analizą What If, jest zasilony przykładowymi danymi, które można (i należy) zastąpić własnymi danymi. Aby wczytać do pliku własne dane, najpierw trzeba przygotować e-sprawozdanie finansowe, w którym jest rachunek zysków i strat w wersji porównawczej. Mając już przygotowany swój plik z e-sprawozdaniem finansowym, można go wczytać do raportu. Wykonujemy to na arkuszu XML. Przechodząc na arkusz XML, trzeba ustawić się w pierwszym wierszu arkusza, w którym są widoczne dane. Następnie, trzeba otworzyć menu podręczne klikając prawym przyciskiem myszy. Z wyświetlonego menu, należy wybrać polecenie „XML”, a następnie „Importuj” (rys. 3).

W wyniku wybrania polecenia „Importuj”, zostanie otworzone okno widoczne na rys. 4, w którym trzeba wskazać plik do wczytania ze sprawozdaniem finansowym, które wcześniej zostało wygenerowane z programu księgowego i zapisane na dysku.

Po wczytaniu danych, zostaną one natychmiast umieszczone w odpowiednich komórkach bilansu, rachunku zysków i strat i pozostałych raportach. Można je przeglądać na arkuszach Bilans i RZiS_P (rys. 5 i 6). Na arkuszach tych, są wprowadzone także proste walidacje (sprawdzenia), które kontrolują czy sumowania poszczególnych wierszy są poprawne. Walidacja ta, jest w kolumnach J i K dla bilansu oraz w kolumnach I oraz J dla RZiS_P.

Oczywiście, jeśli wczytywane jest e-sprawozdanie finansowe, które było wcześniej przesłane do KRS lub KAS, to powinno ono być poprawne, ale jeśli wczytywane jest e-sprawozdanie wygenerowane na potrzeby analizy What If zamieszczonej w niniejszym arkuszu, wówczas dane warto sprawdzić, więc walidacja może być przydatna.

3.2. Wskaźniki

Na arkuszu Wskaźniki została umieszczona tabela, w której jest 29 najpopularniejszych wskaźników (rys. 7). Po prawej stronie tabeli jest możliwość wybrania jednego z 29 wskaźników z tabeli. Po wyborze wskaźnika (komórka C4), poniżej pojawia się wykres z graficzną prezentacją wartości wskaźnika z końca i początku roku (BZ i BO). Jednocześnie w komórkach H4 i I4, wyświetlane są wartości wskaźników (BO i BZ).

Na arkuszu wskaźników umieszczony jest także wzór, wg którego zostały wyliczone ich wartości, a także pożądane wartości prezentowanego wskaźnika.

Wskaźniki wyliczone są na podstawie wartości bilansu i rachunku zysków i strat. Dla wskaźników, które wyliczane są z bilansu, podane są wartości dla BO i BZ, dla tych wskaźników dla których do wyliczenia potrzebne są także dane z rachunku zysków i strat, wyliczone są tylko wartości BZ.

Analizując wartości wskaźników, można poznać ogólną kondycję analizowanej firmy.

3.3. Dashboard

Główny arkusz prezentowanego narzędzia w Excelu, to „Dashboard” (rys. 8), na którym przeprowadzane są obliczenia. W lewym górnym rogu umieszczone są dwa pola (C3 i D3), w których należy wskazać wartości (parametry):

— Stawka podatku — do wyboru jest 19% lub 9%,

— Przyrost — jednostka przyrostu dla symulowanej zmiany. Można tutaj wybrać: 100;500;1 000;1 500;2 000;2 500;3 000;5 000;10 000;50 000;100 000;200 000;300 000;500 000;1 000 000.

Ad. 1. Wybrana stawka podatku, jest wykorzystana do wyliczenia podatku w komórce G11 „Podatek”,