Fulya Ergeç

Excel Rehberi

|Excel'de Hisse Senedi Analizi |Excel’de Hisse Senedi Portföyü Yönetimi
|Kara Geçiş Noktasını Excel İle Hesaplama

Excel İle Vergi Uygulamaları

Excel'de vergi matrahına göre vergi kesintisini, vergi kesintisine göre vergi matrahını hesaplayabileceğiniz gibi, farklı değişkenlerin etkisini izlemek için senaryolar da kullanabilirsiniz.

Bu sayfada çok sayıda resim bulunmaktadır.

PERSONEL GELİR VERGİSİNİ HESAPLAMAK

EXCEL'DE bir çalışma sayfasında vergi matrahınızı yazarak vergi kesintisini hesaplayan bir uygulama hazırlayabilirsiniz. Bir işletmenin muhasebe bölümünde çalışıyorsanız, Excel'in tablolama özelliğinden yararlanarak ve Excel fonksiyonlarından olan VLOOKUP fonksiyonunu kullanarak personelinizin veya işletmenizin gelir vergisini kolayca hesaplayabilirsiniz. Personelinize ilişkin gelir vergisi cetvellerini hesaplayabilmeniz için öncelikle vergilendirilecek matraha göre hazırlanmış olan gelir vergisi dilimlerini ve kesinti oranlarını çalışma sayfasına bir tablo halinde yazmanız gerekmektedir. Bu tablo, ele alınan uygulama için çalışma sayfasında, B3:E10 alanında düzenlenmiştir.

Bu tabloda bulunan vergi dilimlerindeki değerler ve vergi kesinti oranları bu yıl uygulanan değerlerdir, değişik uygulamalar hazırlanmak istenirse bu değerler değiştirilebilir.

Gelir Vergisi Hesaplama isimli sayfa üzerindeki B3:E10 alanında bulunan C sütunundaki değerler sözkonusu vergi dilimlerinin alt sınır değerleridir. C ve D sütunlarında verilen değerler kullanıcı tarafından sabit olarak girilmesi gereken değerlerdir. C5:D10 alanında bulunan bu değerlere göre E5:E10 alanındaki değerler hesaplanmaktadır.

Farklı Vergi Dilimleri
E5:E10 alanında, ilgili vergi dilimi için sabit gelir vergisi kesinti miktarı hesaplanmaktadır. Vergi matrahınız 300 milyon TL ise vergi kesinti miktarınız E6 hücresindeki 75 milyon TL olacaktır.

Vergi matrahı 250 milyon TL olduğunda, bu değer 300 milyon TL'den küçük olduğu için E6 hücresinde hazır hesaplanmış olan değer, vergi kesintisi olarak bulunmayacaktır. 250 milyon tablodaki hiçbir vergi diliminin alt limitine eşit olmadığı için E5:E10 alanında hazırda hesaplanmış olan hiçbir vergi kesinti miktarı doğru değer olmayacaktır. Bu durumda 300 milyon TL'den küçük olan 250 milyon TL değerindeki vergi matrahı için vergi kesinti miktarı, sözkonusu tablo dışında ayrı bir formül (250 milyon * 0,25 ) yardımıyla 62,5 milyon TL olarak hesaplanacaktır.

Benzer şekilde vergi matrahı 350 milyon TL olduğunda vergi matrahı (300.000.001-600.000.000) vergi dilimine girmektedir. VLOOKUP fonksiyonu yardımıyla, 350 milyonun 300 milyonu için vergi kesinti miktarı tablodan (300 milyon * 0,25) 75 milyon TL (E6) olarak bulunur. Kalan 50 milyon için (300.000.001-600.000.000) vergi diliminin, vergi kesinti oranı uygulanır ve (50 milyon * 0,30) 15 milyon TL vergi hesaplanır. 350 milyon TL için söz konusu vergi dilimlerine ve vergi kesinti oranlarına göre vergi miktarı 90 milyon TL olarak hesaplanır.

Vergi matrahı 1500 milyon TL (1.5 milyar TL) olursa (1200.000.001-2400.000.000) vergi dilimine girilir. 1500 milyonun , 1200 milyonu için ödenecek vergi miktarı tablodan VLOOKUP fonksiyonu yardımıyla 375 milyon TL (E8) olarak bulunur. Kalan 300 milyon (1500-1200) için vergi kesintisi(300 milyon *0,40) 120 milyon TL olarak hesaplanır. 1500 milyon TL için vergi kesintisi 495 milyon TL olarak hesaplanır.

Vergi matrahı 6800 milyon TL olursa (4800.000.001-9600.000.000) vergi dilimine girilir. 6800 milyonun , 4800 milyonu için ödenecek vergi miktarı tablodan VLOOKUP fonksiyonu yardımıyla 1935 milyon TL (E10) olarak bulunur.

Kalan 2000 milyon (6800-4800) için vergi kesintisi (2000 milyon *0,50) 1000 milyon TL olarak hesaplanır. Sonuçta sözkonusu çalışma sayfasında C12 hücresine girilen 6800 milyon TL için C13 hücresinde vergi kesintisi 2935 milyon TL olarak hesaplanır. C13 hücresinde, VLOOKUP fonksiyonunun kullanıldığı formül ile vergi kesinti miktarı hesaplanmaktadır. Ödenecek vergi miktarının hesaplanıdığı C13 hücresine,

=VLOOKUP(C12;C5:E10;3)+(C12-VLOOKUP(C12;C5:E10;1))
*VLOOKUP(C12;C5:E10;2)
formülü yazılmalıdır.

VLOOKUP Fonksiyonunun Özellikleri
EXCEL'DE VLOOKUP fonksiyonu, verilen bir değerin, belirlenen alan içindeki ilk sütun içinde aranmasını ve ilk sütun içinde bulunan sözkonusu değerin bulunduğu satır boyunca, sütun sıra numarası verilen sütun üzerindeki değerin bulunmasını sağlamaktadır.

VLOOKUP fonksiyonu, =VLOOKUP(Tanımlanan Değer;Alan;Sütun Sıra Numarası) şeklinde kullanılmalıdır. Burada argümanları ayırıcı karekter ; (noktalı virgül) olarak tanımlanmıştır.

=VLOOKUP(C12;C5:E10;3) fonksiyonu ile C12 hücresinde verilen vergi matrahı değeri, C5:E10 alanındaki ilk sütun olan C sütunu boyunca aranmaktadır.

C5:C10 alanındaki ilk sütun (C sütunu) üzerinde, C12 değerine eşit veya en yakın olan değerin bulunduğu satır ile, sütun sıra numarası 3 olan E sütununun kesiştiği hücrede bulunan değer elde edilmektedir.

=VLOOKUP(C12;C5:E10;3) fonksiyonunu, C12 hücresinde 350 milyon değerinin bulunduğunu düşünerek yorumlayalım. C5:E10 alanında 350 milyona en yakın değer C6 hücresinde 300 milyon değeri olarak görülmektedir. Buna göre C5:E10 alanında 350 milyona en yakın değer 6. Satır da bulunmaktadır. C5:E10 alanında 3 nolu sütun E sütunu olmaktadır. Çalışma sayfasında, C5:E10 alanında 6. satır ile E sütunun kesiştiği hücre, E6 hücresine denk gelmektedir. C12 hücresindeki 350 milyon için =VLOOKUP(C12;C5:E10;3) fonksiyonu ile 75 milyon değeri bulunmaktadır. 75 milyon TL, 350 milyonun 300 milyonu için ödenmesi gereken vergi kesinti miktarıdır.

C12 hücresinde bulunduğu farz edilen 350 milyonun kalan 50 milyonunun vergilendirilmesi için C13 hücresindeki formülün ikinci kısmı devreye girmektedir.

(C12-VLOOKUP(C12;C5:E10;1))*VLOOKUP(C12;C5:E10;2) formülü ile kalan 50 milyonun vergisi hesaplanmaktadır.

VLOOKUP(C12;C5:E10;1) fonksiyonu ile C5:E10 alanında sütun numarası 1 olan C sütunu üzerinde C12'deki 350 milyona en yakın değer olan 300 milyon (C6) bulunmaktadır.

(C12-VLOOKUP(C12;C5:E10;1)) formülü ile C12'deki 350 milyondan 300 milyon çıkarılmaktadır.

Buradan vergilendirilmesi gereken 50 milyon değeri hesaplanmaktadır.

VLOOKUP(C12;C5:E10;2) fonksiyonu ile C5:E10 alanında 2 nolu sütun olan D sütunu ile C5:E10 alanının ilk sütununda C12'deki 350 milyona en yakın değerin bulunduğu 6.Satır (C6) üzerinde kesiştiği hücre olan D6 hücresindeki 0,30 değeri bulunmaktadır.

Vergilendirilmesi gereken kalan 50 milyon için vergi kesintisi (C12-VLOOKUP(C12;C5:E10;1))*VLOOKUP(C12;C5:E10;2) formülü ile 15 milyon olarak hesaplanır.

C12 hücresine girilen 350 milyon için vergi kesintisi C13 hücresinde, =VLOOKUP(C12;C5:E10;3)+(C12-VLOOKUP(C12;C5:E10;1)) *VLOOKUP(C12;C5:E10;2) formülü ile 90 milyon TL olarak hesaplanmaktadır.

Vergi kesintisinin hesaplanmasında VLOOKUP fonksiyonunun nasıl kullanıldığı biraz ayrıntılı anlatmaya çalıştım. Vergi kesintisini hesaplamak için bu fonksiyonun formül içinde düzenlenmesi karmaşık gelebilir. İşinizi kolaylaştırmak için yapmanız gereken, çalışma sayfasının istediğiniz alanına vergi limitlerini ve kesinti oranlarını C5:E10 alanında görüldüğü şekilde düzenlemek, vergi kesintisinin hesaplanacağı hücreye C13'deki formülü biraz dikkatlice yazmak olmalıdır. Sonraki aşamada yazacağınız vergi matrahı üzerinden vergi kesinti miktarını Excel programı sizin için hesaplayacaktır.

VERGİ MATRAHINI HESAPLAMA
YAZININ birinci kısmında, VLOOKUP fonksiyonunu kullanarak vergi matrahına göre ödenecek vergi miktarının nasıl hesaplandığını anlattım. Şimdi tersden hareket ederek vergi kesintisi miktarını belirleyerek vergi matrahının bulunmasını anlatacağım. Vergi kesintisinin istenen bir değere eşitlenip, bu değere göre vergi matrahının bulunması işlemi, Excel'in Hedef Arama (Goal Seek) özelliği sayesinde yapılmaktadır.

Excel'in Goal Seek komutu ile çalışma sayfasındaki herhangi bir hücrenin istenen hedef değere ulaşması durumunda, bu hücreye bağımlı hücrelerdeki değerlerin değişimleri izlenebilmektedir. Goal Seek komutu ile işlemler tersine çevrilerek belirlenen hücrede hedeflenen sonuçların bulunması sağlanmaktadır. Bu soyut açıklamalardan sonra Excel'de vergi uygulaması için kısaca hedeflenen vergi değerinin hesaplatılması Tools menüsünden Goal Seek komutu seçilerek yapılmaktadır.

Tools *Goal Seek komutların seçilmesi ile ekrana Goal Seek isimdeki diyalog penceresi gelmektedir. Hedef değere eşitlenecek olan hücre, vergi kesintisinin formülle hesaplatıldığı C13 hücresidir. Ödenecek vergi miktarının 1 milyar TL'ye eşit olması istenmektedir. Buna göre C13 hücresi için belirlenen hedef değer 1 milyar TL olacaktır. C13 hücresinin hedef değere ulaşması durumda C12 hücresindeki vergi matrahın değeri araştırılmakdır. Goal Seek isimli diyalog penceresinde Set Cell isimli metin kutusuna C13 adresi, To Value metin kutusuna 1000000000 değeri ve By Changing Cell metin kutusuna C12 adresleri girilmeli ve OK fare ile tıklanmalıdır. Bundan sonra ekrana hedef hücrenin (C13) değerinin 1 milyar eşitlenerek bağlı hücredeki (C12) hesaplamaların yapıldığını ifade eden Goal Seek Status isimli diyalog penceresi gelir. Buradan OK fare ile tıklanarak çalışma sayfasına geçiş yapılır.

Hedef hücre C13, Goal Seek komutu yardımıyla 1 milyar değerine eşitlendiğinde bağlı hücre olarak belirtilen C12 hücresi 2.722.222.222 TL değeri bulunur. 1 milyar vergi ödemek için 2.722.222.222 TL vergi matrahı olarak tahakkuk etmelidir.

Burada dikkat edilirse, değiştirilen bir hedef hücre değerine göre , bir bağlı hücre değerindeki değişme incelenmektedir. Birden fazla hedef hücre mevcut olursa dolayısı ile hedef hücreler için belirlenen hedef değerlerin birden fazla bağlı hücre üzerindeki etkileri izlenmek isteniyorsa Excel'in Scenarios komutu kullanılmalıdır.

GELİR VERGİSİ İÇİN SENARYO YÖNETİMİ
EXCEL'DE Tools menüsündeki Scenarios komutu ile birden fazla hücrenin değerinin değiştirilmesi ile bu hücrelere bağlı hücrelerdeki değerlerin değişimi yaratılan senaryolar yardımıyla incelenebilmektedir. Bu kısımda, bir gelir tablosunda, birden fazla kalemin değerinin değiştirilmesinin Ödenecek Vergi Miktarı kaleminde yaratacağı değişikleri izleyebileceğimiz senaryolar üretilecektir.

Gelir tablosu üzerinde söz edilen senaryo çalışmasını yapabilmek için A1:D24 alanında FUL A.Ş.'ye ait özet bir gelir tablosu hazırlanmıştır. Gelir Tablosu isimli çalışma sayfası üzerinde sözkonusu gelir tablosunun kalemlerine ait değerler D sütunu boyunca, D2:D24 alanında bulunmaktadır. Gelir tablosu ait hesaplamalar D2:D24 alanında görüntülen formüller ile yapılmıştır. Bu uygulamada verilen gelir tablosu semboliktir. Burada anlatığım gelir tablosu için senorya uygulaması daha detaylı ve gerçek gelir tabloları için kullanılabilir.

Ekrandaki çalışma sayfasında A1:D24 alanında görünen gelir tablosundaki değerler, 106 değeri ile kısaltılmıştır. Ayrıca buradaki gelir tablosunda görünen DÖNEM KARI kalemi vergi matrahı olarak alınmıştır. Bu nedenle, buradaki uygulamada, bu gelir tablosunun bulunduğu çalışma sayfasının, vergi kesintisinin hesaplandığı çalışma sayfası ile ilişkilendirilmesi gereklidir.

Gelir tablosunda bulunan değerler ile vergi kesintisinin hesaplandığı çalışma sayfasındaki değerlerini birbiri ile uyumlu hale getirmek gerekmektedir. Bu nedenle vergi kesintisinin hesaplandığı çalışma sayfasındaki değerler 106 ile kısaltılmalıdır. Değerlerin 106 ile kısaltıldığı ve Gelir Tablosu çalışma sayfası ile ilişkilendirilmiş vergi kesintisinin hesaplandığı vergi isimli çalışma sayfası, gelir tab isimli çalışma kitabında yeniden düzenlenmiştir. Gelir tab çalışma kitabında, vergi çalışma sayfasındaki C12 hücresindeki vergi matrahı değeri, Gelir Tablosu çalışma sayfasındaki D22 hücresinden buraya aktarılmaktadır. Bu nedenle GelirTablosu'ndaki D22 hücresi ile bağlantı kurulabilmesi için vergi çalışma sayfasındaki C12 hücresine =' Gelir Tablosu'!D22 formülü yazılmalıdır.

Gelir tab çalışma kitabında Gelir Tablosu çalışma sayfasındaki D23 hücresindeki vergi kesintisi değeri, vergi çalışma sayfasında hesaplanıp buraya aktarılmaktadır. Bu nedenle D23 hücresine ='vergi'!C13 formülü yazılmalıdır.

Uzun uzun anlatıldığı şekliyle, gelir tablosunda vergi kesintisini hesaplatabilmek için iki çalışma sayfasının birbiri ile ilişkilendirildiği FUL A.Ş.'ye ait özet bir gelir tablosuna kavuşuldu. Şimdi sıra bu gelir tablosu üzerinde senaryo yönetimine geldi.

Senaryo yönetimi, muhasebede çok çeşitli tablolar için yapılabilmektedir. Muhasebedeki olası mümkün senaryo yönetimi uygulamalarını ve Excel'de senaryo yönetimine ait bir kısım özellikleri bir sonraki yazıma bırakıyorum.

Zamanın kısıtlı olması nedeniyle gelir tablosu uygulamasına ilişkin sadece bir adet senaryo üretilmiştir.

Gelir Tablosu Uygulaması İçin Örnek Senaryo
SENARYO 1 : Gelir tablosundaki, Kısa Vadeli Borçlanma Gideri 0,10 oranında artarsa ve Yurtdışı Satışlar 0,40 oranında azalırsa vergi kesintisinin değeri ne olacaktır.

Senaryo 1'de dikkat edilirse değişen iki değerin, gelir tablosundaki vergi kesintisi değeri üzerindeki etkisi incelenmektedir. Benzer şekilde gelir tablosunda değişen ikiden fazla değerin, birden fazla değer üzerindeki, etkisi üretilecek senaryolar üzerinde incelenebilmektedir. Gelir tablosuna, Senaryo 1'i uygulamak için Excel'deTools menüsünden, Scenario komutu seçilmelidir. Ekrana gelen Scenario Manager isimli diyalog penceresinde Add fare ile tıklanmalıdır.

Bunun ardından ekrana gelen ' Add Scenario' isimli diyalog penceresinde Scenario Name metin kutusuna Senaryo1 yazılmalıdır. ' By Changing Cell' metin kutusuna, gelir tablosunda değiştirilmek istenen değerlerin, Yurtdışı Satışlar ve Kısa Vadeli Borçlanma Giderinin hücre adresleri D4,D20 olarak yazılmalı ve OK fare ile tıklanmalıdır.

Senaryo1 gereğince değiştirilmek istenen D4 ve D20 hücrelerinin olması istenen değerleri ya sabit olarak yada formül yardımıyla Scenario Values isimli diyalog penceresindeki uygun yerlere yazılmalı ve OK fare ile tıklanmalıdır.

Scenario Values diyalog penceresinde D4 ve D20 hücreleri için yazılan formüller ile bulunan sonuçlara göre gelir tablosunda bulunan değerler değiştirilecektir.

Bundan sonra ekranda Senaryo Manager isimli diyalog penceresi görünür. Senaryo1'de değerlere ilişkin yapılan tanımlamaların, gelir tablosu üzerindeki etkisini görebilmek için Scenarios kısmındaki Senaryo1 yazısı fare ile tıklanmalı , ardından Show düğmesi fare ile tıklanmalıdır. Bu aşamalardan sonra Senaryo1 gelir tablosuna uygulanacaktır. Bunun ardından Close düğmesine tıklanarak Scenario Manager kapatılmalıdır.

Senaryo1 gelir tablosuna uygulanmadan önce gelir tablosunun ilk hali saklanmalıdır. Senaryo Manager penceresindeki Show tıklandıktan sonra Senaryo1 gereğince gelir tablosundaki Yurtdışı Satışlar 0,40 azalacak ve Kısa Vadeli Borçlanma Gideri 0,10 artacaktır. Bu değerlere bağlı olarak gelir tablosundaki değerler değişecektir. Buna göre Yurtdışı Satışlar 120*106 TL'den 72*106 TL'ye gerileyecek ve Kısa Vadeli Borçlanma Gideri 300*106 TL'den , 330*106 TL'ye artacaktır. Aynı gelir tablosundaki vergi kesintisini ifade eden VERGİ VE YÜKÜMLÜLÜKLER kaleminin değeri 30,753*106 TL olacaktır. Senaryo1, gelir tablosundaki Yurtdışı Satışlar (D4) ve Kısa Vadeli Borçlanma Giderlerindeki (D20) değişimlerin , Vergi kesintisi (D23) üzerindeki etkisini izleyebilmek için üretilmiştir. O halde gelir tablosunun genelindeki değişimleri görmek yerine sadece D4 ve D20 deki değişimlerin D23 üzerindeki etkisini görebilmek için özet bir senaryo tablosu düzenlemek mümkün olmaktadır. Bu nedenle Scenario Summary isimli diyalog penceresinde Scenario Summary seçeneğinin onay yuvarlağı fare ile tıklanmalı ve Result Cells metin kutusuna D23 yazılmalıdır.

Scenario Summary isimli diyalog penceresinde gerekli düzenlemeler yapıldıktan sonra gelir tablosunun bulunduğu gelir tab isimli çalışma kitabında Scenario Summary isimli bir çalışma sayfası oluşturulur. Bu çalışma sayfasında Scenario Summary tablosu görüntülenir. Scenario Summary tablosunda Senaryo1'e konu olan gelir tablosu kalemlerinin değerleri verilmektedir.

Kısaca bu özet tabloda değişen D4 ve D20 hücrelerinin değerleri ile bu değişimden etkilenen D23 hücresinin değeri görüntülenir. Yine belirtmeliyim, senaryo çalışmalarında birden fazla senaryo üretmek ve görüntülemek mümkündür.

Not: Türkçe EXCEL kullananlar VLOOKUP komutu yerine DÜŞEYARA komutunu kullanacaklardır.

Excel'de Hisse Senedi Analizi

Excel’de Hisse Senedi Portföyü Yönetimi