Cara Menghitung Forecasting Dengan Excel

Pada kesempatan kali ini, kita akan membahas beberapa tutorial mengenai cara menghitung beberapa hal di Excel. Mulai dari cara menghitung Weighted Moving Average, Payback Period, Forecast Penjualan, Regresi, dan membuat rumus. Semua tutorial ini sangat berguna untuk membantu pekerjaan Anda dalam membuat laporan maupun analisis data.

1. Cara Menghitung Weighted Moving Average di Excel

Weighted Moving Average (WMA) merupakan suatu metode peramalan yang digunakan untuk memperkirakan data masa depan dengan menggunakan data masa lalu. Cara menghitung WMA ini sangat mudah dan efektif.

Contoh Soal

Diketahui suatu data penjualan produk dalam 5 bulan terakhir sebagai berikut:

Bulan	Penjualan
1	1000
2	1200
3	1100
4	1300
5	1250

Langkah-langkah

Berikut adalah langkah-langkah untuk menghitung WMA:

1. Tentukan jumlah periode yang akan digunakan, misalnya 3.
2. Hitung bobot (weight) yang akan digunakan, misalnya 0.6 untuk periode terakhir, 0.3 untuk periode kedua terakhir, dan 0.1 untuk periode ketiga terakhir. Pastikan jumlah bobot sama dengan 1.
3. Mulai dari periode ke-(n+1), hitung WMA dengan rumus:
   WMA = (Xn * Wn) + (Xn-1 * Wn-1) + … + (Xn-n * Wn-n)

Berikut adalah hasil perhitungan WMA untuk contoh soal diatas:

Bulan	Penjualan	Bobot	Weighted Moving Average
1	1000
2	1200
3	1100
4	1300	0.6	(1300*0.6) + (1100*0.3) + (1200*0.1) = 1230
5	1250	0.6	(1250*0.6) + (1300*0.3) + (1100*0.1) = 1245

2. Cara Menghitung Payback Period dengan Excel

Payback Period merupakan suatu metode perhitungan pengembalian modal (return on investment) dalam suatu proyek. Metode ini menghitung berapa lama waktu yang dibutuhkan untuk mendapatkan kembali modal yang telah dikeluarkan.

Contoh Soal

Sebuah proyek akan mengeluarkan biaya sebesar Rp 100.000.000 dan memperoleh pendapatan sebesar Rp 30.000.000 per tahun selama 5 tahun. Berapa lama waktu yang dibutuhkan untuk mendapatkan kembali modal?

Langkah-langkah

Berikut adalah langkah-langkah untuk menghitung Payback Period:

1. Tentukan nilai arus kas (cash flow) dari tahun ke-n.
2. Hitung net cash inflow, yaitu selisih antara total arus kas masuk dan total arus kas keluar pada setiap periode.
3. Buat tabel yang menunjukkan kumulatif net cash inflow dari tahun ke-1 hingga tahun ke-n.
4. Cari tahun ke-n dimana total kumulatif net cash inflow bernilai positif, lalu hitung payback period dengan rumus:
   Payback Period = Tahun sebelumnya + (Total kumulatif net cash inflow tahun ke-n / Cash inflow tahun ke-n)

Berikut adalah hasil perhitungan Payback Period untuk contoh soal diatas:

Tahun	Cash Inflow	Cash Outflow	Net Cash Inflow	Kumulatif Net Cash Inflow
1	30000000	100000000	-70000000	-70000000
2	30000000		30000000	-40000000
3	30000000		30000000	-10000000
4	30000000		30000000	20000000
5	30000000		30000000	50000000

Kumulatif net cash inflow bernilai positif pada tahun ke-4, maka:

Payback Period = 3 + (20000000 / 30000000) = 3.67 tahun

3. Cara Menghitung Forecast Penjualan dengan Excel

Forecast Penjualan merupakan suatu metode peramalan penjualan produk atau jasa di masa depan berdasarkan data historis penjualan di masa lalu.

Contoh Soal

Diketahui suatu data penjualan produk dalam 4 bulan terakhir sebagai berikut:

Bulan	Penjualan
1	1000
2	1200
3	1100
4	1300

Hitunglah forecast penjualan untuk bulan ke-5 menggunakan metode Simple Moving Average (SMA) dengan periode 3.

Langkah-langkah

Berikut adalah langkah-langkah untuk menghitung Forecast Penjualan:

1. Tentukan jumlah periode yang akan digunakan, misalnya 3.
2. Hitung nilai rata-rata dari data tersebut pada setiap periode, misalnya pada periode ke-3 dengan rumus:
   SMA = (Xn + Xn-1 + Xn-2) / 3
3. Gunakan nilai SMA tersebut sebagai forecast penjualan pada periode berikutnya.

Berikut adalah hasil perhitungan Forecast Penjualan untuk contoh soal diatas:

Bulan	Penjualan	Simple Moving Average
1	1000
2	1200
3	1100	1100
4	1300	1200
5		(1100 + 1200 + 1300) / 3 = 1200

4. Cara Menambahkan Regresi Linear ke Grafik Excel

Regresi Linear merupakan suatu metode analisis data yang digunakan untuk mengetahui hubungan linear antara dua variabel. Dalam Excel, kita dapat menambahkan regresi linear ke dalam grafik kita untuk memudahkan pengamatan terhadap trend data tersebut.

Langkah-langkah

Berikut adalah langkah-langkah untuk menambahkan regresi linear ke dalam grafik:

1. Buat grafik yang menunjukkan hubungan antara dua variabel.
2. Klik kanan pada salah satu data di grafik, lalu pilih “Add Trendline”.
3. Pilih jenis “Linear” pada tab “Trendline Options”.
4. Ceklis “Display Equation on chart” dan/atau “Display R-squared value on chart” untuk menampilkan persamaan regresi dan/atau nilai R-squared pada grafik.
5. Klik OK.

Berikut adalah salah satu video tutorial mengenai Cara Menambahkan Regresi Linear ke Grafik Excel:

5. Cara Membuat Rumus Excel

Rumus Excel merupakan serangkaian instruksi yang digunakan untuk menghitung nilai dalam sel atau range sel. Dengan menggunakan rumus Excel, pemrosesan data dapat dilakukan secara otomatis dalam jumlah yang besar dan efisien.

Contoh Soal

Diketahui suatu data gaji karyawan pada sebuah perusahaan sebagai berikut:

Nama Karyawan	Gaji Bulanan (Rp)	Tunjangan Kesehatan (%)	Pajak Penghasilan (%)
Alice	7000000	5	10
Bob	5000000	2	5
Charlie	8000000	3	7
David	6000000	4	8

Hitunglah gaji bersih karyawan sesuai dengan data tersebut.

Langkah-langkah

Berikut adalah langkah-langkah untuk menghitung gaji bersih karyawan:

1. Hitung tunjangan kesehatan dengan rumus:
   Tunjangan Kesehatan = Gaji Bulanan * (Tunjangan Kesehatan / 100)
2. Hitung pajak penghasilan dengan rumus:
   Pajak Penghasilan = Gaji Bulanan * (Pajak Penghasilan / 100)
3. Hitung gaji bersih karyawan dengan rumus:
   Gaji Bersih = Gaji Bulanan + Tunjangan Kesehatan – Pajak Penghasilan

Berikut adalah hasil perhitungan gaji bersih karyawan untuk contoh soal diatas:

Nama Karyawan	Gaji Bulanan (Rp)	Tunjangan Kesehatan (%)	Pajak Penghasilan (%)	Tunjangan Kesehatan (Rp)	Pajak Penghasilan (Rp)	Gaji Bersih (Rp)
Alice	7000000	5	10