Durum analizi: Goal Seek, senaryolar, Veri Tabloları ve Çözücü

Bu derste öğreneceklerin

Girdi, hesaplama ve çıktı bölümleri olan basit bir model kurmak
Goal Seek ile başa baş noktasını ve hedef fiyatı bulmak
Scenario Manager ve tek/iki değişkenli Veri Tabloları ile seçenekleri karşılaştırmak
Çözücü ile kısıtlı bir eniyileme problemini kurmak ve çözmek

Leyla Bakü'de küçük bir kahve dükkânı açıyor. Bir fincan 4 ₼'ye satılıyor, değişken maliyet (kahve, süt, bardak) 1,5 ₼, sabit giderler (kira, maaşlar) ayda 3000 ₼. Ayda 1.500 fincan satarsa kâr ne olur? Zarar etmemek için kaç fincan gerekir? Fiyat 4,5 ₼ olursa? Excel'in What-If Analysis araçları bu soruları formülleri yeniden yazmadan yanıtlar.

Model ve Goal Seek

Profit = Q · (P − V) − F

burada:

Qayda satılan fincan sayısı
Pfincan başına fiyat, ₼
Vfincan başına değişken maliyet, ₼
Faylık sabit giderler, ₼

Profit = 0 alınırsa başa baş noktası bulunur: Q* = F / (P − V). (P − V) fincan başına katkı payıdır.

1
Modeli kur
B1 — fiyat (4), B2 — değişken maliyet (1,5), B3 — sabit giderler (3000), B4 — adet (1500), B5 — kâr: =B4*(B1-B2)-B3 → 750 ₼. Girdiler yalnızca sayı, çıktı yalnızca formül olmalıdır.
2
Goal Seek'i aç
Data › Forecast › What-If Analysis › Goal Seek… komutunu seç. Set cell (çıktı): B5, To value (hedef): 0, By changing cell (değişecek girdi): B4.
3
Sonucu kabul et ya da iptal et
Excel B4'ü 1200 yapar. OK'e basarsan B4'teki eski değer (1500) kaybolur; yalnızca bakmak istiyorsan Cancel'a bas.

Goal Seek için iki soru

1) Leyla ayda 2000 ₼ kâr istiyor (fiyat 4 ₼). Kaç fincan satmalı? 2) Satışlar 1.500 fincanda kalırsa hangi fiyat 1500 ₼ kâr getirir? İki cevabı formülle de kontrol et.

Çözümü göster

1) Goal Seek: B5 → 2000, B4 değişiyor → 2.000 fincan.
Kontrol: Q = (F + Profit) / (P − V) = (3000 + 2000) / 2,5 = 2000.
2) Goal Seek: B5 → 1500, B1 değişiyor → 4,50 ₼.
Kontrol: 1500 · (P − 1,5) − 3000 = 1500 ⇒ P − 1,5 = 3 ⇒ P = 4,5.
Doğrusal bir modelde Goal Seek kesin sonuç verir; karmaşık modellerde 1199,9999 gibi bir yaklaşık değer alabilirsin; ROUND ile yuvarla.

Scenario Manager ve Veri Tabloları

Data › Forecast › What-If Analysis › Scenario Manager… › Add ile her senaryoya ad ver ve değişen hücreleri (B1, B4) gir: Kötümser (3,5 ₼, 1.100 fincan), Temel (4 ₼, 1.500), İyimser (4,5 ₼, 1.800). Summary… › Result cells: B5 yeni bir sayfada tablo oluşturur: kâr sırasıyla −800, 750 ve 2400 ₼. Senaryolar yönetime en kötü, beklenen ve en iyi durumu bir bakışta gösterir.

1
Tablonun çerçevesini hazırla
İki değişkenli tablo: D1'e =B5 (çıktıya bağlantı), E1:G1'e fiyatlar (3,5; 4; 4,5), D2:D6'ya adetler (1000–2000, 250'şer artışla).
2
Data Table'ı çalıştır
D1:G6'yı seç › Data › Forecast › What-If Analysis › Data Table…. Row input cell: B1 (fiyat), Column input cell: B4 (adet) › OK. Excel her kesişim için modeli yeniden hesaplar ve {=TABLE(B1,B4)} dizisini yazar.

Etkileşimli

Simülasyon yükleniyor…

Kahve dükkânı modeli (manat cinsinden) ve fiyat × adet tablosu.

Çözücü: kısıtlar altında en iyi plan

Goal Seek tek bir hedefe ulaşmak için tek bir girdiyi değiştirir. Çözücü (Solver) ise birden çok girdiyi aynı anda değiştirir, sonucu en büyük ya da en küçük yapar ve kısıtlara uyar. Bir eklentidir: File › Options › Add-ins › Manage: Excel Add-ins › Go… › Solver Add-in'i işaretle. Ardından Data › Analyze › Solver görünür.

Bir fırının günlük planı

Bir fırın ekmek (kâr 2 ₼) ve pasta (5 ₼) yapıyor. Bir ekmek 0,1 fırın saati ve 0,5 kg un, bir pasta 0,4 fırın saati ve 1 kg un gerektiriyor. Günde 20 fırın saati ve 80 kg un var. Kârı en büyük yapan plan hangisi?

Çözümü göster

Model: B2 — ekmek sayısı, C2 — pasta sayısı (değişken hücreler).
Kâr (D6): =SUMPRODUCT(B2:C2,{2,5}); fırın saati (D4): =SUMPRODUCT(B2:C2,{0.1,0.4}); un (D5): =SUMPRODUCT(B2:C2,{0.5,1}).
Çözücü: Set Objective D6, To: Max, By Changing Variable Cells B2:C2; Subject to the Constraints: D4 <= 20, D5 <= 80; Make Unconstrained Variables Non-Negative işaretli; Select a Solving Method: Simplex LP › Solve.
Cevap: 120 ekmek ve 20 pasta, kâr 2 · 120 + 5 · 20 = 340 ₼.
Kontrol: fırın 12 + 8 = 20 saat, un 60 + 20 = 80 kg; iki kaynak da tamamen kullanılmış. Karşılaştırma: yalnızca ekmek (160 adet) 320 ₼, yalnızca pasta (50 adet) 250 ₼ verir.

Goal Seek (sırayla bas: Alt, A, W, G)Alt+A+W+G

Scenario Manager (Senaryo Yöneticisi)Alt+A+W+S

Data Table penceresiAlt+A+W+T

Çalışma kitabını yeniden hesapla (Veri Tabloları elle moddaysa)F9

Önemli noktalar

Modeli girdiler (sayılar), hesaplamalar ve çıktılar (formüller) olarak ayır.
Goal Seek tek bir girdiyi değiştirerek bir çıktıyı hedefe getirir; başa baş Q* = F / (P − V).
Scenario Manager adlandırılmış senaryoları saklar ve özet rapor oluşturur.
Veri Tablosu bir ya da iki girdinin tüm birleşimlerini {=TABLE()} dizisiyle hesaplar.
Çözücü birden çok girdiyi değiştirir, amacı en büyük/en küçük yapar ve kısıtlara uyar.

Kendini test et

10 soru. Her doğru cevap XP kazandırır.

1 / 10

Fiyat 5 ₼, değişken maliyet 2 ₼, sabit giderler 4500 ₼. Başa baş noktası kaç birimdir?