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 dalam Excel terdapat di seluruh web. Telah banyak ditulis mengenai pembelajaran Pemodelan Kewangan; namun, sebahagian besar latihan pemodelan kewangan adalah sama. Ini melampaui kebiasaan biasa dan meneroka Pemodelan Kewangan praktikal seperti yang digunakan oleh Bankers Investasi 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 mempunyai lebih dari 6000 perkataan dan memerlukan tiga 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
Masukkan Alamat E-mel Dengan meneruskan langkah di atas, anda bersetuju dengan Syarat Penggunaan dan Dasar Privasi kami.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
- Bagaimana membina model kewangan?
- # 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 Model Pemodelan Kewangan, maka lihat 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. Sistem modular pada dasarnya bermaksud membina penyata teras seperti Penyata Pendapatan, Lembaran Imbangan, dan Aliran Tunai menggunakan modul / jadual yang berbeza. Fokus utamanya ialah menyiapkan setiap pernyataan langkah demi langkah dan menghubungkan semua program sokongan ke pernyataan inti setelah selesai. Saya dapat memahami bahawa ini mungkin tidak jelas sekarang; namun, anda akan menyedari bahawa ini sangat mudah semasa kita 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 yang berbeza adalah jadual susut nilai, jadual modal kerja, jadual tidak berwujud, jadual ekuiti pemegang saham, jadual item jangka panjang yang lain, jadual hutang, dll.
- Jadual yang berbeza dihubungkan dengan pernyataan utama setelah selesai.
- Dalam panduan pemodelan kewangan ini, kami akan membina model ekonomi 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 dimulakan 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 elemen atau Analisis Nisbah adalah sesuatu yang baru bagi anda, saya cadangkan 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 harus 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 mengenai penjualan.
Hasil Analisis Vertikal
- Margin Keuntungan meningkat sebanyak 240 mata asas dari 56.2% pada tahun 2007 kepada 58.6% pada tahun 2013. Ini disebabkan terutamanya oleh penurunan Kos Jualan
- Untung Operasi atau EBIT juga menunjukkan margin yang meningkat, 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% kerana peningkatan "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 kecenderungan dari masa ke masa dengan mengira peratusan kenaikan excel atau penurunan berbanding tahun asas. Ini menyediakan hubungan analitik antara akaun yang dikira pada tarikh yang berbeza menggunakan mata wang dengan kekuatan pembelian yang berbeza-beza. Sebenarnya, analisis ini mengindeks laporan dan membandingkan evolusi 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 kaedah tambahan.
Mari kita lihat analisis Horizontal Colgate. 
Hasil Analisis Mendatar
- Kami melihat bahawa Jualan Bersih meningkat sebanyak 2.0% pada tahun 2013.
- Perhatikan juga trend Kos Jualan. Kami 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 yang 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 penting daripada 1.0 untuk semua tahun. Ini menunjukkan bahawa aset semasa lebih penting daripada liabiliti semasa, dan mungkin Colgate mempunyai kecairan yang mencukupi.
- Nisbah cepat Colgate berada dalam julat 0.6-0.7; ini bermaksud bahawa sekuriti Colgates Cash dan Marketable dapat membayar sebanyak 70% daripada liabiliti semasa. Ini kelihatan seperti situasi yang baik untuk Colgate.
- Kitaran Pungutan Tunai telah menurun dari 43 hari pada tahun 2009 menjadi 39 hari pada tahun 2013. Ini terutama disebabkan oleh penurunan dalam tempoh kutipan penghutang.
Juga, lihat artikel terperinci ini mengenai Kitaran Penukaran Tunai.
Langkah 2D - Nisbah Keuntungan Operasi Colgate
Keuntungan nisbah 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 hampir 100%, yang menunjukkan pulangan yang sangat baik 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. Kadar 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, menunjukkan risiko yang lebih rendah daripada Bayaran Faedah Bayar.
# 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 dirancang dengan baik dan logik yang menggambarkan jenis dan jumlah aliran pendapatan dengan tepat sangat penting. Ada banyak cara untuk membuat 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 dalam Volume, Harga Purata, dan Perubahan Harga: Kaedah ini sesuai untuk perniagaan yang mempunyai gabungan 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 penting. (Untuk syarikat dengan strategi pengurangan harga, misalnya, atau pemain khusus harga premium yang terbaik) contohnya, pasaran kereta Mewah
- Kapasiti Isipadu, Kadar Penggunaan Kapasiti, dan Harga Purata: Andaian ini boleh menjadi penting bagi perniagaan di mana kapasiti pengeluaran sangat penting untuk keputusan. (Dalam pembelian kapasiti tambahan, misalnya, atau untuk menentukan apakah pengembangan itu memerlukan pelaburan baru.)
- Ketersediaan dan Harga Produk
- Pendapatan didorong oleh pelaburan dalam 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 terpasang adalah kunci (penambahan baru ke lantai, penurunan di tanah, 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 ditagih). 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 informasi 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 fitur tersebut, kami akan memproyeksikan penjualan Colgate pada masa akan datang berdasarkan data yang tersedia ini. Kami akan menggunakan pendekatan pertumbuhan penjualan di seluruh segmen untuk mendapatkan ramalan. Sila lihat gambar di bawah. Kami telah mengira kadar pertumbuhan tahun ke tahun untuk setiap elemen. 
Sekarang kita dapat mengira peratusan pertumbuhan penjualan berdasarkan tren sejarah dan memproyeksikan pendapatan di bawah setiap bahagian. Jumlah penjualan Bersih adalah jumlah keseluruhan Segmen Pemakanan Mulut, Peribadi & Penjagaan 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 peratusan pendapatan dan susut nilai dari jadual yang berbeza: 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 lima tahun terakhir. Kita dapat melihat ramalan margin berdasarkan ini.
- Begitu juga, Perbelanjaan Jual, Umum & Pentadbiran secara historis berada dalam julat 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 menghitung Susutnilai 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 di modal kerja. Kami akan menangani hutang dan tunai serta 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 menonjol biasanya dianggap pada jumlah 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
- Susutnilai 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. Juga, perhatikan bahawa angka di bawah 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 amalan yang biasa dilakukan 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- Rujukan Maklumat Lalu
- Kami akan menggunakan Baki Akhir untuk PPE = Baki permulaan + Capex - Susut nilai - Penyesuaian untuk Penjualan Aset (persamaan BASE)
- Adalah 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 malah 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 pecahan 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 mengira susut nilai Tanah kerana tanah bukan aset yang boleh disusut nilai
- Untuk menganggarkan susut nilai dari peningkatan Bangunan, pertama-tama kita menggunakan struktur di bawah ini.
- Susut nilai di sini terbahagi kepada dua bahagian - 1) susut nilai dari Aset Penambahbaikan Bangunan yang telah disenaraikan di kunci kira-kira, 2) susut nilai dari peningkatan Bangunan pada masa hadapan.
- Untuk mengira susut nilai dari peningkatan bangunan yang disenaraikan pada aset, kami menggunakan kaedah susut nilai Straight Line Method yang mudah.
- Untuk mengira susut nilai masa depan, pertama kali kita menukar Capex menggunakan Fungsi TRANSPOSE di Excel.
- Kami mengira susut nilai dari sumbangan aset dari setiap tahun.
- Juga, susut nilai tahun pertama dibahagi dua kerana kami menganggap konvensyen pertengahan tahun untuk penggunaan aset.
Jumlah Susut 
Nilai Peningkatan 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 susutnilai 1) Peralatan & Mesin Pembuatan dan 2) Peralatan lain sebagai ditunjukkan di bawah.
Jenis peralatan lain
Jumlah Susut Nilai Colgate = Susut Nilai (Peningkatan Bangunan) + Susut Nilai (Mesin & Peralatan) + Susut nilai (peralatan tambahan) 
Setelah kita mengetahui angka susut nilai sebenar, kita dapat memasukkannya ke dalam persamaan BASE seperti yang ditunjukkan di bawah
- Dengan ini, kami memperoleh angka PP&E 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, yang juga akan kita lakukan dalam kes kita.
Langkah 6B - Ramalan Aset Tidak Ketara Lain
- Seperti yang dinyatakan dalam Laporan 10K Colgate, sebahagian besar jangka hayat tak terbatas berkaitan dengan pemerolehan Sanex
- "Penambahan pada Tak Berwujud" juga rumit untuk diproyeksikan
- Laporan 10K Colgate memberi kami perincian perbelanjaan pelunasan lima tahun akan datang.
- Kami akan menggunakan anggaran ini dalam Model Kewangan
Colgate 2013 - 10K, Halaman 61 kami
Langkah 6C - Intangibles 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 ketika kita mempersiapkan "sisa" yang tidak mempunyai pendorong khusus untuk ramalan. Dalam kes Colgate, Item Jangka Panjang (sisa) lain adalah Pajak Pendapatan Tertunda (liabiliti dan aset), Pelaburan lain, dan liabiliti lain.
Langkah 7A - Rujuk data sejarah dari Imbangan Duga
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 Lembaran Imbangan
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 akan kembali dan melihat kembali 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 tahap ini, anggap bahawa jumlah saham utama dan dilusi yang akan datang akan tetap sama seperti pada tahun 2013. 
Langkah 8B - Hitung 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 yang berkaitan dengan ekuiti seperti Ekuiti, Dividen, Pembelian Saham, Hasil Opsyen, dll 
. Laporan Colgate 10K memberi kami perincian aktiviti saham biasa dan saham perbendaharaan dalam beberapa 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 semula 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 Pembelian 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 ingin mereka beli semula. Satu-satunya maklumat yang dilaporkan oleh 10K laporan mereka adalah bahawa mereka telah membenarkan pembelian semula sehingga 50 juta saham. 
Colgate 2013 - 10K, Halaman 35
- Untuk mengetahui jumlah saham yang dibeli semula, kita perlu menganggap Jumlah Pembelian Balik Saham. Berdasarkan jumlah pembelian semula sejarah, saya telah mengambil jumlah ini seperti $ 1,500 juta untuk semua tahun akan datang.
- Untuk mengetahui jumlah saham yang dibeli semula, kami memerlukan unjuran harga saham tersirat dari potensi pembelian semula.
- Harga saham sebenar = andaian E multiplex PE.
- Masa depan membeli balik PE boleh diandaikan berdasarkan trend sejarah. Kami perhatikan bahawa Colgate telah membeli semula 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 berada pada gandaan PE 19x.
- Dengan menggunakan PE 19x, kita dapat mencari harga tersirat = EPS x 19
- Sekarang kita telah menemui harga tersirat, kita dapat melihat 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 Pilihan seperti di bawah. Kami juga perhatikan bahawa harga mogok rata-rata wajaran opsyen saham untuk tahun 2013 adalah $ 42 dan jumlah opsyen 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 Saham: 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 meneruskan lagi isu pilihan. Kami baru saja mengambilnya sebagai sifar, seperti yang diserlahkan di kawasan kelabu di atas.Selain itu, unit stok terhad diunjurkan menjadi 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 anggap 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 mencari Jumlah Dividen yang Dibayar.
Langkah 8H - Ramalkan akaun ekuiti secara keseluruhan
Dengan ramalan pembelian balik saham, hasil opsyen, dan dividen yang dibayar, kami siap untuk melengkapkan Jadual Ekuiti Pemegang Saham. Hubungkan 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 Opsyen & 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 peratusan mentah yang diramalkan (sebenar)
- Hitung purata saham asas dan dicairkan
- Rujuk saham 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
- Input tersebut 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.
- Tautkan saham rata-rata berwajaran dicairkan yang diperkirakan terkumpul 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 ada 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 - Hitung 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
Gred 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 Pengeluaran / Bayaran Balik Hutang Jangka Panjang, Tunai tersedia untuk bahagian Kemudahan Kredit Bergulir dan bahagian Revolver untuk sekarang 
Dari laporan 10K Colgate; kami perhatikan perincian yang tersedia di Revolved Credit Facility 
Colgate 2013 - 10K, Halaman 35
Juga terdapat 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 / playdown 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 = Bunga (Kemudahan Kredit Berputar) + Faedah (Hutang Jangka Panjang) 
Langkah 12G - Menghubungkan pengeluaran hutang & Revolver ke Aliran Tunai
Langkah 12H - Rujukan Semasa dan Panjang untuk Neraca
- Tunjukkan Bahagian Hutang Jangka Panjang dan Hutang Jangka Panjang Semasa seperti di bawah
- Hubungkan Kemudahan Kredit Berputar, Hutang Jangka Panjang, dan Sebilangan 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 terdiri daripada 99 kursus kumpulan latihan Perbankan Pelaburan. Kursus ini dimulakan dengan asas-asas dan membawa anda ke peringkat Advanced Banking Job. Kursus ini terbahagi kepada lima 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. Terima kasih banyak, dan jaga. Selamat Belajar!
