MS Excel Kasa Defteri / Kasalar Hesabını USD Kurunda Takip Etme

MS Excel Kasalar Hesabını USD Kurunda Takip Etme Eğitim Videosu

Eğitim videomuz Youtube linki aşağıdadır.

Excel Kasa Defteri / Kasalar Hesabını USD Olarak Takip Etme Detay Anlatım

Kasa Defteri başlığı altında yayınladığımız eğitim videolarında oluşturduğumuz çalışma kitabımızı sırası ile aşağıdaki şekilde geliştirmiştik:

- Gelir Gider Tablosu

- Nakit Akış Tablosu

- USD Kur Serisi

- EURO Kur Serisi

- Gram Altın Fiyat Serisi

Bu uygulamada çalışmamızı bir adım daha ileri taşıyalım ve kasalar hesabımızı / varlıklarımızı USD bazında takip edecek şekilde geliştirelim. Bunu neden yapıyoruz? Bunun en güzel cevabını şu günlerde yaşadığımız enflasyonist dönem vermekte ve sabit gelirli çalışanlar temel ihtiyaçlarda dahi alım gücündeki erimeyi iliklerine kadar hissetmektedirler. Bu çalışma genel kabule uygun olarak USD bazlı çalışıldı fakat geliştirilen formüllerde ufak değişikler yaparak EURO ya da Gram Altın bazında istediğiniz şekilde hazırlayabilirsiniz.

Excel çalışma kitabımızda "Kasa Defteri" sayfasındaki Gelir Gider Tablosunun en son hali aşağıdaki ekran alıntısında verilmiştir.

Öncelikle bu tabloda görsel bazı düzenlemeler yapacağız. İşlem adımları;

- En üste boş bir satır ekleyelim ve "O1" hücresine Kasalar Durum başlığını atalım.

- M2:M21 hücre aralığını seçelim ve biçim kopyalama aracı ile O2:U21 aralığında biçimleri kopyalayalım.

- O2 hücresinden başlayarak U2 ye kadar sırası ile TL, USD, EURO, ALT, USD Karşılık, Kambiyo Kar/Zarar ve Parite başlıklarını atalım.

- Tablodaki ilk kaydın üstüne boş bir satır ekleyelim.

Sonuç olarak ekran görüntüsü aşağıdaki şekilde olmalı.

O ile R sütun aralığında her kayıtta, o kayıt gerçekleştikten sonraki kasalar toplamını kendi kur biriminde göstereceğiz. Uzun şekilde örneklendirirsek; O4 hücresinde ilk kayıttaki işlem (transaction) gerçekleştikten (settled) sonraki TL Kasalarının toplamını, P4 de USD Kasalarının toplamını, Q4 de EURO Kasalarının toplamını ve R4 de Altın Kasalarının toplamını göstereceğiz. Bunun için O4 de aşağıdaki formülü giriyoruz.

=O3+EĞER($E4=O$2;$C4-$D4;0)

Bu formülde ilk kısımda O3 hücresine yapılan başvuru ile kasanın bir kayıt önceki değeri alınır. İkinci kısımda işlemin para birimi O2 hücresine (yani TL ye) eşit ise işlemdeki net değer (Çıkış-Giriş) eklenir. Değil ise sıfır eklenir. Formülde işlem kuruna yapılan başvuruyu sütunda ($E4), TL ye yapılan başvuruyu satırda (O$2) ve net değere yapılan başvuruyu sütunlarda ($C4-$D4) sabitleyelim ve O4:R21 aralığındaki tüm hücrelere kopyalayalım. Sonuç olarak aşağıdaki ekran görüntüsünü elde edeceksiniz. Beklendiği üzere son kayıttaki para birimi bazlı kasalar toplamı ile tablo altındaki icmal tablosunun toplamlar tutmaktadır. Formülümüzde bir sorun görünmüyor.

Şimdi S4 hücresinde ilk işlem sonundaki kasalar genel toplamının USD karşılığını aldıracağız. Bunun için S4 hücresine aşağıdaki formül girilmiştir.

=O4/K4+P4+Q4*L4/K4+R4*M4/K4

Bu formül dört kısımdan oluşmaktadır. Her bir kısımda sırası ile TL, USD, EURO ve ALTIN kasaların toplamlarını işlemin gerçekleştiği günün kapanış kurlarından parite değeri üreterek USD ye çevirmektedir. Sonuç olarak da USD karşılıkların toplamını yani işlem sonrasındaki kasalar toplanın USD değerini vermektedir. Formülü S4:S21 aralığında kopyalayalım. sonuç olarak aşağıdaki ekran görüntüsünü elde edeceğiz.

Sıradaki adımda USD dışındaki kasalarda tuttuğumuz varlıkların kronik (işlem tarihine göre ardışık) iki işlem arasındaki taşıma maliyetlerinin toplamını hesaplayacağız. Bu neden ile T4 hücresini boş geçerek T5 e aşağıdaki formülü yazıyoruz.

=O4/K5+P4+Q4*L5/K5+R4*M5/K5-S4

Bu formülü çıkarma işlemini referans alarak iki kısma ayırırsak ilk kısımda, bir önceki kayıt (işlem) sonucundaki TL, USD, EURO ve ALTIN kasalar toplamlarının bu kayıttaki (işlem) kurlara göre USD karşılığı hesaplanır. İkinci kısım bir önceki kaydın (işlemin) kasalar toplamının USD karşılığı olup işlem sonucunda aradaki farkı üretir. Formülü T5:T21 aralığında kopyalayalım ve ekran alıntısı üzerinden bir örnek ile biraz daha açıklamaya çalışalım. Aşağıdaki ekran alıntısında T7 hücresinde görünen -2,8 değeri; 20/11/2021 ~ 22/11/2021 tarihleri arasında TL kasalarda taşıdığımız 1000TL nin USD/TL paritesindeki değişimden kaynaklı maliyetidir. Kambiyo Kar/Zararının kayıt bazlı takibinin bir anlamı olmayıp, varlıklarımızın USD karşılığındaki dalgalanmaların açıklamasının muhasebesel bir bileşeni olacaktır.

Gelir Gider Tablosunda son olarak her kayıt için o işlemin kuruna göre parite değerini hesaplayacağız. Bunun için U4 hücresine aşağıdaki formülü girelim ve U4:U21 aralığındaki tüm hücrelere kopyalayalım.

=EĞER(E4="USD";1;EĞER(E4="TL";1/K4;EĞER(E4="EUR";L4/K4;EĞER(E4="ALT";M4/K4;"HATA"))))

Yukarıdaki formülde iç-içe (kaskat) EĞER işlevleri kullanılarak işlem kurunun USD karşılığı hesaplanmaktadır. Aşağıdaki ekran görüntüsünden alıntılayarak örneklendirirsek; U4 hücresinde işlem kuru USD olduğu için "1" değerini, U6 hücresinde işlem kuru TL olduğu için 0,09 (1/11,05) değerini üretmiştir. Parite bilgisini niye üretiyoruz diye sorabilirsiniz. Bunu bir sonraki adımda kasalar genel toplamı için USD Nakit Akış Tablosu hazırlarken kullanacağız.

USD Bazlı Nakit Akış Tablosu Oluşturma

Nakit Akış Tablosu başlığı ile yayınladığımız eğitim videomuzda tüm para birimlerindeki dönemsel değişimleri gösterecek bir çalışma yapmıştık. Uygulama sonunda Nakit Akış Tablomuzun ekran alıntısı aşağıdaki gibiydi.

Artık kasalar genel toplamının USD karşılığını kayıt bazında elde ettiğimize göre, rezerv para biriminde (Formülleri değiştirerek siz EURO ve ALT bazlı nakit akış tablolarınızı üretebilirsiniz) nakit akış tablosu oluşturabiliriz. Böylelikle kasalar / varlıklar toplamındaki dönemsel değişimleri takip edebiliriz.

İşlem adımlarımız;

- Yeni bir sayfa oluşturalım ve adını USD Nakit Akış Tablosu olarak değiştirelim.

- A1 hücresinden başlayarak sırası ile Dönem, Gelir, Gider, Tasarruf, Kambiyo Kar/Zarar, Trnsfr ve Kasalar Toplam başlıklarını atalım.

- B2 den G2 ye kadar USD yazlım ve tablomuzdaki sayısal değerlerin USD olduğunu belirtelim.

- A4:A6 hücrelerine sırası ile 2021/11, 2021/12 ve 2022/1 dönem verilerini el ile girelim.

- Başlıklar ve tablo içeriğini ayıracak biçimlendirmeler yapalım ve sonuç olarak aşağıdaki şekilde bir ekran görüntüsü elde edelim.

- B4 hücresine aşağıdaki formülü girelim. Formül ile Kasa Defteri sayfasında aşağıdaki koşulların kesişim kümesi içinde kalan kayıtların Giriş ve Parite değerlerinin çarpımının toplamı hesaplanmaktadır.

* Dönem verisi A4 hücresine eşit olanlar (örneğimiz için 2021/11),

* İşlem tipi B1 hücresine eşit olanlar (örneğimiz için "Gelir").

=TOPLA.ÇARPIM(('Kasa Defteri'!$A$3:$A$23=$A4)*('Kasa Defteri'!$C$3:$C$23)*('Kasa Defteri'!$H$3:$H$23=B$1)*('Kasa Defteri'!$U$3:$U$23))

Böylelikle 2021/11 dönemi için Gelirler toplamının USD karşılığını elde etmiş olduk.

- Aynı formülü C4 hücresine de kopyalayalım ve 2021/11 dönemi için Giderler toplamının USD karşılığını da elde edelim.

- D4 hücresinde işimiz kolay. Aşağıdaki formül ile Gelir - Gider yaparak dönemi için tasarruf miktarını hesaplattıracağız.

=B4-C4

- E4 hücresinde 2011/11 dönemi için Kambiyo Kar/Zarar toplamını hesaplattıracağız. Formül ile Kasa Defteri sayfasında dönem verisi A4 hücresine eşit olan (örneğimiz için 2021/11) kayıtların Kambiyo Kar/Zarar değerleri toplamı hesaplanmaktadır.

=TOPLA.ÇARPIM(('Kasa Defteri'!$A$3:$A$23=$A4)*('Kasa Defteri'!$T$3:$T$23))

- F4 hücresinde işimiz biraz daha karışık. Burada muhasebesel/programsal bir düzeltme işlemi yapılmaktadır. Şöyle ki Kasa Defteri sayfamızda kullandığımız kur değerleri TCMB EVDS sisteminden çekilen gün sonu kapanış değerleridir. Fakat Gelir Gider tablosundaki döviz transfer işlemleri gün içinde farklı kur değerlerinde yapılmaktadır. USD bazlı nakit akış kurgumuzda bu nedenle hesap farkları çıkmaktadır. Aşağıdaki formül ile F4 hücresinde, 2021/11 dönemi içindeki transferlerin işlem günündeki Giriş*Parite - Çıkış*Parite değerleri toplamı elde edilir.

=TOPLA.ÇARPIM(('Kasa Defteri'!$A$3:$A$23=$A4)*('Kasa Defteri'!$C$3:$C$23)*('Kasa Defteri'!$H$3:$H$23=F$1)*('Kasa Defteri'!$U$3:$U$23))-TOPLA.ÇARPIM(('Kasa Defteri'!$A$3:$A$23=$A4)*('Kasa Defteri'!$D$3:$D$23)*('Kasa Defteri'!$H$3:$H$23=F$1)*('Kasa Defteri'!$U$3:$U$23))

- G4 hücresinde aşağıdaki formül ile 2021/11 dönemi sonundaki kasalar toplamının USD karşılığını hesaplattıracağız. Formül dört bölümden oluşmaktadır. G3 hücresine yapılan başvuru ile bir önceki dönem değeri alınmaktadır. Bu değere sırası ile 2021/11 dönemi Tasarruflar (D4), Kambiyo Kar/Zarar (E4) ve Transfer Farkları (F4) eklenmektedir.

=G3+D4+E4+F4

- G4:G4 aralığını seçelim ve formülleri 2021/12 ve 2022/1 dönemleri karşılarına da kopyalayalım. sonuç olarak aşağıdaki ekran görüntüsünü elde edeceğiz.

- Son olarak kasalar toplamımızın USD karşılığının dönem bazlı değişimini çizgi grafik üzerinde gösterelim. Aşağıdaki ekran alıntısından sayısal olarak örneklersek 2022/1 cari dönemi içinde kasalarımızdaki nakitlerin USD karşılıkları toplamı 1546,59 USD imiş. Bu değeri Kasa Defteri sayfasındaki Gelir Gider Tablosun son kaydındaki değer ile karşılaştırırsak aynı sonucu bulduğumuzu gözlemleriz.