Sermaye bütçesi için Çözücü kullanma

Ç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 projeler gerçekleştirmek ister. Bir yazılım geliştirme şirketinin 20 yazılım projesinden hangisini üstleneceğini belirlemeye çalıştığını varsayalım. Her projenin katkıda bulunduğu NBD (milyonlarca dolar) ve sermaye (milyonlarca dolar) ve sonraki üç yılın her birinde ihtiyaç duyulan programcı sayısı, sonraki sayfada Şekil 30-1'de gösterilen dosya Capbudget.xlsx Temel Model çalışma sayfasında verilmiştir. Örneğin, Project 2 908 milyon ABD doları verir. 1. Yıl için 151 milyon dolar, 2. Yıl için 269 milyon DOLAR ve 3. Yıl boyunca 248 milyon DOLAR gerekir. Project 2 için 1. Yıl boyunca 139 programcı, 2. Yıl boyunca 86 programcı ve 3. Yıl boyunca 83 programcı gerekir. E4:G4 hücreleri üç yılın her birinde kullanılabilir olan sermayeyi (milyonlarca dolar) ve H4:J4 hücreleri ise kaç programcının kullanılabilir olduğunu gösterir. Örneğin 1. Yıl boyunca 2,5 milyar ABD dolarına kadar sermaye ve 900 programcı kullanılabilir.

Her projeyi üstlenip üstlenmeyeceğine şirket karar vermelidir. Bir yazılım projesinin küçük bir bölümünü üstlenemiyoruz; Örneğin, gerekli kaynakların 0,5'ini ayırırsak, bize 0 ABD doları gelir getirecek çalışma dışı bir programımız olur!

Bir şey yaptığınız veya yapmadığınız durumlarda modellemenin püf noktası , ikili değişen hücreleri kullanmaktır. İkili değişen hücre her zaman 0 veya 1'e eşittir. Bir projeye karşılık gelen bir 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. Bir kısıtlama ekleyerek Çözücü'yü ikili değişen hücre aralığını kullanacak şekilde ayarlarsınız; kullanmak istediğiniz değişen hücreleri seçin ve ardından Kısıtlama Ekle iletişim kutusundaki listeden Bin'i seçin.

Bu arka plan sayesinde yazılım projesi seçim sorununu çözmeye hazırız. Her zaman bir Çözücü modelinde olduğu gibi, hedef hücremizi, değişen hücreleri ve kısıtlamaları tanımlayarak başlarız.

• Hedef hücre. Seçilen projeler tarafından oluşturulan NPV'yi en üst düzeye çıkarırız.

• Hücreleri değiştirme. Her proje için 0 veya 1 ikili değişen hücre ararız. Bu hücreleri A6:A25 aralığında buldum (ve aralığa doit adını verdim). Örneğin, A6 hücresindeki 1, Proje 1'i üstlendiğimize işaret eder; C6 hücresindeki bir 0, Project 1'i üstlenmediğimize işaret eder.

• Kısıtlama -ları. Her Yıl t için (t=1, 2, 3), Kullanılan Yıl t sermayesi için Kullanılabilir Yıl t sermayesinden küçük veya buna eşit olduğundan ve Kullanılan Yıl t emeğinin kullanılabilir Yıl t iş gücünden küçük veya buna eşit olduğundan emin olmamız gerekir.

Gördüğünüz gibi, çalışma sayfamızın NPV, yıllık kullanılan sermaye ve her yıl kullanılan programcılar için hesaplama yapması gerekir. B2 hücresinde, seçili projeler tarafından oluşturulan toplam NBD'yi hesaplamak için SUMPRODUCT(doit,NPV) formülünü kullanıyorum. ( NPV aralık adı C6:C25 aralığına başvurur.) A sütununda 1 bulunan her proje için bu formül projenin NBD değerini alır ve A sütununda 0 bulunan her proje için bu formül projenin NBD değerini almaz. Bu nedenle, tüm projelerin NBD'sini hesaplayabiliyoruz ve hedef hücremiz doğrusaldır çünkü forma uygun terimler toplanarak hesaplanır (hücre değiştiriliyor)*(sabit). Benzer şekilde, E2'den F2:J2'ye SUMPRODUCT(doit,E6:E25) formülünü kopyalayarak her yıl kullanılan sermayeyi ve her yıl kullanılan işçiliği hesaplıyorum.

Şimdi Şekil 30-2'de gösterildiği gibi Çözücü Parametreleri iletişim kutusunu dolduruyorum.

Hedefimiz, seçilen projelerin (B2 hücresi) NBD'sini en üst düzeye çıkarmaktır. 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 kullanılan sermayenin ve işgücünün kullanılabilir sermaye ve işçilik değerinden küçük veya buna eşit 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ıklıyorum ve iletişim kutusunun ortasındaki listeden Bin'i seçiyorum. Şekil 30-3'te gösterildiği gibi Kısıtlama Ekle iletişim kutusu görünmelidir.

Hedef hücre formu olan terimlerin toplamı olarak hesaplandığından *(sabit) ve kaynak kullanım kısıtlamalarının toplamı (değişen hücreler)*(sabitler) bir sabitle karşılaştırılarak hesaplandığından modelimiz doğrusaldır.

Çözücü Parametreleri iletişim kutusu doldurulduğunda, Çöz'e tıklayın ve daha önce Şekil 30-1'de gösterilen sonuçları elde ettik. Şirket, Projeler 2, 3, 6–10, 14-16, 19 ve 20'yi seçerek en fazla 9.293 milyon ABD doları (9,293 milyar ABD doları) NBD elde edebilir.

Bazen proje seçimi modellerinin başka kısıtlamaları vardır. Örneğin, Project 3'i seçtiğimizde Project 4'i de seçmemiz gerektiğini varsayalım. Geçerli en uygun çözümümüz Project 3'i seçtiği için ancak Project 4'i seçmediğinden, geçerli çözümümüzün en uygun durumda kalamadığını biliyoruz. Bu sorunu çözmek için, Project 3 için ikili değişen hücrenin Project 4'ün ikili değişen hücresinden küçük veya buna eşit olması kısıtlamasını eklemeniz yeterlidir.

Bu örneği, Şekil 30-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ğere, L12 hücresine ise Project 4 ile ilgili ikili değere başvurur. L9<=L12 kısıtlamasını ekleyerek, Proje 3'i seçersek, L9 1'e eşit olur ve kısıtlamamız L12'yi (Proje 4 ikili dosyası) 1'e eşit olarak zorlar. Ayrıca, Project 3'i seçmezsek, kısıtlamamız Project 4'ün değişen hücresindeki ikili değeri kısıtlamasız bırakmalıdır. Proje 3'i seçmezsek, L9 0'a eşittir ve kısıtlamamız Project 4 ikili dosyasının 0 veya 1'e eşit olmasını sağlar. Bu da istediğimiz şey. Yeni en uygun çözüm Şekil 30-4'te gösterilmiştir.

Project 3 seçildiğinde Project 4'i de seçmemiz gerektiği anlamına geliyorsa yeni bir en uygun çözüm hesaplanır. Şimdi Projeler 1 ile 10 arasında yalnızca dört proje yapabileceğimizi varsayalım. (Şekil 30-5'te gösterilen P1-P10'un En Çok 4'lü çalışma sayfasına bakın.) L8 hücresinde, SUM(A6:A15) formülüyle 1 ile 10 arasında Projeler ile ilişkili ikili değerlerin toplamını hesaplarız. Ardından L8<=L10 kısıtlamasını ekleyerek ilk 10 projeden en fazla 4'ünün seçilmesini sağlarız. Yeni en uygun çözüm Şekil 30-5'te gösterilmiştir. NPV 9,014 milyar abd dolarına düştü.

Değişen hücrelerin bir kısmının veya tümünün ikili veya tamsayı olması gereken Doğrusal Çözücü modellerinin çözülmesi genellikle değişen tüm hücrelerin kesir olması için izin verilen doğrusal modellerden daha zordur. Bu nedenle genellikle ikili veya tamsayı programlama sorunu için en uygun çözümden memnunuz. Çözücü modeliniz uzun süre çalışıyorsa, Çözücü Seçenekleri iletişim kutusunda Tolerans ayarını ayarlamayı düşünebilirsiniz. (Bkz. Şekil 30-6.) Örneğin, %0,5 Tolerans ayarı, Çözücü'nün teorik en uygun hedef hücre değerinin yüzde 0,5'i içinde uygun bir çözüm bulduğunda durduracağı anlamına gelir (teorik en uygun hedef hücre değeri, ikili ve tamsayı kısıtlamaları atlandığında bulunan en uygun hedef değerdir). Genellikle 10 dakikada en uygun yanıtın yüzde 10'unda bir yanıt bulmakla iki haftalık bilgisayar süresinde en uygun çözümü bulmak arasında bir seçimle karşı karşıyayız! Varsayılan Tolerans değeri %0,05'tir, yani Çözücü teorik en uygun hedef hücre değerinin yüzde 0,05'i içinde hedef hücre değeri bulduğunda durur.

1. Bir şirketin göz önünde bulundurulan dokuz projesi vardır. Her proje tarafından eklenen NBD ve sonraki iki yıl boyunca her projenin gerektirdiği sermaye aşağıdaki tabloda gösterilmiştir. (Tüm sayılar milyon cinsindendir.) Örneğin, Project 1 14 milyon ABD doları NBD ekler ve 1. Yıl boyunca 12 milyon ABD doları ve 2. Yıl boyunca 3 milyon ABD doları harcama gerektirir. 1. Yıl boyunca projeler için 50 milyon ABD doları sermaye ve 2. Yıl boyunca 20 milyon ABD doları kullanılabilir.

	NBD	1. Yıl harcaması	2. Yıl harcaması
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ı üstlenemez ancak projenin tamamını veya hiçbirini üstlenmek zorunda kalırsak, NPV'yi nasıl en üst düzeye çıkarabiliriz?

• Project 4 üstlenilirse, Project 5'in üstlenilmesi gerektiğini varsayalım. NPV'leri nasıl en üst düzeye çıkarabiliriz?

• Bir yayıncılık şirketi, bu yıl hangi 36 kitabı yayımlaması gerektiğini belirlemeye çalışıyor. Dosya Pressdata.xlsx her kitap hakkında aşağıdaki bilgileri verir:

  • Öngörülen gelir ve geliştirme maliyetleri (binlerce dolar)

  • Her kitaptaki sayfalar

  • Kitabın yazılım geliştiricilerinin hedef kitlesine yönelik olup olmadığı (E sütununda 1 ile belirtilir)

    Bir yayıncılık şirketi bu yıl toplam 8500 sayfaya kadar kitap yayımlayabilir ve yazılım geliştiricilerine yönelik en az dört kitap yayımlamalıdır. Şirket kârını nasıl en üst düzeye çıkarabilir?

Bu makale, Wayne L. Winston tarafından Microsoft Office Excel 2007 Veri Analizi ve İş Modellemesi'nden uyarlanmıştır.

Sınıf stilindeki bu 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.