Excel'de Tedarik Zinciri Veri Analizi: Lojistik, OTIF, Stok

Kısa cevap: Excel’de lojistik ve tedarik zinciri veri analizi dört alanda yoğunlaşır: teslimat performansı (teslim süresi, zamanında ve eksiksiz teslim oranı OTIF), stok verimliliği (stok devir hızı, stokta kalma günü, ABC sınıflaması), stok politikası (emniyet stoğu, yeniden sipariş noktası, ekonomik sipariş miktarı) ve tedarikçi performansı (ağırlıklı tedarikçi karnesi). Bu hesapların tamamı sipariş ve stok hareketlerini tutan iki-üç tablo, ÇOKEĞERSAY, ÇOKETOPLA, NORM.S.TERS, KAREKÖK gibi fonksiyonlar ve PivotTable ile yapılabilir.

Excel'de tedarik zinciri veri analizi: lojistik, OTIF ve stok göstergeleri

Örneklerdeki talep, süre ve maliyet değerleri formülleri göstermek için seçilmiş temsili değerlerdir; herhangi bir firmanın veya sektörün verisi değildir.

Lojistik verileri Excel’de nasıl düzenlenmeli?

Her siparişi (veya sipariş satırını) tek satırda tutan bir tablo, analizlerin temelidir. ERP veya depo yönetim sisteminden alınan dışa aktarımları Ctrl+T ile tabloya çevirin:

Tablo	Sütunlar	Amaç
tblSiparis	Sipariş No, Sipariş Tarihi, Söz Verilen Tarih, Sevk Tarihi, Teslim Tarihi, Müşteri, Bölge, Taşıyıcı, İstenen Adet, Teslim Edilen Adet, Hasarlı (1/0)	Teslimat performansı
tblStok	Tarih, Ürün Kodu, Giriş, Çıkış, Kalan, Birim Maliyet	Stok devir hızı, ABC
tblSatinalma	PO No, Tedarikçi, Ürün Kodu, Sipariş Tarihi, Beklenen Tarih, Teslim Tarihi, Adet, Kabul Edilen Adet	Tedarik süresi, tedarikçi karnesi

Tarihlerin gerçekten tarih türünde olduğunu kontrol edin (sola yaslı görünen tarihler genellikle metindir). Farklı sistemlerden gelen dosyaları her ay elle kopyalamak yerine Veri > Veri Al > Klasörden (Power Query) ile aynı klasördeki tüm dışa aktarımları tek tabloda birleştirmek, raporu her ay tek tıkla yenilenebilir hale getirir.

Teslim süresi ve OTIF Excel’de nasıl hesaplanır?

tblSiparis tablosuna şu hesaplanmış sütunları ekleyin:

Teslim süresi (takvim günü): =[@[Teslim Tarihi]]-[@[Sipariş Tarihi]]
Teslim süresi (iş günü): =TAMİŞGÜNÜ.ULUSL([@[Sipariş Tarihi]];[@[Teslim Tarihi]];1;tblTatil[Tarih])-1
Zamanında (1/0): =--([@[Teslim Tarihi]]<=[@[Söz Verilen Tarih]])
Eksiksiz (1/0): =--([@[Teslim Edilen Adet]]>=[@[İstenen Adet]])
OTIF (1/0): =[@Zamanında]*[@Eksiksiz]; gecikme günü: =MAK(0;[@[Teslim Tarihi]]-[@[Söz Verilen Tarih]])

Formüllerin başındaki --, DOĞRU/YANLIŞ sonucunu 1/0’a çevirir. Temsili aylık özet:

Gösterge	Formül	Temsili sonuç
Sipariş sayısı	`=ÇOKEĞERSAY(tblSiparis[Ay];A2)`	1.200
Zamanında teslim oranı	`=ÇOKETOPLA(tblSiparis[Zamanında];tblSiparis[Ay];A2)/B2`	%92
Eksiksiz teslim oranı	Aynı yapıyla Eksiksiz sütunu	%95
OTIF	Aynı yapıyla OTIF sütunu	%88
Medyan teslim süresi	`=ORTANCA(EĞER(tblSiparis[Ay]=A2;tblSiparis[Teslim Süresi]))`	3 gün

OTIF, iki oranın çarpımı değildir; aynı siparişin hem zamanında hem eksiksiz olması gerektiği için sipariş düzeyinde hesaplanıp ortalaması alınır. Bu örnekte %92 × %95 = %87,4 olurdu, gerçek OTIF %88’dir. Taşıyıcı ve bölge kırılımında OTIF’i PivotTable ile göstermek, sorunun belirli bir taşıyıcıda mı yoksa belirli bir bölgede mi yoğunlaştığını ortaya koyar.

Stok devir hızı ve ABC analizi nasıl yapılır?

Stok devir hızı = dönemin satılan malın maliyeti / ortalama stok değeri; stokta kalma günü = 365 / devir hızı. Ürün bazında: =ÇOKETOPLA(tblStok[Çıkış Tutarı];tblStok[Ürün Kodu];A2)/ORTALAMA(dönem_başı_stok;dönem_sonu_stok). Devir hızı düşük ürünler depoda sermaye ve alan bağlar; yüksek devirli ürünlerde ise stoksuz kalma riski izlenmelidir.

ABC analizi, ürünleri yıllık tüketim değerine göre sınıflandırır (Pareto ilkesi):

Her ürün için yıllık tüketim değeri: =ÇOKETOPLA(tblStok[Çıkış];tblStok[Ürün Kodu];A2)*B2 (B = birim maliyet).
Listeyi değere göre büyükten küçüğe sıralayın (Microsoft 365’te =SIRALA(aralık;3;-1)).
Kümülatif pay: =TOPLA($C$2:C2)/TOPLA($C$2:$C$500)
Sınıf: =EĞER(D2<=0,8;"A";EĞER(D2<=0,95;"B";"C"))

Tipik olarak ürünlerin küçük bir bölümü (A sınıfı) toplam değerin büyük kısmını oluşturur; %80 ve %95 eşikleri yaygın ama keyfi seçimlerdir. ABC’yi talep değişkenliğine dayalı XYZ sınıflamasıyla birleştirebilirsiniz: aylık talebin değişim katsayısı =STDSAPMA.S(aylık_talep)/ORTALAMA(aylık_talep) düşükse X (istikrarlı), yüksekse Z (düzensiz) sınıfına girer. AX ürünler sıkı ve otomatik planlamaya, CZ ürünler ise siparişe göre tedarike daha uygundur.

Emniyet stoğu, yeniden sipariş noktası ve ekonomik sipariş miktarı nasıl hesaplanır?

Temsili bir ürün: günlük ortalama talep 40 adet, günlük talebin standart sapması 12 adet, tedarik süresi 5 gün, hedef hizmet düzeyi %95.

Gösterge	Formül (Türkçe Excel)	Temsili sonuç
z değeri (%95)	`=NORM.S.TERS(0,95)`	1,645
Emniyet stoğu	`=YUKARIYUVARLA(B112KAREKÖK(5);0)`	45 adet
Yeniden sipariş noktası	`=40*5+B2`	245 adet
Ekonomik sipariş miktarı (EOQ)	`=KAREKÖK(2146001500/12)`	≈ 1.911 adet

EOQ satırında yıllık talep 14.600 adet (40 × 365), sipariş başına sabit maliyet 1.500 TL ve birim başına yıllık stok tutma maliyeti 12 TL varsayılmıştır. Formüldeki NORM.S.TERS (NORM.S.INV) seçilen hizmet düzeyine karşılık gelen standart normal değeri verir. Bu basit model talebin normal dağıldığını ve tedarik süresinin sabit olduğunu varsayar. Tedarik süresi de değişkense emniyet stoğu =z*KAREKÖK(LT*σ_talep^2+ort_talep^2*σ_LT^2) ile hesaplanır; süre belirsizliği çoğu zaman talep belirsizliğinden daha fazla emniyet stoğu gerektirir.

Stok seviyesi yeniden sipariş noktasının altına düşen ürünleri işaretlemek için stok listesinde =EĞER([@Kalan]<=[@ROP];"SİPARİŞ VER";"") sütunu ve koşullu biçimlendirme yeterlidir. Talep tahmini gerektiren durumlarda Excel’de zaman serisi analizi rehberindeki yöntemlerle günlük ortalama talep yerine tahmin edilen talep kullanılabilir.

Tedarikçi performansı nasıl ölçülür?

tblSatinalma tablosundan her tedarikçi için üç temel ölçüt hesaplanır: zamanında teslim oranı, kalite kabul oranı (=TOPLA(Kabul Edilen Adet)/TOPLA(Adet)) ve ortalama tedarik süresi ile bu sürenin standart sapması (güvenilirlik). Fiyat performansı için de birim fiyatın piyasa veya hedef fiyata oranı eklenebilir. Bu ölçütleri 0–100 puana çevirip ağırlıklandırarak tek bir karne puanı elde edersiniz:

Ölçüt	Ağırlık	Tedarikçi A (temsili)	Tedarikçi B (temsili)
Zamanında teslim	%40	94	81
Kalite kabul	%30	98	99
Tedarik süresi güvenilirliği	%15	85	70
Fiyat	%15	75	92
Ağırlıklı puan	`=TOPLA.ÇARPIM($B$2:$B$5;C2:C5)`	91,0	86,4

Ağırlıklar şirketin önceliklerine göre belirlenmeli ve toplamı %100 olmalıdır. Tedarikçi B daha ucuz olsa da teslim güvenilirliği düşük olduğu için genel puanda geride kalıyor; bu, satın alma kararlarında yalnızca fiyata bakmanın riskini somut olarak gösterir. Karne puanlarının çeyreklik değişimini izlemek, tedarikçi görüşmelerinde veriye dayalı bir zemin sağlar.

Lojistik verinizi birlikte analiz edelim

Sipariş, sevkiyat, stok veya tedarikçi verilerinizden OTIF ve stok raporu, emniyet stoğu hesabı, tedarikçi karnesi ya da tez kapsamında tedarik zinciri analizi yaptırmak isterseniz WhatsApp üzerinden yazabilir ya da iletişim ve teklif sayfamızı kullanabilirsiniz. Kapsam netleştikten sonra çalışma planı ve ücret bütçenize göre birlikte belirlenir.

Taşıma maliyeti ve desi hesabı Excel’de nasıl yapılır?

Kargo ve nakliye maliyetini karşılaştırırken gerçek ağırlık ile hacimsel ağırlığın (desi) büyüğü esas alınır. Kargo firmalarında yaygın kullanılan desi formülü =(En*Boy*Yükseklik)/3000 şeklindedir (ölçüler cm); bölen değer firmaya ve hizmet türüne göre değişebileceği için sözleşmenizdeki değeri bir hücrede tutun. Ücretlendirilen ağırlık: =MAK([@[Ağırlık (kg)]];[@Desi]).

Taşıyıcı karşılaştırmasında birim maliyetleri (TL/sipariş, TL/kg, TL/desi, kendi filonuz için TL/km) PivotTable’da bölge kırılımıyla gösterin. Birim maliyeti düşük ama OTIF’i zayıf bir taşıyıcının geç teslimat nedeniyle oluşan müşteri kaybı ve tekrar gönderim maliyeti, birim fiyat farkını kolayca aşabilir; bu nedenle maliyet ve hizmet göstergelerini aynı tabloda değerlendirmek gerekir.

Depo verimliliği hangi göstergelerle izlenir?

Depo operasyonlarında en çok kullanılan göstergeler şunlardır: saat başına toplanan sipariş satırı (toplama verimliliği), toplama doğruluğu (hatasız toplanan satır / toplam satır), sipariş hazırlama süresi (sipariş girişinden sevke kadar geçen süre), depo doluluk oranı (kullanılan raf adresi / toplam adres) ve sayım farkı oranı (sistem stoğu ile fiziksel sayım arasındaki farkın değeri / toplam stok değeri). Toplama verimliliği için tblToplama tablosunda personel, tarih, satır sayısı ve çalışılan saat tutulur; =ÇOKETOPLA(tblToplama[Satır];tblToplama[Personel];A2)/ÇOKETOPLA(tblToplama[Saat];tblToplama[Personel];A2) personel bazında saatlik satır sayısını verir.

Bu göstergeleri yorumlarken iş yükü karmasını unutmayın: tek kalemli e-ticaret siparişleri ile çok kalemli bayi siparişlerinin saatlik satır sayıları doğrudan karşılaştırılamaz. Sipariş tipine göre ayrı hedefler belirlemek ve dönemsel yoğunlukları (kampanya haftaları, ay sonu) ayrı izlemek daha adil bir değerlendirme sağlar. ABC sınıflamasını depo yerleşimine uygulamak da etkilidir: sık çıkan A ürünlerini sevkiyat alanına yakın ve bel hizasındaki raflara yerleştirmek toplama mesafesini kısaltır. Yerleşim değişikliğinin etkisini, değişiklik öncesi ve sonrası birkaç haftalık toplama verimliliğini karşılaştırarak ölçebilirsiniz; karşılaştırmada aynı sipariş tiplerini ve benzer yoğunluktaki haftaları kullanmak, sonucun mevsimsellikten etkilenmesini azaltır.

Lojistik dashboard’u nasıl kurulur?

Haftalık veya aylık bir lojistik kontrol panelinde şu göstergeler yeterli bir başlangıçtır: sipariş sayısı, OTIF ve bileşenleri, medyan teslim süresi, geciken sipariş listesi, stok değeri ve stokta kalma günü, yeniden sipariş noktasının altındaki ürün sayısı, tedarikçi karne puanları ve sipariş başına taşıma maliyeti. Dilimleyicilerle bölge, taşıyıcı ve ürün grubuna göre filtreleme ekleyin. Geciken siparişleri otomatik listelemek için =FİLTRE(tblSiparis[[Sipariş No]:[Taşıyıcı]];tblSiparis[Gecikme]>0;"Gecikme yok") kullanılabilir. Satış tarafıyla birlikte değerlendirmek için Excel’de satış verileri analizi, üretim tarafı için Excel’de imalat sektörü veri analizi rehberlerimize göz atabilirsiniz.

Lojistik analizlerinde en sık yapılan hatalar nelerdir?

OTIF’i zamanında ve eksiksiz oranlarının çarpımıyla hesaplamak: Sipariş düzeyinde hesaplanmalıdır.
Ortalama teslim süresine güvenmek: Birkaç çok geç sipariş ortalamayı bozar; medyan ve 90. yüzdelik birlikte izlenmelidir.
Emniyet stoğunu sabit bir gün sayısıyla belirlemek: Talep ve tedarik süresi değişkenliğini dikkate almaz.
ABC sınıflamasını bir kez yapıp unutmak: Ürün karması değiştikçe sınıflama en az yılda bir güncellenmelidir.
Takvim günü ile iş gününü karıştırmak: Hedef süreler hangi gün türüyle tanımlandıysa ölçüm de aynı türle yapılmalıdır.

Sık sorulan sorular

OTIF nedir ve nasıl hesaplanır?

OTIF (On Time In Full), siparişlerin hem söz verilen tarihte hem de istenen miktarın tamamıyla teslim edilme oranıdır. Her sipariş için zamanında ve eksiksiz koşulları birlikte kontrol edilir, ikisini de sağlayan siparişler toplam siparişe bölünür.

Stok devir hızı nasıl hesaplanır?

Dönemin satılan malın maliyeti, aynı dönemin ortalama stok değerine bölünür. 365’in devir hızına bölünmesi stokların ortalama kaç gün depoda kaldığını gösterir.

Emniyet stoğu Excel'de nasıl hesaplanır?

Basit modelde emniyet stoğu = NORM.S.TERS(hizmet düzeyi) × günlük talebin standart sapması × KAREKÖK(tedarik süresi). Tedarik süresi de değişkense formüle süre değişkenliği eklenir.

ABC analizinde eşikler neden %80 ve %95?

Bunlar Pareto ilkesine dayanan yaygın ve pratik eşiklerdir; zorunlu değildir. Ürün sayısına ve yönetim kapasitesine göre farklı eşikler seçilebilir.

Desi nasıl hesaplanır?

Paketin en, boy ve yüksekliği (cm) çarpılıp kargo firmasının belirlediği bölen değere (yaygın olarak 3000) bölünür. Ücretlendirmede gerçek ağırlık ile desinin büyüğü esas alınır.

İlgili rehberler ve hizmetler

Veri, modern dünyanın en değerli varlıklarından biri haline gelmiştir ve iş dünyasında, araştırmada ve karar verme süreçlerinde kritik bir rol oynamaktadır. Biz, veri analizi konusundaki tutkulu bir topluluk olarak, veri biliminin ve analizinin gücünü vurguluyoruz. Web sitemiz, işletmelerden akademisyenlere ve veri meraklılarına kadar herkesin veri analizi becerilerini geliştirmesine yardımcı olmayı amaçlayan bir kaynaktır. Misyonumuz, veri analizi sürecini anlaşılır ve erişilebilir hale getirmek, verilerin içinde gizlenen değeri açığa çıkarmak ve daha bilinçli kararlar almalarını desteklemektir.

Web sitemiz, geniş bir veri analizi yelpazesi sunmaktadır. Hangi sektörde olursanız olun veya hangi veri analizi aracını kullanıyorsanız kullanın, ihtiyaçlarınıza uygun hizmetler sunuyoruz. SPSS, R, Python, Excel veya diğer analiz araçlarını kullanarak veri madenciliği, hipotez testleri, regresyon analizi, zaman serisi tahmini, segmentasyon ve daha fazlasını içeren çeşitli analiz türlerini kapsarız. Ayrıca, öğrenmeyi kolaylaştırmak için zengin kaynaklar, öğreticiler ve interaktif araçlar sunuyoruz.

Web sitemizdeki uzman ekibimiz, veri analizi konusundaki derin bilgileri ve deneyimleri ile sizin yanınızda. Kullanıcıların ihtiyaçlarını anlamak ve en iyi sonuçları elde etmelerine yardımcı olmak için buradayız. Veri analizi sürecini karmaşıklıktan arındırarak ve pratik örneklerle anlatarak size rehberlik edeceğiz. Veriye dayalı kararlarınızı daha güçlü hale getirmenize yardımcı olmak için buradayız. Siz de veri analizi dünyasına adım atmak veya mevcut becerilerinizi geliştirmek istiyorsanız, sitemizi keşfedin ve veri ile daha derinlemesine bağlantı kurun.

Excel’de Lojistik ve Tedarik Zinciri Veri Analizi ve Raporlama