Excel Analiza „What If” i Analiza warunkowa - ebook

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).

RYSUNEK 1. Analiza What If w Excelu
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.

RYSUNEK 2. Dashboard Analiza What If
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).

RYSUNEK 3. Otwieranie podręcznego menu na arkuszu XML
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.

RYSUNEK 4. Wskazanie na plik z e-spr do wczytania
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.

RYSUNEK 5. Bilans

RYSUNEK 6. Rachunek zysków i strat wersja porównawcza
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.

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

3.3. Dashboard