Pemodelan Kewangan dalam Excel (Panduan + Langkah Bebas Langkah demi langkah)

Pemodelan kewangan di Excel adalah proses membina model kewangan untuk mewakili transaksi, operasi, penggabungan, pemerolehan, maklumat kewangan untuk menganalisis bagaimana perubahan dalam satu pemboleh ubah dapat mempengaruhi pengembalian akhir sehingga dapat membuat keputusan mengenai satu atau lebih urus niaga kewangan yang disebutkan di atas.

Apakah pemodelan kewangan di Excel?

Pemodelan Kewangan di Excel terdapat di seluruh web dan telah banyak ditulis mengenai pembelajaran Pemodelan Kewangan, namun, kebanyakan latihan pemodelan kewangan hampir sama. Ini melampaui kebiasaan biasa dan meneroka Pemodelan Kewangan praktikal seperti yang digunakan oleh Banker Pelaburan dan Penganalisis Penyelidikan.

Dalam Panduan Excel Pemodelan Kewangan Percuma ini, saya akan mengambil contoh Colgate Palmolive dan akan menyediakan model kewangan bersepadu sepenuhnya dari awal.

Panduan ini melebihi 6000 patah perkataan dan memerlukan 3 minggu untuk disiapkan. Simpan halaman ini untuk rujukan di masa hadapan dan jangan lupa kongsikan :-)

PALING PENTING - Muat turun templat Excel pemodelan Colgate Financial untuk mengikuti arahan

Muat turun Templat Model Kewangan Colgate

Pelajari Pemodelan Kewangan Langkah demi Langkah di Excel

Pemodelan Kewangan dalam Latihan Excel - Baca Saya Pertama

Langkah 1 - Muat turun Templat Model Kewangan Colgate. Anda akan menggunakan templat ini untuk tutorial

Muat turun Model Kewangan Colgate

Langkah 2 - Harap maklum bahawa anda akan mendapat dua templat - 1) Model Kewangan Colgate Palmolive yang Tidak Terselesaikan 2) Model Kewangan Colgate Palmolive yang Diselesaikan

Langkah 3- Anda akan mengusahakan Templat Model Kewangan Colgate Palmolive yang Tidak Terpecahkan . Ikuti arahan langkah demi langkah untuk menyediakan model kewangan bersepadu.

Langkah 4 - Selamat Belajar!

Isi kandungan

Saya telah membuat jadual kandungan yang mudah dilayari agar anda dapat melakukan Pemodelan Kewangan ini

# 1 - Model Kewangan Colgate - Sejarah
# 2 - Analisis Nisbah Colgate Palmolive
# 3 - Mengunjurkan Penyata Pendapatan
# 4- Jadual Modal Kerja
# 5 - Jadual Susut Nilai
# 6 - Jadual Pelunasan
# 7 - Jadual Jangka Panjang Lain
# 8 - Melengkapkan Penyata Pendapatan
# 9 - Jadual Ekuiti Pemegang Saham
# 10 - Jadual Luar Biasa Saham
# 11 - Melengkapkan Penyata Aliran Tunai
# 12- Jadual Hutang dan Faedah Disyorkan
Kursus Pemodelan Kewangan
Model Kewangan Percuma

Sekiranya anda baru menggunakan Pemodelan Kewangan, maka lihatlah panduan ini mengenai Apa itu Pemodelan Kewangan?

Bagaimana membina model kewangan di Excel?

Mari kita lihat bagaimana model kewangan dibina dari awal. Panduan pemodelan kewangan terperinci ini akan memberi anda panduan langkah demi langkah untuk membuat model kewangan. Pendekatan utama yang diambil dalam panduan pemodelan kewangan ini adalah Modular. Pendekatan modular pada dasarnya bermaksud bahawa kita membina penyata teras seperti Penyata Pendapatan, Lembaran Imbangan dan Aliran Tunai menggunakan modul / jadual yang berbeza. Fokus utama adalah untuk menyediakan setiap pernyataan langkah demi langkah dan menghubungkan semua jadual sokongan ke pernyataan teras setelah selesai. Saya dapat memahami bahawa ini mungkin tidak jelas seperti sekarang, namun, anda akan menyedari bahawa ini sangat mudah semasa kita terus maju. Anda dapat melihat di bawah pelbagai Jadual / Modul Pemodelan Kewangan -

Perhatikan perkara berikut -

Penyata inti adalah Penyata Pendapatan, Lembaran Imbangan, dan Aliran Tunai.
Jadual tambahan adalah jadual susut nilai, jadual modal kerja, jadual tidak berwujud, jadual ekuiti pemegang saham, jadual item jangka panjang yang lain, jadual hutang, dll.
Jadual tambahan dihubungkan dengan pernyataan utama setelah selesai
Dalam panduan pemodelan kewangan ini, kami akan membina model kewangan bersepadu langkah demi langkah Colgate Palmolive dari awal.

# 1 - Pemodelan Kewangan di Excel - Projek Sejarah

Langkah pertama dalam Panduan Pemodelan Kewangan adalah menyediakan Sejarah.

Langkah 1A - Muat turun Laporan 10K Colgate

"Model kewangan disiapkan dengan cemerlang dan langkah pertama bermula dengan mengetahui bagaimana prestasi industri dalam beberapa tahun terakhir. Memahami masa lalu dapat memberi kita pandangan berharga yang berkaitan dengan masa depan syarikat. Oleh itu, langkah pertama adalah memuat turun semua kewangan syarikat dan mengisi yang sama dalam lembaran excel. Untuk Colgate Palmolive, anda boleh memuat turun laporan tahunan Colgate Palmolive dari Bahagian Perhubungan Pelabur mereka. Sebaik sahaja anda mengklik "Laporan tahunan", anda akan menemui tetingkap seperti yang ditunjukkan di bawah -

Langkah 1B - Buat Lembaran Kerja Penyata Kewangan Sejarah

Sekiranya anda memuat turun 10K tahun 2013, anda akan perhatikan bahawa hanya data penyata kewangan dua tahun sahaja yang tersedia. Walau bagaimanapun, untuk tujuan Pemodelan Kewangan di excel, set data yang disyorkan adalah mempunyai penyata kewangan 5 tahun terakhir. Sila muat turun laporan tahunan 3 tahun terakhir dan isikan sejarahnya.
Sering kali, tugas-tugas ini kelihatan terlalu membosankan dan membosankan kerana memerlukan banyak masa dan tenaga untuk memformat dan meletakkan cemerlang dalam format yang diinginkan.
Walau bagaimanapun, seseorang tidak boleh lupa bahawa ini adalah pekerjaan yang wajib anda lakukan hanya sekali untuk setiap syarikat dan juga, mengisi sejarah membantu seorang penganalisis memahami trend dan penyata kewangan
Oleh itu, jangan lewatkan ini, muat turun data dan isi data (walaupun anda merasakan bahawa ini adalah kerja keldai ;-))

Sekiranya anda ingin melangkau langkah ini, anda boleh memuat turun Model Sejarah Colgate Palmolive secara langsung di sini.

Penyata Pendapatan Colgate dengan sejarah dihuni

Data Sejarah Lembaran Imbangan Colgate

# 2 - Analisis Nisbah

Langkah kedua dalam Pemodelan Kewangan di Excel adalah melakukan Analisis Nisbah.

Kunci untuk belajar Pemodelan Kewangan di Excel adalah untuk dapat melakukan analisis asas. Sekiranya analisis asas atau Analisis Nisbah adalah sesuatu yang baru untuk anda, saya mengesyorkan agar anda membaca sedikit di internet. Saya berhasrat untuk mengambil analisis nisbah mendalam dalam salah satu catatan saya yang akan datang, namun, berikut adalah gambaran ringkas mengenai nisbah Colgate Palmolive

PENTING - Harap maklum bahawa saya telah mengemas kini Analisis Nisbah Colgate dalam catatan yang berasingan. Sila lihat analisis nisbah komprehensif ini.

Langkah 2A - Analisis Vertikal Colgate

Pada penyata pendapatan, analisis menegak adalah alat universal untuk mengukur prestasi relatif syarikat dari tahun ke tahun dari segi kos dan keuntungan. Ia mesti selalu dimasukkan sebagai sebahagian daripada analisis kewangan. Di sini, peratusan dikira berkaitan dengan penjualan bersih yang dianggap 100%. Usaha analisis menegak dalam penyata pendapatan ini sering disebut sebagai analisis margin kerana menghasilkan margin yang berbeza berkaitan dengan penjualan.

Hasil Analisis Vertikal

Margin Keuntungan telah meningkat sebanyak 240 mata asas dari 56.2% pada tahun 2007 kepada 58.6% pada tahun 2013. Ini disebabkan terutamanya oleh penurunan Kos Jualan
Keuntungan Operasi atau EBIT juga menunjukkan margin yang meningkat dengan itu meningkat dari 19.7% pada tahun 2007 kepada 22.4% pada tahun 2012 (peningkatan 70 mata asas). Ini disebabkan oleh penurunan kos penjualan umum dan pentadbiran. Walau bagaimanapun, perhatikan bahawa margin EBIT berkurang pada tahun 2013 menjadi 20.4% disebabkan oleh kenaikan "perbelanjaan Lain". Juga, periksa perbezaan antara EBIT vs EBITDA
Margin Untung Bersih meningkat dari 12.6% pada tahun 2007 kepada 14.5% pada tahun 2012. Walau bagaimanapun, Margin Keuntungan pada tahun 2013 menurun kepada 12.9%, terutamanya disebabkan oleh peningkatan "perbelanjaan lain".
Pendapatan sesaham terus meningkat dari TK2007 hingga TK2012. Walau bagaimanapun, terdapat sedikit penurunan pada EPS pada TK2013
Juga, perhatikan bahawa Susutnilai dan Pelunasan disediakan secara berasingan dalam Penyata Pendapatan. Ia termasuk dalam Kos Jualan

Langkah 2B - Analisis Mendatar Colgate

Analisis mendatar adalah teknik yang digunakan untuk menilai tren dari masa ke masa dengan mengira peratusan peningkatan excel atau penurunan berbanding tahun asas. Ini memberikan hubungan analitik antara akaun yang dikira pada tarikh yang berbeza menggunakan mata wang dengan daya beli yang berbeza. Akibatnya, analisis ini mengindeks akaun dan membandingkan evolusi ini dari masa ke masa. Seperti metodologi analisis menegak, permasalahan akan muncul yang perlu diselidiki dan dilengkapi dengan teknik analisis kewangan yang lain. Fokusnya adalah untuk mencari gejala masalah yang dapat didiagnosis menggunakan teknik tambahan.

Mari kita lihat analisis Horizontal Colgate

Hasil Analisis Mendatar

Kami melihat bahawa Jualan Bersih telah meningkat sebanyak 2.0% pada tahun 2013.
Juga, perhatikan trend Kos Jualan, kita melihat bahawa mereka tidak berkembang dalam bahagian yang sama dengan Penjualan.
Pemerhatian ini sangat berguna semasa kami melakukan pemodelan kewangan di Excel

Langkah 2C - Nisbah Kecairan Colgate

Nisbah kecairan mengukur hubungan aset lebih cair syarikat (yang paling mudah ditukar kepada tunai) dengan liabiliti semasa. Nisbah kecairan yang paling biasa adalah: Nisbah semasa Nisbah ujian asid (atau aset cepat) Nisbah Tunai
Nisbah Perolehan seperti perolehan Penghutang Akaun, Perolehan inventori, dan Perolehan Hutang

Sorotan Utama Nisbah Kecairan

Nisbah semasa Colgate lebih besar daripada 1.0 untuk semua tahun. Ini menunjukkan bahawa aset semasa lebih besar daripada liabiliti semasa dan mungkin Colgate mempunyai kecairan yang mencukupi
Nisbah Pantas Colgate berada dalam julat 0.6-0.7, ini bermaksud bahawa sekuriti Colgates Cash dan Marketable dapat membayar sebanyak 70% dari liabiliti semasa. Ini kelihatan seperti situasi yang wajar untuk Colgate.
Kitaran Pungutan Tunai telah menurun dari 43 hari pada tahun 2009 menjadi 39 hari pada tahun 2013. Ini terutamanya disebabkan oleh penurunan dalam tempoh kutipan penghutang.

Juga, lihat artikel terperinci ini mengenai Kitaran Penukaran Tunai

Langkah 2D - Nisbah Keuntungan Operasi Colgate

Nisbah keuntungan kemampuan syarikat untuk menjana pendapatan berbanding penjualan, aset, dan ekuiti

Sorotan Utama - Nisbah Keuntungan Colgate

Seperti yang dapat kita lihat dari jadual di atas, Colgate mempunyai ROE yang hampir 100%, yang menunjukkan pulangan yang besar kepada pemegang Ekuiti.

Langkah 2E - Analisis Risiko Colgate

Melalui Analisis Risiko, kami cuba mengukur sama ada syarikat akan dapat membayar obligasi jangka pendek dan jangka panjangnya (hutang). Kami mengira nisbah leverage yang menumpukan pada kecukupan aset atau penjanaan dari aset. Nisbah yang dilihat adalah

Nisbah Hutang kepada Ekuiti
Nisbah hutang
Nisbah Perlindungan Faedah
Nisbah Hutang ke Ekuiti terus meningkat ke tahap yang lebih tinggi iaitu 2.23x. Ini menunjukkan peningkatan Leverage Kewangan dan risiko di pasaran
Walau bagaimanapun, Nisbah Perlindungan Faedah sangat tinggi menandakan kurang risiko Kelalaian Bayaran Faedah.

# 3 - Pemodelan Kewangan di Excel - Projek Penyata Pendapatan

Langkah ketiga dalam Pemodelan Kewangan adalah meramalkan Penyata Pendapatan, di mana kita akan mulai dengan memodelkan item Penjualan atau Hasil.

Langkah 3A - Unjuran Pendapatan

Bagi kebanyakan syarikat, pendapatan merupakan pemacu asas prestasi ekonomi. Model pendapatan yang direka dengan baik dan logik yang menggambarkan dengan tepat jenis dan jumlah aliran pendapatan sangat penting. Ada banyak cara untuk merancang jadual pendapatan seperti ada perniagaan. Beberapa jenis biasa termasuk:

Pertumbuhan Penjualan: Andaian pertumbuhan penjualan dalam setiap tempoh menentukan perubahan dari tempoh sebelumnya. Ini adalah kaedah yang mudah dan biasa digunakan tetapi tidak memberikan gambaran mengenai komponen atau dinamika pertumbuhan.
Kesan inflasi dan Volume / Campuran: Daripada anggapan pertumbuhan sederhana, faktor inflasi harga dan faktor volum digunakan. Pendekatan berguna ini memungkinkan pemodelan kos tetap dan berubah-ubah di syarikat pelbagai produk dan mengambil kira pergerakan harga berbanding jumlah.
Volume Unit, Perubahan Volume, Purata Harga dan Perubahan Harga: Kaedah ini sesuai untuk perniagaan yang mempunyai campuran produk yang sederhana; ia membenarkan analisis kesan beberapa pemboleh ubah utama.
Saiz dan Pertumbuhan Pasaran Dolar: Pangsa Pasar dan Perubahan Saham - Berguna untuk kes di mana maklumat tersedia mengenai dinamika pasaran dan di mana andaian ini cenderung menjadi asas bagi keputusan. Sebagai contoh industri Telekomunikasi
Saiz dan Pertumbuhan Pasaran Unit: Ini lebih terperinci daripada kes sebelumnya dan berguna apabila harga di pasaran adalah pemboleh ubah utama. (Untuk syarikat dengan strategi pengurangan harga, misalnya, atau pemain khusus harga premium terbaik) contohnya pasar kereta mewah
Kapasiti Isipadu, Kadar Penggunaan Kapasiti, dan Harga Purata: Andaian ini boleh menjadi penting bagi perniagaan di mana kapasiti pengeluaran adalah penting untuk membuat keputusan. (Dalam pembelian kapasiti tambahan, misalnya, atau untuk menentukan apakah pengembangan itu memerlukan pelaburan baru.)
Ketersediaan dan Harga Produk
Pendapatan didorong oleh pelaburan modal, pemasaran atau R&D
Hasil berdasarkan asas terpasang (penjualan berterusan komponen, pakai buang, servis dan alat tambah, dll). Contohnya termasuk perniagaan dan perniagaan pisau cukur klasik seperti komputer di mana penjualan perkhidmatan, perisian dan peningkatan penting. Memodelkan pangkalan yang dipasang adalah kunci (penambahan baru ke pangkalan, penurunan di pangkalan, pendapatan berterusan bagi setiap pelanggan, dll.)
Berasaskan pekerja: Contohnya, pendapatan firma perkhidmatan profesional atau firma berasaskan penjualan seperti broker. Pemodelan harus difokuskan pada staf bersih, pendapatan per pekerja (selalunya berdasarkan jam yang dapat dikenakan) Model yang lebih terperinci akan merangkumi kekananan dan faktor lain yang mempengaruhi harga.
Kedai, kemudahan atau rakaman Persegi: Syarikat runcit sering dimodelkan berdasarkan dasar kedai (kedai lama ditambah kedai baru setiap tahun) dan pendapatan per kedai.
Berdasarkan faktor penghunian: Pendekatan ini berlaku untuk syarikat penerbangan, hotel, pawagam dan perniagaan lain dengan kos marginal yang rendah.

Mengunjurkan Pendapatan Colgate

Mari kita lihat laporan Colgate 10K 2013. Kami perhatikan bahawa dalam penyata pendapatan, Colgate belum memberikan maklumat segmen, namun, sebagai bagian dari maklumat tambahan, Colgate telah memberikan beberapa perincian segmen di Halaman 87 Sumber - Colgate 2013 - 10K, Halaman 86

Oleh kerana kami tidak mempunyai maklumat lebih lanjut mengenai segmen tersebut, kami akan memproyeksikan penjualan Colgate pada masa depan berdasarkan data yang tersedia ini. Kami akan menggunakan pendekatan pertumbuhan penjualan di segmen untuk memperoleh ramalan. Sila lihat gambar di bawah. Kami telah mengira kadar pertumbuhan tahun ke tahun untuk setiap segmen. Sekarang kita dapat menganggap peratusan pertumbuhan penjualan berdasarkan tren sejarah dan memproyeksikan pendapatan di bawah setiap segmen. Jumlah penjualan Bersih adalah jumlah keseluruhan Segmen Pemakanan Mulut, Perawatan Peribadi dan Rumah , dan Nutrisi Haiwan Peliharaan.

Langkah 3B - Unjuran Kos

Peratusan Pendapatan: Sederhana tetapi tidak memberikan wawasan mengenai leverage (ekonomi skala atau beban kos tetap)
Kos selain penyusutan sebagai peratus pendapatan dan susut nilai dari jadual yang berasingan: Pendekatan ini benar-benar minimum yang boleh diterima dalam kebanyakan kes, dan hanya membenarkan analisis sebahagian daripada leveraj operasi.
Kos berubah berdasarkan pendapatan atau jumlah, kos tetap berdasarkan tren sejarah dan susut nilai dari jadual yang berasingan: Pendekatan ini adalah minimum yang diperlukan untuk analisis sensitiviti keuntungan berdasarkan pelbagai senario pendapatan

Unjuran Kos untuk Colgate

Untuk mengunjurkan kos, analisis menegak yang dilakukan lebih awal akan sangat membantu. Mari kita lihat semula analisis menegak -

Oleh kerana kami telah meramalkan Penjualan, semua kos lain adalah beberapa margin Penjualan ini.
Pendekatannya adalah dengan mengambil garis panduan dari margin kos dan perbelanjaan sejarah dan kemudian meramalkan margin masa depan.
Sebagai contoh, Kos Jualan berada dalam lingkungan 41% -42% selama 5 tahun yang lalu. Kita boleh melihat ramalan margin berdasarkan ini.
Begitu juga, Perbelanjaan Jual, Umum & Pentadbiran secara historis berada dalam lingkungan 34% -36%. Kami dapat menanggung margin perbelanjaan SG&A pada masa akan datang berdasarkan ini. Begitu juga, kita boleh meneruskan perbelanjaan yang lain.

Dengan menggunakan margin di atas, kita dapat mencari nilai sebenar dengan pengiraan belakang.

Untuk mengira peruntukan cukai, kami menggunakan andaian Kadar Cukai Berkesan

Juga, perhatikan bahawa kami tidak melengkapkan baris "Beban Faedah (Pendapatan)" kerana kami akan melihat kembali Penyata Pendapatan di peringkat kemudian.
Perbelanjaan Faedah dan Pendapatan Faedah.
Kami juga belum mengira Susut Nilai dan Pelunasan yang telah dimasukkan dalam Kos Jualan.
Ini melengkapkan Penyata Pendapatan (sekurang-kurangnya buat masa ini!)

# 4- Pemodelan Kewangan - Jadual Modal Kerja

Setelah kita melengkapkan penyata Pendapatan, langkah keempat dalam Pemodelan Kewangan adalah dengan melihat Jadual Modal Kerja.

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

Langkah 4A - Pautkan Jualan dan Kos Jualan Bersih

Langkah 4B - Rujuk Data Lembaran Imbangan yang berkaitan dengan modal kerja

Rujuk data masa lalu dari kunci kira-kira
Hitung modal kerja bersih
Tiba kenaikan / penurunan modal kerja
Perhatikan bahawa kami belum memasukkan hutang jangka pendek dan tunai serta setara tunai dalam modal kerja. Kami akan menangani hutang dan tunai dan setara tunai secara berasingan.

Langkah 4C - Hitung Nisbah Perolehan

Hitung nisbah dan peratusan sejarah
Gunakan baki akhir atau purata
Kedua-duanya dapat diterima selagi konsistensi dikekalkan

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

Item tertentu tanpa pemacu yang jelas biasanya dianggap pada jumlah yang tetap
Pastikan andaian adalah wajar dan sesuai dengan perniagaan

Langkah 4E - Buat baki modal kerja masa depan

Langkah 4F - Hitung perubahan Modal Kerja

Tiba di Aliran Tunai berdasarkan item baris individu
Pastikan tanda betul!

Langkah 4G - Pautkan Modal Kerja yang diramalkan ke Neraca

Langkah 4H - Pautkan Modal Kerja ke Penyata Aliran Tunai

# 5 - Pemodelan Kewangan di Excel - Jadual Susut Nilai

Dengan selesainya jadual modal kerja, langkah seterusnya dalam Pemodelan Kewangan ini adalah memproyeksikan Capex of Colgate dan memproyeksikan angka Susut Nilai dan Aset. Colgate 2013 - 10K, Halaman 49

Susut nilai dan Pelunasan tidak disediakan sebagai item baris yang berasingan, namun, ia termasuk dalam kos penjualan
Dalam kes seperti ini, sila perhatikan penyata aliran tunai di mana anda akan mendapat perbelanjaan susut nilai dan pelunasan. Perhatikan juga bahawa angka di bawah ini adalah 1) Susut nilai 2) pelunasan. Jadi berapa nombor susut nilai?
Baki Akhir untuk PPE = Baki permulaan + Capex - Susutnilai - Penyesuaian untuk Penjualan Aset (persamaan BASE)

Langkah 5A - Pautkan angka Penjualan Bersih dalam Jadual Susut Nilai

Sediakan item baris
Jualan Bersih Rujukan
Masukkan perbelanjaan modal lalu
Tiba di Capex sebagai% Penjualan Bersih

Langkah 5B - Ramalkan Item Perbelanjaan Modal

Untuk meramalkan perbelanjaan Modal, terdapat berbagai pendekatan. Salah satu pendekatan umum adalah melihat Siaran Akhbar, Unjuran Pengurusan, MD&A untuk memahami pandangan syarikat mengenai perbelanjaan modal masa depan
Sekiranya syarikat telah memberikan panduan mengenai perbelanjaan modal masa depan, maka kita dapat mengambil nombor tersebut secara langsung.
Namun, jika nombor Capex tidak tersedia secara langsung, maka kita dapat menghitungnya dengan kasar menggunakan Capex sebagai% Penjualan (seperti yang dilakukan di bawah)
Gunakan pertimbangan anda berdasarkan pengetahuan industri dan pemacu lain yang munasabah

Langkah 5C- Rujuk Maklumat Lalu

Kami akan menggunakan Baki Akhir untuk PPE = Baki permulaan + Capex - Susut nilai - Penyesuaian untuk Penjualan Aset (persamaan BASE)
Sangat sukar untuk mendamaikan PP&E yang lalu kerana penyataan semula, penjualan aset, dll
Oleh itu, disarankan untuk tidak mendamaikan PPE yang lalu kerana boleh menimbulkan kekeliruan.

Dasar Susut Nilai Colgate

Kami perhatikan bahawa Colgate tidak secara jelas memberikan perpecahan terperinci mengenai Aset. Mereka lebih suka memasukkan semua aset ke Tanah, Bangunan, Mesin dan peralatan lain
Juga, jangka hayat berguna untuk mesin dan peralatan disediakan dalam pelbagai. Dalam kes ini, kita harus melakukan beberapa tekaan untuk mencapai rata-rata jangka hayat yang berguna untuk aset tersebut
Juga, panduan untuk kehidupan berguna tidak disediakan untuk "Peralatan Lain". Kita mesti mengira jangka hayat untuk Peralatan lain

Colgate 2013 - 10K, Halaman 55

Berikut adalah perincian Perincian Harta Tanah, Loji dan Peralatan 2012 dan 2013

Colgate 2013 - 10K, Halaman 91

Langkah 5D - Anggarkan perpecahan loji dan peralatan harta tanah (PPE)

Pertama, cari berat Aset PPE Semasa (2013)
Kami akan menganggap bahawa bobot aset PPE 2013 ini akan terus maju
Kami menggunakan bobot aset ini untuk mengira pecahan anggaran Perbelanjaan Modal

Langkah 5E - Anggarkan Susut Nilai Aset

Harap maklum bahawa kami tidak menghitung susut nilai Tanah kerana tanah bukanlah aset yang boleh diusutkan
Untuk menganggarkan susut nilai dari peningkatan Bangunan, pertama-tama kita menggunakan struktur di bawah.
Susut nilai di sini terbahagi kepada dua bahagian - 1) susut nilai dari Aset Penambahbaikan Bangunan yang telah disenaraikan di Lembaran Imbangan 2) susut nilai dari peningkatan bangunan masa depan
Untuk mengira susut nilai dari peningkatan bangunan yang disenaraikan pada aset, kami menggunakan kaedah susut nilai Straight Line kaedah yang mudah
Untuk mengira susut nilai masa depan, pertama kali kita menukar Capex menggunakan Fungsi TRANSPOSE di Excel
Kami mengira susut nilai sumbangan aset dari setiap tahun
Juga, susut nilai tahun pertama dibahagi dengan 2 kerana kami menganggap konvensyen pertengahan tahun untuk penggunaan aset

Jumlah Susut Nilai Penambahbaikan Bangunan = susut nilai dari Aset Penambahbaikan Bangunan yang telah disenaraikan di Lembaran Imbangan + susut nilai dari masa depan Penambahbaikan bangunan Proses di atas untuk menganggarkan susut nilai digunakan untuk mengira susut nilai 1) Peralatan & Jentera Pembuatan dan 2) Peralatan lain sebagai ditunjukkan di bawah.

Jenis peralatan lain

Jumlah Susut Nilai Colgate = Susut Nilai (Penambahbaikan Bangunan) + Susut Nilai (Mesin & Peralatan) + Susut nilai (peralatan lain) Setelah kita mengetahui jumlah angka susut nilai, kita dapat memasukkannya ke dalam persamaan BASE seperti yang ditunjukkan di bawah

Dengan ini, kita memperoleh angka PP&E Bersih Akhir untuk setiap tahun

Langkah 5F - Pautkan PP&E Bersih ke Lembaran Imbangan

# 6 - Jadual Pelunasan

Langkah keenam dalam Pemodelan Kewangan ini di Excel adalah meramalkan Pelunasan. Kami mempunyai dua kategori luas yang perlu dipertimbangkan di sini - 1) Muhibah dan 2) Lain-lain yang tidak ketara.

Langkah 6A - Meramalkan Muhibah

Colgate 2013 - 10K, Halaman 61

Muhibah datang pada kunci kira-kira apabila syarikat memperoleh syarikat lain. Biasanya sukar untuk memproyeksikan Muhibah untuk tahun-tahun mendatang.
Walau bagaimanapun, Muhibah menjalani ujian penurunan nilai setiap tahun yang dilakukan oleh syarikat itu sendiri. Penganalisis tidak dapat melakukan ujian sedemikian dan menyediakan anggaran penurunan
Sebilangan besar penganalisis tidak memproyeksikan muhibah, mereka tetap menjadikannya tetap berterusan dan inilah yang akan kita lakukan dalam kes kita.

Langkah 6B - Meramalkan Aset Tidak ketara Lain

Seperti yang dinyatakan dalam Laporan 10K Colgate, sebahagian besar jangka hayat tak terbatas berkaitan dengan pemerolehan Sanex
"Penambahan pada Yang Tidak Berwujud" juga sangat sukar untuk diproyeksikan
Laporan 10K Colgate memberi kami perincian perbelanjaan pelunasan 5 tahun akan datang.
Kami akan menggunakan anggaran ini dalam Model Kewangan Colgate 2013 - 10K, Halaman 61 kami

Langkah 6C - Tak berwujud bersih akhir dihubungkan dengan "Aset Tidak ketara Lain"

Langkah 6D - pautkan Susut Nilai dan Pelunasan ke Penyata Aliran Tunai

Langkah 6E - Pautkan Capex & Penambahan kepada penyata aliran Tunai yang Tidak Berwujud ke Tunai

# 7 - Jadual Jangka Panjang Lain

Langkah seterusnya dalam Pemodelan Kewangan ini adalah menyiapkan Jadual Jangka Panjang Lain. Ini adalah jadual yang kami siapkan untuk “sisa” yang tidak mempunyai pendorong khusus untuk ramalan. Dalam kes Colgate, Item Jangka Panjang yang lain (sisa) adalah Pajak Pendapatan Ditangguhkan (liabiliti dan aset), Aset lain dan liabiliti lain.

Langkah 7A - Rujuk data sejarah dari Neraca

Juga, hitung perubahan dalam item ini.

Langkah 7B - Ramalkan Aset dan Liabiliti Jangka Panjang

Pastikan item Jangka Panjang tetap berterusan untuk tahun yang diunjurkan sekiranya tiada pemandu yang kelihatan
Pautkan item jangka panjang yang diramalkan ke Neraca seperti yang ditunjukkan di bawah

Langkah 7C - Rujuk Item Jangka Panjang Lain ke Neraca

Langkah 7D - Pautkan item jangka panjang ke Penyata Aliran Tunai

Harap maklum bahawa jika kami memastikan aset dan liabiliti jangka panjang tetap berterusan, maka perubahan yang mengalir ke penyata aliran tunai akan menjadi sifar.

# 8 - Pemodelan Kewangan di Excel - Melengkapkan Penyata Pendapatan

Sebelum kita melangkah lebih jauh dalam Pemodelan Kewangan berasaskan Excel ini, kita sebenarnya akan kembali dan membaca Penyata Pendapatan
Kumpulkan saham purata wajaran asas sejarah dan bilangan saham purata wajaran dicairkan
Angka-angka ini terdapat dalam laporan 10K Colgate

Langkah 8A - Rujuk saham asas dan dicairkan

Pada peringkat ini, anggap bahawa jumlah saham asas dan dilusi yang akan datang akan tetap sama seperti pada tahun 2013.

Langkah 8B - Kira pendapatan Asas dan Dilusi per saham

Dengan ini, kami bersedia untuk beralih ke jadual seterusnya iaitu Jadual Ekuiti Pemegang Saham.

# 9 - Pemodelan Kewangan - Jadual Ekuiti Pemegang Saham

Langkah seterusnya dalam Pemodelan Kewangan dalam Latihan Excel ini adalah dengan melihat Jadual Ekuiti Pemegang Saham. Objektif utama jadual ini adalah untuk memproyeksikan item berkaitan ekuiti seperti Ekuiti, Dividen, Pembelian Balik Saham, Hasil Opsyen, dll . Laporan Colgate 10K memberi kami perincian aktiviti saham biasa dan saham perbendaharaan pada tahun-tahun kebelakangan seperti yang ditunjukkan di bawah. Colgate 2013 - 10K, Halaman 68

Langkah 9A - Beli Balik Saham: Isi nombor sejarah

Dari segi sejarah, Colgate telah membeli balik saham seperti yang dapat kita lihat jadual di atas.
Isi pembelian semula saham Colgate (berjuta-juta) di lembaran excel.
Pautkan EPS yang dicairkan sejarah dari Penyata Pendapatan
Jumlah Sejarah yang Dibeli Semula harus dirujuk dari penyata aliran tunai

Juga, lihat Pembelian Balik Saham Dipercepat

Langkah 9B - Pembelian Balik Saham: Hitung gandaan PE (gandaan EPS)

Hitung harga rata-rata tersirat di mana Colgate melakukan pembelian semula saham secara sejarah. Ini dikira sebagai Jumlah Dibeli Semula / Jumlah saham
Hitung gandaan PE = Harga Saham Tersirat / EPS

Langkah 9C - Pembelian Balik Saham: Mencari Saham Colgate yang Dibeli Semula

Colgate belum membuat pengumuman rasmi mengenai berapa banyak saham yang mereka ingin beli balik Satu-satunya maklumat bahawa 10K laporan saham mereka adalah bahawa mereka telah membenarkan pembelian balik sehingga 50 juta saham. Colgate 2013 - 10K, Halaman 35

Untuk mengetahui jumlah saham yang dibeli kembali, kita harus menganggap Jumlah Pembelian Balik Saham. Berdasarkan jumlah pembelian semula sejarah, saya telah mengambil angka ini sebagai $ 1,500 juta untuk semua tahun akan datang.
Untuk mengetahui jumlah saham yang dibeli semula, kita memerlukan unjuran harga saham tersirat dari potensi pembelian semula.
Harga saham tersirat = diandaikan E multiplex PE
Masa depan membeli balik PE boleh diandaikan berdasarkan tren sejarah. Kami perhatikan bahawa Colgate telah membeli kembali saham pada julat PE purata 17x - 25x
Berikut adalah petikan dari Reuters yang membantu kami mengesahkan julat PE untuk Colgate

www.reuters.com

Dalam kes kami, saya mengandaikan bahawa semua pembelian balik Colgate di masa depan akan menjadi gandaan PE 19x.
Dengan menggunakan PE 19x, kita dapat mencari harga tersirat = EPS x 19
Sekarang kita telah menemui harga tersirat, kita dapat mengetahui jumlah saham yang dibeli semula = jumlah $ yang digunakan untuk pembelian semula / harga tersirat

Langkah 9D - Pilihan Saham: Mengisi Data Sejarah

Dari ringkasan saham biasa dan ekuiti pemegang saham, kami mengetahui jumlah opsyen yang dilaksanakan setiap tahun.

Selain itu, kami juga mempunyai Opsyen Hasil dari penyata aliran tunai (lebih kurang)

Dengan ini, kita seharusnya dapat mencari harga mogok yang berkesan

Colgate 2013 - 10K, Halaman 53

Juga, perhatikan bahawa opsyen saham mempunyai syarat kontrak selama enam tahun dan terletak hak selama tiga tahun. Colgate 2013 - 10K, Halaman 69

Dengan data ini, kami mengisi data Opsyen seperti di bawah. Kami juga perhatikan bahawa harga mogok rata-rata wajaran opsyen saham untuk tahun 2013 adalah $ 42 dan jumlah pilihan yang dapat dilaksanakan ialah 24.151 juta Colgate 2013 - 10K, Halaman 70

Langkah 9E - Pilihan Saham: Cari Hasil Hasil

Dengan memasukkan nombor ini dalam data pilihan kami di bawah, kami perhatikan bahawa pilihan yang diperoleh adalah $ 1.014 bilion

Langkah 9F - Pilihan Stok: Ramalan Data Unit Stok Terhad

Sebagai tambahan kepada pilihan saham, terdapat Unit Stok Terhad yang diberikan kepada pekerja dengan jangka masa purata berwajaran 2.2 tahun Colgate 2013 - 10K, Halaman 81

Mengisi data ini dalam kumpulan data Pilihan Demi kesederhanaan, kami belum memproyeksikan penerbitan opsyen (saya tahu ini bukan anggapan yang tepat, namun, kerana kekurangan data, saya tidak akan mengambil penerbitan opsyen lagi. Kami baru saja mengambil ini adalah sifar seperti yang diserlahkan di kawasan kelabu di atas.Selain itu, unit stok terhad diunjurkan 2.0 juta ke depan.

Juga, lihat Kaedah Stok Perbendaharaan

Langkah 9G- Dividen: Ramalkan Dividen

Ramalkan anggaran dividen menggunakan nisbah Pembayaran Dividen
Membayar dividen tetap Pembayaran per saham
Dari laporan 10K, kami mengekstrak semua maklumat masa lalu mengenai dividen
Dengan maklumat dividen yang dibayar, kita dapat mengetahui nisbah pembayaran Dividen = Jumlah Dividen Dibayar / Pendapatan Bersih.
Saya telah mengira nisbah pembayaran dividen Colgate seperti yang dilihat di bawah - Kami perhatikan bahawa nisbah pembayaran dividen secara amnya berada dalam julat 50% -60%. Mari kita membuat andaian nisbah pembayaran Dividen 55% pada tahun-tahun mendatang.
Kami juga boleh menghubungkan Pendapatan Bersih yang diproyeksikan dari penyata Pendapatan
Dengan menggunakan nisbah Pendapatan Bersih yang diproyeksikan dan pembayaran dividen, kita dapat menemui Jumlah Dividen yang Dibayar

Langkah 8H - Ramalkan akaun ekuiti secara keseluruhan

Dengan ramalan pembelian semula saham, hasil opsyen dan dividen yang dibayar, kami siap untuk melengkapkan Jadual Ekuiti Pemegang Saham. Pautkan semua ini untuk mencari Baki Ekuiti Berakhir untuk setiap tahun seperti yang ditunjukkan di bawah.

Langkah 9I - Pautkan Tamat Ekuiti Pemegang Saham ke Kunci Kira-kira

Langkah 9J - Pautkan Dividen, Pembelian balik saham & Opsyen diteruskan ke CF

# 10 - Jadual Luar Biasa Saham

Langkah seterusnya dalam pemodelan kewangan dalam talian dalam latihan Excel ini adalah dengan melihat Jadual Pengambilan Saham. Ringkasan Jadual Tunggakan Saham

Saham Asas - sebenar dan purata
Tangkap kesan masa lalu pilihan dan penukaran yang sesuai
Saham Dilusi - purata
Saham Rujukan dibeli semula dan saham baru dari opsyen yang dilaksanakan
Hitung saham asas yang diramalkan (sebenar)
Hitung purata saham asas dan dicairkan
Rujuk bahagian yang diproyeksikan ke Penyata Pendapatan (ingat Penyata Pendapatan Membangun!)
Input sejarah berkongsi maklumat yang luar biasa
Catatan : Jadual ini biasanya disatukan dengan Jadual Ekuiti

Langkah 10A - Masukkan nombor sejarah dari laporan 10K

Saham yang dikeluarkan (realisasi opsyen sebenar) dan saham yang dibeli semula dapat dirujuk dari Jadual Ekuiti Pemegang Saham
Juga, input memberi wajaran jumlah saham rata-rata dan kesan pilihan saham untuk tahun-tahun sejarah.

Langkah 10B - Pautkan terbitan & belian balik saham dari Jadual Ekuiti Saham.

Saham Asas (Berakhir) = Saham Asas (Permulaan) + Terbitan Saham - Saham Dibeli Semula.

Langkah 10C - Cari saham purata wajaran asas,

kita menjumpai purata dua tahun seperti yang ditunjukkan di bawah.
Juga, tambahkan kesan opsyen & unit saham terhad (dirujuk dari jadual ekuiti pemegang saham) untuk mencari Saham Purata Wajaran Dilusi.

Langkah 10D - Pautkan saham berwajaran asas & dicairkan ke Penyata Pendapatan

Sekarang kita telah mengira saham rata-rata berwajaran yang dicairkan, inilah masanya untuk kita mengemas kini yang sama dalam Penyata Pendapatan.
Hubungkan saham rata-rata berwajaran dicairkan yang diperkirakan yang belum dijelaskan ke Penyata Pendapatan seperti yang ditunjukkan di bawah

Dengan ini, kami melengkapkan Jadual dan masa Shares Oustanding untuk beralih ke set penyata kami yang seterusnya.

# 11 - Melengkapkan Penyata Aliran Tunai

Penting bagi kita untuk melengkapkan penyata aliran tunai sepenuhnya sebelum beralih ke jadual seterusnya dan terakhir dalam Pemodelan Kewangan ini iaitu Jadual Hutang Sehingga tahap ini, hanya terdapat beberapa perkara yang tidak lengkap

Penyata Pendapatan - perbelanjaan faedah / pendapatan tidak lengkap pada peringkat ini
Lembaran Imbangan - tunai dan item hutang tidak lengkap pada peringkat ini

Langkah 11A - Kira Aliran Tunai untuk Aktiviti Pembiayaan

Juga, periksa Aliran Tunai dari Pembiayaan

Langkah 11B - Cari kenaikan (penurunan) bersih dalam Tunai & Setara Tunai

Langkah 11C = Lengkapkan penyata aliran tunai

Cari wang tunai & setara tunai akhir tahun pada akhir tahun.

Langkah 11D - Pautkan tunai & setara tunai dengan Kunci Kira-kira.

Sekarang kami bersedia untuk menguruskan jadual terakhir dan terakhir kami, iaitu Jadual Hutang dan Faedah

# 12- Pemodelan Kewangan di Excel - Jadual Hutang dan Faedah

Langkah seterusnya dalam Pemodelan Kewangan Dalam Talian ini adalah melengkapkan Jadual Hutang dan Faedah. Ringkasan Hutang dan Faedah - Jadual

Langkah 12A - Sediakan Jadual Hutang

Rujuk Aliran Tunai yang Ada untuk Pembiayaan
Rujuk semua sumber ekuiti dan penggunaan wang tunai

Langkah 12B - Hitung Aliran Tunai dari Pembayaran Balik Hutang

Rujuk Baki Tunai Permulaan dari Lembaran Imbangan
Kurangkan baki tunai minimum. Kami menganggap bahawa Colgate ingin menyimpan minimum $ 500 juta setiap tahun.

Langkau Penerbitan Hutang Jangka Panjang / Bayaran Balik, Tunai tersedia untuk bahagian Kemudahan Kredit Bergulir dan Revolver untuk sekarang Dari laporan 10K Colgate, kami perhatikan perincian yang tersedia mengenai Kemudahan Kredit Revolusi Colgate 2013 - 10K, Halaman 35

Juga disediakan dalam maklumat tambahan mengenai Hutang adalah pembayaran hutang jangka panjang yang komited. Colgate 2013 - 10K, Halaman 36

Langkah 12C - Hitung Hutang Jangka Panjang Berakhir

Kami menggunakan jadual pembayaran hutang jangka panjang yang disediakan di atas dan mengira Baki Akhir Pembayaran Hutang Jangka Panjang

Langkah 12D - Hubungkan pembayaran hutang jangka panjang.

Langkah 12E -Kira pinjaman / pembayaran budi bicara

Dengan menggunakan formula sapu tunai seperti yang ditunjukkan di bawah, hitung pinjaman / pembayaran mengikut budi bicara.

Langkah 12F - Hitung Belanja Faedah dari Hutang Jangka Panjang

Hitung baki purata untuk Kemudahan Kredit Bergulir dan Hutang Jangka Panjang
Buat andaian yang munasabah untuk kadar faedah berdasarkan maklumat yang diberikan dalam laporan 10K
Hitung Jumlah Belanja Faedah = baki hutang purata x kadar faedah

Cari Jumlah Belanja Faedah = Faedah (Kemudahan Kredit Berputar) + Faedah (Hutang Jangka Panjang)

Langkah 12G - Pautkan pengeluaran hutang & Revolver Utama ke Aliran Tunai

Langkah 12H - Rujukan Semasa dan Panjang untuk Neraca

Tunjukkan Bahagian Hutang Jangka Panjang dan hutang Jangka Panjang seperti yang ditunjukkan di bawah

Hubungkan Kemudahan Kredit Bergulir, Hutang Jangka Panjang dan Sebahagian Hutang Jangka Panjang Semasa ke Lembaran Imbangan

Langkah 12I - Hitung Pendapatan Faedah menggunakan baki tunai purata

Langkah 12J - Pautkan Beban Faedah dan Pendapatan Faedah dengan Penyata Pendapatan

Lakukan pemeriksaan Imbangan Duga: Jumlah Aset = Liabiliti + Ekuiti Pemegang Saham

Langkah 12K - Audit Kira-kira

Sekiranya terdapat percanggahan, kita perlu mengaudit model dan memeriksa sebarang kesalahan hubungan

Kursus Pemodelan Kewangan yang Disyorkan

Saya harap anda menikmati Panduan Excel Pemodelan Kewangan Percuma. Sekiranya anda ingin mempelajari Pemodelan Kewangan di Excel melalui kuliah video pakar kami, anda juga boleh melihat Latihan Perbankan Pelaburan kami. Ini adalah 99 kumpulan latihan Perbankan Pelaburan. Kursus ini bermula dari asas dan membawa anda ke tahap pekerjaan Perbankan Pelaburan yang lebih maju. Kursus ini terbahagi kepada 5 bahagian -

Bahagian 1 - Latihan Perbankan Pelaburan - Kursus Teras
(26 Kursus)
Bahagian 2 - Latihan Pemodelan Perbankan Pelaburan Lanjutan
(20 Kursus)
Bahagian 3 - Tambahan Perbankan Pelaburan
(13 Kursus)
Bahagian 4 - Kursus Asas Perbankan Pelaburan
(23 Kursus)
Bahagian 5 - Kemahiran Insaniah bagi Pelabur Pelaburan
(17 Kursus)

Muat turun Model Kewangan

Model Kewangan Alibaba
Model Kewangan IPO Kotak
Templat Pemodelan Kewangan
Kursus Pemodelan Kewangan Perbankan

Apa selepas ini?

Sekiranya anda mempelajari sesuatu yang baru atau menikmati Pemodelan Kewangan berasaskan Excel ini, sila tinggalkan komen di bawah. Beritahu saya pendapat anda. Banyak terima kasih dan jaga. Selamat Belajar!