Pemodelan Keuangan di Excel (Panduan + Templat Gratis Langkah demi Langkah)

Pemodelan keuangan di Excel adalah proses membangun model keuangan untuk mewakili transaksi, operasi, merger, akuisisi, informasi keuangan untuk menganalisis bagaimana perubahan dalam satu variabel dapat mempengaruhi hasil akhir sehingga dapat membuat keputusan pada satu atau lebih dari transaksi keuangan tersebut di atas.

Apa itu pemodelan keuangan di Excel?

Pemodelan Keuangan di Excel ada di seluruh web dan ada banyak tulisan tentang pembelajaran Pemodelan Keuangan, namun, sebagian besar bagian dari pelatihan pemodelan keuangan persis sama. Ini melampaui omong kosong biasa dan mengeksplorasi Pemodelan Keuangan praktis seperti yang digunakan oleh Bankir Investasi dan Analis Riset.

Dalam Panduan Excel Pemodelan Keuangan Gratis ini, saya akan mengambil contoh Colgate Palmolive dan akan menyiapkan model keuangan yang terintegrasi penuh dari awal.

Panduan ini lebih dari 6000 kata dan saya butuh waktu 3 minggu untuk menyelesaikannya. Simpan halaman ini untuk referensi di masa mendatang dan jangan lupa untuk membagikannya :-)

PALING PENTING - Unduh templat Excel pemodelan Keuangan Colgate untuk mengikuti petunjuknya

Unduh Template Model Keuangan Colgate

Pelajari Pemodelan Keuangan Langkah demi Langkah di Excel

Pemodelan Keuangan dalam Pelatihan Excel - Baca saya Dulu

Langkah 1 - Unduh Template Model Keuangan Colgate. Anda akan menggunakan template ini untuk tutorial

Unduh Model Keuangan Colgate

Langkah 2 - Harap diperhatikan bahwa Anda akan mendapatkan dua templat - 1) Model Keuangan Colgate Palmolive yang Belum Terselesaikan 2) Model Keuangan Colgate Palmolive yang Terselesaikan

Langkah 3 - Anda akan mengerjakan Template Model Keuangan Colgate Palmolive yang Belum Terselesaikan . Ikuti petunjuk langkah demi langkah untuk mempersiapkan model keuangan yang terintegrasi penuh.

Langkah 4 - Selamat Belajar!

Daftar Isi

Saya telah membuat daftar isi yang mudah dinavigasi bagi Anda untuk melakukan Pemodelan Keuangan ini

# 1 - Model Keuangan Colgate - Historis
# 2 - Analisis Rasio Colgate Palmolive
# 3 - Memproyeksikan Laporan Laba Rugi
# 4- Jadwal Modal Kerja
# 5 - Jadwal Penyusutan
# 6 - Jadwal Amortisasi
# 7 - Jadwal Jangka Panjang Lainnya
# 8 - Mengisi Laporan Laba Rugi
# 9 - Jadwal Ekuitas Pemegang Saham
# 10 - Membagikan Jadwal Beredar
# 11 - Melengkapi Laporan Arus Kas
# 12- Direkomendasikan Jadwal Hutang dan Bunga
Kursus Pemodelan Keuangan
Model Keuangan Gratis

Jika Anda baru mengenal Pemodelan Keuangan, lihat panduan tentang Apa itu Pemodelan Keuangan?

Bagaimana cara membangun model keuangan di Excel?

Mari kita lihat bagaimana model keuangan dibangun dari awal. Panduan pemodelan keuangan terperinci ini akan memberi Anda panduan langkah demi langkah untuk membuat model keuangan. Pendekatan utama yang diambil dalam panduan pemodelan keuangan ini adalah Modular. Pendekatan modular pada dasarnya berarti bahwa kita membuat laporan inti seperti Laporan Pendapatan, Neraca, dan Arus Kas menggunakan modul / jadwal yang berbeda. Fokus utamanya adalah mempersiapkan setiap pernyataan selangkah demi selangkah dan menghubungkan semua jadwal pendukung ke pernyataan inti pada saat penyelesaian. Saya dapat memahami bahwa hal ini mungkin belum jelas untuk saat ini, namun Anda akan menyadari bahwa ini sangat mudah saat kita bergerak maju. Anda dapat melihat di bawah ini berbagai Jadwal / Modul Pemodelan Keuangan -

Harap perhatikan hal-hal berikut -

Laporan inti adalah Laporan Pendapatan, Neraca, dan Arus Kas.
Jadwal tambahan adalah jadwal penyusutan, jadwal modal kerja, jadwal tidak berwujud, jadwal ekuitas pemegang saham, jadwal item jangka panjang lainnya, jadwal hutang, dll.
Jadwal tambahan terkait dengan pernyataan inti setelah diselesaikan
Dalam panduan pemodelan keuangan ini, kami akan membangun model keuangan terintegrasi langkah demi langkah dari Colgate Palmolive dari awal.

# 1 - Pemodelan Keuangan di Excel - Proyek Sejarah

Langkah pertama dalam Panduan Pemodelan Keuangan adalah mempersiapkan Sejarah.

Langkah 1A - Unduh Laporan 10K Colgate

“Model keuangan disiapkan di excel dan langkah pertama dimulai dengan mengetahui bagaimana kinerja industri selama beberapa tahun terakhir. Memahami masa lalu dapat memberi kita wawasan berharga terkait masa depan perusahaan. Oleh karena itu, langkah pertama adalah mengunduh semua keuangan perusahaan dan mengisinya dalam lembar excel. Untuk Colgate Palmolive, Anda dapat mengunduh laporan tahunan Colgate Palmolive dari Bagian Hubungan Investor mereka. Setelah Anda mengklik "Laporan tahunan", Anda akan menemukan jendela seperti di bawah ini -

Langkah 1B - Buat Lembar Kerja Laporan Keuangan Historis

Jika Anda mengunduh 10K tahun 2013, Anda akan melihat bahwa hanya tersedia dua tahun data laporan keuangan. Namun, untuk tujuan Pemodelan Keuangan di excel, dataset yang direkomendasikan adalah laporan keuangan 5 tahun terakhir. Harap unduh laporan tahunan 3 tahun terakhir dan isi riwayatnya.
Seringkali, tugas ini tampak terlalu membosankan dan membosankan karena mungkin membutuhkan banyak waktu dan energi untuk memformat dan menempatkan keunggulan dalam format yang diinginkan.
Namun, jangan lupa bahwa ini adalah pekerjaan yang harus Anda lakukan hanya sekali untuk setiap perusahaan dan juga, mengisi sejarah membantu seorang analis memahami tren dan laporan keuangan.
Jadi tolong jangan lewatkan ini, unduh data dan isi datanya (bahkan jika Anda merasa ini adalah pekerjaan keledai ;-))

Jika Anda ingin melewatkan langkah ini, Anda dapat langsung mengunduh Model Historis Colgate Palmolive di sini.

Laporan Laba Rugi Colgate dengan riwayat dihuni

Data Historis Neraca Colgate

# 2 - Analisis Rasio

Langkah kedua dalam Pemodelan Keuangan di Excel adalah melakukan Analisis Rasio.

Kunci untuk mempelajari Pemodelan Keuangan di Excel adalah mampu melakukan analisis fundamental. Jika Analisis Fundamental atau Analisis Rasio adalah sesuatu yang baru bagi Anda, saya sarankan Anda membaca sedikit di internet. Saya bermaksud untuk mengambil analisis rasio mendalam di salah satu posting saya yang akan datang, namun, berikut adalah cuplikan singkat dari rasio Colgate Palmolive

PENTING - Harap dicatat bahwa saya telah memperbarui Analisis Rasio Colgate di posting terpisah. Silakan lihat analisis rasio komprehensif ini.

Langkah 2A - Analisis Vertikal Colgate

Pada laporan laba rugi, analisis vertikal merupakan alat universal untuk mengukur kinerja relatif perusahaan dari tahun ke tahun dalam hal biaya dan profitabilitas. Ini harus selalu dimasukkan sebagai bagian dari analisis keuangan apa pun. Di sini, persentase dihitung sehubungan dengan penjualan bersih yang dianggap 100%. Upaya analisis vertikal dalam laporan laba rugi ini sering disebut sebagai analisis margin karena menghasilkan margin yang berbeda dalam kaitannya dengan penjualan.

Hasil Analisis Vertikal

Margin Laba telah meningkat 240 basis poin dari 56,2% pada tahun 2007 menjadi 58,6% pada tahun 2013. Hal ini terutama disebabkan oleh penurunan Beban Pokok Penjualan
Laba Operasi atau EBIT juga menunjukkan peningkatan marjin sehingga meningkat dari 19,7% pada tahun 2007 menjadi 22,4% pada tahun 2012 (meningkat 70 basis poin). Hal ini disebabkan penurunan biaya umum dan administrasi penjualan. Namun, perhatikan bahwa margin EBIT berkurang pada tahun 2013 menjadi 20,4% karena peningkatan “Beban lain-lain”. Juga, lihat perbedaan antara EBIT vs EBITDA
Marjin Laba Bersih meningkat dari 12,6% pada tahun 2007 menjadi 14,5% pada tahun 2012. Namun demikian, Marjin Laba pada tahun 2013 menurun menjadi 12,9%, terutama disebabkan oleh peningkatan “beban lain-lain”.
Laba per saham terus meningkat dari FY2007 hingga FY2012. Namun, ada sedikit penurunan pada EPS FY2013
Perhatikan juga bahwa Penyusutan dan Amortisasi disajikan secara terpisah dalam Laporan Laba Rugi. Ini sudah termasuk dalam Harga Pokok Penjualan

Langkah 2B - Analisis Horizontal Colgate

Analisis horizontal adalah teknik yang digunakan untuk mengevaluasi tren dari waktu ke waktu dengan menghitung persentase peningkatan excel atau penurunan relatif terhadap tahun dasar. Ini memberikan tautan analitis antara akun yang dihitung pada tanggal berbeda menggunakan mata uang dengan kekuatan pembelian yang berbeda. Akibatnya, analisis ini mengindeks akun dan membandingkan evolusinya dari waktu ke waktu. Seperti halnya metodologi analisis vertikal, akan muncul masalah yang perlu diselidiki dan dilengkapi dengan teknik analisis keuangan lainnya. Fokusnya adalah mencari gejala masalah yang dapat didiagnosis dengan menggunakan teknik tambahan.

Mari kita lihat analisis Horizontal Colgate

Hasil Analisis Horizontal

Kami melihat bahwa Penjualan Bersih telah meningkat sebesar 2,0% di tahun 2013.
Juga, perhatikan tren dalam Biaya Penjualan, kami melihat bahwa mereka tidak tumbuh dalam proporsi yang sama dengan Penjualan.
Pengamatan ini sangat berguna saat kami melakukan pemodelan keuangan di Excel

Langkah 2C - Rasio Likuiditas Colgate

Rasio likuiditas mengukur hubungan aset yang lebih likuid dari suatu perusahaan (yang paling mudah diubah menjadi kas) dengan kewajiban lancar. Rasio likuiditas yang paling umum adalah: Rasio lancar Rasio pengujian asam (atau aset cepat) Rasio Kas
Rasio Perputaran seperti Perputaran Piutang Usaha, Perputaran Persediaan, dan Perputaran Hutang

Ikhtisar Utama dari Rasio Likuiditas

Rasio Colgate saat ini lebih besar dari 1,0 untuk semua tahun. Ini menyiratkan bahwa aset lancar lebih besar dari kewajiban lancar dan mungkin Colgate memiliki likuiditas yang cukup
Quick Ratio Colgate berada pada kisaran 0,6-0,7, ini berarti Colgates Cash dan sekuritas yang dapat dipasarkan dapat membayar sebanyak 70% dari kewajiban lancar. Ini sepertinya situasi yang masuk akal untuk Colgate.
Siklus Penagihan Kas mengalami penurunan dari 43 hari pada tahun 2009 menjadi 39 hari pada tahun 2013. Hal ini terutama disebabkan oleh berkurangnya periode penagihan piutang.

Juga, lihat artikel terperinci ini tentang Siklus Konversi Tunai

Langkah 2D - Rasio Profitabilitas Operasi Colgate

Rasio profitabilitas kemampuan perusahaan untuk menghasilkan pendapatan relatif terhadap penjualan, aset, dan ekuitas

Sorotan Utama - Rasio Profitabilitas Colgate

Seperti yang dapat kita lihat dari tabel di atas, Colgate memiliki ROE mendekati 100%, yang menyiratkan pengembalian yang besar kepada pemegang Ekuitas.

Langkah 2E - Analisis Risiko Colgate

Melalui Analisis Risiko, kami mencoba mengukur apakah perusahaan mampu membayar kewajiban jangka pendek dan jangka panjang (hutang). Kami menghitung rasio leverage yang berfokus pada kecukupan aset atau generasi dari aset. Rasio yang dilihat adalah

Rasio Hutang terhadap Ekuitas
Rasio hutang
Rasio Cakupan Bunga
Debt to Equity Ratio terus meningkat ke level yang lebih tinggi yaitu 2,23x. Ini menandakan peningkatan Leverage Keuangan dan risiko di pasar
Namun, Rasio Cakupan Bunga yang sangat tinggi menandakan risiko gagal bayar pembayaran bunga berkurang.

# 3 - Pemodelan Keuangan di Excel - Proyeksikan Laporan Laba Rugi

Langkah ketiga dalam Pemodelan Keuangan adalah meramalkan Laporan Laba Rugi, di mana kita akan mulai dengan memodelkan item Penjualan atau Pendapatan.

Langkah 3A - Proyeksi Pendapatan

Bagi kebanyakan perusahaan, pendapatan adalah pendorong fundamental dari kinerja ekonomi. Model pendapatan yang dirancang dengan baik dan logis yang mencerminkan secara akurat jenis dan jumlah aliran pendapatan sangatlah penting. Ada banyak cara untuk merancang jadwal pendapatan seperti halnya bisnis. Beberapa jenis umum termasuk:

Pertumbuhan Penjualan: Asumsi pertumbuhan penjualan di setiap periode menunjukkan perubahan dari periode sebelumnya. Ini adalah metode yang sederhana dan umum digunakan tetapi tidak menawarkan wawasan tentang komponen atau dinamika pertumbuhan.
Efek Inflasi dan Volume / Campuran: Sebagai pengganti asumsi pertumbuhan sederhana, faktor inflasi harga dan faktor volume digunakan. Pendekatan yang berguna ini memungkinkan pemodelan biaya tetap dan variabel di perusahaan multi-produk dan memperhitungkan pergerakan harga vs volume.
Volume Satuan, Perubahan Volume, Harga Rata-rata dan Perubahan Harga: Metode ini cocok untuk bisnis yang memiliki bauran produk sederhana; itu memungkinkan analisis dampak dari beberapa variabel kunci.
Pertumbuhan dan Ukuran Pasar Dolar: Pangsa Pasar dan Perubahan Pangsa - Berguna untuk kasus-kasus di mana informasi tersedia tentang dinamika pasar dan di mana asumsi-asumsi ini kemungkinan besar menjadi fundamental bagi suatu keputusan. Misalnya industri Telekomunikasi
Ukuran dan Pertumbuhan Pasar Unit: Ini lebih rinci dari kasus sebelumnya dan berguna ketika harga di pasar adalah variabel kunci. (Untuk perusahaan dengan strategi diskon harga, misalnya, atau pemain ceruk harga premium terbaik dari ras) misalnya pasar mobil mewah
Kapasitas Volume, Tingkat Pemanfaatan Kapasitas, dan Harga Rata-Rata: Asumsi ini dapat menjadi penting untuk bisnis di mana kapasitas produksi penting untuk pengambilan keputusan. (Dalam pembelian kapasitas tambahan, misalnya, atau untuk menentukan apakah perluasan akan membutuhkan investasi baru.)
Ketersediaan dan Harga Produk
Pendapatan didorong oleh investasi dalam modal, pemasaran atau R&D
Berdasarkan pendapatan pada basis terpasang (melanjutkan penjualan suku cadang, sekali pakai, servis dan add-on, dll). Contohnya termasuk bisnis pisau cukur klasik dan bisnis seperti komputer yang mengutamakan penjualan layanan, perangkat lunak, dan peningkatan. Memodelkan basis yang dipasang adalah kuncinya (penambahan baru pada basis, gesekan pada basis, pendapatan berkelanjutan per pelanggan, dll).
Berbasis karyawan: Misalnya, pendapatan dari perusahaan jasa profesional atau perusahaan berbasis penjualan seperti pialang. Pemodelan harus fokus pada kepegawaian bersih, pendapatan per karyawan (seringkali berdasarkan jam yang dapat ditagih). Model yang lebih rinci akan mencakup senioritas dan faktor lain yang mempengaruhi harga.
Berbasis toko, fasilitas, atau rekaman persegi: Perusahaan ritel sering kali dimodelkan berdasarkan toko (toko lama ditambah toko baru setiap tahun) dan pendapatan per toko.
Berdasarkan faktor hunian: Pendekatan ini berlaku untuk maskapai penerbangan, hotel, bioskop, dan bisnis lain dengan biaya marjinal rendah.

Memproyeksikan Pendapatan Colgate

Sekarang mari kita lihat laporan Colgate 10K 2013. Kami mencatat bahwa dalam laporan laba rugi, Colgate belum memberikan informasi segmental, namun sebagai informasi tambahan, Colgate telah memberikan beberapa detail segmen pada Page 87 Sumber - Colgate 2013 - 10K, Halaman 86

Karena kami tidak memiliki informasi lebih lanjut tentang segmen tersebut, kami akan memproyeksikan penjualan Colgate di masa mendatang berdasarkan data yang tersedia ini. Kami akan menggunakan pendekatan pertumbuhan penjualan di seluruh segmen untuk mendapatkan perkiraan. Silakan lihat gambar di bawah ini. Kami telah menghitung tingkat pertumbuhan dari tahun ke tahun untuk setiap segmen. Sekarang kita dapat mengasumsikan persentase pertumbuhan penjualan berdasarkan tren historis dan memproyeksikan pendapatan di bawah setiap segmen. Total Penjualan Bersih adalah jumlah total dari Segmen Nutrisi Mulut, Perawatan Pribadi & Rumah , dan Hewan Peliharaan.

Langkah 3B - Proyeksi Biaya

Persentase Pendapatan: Sederhana tetapi tidak menawarkan wawasan tentang leverage (skala ekonomi atau beban biaya tetap
Biaya selain depresiasi sebagai persentase pendapatan dan depresiasi dari jadwal terpisah: Pendekatan ini sebenarnya paling sedikit yang dapat diterima dalam banyak kasus, dan hanya mengizinkan analisis parsial leverage operasi.
Biaya variabel berdasarkan pendapatan atau volume, biaya tetap berdasarkan tren historis dan depresiasi dari jadwal terpisah: Pendekatan ini adalah minimum yang diperlukan untuk analisis sensitivitas profitabilitas berdasarkan beberapa skenario pendapatan

Proyeksi Biaya untuk Colgate

Untuk memproyeksikan biaya, analisis vertikal yang dilakukan sebelumnya akan sangat membantu. Mari kita lihat kembali analisis vertikal -

Karena kami telah memperkirakan Penjualan, semua biaya lainnya adalah sebagian margin dari Penjualan ini.
Pendekatannya adalah dengan mengambil pedoman dari biaya historis dan margin pengeluaran dan kemudian memperkirakan margin masa depan.
Misalnya, Cost of Sales berada pada kisaran 41% -42% selama 5 tahun terakhir. Kami dapat melihat perkiraan margin atas dasar ini.
Demikian juga Beban Penjualan, Umum & Administrasi secara historis berada pada kisaran 34% -36%. Kami dapat mengasumsikan margin biaya SG&A di masa depan atas dasar ini. Demikian juga, kita dapat melanjutkan dengan rangkaian pengeluaran lainnya.

Dengan menggunakan margin di atas, kita dapat menemukan nilai aktual dengan perhitungan mundur.

Untuk menghitung provisi pajak, kami menggunakan asumsi Tarif Pajak Efektif

Juga, perhatikan bahwa kami tidak melengkapi baris "Beban Bunga (Pendapatan)" karena kami akan melihat kembali Laporan Laba Rugi di tahap selanjutnya.
Beban Bunga dan Pendapatan Bunga.
Kami juga belum menghitung Penyusutan dan Amortisasi yang sudah dimasukkan ke dalam Harga Pokok Penjualan.
Ini melengkapi Laporan Laba Rugi (setidaknya untuk saat ini!)

# 4- Pemodelan Keuangan - Jadwal Modal Kerja

Sekarang kita telah menyelesaikan laporan Laba Rugi, langkah keempat dalam Pemodelan Keuangan adalah melihat Jadwal Modal Kerja.

Berikut adalah langkah-langkah yang harus diikuti untuk Jadwal Modal Kerja

Langkah 4A - Hubungkan Penjualan Bersih dan Biaya Penjualan

Langkah 4B - Referensi Data Neraca terkait modal kerja

Referensi data masa lalu dari neraca
Hitung modal kerja bersih
Tiba pada peningkatan / penurunan modal kerja
Perhatikan bahwa kami belum memasukkan hutang jangka pendek dan kas dan setara kas dalam modal kerja. Kami akan menangani hutang dan kas dan setara kas secara terpisah.

Langkah 4C - Hitung Rasio Perputaran

Hitung rasio dan persentase historis
Gunakan akhir atau saldo rata-rata
Keduanya dapat diterima selama konsistensi dipertahankan

Langkah 4D - Isi asumsi untuk item modal kerja masa depan

Item tertentu tanpa pendorong yang jelas biasanya diasumsikan dalam jumlah yang konstan
Pastikan asumsi masuk akal dan sejalan dengan bisnis

Langkah 4E - Proyeksikan saldo modal kerja masa depan

Langkah 4F - Hitung perubahan Modal Kerja

Tiba di Arus Kas berdasarkan item baris individu
Pastikan tanda-tandanya akurat!

Langkah 4G - Hubungkan Modal Kerja yang diperkirakan ke Neraca

Langkah 4H - Hubungkan Modal Kerja dengan Laporan Arus Kas

# 5 - Pemodelan Keuangan di Excel - Jadwal Depresiasi

Dengan selesainya jadwal modal kerja, maka langkah selanjutnya dalam Pemodelan Keuangan ini adalah proyek Capex of Colgate dan memproyeksikan angka Depresiasi dan Aset. Colgate 2013 - 10K, Halaman 49

Penyusutan dan Amortisasi tidak disediakan sebagai item baris terpisah, namun sudah termasuk dalam biaya penjualan
Dalam kasus seperti itu, silakan lihat laporan Arus Kas di mana Anda akan menemukan Beban Penyusutan dan Amortisasi. Perhatikan juga bahwa angka di bawah ini adalah 1) Penyusutan 2) amortisasi. Jadi berapa angka depresiasi?
Saldo Akhir untuk APD = Saldo Awal + Belanja Modal - Depresiasi - Penyesuaian untuk Penjualan Aset (Persamaan DASAR)

Langkah 5A - Hubungkan angka Penjualan Bersih dalam Jadwal Penyusutan

Siapkan item baris
Referensi Penjualan Bersih
Masukkan belanja modal masa lalu
Tiba di Capex sebagai% dari Penjualan Bersih

Langkah 5B - Perkirakan Item Pengeluaran Modal

Untuk meramalkan belanja modal, ada berbagai pendekatan. Salah satu pendekatan umum adalah dengan melihat Siaran Pers, Proyeksi Manajemen, MD&A untuk memahami pandangan perusahaan tentang belanja modal di masa depan.
Jika perusahaan sudah memberikan panduan tentang belanja modal di masa depan, maka angka tersebut bisa kita ambil secara langsung.
Namun, jika angka Capex tidak tersedia secara langsung, maka kita dapat menghitungnya secara kasar menggunakan Capex sebagai% Penjualan (seperti yang dilakukan di bawah)
Gunakan penilaian Anda berdasarkan pengetahuan industri dan pendorong wajar lainnya

Langkah 5C- Referensi Informasi Masa Lalu

Kami akan menggunakan Saldo Akhir untuk APD = Saldo awal + Belanja Modal - Depresiasi - Penyesuaian untuk Penjualan Aset (persamaan BASE)
Sangat sulit untuk merekonsiliasi PP&E yang lalu karena pernyataan ulang, penjualan aset, dll
Oleh karena itu, disarankan untuk tidak merekonsiliasi APD sebelumnya karena dapat menimbulkan kebingungan.

Kebijakan Depresiasi Colgate

Kami mencatat bahwa Colgate belum secara eksplisit memberikan rincian rinci tentang Aset. Mereka lebih suka menggabungkan semua aset menjadi Tanah, Bangunan, Mesin, dan peralatan lainnya
Selain itu, umur manfaat untuk mesin dan peralatan tersedia dalam kisaran tertentu. Dalam hal ini, kita harus melakukan beberapa tebakan untuk sampai ke masa manfaat rata-rata yang tersisa untuk aset
Selain itu, pedoman masa manfaat tidak tersedia untuk "Peralatan Lainnya". Kita harus memperkirakan masa manfaat Peralatan lainnya

Colgate 2013 - 10K, Halaman 55

Di bawah ini adalah rincian Rincian Properti, Pabrik dan Peralatan tahun 2012 dan 2013

Colgate 2013 - 10K, Halaman 91

Langkah 5D - Perkirakan pecahnya Pabrik dan Peralatan Properti (APD)

Pertama, temukan Bobot Aset dari APD Saat Ini (2013)
Kami akan berasumsi bahwa bobot aset PPE 2013 ini akan terus berlanjut
Kami menggunakan bobot aset ini untuk menghitung pemecahan estimasi Belanja Modal

Langkah 5E - Perkirakan Depresiasi Aset

Harap dicatat bahwa kami tidak menghitung penyusutan Tanah karena tanah bukan merupakan aset yang dapat disusutkan
Untuk memperkirakan penyusutan dari perbaikan Bangunan, pertama-tama kami menggunakan struktur di bawah ini.
Penyusutan di sini dibagi menjadi dua bagian - 1) penyusutan dari Aset Perbaikan Bangunan yang sudah terdaftar di neraca 2) penyusutan dari Perbaikan bangunan di masa depan
Untuk menghitung penyusutan dari perbaikan bangunan yang tercantum pada aset, kami menggunakan Metode Penyusutan Garis Lurus sederhana
Untuk menghitung depresiasi masa depan, pertama-tama kita mengubah Belanja Modal menggunakan Fungsi TRANSPOSE di Excel
Kami menghitung penyusutan dari kontribusi aset setiap tahun
Juga, depresiasi tahun pertama dibagi 2 seperti yang kami asumsikan sebagai konvensi tengah tahun untuk penyebaran aset

Total Depresiasi Perbaikan Gedung = depresiasi dari Aset Perbaikan Gedung yang sudah terdaftar di neraca + depresiasi dari masa depan Perbaikan gedung Proses di atas untuk memperkirakan depresiasi digunakan untuk menghitung depresiasi 1) Peralatan & Mesin Manufaktur dan 2) Peralatan lainnya sebagai ditunjukkan di bawah ini.

Jenis peralatan lainnya

Total Depresiasi Colgate = Depresiasi (Perbaikan Gedung) + Depresiasi (Mesin & Peralatan) + Depresiasi (peralatan lain) Setelah kita mengetahui angka depresiasi total, kita dapat memasukkannya ke dalam persamaan BASE seperti yang ditunjukkan di bawah ini

Dengan ini, kami mendapatkan angka PP&E Net Akhir untuk setiap tahun

Langkah 5F - Hubungkan PP&E Bersih ke Neraca

# 6 - Jadwal Amortisasi

Langkah keenam dalam Pemodelan Keuangan di Excel ini adalah memperkirakan Amortisasi. Kami memiliki dua kategori besar untuk dipertimbangkan di sini - 1) Niat Baik dan 2) Hal Tak Berwujud Lainnya.

Langkah 6A - Peramalan Goodwill

Colgate 2013 - 10K, Halaman 61

Goodwill muncul di neraca ketika sebuah perusahaan mengakuisisi perusahaan lain. Biasanya sangat sulit untuk memproyeksikan niat baik untuk tahun-tahun mendatang.
Namun, Goodwill tunduk pada tes penurunan nilai setiap tahun yang dilakukan oleh perusahaan itu sendiri. Analis tidak dalam posisi untuk melakukan pengujian tersebut dan menyiapkan estimasi penurunan nilai
Sebagian besar analis tidak memproyeksikan niat baik, mereka hanya membuat ini konstan dan inilah yang juga akan kami lakukan dalam kasus kami.

Langkah 6B - Memperkirakan Aset Tak Berwujud Lainnya

Seperti dicatat dalam Laporan 10K Colgate, mayoritas dari kehidupan tak berwujud yang terbatas terkait dengan akuisisi Sanex
“Additions to Intangibles” juga sangat sulit untuk diproyeksikan
Laporan 10K Colgate memberi kami rincian biaya amortisasi 5 tahun ke depan.
Kami akan menggunakan perkiraan ini dalam Model Keuangan Colgate 2013 - 10K, Halaman 61

Langkah 6C - Ending net intangible ditautkan ke "Aset Tak Berwujud Lainnya"

Langkah 6D - hubungkan Depresiasi dan Amortisasi ke Laporan Arus Kas

Langkah 6E - Hubungkan Capex & Addition to Intangible to Cash Flow

# 7 - Jadwal Jangka Panjang Lainnya

Langkah selanjutnya dalam Pemodelan Keuangan ini adalah menyusun Jadwal Jangka Panjang Lainnya. Ini adalah jadwal yang kami persiapkan untuk "sisa" yang tidak memiliki pendorong khusus untuk perkiraan. Dalam kasus Colgate, Item Jangka Panjang lainnya (sisa) adalah Pajak Penghasilan Tangguhan (kewajiban dan aset), Aset lainnya dan kewajiban lainnya.

Langkah 7A - Referensi data historis dari Neraca

Juga, hitung perubahan item-item ini.

Langkah 7B - Perkirakan Aset dan Liabilitas Jangka Panjang

Pertahankan item Jangka Panjang konstan untuk tahun yang diproyeksikan jika pengemudi tidak terlihat
Tautkan item perkiraan jangka panjang ke Neraca seperti yang ditunjukkan di bawah ini

Langkah 7C - Referensi Item Jangka Panjang Lainnya ke Neraca

Langkah 7D - Hubungkan item jangka panjang dengan Laporan Arus Kas

Harap dicatat bahwa jika kami menjaga aset dan kewajiban jangka panjang tetap konstan, maka perubahan yang mengalir ke laporan arus kas akan menjadi nol.

# 8 - Pemodelan Keuangan di Excel - Melengkapi Laporan Laba Rugi

Sebelum kita melangkah lebih jauh dalam Pemodelan Keuangan berbasis Excel ini, kita sebenarnya akan kembali dan melihat kembali Laporan Laba Rugi
Isi saham rata-rata tertimbang dasar historis dan jumlah rata-rata tertimbang saham dilusian
Angka-angka ini tersedia dalam laporan 10K Colgate

Langkah 8A - Referensi saham dasar dan dilusian

Pada tahap ini, asumsikan bahwa jumlah saham dasar dan dilusian di masa mendatang akan tetap sama seperti tahun 2013.

Langkah 8B - Hitung laba per saham Dasar dan Dilusian

Dengan ini, kami siap untuk beralih ke jadwal berikutnya yaitu Jadwal Ekuitas Pemegang Saham.

# 9 - Pemodelan Keuangan - Jadwal Ekuitas Pemegang Saham

Langkah selanjutnya dalam Pemodelan Keuangan dalam Pelatihan Excel ini adalah dengan melihat Jadwal Ekuitas Pemegang Saham. Tujuan utama dari jadwal ini adalah untuk memproyeksikan item terkait ekuitas seperti Ekuitas Pemegang Saham, Dividen, Pembelian kembali Saham, Hasil Opsi, dll . Laporan 10K Colgate memberi kami rincian aktivitas saham biasa dan saham treasuri dalam beberapa tahun terakhir seperti yang ditunjukkan di bawah ini. Colgate 2013 - 10K, Halaman 68

Langkah 9A - Share Repurchase: Isi angka-angka historis

Secara historis, Colgate telah membeli kembali saham seperti yang bisa kita lihat di jadwal di atas.
Isi pembelian kembali saham Colgate (jutaan) di lembar excel.
Hubungkan EPS terdilusi historis dari Laporan Laba Rugi
Jumlah Historis yang Dibeli Kembali harus direferensikan dari laporan arus kas

Selain itu, lihat Pembelian Kembali Saham yang Dipercepat

Langkah 9B - Pembelian Kembali Saham: Hitung kelipatan PE (kelipatan EPS)

Hitung harga rata-rata tersirat saat Colgate melakukan pembelian kembali saham secara historis. Ini dihitung sebagai Jumlah Dibeli kembali / Jumlah saham
Hitung kelipatan PE = Harga Saham Tersirat / EPS

Langkah 9C - Pembelian Kembali Saham: Menemukan Saham Colgate Dibeli Kembali

Colgate belum membuat pengumuman resmi tentang berapa banyak saham yang akan mereka beli kembali. Satu-satunya informasi yang saham laporan 10K mereka adalah bahwa mereka telah mengesahkan pembelian kembali hingga 50 juta saham. Colgate 2013 - 10K, Halaman 35

Untuk mengetahui jumlah saham yang dibeli kembali, kita perlu mengasumsikan Jumlah Pembelian Kembali Saham. Berdasarkan jumlah pembelian kembali historis, saya menganggap angka ini sebagai $ 1.500 juta untuk tahun-tahun mendatang.
Untuk mengetahui jumlah saham yang dibeli kembali, kami memerlukan proyeksi harga saham tersirat dari potensi pembelian kembali.
Harga saham tersirat = diasumsikan EPS multipleks PE
Pembelian kembali PE di masa depan dapat diasumsikan berdasarkan tren historis. Kami mencatat bahwa Colgate telah membeli kembali saham pada kisaran PE rata-rata 17x - 25x
Di bawah ini adalah cuplikan dari Reuters yang membantu kami memvalidasi rentang PE untuk Colgate

www.reuters.com

Dalam kasus kami, saya berasumsi bahwa semua pembelian kembali Colgate di masa mendatang akan berada pada kelipatan PE 19x.
Menggunakan PE 19x, kita dapat menemukan harga tersirat = EPS x 19
Sekarang kita telah menemukan harga tersirat, kita dapat menemukan jumlah saham yang dibeli kembali = $ jumlah yang digunakan untuk pembelian kembali / harga tersirat

Langkah 9D - Opsi Saham: Isi Data Historis

Dari ringkasan saham biasa dan ekuitas pemegang saham, kami mengetahui jumlah opsi yang dieksekusi setiap tahun.

Selain itu, kami juga memiliki Hasil Opsi dari laporan arus kas (perkiraan)

Dengan ini, kita harus dapat menemukan harga kesepakatan yang efektif

Colgate 2013 - 10K, Halaman 53

Juga, perhatikan bahwa opsi saham memiliki persyaratan kontrak enam tahun dan vest selama tiga tahun. Colgate 2013 - 10K, Halaman 69

Dengan data ini, kami mengisi data Opsi seperti di bawah ini Kami juga mencatat bahwa strike price rata-rata tertimbang dari opsi saham untuk tahun 2013 adalah $ 42 dan jumlah opsi yang dapat dieksekusi adalah 24.151 juta Colgate 2013 - 10K, Halaman 70

Langkah 9E - Opsi Saham: Temukan Hasil Opsi

Menempatkan angka-angka ini dalam data opsi kami di bawah ini, kami mencatat bahwa hasil opsi adalah $ 1,014 miliar

Langkah 9F - Opsi Saham: Perkiraan Data Unit Stok Terbatas

Selain opsi saham, terdapat Satuan Saham Yang Dibatasi yang diberikan kepada karyawan dengan rata-rata tertimbang periode 2,2 tahun Colgate 2013 - 10K, Page 81

Mengisi data ini dalam kumpulan data Opsi Demi kesederhanaan, kami belum memproyeksikan penerbitan opsi (saya tahu ini bukan asumsi yang tepat, namun, karena kurangnya data, saya tidak akan mengambil penerbitan opsi lagi. Kami baru saja mengambil ini sebagai nol seperti yang disorot di area abu-abu di atas. Selain itu, unit stok terbatas diproyeksikan menjadi 2,0 juta ke depan.

Juga, lihat Metode Saham Treasury

Langkah 9G- Dividen: Perkirakan Dividen

Perkirakan perkiraan dividen menggunakan rasio Pembayaran Dividen
Pengeluaran dividen tetap untuk pembayaran per saham
Dari laporan 10K, kami mengekstrak semua informasi masa lalu tentang dividen
Dengan adanya informasi dividen yang dibayarkan, kita dapat mengetahui Dividend payout ratio = Total Dividen Dibayar / Pendapatan Bersih.
Saya telah menghitung rasio pembayaran dividen Colgate seperti yang terlihat di bawah ini - Kami mencatat bahwa rasio pembayaran dividen secara luas berada di kisaran 50% -60%. Mari kita membuat asumsi rasio pembayaran Dividen 55% di tahun-tahun mendatang.
Kami juga dapat menautkan Proyeksi Pendapatan Bersih dari laporan Laba Rugi
Dengan menggunakan proyeksi Pendapatan Bersih dan rasio pembayaran dividen, kita dapat menemukan Total Dividen Dibayar

Langkah 8H - Perkirakan akun ekuitas secara keseluruhan

Dengan perkiraan pembelian kembali saham, hasil opsi dan pembayaran dividen, kami siap untuk menyelesaikan Jadwal Ekuitas Pemegang Saham. Tautkan semua ini untuk menemukan Saldo Ekuitas Akhir untuk setiap tahun seperti yang ditunjukkan di bawah ini.

Langkah 9I - Tautkan Ekuitas Pemegang Saham Akhir ke Neraca

Langkah 9J - Hubungkan Dividen, Pembelian Kembali Saham & Opsi dilanjutkan ke CF.

# 10 - Membagikan Jadwal Beredar

Langkah selanjutnya dalam pemodelan keuangan online dalam pelatihan Excel ini adalah melihat Jadwal Pengambilalihan Saham. Ringkasan Jadwal Saham Beredar

Saham Dasar - aktual dan rata-rata
Tangkap efek masa lalu dari opsi dan konvertibel yang sesuai
Saham Dilusian - rata-rata
Saham Referensi dibeli kembali dan saham baru dari opsi yang dieksekusi
Hitung bagian dasar yang diperkirakan (aktual)
Hitung rata-rata saham dasar dan dilusian
Referensi saham yang diproyeksikan ke Laporan Laba Rugi (ingat Laporan Laba Rugi Build up!)
Masukkan informasi saham historis yang beredar
Catatan : Jadwal ini biasanya terintegrasi dengan Jadwal Ekuitas

Langkah 10A - Masukkan angka historis dari laporan 10K

Saham yang diterbitkan (realisasi opsi sebenarnya) dan saham yang dibeli kembali dapat dirujuk dari Daftar Ekuitas Pemegang Saham
Selain itu, input tersebut membobotkan jumlah rata-rata saham dan pengaruh opsi saham selama tahun-tahun sejarah.

Langkah 10B - Penerbitan saham tautan & pembelian kembali dari Jadwal Ekuitas Saham.

Saham Dasar (Akhir) = Saham Dasar (Awal) + Penerbitan Saham - Saham yang Dibeli Kembali.

Langkah 10C - Temukan bagian rata-rata tertimbang dasar,

kami menemukan rata-rata dua tahun seperti yang ditunjukkan di bawah ini.
Juga, tambahkan efek opsi & unit saham terbatas (direferensikan dari jadwal ekuitas pemegang saham) untuk menemukan Saham Rata-Rata Tertimbang Dilusian.

Langkah 10D - Hubungkan Saham Dasar & tertimbang yang dilusian ke Laporan Laba Rugi

Sekarang setelah kita menghitung rata-rata tertimbang saham yang dilusian, sekarang saatnya kita memperbaruinya di Laporan Laba Rugi.
Hubungkan perkiraan rata-rata tertimbang saham dilusian yang diperkirakan ke Laporan Laba Rugi seperti yang ditunjukkan di bawah ini

Dengan ini, kami menyelesaikan Jadwal Pengeluaran Saham dan waktu untuk beralih ke rangkaian pernyataan berikutnya.

# 11 - Melengkapi Laporan Arus Kas

Penting bagi kita untuk melengkapi laporan arus kas sebelum kita beralih ke jadwal berikutnya dan terakhir dalam Pemodelan Keuangan ini yaitu Jadwal Hutang Sampai tahap ini, hanya ada beberapa hal yang tidak lengkap.

Laporan Laba Rugi - beban bunga / pendapatan tidak lengkap pada tahap ini
Neraca - item kas dan hutang tidak lengkap pada tahap ini

Langkah 11A - Hitung Arus Kas untuk Aktivitas Pendanaan

Juga, periksa Arus Kas dari Pembiayaan

Langkah 11B - Temukan kenaikan bersih (penurunan) dalam Kas & Setara Kas

Langkah 11C = Lengkapi laporan arus kas

Temukan kas & setara kas akhir tahun di akhir tahun.

Langkah 11D - Hubungkan kas & setara kas ke Neraca.

Sekarang kami siap untuk mengurus jadwal terakhir dan terakhir kami, yaitu Jadwal Hutang dan Bunga

# 12- Pemodelan Keuangan di Excel - Jadwal Hutang dan Bunga

Langkah selanjutnya dalam Pemodelan Keuangan Online ini adalah melengkapi Jadwal Hutang dan Bunga. Ringkasan Hutang dan Bunga - Jadwal

Langkah 12A - Buat Jadwal Hutang

Referensi Arus Kas yang Tersedia untuk Pembiayaan
Referensi semua sumber ekuitas dan penggunaan uang tunai

Langkah 12B - Hitung Arus Kas dari Pelunasan Hutang

Referensi Saldo Kas Awal dari Neraca
Kurangi saldo kas minimum. Kami berasumsi bahwa Colgate ingin menyimpan minimal $ 500 juta setiap tahun.

Lewati Penerbitan / Pembayaran Utang Jangka Panjang, Uang tunai tersedia untuk Fasilitas Kredit Bergulir dan bagian Revolver untuk saat ini Dari laporan 10K Colgate, kami mencatat perincian yang tersedia tentang Fasilitas Kredit Berputar Colgate 2013 - 10K, Halaman 35

Juga disediakan dalam informasi tambahan tentang Hutang adalah pembayaran hutang jangka panjang yang berkomitmen. Colgate 2013 - 10K, Halaman 36

Langkah 12C - Hitung Akhir Hutang Jangka Panjang

Kami menggunakan jadwal pembayaran Hutang Jangka Panjang yang disediakan di atas dan menghitung Saldo Akhir dari Pembayaran Utang Jangka Panjang

Langkah 12D - Hubungkan pembayaran hutang jangka panjang.

Langkah 12E -Hitung pinjaman / pembayaran diskresioner

Dengan menggunakan rumus cash sweep seperti yang ditunjukkan di bawah ini, hitunglah pinjaman / pembayaran diskresioner.

Langkah 12F - Hitung Beban Bunga dari Hutang Jangka Panjang

Hitung saldo rata-rata Fasilitas Kredit Revolving dan Utang Jangka Panjang
Buat asumsi yang wajar untuk suku bunga berdasarkan informasi yang diberikan dalam laporan 10K
Hitung Total Pengeluaran Bunga = saldo rata-rata hutang x tingkat bunga

Temukan Total Pengeluaran Bunga = Bunga (Fasilitas Kredit Bergulir) + Bunga (Hutang Jangka Panjang)

Langkah 12G - Hubungkan Hutang Pokok & Penarikan Revolver ke Arus Kas

Langkah 12H - Referensi Saat Ini dan Jangka Panjang ke Neraca

Batasi Bagian Lancar dari Utang Jangka Panjang dan Utang Jangka Panjang seperti yang ditunjukkan di bawah ini

Tautkan Fasilitas Kredit Bergulir, Utang Jangka Panjang, dan Bagian Lancar dari Utang Jangka Panjang ke Neraca

Langkah 12I - Hitung Pendapatan Bunga menggunakan saldo kas rata-rata

Langkah 12J - Hubungkan Beban Bunga dan Pendapatan Bunga dengan Laporan Laba Rugi

Lakukan pemeriksaan Neraca: Total Aset = Kewajiban + Ekuitas Pemegang Saham

Langkah 12K - Audit Neraca

Jika ada ketidaksesuaian, maka kita perlu mengaudit model dan memeriksa kesalahan tautan apa pun

Kursus Pemodelan Keuangan yang Direkomendasikan

Saya harap Anda menikmati Panduan Excel Pemodelan Keuangan Gratis. Jika Anda ingin mempelajari Pemodelan Keuangan di Excel melalui video ceramah ahli kami, Anda juga dapat melihat Pelatihan Perbankan Investasi kami. Ini terutama 99 kursus bundel pelatihan Perbankan Investasi. Kursus ini dimulai dari dasar-dasar dan membawa Anda ke level lanjutan dari Pekerjaan Perbankan Investasi. Kursus ini dibagi menjadi 5 bagian -

Bagian 1 - Pelatihan Perbankan Investasi - Kursus Inti
(26 Kursus)
Bagian 2 - Pelatihan Pemodelan Perbankan Investasi Tingkat Lanjut
(20 Kursus)
Bagian 3 - Pengaya Perbankan Investasi
(13 Kursus)
Bagian 4 - Kursus Yayasan Perbankan Investasi
(23 Kursus)
Bagian 5 - Soft Skills untuk Bankir Investasi
(17 Kursus)

Unduh Model Keuangan

Model Keuangan Alibaba
Kotak Model Keuangan IPO
Template Pemodelan Keuangan
Kursus Pemodelan Keuangan Perbankan

Apa selanjutnya?

Jika Anda mempelajari sesuatu yang baru atau menikmati Pemodelan Keuangan berbasis Excel ini, silakan tinggalkan komentar di bawah. Biarkan aku tahu apa yang kamu pikirkan. Banyak terima kasih dan hati-hati. Selamat Belajar!