Obrót materiałami prędzej czy później czeka każdą firmę w branży produkcyjnej. W momencie kiedy zaczynamy dużo odbierać i dużo wysyłać, prosta kalkulacja na kartce papieru przestaje być wystarczającym dokumentem potwierdzającym przyjęcia i wydania towaru. Tym bardziej sprawę skomplikuje sytuacja, w której towar zakupujemy w obcej walucie np. w Euro lub Dolarze.

Moim celem było stworzenie czytelnego i prostego arkusza kalkulacyjnego, który na pierwszy rzut oka dawałby wymierne i precyzyjne informacje dotyczące stanów materiału w magazynie, zużycia własnego w produkcji oraz stanowiłby podstawę do raportów zakupów i sprzedaży z obrotu materiałami w relacji dostawca / klient.

Oto pierwsza (podsumowująca) zakładka arkusza:



Dla każdego materiału otrzymujemy podsumowanie przyjęć / wydań oraz tendencję wzrost / spadek liczoną od początku danego miesiąca księgowego. W ostatniej rubryce znajdziemy podsumowanie wydań wewnętrznych (jeśli sami wykorzystujemy dany materiał)

Podsumowanie w moim przypadku rozbiłem na tworzywo sztuczne oraz lakier.

Drugi arkusz dokonuje szczegółowej kalkulacji przychodów / rozchodów materiału na bazie jego ceny wejściowej oraz wyjściowej oraz dwóch innych arkuszy: rejestru przyjęć i rejestru wydań



Rodzaj materiału wybierany jest z listy, dane wyjściowe generują się na podstawie wybranego rodzaju materiału, tabele są więc dynamiczne i łatwo dostosować je do swoich potrzeb. Jedyną wartością wprowadzaną ręcznie w tym arkuszu jest kurs wymiany walut dla każdego materiału osobno (jeśli wyrażony w PLN, wtedy równa się on 1). Dlaczego nie możemy wprowadzić ogólnego dziennego przelicznika walutowego? W praktyce dostawcy zagraniczni mają bowiem różne zasady przeliczania kwot faktur: zazwyczaj wykorzystując przelicznik NBP z dnia dostawy, czasem jednak jest to średnia z ostatniego kwartału bądź ostatniego roku. Dlatego wartość kursu musi być rozważona dla każdego materiału indywidualnie.

Wartość wydań wewnętrznych obliczana jest według rejestru przyjęć / wydań zewnętrznych oraz stanu magazynowego. Dodatkowo dla today() oraz today()+1 wartość wydań wewnętrznych wynosi zawsze 0. Brzmi skomplikowanie ale jest bardzo proste: skoro stan fizyczny dotyczy ostatniego dnia (today()-1), dzisiejszy stan będzie wynosił zawsze 0 lub "", a zatem arkusz stwierdzi, iż cały towar został wydany wewnętrznie. Podwójna funkcja warunkowa stanowi tu skuteczny bezpiecznik:

Karty przyjęć i wydań są do siebie podobne, karta wydań posiada dodatkowo funkcę wprowadzania odbiorcy, co pozwala w łatwy sposób sprawdzić, gdzie towar pojechał)



Podsumowanie wydań w tym samym arkuszu:



Dodatkowo z podarkuszy warto wspomnieć jeszcze o:

- Stanach magazynowych (wewnętrzna wartość inwentaryzowana dziennie pozwala dokładniej określić ile materiału zużyliśmy w toku produkcji)
- Informacji o materiale - arkusz ten zawiera wszystkie podstawowe wiadomości o każdym z materiałów wraz z miejscem pozyskania i cenami sprzedaży zakupu. Dodatkowo znajduje się tu lista firm, od których kupujemy i tych, do których towar jest sprzedawany.

Całość arkusza oparta jest na bardzo prostych ale potężnych funkcjach:

- VLOOKUP, HLOOKUP (Wyszukiwanie / indeksacja danych w pionie i poziomie)
- SUMIF (Sumowanie warunkowe)
- IF(IF = funkcja warunkowa z dwiema zmiennymi