System informatyczny analizy sprzedaży wg zasad Metodyki 4TG

PROLOG,
CIUT O METODYCE 4TG

Metodyka 4TG, to takie zasady pracy w MS Excel, które pozwalają w jak najlepszy sposób rozwiązać określone klasy problemów, oznacza to, że z jednej strony ograniczają konieczną ilość używanych elementów MS Excel do niezbędnych, a z drugiej strony wykorzystują standardy rozwiązań typowych problemów.

Wiadomo, że każdą z różnych potrzeb użytkowników w MS Excel można zrealizować w różny sposób. Badania poprzedzające powstanie metodyki 4TG wyznaczyły takie metody i sposoby rozwiązań, które powodują, że rozwiązanie posiada właściwą jakość, to znaczy, że rozwiązanie charakteryzują cechy:
- bezpieczeństwo,
- czytelność,
- elastyczność.
Stosowanie niektórych elementów MS Excel, czy sposobów rozwiązań powoduje, że brak jest niektórych z wyżej wymienionych cech. Oznacza to, że lista wykorzystywanych elementów w Metodyce 4TG została ograniczona do niezbędnych.

Badania dotyczyły także problemów rozwiązywanych za pomocą MS Excel. Problemy merytoryczne dotyczące: rachunkowości, logistyki, zarządzania i innych nauk zostały sprowadzone do typowych problemów informatycznych. Typowe problemy informatyczne zostały rozwiązane i zaimplementowane w MS Excel za pomocą takich sposobów narzędzi arkusza, które dają rozwiązania o docelowej jakości. To są rozwiązania standardowe metodyki 4TG.

Rozwiązania standardowe realizują potrzebne funkcjonalności i służą do realizacji systemów realizowanych według metodyki 4TG. Oznacza to, że są to takie „większe klocki”, które ułatwiają tworzenie i budowę automatycznie działających rozwiązań (to MS Excel ma pracować a nas, a nie my w MS Excel).

Przykład: takim „większym klockiem” w MS Excel jest: Formatuj jako tabelę (w menu: Narzędzia główne) – to rozwiązanie realizuje: nazwanie zakresu zaznaczonej tabeli, możliwość przechodzenia klawiszem: Tab, po poszczególnych komórkach tabeli, automatyczne dodawanie kolejnych pozycji do tabeli, formatowanie komórek, wprowadzania wzorów do kolejnych kolumn (z możliwością automatycznego kopiowania wzorów w ramach kolumny), automatycznej zmiany zakresów tabeli. Działania takie wskazują jakąś drogę rozwiązania problemu, która jest trochę różna od zasad metodyki 4TG, dlatego to rozwiązanie nie jest stosowane w Metodyce 4TG, ale pokazuje działanie „większych klocków”, które umożliwiają szybszą realizację systemów w MS Excel.
Metodyka 4TG korzysta z niektórych funkcjonalności przedstawionych w Formatuj jako tabelę. Na przykład: automatyzacji wprowadzania wzorów, ale do tego celu używane są w metodyce 4TG inne narzędzia, specjalnie zrealizowane dla potrzeb Metodyki 4TG. Metodyka 4TG posiada również wiele innych narzędzi (rozwiązań), których MS Excel nie ma (lub są, ale nie są bezpieczne czy elastyczne). Na przykład narzędzia wspomagające prowadzenie rejestrów, automatyzacji rejestracji, transmisji danych, zarządzania plikami (wymiany danych między plikami), zarządzania strukturami danych, parametryzacji, organizacji raportów. Dlatego, dzięki wykorzystaniu opracowanych w Metodyce 4TG zasad, budowanie nowych rozwiązań składa się głównie z łączenia rozwiązań standardowych (łączenia według zasad metodyki).

Oznacza to, że aby rozwiązanie miało właściwą jakość nie tylko ważne jest narzędzie: MS Excel i standardy rozwiązań, ale również analiza założeń, projektowanie, schematy budowy (i schematy programowania w Visual Basic for Excel).

 

FRAGMENT SYSTEMU ANALIZ SPRZEDAŻY (Z ELEMENTAMI METODYKI 4TG)

W analizowaniu sprzedaży istotna jest różnorodność układów analizowanych. Badanie związków między sprzedażą, a innymi wielkościami, badanie związków w czasie, badanie związków między sprzedażą komplementarnych i substytucyjnych wyrobów, badanie związków popyt – cena, popyt – marża, itp. To jest jedna z metod, która pozwala zauważyć związki między różnymi zależnościami. Znalezione związki między różnymi zależnościami należy weryfikować oraz poddawać bardziej szczegółowej analizie. Wnioski z badań pozwalają na podejmowanie decyzji na temat: strategii sprzedaży, planowaniu struktur asortymentowych sprzedaży, czy planowaniu polityki cenowej.

   
Chcąc przeprowadzić analizę według różnych kryteriów (i aby można było przeprowadzić to szybko) należy przede wszystkim rejestrować właściwie informacje. Oznacza to, że: jeżeli chce się poddać analizie sprzedaż według: grup produktowych i produktów, regionów sprzedaży i odbiorców, czasu (lat / kwartałów / miesięcy) należy zbierać dane w takich układach. To znaczy, że przy każdej pozycji sprzedaży konieczne jest określenie:
- jakiej dotyczy grupy produktowej,
- komu został sprzedany (i w jakim regionie),
- daty sprzedaży (rok, kwartał, miesiąc).  

Kolejny krok dla potrzeb tworzenia systemu informatycznego do prezentacji sprzedaży, to zaprojektowanie symboliki (klucza, według którego będą agregowane dane). Symbolika powinna być tak zaprojektowana, aby symbole danej grupy miały zawsze tyle samo znaków. Dla potrzeb rozwiązywanego przykładu zostanie przyjęte założenie:
- cztery pierwsze znaki będą dotyczyć produktów, przy czym dwa pierwsze znaki będą dotyczyły grupy produktowej; trzeci, to symbol produktu danej grupy; a czwarty: symbol koloru,
- kolejna (pięcioznakowa) grupa symboli dotyczy odbiorców, w tym dwa pierwsze znaki symbol kraju (regionu), kolejne trzy: symbol odbiorcy,
- ostatnia, siedmioznakowa, grupa symboli jest związana z czasem: cztery pierwsze znaki, to rok; kolejny znak – kwartał, a dwa ostatnie – miesiąc.
Między poszczególnymi grupami symboli zostały zaprojektowane spacje tak, aby łatwiej zinterpretować dane (ze względu na podwyższenie czytelności rozwiązania).

Przykładowo (zob. Rysunek nr 1):
Symbol w pierwszej pozycji "301S PL001 200601" (zobacz Rysunek nr 1) oznacza sprzedaż ław (30) prostych (1) w kolorze drewna, czyli surowych (S) dla pierwszego odbiorcy polskiego (PL001) w styczniu roku 2006 (2006101).
Symbol w drugiej pozycji "352C PL006 200601" oznacza sprzedaż stołów (35), zwykłych (1), w kolorze czarnym (C) dla szóstego odbiorcy polskiego (PL006) w styczniu roku 2006 (2006101).

Rysunek nr 1. Dane źródłowe (arkusz Hist). Komórki w kolumnie I – wartości sprzedaży, komórki w kolumnie M – symbole do agregacji danych.

Posiadając dane źródłowe - historyczne (zob. Rysunek nr 1) w postaci poszczególnych transakcji (w wierszach) wraz z przyporządkowanym do każdej transakcji symbolem (kol. M), wg wcześniej zaprojektowanych reguł, można zagregować dane o wielkości sprzedaży (kol. I) według dowolnego kryterium.

Chcąc uzyskać zagregowane dane wg dowolnego kryterium należy użyć funkcji SUMA.JEŻELI. W parametrze pierwszym tej funkcji trzeba wykorzystać kolumnę z kluczem (kolumna M), a w parametrze trzecim użyć kolumnę z wielkością sprzedaży (kolumna I). W parametrze drugim zastosować symbol, według którego będą agregowane dane.  Przykłady agregacji znajdują się na Rysunku 2.

Rysunek nr 2. Arkusz Raport. Wykorzystanie funkcji SUMA.JEŻELI do agregowania danych.

Do komórek kolumny B w arkuszu Raport zostały wprowadzone symbole (według zaprojektowanej wcześniej symboliki). Wzór w komórce C2: =SUMA.JEŻELI(Hist!M:M;B2;Hist!I:I) sumuje wielkość sprzedaży w całym 2006 roku. A po jego skopiowaniu do komórek: C4:C7 sumuje wielkość sprzedaży z poszczególnych kwartałów roku 2006. I dalej, po skopiowaniu do komórek: C9:C12 sumuje wielkość sprzedaży z pierwszych 4 miesięcy 2006 roku, a w komórce C14 sumuje sprzedaż wszystkich produktów grupy produktowej: ławy w całym 2006 roku (zobacz Rysunek nr 2).

W kolejnym etapie rozważań na temat rozwiązania, zostały wyczyszczone zawartości komórek siedmiu początkowych wierszy i wiersza 14-tego (zobacz Rysunek nr 3). Pozostały wzory w komórkach C9:C12. Zostały zmienione zawartości komórek B9:B12, w których były przechowywane symbole. Zawartościami tych komórek są teraz wzory, które wykorzystują wartość wprowadzoną do komórki B2 (symbol produktu). Do komórki B9 został wprowadzony wzór: =B$2&” ????? 2006?01”. Po skopiowaniu wzoru do komórek B10:B12 należy w tych komórkach wzory poprawić. Trzeba, w każdym z tych wzorów, wymienić numer miesiąca. W komórce B10, zmienić numer miesiąca na: 2, w komórce B11 na: 3, a w komórce B12: na 4.
Po wprowadzeniu do komórki B2 wartości: ”30??”, wzory w komórkach kolumny C sumują dane ze sprzedaży wszystkich produktów grupy produktowej: ławy, w każdym z 4 pierwszych miesięcy w 2006 roku.

Rysunek nr 3. Arkusz Raport. Wzory wyznaczające klucz do agregacji. Sterowanie parametrem z komórki B2.

Po wymianie wartości w komórce B2 na: ”35??” (zob. Rysunek nr 4), wzory w komórkach kolumny C sumują sprzedaż wszystkich produktów grupy produktowej: stoły. Oznacza to, że komórka B2 stała się komórką z parametrem sterującym symbolem produktu. Komórki z parametrami powinny być wyróżnione (w Metodyce 4TG została przyjęta konwencja barwienia tła komórek, do których wprowadza się informacje, na jasno zielono).

Rysunek nr 4. Arkusz Raport. Zmiana symbolu produktu. Wzory wyznaczające klucz do agregacji. Sterowanie parametrem z komórki B2.

Jednocześnie, w komórkach kolumny A, został dodany kolejny numer miesiąca (wzór w komórce A9: =1+A8). Po skopiowaniu tego wzoru do komórek A9:A12 wyniki działania (skopiowanych wzorów) ustaliły numery czterech kolejnych miesięcy. W konsekwencji został zmieniony wzór w komórce B9: =B$2&” ????? 2006?”&TEKST(A9;”00”). Wykorzystana została funkcja TEKST, która, w tym przypadku, numer miesiąca zapisuje w postaci dwóch znaków tekstu (o tym decyduje drugi parametr: ”00”). Po skopiowaniu wzoru do komórek B9:B12 automatycznie, we wszystkich skopiowanych wzorach, zostaną zmienione numery miesięcy (poprzednio należało, po skopiowaniu, poprawić numer miesiąca).

W kolejnym kroku pracy nad rozwiązaniem zostały dodane kolejne parametry: symbol odbiorcy, rok i wskaźnik, który wskazuje czy mają być prezentowane dane miesięczne, czy kwartalne.
W konsekwencji konieczna jest zmiana wzorów w komórkach B9:B12, które powinny uwzględniać nowe parametry. I tak, wzór w komórce B9: =B$2&" "&B$3&" "&B$4&JEŻELI(B$5;"?"&TEKST(A9;"00");TEKST(A9;"0")&"??")
wykorzystuje adresy parametrów. Funkcja JEŻELI w zależności od wartości w komórce B5 (PRAWDA/FAŁSZ) daje w wyniku:
- "?" i numer miesiąca (dla wartości PRAWDA w komórce B5),
- numer kwartału i "??" (dla wartości FAŁSZ w komórce B5).

Rysunek nr 5. Arkusz Raport. Dodanie kolejnych parametrów sterujących raportem.

Na rysunku nr 6 został dostawiony wykres, w którym porównane są dane wyliczone w komórkach kolumny C. Zmiany w komórkach z parametrami: B2, B3, B4, B5 i A9 powodują zmiany w wynikach wzorów komórek B9:B12, a zmiany w tych komórkach wpływają na zmiany w wynikach wzorów komórek C9:C12, a te z kolei na zmiany w wykresie. Oznacza to, że sterowanie zawartością wykresu możliwe jest za pomocą komórek z parametrami.

Rysunek nr 6. Arkusz Raport. Wykres.

Rysunek nr 7. Arkusz Produkty. Lista symboli i przyporządkowanych do nich nazw.

Jednocześnie komórki z parametrami (w tym przypadku B2) sterują tytułem. Zmiana w komórce B2 powoduje zmianę wyniku wzoru w komórce C7: =INDEKS(Produkty!C:C;PODAJ.POZYCJĘ(B2;Produkty!B:B;0)).
Funkcja PODAJ.POZYCJĘ (wewnętrzna w tym wzorze) namierza symbol produktu w kolumnie B arkusza Produkty, wynikiem działania funkcji jest numer wiersza, w którym został znaleziony symbol produktu (z komórki B2). Numer wiersza wykorzystywany jest przez funkcję INDEKS (zewnętrzną w tym wzorze), która z kolumny C wybiera właściwą nazwę. Znaleziona nazwa jest wynikiem działania wzoru w komórce C7, a dalej tytułem na wykresie.

Rysunek nr 8. Arkusz Raport. Wartości po zmianie wskaźnika: czy miesiąc na fałsz – układ kwartalny.

Po zmianie wskaźnika w komórce B5, na FAŁSZ w komórkach B9:B12 część symbolu odpowiedzialna za czas zmieniła się (w komórce B9 z: "2006?01" na: "20061??"). W wyniku zmiany zmieniły się wyniki działania wzorów w komórkach w kolumnie C, a dalej słupki na wykresie.

 

PODSUMOWANIE ROZWIĄZANIA

Powyżej został przedstawiony przykład automatycznego generowania prostych raportów, który można, na podstawie tekstu powyżej, zrobić samodzielnie. Przeznaczone są one do prostych analiz sprzedaży. Do bardziej złożonych analiz konieczne są trochę bardziej złożone modele i automatycznie działające raporty (dashboardy), które trudniej przedstawić w krótkim artykule.

W niniejszym przykładzie zostały wykorzystane te elementy MS Excel, które stosowane są w metodyce 4TG, głównie to: funkcje, zasady używania symboliki, czytelność zarówno z punktu widzenia projektanta jak i użytkownika, podział rozwiązania na arkusze. Ze względu na fakt, że przykład jest prosty, zamieszczona na początku, analiza rozwiązania ograniczyła się głównie do zasad organizacyjnych dotyczących budowy symboliki.

 

DASHBOARDY, PRZYKŁAD 1

W pierwszej części niniejszego artykułu został przedstawiony raport, który użytkownik może samemu zrobić przy podstawowych umiejętnościach MS Excel. Teraz trochę więcej możliwości.
Jedną z możliwości jest wspomaganie wprowadzania informacji do komórek z parametrami za pomocą przycisku (zobacz Rysunek nr 9). Zasada działania jest taka:
- uaktywnienie komórki (na przykład: id odbiorcy – komórka B2),
- użycie przycisku: WYBÓR Z LISTY,
- wybranie z listy parametrów (w przykładzie jest lista z odbiorcami).
Po wybraniu z listy (w tym przypadku są to wszyscy odbiorcy – symbol: "?????"), automatycznie zmieniają się symbole, według których agregowane są dane. Oznacza to, że raport dotyczy sprzedaży do wszystkich odbiorców ław prostych w kolorze czarnym w okresie marzec 2011 – luty 2015. 

Rysunek nr 9. Wybór odbiorcy.

Lista użyta w tym przypadku nie jest listą dostępną w MS Excel. Jest listą stworzoną na potrzeby metodyki 4TG – to narzędzie ma zdecydowanie większe możliwości od listy dostępnej w MS Excel. W zależności od użytych parametrów można taką listę rozbudować o komentarz każdej pozycji, filtr ustawiany na bieżąco podczas wyboru, miejsce listy. Możliwe jest wprowadzenie wybranej pozycji z listy do zabezpieczonej komórki, ale przede wszystkim rozwiązanie umożliwia stosowanie wzorów do parametryzacji listy (pozwala to między innymi na łączenie list z różnych źródeł, czy wyboru właściwej listy).

Po zmianie symbolu wyrobu (zobacz Rysunek nr 10) raport dotyczy sprzedaży do wszystkich odbiorców wszystkich wyrobów w kolorze czarnym w tym samym okresie.

Rysunek nr 10. Wybór wyrobu.

 

Należy zwrócić uwagę, że na tym wykresie prezentowane są zawsze wartości danych z 48 okresów. Oznacza to, że jest możliwość pokazywania dowolnych 48 kolejnych okresów. W kolejnym kroku wykorzystane zostanie narzędzie do zmiany okresu (przycisk ze strzałkami w lewo oznacza prezentację wcześniejszych, a przycisk ze strzałkami w prawo oznacza prezentację następnych kolejnych 48 okresów). I tak, po użyciu przycisku ze strzałkami w prawo nastąpi zmiana okresu prezentowanych danych (zobacz Rysunek nr 11).

 

Rysunek nr 11. Zmiana okresu.

 

DASHBOARDY, PRZYKŁAD 2

W kolejnym przykładzie zostanie przedstawiona analiza sprzedaży przy użyciu wykresów kołowych (z udziałami procentowymi) dowolnych wielkości sprzedażowych w zależności od wprowadzonych parametrów (zobacz Rysunek nr 12).

 

 

Rysunek nr 12. Arkusz z raportem złożonym.

 

W raporcie jest porównanie sprzedaży ław i stołów w latach: 2015, 2014, 2013 i 2012.
Po wprowadzeniu numeru kwartał: 3 (komórka B7) porównanie sprzedaży ław i stołów dotyczy kwartałów 3, 2, 1 z 2015 roku oraz 4 z 2014 roku (ostatnich 4 kwartałów).  A po usunięciu numeru kwartału (komórka B7) i wprowadzeniu numeru miesiąca: 2 (komórka B8) porównanie sprzedaży ław i stołów dotyczy ostatnich 4 miesięcy: lutego 2015, stycznia 2015,  grudnia 2014 i listopada 2014 (zobacz Rysunek nr 13).

 

Rysunek nr 13. Arkusz z raportem złożonym. Po zmianie danych.

 

Kolejne narzędzie wspierające pracę w modelu, a zrealizowane w ramach metodyki 4TG, które zostanie użyte w niniejszym przykładzie to wybór z listy, który zmienia wartości parametrów w zdefiniowanych komórkach.

Należy zwrócić uwagę, że na wykresie (zobacz Rysunek nr 12 lub nr 13) jest sześć miejsc na parametry wyrobów i odbiorców, Do każdej pary: symbol produktu – symbol odbiorcy dołączony jest symbol czasu. I tak, na tych rysunkach zostały wykorzystane po 2 symbole (z możliwych sześciu) na 4 wykresach:
- na pierwszym wykresie: "30?? ????? 2015?02" oraz "35?? ????? 2015?02",
- na drugim: "30?? ????? 2015?01" oraz "35?? ????? 2015?01",
- na trzecim: "30?? ????? 2014?12" oraz "35?? ????? 2014?01",
- na czwartym: "30?? ????? 2014?11" oraz "35?? ????? 2014?11".
Według tych symboli zostały zagregowane dane historyczne.
Analiza sprzedaży realizowana przez analityka sprzedaży częściowo może wykorzystywać w każdym z okresów ten sam zestaw parametrów, a częściowo może być on inny. Dlatego zestawy parametrów, które powtarzają się można zdefiniować, aby szybciej wpisywać je do raportu sterowanego parametrami. W niniejszym przykładzie po użyciu przycisku: WYBÓR WARIANTU pojawia się lista z tytułami raportów, spośród których użytkownik może wybrać dowolny tytuł raportu. Na Rysunkach nr 12 i nr 13 prezentowane dane dotyczą raportu nazwanego: "ławy i stoły".
Na Rysunku nr 15 jest przedstawiona sprzedaż wg kolorów, ponieważ został wybrany tytuł raportu: "wyroby (kolory)" (zobacz Rysunek nr 14).

Rysunek nr 14. Lista wyboru wariantu raportu.

Wyroby sprzedawane są w czterech kolorach: czarnym, białym, zielonym oraz w kolorze naturalnym drewna – surowe. Dane dotyczą sprzedaży w kolejnych czterech miesiącach od listopada 2014 do lutego 2013.
Z punktu widzenia przetwarzania informacji makro podpięte pod przycisk: „wybór wariantu” wymienia na raz wiele parametrów. Wartości tych parametrów należało wcześniej przyporządkować do kolejnych pozycji listy.

 

Rysunek nr 15. Arkusz z raportem złożonym. Po zmianie danych.

 

METODYKA 4TG NARZĘDZIEM DO BUDOWY SYSTEMU EKSPERCKIEGO

Zasady i reguły używania MS Excel zawarte w metodyce 4TG umożliwiają w stosunkowo łatwy sposób budowę systemu eksperckiego, który może sam analizować dane (złożenie go z „klocków”). Działa to tak: system ekspercki sam przegląda kolejno wiele raportów i wybiera niezgodności, czyli sprawdza różnice w stosunku do zdanych wartości. System taki ma możliwość na samodzielne mierzenie związków (zależności) między poszczególnymi wielkościami oraz pokazywanie w pierwszej kolejności tylko tych, które nie spełniają określonych kryteriów merytorycznych. Oczywiście tylko część wyników w raportach nie spełnia kryteriów (nie są zgodne z planem), dlatego należy je pokazać analitykowi w pierwszej kolejności.  

Przykład: w budżecie sprzedaży zapisano, że wartość sprzedaży ław w określonym okresie ma wynosić 100 000 PLN, przy czym dopuszczalne odchylenie jest +/- 5%. Zapisano również wartości sprzedaży innych produktów, z podziałem na regiony, handlowców, grupy produktowe, … . Jeżeli wielkość sprzedaży ław w tym okresie będzie na poziomie 95 000/105 000 PLN, to takie dane nie znajdą się w raporcie odchyleń. Jeśli zaś poziom sprzedaży będzie mniejszy niż 95 000 PLN lub większy niż 105 000 PLN, to raport system automatycznie oznaczy, że dany raport ma być zaprezentowany analitykowi, czy decydentowi.

Inny przykład: Na podstawie danych historycznych mierzony jest współczynnik kierunkowy szacowanej prostej – trendu sprzedaży (y = ax + b), jeśli współczynnik kierunkowy zmieni się podczas badania w ostatnim okresie, w stosunku do badania innego okresu, o wiecej niż założona wartość, to również taki raport ma być w grupie raportów, które zarządzający powinien zobaczyć.

Zaletą takiego rozwiązania jest możliwość oszczędności czasu managera, analityka, który analizuje tylko wybrane raporty. Szczególnie wtedy, kiedy rozliczne zajęcia managera nie pozwalają na skupienie uwagi na setkach/tysiącach raportów – możliwych do uzyskania z systemu. W okresach, w których jest mniej pracy (więcej wolnego czasu) jest możliwość analizy innych raportów, ustalania dodatkowych kryteriów wyboru, badaniu związkow, … .