Jednym z zadań analityka finansowego czy też kontrolera finansowego jest udział w procesie przygotowania budżetu firmy na przyszły rok bądź nawet koordynacja tego procesu. Plan taki powstaje we współpracy z różnymi działami przedsiębiorstwa, jak na przykład z działem sprzedaży, produkcji, zasobów ludzkich itp. Jest to proces długotrwały i skomplikowany ponieważ procesowi planowania podlega większość z obszarów działalności firmy. Wynikiem takiej wewnętrznej kooperacji jest plan działania firmy na przyszły okres (przeważnie roku) wyznaczający cele w wielu aspektach i dziedzinach funkcjonowania jednostki. W dzisiejszym materiale rozwiążę problem analityka finansowego pracującego w firmie dystrybucyjnej obsługującej około 80 klientów , której asortyment towarów liczy w przybliżeniu 120 różnych pozycji. Firma, w której pracuje nasz analityk do zakupionych towarów dolicza 15% ich wartości i to stanowi ich cenę odsprzedaży. Ponieważ na rynku, na którym działa nasz podmiot jest bardzo duża konkurencja firma musi czymś zachęcić obecnych klientów jaki i zdobyć nowych. Takim sposobem jest system rocznych bonusów od zakupów dokonanych przez poszczególnych klientów, wielkość bonusów określana jest przez dwa parametry:
1. Ilościowa granica przyznania bonusu – określa roczną wielkość sprzedaży (ilość) każdego z towarów, po przekroczeniu której przysługuje bonus
2. % bonusu – procent liczony od wielkości zakupu danego towaru po przekroczeniu wyznaczonej granicy sprzedaży
W budżecie roczne bonusy pomniejszają wartość planowanej sprzedaży przez co wpływają na pogorszenie wyniku firmy (marży). Zadaniem naszego analityka jest ustalenie kilku wariantów bonusów (różnych granic ilości i odpowiadającym im % bonusów), aby marża utrzymywała się w określonym przedziale, a klient mógł wybrać z pośród kilku wariantów przyznania bonusu. Czyli doprowadzenie do sytuacji „wilk syty i owca cała”.
Model sprzedaży i wyników budżetu
W naszym przykładzie analityk zbudował uproszczony model do wyliczenia wielkości i warunków bonusów. Składa się on z dwóch arkuszy: sprzedaż – w którym znajdują się plany sprzedażowe w przyszłym roku oraz wyniki, w którym podawane są warunki wyliczenia bonusów oraz bardzo uproszczony rachunek wyników pokazujący wynik na sprzedaży netto i brutto.
Na widoku 1 został przedstawiony arkusz sprzedaż, który zawiera roczne plany sprzedaży poszczególnych towarów do poszczególnych klientów. Zawiera on kolumny: Klient, Towar, Cena zakupu (cena zakupu 1 szt. danego towaru), Koszt zakupu (koszt zakupu liczony jako iloczyn ilości i ceny zakupu poszczególnego towaru), Ilość, Cena sprzedaży (cena zakupu +15%), Wartość sprzedaży (iloczyn ilości i ceny sprzedaży), Bonus (bonus od wartości sprzedaży, liczony wg warunków z arkusza wyniki), Wartość netto (liczona Wartość sprzedaży – Koszt zakupu – Bonus).
Widok 1 Arkusz sprzedaż
Arkusz wyniki (Widok 2) podzielony został na dwie części. W pierwszej części WARUNKI BONUSÓW określamy granicę przyznania bonusu dla każdego towaru oraz jego % liczony od wartości sprzedaży. W zakładce sprzedaż wielkość Bonus liczona jest przy użyciu funkcji JEŻELI (Ilość >bonus granica [ilość]; Wartość sprzedaży * bonus%; 0). W drugiej części arkusza zatytułowanej MODEL znajdują się poszczególne elementy wyniku firmy, przy założeniu 0% bonusów.
Wartość sprzedaży kształtująca się na poziomie 50 mln zł , koszty zakupu w wysokości 43,5 mln zł¸ rabat równy 0 , wynik na sprzedaży brutto (wartość sprzedaży – koszty zakupu – bonus) równy 6,5 mln zł, marża I (wynik na sprzedaży brutto dzielony przez wartość sprzedaży) 13,04%, dodatkowo podane są zaplanowane całoroczne koszty sprzedaży i koszty zarządu w łącznej wysokości około 2,2 mln zł. Przy takich poziomach poszczególnych wartości sprzedaży kosztów wynik na sprzedaży netto (wynik na sprzedaży brutto – koszty sprzedaży i zarządu) kształtuje się na poziomie 4,3 mln zł, a marża II (wynik na sprzedaży netto podzielony przez wartość sprzedaży) na poziomie 8,59%. Oczywiście suma poszczególnych wielkości (wartość sprzedaży, koszty zakupy, bonus) stanowią sumę pozycji z arkusza sprzedaż.
Widok 2 Arkusz wyniki
Model jest dynamiczny, przykładowo jako granicę bonusów wpiszemy 200, a za % bonusu wpiszemy 6%. Oznacza to, że jeżeli w przyszłym roku dany klient zakupi 200 szt. określonego towaru to na koniec roku dostanie za niego bonus w wysokości 6% wartości jego sprzedaży. Przy takich warunkach bonusowych dla każdego z klientów suma bonusów wyniesie 2,2 mln zł. (Widok 3) co obniży marże II do poziomu 4,22%.
Widok 3 Wynik przy przykładowych warunkach bonusów
Zadanie analityka
Zadaniem naszego analityka jest wyznaczenie takich wariantów kryteriów bonusów (granicy ilościowej i % bonusu), aby marża II kształtowała się na poziomie pomiędzy 5% -6%. Będzie to wynik w granicach 2,5 – 3 mln zł, klient będzie miał do wyboru kilka wariantów bonusów, a firma i tak wynik w wyznaczonym satysfakcjonującym przedziale. Założeniem jest przedział % bonusu od 3% do 10% co 0,5% (15 wartości) oraz granica ilościowa od 100 do 500 co 50 (9 wartości). Z tych przyjętych przedziałów wychodzi 9*15=135 kombinacji wysokości marży II, które należy przeanalizować. Oczywiście nasz analityk mógłby każdy z wariantów podstawiać do modelu i notować wynik, ale powtarzanie tej czynności 135 razy jest męczące, pracochłonne i nie eliminuję możliwości popełnienia błędu. Jest jednak ratunek dla naszego analityka – jest nim narzędzie
Tabela danych jako narzędzie analizy symulacji
Tabela danych jest jednym z narzędzi analizy symulacji. Jest to analiza „co, jeśli?” polegająca na zmienianiu wartości w komórkach modelu i obserwacji, jak te zmiany wpłyną na wynik modelu. Tabela danych pomaga badać zestaw możliwych wyników naszego modelu przy jego zmiennych parametrach wejściowych (maksymalnie dwóch). Zaletą tego narzędzia jest fakt, że wszystkie wyniki przedstawione zostają w jednej tabeli w jednym arkuszu, co pozwala na szybką ocenę możliwości badanego założenia.
Lokalizacja narzędzia to wstążka Dane -> Analiza symulacji -> Tabela danych. Po wybraniu pokazuję się nam okienko dialogowe Tabela danych, które zawiera dwa pola (Widok 4).
Widok 4 Okienko dialogowe Tabela danych
Wierszowa komórka wejściowa - jest odpowiedzialna za wartości w tabeli znajdujące się w pierwszym wierszu. W niej wskazujemy gdzie w naszym modelu dane umieszczone w pierwszym wierszu mają być podstawiane.
Kolumnowa komórka wejściowa - jest odpowiedzialna za wartości w tabeli znajdujące się w pierwszej kolumnie. W niej wskazujemy gdzie w naszym modelu dane umieszczone w pierwszej kolumnie mają być podstawiane.
Nasza tabela danych będzie zależna od dwóch parametrów: % bonusu i jego granicy ilościowej. Tabele danych budujemy w arkuszu wyniki (tabela musi być w tym samym arkuszu co model). Pierwszym krokiem do stworzenia tabeli danych jest określenie jej układu i tak granicę ilościową umieścimy w wierszy 4 zaczynając od komórki G4 (przedział od 100 do 500 co 50. Natomiast % bonusu umieścimy w kolumnie F zaczynając od komórki F5 (przedział od 3% do 10% co 0,5%). W ten sposób lewa górna komórka (F4) zostanie pusta (Widok 5).
Widok 5 Rozmieszczenie wartości parametrów modelu
W komórce F4 wstawiamy formułę wyliczającą marżę II (wynik netto na sprzedaży/wartość sprzedaży) – jest to warunek konieczny, aby nasza tabela danych została wyliczona. Tabela danych potrzebuję formuły wyliczającej badany przez nas wynik (Widok 6).
Widok 6 Formuła wyliczająca wartość marży II
Następnie zaznaczamy cała naszą zbudowaną macierz, łącznie z wartościami i formułą (Widok 7).
Widok 7 Zaznaczenie całej tabel
Przechodzimy do Dane -> Analiza symulacji -> Tabela danych i pojawi się nam okienko dialogowe Tabela danych (Widok 8).
Widok 8 Okno dialogowe Tabela danych
1. W wierszowej komórce wejściowej – wskazujemy komórkę w naszym modelu gdzie mają być podstawiane wartości z wiersza nr 4 (ilościowa granica bonusów). Jest to komórka C3.
2. W kolumnowej komórce wejściowej – wskazujemy komórkę w naszym modelu gdzie mają być podstawiane wartości z kolumny F (i% bonusu). Jest to komórka C4.
Klikamy OK i cała tabela wypełni się nam wartościami marży II dla parametrów z 4 wiersza i kolumny F (Widok 9). Gdzie określona wartość marży II jest rezultatem podstawienia do modelu granicy ilościowej znajdującej się powyżej w pionie wartości z wiersza 4 i % bonusu znajdującego się w poziomie po lewej stronie w kolumnie F. W jednej macierzy mamy zawarte wyniki dla 135 kombinacji dwóch zmiennych naszego modelu.
Widok 9 Wynikowa tabela danych
Dodatkowo możemy w naszej zbudowanej tabeli zastosować formatowanie warunkowe Narzędzia główne -> Formatowanie warunkowe -> Reguły wyróżniania komórek -> Między… i zastosować formatowanie warunkowe dla wartości pomiędzy 5% i 6% (Widok 10). Umożliwi to wyróżnienie wyników nas interesujących, czyli wszystkich marż II znajdujących się w tym przedziale.
Widok 10 Zastosowanie formatowania warunkowego
Jak widzimy na widoku 11 istnieje możliwość 23 kombinacji dwóch kryteriów, na przykład dla granicy ilościowej równej 150 i % bonusu równego 4%, marża II wyniesie 5,4%.
Pod macierzą z marzą II, w taki sam szybki sposób możemy utworzyć macierz dla wyniku na sprzedaży netto, tylko w lewym górnym rogu zamiast formuły wyliczającej marżę II wstawiamy formułę wyliczająca wynik na sprzedaży netto. Następnie zastosować formatowanie warunkowe wartości pomiędzy 2,5 mln i 3,0 mln i otrzymamy odzwierciedlenie % wartości marży w wartościach złotówkowych wyniku netto na sprzedaży (Widok 12)
W ten sposób nasz analityk szybko i bezbłędnie wykonał swoje zadanie, a przy okazji wyniki zostały przedstawione w sposób przejrzysty i czytelny. Teraz klientom firmy mogą zostać przedstawione 23 warianty bonusów do wyboru, z których każdy przy takim poziomie planowej sprzedaży firmie przyniesie wynik netto na sprzedaży w granicach 2,5 mln zł do 3,0 mln zł (marża II 5,0% do 6,0%). Często z poszczególnymi bardziej znaczącymi klientami prowadzone są osobne negocjacje dotyczące bonusów. W takim wypadku można zastosować wyliczone macierze bądź skonstruować identyczny model wraz z tabelami danych tylko dla danego klienta. Jak widać jest to doskonałe narzędzie do przeprowadzania symulacji wyniku modelu ze względu na zmiany maksymalnie dwóch jego parametrów. Dostarcza ono wiele istotnych i niezbędnych informacji w jasnym i prostym do odczytu układzie.
Porady udzielił:
Wojciech Próchnicki
Ekspert i trener firmy Tax Consilium Sp z o.o.
Wybitny finansista, doświadczony praktyk w zakresie wykorzystania Excela i VBA w biznesie,specjalista ds. controllingu i raportowania oraz analityk finansowy