Optymalizacja Zakupów

Za pomocą Solvera można rozwiązywać problemy optymalizacyjne odpowiednio określając zmienne, warunki ograniczające i komórkę celu.

Problem

Niedawno miałem do rozwikłania w pracy ciekawą zagwozdkę. Chodziło o zakup odpowiedniej ilości produktów biorąc pod uwagę aktualną sprzedaż. Celem było to, aby ich zapasy skończyły się w tym samym czasie. Problem dodatkowo polegał na tym, że dostawca posiadał tzw. MOQ (Minimum Order Quantity), czyli minimalną ilość zamówienia na produkty określonego typu.

Tabela Wyjściowa

Screen poniżej pokazuje tabelkę wyjściową. Załóżmy, że dotyczy ona zamówienia długopisów firmy X. Długopisy sprzedawane są w sześciu różnych kolorach.

Analiza Tabeli

Przeanalizujmy zawartość poszczególnych kolumn:

• kolumna C pokazuje stan na magazynie każdego długopisu (sztuki)
• kolumna D pokazuje średnią miesięczną sprzedaż (sztuki)
• kolumna E wylicza na ile miesięcy wystarczy nam towaru przy aktualnej sprzedaży
• kolumna F to nasze zmienne, to tutaj Solver musi nam zaproponować, jaką ilość powinniśmy zamówić.
• kolumna G zawiera formułę, która z kolei pokazuje nam jak zmieni się stan magazynowy produktu po dokonaniu zakupu.
• kolumna H pokazuje natomiast na ile wystarczy nam produktu po dokonaniu zakupów. Chcąc, aby wszystkie długopisy sprzedały się mniej więcej w tym samym czasie dążymy do tego, aby wartości w tej kolumnie były identyczne lub bardzo zbliżone do siebie (minimalna różnica między maksimum a minimum).

Ten screen pokazuje nam także, że dostawca ustalił MOQ na 5000 sztuk długopisów. Tyle długopisów musi się, zatem znaleźć w zamówieniu.

Komórka pod spodem zawiera formułę sumującą ilości zamówione (kolumna F) – dla pewności, że nie zamawiamy poniżej MOQ.

Na samym dole mamy także formuły wyliczające minimalną i maksymalną wartość z kolumny H. Komórka celu to różnica pomiędzy obiema wartościami.

Parametry Solvera

Następny screen pokazuje już wyjściowe parametry Solvera.

Naszą komórką celu jest E19 (różnica). Ponieważ chcemy, aby uzyskała ona jak najniższą wartość na przycisku opcji wybieramy wartość „Min”.

W warunkach ograniczających decydujemy, że:

• suma wszystkich ilości ma być równa MOQ
• ilości mają być liczbami całkowitymi

Pozostałe parametry Solvera możemy pozostawić bez zmian.

Wyniki Końcowe

Wynik końcowy został umieszczony na screenie poniżej. Solver świetnie wykonał swoje zadanie. Biorąc pod uwagę bieżącą sprzedaż, zapasy wszystkich długopisów zostaną wyczerpane za niecałe trzy miesiące (2,93).