Budżet firmy handlowej – ustalenie warunków bonusów dla klientów. Zastosowanie narzędzia Tabela danych.
Tagi: budżet MS Excel biznes rozliczanie rachunkowość kurs wiedza     Kategoria: Wpisy ogólne

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ścii 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.

Nasz 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

 

Porady udzielił:

Wojciech Próchnicki

Wybitny finansista, doświadczony praktyk w zakresie wykorzystania Excela i VBA w biznesie,specjalista ds. controllingu i raportowania oraz analityk finansowy

 

 

 

 

 

 

 

 

 

 

 

 

14.00

Przechodzimy do Dane Þ Analiza symulacji Þ Tabela danych i pojawi się nam okienko dialogowe Tabela danych (Widok 8).

 

Widok 8 Okno dialogowe Tabela danych

Normal 0 21 false false false PL X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:Standardowy; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Times New Roman”,”serif”;}

Z naszej wiedzy korzystają

lewo
prawo

NEWSLETTER TAX CONSILIUM

Dołącz do naszej sieci informacyjnej.
Otrzymasz comiesięczne podsumowania najlepszych artykułów, darmowe materiały, informacje o zniżkach i kursach.