E-Ticaret Kâr Hesap Tablosu (Excel): Ürün Bazında Net Kâr Şablonu ve Formüller

E-ticaret kâr hesap tablosu, her ürünün satış fiyatından komisyon, kargo, sipariş başı bedeller, ambalaj, reklam, iade payı ve alış maliyetini düşerek birim net kârı ve kâr marjını gösteren bir Excel çalışma sayfasıdır. Doğru kurulduğunda hangi ürünün kazandırdığını, hangisinin satıldıkça zarar ettirdiğini birkaç dakikada görürsünüz; tek gereken, her ürün için aynı sütunları eksiksiz doldurmaktır.

Bu yazıda tablonun sütunlarını tek tek tanımlıyor, her sütunun formülünü Türkçe Excel yazımıyla veriyor, zarar eden ürünleri kendiliğinden kırmızıya boyayan koşullu biçimlendirmeyi kuruyor ve kanal bazında özet sekmesini hazırlıyoruz. Örnekteki tüm rakamlar varsayımsaldır; kendi fiyat, oran ve maliyetlerinizle değiştirin.

Tablonun sütunları

Tablonun mantığı basittir: bir satır, bir kanaldaki bir üründür. Aynı ürünü Trendyol'da ve kendi sitenizde satıyorsanız iki ayrı satır açın, çünkü komisyon, kargo ve reklam maliyeti kanala göre değişir.

Tüm tutarları KDV dahil girmek hesabı sadeleştirir: satış fiyatı da giderler de faturadaki tutarlarla aynı olur. KDV hariç kâra en sonda, tek bir sütunda geçilir.

Sütunlarda en çok atlanan iki kalem şunlardır:

• İade payı. Bir ürünün iade oranı %8 ise ve her iade size ortalama 150 TL'ye mal oluyorsa, gönderilen her siparişe yaklaşık 12 TL iade payı düşer. Bu sütunu boş bırakan tablo, iadesi yüksek ürünleri olduğundan kârlı gösterir. İade oranının nasıl hesaplandığını iade oranı rehberimizde anlattık.
• Reklam. Bir ürüne ayda 3.000 TL reklam harcayıp 150 adet sattıysanız sipariş başı reklam maliyeti 20 TL'dir. Reklamı genel gider sayıp ürüne dağıtmazsanız reklamla satan ürünün kârı şişer.

Alış maliyetini yalnız fatura tutarı olarak değil, ürünün size geliş maliyeti olarak yazın: navlun, gümrük ya da depoya taşıma gibi kalemler varsa birim başına ekleyin. Bu hesabın ayrıntısı ürün maliyeti yazımızda yer alıyor.

Formüller

Aşağıdaki formüller Türkçe Excel içindir: işlev adları Türkçe, bağımsız değişken ayırıcısı noktalı virgül, ondalık ayırıcısı virgüldür. İngilizce Excel ya da Google E-Tablolar'da EĞER yerine IF, TOPLA yerine SUM, ETOPLA yerine SUMIF yazılır; ayırıcı virgül, ondalık nokta olur. Formülleri 2. satıra yazıp aşağı doğru kopyalayın.

Komisyon tutarı (F sütunu)

F2 hücresine: =EĞER(C2="Hepsiburada";D2*E2*1,2;D2*E2)

Trendyol'un resmî listesindeki komisyon oranları KDV dahildir; tabloda %21 yazıyorsa kesinti satış fiyatının %21'idir. Hepsiburada'nın liste oranlarına ise KDV eklenir; %15'lik oran fiilen satış fiyatının %18'ine denk gelir. Formül bu farkı kanal adına bakarak hesaplar. Kategorinizin oranını Trendyol komisyon oranları ve Hepsiburada komisyon oranları sayfalarından görebilirsiniz; ama hesapta her zaman kendi satıcı panelinizde görünen güncel oranı kullanın.

Birim kâr (M ve N sütunları)

M2 hücresine: =D2-TOPLA(F2:L2)

Satış fiyatından F ile L arasındaki tüm giderler düşülür. Sonuç KDV dahil birim kârdır.

N2 hücresine: =M2/1,2

Örnekteki ürün ve giderlerin tamamı %20 KDV'li varsayılmıştır; bu varsayımla KDV dahil kârı 1,2'ye bölmek KDV hariç kârı verir. Farklı KDV oranlı ürünleriniz varsa tabloya ayrı bir "KDV oranı" sütunu ekleyip formülü ona bağlayın ve hesap yöntemini muhasebecinizle netleştirin.

Marj ve toplam kâr (O ve Q sütunları)

O2 hücresine: =EĞER(D2=0;0;N2/(D2/1,2))

Marj, KDV hariç kârın KDV hariç satış fiyatına oranıdır. O sütununu yüzde biçiminde gösterin. EĞER kısmı, fiyatı henüz girilmemiş satırlarda sıfıra bölme hatasını önler.

Q2 hücresine: =N2*P2

Birim kârı aylık adetle çarpar. Bir ürünün işletmenize gerçek katkısı bu sütundur: yüksek marjlı ama hiç satmayan ürünün toplam kârı da düşüktür.

Örnek: beş satırlık tablo

"Diğer giderler" ambalaj, reklam ve iade payının toplamıdır (tişört için 5 + 15 + 12). Kendi sitedeki %3, ödeme altyapısı komisyonu için seçilmiş örnek bir orandır. Toplam kâr sütunu Excel'in yuvarlanmamış ara değerleriyle hesaplandığı için kuruş farkı görebilirsiniz.

Tablo ilk bakışta bir şeyi açıkça gösteriyor: en çok satan ürün olan telefon kılıfı her satışta 9,23 TL zarar ettiriyor ve ayda 200 adetle toplam kârdan 1.846,67 TL götürüyor. Ciroya bakan bir satıcı bu ürünü başarılı sayardı. Aynı ürünün sabit giderleri (kargo, sipariş başı bedel, ambalaj, reklam, iade payı ve alış) 131 TL tutuyor; %20 komisyonla başabaş fiyatı 131 ÷ 0,80 = 163,75 TL'dir. Fiyatı yükseltmek, set olarak satmak ya da kargo maliyetini düşürmek gerekir. Bu tür ürünleri gruplamayı ürün bazında kârlılık analizi yazımızda ayrıntılı anlattık.

Koşullu biçimlendirme

Yüzlerce satırlık bir tabloda sorunlu ürünleri gözle aramak yerine Excel'in onları işaretlemesini sağlayın. Giriş sekmesindeki Koşullu Biçimlendirme menüsünden üç kural tanımlayın:

1. Zarar eden satır (kırmızı). A2:Q200 aralığını seçin, "Biçimlendirilecek hücreleri belirlemek için formül kullan" seçeneğine =$N2<0 yazın ve açık kırmızı dolgu seçin. Dolar işareti sütunu sabitler; böylece kâr negatifse satırın tamamı boyanır.
2. Hedefin altındaki marj (sarı). Aynı aralığa =VE($N2>=0;$O2<0,12) kuralını ekleyin. Örnekte hedef marj %12'dir; kendi hedefinizi yazın. Örnek tabloda tişört ve kulaklık sarıya boyanır.
3. Eksik veri (gri). =YADA($D2="";$L2="") kuralıyla fiyatı ya da alış maliyeti boş satırları griye boyayın. Alış maliyeti boş bir satır, kâr hesabında olduğundan iyi görünür.

Kuralların sırası önemlidir: Koşullu Biçimlendirme Kurallarını Yönet ekranında kırmızı kuralı en üste taşıyın. Ayrıca tabloyu Ekle sekmesinden "Tablo" olarak biçimlendirirseniz yeni satır eklediğinizde formüller ve kurallar kendiliğinden genişler.

Kanal bazında sekme

Ürün sekmesi her satırı ayrı gösterir; ama "Hangi kanal ne kazandırıyor?" sorusu için bir özet sekmesi açın. Yeni bir sayfaya "Kanallar" adını verin ve şu düzeni kurun:

Örnek tabloda sonuç şöyle çıkar: Trendyol üç ürünle 345 adet ve 5.456,26 TL, Hepsiburada 60 adet ve 3.495,90 TL, kendi site 30 adet ve 6.897,58 TL kâr getiriyor. Kendi sitenin adet başı kârı en yüksek kanal olduğu görülüyor; ama bu sitede reklam ve ödeme altyapısı maliyetinin doğru girildiğinden emin olmadan karar vermeyin.

Parametreler sekmesi

Komisyon oranlarını ve kargo ücretlerini her satıra elle yazmak hata kaynağıdır. Üçüncü bir "Parametreler" sekmesinde kategori ve kanal bazında oranları, desi aralığına göre kargo ücretlerini tutun ve ürün sekmesinden DÜŞEYARA (İngilizcede VLOOKUP) ile çekin. Örneğin E2 hücresine =DÜŞEYARA(R2;Parametreler!A:B;2;YANLIŞ) yazarsanız R sütunundaki kategori adına göre oran gelir. Oran değiştiğinde yalnız Parametreler sekmesini güncellersiniz, tüm satırlar kendiliğinden yenilenir. Kampanya dönemindeki farklı oranları ayrı senaryolarla denemek için kampanya kâr simülasyonu yazımıza bakabilirsiniz.

Excel'in sınırları ve otomasyon

Bu tablo, kârlılığı ilk kez ürün bazında görmek için çok iyi bir başlangıçtır. Ama birkaç yüz ürüne ve birden fazla kanala ulaştığınızda sınırları belirginleşir:

• Gecikme. Tablo, en son güncellediğiniz gün kadar doğrudur. Komisyon oranı ya da kargo tarifesi değiştiğinde fark etmeniz haftalar sürebilir.
• Ortalama ile gerçeğin farkı. Tabloya yazdığınız kargo ücreti bir ortalamadır; pazaryerinin kestiği gerçek kargo faturası desiye, bareme ve kampanyaya göre farklı çıkabilir.
• Sipariş bazında görünmezlik. Tablo ürün başına tek bir fiyat varsayar; oysa aynı ürün gün içinde indirimli, kuponlu ya da sepette başka ürünle satılır.
• Elle veri girişi. Her ay satış adetlerini ve reklam harcamalarını kopyalamak zaman alır ve kopyalama hatası kaçınılmazdır.

Bu noktada tabloyu bırakmak zorunda değilsiniz; ama sürekli güncel kalması gereken kısmı otomatiğe bağlamak mantıklıdır. Hastürk CRM'in gelir, gider ve kâr-zarar bölümü, Trendyol ve Hepsiburada'da komisyon, kargo, hizmet bedeli ve reklam kesintilerini pazaryerinin kestiği faturalardan çeker; her siparişin ve her ürünün net kârını hesaplar, zarar eden ürünler için uyarı verir. Diğer kanallarda komisyon oranını bir kez girmeniz yeterlidir. Alış maliyetlerini ürün kartından ya da Excel ile toplu yükleyebilirsiniz; toplu yüklemenin adımlarını Excel ile toplu fiyat ve stok güncelleme yazımızda anlattık.

Excel tablonuz ise senaryo denemek için değerini korur: "Fiyatı 20 TL artırırsam marj ne olur?" ya da "Kargo 10 TL zamlanırsa hangi ürünler zarara geçer?" gibi soruları tabloda birkaç hücre değiştirerek yanıtlayabilirsiniz. Tek bir ürün için hızlı hesap gerekiyorsa satış fiyatı hesaplama aracı da işinizi görür.

Sık sorulan sorular

Kâr tablosunda tutarları KDV dahil mi yazmalıyım?

Pratik olan, tüm tutarları faturalardaki gibi KDV dahil yazıp en sonda tek bir sütunda KDV hariç kâra geçmektir. Böylece her hücrede KDV ayırmakla uğraşmazsınız. Örnekteki 1,2'ye bölme, tüm kalemlerin %20 KDV'li olduğu varsayımına dayanır; farklı oranlı ürünlerde ayrı bir KDV sütunu kullanın.

Trendyol ve Hepsiburada komisyonunu aynı formülle hesaplayabilir miyim?

Hayır, ayırmanız gerekir. Trendyol'un liste oranları KDV dahildir, satış fiyatıyla doğrudan çarpılır. Hepsiburada'nın liste oranlarına KDV eklenir; bu yüzden formülde 1,2 ile çarpılır. Yazıdaki EĞER formülü bu ayrımı kanal adına bakarak yapar.

İade payını nasıl hesaplarım?

Ürünün iade oranını bir iadenin size ortalama maliyetiyle çarpın. Örneğin %10 iade oranı ve iade başına 120 TL maliyet, gönderilen her sipariş için 12 TL iade payı demektir. Bir iadenin maliyetine gidiş ve dönüş kargo, ambalaj, işçilik ve satılamayan ürün kaybı girer.

Tabloyu ne sıklıkla güncellemeliyim?

Satış adetlerini ve reklam harcamalarını ayda bir, komisyon oranı ya da kargo tarifesi değiştiğinde ise hemen güncelleyin. Kampanya dönemlerini ayrı bir kopyada inceleyin; indirimli fiyatlarla normal dönem verisini karıştırmayın.

Google E-Tablolar'da da çalışır mı?

Evet. Aynı sütun düzeni ve formüller Google E-Tablolar'da çalışır; dil ayarına göre işlev adları İngilizce (IF, SUM, SUMIF, VLOOKUP) ve ayırıcı virgül olabilir. Koşullu biçimlendirmede "Özel formül" seçeneğini kullanın.

Sonuç

İyi bir kâr tablosu, her ürünü aynı gider sütunlarından geçirir ve sonucu birim kâr, marj ve toplam kâr olarak gösterir. Komisyonun KDV'li olup olmadığını kanala göre ayırın, iade ve reklamı ürüne dağıtın, zarar eden satırları koşullu biçimlendirmeyle görünür kılın ve kanal özetiyle hangi kanalın kazandırdığını izleyin. Tablonun güncel kalması zorlaştığında sipariş bazındaki gerçek kesintileri otomatik takip etmek için canlı demoyu inceleyebilirsiniz.