Bir şirket hangi projeleri üstlenmesi gerektiğini saptamak için Çözücü'yü nasıl kullanabilir?
Her yıl, Eli Lilly gibi bir şirketin hangi ilaçları geliştireceğini belirlemesi gerekiyor; hangi yazılım programlarının geliştirileceği Microsoft gibi bir şirket; Proctor & Gamble gibi bir şirket, hangi yeni tüketici ürünlerini geliştirecek. Excel'deki Çözücü özelliği, şirketin bu kararları almasına yardımcı olabilir.
Bir şirket hangi projeleri üstlenmesi gerektiğini saptamak için Çözücü'yü nasıl kullanabilir?
Çoğu şirket, sınırlı kaynaklara (genellikle sermaye ve işgücü) tabi olarak, en büyük net bugünkü değere (NPV) katkıda bulunan projeleri üstlenmek ister. Diyelim ki bir yazılım geliştirme firması 20 yazılım projesinden hangisini üstlenmesi gerektiğini belirlemeye çalışıyor. Her bir projenin katkıda bulunduğu NPV (milyon dolar cinsinden) ve önümüzdeki üç yılın her birinde ihtiyaç duyulan sermaye (milyon dolar cinsinden) ve programcı sayısı, bir sonraki sayfada Şekil 30-1'de gösterilen dosya Capbudget.xlsx Temel Model çalışma sayfasında verilmiştir. Örneğin, Proje 2 908 milyon dolar getiri sağlıyor. 1. Yılda 151 milyon dolar, 2. Yılda 269 milyon dolar ve 3. yılda 248 milyon dolar gerekiyor. Proje 2, 1. yılda 139 programcı, 2. yılda 86 programcı ve 3. yılda 83 programcı gerektirir. E4:G4 hücreleri her üç yıl için kullanılabilir sermayeyi (milyon dolar cinsinden) gösterirken, H4:J4 hücreleri kaç programcının kullanılabilir olduğunu gösterir. Örneğin, 1. Yıl boyunca 2,5 milyar dolara kadar sermaye ve 900 programcı mevcuttur.
Şirket, her projeyi üstlenip üstlenmeyeceğine karar vermelidir. Diyelim ki bir yazılım projesinin bir kısmını üstlenemiyoruz; Örneğin, gerekli kaynakların 0,5'ini tahsis edersek, bize 0 ABD doları gelir getirecek çalışmayan bir programımız olur!
Bir şey yaptığınız veya yapmadığınız durumları modellemenin püf noktası, ikili değişen hücreleri kullanmaktır. İkili değiştiren bir hücre her zaman 0 veya 1'e eşittir. Bir projeye karşılık gelen ikili değişen hücre 1'e eşit olduğunda, projeyi yaparız. Bir projeye karşılık gelen ikili değişen hücre 0'a eşitse, projeyi yapmayız. Çözücü'yü, kısıtlama ekleyerek bir aralıktaki ikili değişen hücreleri kullanacak şekilde ayarlarsınız; kullanmak istediğiniz değişen hücreleri seçin ve sonra Kısıtlama Ekle iletişim kutusundaki listeden Çöp Kutusu'nu seçin.
Bu arka planla, yazılım projesi seçim problemini çözmeye hazırız. Çözücü modelinde her zaman olduğu gibi, hedef hücremizi, değişen hücreleri ve kısıtlamaları tanımlayarak işe başlarız.
- Hedef hücre. Seçilen projeler tarafından oluşturulan NPV'yi en üst düzeye çıkarıyoruz.
- Değişen hücreler. Her proje için 0 veya 1 ikili değişen hücre arıyoruz. Bu hücreleri A6:A25 aralığına yerleştirdim (ve aralığı doit olarak adlandırdım). Örneğin, A6 hücresindeki 1, Proje 1'i üstlendiğimizi gösterir; C6 hücresindeki 0, Proje 1'i üstlenmediğimizi gösterir.
- Kısıtlamalar. Her t Yılı (t=1, 2, 3) için, kullanılan T Yılı sermayesinin mevcut t Yılı sermayesinden daha az veya ona eşit olmasını ve kullanılan T Yılı emeğinin mevcut t Yılı emeğinden daha az veya ona eşit olmasını sağlamamız gerekir.
Gördüğünüz gibi, çalışma sayfamız herhangi bir proje seçimi için NPV'yi, yıllık olarak kullanılan sermayeyi ve her yıl kullanılan programcıları hesaplamalıdır. B2 hücresinde, seçilen projeler tarafından oluşturulan toplam NBD değerini hesaplamak için TOPLA.ÇARPIM(doit;NBD) formülünü kullanıyorum. ( NBD aralık adı, C6:C25 aralığını belirtir.) A sütununda 1 olan her proje için, bu formül projenin NBV'sini seçer ve A sütununda 0 olan her proje için, bu formül projenin NBD'sini seçmez. Bu nedenle, tüm projelerin NBD değerini hesaplayabiliyoruz ve hedef hücremiz doğrusaldır çünkü ( değişen hücre)*(sabit) biçimini izleyen terimlerin toplanmasıyla hesaplanır. Benzer şekilde, her yıl kullanılan sermayeyi ve her yıl kullanılan emeği, TOPLA.ÇARPIM(doit,E6:E25) formülünü E2'den F2:J2'ye kopyalayarak hesaplıyorum.
Şimdi Şekil 30-2'de gösterildiği gibi Çözücü Parametreleri iletişim kutusunu dolduruyorum.
Seçili projelerin NBD değerini en üst düzeye çıkarmayı hedefliyoruz (hücre B2). Değişen hücrelerimiz ( doit adlı aralık), her proje için ikili değişen hücrelerdir.
E2:J2<=E4:J4 kısıtlaması, her yıl boyunca kullanılan sermaye ve emeğin, mevcut sermaye ve emeğe eşit veya daha az olmasını sağlar. Değişen hücreleri ikili yapan kısıtlamayı eklemek için Çözücü Parametreleri iletişim kutusunda Ekle'ye tıklayıp iletişim kutusunun ortasındaki listeden Bölme'yi seçiyorum. Kısıtlama Ekle iletişim kutusu Şekil 30-3'te gösterildiği gibi görünmelidir.
Modelimiz doğrusaldır çünkü hedef hücre (değişen hücre)*(sabit) biçimindeki terimlerin toplamı olarak hesaplanır ve kaynak kullanım kısıtlamaları (değişen hücreler)*(sabitler) toplamı bir sabitle karşılaştırılarak hesaplanır.
Çözücü Parametreleri iletişim kutusu doldurulmuşken, Çöz'e tıklayın ve daha önce Şekil 30-1'de gösterilen sonuçları elde ettik. Şirket, Proje 2, 3, 6-10, 14-16, 19 ve 20'yi seçerek maksimum 9.293 milyon ABD Doları (9,293 milyar ABD Doları) NPV elde edebilir.
Diğer kısıtlamaları işleme
Bazen proje seçimi modellerinin başka kısıtlamaları da olur. Örneğin, Proje 3'ü seçersek Proje 4'ü de seçmemiz gerektiğini varsayalım. Geçerli optimal çözümümüz Proje 3'ü seçtiği ancak Proje 4'ü seçmediği için, geçerli çözümümüzün en iyi durumda kalamayacağını biliyoruz. Bu sorunu çözmek için, Proje 3'teki ikili değişen hücrenin, Proje 4'teki ikili değişen hücreden küçük veya ona eşit olması kısıtlamasını eklemeniz yeterlidir.
Bu örneği, Şekil 3-4'te gösterilen dosya Capbudget.xlsx If 3 then 4 çalışma sayfasında bulabilirsiniz. L9 hücresi Project 3 ile ilgili ikili değeri ve L12 hücresi de Project 4 ile ilgili ikili değeri gösterir. L9<=L12 kısıtlamasını ekleyerek, Proje 3'ü seçersek, L9 1'e eşittir ve kısıtlamamız L12'yi (Proje 4 ikili) 1'e eşit olmaya zorlar. Kısıtımız, Proje 3'ü seçmezsek, Proje 4'ün değişen hücresindeki ikili değeri de kısıtlamasız bırakmalıdır. Proje 3'ü seçmezsek, L9 0'a eşittir ve kısıtlamamız Proje 4 ikili dosyasının 0 veya 1'e eşit olmasına izin verir, istediğimiz budur. Yeni optimal çözüm Şekil 30-4'te gösterilmektedir.
Proje 3'ü seçmek, Proje 4'ü de seçmemiz gerektiği anlamına geliyorsa, yeni bir optimal çözüm hesaplanır. Şimdi de Proje 1'den 10'a kadar olan projelerden yalnızca dört proje yapabildiğimizi varsayalım. (Şekil 30-5'te gösterilen P1-P10 çalışma sayfasının En Çok 4'üne bakın.) L8 hücresinde, 1 ile 10 arası projelerle ilişkili ikili değerlerin toplamını TOPLA(A6:A15) formülüyle hesaplıyoruz. Ardından, ilk 10 projeden en fazla 4'ünün seçilmesini sağlayan L8<=L10 kısıtlamasını ekliyoruz. Yeni optimal çözüm Şekil 30-5'te gösterilmektedir. NBD 9.014 milyar dolara düştü.
İkili ve Tamsayılı Programlama Problemlerini Çözme
Değişen hücrelerden bazılarının veya tümünün ikili ya da tam sayı olması gereken Doğrusal Çözücü modellerini çözmek, tüm değişen hücrelerin kesirli olmasına izin verilen doğrusal modellere göre genellikle daha zordur. Bu nedenle, genellikle ikili veya tamsayı programlama problemine optimuma yakın bir çözümle tatmin oluruz. Çözücü modeliniz uzun süre çalışırsa, Çözücü Seçenekleri iletişim kutusundaki Tolerans ayarını düzenlemeyi düşünebilirsiniz. (Bkz. Şekil 30-6.) Örneğin, %0,5'lik bir Tolerans ayarı, Çözücü'nün teorik en iyi hedef hücre değerinin yüzde 0,5'i (teorik en uygun hedef hücre değeri, ikili ve tamsayı kısıtlamaları atlandığında bulunan en uygun hedef değerdir) uygun bir çözüm bulduğunda ilk kez duracağı anlamına gelir. Çoğu zaman, 10 dakika içinde optimalin yüzde 10'u içinde bir cevap bulmak veya iki haftalık bilgisayar süresinde en uygun çözümü bulmak arasında bir seçimle karşı karşıya kalıyoruz! Varsayılan Tolerans değeri %0,05'tir; bu da, teorik en uygun hedef hücre değerinin yüzde 0,05'i içinde bir Hedef Hücre değeri bulduğunda Çözücü'nün durduğu anlamına gelir.
Sorunlar
- Bir şirketin düşünülmekte olan dokuz projesi var. Her projenin eklediği NBD ve gelecek iki yıl boyunca gerekli olan sermaye aşağıdaki tabloda gösterilmektedir. (Tüm sayılar milyon cinsindendir.) Örneğin, Proje 1, NBD'ye 14 milyon ABD doları ekleyecek ve 1. yıl boyunca 12 milyon ABD doları ve 2. yıl boyunca 3 milyon ABD doları harcama gerektirecektir. 1. Yıl boyunca, projeler için 50 milyon dolar sermaye mevcuttur ve 2. yıl boyunca 20 milyon dolar mevcuttur.
| NBD işlevi | 1. Yıl harcamaları | 2. Yıl harcamaları | |
|---|---|---|---|
| Proje 1 | 14 | 12 | 3 |
| Proje 2 | 17 | 54 | 7 |
| Proje 3 | 17 | 6 | 6 |
| Proje 4 | 15 | 6 | 2 |
| Proje 5 | 40 | 30 | 35 |
| Proje 6 | 12 | 6 | 6 |
| Proje 7 | 14 | 48 | 4 |
| Proje 8 | 10 | 36 | 3 |
| Proje 9 | 12 | 18 | 3 |
- Bir projenin bir kısmını üstlenemezsek, ancak bir projenin tamamını veya hiçbirini üstlenmemek zorundaysak, NPV'yi nasıl en üst düzeye çıkarabiliriz?
- Proje 4 üstlenilirse, Proje 5'in üstlenilmesi gerektiğini varsayalım. NPV'yi nasıl en üst düzeye çıkarabiliriz?
Bir yayınevi bu yıl 36 kitaptan hangisini yayınlayacağını belirlemeye çalışıyor. Dosya Pressdata.xlsx her kitap hakkında aşağıdaki bilgileri verir:
- Öngörülen gelir ve geliştirme maliyetleri (bin dolar cinsinden)
- Her kitaptaki sayfalar
- Kitabın bir yazılım geliştirici kitlesine yönelik olup olmadığı (E sütununda 1 ile gösterilir)
Bir yayınevi bu yıl toplam 8500 sayfaya kadar kitap yayınlayabilir ve yazılım geliştiricilere yönelik en az dört kitap yayınlamalıdır. Şirket kârını nasıl maksimize edebilir?
Makale hakkında
Bu makale, Wayne L. Winston tarafından yazılan Microsoft Office Excel 2007 Data Analysis and Business Modeling adlı kitaptan uyarlanmıştır.
Bu sınıf tarzı kitap, Excel'in yaratıcı, pratik uygulamalarında uzmanlaşmış, tanınmış bir istatistikçi ve işletme profesörü olan Wayne Winston'ın bir dizi sunumundan geliştirilmiştir.