Excel'de Finansal Modelleme (Adım Adım Ücretsiz Kılavuz + Şablon)

İçindekiler

Excel'de Finansal modelleme nedir?

Excel'de finansal modelleme, bir değişkendeki bir değişikliğin nihai getiriyi nasıl etkileyebileceğini analiz etmek için bir işlemi, işlemi, birleşme, satın alma, finansal bilgileri temsil eden bir finansal model oluşturma sürecidir. söz konusu mali işlemler.

Excel'de Finansal modelleme nedir?

Excel'de Finansal Modelleme web'in her yerinde. Finansal Modellemeyi öğrenmek hakkında çok şey yazıldı; ancak, eğitimin finansal modelleme parçalarının çoğu aynıdır. Bu, alışılmış anlamsız sözlerin ötesine geçer ve Yatırım Bankacıları ve Araştırma Analistleri tarafından kullanılan pratik Finansal Modellemeyi araştırır.

Bu Ücretsiz Finansal Modelleme Excel Kılavuzunda, bir Colgate Palmolive örneğini ele alacağım ve sıfırdan tam entegre bir finansal model hazırlayacağım.

Bu kılavuz 6000 kelimeden fazla ve tamamlamam üç haftamı aldı. Bu sayfayı ileride başvurmak üzere kaydedin ve paylaşmayı unutmayın :-)

EN ÖNEMLİ - Talimatları izlemek için Colgate Finansal modelleme Excel şablonunu indirin

Colgate Finansal Model Şablonunu İndirin

Excel'de Adım Adım Finansal Modellemeyi Öğrenin

Excel Eğitiminde Finansal Modelleme - Önce Beni Oku

Adım 1 - Colgate Finansal Model Şablonunu İndirin. Bu şablonu eğitim için kullanacaksınız

Colgate'in Finansal Modelini İndirin

E-posta Adresini Girin Yukarıdaki adıma devam ederek Kullanım Koşullarımızı ve Gizlilik Politikamızı kabul etmiş olursunuz.

Adım 2 - Lütfen iki şablon alacağınızı unutmayın - 1) Çözülmemiş Colgate Palmolive Finansal Modeli 2) Çözülmüş Colgate Palmolive Finansal Modeli

Adım 3- Çözülmemiş Colgate Palmolive Finansal Model Şablonu üzerinde çalışacaksınız . Tam entegre bir finansal model hazırlamak için talimatları adım adım izleyin.

Adım 4 - Mutlu Öğrenmeler!

İçindekiler

Bu Finansal Modellemeyi yapmanız için içindekiler tablosunda gezinmeyi kolaylaştırdım

Finansal model nasıl oluşturulur?
# 1 - Colgate'in Finansal Modeli - Tarihsel
# 2 - Colgate Palmolive'in Oran Analizi
# 3 - Gelir Tablosunun Öngörülmesi
# 4- İşletme Sermayesi Programı
# 5 - Amortisman Takvimi
# 6 - Amortisman Planı
# 7 - Diğer Uzun Vadeli Program
# 8 - Gelir Tablosunu Doldurmak
# 9 - Hissedarların Öz Sermaye Planı
# 10 - Üstün Programı Paylaşıyor
# 11 - Nakit Akışı Tablolarını Tamamlama
# 12- Borç ve Faiz Planı Önerilir
Finansal Modelleme Kursu
Ücretsiz Finansal Modeller

Finansal Modellemede yeniyseniz, Finansal Modelleme Nedir? Konulu bu kılavuza bir göz atın.

Excel'de bir finansal model nasıl oluşturulur?

Finans modelinin nasıl sıfırdan inşa edildiğine bakalım. Bu ayrıntılı finansal modelleme kılavuzu, bir finansal model oluşturmak için size adım adım bir kılavuz sağlayacaktır. Bu finansal modelleme kılavuzunda benimsenen temel yaklaşım Modülerdir. Modüler sistem temelde, farklı modüller / programlar kullanarak Gelir Tablosu, Bilanço ve Nakit Akışları gibi temel tablolar oluşturmak anlamına gelir. Temel odak noktası, her bir ifadeyi adım adım hazırlamak ve tüm destekleyici programları tamamlandığında ana ifadelere bağlamaktır. Bunun şu anda net olmayabileceğini anlayabiliyorum; ancak ilerledikçe bunun çok kolay olduğunu anlayacaksınız. Aşağıda çeşitli Finansal Modelleme Programlarını / Modüllerini görebilirsiniz -

Lütfen aşağıdakilere dikkat edin -

Temel tablolar Gelir Tablosu, Bilanço ve Nakit Akışlarıdır.
Farklı programlar amortisman çizelgesi, işletme sermayesi çizelgesi, maddi olmayanlar çizelgesi, hissedarların öz sermaye çizelgesi, diğer uzun vadeli kalemler çizelgesi, borç çizelgesi vb.
Farklı programlar, tamamlandıktan sonra temel ifadelere bağlanır.
Bu finansal modelleme kılavuzunda, Colgate Palmolive'in adım adım entegre bir ekonomik modelini sıfırdan oluşturacağız.

# 1 - Excel'de Finansal Modelleme - Geçmişleri Projelendirin

Finansal Modelleme Kılavuzunun ilk adımı, Tarihsellerin hazırlanmasıdır.

Adım 1A - Colgate'in 10K Raporlarını İndirin

“Finansal modeller excel'de hazırlanıyor ve ilk adımlar sektörün geçmiş yıllarda nasıl gittiğini bilmekle başlıyor. Geçmişi anlamak bize şirketin geleceği ile ilgili değerli bilgiler sağlayabilir. Bu nedenle ilk adım, şirketin tüm finansallarını indirmek ve bunu bir excel sayfasına doldurmaktır. Colgate Palmolive için, Colgate Palmolive'in yıllık raporlarını Yatırımcı İlişkileri Bölümünden indirebilirsiniz. "Yıllık rapor" u tıkladığınızda, aşağıdaki pencereyi göreceksiniz -

Adım 1B - Geçmiş Finansal Tablolar Çalışma Sayfasını Oluşturun

2013'ün 10K'sını indirirseniz, yalnızca iki yıllık mali tablo verilerinin mevcut olduğunu göreceksiniz. Ancak, Excel'de Finansal Modellemenin amacı için önerilen veri setinin son 5 yıllık finansal tablolara sahip olmasıdır. Lütfen yıllık raporun son 3 yılını indirin ve geçmişini doldurun.
Excel'i biçimlendirmek ve istenen biçime getirmek çok zaman ve enerji gerektirebileceğinden, çoğu zaman bu görevler çok sıkıcı ve sıkıcı görünür.
Bununla birlikte, bunun her şirket için yalnızca bir kez yapmanız gereken iş olduğunu ve aynı zamanda tarihsel bilgileri doldurmanın bir analistin eğilimleri ve mali tabloları anlamasına yardımcı olduğunu unutmamak gerekir.
Bu yüzden lütfen bunu atlamayın, verileri indirin ve verileri doldurun (bunun eşeğin işi olduğunu düşünseniz bile ;-))

Bu adımı atlamak isterseniz, Colgate Palmolive Tarihsel Modelini buradan doğrudan indirebilirsiniz .

Geçmiş nüfuslu Colgate Gelir Tablosu

Colgate Bilanço Geçmiş Verileri

# 2 - Oran Analizi

Excel'de Finansal Modellemenin ikinci adımı Oran Analizi yapmaktır.

Excel'de Finansal Modellemeyi öğrenmenin anahtarı, temel analizleri gerçekleştirebilmektir. Element analizi veya Oran Analizi sizin için yeni bir şeyse, internette biraz okumanızı tavsiye ederim. Gelecek gönderilerimden birinde derinlemesine bir oran analizi yapmayı planlıyorum, ancak burada Colgate Palmolive oranlarının hızlı bir görüntüsü var.

ÖNEMLİ - Lütfen Colgate'in Oran Analizini ayrı bir gönderide güncellediğimi unutmayın. Lütfen bu kapsamlı oran analizine bir göz atın.

Adım 2A - Colgate'in Dikey Analizi

Gelir tablosunda, dikey analiz, firmanın maliyet ve karlılık açısından yıldan yıla göreceli performansını ölçmek için evrensel bir araçtır. Her zaman herhangi bir finansal analizin parçası olarak dahil edilmelidir. Burada yüzdeler,% 100 olarak kabul edilen net satışlara göre hesaplanır. Gelir tablosundaki bu dikey analiz çabası, satışlarla ilgili farklı marjları sağladığı için genellikle marj analizi olarak adlandırılır.

Dikey Analiz Sonuçları

Kar Marjı , 2007'deki% 56,2'den 2013'te% 58,6'ya 240 baz puan artmıştır. Bunun başlıca nedeni, Satış Maliyetinin düşmesidir.
Faaliyet Karı veya FVÖK , 2007'de% 19,7'den 2012'de% 22,4'e (70 baz puanlık bir artış) yükselen marjlar da göstermiştir. Bunun nedeni Satış genel ve idari maliyetleriydi. Ancak, artan "Diğer giderler" nedeniyle FVÖK marjlarının 2013 yılında% 20,4'e düştüğünü unutmayın. Ayrıca, FAVÖK ile FAVÖK arasındaki farkı kontrol edin
Net Kar Marjı , 2007'de% 12,6'dan 2012'de% 14,5'e yükseldi. Ancak, 2013 yılında Kar Marjı, özellikle artan “diğer giderler” nedeniyle% 12,9'a geriledi.
Hisse başına kazanç , 2007 mali yılından 2012 mali yılına kadar istikrarlı bir şekilde artmıştır. Ancak 2013 mali yılı EPS'sinde hafif bir düşüş oldu.
Ayrıca, Amortisman ve Amortismanların Gelir Tablosunda ayrıca verildiğini unutmayın. Satış Maliyetine dahildir.

Adım 2B - Colgate'in Yatay Analizi

Yatay analiz, baz yıla göre yüzde artış excel veya düşüşleri hesaplayarak zaman içindeki eğilimleri değerlendirmek için kullanılan bir tekniktir. Farklı satın alma güçlerine sahip para birimi kullanılarak farklı tarihlerde hesaplanan hesaplar arasında analitik bir bağlantı sağlar. Gerçekte, bu analiz raporları indeksler ve bunların zaman içindeki gelişimini karşılaştırır. Dikey analiz metodolojisinde olduğu gibi, araştırılması ve diğer finansal analiz teknikleriyle tamamlanması gereken sorunlar ortaya çıkacaktır. Odak noktası, ek yöntemler kullanılarak teşhis edilebilecek sorunların belirtilerini aramaktır.

Colgate'in Yatay analizine bakalım.

Yatay Analiz Sonuçları

Net Satışların 2013 yılında% 2,0 arttığını görüyoruz.
Ayrıca, Satış Maliyetindeki eğilime dikkat edin. Satışları ile aynı oranda büyümediklerini görüyoruz.
Excel'de finansal modelleme yaparken bu gözlemler son derece kullanışlıdır.

Adım 2C - Colgate'in Likidite Oranları

Likidite oranları, bir işletmenin daha likit varlıklarının (nakde en kolay dönüştürülebilenler) cari borçlarla ilişkisini ölçer. En yaygın likidite oranları cari oran, Asit testi (veya hızlı varlık) oranı Nakit Oranlarıdır.
Alacak Hesabı cirosu, Stok devir hızı ve Borç Devir Hızı gibi Ciro Oranları

Likidite Oranlarının Önemli Noktaları

Colgate'in Mevcut Oranı tüm yıllar boyunca 1,0'dan daha önemlidir. Bu, mevcut varlıkların cari borçlardan daha önemli olduğu ve belki Colgate'in yeterli likiditeye sahip olduğu anlamına gelir.
Colgate'in hızlı Oranı 0,6-0,7 aralığındadır; bu, Colgates Cash ve Pazarlanabilir menkul kıymetlerin cari borçların% 70'e kadarını ödeyebileceği anlamına gelir. Bu Colgate için iyi bir duruma benziyor.
Nakit Tahsilat Döngüsü 2009'da 43 günden 2013'te 39 güne düşmüştür. Bunun başlıca sebebi alacak tahsilat süresinin kısalmasıdır.

Ayrıca, Nakit Dönüşüm Döngüsü hakkındaki bu ayrıntılı makaleye bir göz atın.

Adım 2D - Colgate'in İşletme Karlılık Oranları

Karlılık, bir şirketin satışlara, varlıklara ve öz sermayeye göre kazanç elde etme kabiliyetine oranlar

Önemli Noktalar - Colgate'in Karlılık Oranları

Yukarıdaki tablodan da görebileceğimiz gibi, Colgate% 100'e yakın bir ROE'ye sahiptir, bu da Hisse senedi sahiplerine mükemmel getiri anlamına gelir.

Adım 2E - Colgate'in Risk Analizi

Risk Analizi ile şirketlerin kısa ve uzun vadeli yükümlülüklerini (borçlarını) ödeyip ödeyemeyeceğini ölçmeye çalışıyoruz. Varlıkların yeterliliğine veya varlıklardan üretime odaklanan kaldıraç oranlarını hesaplıyoruz. Bakılan oranlar

Borç-Özkaynak Oranı
Borç oranı
Faiz Karşılama Oranı
Borç-Özkaynak Oranı istikrarlı bir şekilde daha yüksek bir 2.23x seviyesine yükseldi. Bu, artan Finansal Kaldıraç ve piyasadaki riskleri ifade eder
Bununla birlikte, Faiz Kapsamı Oranı çok yüksektir ve Faiz Ödemesi Temerrüdü riskinin daha az olduğunu gösterir.

# 3 - Excel'de Finansal Modelleme - Gelir Tablosunu Tasarlayın

Finansal Modellemede üçüncü adım, Satış veya Gelir kalemlerini modellemeye başlayacağımız Gelir Tablosunu tahmin etmektir.

Adım 3A - Gelir Projeksiyonları

Çoğu şirket için gelirler, ekonomik performansın temel bir itici gücüdür. Gelir akışlarının türünü ve miktarını doğru bir şekilde yansıtan iyi tasarlanmış ve mantıklı bir gelir modeli son derece önemlidir. Bir gelir planı oluşturmanın işletmeler kadar birçok yolu vardır. Bazı yaygın türler şunları içerir:

Satış Büyümesi: Her dönemdeki satış artışı varsayımı, bir önceki döneme göre değişimi tanımlar. Bu basit ve yaygın olarak kullanılan bir yöntemdir ancak büyümenin bileşenleri veya dinamikleri hakkında hiçbir fikir vermez.
Enflasyon ve Hacim / Karışım etkileri: Basit bir büyüme varsayımı yerine, bir fiyat enflasyon faktörü ve bir hacim faktörü kullanılır. Bu kullanışlı yaklaşım, çok ürünlü şirketlerde sabit ve değişken maliyetlerin modellenmesine izin verir ve fiyat ve hacim hareketlerini hesaba katar.
Birim Hacim, Hacim Değişimi, Ortalama Fiyat ve Fiyat Değişimi: Bu yöntem, basit bir ürün karması olan işletmeler için uygundur; birkaç anahtar değişkenin etkisinin analizine izin verir.
Dolar Piyasa Büyüklüğü ve Büyüme: Pazar Payı ve Paydaki Değişim - Piyasa dinamikleri hakkında bilginin mevcut olduğu ve bu varsayımların bir karar için temel olmasının muhtemel olduğu durumlar için kullanışlıdır. Örneğin, Telekom sektörü.
Birim Pazar Büyüklüğü ve Büyüme: Bu, önceki durumdan daha ayrıntılıdır ve pazardaki fiyatlandırma çok önemli bir değişken olduğunda yararlıdır. (Örneğin, fiyat indirimi stratejisine sahip bir şirket veya türünün en iyisi premium fiyatlı niş oyuncu için), örneğin Lüks otomobil pazarı
Hacim Kapasitesi, Kapasite Kullanım Oranı ve Ortalama Fiyat: Bu varsayımlar, karar için üretim kapasitesinin gerekli olduğu işletmeler için önemli olabilir. (Örneğin ek kapasite satın alımında veya genişlemenin yeni yatırımlar gerektirip gerektirmeyeceğini belirlemek için.)
Ürün Bulunabilirliği ve Fiyatlandırma
Gelir, sermaye, pazarlama veya Ar-Ge'ye yapılan yatırımdan kaynaklanıyordu
Gelir bazında kurulu baz (parça, tek kullanımlık malzeme, servis ve eklenti satışlarının devamı vb.). Örnekler arasında klasik tıraş bıçağı işletmeleri ve hizmet, yazılım ve yükseltmelerin satışının gerekli olduğu bilgisayarlar gibi işletmeler yer alır. Kurulu tabanın modellenmesi anahtardır (zemine yeni eklemeler, zeminde yıpranma, müşteri başına sürekli gelirler, vb.)
Çalışan temelli: Örneğin, profesyonel hizmet firmalarının veya komisyoncular gibi satış bazlı firmaların gelirleri. Modelleme net kadroya, çalışan başına gelire (genellikle faturalandırılabilir saatlere göre) odaklanmalıdır. Daha detaylı modeller kıdem ve fiyatlandırmayı etkileyen diğer faktörleri içerecektir.
Mağaza, tesis veya Kare görüntü tabanlı: Perakende şirketleri genellikle mağazalar (eski mağazalar ve her yıl yeni mağazalar) ve mağaza başına gelire dayalı olarak modellenir.
Doluluk faktörüne dayalı: Bu yaklaşım havayolları, oteller, sinema salonları ve düşük marjinal maliyetleri olan diğer işletmeler için geçerlidir.

Colgate Gelirlerinin Planlanması

Şimdi Colgate 10K 2013 raporuna bakalım. Gelir tablosunda, Colgate'in bölümlere göre bilgi sağlamadığını not ediyoruz; ancak ek bilgi olarak Colgate bölümlerin bazı detaylarını Sayfa 87 Kaynak - Colgate 2013 - 10K, Sayfa 86'da sağlamıştır.

Özellikler hakkında daha fazla bilgiye sahip olmadığımız için, Colgate'in gelecekteki satışlarını bu mevcut verilere dayanarak tahmin edeceğiz. Tahminleri türetmek için segmentler arasında satış büyüme yaklaşımını kullanacağız. Lütfen aşağıdaki resme bakın. Her element için yıldan yıla büyüme oranını hesapladık. Artık geçmiş trendlere dayalı bir satış büyüme yüzdesi varsayabilir ve her bir bölümün altındaki gelirleri tahmin edebiliriz. Toplam Net satışlar Ağız, Kişisel ve Evde Bakım ve Evcil Hayvan Besleme Segmentinin toplamıdır .

Adım 3B - Maliyet Tahminleri

Gelir Yüzdesi: Basit ancak herhangi bir kaldıraç (ölçek ekonomisi veya sabit maliyet yükü) hakkında fikir vermez
Gelirlerin yüzdesi olarak amortisman dışındaki maliyetler ve farklı bir programdan amortisman: Bu yaklaşım, çoğu durumda gerçekten kabul edilebilir minimumdur ve işletme kaldıracının yalnızca kısmi analizine izin verir.
Gelire veya hacme dayalı değişken maliyetler , geçmiş eğilimlere dayalı sabit maliyetler ve ayrı bir çizelgeden amortisman: Bu yaklaşım, birden çok gelir senaryosuna dayalı kârlılığın duyarlılık analizi için gereken minimum değerdir

Colgate için Maliyet Projeksiyonları

Maliyetin öngörülmesi için, daha önce yapılan dikey analiz yardımcı olacaktır. Dikey analize yeniden bakalım -

Satışları önceden tahmin ettiğimiz için, diğer tüm maliyetler bu Satışların bazı marjlarıdır.
Yaklaşım, kılavuzları tarihi maliyet ve gider marjlarından almak ve ardından gelecekteki marjı tahmin etmektir.
Örneğin, Satışların Maliyeti son beş yıldır% 41-% 42 aralığında olmuştur. Bu temelde marjları tahmin etmeye bakabiliriz.
Aynı şekilde Satış, Genel ve İdari Giderler de geçmişte% 34-% 36 aralığında olmuştur. Bu temelde, gelecekteki SG&A gider marjını varsayabiliriz. Aynı şekilde, başka bir takım masraflar için devam edebiliriz.

Yukarıdaki marjları kullanarak, gerçek değerleri geriye dönük hesaplamalarla bulabiliriz.

Vergi karşılığını hesaplamak için, Efektif Vergi Oranı varsayımını kullanıyoruz.

Ayrıca, Gelir Tablosunu daha sonraki bir aşamada yeniden düzenleyeceğimiz için "Faiz Gider (Gelir)" satırını tamamlamadığımızı unutmayın.
Faiz Gideri ve Faiz Geliri.
Satışların Maliyetine zaten dahil edilmiş olan Amortisman ve Amortismanı da hesaplamadık.
Bu, Gelir Tablosunu tamamlar (en azından şimdilik!)

# 4- Finansal Modelleme - Çalışma Sermayesi Programı

Gelir beyanını tamamladığımıza göre, Finansal Modellemenin dördüncü adımı Çalışma Sermayesi Çizelgesine bakmaktır.

Çalışma Sermayesi Çizelgesi için izlenecek adımlar aşağıdadır

Adım 4A - Net Satışları ve Satış Maliyetlerini Bağlayın

Adım 4B - İşletme sermayesi ile ilgili Bilanço Verilerine referans verin

Bilançodan geçmiş verileri referans alın
Net işletme sermayesini hesaplayın
İşletme sermayesinde bir artış / düşüşe ulaşmak
Kısa vadeli borçları ve nakit ve nakit benzerlerini işletme sermayesine dahil etmediğimizi unutmayın. Borç ve nakit ve nakit benzerlerini ayrı ayrı ele alacağız.

Adım 4C - Devir Oranlarını Hesaplayın

Geçmiş oranları ve yüzdeleri hesaplayın
Bitiş veya ortalama bakiyeyi kullanın
Tutarlılık korunduğu sürece ikisi de kabul edilebilir

Adım 4D - Gelecekteki işletme sermayesi kalemleri için varsayımları doldurun

Belirgin bir sürücü olmayan bazı öğeler genellikle sabit miktarlarda varsayılır
Varsayımların makul ve işe uygun olduğundan emin olun

Adım 4E - Gelecekteki işletme sermayesi dengelerini planlayın

Adım 4F - İşletme Sermayesindeki değişiklikleri hesaplayın

Ayrı ayrı satır öğelerine göre Nakit Akışlarına ulaşma
İşaretlerin doğru olduğundan emin olun!

Adım 4G - Öngörülen İşletme Sermayesini Bilançoya Bağlayın

Adım 4H - İşletme Sermayesini Nakit Akışı Tablosuna Bağlayın

# 5 - Excel'de Finansal Modelleme - Amortisman Çizelgesi

İşletme sermayesi programının tamamlanmasıyla, bu Finansal Modellemedeki bir sonraki adım, Colgate Yatırım Harcaması projesidir ve Amortisman ve Varlıklar rakamlarını projelendirmektir. Colgate 2013 - 10K, Sayfa 49

Amortisman ve Amortisman ayrı bir satır öğesi olarak sağlanmaz; ancak satışların maliyetine dahildir
Bu gibi durumlarda, Amortisman ve İtfa Giderini bulacağınız Nakit akışı tablolarına bakınız. Ayrıca, aşağıdaki rakamların 1) Amortisman 2) amortisman olduğuna dikkat edin. Öyleyse amortisman numarası nedir?
KKD için Kapanış Bakiyesi = Başlangıç bakiyesi + Yatırım harcamaları - Amortisman - Varlık Satışları için Ayarlama (BASE denklemi)

Adım 5A - Amortisman Planındaki Net Satış rakamlarını bağlayın

Satır öğelerini ayarlayın
Referans Net Satışlar
Geçmiş sermaye harcamalarını girin
Net Satışların Yüzdesi Olarak Capex'e Ulaşın

Adım 5B - Sermaye Harcaması Kalemlerini Tahmin Edin

Sermaye harcamasını tahmin etmek için çeşitli yaklaşımlar vardır. Yaygın bir uygulama, şirketin gelecekteki sermaye harcamaları hakkındaki görüşünü anlamak için Basın Bültenlerine, Yönetim Öngörülerine, MD & A'ya bakmaktır.
Şirket gelecekteki sermaye harcamaları konusunda rehberlik sağladıysa, bu rakamları doğrudan alabiliriz.
Bununla birlikte, Capex rakamları doğrudan mevcut değilse, o zaman Capex'i Satışların yüzdesi olarak kullanarak kabaca hesaplayabiliriz (aşağıda yapıldığı gibi)
Kararınızı endüstri bilgisine ve diğer makul faktörlere göre kullanın.

Adım 5C - Geçmiş Bilgileri Referans Alın

KKD için Kapanış Bakiyesi kullanacağız = Başlangıç bakiyesi + Yatırım harcamaları - Amortisman - Varlık Satışları için Ayarlama (TEMEL denklemi)
Yeniden düzenlemeler, varlık satışları vb. Nedeniyle geçmişteki KK & E'yi uzlaştırmak karmaşıktır.
Bu nedenle, bazı karışıklıklara yol açabileceğinden, geçmişteki KKD'lerin uzlaştırılmaması önerilir.

Colgate'in Amortisman Politikası

Colgate'in Varlıkların ayrıntılı bir dağılımını açıkça sağlamadığını not ediyoruz. Bunun yerine tüm varlıkları Arazi, Bina, Makine ve diğer ekipmanlara ayırdılar.
Ayrıca, makine ve teçhizatın faydalı ömürleri de menzil dahilinde sağlanmaktadır. Bu durumda, varlıklar için kalan ortalama faydalı ömre ulaşmak için bazı tahmin çalışmaları yapmamız gerekecektir.
Ayrıca, faydalı ömür için rehberlik "Diğer Ekipmanlar" için sağlanmamaktadır. Diğer Ekipmanların kullanım ömrünü tahmin etmemiz gerekecek

Colgate 2013 - 10K, Sayfa 55

Aşağıda 2012 ve 2013 Emlak, Tesis ve Ekipman Detaylarının dağılımı verilmiştir

Colgate 2013 - 10K, Sayfa 91

Adım 5D - Gayrimenkulün ve Ekipmanın (KKD) dağılımını tahmin edin

Öncelikle, Mevcut KKD'nin (2013) Varlık ağırlıklarını bulun
2013 KKD'nin bu varlık ağırlıklarının ileriye dönük olarak devam edeceğini varsayacağız.
Tahmini Sermaye Harcamalarının dağılımını hesaplamak için bu varlık ağırlıklarını kullanırız

Adım 5E - Varlıkların Amortismanının Tahmin Edilmesi

Arazi amortismana tabi bir varlık olmadığı için Arazinin amortismanını hesaplamadığımızı lütfen unutmayın.
Bina iyileştirmelerinden amortismanı tahmin etmek için önce aşağıdaki yapıyı kullanıyoruz.
Buradaki amortisman iki bölüme ayrılmıştır: 1) Bilançoda zaten listelenen Bina İyileştirmeleri Varlığından amortisman, 2) Gelecekteki Bina iyileştirmelerinden amortisman.
Duran varlıkta listelenen bina iyileştirmelerinden kaynaklanan amortismanı hesaplamak için, basit Düz Amortisman Yöntemi'ni kullanırız.
Gelecekteki amortismanı hesaplamak için, önce Excel'deki TRANSPOSE Fonksiyonunu kullanarak Capex'i aktarıyoruz.
Her yılın varlık katkılarından amortismanı hesaplarız.
Ayrıca, varlık dağıtımı için yıl ortası sözleşmesini varsaydığımız için, ilk yıl amortismanı ikiye bölünür.

Bina İyileştirmenin Toplam Amortismanı = Bina İyileştirmelerinden kaynaklanan amortisman Halihazırda bilançoda listelenen varlık + gelecekteki amortisman Bina iyileştirmeleri Amortismanı tahmin etmek için yukarıdaki süreç, 1) İmalat Ekipmanı ve Makinelerin ve 2) diğer Ekipmanların amortismanını hesaplamak için kullanılır. aşağıda gösterilen.

Diğer Ekipman türleri

Colgate'in Toplam Amortismanı = Amortisman (Bina İyileştirmeleri) + Amortisman (Makine ve Ekipman) + Amortisman (ek ekipman) Gerçek amortisman rakamlarını bulduktan sonra, bunu aşağıda gösterildiği gibi BASE denklemine koyabiliriz

Bununla, her yıl için Son Net PP&E rakamlarını alıyoruz

Adım 5F - Net PP & E'yi Bilançoya Bağlayın

# 6 - Amortisman Planı

Excel'deki bu Finansal Modellemenin altıncı adımı, Amortismanı tahmin etmektir. Burada ele almamız gereken iki geniş kategori var - 1) Şerefiye ve 2) Diğer Maddi Olmayan Varlıklar.

Adım 6A - Şerefiyenin Tahmin Edilmesi

Colgate 2013 - 10K, Sayfa 61

Bir şirket başka bir şirketi satın aldığında şerefiye bilançoya girer. Şerefiye'yi gelecek yıllar için tasarlamak genellikle karmaşıktır.
Ancak, Şerefiye, her yıl şirketin kendisi tarafından gerçekleştirilen değer düşüklüğü testlerine tabidir. Analistler, bu tür testleri yürütecek ve değer düşüklüğü tahminlerini hazırlayacak durumda değildir.
Çoğu analist iyi niyet öngörmez; onlar sadece bunu sabit tutuyorlar ki bizim durumumuzda da yapacağız.

Adım 6B - Diğer Maddi Olmayan Varlıkların Tahmin Edilmesi

Colgate'in 10K Raporunda belirtildiği gibi, sonlu ömürlülüğün büyük bir kısmı Sanex devralımı ile ilgilidir.
"Maddi Olmayan Varlıklara Eklemeler" de proje için karmaşıktır
Colgate'in 10K raporu bize önümüzdeki beş yıllık amortisman giderinin ayrıntılarını sağlar.
Bu tahminleri Mali Model Colgate 2013 - 10K, Sayfa 61'de kullanacağız.

Adım 6C - Sona eren net maddi olmayan varlıklar "Diğer Maddi Olmayan Varlıklar" ile bağlantılıdır.

Adım 6D - Amortisman ve Amortismanı Nakit Akış Tablolarına Bağlayın

Adım 6E - Sermaye Harcaması ve Maddi Olmayan Tutarları Nakit Akışı Tablolarına Bağlayın

# 7 - Diğer Uzun Vadeli Program

Bu Finansal Modellemedeki bir sonraki adım, Diğer Uzun Vadeli Programı hazırlamaktır. Bu, öngörü için belirli itici güçleri olmayan "artıklara" hazırlandığımız zamandır. Colgate durumunda, diğer Uzun Vadeli Kalemler (kalanlar) Ertelenmiş Gelir Vergileri (borç ve varlıklar), Diğer yatırımlar ve diğer borçlardır.

Adım 7A - Bilançodaki geçmiş verilere referans verin

Ayrıca, bu öğelerdeki değişiklikleri hesaplayın.

Adım 7B - Uzun Vadeli Varlık ve Yükümlülükleri Tahmin Edin

Görünür bir sürücü olmaması durumunda Uzun Vadeli öğeleri öngörülen yıllar boyunca sabit tutun
Öngörülen uzun vadeli kalemleri aşağıda gösterildiği gibi Bilançoya bağlayın

Adım 7C - Diğer Uzun Vadeli Kalemleri Bilançoya Gönderin

Adım 7D - Uzun vadeli kalemleri Nakit Akış Tablosuna bağlayın

Uzun vadeli varlıkları ve borçları sabit tutarsak, nakit akış tablosuna akan değişikliğin sıfır olacağını lütfen unutmayın.

# 8 - Excel'de Finansal Modelleme - Gelir Tablosunu Tamamlama

Bu Excel tabanlı Finansal Modellemede daha fazla ilerlemeden önce, geri dönüp Gelir Tablosuna yeniden bakacağız.
Tarihsel temel ağırlıklı ortalama hisse senetlerini ve seyreltilmiş ağırlıklı ortalama hisse sayısını doldurun
Bu rakamlar Colgate'in 10K raporunda mevcuttur

Adım 8A - Temel ve seyreltilmiş paylara referans verin

Bu aşamada, gelecekteki birincil ve sulandırılmış hisse senedi sayısının 2013'teki ile aynı kalacağını varsayalım.

Adım 8B - Hisse başına Temel ve Seyreltilmiş kazancı hesaplayın.

Bununla, bir sonraki programımıza, yani Hissedar Özsermaye Planına geçmeye hazırız.

# 9 - Finansal Modelleme - Hissedarların Özkaynak Planı

Excel'de Finansal Modelleme Eğitiminin bir sonraki adımı, Hissedarların Özkaynak Planına bakmaktır. Bu programın birincil amacı, Hissedar Özsermayesi, Temettüler, Hisse geri alımı, Opsiyon Gelirleri vb. Gibi öz sermaye ile ilgili kalemleri projelendirmektir. Colgate'in 10K raporu, aşağıda gösterildiği gibi, geçmiş yıllardaki adi hisse senedi ve hazine hisse senedi faaliyetlerinin ayrıntılarını sağlar. . Colgate 2013 - 10K, Sayfa 68

Adım 9A - Hisse Yeniden Satın Alma: Geçmiş rakamları doldurun

Tarihsel olarak, yukarıdaki programı görebildiğimiz için Colgate hisseleri yeniden satın almıştır.
Colgate'in hisse geri alımını (milyonlarca) excel sayfasında doldurun.
Geçmişte seyreltilmiş EPS'yi Gelir Tablosundan bağlayın
Geçmişteki Yeniden Satın Alınan Tutar, nakit akış tablolarından referans alınmalıdır.

Ayrıca, Hızlandırılmış Hisse Geri Alımına bir göz atın.

Adım 9B - Hisse Yeniden Satın Alma: PE katını hesaplayın (EPS çoklu)

Colgate'in geçmişte hisse geri satın aldığı ima edilen ortalama fiyatı hesaplayın. Bu, Geri Alınan Miktar / Hisse Sayısı olarak hesaplanır.
PE katsayısını hesaplayın = Örtülü Hisse Fiyatı / EPS

Adım 9C - Yeniden Satın Alma Paylaşımı: Colgate'in Geri Alınan Payını Bulma

Colgate, kaç hisseyi geri almayı planladıklarına dair resmi bir açıklama yapmadı. 10K raporunun paylaştığı tek bilgi, 50 milyon hisseye kadar geri alım yetkisi verdikleri yönündedir. Colgate 2013 - 10K, Sayfa 35

Geri satın alınan hisse sayısını bulmak için Hisse Geri Alım Miktarını varsaymamız gerekir. Geçmişteki geri alım miktarına göre, bu rakamı gelecek yıllar için 1.500 milyon dolar gibi aldım.
Geri satın alınan hisse sayısını bulmak için, potansiyel geri alımın öngörülen zımni hisse fiyatına ihtiyacımız var.
Gerçek hisse fiyatı = varsayılan PE multipleks EPS.
Gelecekteki PE çoklu geri alımları geçmiş trendlere dayalı olarak varsayılabilir. Colgate'in ortalama 17x - 25x PE aralığında hisse geri satın aldığını not ediyoruz.
Aşağıda, Colgate için PE aralığını doğrulamamıza yardımcı olan Reuters'ten alınan anlık görüntü bulunmaktadır.

www.reuters.com

Bizim durumumuzda, Colgate'in gelecekteki tüm geri alımlarının 19x'in PE katında olacağını varsaydım.
19x PE değerini kullanarak, zımni fiyatı bulabiliriz = EPS x 19
Artık zımni fiyatı bulduğumuza göre, geri satın alınan hisse sayısını = geri satın alma / zımni fiyat için kullanılan $ tutarını görebiliriz.

Adım 9D - Stok Seçenekleri: Geçmiş Verileri Doldurun

Adi hisselerin ve hissedarların özkaynaklarının özetinden, her yıl kullanılan opsiyonların sayısını biliyoruz.

Ayrıca, nakit akış tablolarından Opsiyon Gelirleri de var (yaklaşık)

Bununla etkili bir kullanım fiyatı bulabilmeliyiz.

Colgate 2013 - 10K, Sayfa 53

Ayrıca, hisse senedi opsiyonlarının altı yıllık sözleşme sürelerine sahip olduğunu ve üç yıldan fazla hak kazandığını unutmayın. Colgate 2013 - 10K, Sayfa 69

Bu verilerle Seçenekler verilerini aşağıdaki gibi dolduruyoruz. Ayrıca 2013 için hisse senedi opsiyonlarının ağırlıklı ortalama kullanım fiyatının 42 $ olduğunu ve kullanılabilir opsiyon sayısının 24.151 milyon Colgate 2013 - 10K, Sayfa 70 olduğunu not ediyoruz.

Adım 9E - Hisse Opsiyonları: Devam Eden Opsiyonu Bulun.

Bu rakamları aşağıdaki opsiyon verilerimize koyarsak, opsiyon gelirlerinin 1.014 milyar $ olduğunu not ediyoruz.

Adım 9F - Stok Seçenekleri: Kısıtlanmış Stok Birimi Verilerini Tahmin Et

Stok seçeneklerine ek olarak, çalışanlara verilen, ağırlıklı ortalama 2,2 yıllık Sınırlı Stok Birimleri vardır Colgate 2013 - 10K, Sayfa 81

Bu verileri Seçenekler veri kümesine yerleştirme Basitlik uğruna, seçenek ihracı öngörmedik (bunun doğru bir varsayım olmadığını biliyorum; ancak, veri eksikliği nedeniyle, başka seçenek sorunlarını ileriye götürmüyorum. Bunları yeni aldık Yukarıdaki gri alanda vurgulandığı gibi sıfır olarak ek olarak, kısıtlı stok birimlerinin ileriye dönük 2,0 milyon olacağı tahmin edilmektedir.

Ayrıca Hazine Stok Metodu'na bir göz atın.

Adım 9G - Temettüler: Temettüleri Tahmin Edin

Temettü Ödeme Oranını kullanarak tahmini temettüleri tahmin edin.
Sabit temettü aşımı Hisse başına ödeme
10K raporlarından, temettülerle ilgili tüm geçmiş bilgileri çıkarıyoruz.
Ödenen temettü bilgileri ile Temettü ödeme oranı = Ödenen Toplam Temettü / Net Gelir öğrenebiliriz.
Colgate'in temettü ödeme oranını aşağıda görüldüğü gibi hesapladım - Temettü ödeme oranının genel olarak% 50 -% 60 aralığında olduğunu not ediyoruz. Gelecek yıllarda temettü ödeme oranının% 55 olduğunu varsayalım.
Ayrıca, öngörülen Net Geliri Gelir tablosundan da ilişkilendirebiliriz.
Hem öngörülen Net Geliri hem de temettü ödeme oranını kullanarak Ödenen Toplam Temettüleri bulabiliriz.

Adım 8H - Hisse senedi hesabının tamamını tahmin edin

Hisse geri alımı, opsiyon gelirleri ve ödenen temettüler ile ilgili tahminlerle, Hissedarların Öz Sermaye Planını tamamlamaya hazırız. Aşağıda gösterildiği gibi her yıl için Kapanış Özsermaye Bakiyesini bulmak için tüm bunları birbirine bağlayın.

Adım 9I - Kapanış Özkaynaklarının Bilanço ile İlişkilendirilmesi

Adım 9J - Temettüleri Bağlayın, Hisse geri satın alımı ve Opsiyonlar CF'ye ilerler

# 10 - Üstün Programı Paylaşıyor

Excel eğitiminde bu çevrimiçi finansal modellemenin bir sonraki adımı, Hisse Senedi Dışlama Planına bakmaktır. Ödenmemiş Hisse Senetlerinin Özeti

Temel Paylaşımlar - gerçek ve ortalama
Seçeneklerin ve dönüştürülebilir araçların geçmiş etkilerini uygun şekilde yakalayın
Seyreltilmiş Hisseler - ortalama
Yeniden satın alınan Referans Payları ve kullanılan opsiyonlardan yeni hisseler
Öngörülen ham yüzdeleri hesaplayın (gerçek)
Ortalama temel ve seyreltilmiş hisseleri hesaplayın
Öngörülen hisseleri Gelir Tablosuna referansla (geri çağırma Gelir Tablosu Oluşturun!)
Giriş tarihi, olağanüstü bilgileri paylaşır
Not : Bu program, genellikle Hisse Senedi Programı ile entegredir

Adım 10A - 10K raporundaki geçmiş sayıları girin

İhraç edilen hisseler (opsiyonların fiili olarak gerçekleştirilmesi) ve geri satın alınan hisseler, Hissedarın Özsermaye Planından referans alınabilir.
Girdi, ortalama hisse sayısını ve geçmiş yıllar için hisse senedi opsiyonlarının etkisini ağırlıklandırdı.

Adım 10B - Hisse Senedi Programından hisse ihraçları ile geri satın alımları ilişkilendirin.

Temel Paylar (Son) = Temel Paylar (Başlangıç) + Hisse İhraçları - Geri Alınan Paylar.

Adım 10C - Temel ağırlıklı ortalama paylaşımları bulun,

aşağıda gösterildiği gibi ortalama iki yıl buluyoruz.
Ayrıca, Seyreltilmiş Ağırlıklı Ortalama Payları bulmak için opsiyonların ve sınırlı stok birimlerinin etkisini (hissedarın öz sermaye programından referans olarak alınmıştır) ekleyin.

Adım 10D - Temel ve seyreltilmiş ağırlıklı hisse senetlerini Gelir Tablosu'na bağlayın

Artık seyreltilmiş ağırlıklı ortalama hisse senetlerini hesapladığımıza göre, Gelir Tablosu'nda da aynısını güncellememizin zamanı geldi.
Beklenen seyreltilmiş ağırlıklı ortalama hisse senetlerini Gelir Tablosu'na aşağıda gösterildiği gibi bağlayın

Bununla birlikte, Devredilen Hisse Senedi Programını tamamlıyoruz ve bir sonraki beyanlar kümemize geçme zamanı.

# 11 - Nakit Akışı Tablolarını Tamamlama

Bu Finansal Modellemedeki bir sonraki ve son programımıza, yani Borç Çizelgesine geçmeden önce nakit akış tablolarını tam olarak tamamlamamız önemlidir. Bu aşamaya kadar sadece birkaç tamamlanmamış şey var

Gelir Tablosu - bu aşamada faiz gideri / geliri eksik
Bilanço - nakit ve borç kalemleri bu aşamada eksiktir

Adım 11A - Finansman Faaliyetleri için Nakit Akışını Hesaplayın

Ayrıca, Finansmandan Nakit Akışına bakın

Adım 11B - Nakit ve Nakit Benzerlerinde net artışı (düşüş) bulun

Adım 11C = Nakit akışı tablolarını tamamlayın

Yıl sonunda yıl sonu nakit ve nakit benzerlerini bulun.

Adım 11D - Nakit ve nakit benzerlerini Bilançoya bağlayın.

Artık son ve son programımıza, yani Borç ve Faiz Planına, dikkat etmeye hazırız.

# 12- Excel'de Finansal Modelleme - Borç ve Faiz Planı

Bu Çevrimiçi Finansal Modellemedeki bir sonraki adım, Borç ve Faiz Planını tamamlamaktır. Borç ve Faizin Özeti - Program

12A Sınıfı - Bir Borç Takvimi Oluşturun

Finansman için Mevcut Nakit Akışına Başvurun
Tüm öz kaynak kaynaklarına ve nakit kullanımlarına atıfta bulunun

Adım 12B - Borç Geri Ödemesinden Nakit Akışını Hesaplayın

Bilançodan Başlangıç Nakit Bakiyesine Referans Verin
Minimum nakit bakiyesini düşürün. Colgate'in her yıl minimum 500 milyon $ tutmak istediğini varsaydık.

Uzun Vadeli Borç İhracı / Geri Ödemelerini, Döner Kredi Tesisi için mevcut Nakit ve Revolver bölümünü şimdilik atlayın Colgate'in 10K raporundan; Döndürülmüş Kredi Tesisi Colgate 2013 - 10K, Sayfa 35 ile ilgili mevcut ayrıntıları not ediyoruz

Borçla ilgili ek bilgilerde ayrıca taahhüt edilen uzun vadeli borç geri ödemeleri de verilmektedir. Colgate 2013 - 10K, Sayfa 36

Adım 12C - Sona Eren Uzun Vadeli Borç Hesaplayın.

Yukarıda verilen Uzun Vadeli Borç geri ödeme programını kullanırız ve Uzun Vadeli Borç Geri Ödemelerinin Kapanış Bakiyesini hesaplarız.

Adım 12D - Uzun vadeli borç geri ödemelerini ilişkilendirin.

Adım 12E - İsteğe bağlı borçlanmaları / playdown'ları hesaplayın.

Aşağıda gösterildiği gibi nakit süpürme formülünü kullanarak isteğe bağlı borçlanmaları / ödemeleri hesaplayın.

Adım 12F - Uzun Vadeli Borçtan Faiz Giderini Hesaplayın

Döner Kredi Kolaylığı ve Uzun Vadeli Borç için ortalama bakiyeyi hesaplayın
10K raporunda verilen bilgilere dayanarak bir faiz oranı için makul bir varsayımda bulunun
Toplam Faiz Giderini Hesapla = ortalama borç bakiyesi x faiz oranı

Toplam Faiz Giderini Bulun = Faiz (Döner Kredi Tesisi) + Faiz (Uzun Vadeli Borç)

Adım 12G - Ana Bağlantı borcu ve Revolver'ın Nakit Akışlarına indirilmesi

Adım 12H - Referans Cari ve Uzun Vadeli Bilanço

Uzun Vadeli Borç ve Uzun Vadeli Borcun Mevcut Kısmını aşağıda gösterildiği gibi ayırın

Döner Kredi Kolaylığını, Uzun Vadeli Borçları ve Uzun Vadeli Borçların Mevcut Kısmını Bilançoya Bağlayın

Adım 12I - Ortalama nakit bakiyesini kullanarak Faiz Gelirini hesaplayın

Adım 12J - Faiz Giderini ve Faiz Gelirini Gelir Tablosuna Bağlayın

Bilanço kontrolünü yapın: Toplam Varlıklar = Borçlar + Hissedarın Özsermayesi

Adım 12K - Bilançoyu Denetleyin

Herhangi bir tutarsızlık varsa, modeli denetlememiz ve herhangi bir bağlantı hatası olup olmadığını kontrol etmemiz gerekir.

Önerilen Finansal Modelleme Kursu

Umarım Ücretsiz Finansal Modelleme Excel Kılavuzu'nu beğenmişsinizdir. Excel'de Finansal Modellemeyi uzman video derslerimizle öğrenmek isterseniz Yatırım Bankacılığı Eğitimimize de bakabilirsiniz. Bu, öncelikle 99 kurs Yatırım Bankacılığı eğitim paketidir. Bu kurs temel bilgilerle başlar ve sizi Yatırım Bankacılığı İşinin ileri düzeyine götürür. Bu kurs beş bölüme ayrılmıştır -

Bölüm 1 - Yatırım Bankacılığı Eğitimi - Temel Kurslar
(26 Kurs)
Bölüm 2 - İleri Yatırım Bankacılığı Modelleme Eğitimi
(20 Kurs)
Bölüm 3 - Yatırım Bankacılığı Eklentileri
(13 Ders)
Bölüm 4 - Yatırım Bankacılığı Hazırlık Kursları
(23 Kurs)
Bölüm 5 - Yatırım Bankacıları için Esnek Beceriler
(17 Ders)

Finansal Modeller İndir

Alibaba Finansal Modeli
Kutu Halka Arz Finansal Modeli
Finansal Modelleme Şablonları
Bankacılık Finansal Modelleme Kursu

Sırada ne var?

Yeni bir şey öğrendiyseniz veya bu Excel tabanlı Finansal Modellemeden hoşlandıysanız, lütfen aşağıya bir yorum bırakın. Ne düşündüğü söyle. Çok teşekkürler ve kendinize iyi bakın. Mutlu Öğrenmeler!