Excel dla menedżera. Casebook - ebook

6. Selekcja asortymentu do produkcji na magazyn w Karpol sp. z o.o.

Definicja problemu

Spółka Karpol została założona w 1994 roku. Jest jedną z największych w Polsce i liczącą się w Europie firmą produkującą systemy wentylacyjne. Swoje produkty przedsiębiorstwo dostarcza bezpośrednio na place budów (obiekty sportowe, biurowce, budynki użyteczności publicznej), jak również hurtowniom wentylacyjnym oraz klientom indywidualnym, zwłaszcza z rynku lokalnego.

Presja rynku sprawia, że w Karpolu podejmowane są działania m.in. w obszarze logistycznej obsługi klienta, mające na celu utrzymanie pozycji konkurencyjnej.

Jednym z kluczowych elementów logistycznej obsługi klienta jest cykl realizacji zamówienia, czyli czas między złożeniem zamówienia a dostawą produktu.

Ze względu na stosowany w Karpolu model produkcji (produkcja na zamówienie) cykl realizacji zamówienia obejmuje również etap produkcji. Takie rozwiązanie jest ekonomicznie uzasadnione, a co najważniejsze, w przypadku dużych zleceń składanych przez firmy realizujące inwestycje budowlane, satysfakcjonuje klientów. Jednakże w niektórych sytuacjach klienci oczekują natychmiastowej realizacji zamówienia, a gdy okazuje się to niemożliwe, składają zamówienie u konkurencji. Jest to szczególnie widoczne w przypadku dwóch typów zamówień, które z różnych powodów są ważne dla firmy.

Pierwszy typ to zamówienia składane przez klientów indywidualnych. Atrakcyjność tych zleceń wynika z faktu, że w przypadku klientów indywidualnych stosowane są wyższe marże, a liczba tego typu zleceń stale rośnie.

Drugim typem są zamówienia, które tutaj nazywać będziemy uzupełniającymi. Zamówienia uzupełniające to dodatkowe zamówienia składane przez firmy realizujące inwestycje budowlane. Firmy te składają początkowo duże zamówienie, kierując się projektem budowlanym. Jednak w trakcie realizacji inwestycji, w wyniku zmian w projekcie, uszkodzenia lub z innych przyczyn pojawia się konieczność zamówienia dodatkowych elementów. W takich sytuacjach czas realizacji zamówienia odgrywa istotną rolę i jeśli towar nie jest dostępny od ręki, klienci szukają innych dostawców. Na ogół nie są to duże zamówienia, a co za tym idzie ich utrata nie jest szczególnie istotna. Ważniejsze w tym przypadku jest to, że są to zamówienia kluczowych dla firmy klientów, którzy w przyszłości mogą z tego powodu całkowicie zrezygnować z usług Karpolu.

Ze względu na opisane tu dwa typy zamówień postanowiono w Karpolu skrócić czas realizacji zamówienia dla najlepiej rotujących produktów. Podjęto decyzję, że produkty te będą produkowane na magazyn, a nie jak dotychczas wyłącznie na zamówienie. Dzięki temu będą one stale dostępne, a składane na nie zamówienia będzie można bezzwłocznie zrealizować.

Opracowanie listy asortymentu produkowanego na magazyn powierzono kierownikowi działu handlowego, który kierując się doświadczeniem, dokonał selekcji 68 towarów cechujących się dużą rotacją oraz wyznaczył dla nich maksymalne i minimalne stany magazynowe.

Jednocześnie podjęto próbę opracowania metody selekcji asortymentu do produkcji na magazyn i wyznaczania maksymalnych i minimalnych stanów magazynowych, którą będzie można wspomagać proces decyzyjny w przyszłości. Poniższy przykład ilustruje wyniki tych prac.

Procedura selekcji asortymentu do produkcji na magazyn

Jak wynika z przedstawionej charakterystyki problemu, w celu selekcji asortymentu do produkcji na magazyn bardziej istotna od wielkości składanych zamówień jest ich częstotliwość (klienci indywidualni na ogół zamawiają niewielkie ilości). Dlatego też przyjęto, że pierwszym kryterium selekcji będzie średnia miesięczna częstotliwość zamówień c_(i) (gdzie i = 1, …, n; n – liczba pozycji). Wstępnie ustalono też wartość tego kryterium: c_(i) ≥ 3.

Ponadto uzgodniono, że drugim kryterium powinna być średnia miesięczna wielkość zamówień w_(i), a to dlatego, by wyeliminować zlecenia produkcyjne wystawiane na zaledwie kilka sztuk towaru. Przyjęto, że: w_(i) ≥ 10.

Postanowiono również, że pod uwagę nie należy brać produktów, które mają wysoki współczynnik zmienności wielkości zamówień V_(wi), co w skrajnym przypadkach może oznaczać, że produkt jest niezamawiany nawet przez kilka miesięcy, po czym składane jest duże zamówienie znacznie przekraczające stan magazynu. W takich przypadkach produkcja na magazyn nie spełnia swojej funkcji. Wstępnie ustalono, że V_(wi) < 90%.

Jak zaznaczono, wartości poszczególnych kryteriów zostały ustalone wstępnie i mogą ulec zmianie, jeśli okaże się, że np. lista produktów jest zbyt długa.

Dla produktów, które spełniają powyższe kryteria, a więc będą produkowane ma magazyn, ustalono maksymalne (Z_(max)) i minimalne (Z_(min)) stany zapasów:

Użycie we wzorach znaku przybliżenia, wynika z przyjęcia jeszcze jednego założenia, że wartości Z_(max) i Z_(min) będą zaokrąglane w górę do najbliższej wartości będącej wielokrotnością 5.

Selekcja asortymentu do produkcji na magazyn i wyznaczenie stanów magazynowych według przedstawionych założeń wymaga realizacji następującej procedury.

Przygotowujemy dane w postaci tabeli, w której pierwsza kolumna zawiera identyfikatory pozycji asortymentowych P_(i). W kolejnych kolumnach umieszczamy miesięczne częstotliwości zamówień c_(ij) (j = 1,…, m) za trzy ostatnie lata i dalej miesięczne wielkości zamówień w_(ij) (w sztukach) za ten sam okres.

Dla każdej pozycji asortymentu obliczamy w dwóch kolejnych kolumnach tabeli średnią częstotliwość zamówień:

oraz współczynnik zmienności:

gdzie:

W analogiczny sposób postępujemy z danymi o miesięcznych wielkościach zamówień. Najpierw obliczamy średnią:

a następnie współczynnik zmienności:

gdzie:

Na koniec dokonujemy selekcji asortymentu do produkcji na magazyn oraz wyznaczamy maksymalne i minimalne stany magazynowe zgodnie z przedstawionymi na wstępie założeniami. Jeśli lista wyselekcjonowanych produktów jest zbyt długa (ponad 70 pozycji), modyfikujemy przyjęte wartości kryteriów.

Implementacja

Dane potrzebne do obliczeń zostały importowane do skoroszytu Produkcja na magazyn – DANE.xlsx z systemu informatycznego przedsiębiorstwa. W arkuszu WZ (patrz rysunek 6.1) znajdują się dane o wielkości zamówień za okres od stycznia 2008 roku do grudnia 2010 roku. Każdy wiersz tabeli odpowiada jednej pozycji dokumentu WZ i zawiera kolejno: kod produktu (KOD), liczbę wydanych sztuk (ILOŚĆ) oraz datę wydania (DATA). Dane uporządkowane są rosnąco według daty wydania oraz kodu produktu.

■ Rys. 6.2. Arkusz KDM

Ponieważ na przestrzeni analizowanego okresu, z różnych powodów, niektóre kody uległy modyfikacji, przed przystąpieniem do obliczeń konieczne jest ich ujednolicenie. W przeciwnym razie otrzymamy błędne wyniki analizy, gdyż ten sam produkt będzie występował jako dwie różne pozycje asortymentowe.

W arkuszu KDM (patrz rysunek 6.2) znajdują się szczegółowe informacje na temat koniecznych do przeprowadzania modyfikacji. Tabela Kody towarów do modyfikacji (kolumny A:B) zawiera listę kodów, które należy zmienić, zastępując kod z kolumny STARY kodem z kolumny NOWY. W drugiej tabeli tego arkusza Inne modyfikacje (kolumny D:E) znajdują się dodatkowo trzy reguły, definiujące sposób dokonania kolejnych zmian. W pierwszej kolumnie (kod rozpoczynający się:) podane zostało, których kodów dotyczy dana reguła, natomiast w drugiej, jak należy je zmodyfikować.

■ Rys. 6.1. Arkusz WZ

Modyfikację kodów można przeprowadzić na kilka sposobów, np. pisząc makro lub też jedną złożoną formułę. Jednak najprostszym rozwiązaniem wydaje się przeprowadzenie modyfikacji w czterech krokach: zastąpienie podanych starych kodów nowymi, zastosowanie kolejno trzech podanych reguł. W tym celu tabelę z arkusza WZ uzupełniamy o cztery dodatkowe kolumny (KOD1, KOD2, KOD3 i KOD4), w których umieszczone zostaną wyniki realizacji kolejnych kroków:

• W pierwszej kolumnie wpisujemy formułę, która sprawdza, czy KOD danej pozycji asortymentowej występuje w tabeli kody towarów do modyfikacji. Jeśli wynik wyszukiwania jest pozytywny, w kolumnie KOD1 wpisujemy nowy kod, a jeśli negatywny – przepisujemy symbol z kolumny KOD.

• W drugiej kolumnie sprawdzamy, czy kod produktu (tym razem z kolumny KOD1) rozpoczyna się od „Z-” i nie kończy się „/O”. Jeśli tak, to w kolumnie KOD2 wpisujemy symbol z kolumny KOD1 uzupełniony o „/O”. Jeśli nie, to przepisujemy niezmieniony symbol z KOD1.

• W trzeciej kolumnie sprawdzamy, czy kod produktu (KOD2) rozpoczyna się od „WFR-”. W przypadku gdy wynik jest pozytywny, w kolumnie KOD3 wpisujemy symbol z KOD2, w którym „WFR-” zastąpione jest tekstem „WFRS-”, w przeciwnym razie przepisujemy niezmieniony symbol z KOD2.

• Z kolumną czwartą postępujemy analogicznie jak z trzecią: sprawdzamy, czy kod produktu (KOD3) rozpoczyna się od „RSDS-”. W przypadku gdy wynik jest pozytywny, w kolumnie KOD4 wpisujemy symbol z KOD3, w którym „RSDS-” zastąpione jest tekstem „RSD-”, w przeciwnym razie przepisujemy niezmieniony symbol z KOD3.

Przed wprowadzeniem opisanych tu formuł dobrze jest posortować tabelę WZ według kolumny KOD. Dzięki temu łatwiej będzie nam odszukać modyfikowane pozycje i sprawdzić, czy wpisywane formuły działają poprawnie. Po wprowadzeniu wszystkich formuł tabelę WZ sortujemy rosnąco według pól DATA oraz KOD4.

1. Otwórz plik Selekcja asortymentu – DANE.xlsx.

2. Upewnij się, że kursor znajduje się w komórce A1 arkusza WZ (lub dowolnej innej komórce tabeli w arkuszu WZ).

3. Kliknij pozycję Narzędzia główne > Sortuj i filtruj > Sortowanie niestandardowe (G > Y > T).

4. W oknie dialogowym Sortowanie w obszarze Kolumna w polu Sortuj według wybierz KOD.

5. W obszarze Kolejność wybierz opcję Od A do Z.

6. Kliknij przycisk OK.

7. W komórkach D1:G1 wpisz kolejno nagłówki kolumn KOD1, KOD2, KOD3 i KOD4.

8. W komórkach D2:G2 wpisz kolejno formuły:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;KDM!$A$4:$B$89;2; FAŁSZ);A2)

=JEŻELI(ORAZ(LEWY(D2;2)=”Z-”;PRAWY(D2;2)<>”/O”);D2&”/O”; D2)

=JEŻELI(LEWY(E2;4)=”WFR-”;ZASTĄP(E2;1;4;”WFRS-”);E2)

=JEŻELI(LEWY(F2;5)=”RSDS-”;ZASTĄP(F2;1;5;”RSD-”);F2)

9. Przekopiuj formuły do pozostałych wierszy kolumn D:G. Po skopiowaniu możesz odszukać w tabeli dowolny kod, który miał ulec modyfikacji, i zweryfikować poprawność działania formuły. Czynność weryfikacji należy powtórzyć dla każdej kolumny (E, F, G).

10. Kliknij na dowolnej komórce znajdującej się w tym arkuszu tabeli (np. A1).

11. Kliknij ponownie pozycję Narzędzia główne > Sortuj i filtruj > Sortowanie niestandardowe (G > Y > T).

12. W oknie dialogowym Sortowanie w obszarze Kolumna w polu Sortuj według wybierz DATA.

13. W obszarze Kolejność wybierz opcję Od najstarszych do najnowszych.

14. Kliknij przycisk Dodaj poziom.

15. W nowym wierszu w obszarze Kolumna w polu Następnie według wybierz KOD4.

16. W obszarze Kolejność wybierz opcję Od A do Z.

17. Kliknij przycisk OK.

Mając ujednolicone kody produktów, możemy przystąpić do obliczeń. Zaczynamy od sporządzenia zestawienia według charakterystyki przedstawionej w części „Procedura selekcji asortymentu do produkcji na magazyn”. W nowym arkuszu, który nazwiemy TP, wstawiamy tabelę przestawną dla danych z arkusza WZ.

1. Kliknij dowolną komórkę w tabeli znajdującej się w arkuszu WZ.

2. Kliknij pozycję Wstawianie > Tabela przestawna (V > TA).

3. W oknie dialogowym Wstawianie tabeli przestawnej sprawdź, czy prawidłowo został wpisany zakres tabel z danymi do analizy (WZ!$A$1:$G$58365) oraz czy zaznaczona jest pozycja Nowy arkusz.

4. Kliknij przycisk OK.

Wstawiony zostanie nowy arkusz zawierający narzędzia do budowy raportu tabeli przestawnej.

5. Zmień nazwę nowo utworzonego arkusza na TP.

W obszarze wierszy tabeli przestawnej umieszczamy pole KOD4, natomiast w obszarze kolumn pole DATA. Następnie ustawiamy grupowanie pól w obszarze kolumn według miesięcy i lat.

W obszarze wartości umieszczamy pola KOD4 oraz ILOŚĆ. Ponieważ pierwsze z tych pól jest traktowane przez tabelę przestawną jako tekstowe (zawiera kody produktów), zamiast sumowania wartości następuje zliczanie wystąpień (Licznik z KOD4). Tym samym obliczana jest częstotliwość zamówień, czyli liczba wierszy tabeli WZ z danym kodem towaru i datą zawierającą się w danym miesiącu i roku.

1. Z listy Pola tabeli przestawnej przeciągnij kolejno: pole KOD4 do obszaru WIERSZE, pole DATA do obszaru KOLUMNY.

2. Kliknij na dowolnej dacie w nagłówku tabeli przestawnej.

3. Kliknij pozycję Narzędzia Tabel Przestawnych > Analiza > Grupuj pole (JW > G).

4. W oknie dialogowym Grupowanie w polu Według zaznacz Miesiące oraz Lata, pozostałe ustawienia pozostaw domyślne.

5. Kliknij przycisk OK.

6. Z listy Pola tabeli przestawnej przeciągnij pola KOD4 i ILOŚĆ do obszaru WARTOŚCI.

Do uzyskania tabeli zgodnie z opisanymi wcześniej wymaganiami konieczna jest jeszcze zmiana układu kolumn tabeli. Obecnie w tabeli przestawnej dla każdego miesiąca wyświetlane są zarówno częstotliwość (Liczba z KOD4), jak i miesięczna wielkość zamówień (Suma z ILOŚĆ), naszym celem jest, by w tabeli najpierw znajdowały się wszystkie kolumny z częstotliwością, a dopiero za nimi kolumny prezentujące miesięczną wielkość zamówień. Na koniec formatujemy tabelę przestawną tak, by w pustych komórkach pokazywana była wartość zero, a sumy końcowe wierszy i kolumn nie były wyświetlane.

1. Aby zmienić układ kolumn w tabeli przestawnej, przestaw porządek, w jakim wyświetlane są obiekty w obszarze KOLUMNY, przenosząc ∑ Wartości na początek listy.

2. Kliknij pozycję Narzędzia Tabel Przestawnych > Analiza, rozwiń przycisk Tabela przestawna, a następnie kliknij Opcje i ponownie Opcje (JW > ZT > M > O). Wyświetlone zostanie okno Opcje tabeli przestawnej, gdzie:

• na karcie Układ i formatowanie wpisujemy zero w polu Dla pustych komórek pokaż,

• na karcie Sumy i filtry odznacz (☑ > ☐) Pokaż sumy końcowe wierszy oraz Pokaż sumy końcowe kolumn,

3. Kliknij przycisk OK.

Wypełnienie pustych komórek wartości tabeli przestawnej zerami jest konieczne, gdyż funkcje arkusza, które dalej będziemy stosować (ŚREDNIA, ODCH.STANDARD.POPUL) pomijają w obliczeniach komórki zakresu, które nie posiadają wartości. Oznaczałoby to zawyżenie wartości obliczanych średnich i zaniżenie wartości odchyleń dla tych pozycji asortymentowych, na które nie było popytu w niektórych miesiącach analizowanego okresu.

Poprawnie wykonaną tabelę przestawną przedstawiono na rysunku 6.3.

■ Rys. 6.3. Arkusz TP

Dalsze obliczenia wymagają uzupełnienia tabeli przestawnej o dodatkowe kolumny. Należy jednak pamiętać, że ręczne dopisywanie kolumn do tabeli przestawnej (bez użycia narzędzi tabeli przestawnej) ogranicza możliwości przetwarzania (np. sortowania). Aby uniknąć tych problemów, kopiujemy dane z tabeli przestawnej do nowego arkusza, któremu nadajemy nazwę Obliczenia.

1. Wstaw nowy arkusz do skoroszytu przed arkusz TP.

2. Zmień nazwę wstawionego arkusza na Obliczenia.

3. Przejdź do arkusza TP.

4. Kliknij dowolną komórkę z danymi tabeli przestawnej.

5. Zaznacz całą tabelę, wciskając CTRL+A oraz CTRL+C, by skopiować zaznaczenie i umieścić je w schowku.

6. Przejdź do arkusza Obliczenia.

7. Wybierz pozycję Narzędzia główne, kliknij Wklej w grupie Schowek, a następnie w podgrupie Wklej wartości kliknij Wartości (G > V > W).

Niestety skopiowana tabela nie ma jednoznacznych nazw poszczególnych kolumn. Co prawda z czterech pierwszych wierszy możemy odczytać, czego dotyczą poszczególne kolumny, jednak pozostawienie tabeli w takiej postaci utrudni nam jej sortowanie i filtrowanie. Dlatego w pierwszym wierszu arkusza wprowadzamy nowe nazwy kolumn, a wiersze 2–4 kasujemy. Pierwszej kolumnie tabeli nadajemy nazwę KOD, natomiast następnym nazwy według schematu: XX-RR-MM. W miejsce XX wstawiamy CZ dla kolumn prezentujących częstotliwość zamówień, a WS dla kolumn zawierających dane o wielkości zamówień. W miejsce RR i MM wpisujemy odpowiedni rok i miesiąc.

1. W komórce A1 arkusza Obliczenia wpisz nazwę KOD.

2. W komórce B1 wpisz CZ-08-01.

3. Skopiuj komórkę B1 aż do komórki M1, klikając uchwyt wypełnienia prawym przyciskiem myszy, a z menu podręcznego, które się pojawi, wybierz pozycję Wypełnij serią.

4. W analogiczny sposób postępuj z następnymi nagłówkami pamiętając, by począwszy od AL1 nazwę kolumny rozpoczynać od WS.

5. Zaznacz i usuń wiersze 2–4 w arkuszu Obliczenia.

Do tak utworzonej tabeli dopisujemy cztery kolejne kolumny, w których obliczamy średnią i współczynnik zmienności dla częstotliwości, a następnie średnią i współczynnik zmienności dla wielkości zamówień. Kolumnom tym nadajemy następujące nazwy: ŚrCZ, WzCZ, ŚrWZ, WzWZ. Wartości średnich formatujemy jako całkowite liczbowe, natomiast wartości współczynników zmienności jako całkowite procentowe.

1. W komórkach BV1, BW1, BX1 i BY1 wpisz kolejno nagłówki kolumn ŚrCZ, WzCZ, ŚrWZ, WzWZ.

2. W komórkach BV2, BW2, BX2 i BY2 wpisz kolejno formuły:

=ŚREDNIA(B2:AK2)

=ODCH.STANDARD.POPUL(B2:AK2)/BV2

=ŚREDNIA(AL2:BU2)

=ODCH.STANDARD.POPUL(AL2:BU2)/BX2

3. Skopiuj wpisane formuły do pozostałych wierszy tabeli.

4. Zaznacz kolumny BV i BX.

5. Na zaznaczonym obszarze kliknij prawym przyciskiem myszy i z menu podręcznego wybierz Formatuj komórki. W oknie dialogowym Formatowanie komórek na karcie Liczby wybierz Kategorię Liczbowe, a w polu Miejsca dziesiętne wpisz zero.

6. Kliknij przycisk OK.

7. Analogicznie postępuj z kolumnami BW i BY z tą różnicą, że na karcie Liczby wybierz Kategorię Procentowe.

Ponieważ utworzona tabela zawiera aż 77 kolumn, to nie możemy jednocześnie odczytać kodu towaru i wartości obliczonych przez nas miar. Aby usunąć tę niedogodność, możemy skorzystać z jednej z trzech możliwości: ukryć kolumny z miesięczną częstotliwością i wielkością zamówień, włączyć podział umożliwiający jednoczesne oglądanie oddalonych od siebie części arkusza lub też zablokować pierwszą kolumnę arkusza. Trzecie rozwiązanie wydaje się tu najwygodniejsze, gdyż zapewnia nam dostęp do danych z poszczególnych miesięcy. Dlatego włączymy opcję blokady pierwszego wiersza arkusza.

1. Wybierz pozycję Widok, Zablokuj okienka, Zablokuj pierwszą kolumnę w grupie Okno (O > B > P). Zablokowana kolumna jest oddzielona wyraźniejszą linią granicy pomiędzy kolumną A i B.

2. W prawej części arkusza (niezablokowanej) przesuwamy suwak tak, by widoczne były kolumny BV–BY.

Należy pamiętać, że jeśli w tabeli, w której mamy włączone filtrowanie dopiszemy nową kolumnę i wypełniamy ją formułami (lub wartościami), to zostaną one wpisane jedynie do tych wierszy tabeli, które są aktualnie wyświetlane (spełniają kryterium filtrowania). Z tego powodu wygodniej jest najpierw dopisać wszystkie potrzebne formuły, a dopiero potem dokonać selekcji asortymentu do produkcji na magazyn. A zatem najpierw dopisujemy dwie kolumny o nagłówkach ZMin i ZMax, w których dla każdej pozycji asortymentowej obliczamy minimalne i maksymalne stany zapasów, a dopiero potem włączamy sortowanie i filtrowanie (według opisu z części „Procedura selekcji asortymentu do produkcji na magazyn”).

1. W komórkach BZ1, CA1 wpisz kolejno nagłówki kolumn ZMin i ZMax.

2. W komórkach BZ1, CA1 wpisz kolejno formuły:

=ZAOKR.W.GÓRĘ(BX2*0,2;5)

=ZAOKR.W.GÓRĘ(BX2;5)

3. Skopiuj wpisane formuły do pozostałych wierszy tabeli.

4. Kliknij dowolną komórkę tabeli znajdującej się w tym arkuszu.

5. Kliknij pozycję Narzędzia główne > Sortuj i filtruj > Sortowanie niestandardowe (G > Y > T).

6. W oknie dialogowym Sortowanie w obszarze Kolumna w polu Sortuj według wybierz ŚrWZ.

7. W obszarze Kolejność wybierz opcję Od największych do najmniejszych.

8. Kliknij przycisk OK.

9. Kliknij pozycję Narzędzia główne > Sortuj i filtruj > Filtruj (G > Y > F). Przy każdym nagłówku tabeli wyświetlony zostanie przycisk menu filtrowania

.

10. Wprowadź kolejno trzy kryteria filtrowania. Kliknij przycisk menu filtrowania przy nagłówku ŚrCZ.

11. Kliknij Filtry liczb, a następnie Większe niż lub równe.

12. W oknie dialogowym Autofiltr niestandardowy w polu, w którym znajduje się kursor, wpisz liczbę 3.

13. Kliknij przycisk OK.

14. W analogiczny sposób postępuj z kolumną ŚrWZ (gdzie wybierz Większe niż lub równe i wpisz liczbę 10) oraz z kolumną WzWZ (gdzie wybierz Mniejsze niż i wpisz 90%).

Rysunek 6.4 przedstawia gotową listę asortymentu do produkcji na magazyn z arkusza Obliczenia. Ponieważ na liście znajduje się 59 pozycji, przyjęte na wstępie kryteria nie wymagają modyfikacji.

■ Rys. 6.4. Lista asortymentu do produkcji na magazyn

Zadania do samodzielnego wykonania

Zadanie 1.

Utwórz kopię arkusza Obliczenia i nazwij ją Z1. W arkuszu Z1 sporządź listę asortymentu do produkcji na magazyn dla następujących wartości kryteriów: c_(i) > 5, w_(i) > 15, V_(wi) < 80%. Ile produktów znalazło się na nowej liście?

Zadanie 2.

W kolumnach KOD3 i KOD4 (arkusz WZ) znajdują się formuły, w których wykorzystano funkcję ZASTĄP niezgodnie z jej podstawowym przeznaczeniem. Korzystając z Pomocy MS Excel, znajdź wśród funkcji tekstowych funkcję właściwą do realizacji tego zadania, po czym wykorzystując tę funkcję, wpisz nowe formuły w obu tych kolumnach.

Zadanie 3.

W arkuszu NGT znajduje się tabela zwierająca w dwóch kolejnych kolumnach symbole i nazwy wszystkich grup asortymentu. Dopisz trzecią kolumnę o nazwie ILPOZ i korzystając z danych z arkusza Obliczenia, oblicz liczbę pozycji asortymentowych należących do każdej z grup. Ile jest grup asortymentowych liczących ponad pięćset pozycji?

Zadanie 4.

W arkuszu NGT dopisz cztery kolejne kolumny: 2008, 2009, 2010, Razem i korzystając z danych z arkusza WZ, oblicz wielkość produkcji w poszczególnych grupach asortymentowych w kolejnych latach oraz w całym analizowanym okresie. Do obliczenia produkcji w kolejnych latach zastosuj funkcję SUMA.WARUNKÓW, natomiast do obliczenia produkcji ogółem funkcję SUMA.JEŻELI. Wprowadzając formułę obliczeniową dla pierwszej grupy asortymentu w 2008 roku. używaj odwołań bezwzględnych i mieszanych (czyli $A$1, A$1, $A1) w taki sposób, by utworzoną formułę można było skopiować nie tylko do pozostałych wierszy, ale również do dwóch następnych kolumn (2009 i 2010).

Zadanie 5.

W nowym arkuszu o nazwie Z5 sporządź zestawienie dwudziestu pozycji asortymentu o największej sprzedaży (ilościowo) w analizowanym okresie. Oprócz sprzedaży ogółem zamieść w nim również dane o sprzedaży w poszczególnych latach. Do wykonania tego zadania posłuż się tabelą przestawną i danymi z arkusza WZ. Zwróć uwagę, aby pozycje asortymentu znajdujące się w zestawieniu były uporządkowane malejąco ze względu na sprzedaż.

Zadanie 6.

W nowym arkuszu o nazwie Z7 sporządź zestawienie dziesięciu najlepiej sprzedających się w 2010 roku typów nypli. Wyniki przedstaw w formie tabelarycznej oraz wykresu kolumnowego. Do wykonania tego zadania posłuż się tabelą i wykresem przestawnym oraz danymi z arkusza WZ.

Zadanie 7. Porównanie list asortymentu do produkcji na magazyn

Jak już wcześniej nadmieniono, aktualnie obowiązuje lista asortymentu do produkcji na magazyn, którą, kierując się swoim doświadczeniem, opracował kierownik działu handlowego. Lista ta znajduje się w arkuszu ALA. W nowym arkuszu o nazwie Z7 porównaj ją z listą utworzoną w niniejszym case study. W tym celu sporządź zestawienie, w którym znajdą się pozycje asortymentowe z obu list (również te, które występują na jednej liście, a nie występują na drugiej) wraz z danymi o minimalnych i maksymalnych stanach zapasów zarówno wg nowej, jak i aktualnie obowiązującej listy.

Podpowiedź:

Skopiuj arkusz Obliczenia i zaznacz w nim wyselekcjonowane za pomocą filtru pozycje. Wyłącz filtr i dla wszystkich pozycji asortymentu, w dwóch kolejnych kolumnach dopisz formuły, które wyszukują na liście z arkusza ALA i wstawiają (jeżeli znajdą) minimalne i maksymalne stany zapasów określone przez kierownika. Następnie w kolejnej kolumnie dopisz formułę, która zwraca 1, jeżeli pozycja asortymentu została przez Ciebie zaznaczona (czyli znajduje się na liście z arkusza Obliczenia) lub ma minimalne stany zapasów określone przez kierownika większe od zera (czyli znajduje się na liście z arkusza ALA). Na koniec włącz filtrowanie tabeli tak, by wyświetlone zostały jedynie te pozycje, które w ostatniej kolumnie mają wartość 1.