Przedsiębiorco, jak wykorzystasz tabele przestawne do analizy kosztów zakupowych?

Za każdą trafną biznesową decyzją stoi gruntowna analiza danych. Podpowiadamy, jak wykonać taką analizę z pomocą tabeli przestawnej i dlaczego warto włączyć ją do pakietu firmowych narzędzi.

Z artykułu dowiesz się między innymi:
✔ jakie metody analizy danych warto wykorzystać;
✔ jak utworzyć w arkuszu kalkulacyjnym tabelę przestawną;
✔ jak zrobić przekrój wydatków firmowych.

Z tabelą przestawną lepiej poznamy nasze wydatki: co kupujemy, w jakiej ilości i od kogo. Zbadanie przekroju kosztów zakupowych w firmie pozwoli na dalsze podejmowanie racjonalnych decyzji zakupowych i ułożenie odpowiedniej strategii zakupowej. Posłuży też optymalizacji firmowych wydatków dzięki unikaniu zbędnych zakupów czy grupowaniu zamówień.

Skąd uzyskać dane do analizy?

Jeśli w firmie korzystasz z programu gospodarki magazynowej lub programu księgowego, wygeneruj raport. W przypadku braku dostępu do takiego oprogramowania możesz poprosić o dane dotyczące ilości zużywanego asortymentu swoich dostawców.

Przykładowy plik możesz pobrać poniżej. To na nim będziemy pracować.

Analiza kosztów firmowych dla przedsiębiorców

Pobierz

W większości przypadków otrzymasz dane zawierające następujące etykiety danych (które widnieją jako nazwy kolumn):

• ID_Firmy;
• Dostawca;
• Ilość zamówienia;
• Jednostka zamówienia;
• Data dokumentu;
• Miesiąc;
• Nr dokumentu;
• Wartość jednostkowa;
• Wartość netto;
• Waluta;
• Zakupiony towar – nazwa.

Dobrze, masz już wyeksportowane dane. Co możesz z nimi zrobić?

Podziel asortyment na kategorie

W przypadku kiedy do analizy mamy zestawienie zawierające kilka lub kilkanaście tysięcy rekordów i więcej, pracę usprawni nam utworzenie kategorii. W ten sposób łatwiej będzie nam później przejść do wyciągnięcia wniosków i podjęcia właściwych działań (np. napisania dobrego zapytania ofertowego).

Jest kilka prostych metod analizy danych. Poniżej przedstawiamy sposób przyporządkowania artykułów według nazwy lub dostawcy. Możesz je także stosować razem.

1. Kategorie artykułów wg nazw produktów
– Tworzymy dodatkową kolumnę „Towar – skrót”.
– Wykorzystując funkcję, np. LEWY (=LEWY(K2;znajdź(„ „)-1) z kolumny „Zakupiony towar – nazwa” pozyskujemy pierwsze słowo z nazwy artykułu.
– Następnie filtrujemy kolumnę alfabetycznie i do utworzonej kolumny „Kategoria zakupowa” wpisujemy ręcznie nazwę dla danej kategorii. Np. fraza „Bateria” występuje w naszym zestawieniu 8 razy – dzięki sortowaniu alfabetycznemu wszystkie te rekordy będą ze sobą sąsiadować. W ten sposób łatwiej będzie je przypisać do odpowiedniej kategorii.
– Postępujemy w ten sam sposób ze wszystkimi rekordami, do czasu przypisania ich odpowiedniej kategorii zakupowej.

2. Kategorie artykułów wg dostawców
Uwaga: metodę możesz wykorzystać, gdy wiesz, że masz dostawców wyspecjalizowanych w dostawie jednej kategorii lub podkategorii w zestawieniu produktów.
– Sprawdzamy wybrany artykuł, przyporządkowując go danej kategorii. Np. Router DenLink-200 jest dostarczany przez dostawcę „Firma komputerowa 3”.
– Filtrujemy kolumnę B i wybieramy jeden element „Firma komputerowa 3”.
– Następnie tworzymy dodatkową kolumnę „Kategoria zakupowa” (w naszym przypadku kolumna M) i tutaj wpisujemy dla wszystkich pozycji danego dostawcy nazwę kategorii „Elektronika i Sprzęt IT”.
– Operację powtarzamy dla wszystkich dostawców.
– Jeśli stwierdzisz, że dla danej kategorii jest wskazana dywersyfikacja dostawców lub jeżeli będziesz chciał mieć dedykowanych dostawców dla wąskich podkategorii produktów, możesz podzielić artykuły na takie podkategorie.

Uwzględnij dane w tabeli przestawnej

Teraz stworzymy tabelę przestawną, która pomoże nam w szczegółowej analizie wydatków. Jak to zrobić?

1. Zaznaczamy pierwszą kolumnę w arkuszu, następnie używamy kombinacji klawiszy Ctrl + A. Pamiętaj, że kolumny muszą posiadać nazwy i kompletny ciąg danych (nie może być pustych rekordów).
2. Z menu górnego wybieramy zakładkę Wstawianie i opcję Tabela przestawna.
3. Tworzymy nowy arkusz, zaznaczając opcję Nowy arkusz – to tam znajdzie się nasza tabela. Jeżeli chcesz trzymać tabelę w innym, istniejącym już arkuszu, wybierasz opcję Istniejący arkusz i wybierasz lokalizację interesującego cię pliku.
4. W arkuszu powinno pojawić się okienko, kliknij wewnątrz jego obszaru.
   
5. Teraz pojawi się zakładka tworzenia tabeli przestawnej (podgląd poniżej).
   

Opis obszarów tabeli przestawnej

• FILTRY – wartości, które chcemy filtrować lub które chcemy odfiltrować.
• WIERSZE – wartości, które chcemy podsumować, analizować (np. chcemy sprawdzić, ile kosztowały produkty zakupione w danej kategorii).
• WARTOŚCI – analizowane wartości. To może być suma, ilość, iloczyn, wartość minimalna lub maksymalna naszych rekordów (np. ceny wszystkich produktów).
• KOLUMNY – to dodatkowy podział danych, jeśli chcemy mieć rozdział pomiędzy wierszami a kolumnami (np. chcemy sprawdzić, ile kosztowały produkty w danej kategorii oraz w danej walucie).

Analizuj zestawione dane

Stwórzmy teraz prosty raport – zestawienie, które pokaże kategorię zakupową, w której nasza firma ponosi największe koszty.

Przeciągamy odpowiednie wartości do jednego z wyżej opisanych obszarów tabeli. WIERSZE uzupełniamy wartościami z Kategorii produktu, a obszar WARTOŚCI danymi z kolumny Wartość netto.

Jeżeli do pola WIERSZE przeciągniemy teraz wartości z kolumny Towar – skrót, zobaczymy, jakie koszty ponieśliśmy w danej kategorii.

Jeżeli interesują nas produkty od konkretnego dostawcy, możemy uzupełnić pole FILTRY o wartości z kolumny Towar – skrót.

Proste, prawda? Przestaw się na analizę danych w zakupach firmowych.