Kartoteka magazynowa Excel w programie magazynowym WMS.net

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Kartoteka magazynowa to karta jednego indeksu: stałe dane o towarze, które nie zmieniają się z każdą dostawą. Ta strona opisuje wyłącznie ją. Pokazujemy, jakie pola w niej stoją, oraz jak zbudować ją w Excelu tak, żeby indeks się nie powtarzał. Osobno porównujemy te pola z formularzem asortymentu w Studio WMS.net. Cały skoroszyt magazynu z arkuszami przychodów, rozchodów i stanów opisuje strona o programie magazynowym w Excelu, a decyzję o odejściu od arkusza strona zamień Excel na WMS.

Rodzaje ewidencji i dokumenty, które zmieniają stan, zbiera strona o ewidencji magazynowej, a kody EAN i etykiety strona o kodach kreskowych w magazynie. Numer GTIN trzeba aktywować w GS1, zanim trafi do kolumny EAN, co opisuje strona o aktywacji numeru GTIN. Tabela na końcu zestawia te strony i podpowiada, którą otworzyć dalej.

O czym piszemy na tej stronie?

1. Czym jest kartoteka magazynowa i jak działa w Excelu?
2. Jakie dane powinna zawierać kartoteka magazynowa?
3. Jak stworzyć kartotekę magazynową w Excelu - praktyczny poradnik
4. Jak ta sama kartoteka wygląda w Studio WMS.net?
5. Kartoteka na papierze, w arkuszu i w systemie - co się zmienia?
6. Excel jako narzędzie uzupełniające system WMS
7. Którą stronę o kartotece i Excelu otworzyć dalej?
8. Excel czy Studio WMS.net? Najczęstsze pytania i odpowiedzi

Czym jest kartoteka magazynowa i jak działa w Excelu?

Rola kartoteki w ewidencji towarowej

Kartotekę definiuje się jako uporządkowany zbiór danych o każdym asortymencie: jego parametry i stan ilościowy. Karty prowadzi się odrębnie dla każdego numeru SKU albo indeksu materiałowego, dzięki czemu stany ilościowe i wartościowe da się śledzić osobno dla każdej pozycji. Jest to potrzebne do prawidłowego rozliczania zapasów. Każde przyjęcie i każde wydanie zmienia stan tylko jednego indeksu, więc brak towaru i pomyłka przy kompletacji wychodzą na jaw przy tej konkretnej karcie.

Kartoteka papierowa i kartoteka w Studio WMS.net różnią się bezpieczeństwem i szybkością dostępu do informacji. Dokumenty analogowe łatwo zniszczyć, a szuka się w nich ręcznie. Kartoteka w bazie MS SQL Server pozwala filtrować pozycje po kolumnach, a stany zmieniają się po skanowaniu kodów kreskowych na terminalach Android. Praca na jednej bazie wyklucza też kilka wersji tego samego zestawienia, co często dezorganizuje małe magazyny prowadzone na papierze albo w plikach.

W arkuszu najłatwiej pomylić dwie rzeczy: kartotekę i stan. Kartoteka mówi, czym jest towar: indeks, nazwa, jednostka, EAN, cena ewidencyjna, zapas minimalny. Stan mówi, ile towaru jest i gdzie leży: ilość, lokalizacja, numer partii, termin ważności. Pierwsze zmienia się rzadko i mieści się w jednym wierszu na indeks. Drugie zmienia się przy każdym dokumencie, więc w dobrze zbudowanym skoroszycie leży w osobnym arkuszu ruchów, a nie w kolumnach kartoteki. Ten podział obowiązuje tak samo w Excelu, jak w systemie klasy WMS.

Stałe progi z kartoteki zasilają potem zarządzanie zapasami, a sama karta jest punktem odniesienia dla całej logistyki magazynowej. Rozbudowany opis kartoteki w programie, z kolumnami i zakładkami, ma strona o kartotekach magazynowych w systemie WMS, a ogólny przegląd tematu strona o kartotekach magazynowych.

Jakie dane powinna zawierać kartoteka magazynowa?

Dane niezbędne w kartotece magazynowej

Nazwa i unikalny kod produktu tworzą identyfikator pozycji w całym łańcuchu dostaw, a pozostałe pola opisują już ten jeden indeks. Tabela zestawia pola kartoteki, a trzecia kolumna wskazuje, gdzie to samo pole stoi w formularzu Studio WMS.net. Schemat pod tabelą pokazuje, które dane zostają w kartotece, a które należą do stanu.

Kod EAN pozwala skanować towar na terminalu Android i eliminuje pomyłki przy wpisywaniu, więc w kartotece wystarcza sam numer. Numer partii i numer seryjny opisują już konkretną dostawę albo sztukę, dlatego odpowiadają im osobne strony o numerze partii i o numerach seryjnych. Dzięki partii można w razie wykrycia wady wycofać z obrotu konkretną serię, a terminy ważności ustawiają kolejność wydań, co opisuje strona o partiach i datach ważności.

Data przyjęcia i data wydania są potrzebne do rotacji według strategii FIFO, a lokalizacja do szybkiego odnalezienia towaru, ale wszystkie zapisuje się przy ruchu, a nie przy indeksie. Informacja o dostawcy i producencie przy indeksie pozwala szybko sprawdzić pochodzenie partii przy reklamacji. Cena jednostkowa służy do bieżącej wyceny zapasów i kontroli rentowności, a sposób jej użycia ustala wdrożenie i księgowość. Jeden indeks odpowiada zawsze jednemu towarowi, a przegląd kartoteki warto robić po każdym spisie.

Jak stworzyć kartotekę magazynową w Excelu - praktyczny poradnik

Instrukcja budowy kartoteki magazynowej w programie Excel

Skoroszyt kartoteki składa się z dwóch arkuszy. Pierwszy, Kartoteka, ma jeden wiersz na indeks. Drugi, Słowniki, trzyma listy jednostek miary i grup towarowych, z których korzystają listy wyboru. Ruchy towaru dodaje się w trzecim arkuszu, ale to temat strony o programie magazynowym w Excelu, więc tutaj zostajemy przy samej karcie.

W pierwszym wierszu arkusza Kartoteka wpisuje się nagłówki: Indeks, Nazwa, J.m., Grupa, EAN, Cena ewidencyjna, Zapas min, Zapas max, Seria i Data ważności. Starsze poradniki dodają do kartoteki jeszcze Ilość, Lokalizację, Datę przyjęcia i Wartość. Przy kilkudziesięciu indeksach to działa, ale ilość trzeba wtedy poprawiać ręcznie przy każdym ruchu, więc te cztery kolumny lepiej liczyć z arkusza ruchów. Ustalenie takiej struktury daje ład informacyjny i szybkie odnajdywanie towaru na regale. Zakres danych warto sformatować jako tabelę, bo wtedy filtry i reguły poprawności rozszerzają się na nowe wiersze, a sortowanie po dowolnej kolumnie jest jednym kliknięciem.

Formaty komórek ustawia się przed wpisaniem danych. Kolumna EAN dostaje format tekstowy, bo Excel usuwa z liczby zera wiodące i często pokazuje ją w zapisie wykładniczym. Kolumny cen dostają format walutowy. Najważniejsza jest jednak poprawność danych, która ogranicza błędy przy wpisywaniu: listy rozwijane dla jednostek i grup oraz reguły długości i liczby dla EAN i cen. Tabela zestawia reguły dla poszczególnych kolumn.

Unikalność indeksu to najważniejsza reguła arkusza, bo dwa wiersze tego samego towaru dają dwa stany i żaden z nich nie jest prawdziwy. Zakres w formule jest przykładowy i trzeba go zmienić na rzeczywistą liczbę wierszy. Formuły wpisuje się w polu Formuła reguły niestandardowej, a odwołanie dotyczy pierwszej komórki zakresu:

Indeks, komórka A2:
=ORAZ(LICZ.JEŻELI($A$2:$A$5000;A2)=1;DŁ(A2)=DŁ(USUŃ.ZBĘDNE.ODSTĘPY(A2)))

EAN, komórka E2 (13 cyfr i cyfra kontrolna):
=ORAZ(DŁ(E2)=13;MOD(10-MOD(SUMA.ILOCZYNÓW(--FRAGMENT.TEKSTU(LEWY(E2;12);WIERSZ($1:$12);1);1+2*(MOD(WIERSZ($1:$12);2)=0));10);10)=--PRAWY(E2;1))

Druga formuła liczy cyfrę kontrolną z pierwszych dwunastu cyfr: cyfry na pozycjach nieparzystych mają wagę 1, na parzystych wagę 3, a cyfra kontrolna uzupełnia sumę do pełnej dziesiątki. Sprawdza więc tylko, czy numer jest poprawnie zbudowany. Nie sprawdza, czy numer przydzielono firmie i aktywowano, a to opisuje strona o aktywacji numeru GTIN.

Poprawność danych ma jedną słabość: wklejenie komórek z innego pliku może reguły nadpisać. Dlatego kolumnę Indeks uzupełnia się formatowaniem warunkowym z regułą Duplikaty wartości, a drugie formatowanie podświetla na czerwono wiersze, w których stan z arkusza ruchów spadł poniżej zapasu minimalnego. Pozwala to terminowo uzupełniać braki. Nagłówki zamraża się poleceniem Zablokuj panele, a arkusz Słowniki chroni się przed edycją poleceniem Chroń arkusz. Gotowy skoroszyt zapisuje się jako szablon, żeby każdy nowy pracownik korzystał z tej samej struktury.

Jeśli kartoteka ma kiedyś trafić do bazy Studio WMS.net, warto od początku trzymać się zasad pliku importu, które opisuje strona o kartotekach z Excela:

• plik w formacie XLS, a dane w zakładce o nazwie Arkusz1;
• nazwy kolumn w pierwszym wierszu zgodne ze strukturą tabel bazy, bez kolumn, których baza nie zna;
• kolumny logiczne jako 0 i 1, a daty w formacie RRRR-MM-DD; liczby zapisuje się samymi cyframi z przecinkiem dziesiętnym;
• unikalność zapisu wymusza indeks razem z rodzajem kartoteki (kolumna PRX) i symbolem magazynu.

Nazwy kolumn w przykładzie powyżej są opisowe. Przed importem trzeba je zamienić na nazwy z bazy danej wersji programu, a sprawdza się je w dokumentacji wersji. Migrację całej bazy kartotek i kontrahentów przy wdrożeniu, razem z walidacją przed zapisem, opisuje strona o imporcie danych z Excela.

Jak ta sama kartoteka wygląda w Studio WMS.net?

Formularz nowego asortymentu składa się z trzech zakładek: 1. Dane ewidencyjne, 2. Dane uzupełniające i 3. Inne. Nad formularzem stoją przyciski Anuluj i Zapisz oraz Opis, Uwagi, Zdjęcia, Pliki i Załączniki. Z lewej widać menu sekcji NOWY z pozycjami Asortyment i Kontrahent oraz kolejne grupy menu. Poniższe zrzuty pokazują nowy formularz z wartościami domyślnymi, więc większość pól jest pusta albo ma wartość zerową.

Zakładka Dane ewidencyjne

Na pierwszej zakładce stoją pola SKU (na zrzucie wypełnione już numerem), Numer Artykułu, Numer SAP, Nazwa, Opis i EAN z ikoną lupy. Pod nimi są listy Jednostka miary, Kontrahent, Dostawca i Producent oraz pole Aktywne, zaznaczone w nowym formularzu. W arkuszu odpowiada im kilka kolumn, ale w formularzu jednostkę i kontrahenta wybiera się z list, a nie wpisuje z klawiatury. Według strony o kartotekach w systemie WMS SKU nadaje system, a strona o kartotekach magazynowych wspomina też o przejęciu indeksu z systemu ERP. Przy automatycznym nadawaniu numeru nie ma potrzeby wymuszać unikalności regułą poprawności, jak w arkuszu.

Przy imporcie z pliku o unikalności zapisu decyduje indeks razem z rodzajem kartoteki i symbolem magazynu, więc ten sam indeks może wystąpić w innym magazynie. Pole Aktywne zastępuje kasowanie wiersza: pozycja wycofana z użycia jest blokowana, a nie usuwana, bo kartoteka ma historię obrotów, do której odnoszą się dokumenty. W Excelu analogiczną rolę pełni kolumna Aktywny zamiast usuwania wiersza.

Zakładka Dane uzupełniające

Druga zakładka ma sześć ramek: Wymiary jednostki (wysokość, szerokość, długość i objętość), Pakowanie (opakowanie jednostkowe, ilość w opakowaniu zbiorczym, ilość na palecie i rodzaj palety), Składowanie (składowanie piętrowe i ilość warstw), dwie ramki z wagą netto i brutto opakowania jednostkowego oraz zbiorczego i Definicje zapasów. W tej ostatniej stoją domyślna lokacja oraz zapas minimalny, maksymalny i optymalny. Według strony o kartotekach magazynowych z tych pól korzysta dobór lokalizacji i wydruk etykiety, a progi zapasu zasilają kontrolę stanów magazynowych. Arkusz zwykle nie ma odpowiednika tej zakładki, więc dobór miejsca na regale zależy wtedy od wiedzy magazyniera.

Zakładka Inne

Trzecia zakładka zaczyna się od pól wyboru Wymagana seria i Wymagana data ważności, które na zrzucie są niezaznaczone. Poniżej stoją lista Grupa towarowa oraz pola Numer MPK, Kod PCN, Link do zdjęcia z ikoną lupy i Uwagi. Dwie ramki na dole to Cechy, z trzema polami na cechy własne, oraz Ceny z polami Cena ewidencyjna, Cena netto, Cena brutto i Cena zakupu. Pola wyboru ustawiają tylko wymóg: wartości numeru partii i terminu wpisuje się przy przyjęciu, bo każda dostawa może mieć inne.

Numer SAP, Numer MPK, Kod PCN, trzy cechy własne i link do zdjęcia to pola, których prosty arkusz zwykle nie ma. Można je dodać jako kolumny, ale każda kolejna wymaga własnej reguły poprawności i dopisania do pliku importu. Zakres pól w konkretnej wersji programu warto potwierdzić w dokumentacji tej wersji.

Kartoteka na papierze, w arkuszu i w systemie - co się zmienia?

Ta sama kartoteka może istnieć na papierze, w arkuszu i w systemie. Tabela zestawia, co zmienia się w sposobie pracy z kartą towaru. Kolumna o Excelu opisuje arkusz zbudowany według tej strony, a nie arkusz bez żadnych reguł.

Cztery sygnały mówią, że kartoteka w arkuszu przestaje wystarczać:

• dwie osoby edytują dwie kopie pliku i stany indeksu się rozjeżdżają;
• towar trzeba skanować na hali, a arkusz kończy się na wpisywaniu z klawiatury;
• partie i terminy ważności mają decydować o wydaniu, a kartoteka w arkuszu niczego nie wymusza;
• ten sam towar pojawia się pod dwoma indeksami mimo reguł poprawności, bo ktoś wkleił dane z innego pliku.

Kryteria i próg decyzji opisuje strona Zamień Excel na WMS, a przegląd zastosowań arkusza w magazynie strona o Excelu w zarządzaniu magazynem małej i średniej firmy. Na tej stronie wystarczy wniosek: kartoteka w arkuszu działa przy małym asortymencie i jednej osobie przy klawiaturze, a każdy z czterech sygnałów to powód, by przenieść ją do systemu WMS.

Excel jako narzędzie uzupełniające system WMS

Analityka w arkuszu na danych z WMS

Po wdrożeniu systemu Excel nie znika, tylko przestaje być rejestrem stanów. Studio WMS.net eksportuje dane do formatów tabelarycznych, więc analitycy przetwarzają zestawienia w znanym środowisku bez obciążania bazy operacyjnej. Arkusz przydaje się do raportów ad hoc oraz do symulacji planowania zapotrzebowania w okresach szczytowych, a dane wyjściowe pochodzą z kartotek i ruchów zapisanych w systemie. Wykresy i zestawienia dla zarządu powstają z nich szybko, a filtrowanie wyeksportowanych danych pomaga w kontroli kosztów surowców i gospodarki opakowaniami.

Wskaźniki KPI i trendy rotacji liczy się na danych historycznych z modułów WMS, a testowanie wariantów nie ryzykuje bieżących operacji, bo arkusz pracuje na kopii. Zasady eksportu i integracji z arkuszem opisuje strona WMS excel.

Którą stronę o kartotece i Excelu otworzyć dalej?

Serwis ma kilka stron o arkuszu i kartotece, a każda odpowiada na inne pytanie. Ta strona opisuje samą kartotekę w Excelu, a tabela pokazuje, do czego służą pozostałe.