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).
Przy takich założeniach zamiast Solvera można skorzystać z prostej matematyki (arytmetyki).
do sumy z kolumny C (stan aktualny) dodać planowany zakup 5000 = stan po zakupie
=SUMA($C$5:$C$10)+5000
Udział każdego typu produktu w tym łącznym stanie powinien wynosić:
wartość z bieżącego wiersza kolumny D podzielona przez sumę kolumny D
=D5/SUMA($D$5:$D$10)
Czyli Twoja Kolumna G to byłby stan po zakupie * ten udział
Jeśli od tego odejmiemy produkty już na stanie dostaniemy wielkość zakupu:
=(SUMA($C$5:$C$10)+5000) * (D5/SUMA($D$5:$D$10)) – C5
A uwzględniając konieczność zaokrągleń:
=ZAOKR((SUMA($C$5:$C$10)+5000)*D5/SUMA($D$5:$D$10)-C5;0)
Ponieważ niedokładności wynikające z zaokrągleń czasem lubią się nakładać, to w ostatniej komórce umieściłbym inną formułę:
=5000-SUMA(F5:F9)
Pozdrawiam,