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ı
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.
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 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.
=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 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.
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 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 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.
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.
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 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.
Zamanın kısıtlı olması nedeniyle gelir tablosu uygulamasına ilişkin sadece bir adet senaryo üretilmiştir.
Senaryo 1'de dikkat edilirse değişen iki değerin, gelir tablosundaki vergi kesintisi değeri üzerindeki etkisi incelenmektedir.
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. 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 AnaliziExcel’de Hisse Senedi Portföyü Yönetimi |