Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00112 005898 13638049 na godz. na dobę w sumie
Kontroling sprzedażowy w Excelu - ebook/pdf
Kontroling sprzedażowy w Excelu - ebook/pdf
Autor: Liczba stron: 367
Wydawca: Wiedza i Praktyka Język publikacji: polski
ISBN: 978-83-269-3098-0 Data wydania:
Lektor:
Kategoria: ebooki >> poradniki >> zdrowie
Porównaj ceny (książka, ebook (-8%), audiobook).

Osoba zajmująca się w organizacji kontrolingiem musi być wyposażona w odpowiednie narzędzia (system ERP czy oprogramowanie BI), lecz często wszelkie analizy i raporty wykonuje w arkuszu kalkulacyjnym MS Excel. Wykorzystanie Excela w kontrolingu sprzedażowym daje możliwość szybkiego wykonania potrzebnych analiz, raportów czy też zbudowania narzędzi do cyklicznego badania i monitorowania określonych wielkości i wskaźników. Często osoby pracujące w kontrolingu znają Excela, ale nie potrafią zastosować go w konkretnym przypadku czy też nie potrafią za jego pomocą zbudować modelu sprzedażowego. W książce pokazujemy, w jaki  sposób taki model sprzedażowy zbudować oraz jak najlepiej wykorzystać do analizy danych różne funkcje Excela. Dużą zaletą książki jest omawianie poszczególnych funkcji na praktycznych przypadkach zaczerpniętych z codziennego funkcjonowania przedsiębiorstw.
Przykłady zawarte w podręczniku możliwe są do wykonania w Excelu 2007, Excelu 2010 i 2013. Arkusze z opisywanymi przykładami można pobrać z Internetu.

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

z a a w a n s o w a n y z a a w a n s o w a n y Kontroling sprzedażowy w Excelu Książka w formie praktycznych przykładów opisuje trzynaście zagadnień związanych z wykorzystaniem Excela do analizy danych sprzedażowych. Osoby pracujące w analizie sprzedaży, ale także zajmujące się innymi obszarami, jak tworzenie prognoz, dowiedzą się, jak w innowacyjny sposób korzystać z narzędzi oferowanych przez ten popularny arkusz kalkulacyjny. Autor jest nie tylko ekspertem od Excela, ale posiada także doświadczenie w zakresie kontrolingu, raportowania oraz analizy fi nansowej zdobyte w międzynarodowych korporacjach. NAJBLIŻSZE TOMY • Zaawansowane narzędzia graficznej prezentacji wyników w Excelu Podręcznik opisuje dostępne w Excelu narzędzia wizualizacji, m.in. Smart Art, formatowanie warunkowe (paski danych, skale kolorów i zestawy ikon), zaawansowane wykresy, przebiegi w czasie, pokrętła i suwaki tworzone z wykorzystaniem ActiveX czy sposoby prezentacji danych na dwóch monitorach. • ScoreCard – zarządzanie procesami biznesowymi z wykorzystaniem Excela Czytelnik pozna narzędzia, które pozwalają za pomocą Excela przełożyć ogólną wizję przedsiębiorstwa na konkretne działania operacyjne i przejrzyste cele dla pracowników fi rmy na wszystkich szczeblach organizacyjnych. • Pobieranie i porządkowanie danych z zewnętrznych źródeł Książka pokazuje, jak sprawnie radzić sobie z powszechnym problemem – pobieraniem danych z zewnętrznych źródeł. Autor przedstawia wiele pomysłowych sposobów, jak sprawnie uporządkować dane będące nawet w całkowitej rozsypce. ACX01 ISBN 978-83-269-3098-0 Cena: 79 zł KONTROLING SPRZEDAŻOWY W EXCELU K o n t r o l i n g s p r z e d a ż o w y w E x c e u l 1 Tom I NPV WSP.KORELACJIJEŻELI ROZKŁ.EXP KOMÓRKA VBA LOGSUMA DNI.ROBOCZE ILOCZYN COS CZY.LICZBA Kontroling sprzedażowy w Excelu Wojciech Próchnicki Autor: Wojciech Próchnicki Kierownik marketingu: Ewa Ziętek-Maciejczyk Wydawca: Monika Kijok Redaktor naczelny Grupy Czasopism: Urszula Wróblewska Redaktor prowadzący: Rafał Janus Korekta: Zespół Skład i łamanie: Triograf, Dariusz Kołacz Projekt okładki: Piotr Fedorczyk Druk: Miller ISBN: 978-83-269-3098-0 Copyright by Wydawnictwo Wiedza i Praktyka sp. z o.o. Warszawa 2014 Wydawnictwo Wiedza i Praktyka sp. z o.o. 03-918 Warszawa, ul. Łotewska 9a tel. 22 518 29 29, faks 22 617 60 10 NIP: 526-19-92-256 Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł „Kontroling sprzedażowy w Excelu” wraz z przysługującym Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są pra- wem autorskim. Przedruk materiałów opublikowanych w „Kontroling sprzedażowy w Excelu” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło. Publikacja „Kontroling sprzedażowy w Excelu” została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifi kacji, wiedzy i doświadcze- nia autorów oraz konsultantów. Zaproponowane w publikacji „Kontroling sprzedażo- wy w Excelu” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako ofi cjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Kontroling sprzedażowy w Excelu” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przykładów. Spis treści Wstęp ........................................................................................................................................................ 4 1. Planowanie budżetu i ustalenie warunków bonusów z wykorzystaniem narzędzia Tabela 2.1. danych ............................................................................................................................................... 5 1.1. Model sprzedaży i wyników budżetu .................................................................................. 5 1.2. Zadanie do wykonania .......................................................................................................... 7 1.3. Tabela danych jako narzędzie analizy symulacji ................................................................ 8 2. Grafi czna prezentacja struktury sprzedaży na wykresie bąbelkowym .................................... 13 Grafi czna prezentacja osiągniętych wyników dla poszczególnych towarów .............................................................................................. 13 2.2. Ustalenie granic poszczególnych grup towarów .............................................................. 15 2.3. Określenie polityki sprzedaży dla poszczególnych grup produktów .............................. 17 3. Budżetowanie elastyczne sprzedaży ............................................................................................ 18 3.1. Budżetowanie elastyczne .................................................................................................... 18 3.2. Struktura sprzedaży jako podstawa budżetów cząstkowych .......................................... 19 3.3. Wykonanie budżetu i analiza odchyleń ............................................................................ 22 4. Grafi czna prezentacja sprzedaży według województw ............................................................. 26 4.1. Analiza danych sprzedażowych z wykorzystaniem tabeli przestawnej ......................... 27 4.2. Wybór miesiąca z rozwijanej listy ..................................................................................... 28 4.3. Pobieranie danych z tabeli z użyciem funkcji WEŹDANETABELI .............................. 31 5. Ocena dostawców .......................................................................................................................... 36 5.1. Wskaźniki oceny dostawców ............................................................................................. 36 5.2. Miesięczna ocena dostawców ............................................................................................. 37 5.3. Podział na dostawców podstawowych i alternatywnych ................................................ 37 6. Rachunek marży zysku w ramach poszczególnych produktów i klientów ............................. 40 6.1. Składniki kalkulacji marży ................................................................................................. 40 6.2. Analiza marży I, II i III poziomu ....................................................................................... 41 6.3. Rachunek marżowy dla produktów .................................................................................. 41 6.4. Rachunek marżowy dla klientów ........................................................................................ 42 7. Analiza ABC jako metoda klasyfi kacji towarów w przedsiębiorstwie ................................... 44 7.2. Analiza ABC ......................................................................................................................... 44 7.2. Przykład analizy ABC ......................................................................................................... 45 8. System raportowania wskaźników efektywności pracy przedstawicieli handlowych .......... 49 8.1. Wskaźniki efektywności ..................................................................................................... 49 8.2. Forma raportowania KPI .................................................................................................... 50 8.3 Grafi czna prezentacja realizacji celów KPI ...................................................................... 51 9. Prezentacja odchyleń sprzedaży na wykresie typu fl ying bridges ........................................... 53 10. Konsolidacja tygodniowych planów (wykonań lub odchyleń) sprzedażowych .................. 57 10.1. Pliki do planowania sprzedaży ........................................................................................... 57 10.2. Konsolidacja danych ........................................................................................................... 58 10.3. Konsolidacja bez łączy ze źródłem danych ...................................................................... 62 11. Zastosowanie tabel przestawnych do analizy dużych zbiorów danych sprzedażowych ...... 66 11.1. Struktura danych wykorzystanych do tabeli przestawnej .............................................. 67 11.2. Tworzenie tabeli przestawnej ............................................................................................. 67 11.3. Modyfi kacja pól tabeli przestawnej ................................................................................... 69 12. Warianty budżetu sprzedaży tworzone przy użyciu menedżera scenariuszy ........................ 76 12.1. Model budżetu sprzedaży ...................................................................................................76 12.2. Menedżer scenariuszy .........................................................................................................79 13. Obliczanie różnych aspektów rentowności sprzedaży ..............................................................83 13.1. Budowa numeru zlecenia ...................................................................................................84 13.2 Określenie rentowności sprzedaży w różnych aspektach ............................................85 3 Kontroling sprzedażowy w Excelu Wstęp Osoba zajmująca się w organizacji kontrolingiem musi być wyposażona w odpowiednie narzędzia (system ERP czy oprogramowanie BI), lecz często wszelkie analizy i rapor- ty wykonuje w arkuszu kalkulacyjnym MS Excel. Wykorzystanie Excela w controllingu sprzedażowym daje możliwość szybkiego wykonania potrzebnych analiza, raportów czy też zbudowania narzędzi do cyklicznego badania i monitorowania określonych wielko- ści i wskaźników. Często osoby pracujące w kontrolingu znają Excela, ale nie potrafi ą zastosować go w konkretnym przypadku czy też nie potrafi ą za jego pomocą zbudować modelu sprzedażowego. W książce pokazano, jak w pomysłowy sposób wykorzystać do analizy danych różne funkcje Excela. Dużą zaletą jest omawianie poszczególnych funkcji na praktycznych przypadkach, które mogą zdarzyć się w fi rmach i innych organizacjach. Z głównych narzędzi Excela w książce znalazły się opisy tabel danych, tabel przestaw- nych, menedżera scenariuszy, konsolidacja oraz zaawansowane wykresy. Ale czytelnik dowie się nie tylko, jak korzystać z samego arkusza kalkulacyjnego. Autor pokazuje, jak analizować duże zbiory danych sprzedażowych i grafi cznie przedstawić wyniki sprze- dażowe. W przykładach omawia też takie modele, jak analiza ABC, analiza rachunku marżowego, budżetowanie elastyczne sprzedaży oraz KPI. Przykłady zawarte w podręczniku możliwe są do wykonania w Excelu 2007 i jego now- szych wersjach. Arkusze z opisywanymi przykładami można pobrać z Internetu. UWAGA Wszystkie pliki Excela z przykładami omawianymi w książce można pobrać ze strony: http://online.wip.pl/download/exceltom1.zip 4 1. Planowanie budżetu i ustalenie warunków bonusów z wykorzystaniem narzędzia Tabela danych Budżet fi rmy na kolejny rok powstaje we współpracy z różnymi działami przedsiębior- stwa, np. sprzedaży, produkcji, zasobów ludzkich itd. Jest to długotrwały i skompliko- wany proces, ponieważ planowaniu podlega większość z obszarów działalności danej organizacji. Wynikiem takiej wewnętrznej kooperacji jest plan funkcjonowania fi rmy na przyszły okres (przeważnie roku), wyznaczający cele w wielu aspektach i dziedzinach funkcjonowania jednostki. W tym rozdziale przyjrzymy się fi rmie dystrybucyjnej obsługującej około 80 klien- tów, której asortyment towarów liczy w przybliżeniu 120 pozycji. Na jej przykładzie pokażemy, jak w prosty i szybki sposób wyliczyć wszystkie warianty modelu dla kom- binacji dwóch parametrów przyjmujących kilkanaście różnych wartości (łącznie 135 kombinacji) . Firma dolicza do zakupionych towarów 15 ich wartości i w ten sposób ustala cenę sprzedaży. Ponieważ na rynku, na którym działa nasz podmiot, jest bardzo duża konku- rencja, fi rma musi czymś zachęcić zarówno obecnych klientów, jak i zdobyć nowych. Ta- kim sposobem jest system rocznych bonusów od zakupów dokonanych przez poszcze- gólnych klientów (resellerów, detalistów). Wielkość bonusów jest określana 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. Procentowa wysokość 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ą ujemnie na wynik fi rmy (marżę). Zadaniem pracownika jest ustalenie kilku wariantów bonusów (różnyh granic wielkości sprzedaży i odpowiadającym im bo- nusów), aby marża utrzymywała się w określonym przedziale, a klient mógł wybrać spo- śród kilku wariantów przyznania bonusu. Czyli doprowadzenie do sytuacji „wilk syty i owca cała”. 1.1. Model sprzedaży i wyników budżetu W naszym przykładzie pracownik 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 5 Kontroling sprzedażowy w Excelu wyliczenia bonusów, oraz bardzo uproszczony rachunek wyników pokazujący wynik na sprzedaży netto i brutto. Na rysunku 1.1. został przedstawiony arkusz sprzedaż, który zawiera roczne pla- ny sprzedaży poszczególnych towarów do poszczególnych klientów. Składa się on z kolumn: 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 warun- ków z arkusza wyniki), Wartość netto (liczona Wartość sprzedaży – Koszt zakupu – Bonus). Rysunek 1.1. Arkusz sprzedaż Arkusz wyniki (rysunek 1.2.) podzielony został na dwie części. W pierwszej części WA- RUNKI BONUSÓW określamy granicę przyznania bonusu dla każdego towaru oraz jego wysokość liczoną jako procent od wartości sprzedaży. W zakładce sprzedaż (rysu- nek 1.1) wielkość Bonus liczona jest przy użyciu funkcji JEŻELI (Ilość bonus granica [ilość]; Wartość sprzedaży * procentowa wartość bonusu; 0). W drugiej części arkusza zatytułowanej MODEL znajdują się poszczególne elemen- ty wyniku fi rmy, 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 , dodat- kowo 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 kosztów sprzedaży 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 poszcze- gólnych wielkości (wartość sprzedaży, koszty zakupy, bonus) stanowi sumę pozycji z arkusza sprzedaż. 6 Rysunek 1.2. Arkusz wyniki Model jest dynamiczny. Przykładowo, jako granicę bonusów wpiszemy 200, a za pro- centową wysokość 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ł (rysunek 1.3.), co obniży marżę II do poziomu 4,22 . Rysunek 1.3. Wynik przy przykładowych warunkach bonusów 1.2. Zadanie do wykonania Zadaniem pracownika jest wyznaczenie takich wariantów kryteriów bonusów (grani- cy ilościowej i wysokości bonusu), aby marża II kształtowała się na poziomie pomię- dzy 5–6 . Przełoży się to na wynik w granicach 2,5–3 mln zł. Klient będzie miał do wyboru kilka wariantów bonusów, a fi rma osiągnie wynik w wyznaczonym, satysfak- cjonującym przedziale. Zakłada się, że wysokość bonusu będzie w przedziale od 3 do 10 , co 0,5 (to da 15 wartości) oraz granica wielkości sprzedaży od 100 do 500, co 50 (9 wartości). 7 Kontroling sprzedażowy w Excelu Z tych przyjętych przedziałów wychodzi 9 × 15 = 135 kombinacji wysokości marży II, które należy przeanalizować. Pracownik mógłby każdy z wariantów podstawiać do mo- delu i notować wynik, ale powtarzanie tej czynności 135 razy jest męczące, pracochłon- ne i nie eliminuje możliwości popełnienia błędu. Jest jednak dobre rozwiązanie – narzę- dzie tabela danych. 1.3. Tabela danych jako narzędzie analizy symulacji Tabela danych jest jednym z narzędzi analizy symulacji. Jest to analiza typu „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 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 jed- nym 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 wybra- niu pokaże się okienko dialogowe Tabela danych, które zawiera dwa pola (rysunek 1.4.). Rysunek 1.4. Okienko dialogowe Tabela danych Pole Wierszowa komórka wejściowa wskazuje na wartości w  tabeli znajdujące się w pierwszym wierszu. W niej należy wpisać, gdzie w naszym modelu dane umieszczone w pierwszym wierszu mają być podstawiane. Z  kolei Kolumnowa komórka wejściowa dotyczy wartości w  tabeli znajdujących się w pierwszej kolumnie. W niej należy wskazać, gdzie w naszym modelu dane umieszczo- ne w pierwszej kolumnie mają być podstawiane. Tabela danych będzie zależna od dwóch parametrów: procentowej wysokości bo- nusu i jego granicy ilościowej. Tabelę danych budujemy w arkuszu wyniki (tabela musi być w tym samym arkuszu co model). Pierwszym krokiem do stworzenia ta- beli danych jest określenie jej układu. Granicę ilościową umieścimy w  wierszu 4, zaczynając od komórki G4 (przedział od 100 do 500, co 50). Natomiast wysokość bonusu wyrażoną w procentach 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 (rysunek 1.5.). 8 Rysunek 1.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 potrzebuje formuły wyliczającej badany przez nas wynik (rysunek 1.6.). Rysunek 1.6. Formuła wyliczająca wartość marży II Następnie zaznaczamy tak zbudowaną macierz, łącznie z wartościami i formułą (rysu- nek 1.7.). 9 Kontroling sprzedażowy w Excelu Rysunek 1.7. Zaznaczenie całej tabeli Przechodzimy do Dane/Analiza warunkowa/Tabela danych. Pojawi się okienko dia- logowe Tabela danych (rysunek 1.8.). Rysunek 1.8. Okno dialogowe Tabela danych 1. W wierszowej komórce wejściowej wskazujemy komórkę w 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 modelu, gdzie mają być podstawiane wartości z kolumny F (procentowa wysokość bonusu). Jest to komórka C4. 3. Klikamy OK i cała tabela wypełni się wartościami marży II dla parametrów z 4. wier- sza i kolumny F (rysunek 1.9.). Gdzie określona wartość marży II jest rezultatem pod- stawienia do modelu progu sprzedaży z wiersza 4 i wartości bonusu znajdującego się w kolumnie F. W jednej macierzy mamy zawarte wyniki dla 135 kombinacji dwóch zmiennych. 10 Rysunek 1.9. Wynikowa tabela danych Dodatkowo w zbudowanej tabeli można zastosować formatowanie warunkowe Narzę- dzia główne/Formatowanie warunkowe/Reguły wyróżniania komórek/Między… i za- stosować formatowanie warunkowe dla wartości pomiędzy 5 i 6 (rysunek 1.10.). Umożliwi to wyróżnienie interesujących wyników, czyli wszystkich marż II znajdu- jących się w tym przedziale. Rysunek 1.10. Zastosowanie formatowania warunkowego Jak widać na rysunku 1.11, są 23 kombinacje dwóch kryteriów spełniających zadane warunki, na przykład dla granicy ilościowej równej 150 i wysokości bonusu równej 4 marża II wyniesie 5,4 . 11 Kontroling sprzedażowy w Excelu Rysunek 1.11. Tabela wynikowa z formatowaniem Pod macierzą z marżą II w taki sam szybki sposób możemy utworzyć macierz dla wyni- ku na sprzedaży netto, tylko w lewym górnym rogu zamiast formuły wyliczającej marżę II należy wstawić formułę wyliczającą wynik na sprzedaży netto. Następnie zastosować formatowanie warunkowe wartości pomiędzy 2,5 mln a 3,0 mln. W efekcie otrzyma- my odzwierciedlenie procentowej wartości marży w wartościach złotówkowych wyniku netto na sprzedaży (rysunek 1.12.). Rysunek 1.12. Tabela wyniku na sprzedaży netto W ten sposób pracownik szybko i bezbłędnie wykonał powierzone mu zadanie, a przy okazji wyniki zostały przedstawione w sposób przejrzysty i czytelny. Teraz klientom fi r- my mogą zostać przedstawione 23 warianty bonusów do wyboru, z których każdy przy takim poziomie planowej sprzedaży fi rmie przyniesie wynik netto na sprzedaży w gra- nicach 2,5 mln zł do 3,0 mln zł (marża II 5,0 do 6,0 ). UWAGA 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 maksy- malnie dwóch jego parametrów. Dostarcza ono wielu istotnych i niezbędnych informacji w jasnym i prostym do odczytu układzie. 12 2. Grafi czna prezentacja struktury sprzedaży na wykresie bąbelkowym W tym rozdziale przyjrzymy się, jak w procesie budowania strategii sprzedażowej wykorzystać trzy parametry, a uzyskane wyniki przedstawić w czytelnej formie. Sposób podziału towarów na grupy i opisanie ich przy użyciu trzech argumentów umożliwia łatwe i przejrzyste zobrazowanie struktury indeksów i pozwala na bu- dowanie polityki sprzedażowej mającej na celu poprawę wyniku całej firmy. Daje jasny obraz struktury sprzedaży i działań, które należy podjąć w kierunku poprawy wyniku. Przykładowa fi rma analizuje sprzedaż produktów wg trzech kryteriów: (cid:121) wielkości sprzedaży, (cid:121) marży osiągniętej na danym indeksie, (cid:121) liczby punktów, do których towar jest dostarczany. Następnie według dwóch kryteriów: wartości sprzedaży oraz marży (parametr licz- by punktów nie jest brany pod uwagę na tym etapie) wyznacza cztery grupy towa- rów: 1. Grupa 1 – towary o wysokiej marży i wysokiej wartości sprzedaży, 2. Grupa 2 – towary o wysokiej marży i niskiej wartości sprzedaży, 3. Grupa 3 – towary o niskiej marży i wysokiej wartości sprzedaży, 4. Grupa 4 – towary o niskiej marży i niskiej wartości sprzedaży. Na podstawie przyporządkowania towarów do tych czterech grup podejmowane są decyzje strategiczne dotyczące polityki sprzedażowej poszczególnych indeksów. Dla jasnego obrazu i umieszczenia trzech danych branych pod uwagę w procesie budowa- nia polityki sprzedażowej zostanie stworzona ilustracja grafi czna w formie wykresu bąbelkowego. 2.1. Grafi czna prezentacja osiągniętych wyników dla poszczególnych towarów Pierwszym krokiem jest zestawienie potrzebnych danych do interpretacji grafi cznej. Na rysunku 2.1 przedstawione zostały dane dla szesnastu towarów: wartość sprzedaży, marża procentowa i wartościowa oraz liczba punktów, do których dostarczany jest dany produkt. 13 Kontroling sprzedażowy w Excelu Rysunek 2.1. Zestawienie wartości sprzedaży, marży i liczby punktów sprzedaży dla towarów Na tej podstawie zostanie zbudowany wykres bąbelkowy, do którego każdy towar jest dodawany jako nowa seria. Na rysunku 2.2 pokazano odpowiednie parametry dla każdej z serii (każdego towaru). Poszczególne parametry: 1. Nazwa serii – wskazujemy nazwę towaru. 2. Wartość X serii – precyzujemy wartość sprzedaży danego indeksu. 3. Wartość Y serii – wskazujemy procentową marżę danego indeksu. 4. Rozmiar bąbelków serii – wpisujemy liczbę punktów dla danego indeksu. Rysunek 2.2. Dodawanie serii dla każdego z towarów 14 Po dodaniu wszystkich indeksów towarów otrzymamy wykres, jak na rysunku 2.3. Każ- dy „bąbel” reprezentuje jeden z indeksów towarowych opisany trzema wartościami: na osi x wartością sprzedaży, na osi y procentem marży, natomiast rozmiar bąbla oznacza liczbę punktów, do których towar jest dostarczany. Rysunek 2.3. Otrzymany wykres bąbelkowy dla wszystkich towarów Dzięki takiemu zabiegowi otrzymaliśmy jasny obraz każdego z towarów opisany za po- mocą trzech argumentów. 2.2. Ustalenie granic poszczególnych grup towarów Kolejnym krokiem jest ustalenie wartości sprzedaży oraz marży procentowej, według których towary zostaną przypisane do czterech grup. W tym wypadku takie granice to dla wartości sprzedaży 25.000 zł, a dla marży 10 . Na wykresie dzielimy towary przez przesunięcie punktu przecięcia dwóch osi x i y (rysunek 2.4). Rysunek 2.4. Wyodrębnienie grup towarów na wykresie 15 Kontroling sprzedażowy w Excelu Według tych granic każda grupa opisana jest wg dwóch wartości granicznych, co obra- zuje tabela poniżej. Tabela 1.1. Podział towarów na grupy GRUPA Grupa 1 Grupa 2 Grupa 3 Grupa 4 Wariant wartości sprzedaży Wariant marży = 25.000 25.000 = 25.000 25.000 = 10 = 10 10 10 Obszary dla każdej z grup na wykresie przedstawia rysunek 2.5. Jest to przejrzysty spo- sób grafi cznego ujęcia przypisania każdego z indeksów towarów do jednej z czterech grup. Rysunek 2.5. Określenie grup na wykresie Aby umieścić pola z nazwami grup na wykresie, należy przejść do Wstawianie/Tekst/ Pole tekstowe i wstawić cztery pola tekstowe z nazwami poszczególnych grup. Następ- nie te pola tekstowe można umieścić w wybranych miejscach na wykresie. Trzymając wciśnięty lewy klawisz [Ctrl], zaznaczamy cztery pola tekstowe i wykres, następnie przyciskamy prawy przycisk myszy i z menu podręcznego wybieramy opcję Grupuj/ Grupuj. 16 2.3. Określenie polityki sprzedaży dla poszczególnych grup produktów Ostatnim zadaniem jest określenie dalszych działań sprzedażowych dla poszczególnych grup towarów. Grupa 1 to grupa indeksów T7, T14, T6, T10 i T11. Zarówno wartość sprzedaży, jak i marży procentowej jest na zadawalającym poziomie. Dlatego też możemy operować trzecim parametrem, próbując zwiększyć liczbę punktów, do których jest dostarczany towar. Grupa 2 zawiera indeksy T16, T2, T4, T1 i T9. W tej grupie towary osiągają zadowalającą marżę, ale wielkość sprzedaży jest poniżej wyznaczonej granicy. W tej grupie powinno się zwiększyć sprzedaż do już obsługiwanych punktów, jak też zdobywać nowe punkty sprzedaży. Wynikiem polityki sprzedażowej powinno być przejście tych towarów z gru- py 2 do grupy 1. Grupa 3 obejmuje indeksy T12, T15 i T8. W tej grupie wartość sprzedaży jest powyżej wyznaczonego minimum, natomiast marża poniżej. Dla indeksów z tej grupy należy podjąć działania mające na celu zwiększenie marży, np. przez znalezienie tańszego do- stawcy towaru lub oszczędności na kosztach dotyczących obsługi sprzedażowej tych indeksów. Jak w przypadku grupy 2 wynikiem powinno być przejście indeksów do grupy 1. Grupa 4 mieści w sobie indeksy T3, T5 i T13. W tej grupie wartość sprzedaży oraz war- tość marży jest poniżej wyznaczonego minimum. Jest to grupa najwyższego ryzyka i na- leży wyznaczyć taką politykę, aby zwiększyć sprzedaż (większe ilości do już obsługi- wanych punktów lub zdobywanie nowych) oraz by zwiększyć marżę (tańszy dostawca, redukcja kosztów). Jeżeli dane kroki nie dadzą zakładanego rezultatu, należy zastanowić się nad zastąpieniem tych indeksów innymi podobnymi towarami. Jak widać, idealnym rozwiązaniem jest sytuacja, w której wszystkie indeksy należą do grupy 1. Grupa 2 wymaga zwiększenia sprzedaży, grupa 3 podniesienia, natomiast grupa 4 wymaga szczególnej uwagi, ponieważ to ona obniża wyniki całej fi rmy. 17 Kontroling sprzedażowy w Excelu 3. Budżetowanie elastyczne sprzedaży Budżet elastyczny jest to plan tworzony w taki sposób, aby możliwe było korygowanie wstępnie zaplanowanych wielkości w zależności od zmiany warunków gospodarczych, możliwości fi rmy czy też zmiany rozmiarów działalności. Budżetowanie elastyczne ma również zastosowanie w zarządzaniu i planowaniu sprzedaży. Podstawowym założeniem tego rodzaju budżetowania jest określenie i zdefi niowanie głównych funkcji i zależności budżetowych występujących w strukturze sprzedaży. UWAGA W  ten sposób można budżetować nie tylko sprzedaż, ale również koszty w przedsiębiorstwie, co pozwoli na określenie wyniku na działalności całej organizacji. Oczywiście najważniejsze jest ustalenie zmiennych parametrów w modelu oraz wyznaczenie ich wpływu na poszczególne wielkości sprzedaży jak też kosztów. Podstawową funkcją opisującą przychody ze sprzedaży jest: Przychody ze sprzedaży = liczba sprzedanych sztuk produktu × cena jednostkowa produktu Ten rodzaj budżetowania pozwala na określenie dwóch rodzajów odchyleń sprzedaży: 1. Odchylenia wynikające z  liczby sprzedanych sztuk produktu (wolumenowe) – ustalenie tego odchylenia polega na przeliczeniu wykonanej sprzedaży przez ceny przyjęte w  budżecie i  obliczeniu odchylenia od budżetowanej wartości sprze- daży. 2. Odchylenia wynikające z ceny jednostkowej produktu (efektywne) – ustalenie tego odchylenia polega na przeliczeniu budżetowanej wielkości sprzedaży przez ceny z wykonania i obliczeniu odchylenia od budżetowanej wartości sprzedaży. Zaletą budżetowania elastycznego jest łatwe ustalenie przyczyny otrzymanych odchyleń: czy wynikają z odchylenia od planowanego wolumenu, czy też to skutek różnicy pomię- dzy ceną planowaną a ceną osiągniętą. 3.1. Budżetowanie elastyczne Przykładem pokazującym zastosowanie budżetowania elastycznego w sprzedaży jest fi r- ma, która wytwarza trzy grupy produktów sprzedawanych na rynku: 1. Krzesła – jako osobny produkt. 2. Stoły – jako osobny produkt. 3. Komplety – stół z kompletem krzeseł. 18 Struktura organizacyjna działu sprzedaży jest przedstawiona na rysunku 3.1. Dział sprzedaży podzielony został na trzy grupy produktowe. Całym działem zarządza dyrek- tor sprzedaży, a nad każdą gałęzią reprezentującą grupę produktową czuwa kierownik. Każda grupa produktowa ma po trzech sprzedawców, którym przypisany jest określony region kraju. Rysunek 3.1. Struktura działu sprzedaży w przykładowej fi rmie W tej jednostce zostanie zastosowane budżetowanie elastyczne sprzedaży oraz po- kazany sposób wyliczania dwóch rodzajów odchyleń od przyjętego planu. Założenie w planowaniu sprzedaży jest takie, że zostaje wyznaczona jedna kwota całorocznej sprzedaży, która następnie powinna być rozdzielona na poszczególne grupy pro- duktowe oraz na poszczególnych sprzedawców. Dzięki temu każdy ze sprzedawców otrzyma swój budżet cząstkowy sprzedaży. Opracowany system budżetu elastycz- nego musi być tak skonstruowany, aby przy zmianie ogólnej planowanej wartości sprzedaży cały model przeliczył się do poziomu budżetu cząstkowego każdego ze sprzedawców. 3.2. Struktura sprzedaży jako podstawa budżetów cząstkowych Z określenia możliwości produkcyjnych fi rmy oraz zaplanowaniu sprzedaży na takim poziomie, aby EBIT fi rmy był na zadowalającym poziomie, dyrektor sprzedaży otrzymał planowaną wartość sprzedaży na 2013 rok w wysokości 5.496.586,79 zł. Jednocześnie 19 Kontroling sprzedażowy w Excelu zastrzeżono możliwość zmiany tej wartości. Zadaniem dyrektora jest przedstawienie po- działu tej wartości na poszczególne grupy produktowe i sprzedawców oraz przeliczenie wartości na liczbę produktów z założeniem 5 wzrostu średniej ceny z roku poprzed- niego. Model i założenia przeliczania na wielkość sprzedaży i dzielenia na budżety cząst- kowe muszą być tak skonstruowane, aby zmiana planowanej ogólnej wartości sprzedaży nie przysporzyła problemu ani nie była czasochłonna dla działu. W celu określenia struktury wartości sprzedaży w podziale na grupy produktowe i sprze- dawców za podstawę do podziału przyjęto strukturę sprzedaży z przedniego roku 2012 (rysunek 3.2). Dla niej określono wartościowy podział wykonania w roku poprzednim oraz wyrażony procentowo udział poszczególnych grup produktowych oraz sprzedaw- ców w całej wartości sprzedaży. Według struktury z roku 2012 grupa krzesła stanowi 30,3 sprzedaży, grupa stoły 32,3 natomiast grupa komplety 37,5 . Planowane wartości sprzedaży zostaną rozbite na po- szczególne grupy takimi właśnie procentami udziału. Oczywiście procenty można zmie- nić, jeżeli osoby odpowiedzialne za sprzedaż posiadają informacje o planowanych zmia- nach na rynku mających wpływ na strukturę sprzedaży. Dodatkowo może ulec zmianie również rozbicie na poszczególnych sprzedawców, co ma związek z wyznaczonymi im celami budżetowymi czy też np. planem zatrudnienia nowych sprzedawców i wyznacze- niem im celów sprzedażowych na przyszły rok. Rysunek 3.2. Struktura sprzedaży w 2012 roku Na podstawie takiej struktury procentowej sprzedaży podzielono planowaną wartość sprzedaży w roku 2013 na poszczególne grupy i sprzedawców (rysunek 3.3). Otrzymano w ten sposób roczne budżety cząstkowe dla każdego ze sprzedawców przy założeniu takiej samej struktury sprzedaży jak w poprzednim roku. 20 Rysunek 3.3. Planowana struktura sprzedaży w 2013 roku Kolejnym krokiem jest przejście z cząstkowych budżetów wartościowych na ilościowe. W tym celu określono średnie ceny dla każdej z grup produktów w 2012 roku. Założe- niem budżetowym jest zwiększenie cen o 5 w stosunku do roku poprzedniego. W ten sposób otrzymano średnie ceny grup na rok 2013 (rysunek 3.4). Na procent zmiany cen może mieć wpływ wiele czynników, np. wzrost cen surowców do produkcji wyro- bów, czy nowe założenia marży I stopnia na poszczególnych grupach produktowych. Rysunek 3.4. Planowane ceny grup towarów w 2013 roku Na rysunku 3.5 zostały przedstawione ilościowe budżety cząstkowe dla każdego ze sprze- dawców. Sposób ich wyliczenia to podzielenie planowanej wartości sprzedaży w danej grupie produktowej przez planowaną średnią cenę w tej grupie. Plany dla poszczegól- nych sprzedawców mogą być przeliczone także na produkty według zakładanej ceny produktu na rok następny lub na klientów według struktury sprzedaży na poszczegól- nych klientów z poprzedniego roku. Według takich założeń i metodologii został stworzony model budżetu elastycznego, któ- ry podaną łączną wartość sprzedaży dzieli na poszczególne budżety cząstkowe grup pro- duktowych i sprzedawców w ujęciu tak wartościowym, jak ilościowym. W przypadku zmiany ogólnej planowanej wartości sprzedaży na rok 2013 wystarczy wpisać kwotę do zbudowanego modelu, a Excel automatycznie podzieli ją na budżety cząstkowe i wyliczy 21 Kontroling sprzedażowy w Excelu Rysunek 3.5. Planowana struktura wielkości sprzedaży w 2013 roku budżet ilościowy. Jak widać, opracowanie takiego elastycznego modelu jest rozwiąza- niem, które usprawnia dynamiczną zmianę budżetu sprzedaży i w szybki sposób wyzna- cza cząstkowe budżety wartościowe i ilościowe. 3.3. Wykonanie budżetu i analiza odchyleń Wykonanie planów na rok 2013 zostało określone na poziomie wartości sprzedaży równej 5.721.648,25 zł. Strukturę wykonania wartościową i ilościową w podziale na grupy pro- duktów i sprzedawców przedstawia rysunek 3.6. Dodatkowo wyznaczone zostały średnie ceny grup w badanym roku, które dla grup stoły i komplety są niższe niż planowane. Rysunek 3.6. Rzeczywiste wykonanie sprzedaży w 2013 roku 22 Dla roku 2013 zostały wyliczone odchylenia od planu w wartościach sprzedaży, ilo- ściach i średniej cenie (rysunek 3.7). Plusem jest określenie tego rodzaju odchyleń dla każdego z budżetów cząstkowych. Dzięki temu możemy zobaczyć, która grupa pro- duktowa czy sprzedawca wykonali lub nie wykonali założonego budżetu. Jak widać, odchylenie od budżetu ma charakter dodatni dla całej fi rmy. Zrealizowana wartość sprzedaży jest wyższa o 252.061,46 zł od planowanej na rok 2013. Dzięki szczegóło- wości na poziomie grupy i sprzedawców da się zauważyć, że pomimo zrealizowania budżetu na poziomie całej fi rmy dla grupy stoły budżet nie został osiągnięty – odchy- lenie wynosi 156.777,50 zł. Budżetu nie zrealizował również sprzedawca nr 2 z grupy krzesła oraz sprzedawcy numer 1 i 3 z grupy stoły. Rysunek 3.7. Odchylenie wartości sprzedaży, ilości i ceny w 2013 roku Jednak sama informacja o odchyleniach wartości sprzedaży od planowanych nie mówi nam, co było przyczyną odchyleń. Możliwości są dwie: odchylenie na planowanych ilo- ściach czy odchylenie wynikające z ceny. Dlatego też zostały wyznaczone odchylenia od wartości sprzedaży wolumenowe (ilościowe) oraz efektywne (ceny). Odchylenia wolumenowe zostały wyliczone w następujący sposób: Odchylenie wolumenowe wartości sprzedaży = (ilość wykonana × cena sprzedaży z budże- tu) – budżetowana wartość sprzedaży Odchylenia efektywne zostały wyliczone w następujący sposób: Odchylenie efektywne wartości sprzedaży = (ilość planowana × cena sprzedaży z wykona- nia) – budżetowana wartość sprzedaży W ten sposób określiliśmy, jaki wpływ wartościowy na odchylenie wartości sprzedaży miało odchylenie w ilościach, a jakie w cenach. Na dodatnie odchylenie zaplanowanej wartości sprzedaży miało wpływ odchylenie od ilości sprzedaży w wysokości 401.142,03 zł na plus oraz na minus 149.080,58 zł z tytułu ceny. Z tego wynika, że zaplanowana sprze- daż była za niska, jeżeli chodzi o ilości sprzedaży, a przewidywania wzrostu ceny sprze- daży o 5 były zbyt wysokie. 23 Kontroling sprzedażowy w Excelu Rysunek 3.8. Odchylenie wolumenowe i efektywne w 2013 roku Budżetowanie elastyczne sprzedaży jest sposobem szybkiego reagowania na zmiany za- chodzące w wielkości sprzedaży oraz szybkim sposobem na określanie budżetów cząst- kowych (ilościowych i wartościowych) dla podanej łącznej wartości sprzedaży plano- wanej na dany rok. Pozwala na określenie i wycenienie wartościowo dwóch rodzajów odchyleń: wolumenowych wynikających ze zmiany planowanych ilości oraz efektyw- nych wynikających ze zmiany planowanej ceny. Dodatkowo model budżetu można uzależnić od większej liczby wprowadzanych pa- rametrów niż tylko od łącznej wartości sprzedaży. Może to być również parametr procentowej zmiany sprzedaży w  poszczególnych grupach produktowych. Warto stosować sposób budżetowania elastycznego w sprzedaży, ponieważ daje on wiele możliwości budowy scenariuszy budżetowych z  podziałem na budżety cząstkowe oraz pozwala na określenie odchyleń. Dodatkowo, w ten sam sposób mogą być wy- dzielane budżety dla poszczególnych sprzedawców w  podziale na klientów i  pro- dukty. W tym wypadku poziom szczegółowości będzie już bardzo duży i pozwoli na dokładne ustalenie przyczyn występujących odchyleń od planowanej wartości sprzedaży. Podstawą budżetu elastycznego jest dobrze zbudowany model, np. w Excelu, który po- zwoli na szybkie i sprawne przeliczanie różnego rodzaju scenariuszy budżetowych w za- leżności od wielu parametrów. Parametry określane na wejściu do modelu mogą być różne: 1. Wartość planowanej sprzedaży – podstawowy parametr wejściowy do modelu budżetu elastycznego, na podstawie którego dokonywane są dalsze analizy i po- działy. 2. Wartość procentowej podwyżki cen sprzedaży – na wysokość zmiany cen może mieć wpływ wiele czynników, np. wzrost cen surowców do produkcji wyrobów, nowe założenia dotyczące marży I stopnia na poszczególnych grupach produktowych, pro- centy mogą być różne dla poszczególnych grup produktowych. 3. Procentowa struktura podziału wartości sprzedaży na poszczególne grupy pro- duktowe – może być określona na podstawie struktury z lat ubiegłych bądź zmieniona 24 wg informacji dotyczących zmiany zapotrzebowania na rynku na grupę produktów lub wynikać ze zmiany w konkurencji na rynku. 4. Procentowa struktura podziału wartości sprzedaży na poszczególnych sprzedaw- ców – może być określona na bazie struktury z lat ubiegłych bądź zmodyfi kowana, jeżeli zmieni się liczba sprzedawców. 25 Kontroling sprzedażowy w Excelu 4. Grafi czna prezentacja sprzedaży według województw Po zamknięciu miesiąca, oprócz wielu raportów w formie tabelarycznej, często należy przedstawić niektóre dane w formie grafi cznej. Jednym z takich zagadnień jest prezen- tacja udziałów grup produktów w miesięcznej sprzedaży w podziale geografi cznym, np. na poszczególne województwa. W naszym przykładzie dodatkowym utrudnieniem jest fakt, że prezentacja ma być w for- mie mapy Polski z podziałem na województwa, a na obszarze każdego z nich jako wykres kołowy ma być umieszczony podział sprzedaży trzech grup produktów. Przygotowanie takiego raportu może zająć sporo czasu. Oprócz przygotowania danych czasochłonnym zadaniem jest stworzenie szesnastu wykresów, które trzeba umieścić odpowiednio na mapie kraju. Jeśli jest to zadanie, które trzeba wykonywać regularnie, warto je zautomatyzować. Excel umożliwia stworzenie odpowiedniego narzędzia, w którym wybierany będzie tylko mie- siąc, a dane na mapie będą aktualizowały się same. Osoba odpowiedzialna za ten raport będzie musiała jedynie co miesiąc zaktualizować dane sprzedażowe i tabelę. Na rysunku 4.1 przedstawione są dane sprzedażowe za dziesięć pierwszych miesięcy bie- żącego roku. Zawarte w arkuszu informacje to: województwo, grupa produktowa, miesiąc oraz wartość sprzedaży. Pracownik ma za zadanie stworzyć grafi czny raport dla wszyst- kich miesięcy od początku roku, a takie polecenie otrzymał dopiero w listopadzie. Na początku musi więc zbudować dziesięć identycznych raportów. Całkiem sporo, wziąw- szy pod uwagę czasochłonność każdego z nich. Dlatego też zostanie zbudowane do tego odpowiednie narzędzie, które ułatwi mu to zadanie. Rysunek 4.1. Dane sprzedażowe 26 4.1. Analiza danych sprzedażowych z wykorzystaniem tabeli przestawnej Pierwszym krokiem jest zbudowanie z dostępnych danych tabeli przestawnej o następu- jącej strukturze (rysunek 4.2): 1. Etykiety kolumn – pole miesiąc. 2. Etykiety wierszy – pola województwo i grupa. 3. Wartości – pole sprzedaż (suma, format liczbowy z separatorem tysiąca bez miejsc po przecinku). Rysunek 4.2. Struktura tabeli Tabela wygląda jak na rysunku 4.3 i z niej będziemy pobierać dane do wykresów. Rysunek 4.3. Tabela przestawna z wartościami sprzedaży 27 Kontroling sprzedażowy w Excelu 4.2. Wybór miesiąca z rozwijanej listy W nowym arkuszu tworzymy tabelkę jak na rysunku 4.4. W komórce C1 zbudujemy rozwijaną listę, z której będzie można wybrać numer miesiąca od 1 do 12. Dla wybrane- go miesiąca zostaną pobrane dane z tabeli przestawnej, na podstawie których zaktuali- zują się wykresy na mapie. Rysunek 4.4. Tabela do pobierania danych Aby zbudować rozwijaną listę, przechodzimy do Dane/Narzędzia danych/Poprawność danych/Poprawność danych… Pojawi się okno Sprawdzania poprawności danych. W pierwszej zakładce Ustawienia (rysunek 4.5) w polu Dozwolone wybieramy Lista, w polu Źródło wpisujemy numery miesiąca oddzielone średnikiem. Rysunek 4.5. Okno Sprawdzanie poprawności danych – zakładka Ustawienia 1. Wybieramy jako dozwoloną opcję Listy. 2. Wpisujemy źródło danych do rozwijanej listy. 28 3. Zaznaczamy opcję Ignoruj puste (na rozwijanej liście nie pokażą się puste pozycje) oraz zaznaczamy Rozwinięcie w komórce (lista zostanie rozwinięta w momencie za- znaczenia komórki przez użytkownika). W  kolejnej zakładce Komunikat wejściowy wpisujemy informację, która pojawi się w momencie zaznaczenia komórki zawierającej listę (rysunek 4.6) o treści „wybór nu- meru miesiąca”. Rysunek 4.6. Okno Sprawdzanie poprawności danych – zakładka Komunikat wej- ściowy 1. Zaznaczamy opcję, która umożliwi wyświetlanie komunikatu wejściowego. 2. W polu Tytuł wpisujemy tytuł komunikatu wejściowego o treści informacja. 3. W polu Komunikat wejściowy wpisujemy treść pokazującej się informacji: wybór nu- meru miesiąca. W trzeciej zakładce Alert o błędzie możemy tak zablokować komórkę, aby możliwy był wybór tylko wartości z rozwijanej listy. W tym celu zaznaczamy opcję Pokazuj alerty po wprowadzeniu nieprawidłowych danych (rysunek 4.7). W polu Styl wybieramy opcję Zatrzymaj, dzięki której po próbie wpisania innej wartości niż dostępne Excel zatrzyma dalsze działania i wyświetli na ekranie odpowiedni komunikat. Dodatkowo możliwe bę- dzie wybranie tylko jednej z pozycji z rozwijanej listy. W polu Tytuł wpisujemy tytuł ko- munikatu, natomiast pole Komunikat o błędzie należy wypełnić informacją wyświetlaną w chwili popełnienia błędu przez użytkownika o treści „tylko wartości z listy”. Całość działań zatwierdzamy przyciskiem OK. 29 Kontroling sprzedażowy w Excelu Rysunek 4.7. Okno Sprawdzanie poprawności danych – zakładka Alert o błędzie 1. Zaznaczamy opcję, która spowoduje wykrywanie prób wprowadzenia do komórki innej wartości niż dostępne na liście. 2. W  polu Styl wybieramy rodzaj działania w  momencie wystąpienia błędu. W  tym przykładzie każemy wstrzymać dalsze działania. 3. W polu Tytuł wpisujemy tytuł komunikatu o treści błąd!. 4. W polu Komunikat o błędzie wpisujemy treść pokazującej się informacji: tylko war- tości z listy. W ten sposób w komórce C2 powstała rozwijana lista (rysunek 4.8), z której można wybrać jeden z 12 miesięcy. Rysunek 4.8. Rozwijana lista z wyborem numeru miesiąca 30 4.3. Pobieranie danych z tabeli z użyciem funkcji WEŹDANETABELI Wartości sprzedaży w  wybranym miesiącu będą pobierane z  tabeli przestawnej (umieszczonej w  innym arkuszu) przy użyciu funkcji WEŹDANETABELI, która zwraca dane przechowywane w raporcie tabeli przestawnej. Budowa funkcji jest następująca: =WEŹDANETABELI(pole_danych;tabela_przestawna;pole1;element1;pole2;ele- ment2,...) Gdzie: 1. pole_danych – jest to ujęta w cudzysłowy nazwa pola z danymi pobieranymi przez funkcję. 2. Tabela_przestawna – to odwołanie do komórki (zakresu komórek), w której znajduje się raport tabeli przestawnej. Argument wskazuje, z której tabeli przestawnej dane są pobierane. 3. Pole1;element1;pole2;element2 – pary nazw pól i elementów (od 1 do 126 możliwych par). Opisują dane do pobrania. Mogą mieć dowolną kolejność. Nazwy pól i nazwy elementów innych niż daty i liczby muszą być ujęte w cudzysłowy. W arkuszu z zestawieniem z rysunku 4.9 w komórce C5 (pierwsze województwo dla grupy 1) wpisujemy „=” i wskazujemy z tabeli przestawnej dowolną wartość (rysu- nek 4.9). Rysunek 4.9. Funkcja WEŹDANETABELI Następnie w naszej funkcji dokonujemy parametryzacji jej argumentów (rysunek 4.10). Uwaga, należy pamiętać o odpowiednim blokowaniu adresów komórek. Rysunek 4.10. Parametryzacja funkcji WEŹDANETABELI 31 Kontroling sprzedażowy w Excelu Funkcję dodatkowo zagnieżdżamy w funkcji JEŻELI.BŁĄD. Jeżeli w danym miesiącu dla danej grupy i województwa nie było sprzedaży, ta funkcja spowoduje, że w zestawie- niu pojawi się 0 (rysunek 4.11). Rysunek 4.11. Dodanie funkcji JEŻELI.BŁĄD Funkcję kopiujemy na całe zestawienia i dzięki temu po wyborze miesiąca w komórce C2 dane zostaną zaktualizowane i z tabeli przestawnej pobrane dla wybranego okresu (ry- sunek 4.12). W taki sposób przygotowaliśmy podstawę do grafi cznej prezentacji danych w wybranym miesiącu roku. Oczywiście jeżeli dane, którymi dysponujemy, byłyby za kilka lat, to drugim argumentem do wyboru z rozwijanej listy dodatkowo mógłby być rok. Rysunek 4.12. Gotowe zestawienie z wyborem numeru miesiąca 4.4. Grafi czna prezentacja Dla każdego z województw wstawiamy wykres kołowy z efektem 3D. Dobrym sposobem jest zbudowanie jednego wykresu i skopiowanie go 15 razy, a następnie zmiana jedynie zakresu danych (rysunek 4.13). 32 Rysunek 4.13. Wykres kołowy z efektem 3D Wprowadzamy odpowiednie modyfi kacje wykresu: 1. Usunięcie legendy. 2. Usunięcie konturu obszaru kreślenia. 3. Usunięcie wypełnienia kształtu obszaru kreślenia. 4. Dodanie etykiety danych do wykresu jako wartość procentową. Otrzymamy wykres jak na rysunku 4.14. Rysunek 4.14. Zmodyfi kowany wykres 33 Kontroling sprzedażowy w Excelu Do arkusza wstawiamy obraz mapy Polski z podziałem na województwa. Wykres odpo- wiednio pomniejszamy i umieszczamy na mapie w województwie dolnośląskim. Jeżeli wykres zostanie ukryty pod mapą, to z menu podręcznego wykresu wybieramy opcję Przesuń na wierzch. Efekt tych działań przedstawia rysunek 4.15. Rysunek 4.15. Mapa z wykresem dla województwa dolnośląskiego Następnie kopiujemy wykres 15 razy, zmieniając jego zakres danych na poszczególne wartości sprzedaży dla danych województw oraz umieszczamy na mapie kraju w odpo- wiednim województwie (rysunek 4.16). Rysunek 4.16. Mapa z wykresami dla wszystkich województw 34 Ostatnim krokiem jest zgrupowanie wszystkich wykresów łącznie z mapą, żeby przy przesuwaniu konturów Polski również przesuwały się wykresy. Z wciśniętym klawiszem [Ctrl] wskazujemy mapę oraz wszystkie wykresy. Następnie z menu podręcznego wybie- ramy opcję Grupuj (rysunek 4.17). Rysunek 4.17. Grupowanie obiektów Efektem końcowym jest graficzne przestawienie danych sprzedażowych trzech grup produktów w  każdym z  województw z  możliwością wyboru miesiąca. Pracownik w kolejnym miesiącu będzie jedynie musiał dołożyć dane sprzedażowe do zestawie- nia i odświeżyć tabelę przestawną. Z listy wybrać kolejny miesiąc i graficzny raport gotowy. Dodatkowym plusem takiego rozwiązania jest fakt, że w szybki sposób moż- na wrócić do poprzednich miesięcy, a wykresy same się zaktualizują. Dzięki paru prostym czynnościom powstało elastyczne i jednocześnie estetyczne narzędzie do raportowania. 35 Kontroling sprzedażowy w Excelu 5. Ocena dostawców W przykładowej fi rmie podjęto decyzję o stworzeniu narzędzia do oceny dostawców materiałów, ponieważ te same towary kupowane są u wielu dostawców, co często stwa- rza niepotrzebne problemy. Wynikają z różnych terminów realizacji zamówień, niekom- pletności dostaw czy też niedostarczania towarów w ustalonym terminie, co powoduje opóźnienia w realizacji zleceń. Narzędzie do oceny poszczególnych dostawców ma dzie- lić ich na dwie grupy: podstawowych i alternatywnych. Na podstawie dokonanej analizy zostaną wyłonieni najlepsi dostawcy jako podstawowi. To u nich w pierwszej kolejności będą składane zamówienia na dany wyrób. W przypadku braków zamówienie zostanie złożone w grupie dostawców alternatywnych. Pozwoli to na skrócenie czasu i optymali- zację dostaw oraz na eliminację opóźnień w realizacji zleceń od klientów. Dodatkową korzyścią z wyników otrzymanych tą drogą będzie możliwość negocjacji z poszczególnymi dostawcami warunków dostawy. U dostawców podstawowych zwięk- szy się liczba składanych zamówień, co daje możliwość obniżenia kosztów transportu czy też uzyskania większych upustów. 5.1. Wskaźniki oceny dostawców Pracownik ma poddać analizie 30 stałych dostawców, mając do dyspozycji dane za cały poprzedni rok. Do oceny zostaną użyte cztery wskaźniki określające poziom obsługi przez każdego z nich. Będą to on time, in full, OTIF i lead time. ON TIME (na czas) – wskaźnik terminowości. Odpowiada na pytanie, ile procent zamó- wień zostało realizowanych terminowo w badanym okresie. ON TIME = LICZBA ZAMÓWIEŃ ZREALIZOWANYCH W TERMINIE × 100 OGÓLNA LICZBA ZAMÓWIEŃ Rysunek 5.1. Wzór na wskaźnik ON TIME IN FULL (kompletne) – wskaźnik kompletności. Odpowiada na pytanie, jaki procent zamówień zostało zrealizowanych kompletnie w badanym okresie. IN FULL = LICZBA ZAMÓWIEŃ KOMPLETNYCH × 100 OGÓLNA LICZBA ZAMÓWIEŃ Rysunek 5.2. Wzór na wskaźnik IN FULL LEAD TIME (czas realizacji) – wskaźnik czasu realizacji zamówienia (w dniach). Wyli- cza średnią liczbę dni realizowanych zamówień w badanym okresie. 36
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Kontroling sprzedażowy w Excelu
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: