Excel Çözücü ile Doğrusal Programlama Problemi Çözelim!
Doğrusal (linear) programlama problemlerini Excel Çözücü (Solver) ile nasıl kolayca çözebileceğimize bakalım.
Senaryo
Operasyon alanını genişletmek isteyen bir şirket en fazla 11 yeni mağaza açmayı planlıyor. Bu mağazaları her lokasyonda üç mağaza türünden birinde inşa edecekler — küçük süpermarket, büyük süpermarket veya hipermarket. Market türlerine göre inşaat maliyeti, gerekli çalışan sayısı, ve yıllık net gelir aşağıdaki tabloda gösterilmiştir:
Şirketin inşaat sermayesi ve personel sayısı ise sırasıyla 82,5 milyon $ ve 300 kişidir.
Her bir mağaza türünden kaçar tane inşa etmeliyiz ki gelirimiz maksimum olsun?
Karar Değişkenleri, Kısıtlar ve Amaç Fonksiyonunun Belirlenmesi
Karar Değişkenleri
Karar değişkenleri; her bir mağaza türünden kaçar tane inşa edileceğini temsil ediyor.
Karar değişkenlerinin optimal değerlerin yer alacağı C3:C5 hücreleri henüz boş, fakat en son aşamada bu hücrelerde bize maksimum geliri sağlayacak market sayılarını göreceğiz!
Kısıtlar
KISIT_1: Toplamda en fazla 11 yeni mağaza inşa edileceğini biliyoruz; o halde karar değişkenlerinin değerlerinin yer alacağı hücrelerin (C3:C5) toplamı 11'den küçük veya eşit olmalı.
KISIT_2: İnşaat maliyeti dizisi (yanda kırmızı ile çerçevelenmiş) ve mağaza sayısı dizisinin (mavi ile çerçevelenmiş) çarpımını TOPLA.ÇARPIM (İngilizcesi SUMPRODUCT) fonksiyonunu kullanarak bulabiliriz. Sonuç, şirketin inşaat sermayesinden (82,5 milyon $) küçük veya eşit olmalı.
KISIT_3: Her bir market türünde çalışması gereken personel sayısı dizisi ve karar değişkenleri dizisinin çarpımı şirketin toplam personel sayısından (300) küçük veya eşit olmalı.
Amaç Fonksiyonu
Amacımız gelirimizi maksimize etmek olduğu için mağaza tiplerine göre yıllık net gelir dizisi (kırmızı) ile karar değişkenleri dizisinin (mavi) çarpımı bize aradığımız cevabı verir. C12 hücresine bu iki dizinin çarpımını yine TOPLA.ÇARPIM fonksiyonunu kullanarak yazıyoruz.
Çözüm
Veee kurgumuzu tamamlayıp son aşamaya ulaştık! Tek yapmamız gereken Excel’e bu modelimizi anlatmak.
Çözücüyü açtığımızda karşımıza ilk olarak hedef kısmı çıkıyor. Neyi maksimize ya da minimize etmek istiyorsak o bizim hedefimizdir. Bu senaryoda gelirimizi maksimize etmek istiyorduk ve gelirin maksimum değerini veren formülü (amaç fonksiyonu) bir önceki adımda C12 hücresinin içine yazmıştık. Şimdi de C12 hücresini ‘Hedef Ayarla’ kutucuğuna yazıyoruz ve bu hücrenin maksimum değerini bulmak istediğimizden en büyük seçeneğini işaretliyoruz.
‘Değişken hücreleri değiştirerek’ kutucuğuna karar değişkenlerinin yer aldığı hücreleri giriyoruz.
‘Kısıtlamalara bağlıdır:’ kısmındaki ekle butonuna basarak ilk kısıtın girişini yapıyoruz. Tamam yerine Ekle’ye basarak geri kalan 2 kısıtın da girişini yapalım.
Tüm kısıtları girdikten sonra ‘kısıtlanmamış değerleri pozitif yap’ kutucuğunu tikliyoruz çünkü mağaza sayılarını verecek olan karar değerlerimiz negatif olamaz. -2 tane hipermarket inşa edilemeyeceği gibi mesela :)
Çözme yöntemini de Basit LP olarak seçtiysek artık çözümü görmeye bir ‘Çöz’ butonuna basmak kadar uzağız demektir :)
Maksimum gelir olan 20,4 milyon doları elde edebilmemiz için 2 adet küçük süpermarket ve 9 adet büyük süpermarket inşa etmeliymişiz.
Çözümü özetlemek gerekirse, önce karar değişkenlerimizi belirledik. Sonrasında TOPLA.ÇARPIM fonksiyonunu çokça kullanarak kısıtları ve amaç fonksiyonunu tanımladığımız bir modelleme yaptık. Son olarak ise bu modellemeyi Çözücü’de gerekli alanları doldurarak Excel’e anlattık.
Nice Şenolan
Referanslar
Pollington, B. Using Excel to solve linear programming problems, https://www.msubillings.edu/asc/writingcenter/resources/math/tutorials/finitemathhelps/Lin%20Prog%20with%20Excel.pdf. PDF download.
