Mağaza Satış Takibi Excel'de Nasıl Yapılır? Kâr ve ABC Analizi

Her akşam kasada ne kadar ciro yaptığınızı biliyorsunuzdur. Ama bunun ne kadarı kâr? Hangi ürün gelirin büyük kısmını getiriyor, hangisi rafta bekliyor? Haftanın en iyi günü hangisi, hangi satış elemanı daha iyi satıyor? Bu soruların cevabı satış kayıtlarında gizli; yeter ki doğru şekilde tutulsun.

Bu yazıda küçük bir mağaza için Excel'de satış defteri kurmayı, kâr ve mark-up hesaplamayı ve ABC analiziyle ürünlerinizi gruplamayı anlatıyorum.

Satışı tek satır olarak kaydedin

Temel kural: her satış bir satır. Gerekli sütunlar:

• Tarih
• Ürün (listeden seçilen)
• Adet
• İndirim (yüzde)
• Satış elemanı
• Ödeme yöntemi

Fiyat, maliyet ve kâr sütunlarını elle yazmayın; ürün listesinden formülle çekin. Ürünler sayfasında stok kodu, ad, kategori, alış ve satış fiyatı bulunsun. Satış sayfasında fiyatı şöyle getirirsiniz:

=DÜŞEYARA(B2;Ürünler!A:E;5;0)

Ardından:

• Gelir = fiyat × adet × (1 − indirim)
• Maliyet = alış fiyatı × adet
• Kâr = gelir − maliyet

Mark-up ve marj aynı şey değildir

İkisi sık karıştırılır. Mark-up maliyetin üzerine konan yüzdedir, marj ise gelirin ne kadarının kâr olduğunu gösterir.

Formüller: mark-up = kâr / maliyet, marj = kâr / gelir. "%50 ekliyorum" demek, %50 kâr marjı demek değildir; gerçek marj %33'tür. Fiyatlandırırken hangisini kullandığınızı bilin.

ABC analizi: gelirin çoğunu kim getiriyor?

Pareto ilkesine yakın bir durum çoğu mağazada görülür: az sayıda ürün gelirin büyük bölümünü getirir. ABC analizi bunu ölçer:

1. Ürünleri döneme göre gelire dönüştürün (ÇOKETOPLA ile).
2. Gelire göre büyükten küçüğe sıralayın.
3. Her ürünün toplam içindeki payını hesaplayın.
4. Birikimli payı bulun.
5. İlk %80'e kadar olanlar A, sonraki %15 B, kalanlar C.

Örnek: 100 ürünlü bir mağazada 15 ürün gelirin %80'ini getiriyorsa onlar A grubudur. Bunların stoğunu kesinlikle bitirmemelisiniz. C grubundakiler çok yer kaplıyorsa tasfiyeyi düşünebilirsiniz. Birikimli pay formülü: =TOPLA($D$2:D2) gibi, her satırda genişleyen toplam kullanın.

Grup formülü: =EĞER(E2<=0,8;"A";EĞER(E2<=0,95;"B";"C")). Sınırları kendi işinize göre değiştirebilirsiniz.

Zaman ve kişi kırılımları

• Haftanın günü: =METNEÇEVİR(A2;"gggg") tarihi gün adına çevirir. ETOPLA ile her gün için geliri toplayın; pazartesi günü çok zayıfsa personel planını buna göre yapın.
• Aylar: gidişatı görmek için ay bazında toplayın; mevsimsellik varsa geçen yılla kıyaslayın.
• Satış elemanları: yalnızca ciro değil, kâr ve ortalama sepet de bakın. Düşük fiyata çok satan biri, kâr getirmeyebilir.

Sık yapılan hatalar

• Ürün fiyatını değiştirince geçmişi bozmak. Listedeki fiyatı güncellerseniz eski satışlar da yeni fiyatla hesaplanır. Geçmişi korumak için yeni fiyatlı ürüne farklı kod verin.
• İndirimi kâra katmamak. İndirimli satışta marj düşer; hesapta mutlaka gözükmelidir.
• Cirodan kâr çıkarmak. Kira, personel ve diğer giderler bu tablonun dışındadır; burada brüt kâr görürsünüz.
• Tabloyu yıllarca büyütmek. Dönem sonunda bir kopya saklayıp yenisine başlayın.

Hazır Excel şablonu

Şablon bu mantığı hazır sunar ve ücretlidir. Dört sayfadan oluşur:

• «Listeler»: ürünler (stok kodu, ad, kategori, alış ve satış fiyatı), satış elemanları, ödeme yöntemleri ve ABC grup sınırları.
• «Satışlar»: 3000 satıra kadar; tarih, listeden ürün, adet, indirim, satış elemanı ve ödeme girilir. Fiyat, gelir, maliyet, kâr ve mark-up kendiliğinden hesaplanır.
• «ABC analizi»: ürünlerin gelire göre sıralaması, pay, birikimli pay ve A, B veya C grubu.
• «Özet»: toplamlar, haftanın günlerine, aylara, satış elemanlarına ve ödeme yöntemlerine göre sonuçlar, en iyi 10 ürün ve dört grafik.

A grubu gelirin ilk %80'ini, B sonraki %15'i getiren ürünlerdir; sınırları “Listeler” sayfasından değiştirebilirsiniz. Tablo 3000 satış satırı ve 200 ürün için tasarlanmıştır. Küçük mağaza, satış noktası ve e-ticaret sitesi sahipleri, serbest çalışanlar ve esnaflar için uygundur.

Pakette boş şablon ve kurgusal verilerle doldurulmuş bir örnek var. Makro yoktur; Excel 2007 ve üstü ile LibreOffice'te çalışır, dosya Türkçedir. Bu sıradan formüllerle çalışan bir Excel tablosudur, muhasebe programı değildir. Oranları ve eşikleri kullanmaya başlamadan önce yürürlükteki düzenlemelerle karşılaştırın; hesaplama yaklaşıktır.

Sık sorulan sorular

Kasa programım varsa bu tabloya gerek var mı?

Kasa programı satışı kaydeder; ama ABC analizi, kırılımlar ve kâr yapısı için dışa aktardığınız veriyi bir tabloda yorumlamanız gerekebilir. Excel bu analiz için esnek bir araçtır.

ABC analizini ne sıklıkla yenilemeliyim?

Üç ayda bir yeterlidir. Mevsimsel işlerde dönemi sezona göre seçin.

Brüt kâr ile net kâr arasındaki fark nedir?

Brüt kâr gelirden yalnızca ürün maliyetini düşer. Net kâr ise kira, maaş ve diğer giderleri de çıkarır.

Birden fazla şubeyi tek tabloda izleyebilir miyim?

Mümkündür; satış satırlarına şube sütunu ekleyerek yapabilirsiniz. Veri hacmi büyüdükçe dönemlere ayırmak daha rahat olur.