Cara Membuat Grafik Break Even di Excel

Analisis break-even (impas) adalah alat untuk mengevaluasi peluang laba suatu model bisnis dan berbagai strategi penetapan harga. Anda bisa memperoleh biaya tetap, biaya variabel, dan opsi penetapan harga di Excel untuk menentukan titik impas (break-even) suatu produk. Titik impas merupakan jumlah unit yang perlu dijual pada harga yang ditetapkan supaya impas (menutupi biaya produksi).

Bagian 1

Bagian 1 dari 5:

Membuat Tabel Biaya Variabel

Unduh PDF

Anda membutuhkan beberapa kertas kerja (worksheet) di buku kerja untuk memantau semua biaya Anda.
Dengan demikian, kertas kerja baru akan terbuka.
Kertas kerja ini akan menampung semua tabel yang memantau semua biaya variabel, misalnya biaya pengiriman, komisi, dan lain-lain.
Untuk membuat tabel biaya variabel standar, masukkan “Deskripsi” ke sel A1 dan “Jumlah” ke sel B1.
Di bawah judul “Deskripsi”, masukkan jenis biaya variabel produk yang akan ditangani.
Kolom ini nantinya akan diisi dengan biaya yang benar-benar terjadi selama proses.
7
Buat tabel dari data yang dimasukkan. Ubah data menjadi tabel supaya Anda bisa memasukkannya menjadi rumus dengan mudah:
- Pilih semua data, termasuk baris judul dan sel kosong, dengan mengeklik dan menggeser kursor tetikus menyeberangi semua sel.
- Klik tombol "Format as Table". Anda akan menemukannya di label Home. Jika Anda menggunakan Excel untuk Mac, klik label Tables, klik tombol "New", dan pilih "Insert Table with Headers".
- Centang kotak "My table has headers". Dengan demikian, label baris pertama akan ditetapkan sebagai label judul.
- Klik kotak "Table Name" di sudut kanan atas dan ubah namanya menjadi "BiayaVariabel"
Iklan

Bagian 2

Bagian 2 dari 5:

Membuat Tabel Biaya Tetap

Unduh PDF

Dengan demikian, kertas kerja baru akan terbuka.
Kertas kerja ini akan menampung semua biaya tetap produk, misalnya biaya sewa, asuransi, dan biaya lain yang tidak berubah.
Layaknya kertas kerja Biaya Variabel, buat label "Deskripsi" di sel A1 dan Label “Jumlah” di sel B1.
Isi kolom pertama dengan deskripsi biaya tetap, misalnya “Sewa”.
Anda akan mengisi biaya-biaya ini setelah menyelesaikan seluruh kertas kerja.
6
Buatlah tabel dari data yang telah dimasukkan. Pilih semua yang telah Anda buat di kertas kerja ini, termasuk judul:
- Klik tombol "Format as Table" di label Home.
- Cek "My table has headers" untuk mengubah baris 1 menjadi judul tabel.
- Klik kotak "Table Name" dan masukkan nama tabel "BiayaTetap"
Iklan

Bagian 3

Bagian 3 dari 5:

Membuat Kertas Kerja Break-Even

Unduh PDF

Kertas kerja ini akan menampung grafik BEP (Break Even Point) utama Anda. Anda tidak harus mengubah namanya menjadi “BEP”, tetapi pekerjaan Anda akan lebih mudah jika melakukannya.
2
Atur tata letak untuk kertas kerja break-even. Demi contoh ini, buat kertas kerja menggunakan tata letak berikut:
- A1: Penjualan - Inilah label untuk bagian Penjualan di kertas kerja.
- B2: Harga Per Unit – Inilah harga yang dikenakan untuk setiap barang yang dijual.
- B3: Unit Terjual – Inilah jumlah unit yang dijual pada harga yang ditetapkan dalam rentang waktu tertentu.
- A4: Biaya – Inilah label untuk bagian Biaya dalam kertas kerja.
- B5: Biaya Variabel – Inilah biaya produk yang dapat Anda kendalikan (pengiriman, tingkat komisi, dll.)
- B6: Biaya Tetap – Inilah biaya produk yang tidak dapat Anda kontrol (sewa fasilitas,asuransi, dll.)
- A7: Pendapatan – Inilah jumlah uang yang dihasilkan dari penjualan produk sebelum dikurangi biaya.
- B8: Margin Unit – Inilah jumlah uang yang dihasilkan per unit setelah dikurangi biaya.
- B9: Margin Laba Kotor – Inilah jumlah total uang yang dihasilkan dari penjualan unit produk setelah dikurangi biaya.
- A10: BEP – Inilah label untuk bagian titik break-even di kertas kerja.
- B11: Unit – Inilah jumlah unit yang perlu dijual untuk menutupi biaya produksi.
3
Ganti format numerik untuk sel keluaran (output) dan masukan (input). Anda perlu mengubah format numerik untuk sel tertentu supaya data bisa muncul dengan benar:
- Sorot C2, C5, C6, C8, dan C9. Klik menu tarik turun di bagian “Number” pada label Home dan pilih "Currency" (mata uang).
- Sorot C3 dan C11. Klik menu tarik turun dan pilih "More number formats". Pilih "Number" dan atur "Decimal places" (koma desimal) menjadi "0".
4
Buat rentang untuk digunakan dalam rumus. Pilih dan buat rentang berikut agar rumus dapat berfungsi. Dengan demikian, variabel bisa dibuat untuk dimasukkan ke dalam rumus sehingga Anda bisa melacak dan memperbarui nilai-nilainya dengan mudah.
- Pilih B2:C3 lalu klik label "Formulas". Klik "Create from selection" dan lanjutkan dengan "OK”.
- Pilih B5:C6 lalu klik label "Formulas". Klik "Create from selection" dan lanjutkan dengan "OK”.
- Pilih B8:C9 lalu klik label "Formulas". Klik "Create from selection" dan lanjutkan dengan "OK”.
- Pilih B11:C11 lalu klik label "Formulas". Klik "Create from selection" dan lanjutkan dengan "OK".
Iklan

Bagian 4

Bagian 4 dari 5:

Memasukkan Rumus

Unduh PDF

1
Masukkan rumus biaya variabel. Rumus ini akan menghitung total biaya variabel untuk barang yang dijual. Klik C5 dan masukkan rumus berikut:
- =SUM(Biaya Variabel)*Unit_Terjual
2
Masukkan biaya tetap ke rumus. Excel akan menghitung total biaya tetap produk Anda. Klik C6 dan masukkan rumus berikut:
- =SUM(BiayaTetap)
3
Masukkan rumus margin unit. Rumus ini akan menghitung margin yang dihasilkan setelah menyertakan biaya variabel. Klik C8 dan masukkan rumus berikut:
- =Harga_Per_Unit-SUM(BiayaVariabel )
4
Masukkan rumus margin laba kotor. Rumus ini menentukan jumlah laba total untuk semua unit setelah dikurangi biaya variabel. Klik C9 dan masukkan rumus berikut:
- =Margin_Unit*Unit_Terjual
5
Masukkan rumus BEP. Rumus ini menggunakan biaya tetap dan membandingkannya dengan margin sehingga Anda mengetahui banyaknya unit perlu dijual supaya bisa impas. Klik C11 dan masukkan rumus berikut:
- =IFERROR(Biaya_Tetap/Margin_Unit,0)
Iklan

Bagian 5

Bagian 5 dari 5:

Menentukan Titik Break Even

Unduh PDF

1
Masukkan biaya variabel bisnis Anda. Kembali ke tabel BiayaVariabel dan isi semua biaya yang berhubungan dengan produk. Semakin akurat biaya yang didaftarkan, semakin akurat pula perhitungan yang akan dilakukan.
- Setiap biaya di tabel BiayaVariabel harus berupa per unit dijual.
Masukkan biaya-biaya ini ke tabel biaya tetap. Inilah biaya untuk menjalankan bisnis, semuanya ditetapkan dalam interval yang sama (misalnya biaya bulanan).
Dalam kertas kerja BEP, masukkan perkiraan harga per unit. Anda akan bisa menyesuaikannya seiiring berjalannya perhitungan.
Inilah angka penjualan yang diinginkan dalam rentang waktu sama dengan Biaya Tetap. Sebagai contoh, jika biaya tetap Anda termasuk sewa bulanan dan asuransi, Unit Terjual adalah angka penjualan unit selama rentang waktu yang sama.
Sel keluaran Unit (C11) akan menampilkan jumlah unit yang akan perlu dijual supaya mencapai impas. Angka ini akan berubah tergantung pada harga Per Unit serta tabel Biaya Variabel dan Biaya Tetap Anda.
Mengubah Harga Per Unit akan turut mengubah jumlah unit yang diperlukan untuk impas. Coba ganti harga jual unit dan lihat dampaknya terhadap nilai BEP. ^{[1]XTeliti sumber}
Iklan

Tips

Meningkatkan harga memungkinkan usaha bisa mencapai titik break-even dengan volume penjualan yang lebih rendah, tetapi hal ini bisa menyebabkan penjualan total menurun.