Solver jest fantastycznym narzędziem, który pozwala rozwiązywać problemy optymalizacyjne. Musimy tylko odpowiednio określić zmienne, warunki ograniczające i komórkę celu. Optymalizacja zakupów jest bardzo ciekawym problemem pokazującym potęgę Solvera.
Minimalna ilość zamówienia
Niedawno stanąłem w pracy przed ciekawym wyzwaniem. Chodziło o zakup optymalnej ilości produktów, uwzględniając dynamikę sprzedaży. Celem było to, aby 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.
Wiedziałem, że muszę do tego celu użyć Solvera, który jest narzędziem znacznie bardziej zaawansowanym od Szukaj Wyniku.
Tabela wyjściowa dla Solvera
Screen poniżej pokazuje tabelkę wyjściową. Na potrzeby wpisu, załóżmy, że dotyczy ona zamówienia długopisów firmy X. Długopisy sprzedawane są w sześciu różnych kolorach.
Analiza założeń
Przeanalizujmy zawartość poszczególnych kolumn:
- Stan na magazynie – kolumna C.
- Średnia miesięczna sprzedaż – kolumna D.
- Liczba m-cy na ile wystarczy towaru przy aktualnej dynamice sprzedaży – kolumna E.
- Ilości do zamówienia – kolumna F. To tutaj Solver musi nam zaproponować, jaką ilość powinniśmy zamówić. Są to nasze zmienne.
- Stan magazynowy produktu po dokonaniu zakupu – kolumna G.
- Czas na jaki wystarczy nam produktu po dokonaniu zakupów – kolumna H. 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.
Optymalizacja zakupów ma polegać na tym, aby ta różnica była jak najmniejsza.
Parametry Solvera
Następny screen pokazuje już wyjściowe parametry Solvera.
Naszą komórką celu jest E19 – różnica pomiędzy wartością MIN i MAX. Ponieważ chcemy, aby uzyskała ona jak najniższą wartość na przycisku opcji wybieramy wartość Min.
Z perspektywy czasu uważam, że zamiast skupić się na różnicy pomiędzy wartością MIN i MAX, lepiej byłoby obliczyć odchylenie standardowe. W zadaniu dążylibyśmy do tego, aby było ono minimalne.
W warunkach ograniczających określamy, ż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 umieściłem 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).
