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. Ada banyak tulisan tentang pembelajaran Pemodelan Keuangan; namun, sebagian besar bagian dari pelatihan pemodelan keuangan adalah 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 tiga 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

Masukkan Alamat Email Dengan melanjutkan langkah di atas, Anda menyetujui Persyaratan Penggunaan dan Kebijakan Privasi kami.

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

  • Bagaimana cara membangun model keuangan?
  • # 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 nol. 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. Sistem modular pada dasarnya berarti membangun laporan inti seperti Laporan Penghasilan, Neraca, dan Arus Kas menggunakan modul / jadwal yang berbeda. Fokus utamanya adalah mempersiapkan setiap pernyataan selangkah demi selangkah dan menghubungkan semua program pendukung ke pernyataan inti setelah diselesaikan. Saya dapat memahami bahwa ini mungkin tidak jelas sekarang; namun, Anda akan menyadari bahwa ini sangat mudah saat kami 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 yang berbeda adalah jadwal depresiasi, jadwal modal kerja, jadwal tidak berwujud, jadwal ekuitas pemegang saham, jadwal item jangka panjang lainnya, jadwal hutang, dll.
  • Jadwal yang berbeda terkait dengan pernyataan inti setelah diselesaikan.
  • Dalam panduan pemodelan keuangan ini, kami akan membangun model ekonomi 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 yang ditunjukkan 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, orang tidak boleh 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 datanya 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 unsur atau Analisis Rasio adalah sesuatu yang baru bagi Anda, saya sarankan Anda membaca sedikit di internet. Saya bermaksud untuk mengambil analisis rasio yang mendalam di salah satu posting saya yang akan datang, namun, berikut adalah snapshot cepat 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 terkait penjualan.

Hasil Analisis Vertikal
  • Margin Laba telah meningkat sebesar 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, 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 turun 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 kenaikan excel atau penurunan relatif terhadap tahun dasar. Ini memberikan tautan analitis antara akun yang dihitung pada tanggal yang berbeda menggunakan mata uang dengan berbagai kekuatan pembelian. Akibatnya, analisis ini mengindeks laporan dan membandingkan evolusinya dari waktu ke waktu. Seperti metodologi analisis vertikal, akan muncul masalah yang perlu diselidiki dan dilengkapi dengan teknik analisis keuangan lainnya. Fokusnya adalah mencari gejala masalah yang dapat didiagnosis menggunakan metode tambahan.

Mari kita lihat analisis Horizontal Colgate.

Hasil Analisis Horizontal
  • Kami melihat bahwa Penjualan Bersih meningkat sebesar 2,0% di tahun 2013.
  • Perhatikan juga tren dalam Cost of Sales. 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 uji asam (atau aset cepat) Rasio Kas.
  • Rasio Perputaran seperti Perputaran Piutang Usaha, Perputaran Persediaan, dan Perputaran Hutang
Ikhtisar Utama dari Rasio Likuiditas
  • Rasio Lancar Colgate lebih signifikan dari 1,0 untuk semua tahun. Ini menyiratkan bahwa aset lancar lebih signifikan daripada kewajiban lancar, dan mungkin Colgate memiliki likuiditas yang cukup.
  • Rasio cepat Colgate berada di 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 bagus 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 sangat baik kepada pemegang Ekuitas.

Langkah 2E - Analisis Risiko Colgate

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

  • Rasio Hutang terhadap Ekuitas
  • Rasio pinjaman
  • 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, menunjukkan risiko gagal bayar pembayaran bunga yang lebih kecil.

# 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 penggerak fundamental dari kinerja ekonomi. Model pendapatan yang dirancang dengan baik dan logis yang mencerminkan jenis dan jumlah arus pendapatan secara akurat sangatlah penting. Ada banyak cara untuk membuat jadwal pendapatan seperti halnya bisnis. Beberapa jenis umum termasuk:

  • Pertumbuhan Penjualan: Asumsi pertumbuhan penjualan di setiap periode menentukan perubahan dari periode sebelumnya. Ini adalah metode 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 merupakan variabel penting. (Untuk perusahaan dengan strategi diskon harga, misalnya, atau pemain ceruk harga premium terbaik dari breed) misalnya, pasar mobil mewah
  • Kapasitas Volume, Tingkat Pemanfaatan Kapasitas, dan Harga Rata-Rata: Asumsi ini dapat menjadi penting untuk bisnis di mana kapasitas produksi sangat penting untuk 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 mencakup bisnis pisau cukur klasik dan bisnis seperti komputer yang membutuhkan penjualan layanan, perangkat lunak, dan peningkatan versi. Memodelkan pangkalan yang dipasang adalah kuncinya (penambahan baru ke lantai, gesekan di tanah, 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 bagian dari 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 fitur 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 elemen. Sekarang kita dapat mengasumsikan persentase pertumbuhan penjualan berdasarkan tren historis dan memproyeksikan pendapatan di bawah setiap bagian. 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 yang berbeda: 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 dari margin Penjualan ini.
  • Pendekatannya adalah dengan mengambil pedoman dari biaya historis dan margin pengeluaran dan kemudian memperkirakan margin masa depan.
  • Misalnya, Cost of Sales berada di kisaran 41% -42% selama lima 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 kita tidak melengkapi baris “Beban Bunga (Pendapatan)” karena kita 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 di masa depan
  • Item tertentu tanpa pengemudi yang menonjol 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, itu sudah termasuk dalam biaya penjualan
  • Dalam kasus seperti itu, silakan lihat laporan Arus Kas di mana Anda akan menemukan Beban Penyusutan dan Amortisasi. Juga, perhatikan bahwa angka-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 praktik umum adalah 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)
  • 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 malah 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, panduan masa manfaat tidak tersedia untuk "Peralatan Lainnya". Kami 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 Capex menggunakan Fungsi TRANSPOSE di Excel.
  • Kami menghitung penyusutan dari kontribusi aset setiap tahun.
  • Juga, depresiasi tahun pertama dibagi dua 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 tambahan) Setelah kita mengetahui angka depresiasi yang sebenarnya, 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 rumit 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.
  • Kebanyakan analis tidak memproyeksikan niat baik; mereka hanya menjaga ini tetap konstan, 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 rumit untuk diproyeksikan
  • Laporan 10K Colgate memberi kami rincian biaya amortisasi lima tahun ke depan.
  • Kami akan menggunakan perkiraan ini dalam Model Keuangan Colgate 2013 - 10K, Halaman 61
Langkah 6C - Aktiva bersih tak berwujud akhir 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 saat kami mempersiapkan "sisa makanan" yang tidak memiliki pendorong khusus untuk perkiraan. Dalam kasus Colgate, Item Jangka Panjang lainnya (sisa) adalah Pajak Penghasilan Tangguhan (kewajiban dan aset), Investasi lain, 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 akan kembali dan melihat kembali Laporan Laba Rugi
  • Mengisi rata-rata tertimbang saham 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 primer 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 sahamnya, seperti yang bisa kita lihat jadwal di atas.
  • Isi pembelian kembali saham Colgate (jutaan) di lembar excel.
  • Hubungkan EPS terdilusi historis dari Laporan Laba Rugi
  • Jumlah Pembelian Kembali historis 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 yang Dibeli / 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 dibagikan laporan 10K mereka adalah bahwa mereka telah mengotorisasi 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 telah mengambil angka ini seperti $ 1.500 juta untuk tahun-tahun mendatang.
  • Untuk mengetahui jumlah saham yang dibeli kembali, kita memerlukan perkiraan harga saham tersirat dari potensi pembelian kembali.
  • Harga saham aktual = diasumsikan EPS multipleks PE.
  • Pembelian kembali PE di masa mendatang 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 melihat 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 harga kesepakatan 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 Satuan Persediaan Terbatas

Selain opsi saham, terdapat Unit 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 membahas masalah 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 asumsikan 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 menyelesaikan Jadwal Ekuitas Pemegang Saham. Tautkan semua ini untuk menemukan Saldo Ekuitas Akhir untuk setiap tahun, seperti yang ditunjukkan di bawah ini.

Langkah 9I - Hubungkan 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 convertible yang sesuai
  • Saham Dilusian - rata-rata
  • Saham Referensi dibeli kembali dan saham baru dari opsi yang dieksekusi
  • Hitung persentase mentah 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
  • Masukan 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 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 Pengambilalihan Saham dan waktu untuk beralih ke rangkaian pernyataan berikutnya.

# 11 - Melengkapi Laporan Arus Kas

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

  • 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 kita siap untuk mengurus jadwal terakhir dan terakhir kita, 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

Grade 12A - Atur 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 rincian yang tersedia pada 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 / playdown discretionary.

Dengan menggunakan rumus penyisiran tunai, seperti yang ditunjukkan di bawah ini, hitunglah pinjaman / pembayaran bebas.

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 - Hutang Tautan 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
  • Hubungkan Fasilitas Kredit Berputar, Hutang Jangka Panjang, dan Bagian Lancar dari Hutang 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 dengan dasar-dasar dan membawa Anda ke level lanjutan dari Pekerjaan Perbankan Investasi. Kursus ini dibagi menjadi lima bagian -

  • Bagian 1 - Pelatihan Perbankan Investasi - Kursus Inti
    (26 Kursus)
  • Bagian 2 - Pelatihan Pemodelan Perbankan Investasi Tingkat Lanjut
    (20 Kursus)
  • Bagian 3 - Add-on Perbankan Investasi
    (13 Kursus)
  • Bagian 4 - Kursus Yayasan Perbankan Investasi
    (23 Kursus)
  • Bagian 5 - Keterampilan Lunak 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. Terima kasih banyak, dan hati-hati. Selamat Belajar!

Artikel yang menarik...