Category: Project Management

2004) Bagian 3

Referensi	:	[GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) [XLS-PMG-07]: Menghitung upah lembur dengan excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Bagian 2
Platform	:	Microsoft Excel
Download	:	Download

Pada tulisan sebelumnya telah diuraikan tahapan untuk menghitung lembur dengan kondisi 5 (lima) hari kerja/minggu dan 6 (enam) hari kerja/minggu dengan input jam kerja yang sudah diketahui. Pada tulisan ini akan saya sharing template yang pernah saya buat untuk aplikasi di suatu proyek dengan kondisi yang lebih komplek yaitu:

1. Data yang dimasukkan adalah [Jam Masuk] dan [Jam Keluar].
2. Jam Istirahat bisa diatur fleksibel atau bisa dirubah.
3. Perhitungan lima(5) hari kerja, jika karyawan bekerja di kantor pusat dan menggunakan perhitungan 6 (enam) hari kerja jika bekerja di lapangan
4. Slip gaji sudah dilengkapi dengan perhitungan BPJS dan Pajak Penghasilan (PPH21).

Berikut tutorial cara penggunaan spreadsheet excel yang sudah di download:

1. Memasukkan Setting Hari Libur di Sheet [hr_libur]

Masukkan tanggal hari libur di kolom [tgl] atau kolom [A] dengan format tanggal yang sesuai dengan settingan di windows. Contoh setting tanggal di komputer saya yang terlihat di task bar windows adalah , maka untuk tanggal 30 Mei 2018 dimasukkan dengan format 5/30/2018 atau 5/30/18.

Setelah memasukkan tanggal sesuai dengan format yang benar, masukkan juga keterangan hari libur di kolom [Keterangan]. Bagian kolom [No] tidak perlu diisi karena kolom ini akan terisi otomatis tiap ada penambahan record atau dta baru.

2. Memasukkan Setting Jam istirahat di sheet [jm_istrht]

Kolom [No], [Hari] dan [Kode Hari] berisi formula. Input jam kerja dimasukkan di kolom:

[Rest01 Start], [Rest01 End] : untuk jam istirahat pertama

[Rest02 Start1], [Rest02 End] : untuk jam istirahat kedua

[Rest03 Start], [Rest03 End] : untuk jam istirahat ketiga

Jam dimasukkan dengan format hh:mm, dalam format ini jika akan memasukkan jam 1 siang atau 1:00 PM maka jam dimasukkan dengan format 13:00 demikain juga untuk jam 11:00 pm dimasukkan dengan format 23:00.

Format hh:mm ini juga berlaku saat pengisian jam masuk kerja dan jam keluar kerja.

3. Memasukkan Isian di sheet [ts] dan [slip]

Jika diinginkan format tanpa detail perhitungan gaji, dapat mengunakan format di sheet [ts] sedangkan jika diinginkan input jam kerja disertai dengan detail perhitungan gaji dapat menggunakan format di sheet [slip].

Pada prakteknya di lapangan, saya menggunakan 2 (dua) sheet tersebut untuk aplikasi yang berbeda. Sheet [ts] diberikan ke karyawan digunakan untuk mendapatkan approval dari atasan, sedangkan sheet [slip] digunakan untuk menghitung gaji atau sebagai slip gaji.

Masing-masing sheet [ts] dan [slip] terdapat kolom [Q]:[AE] yang berisi rumus bantu untuk perhitungan timesheet, Anda bisa hide kolom ini saat pencetakan atau distribusi form ke karyawan. Jangan dihapus atau dimodifikasi rumus ini, kecuali Anda benar-benar memahami isi dan maksud dari rumus tersebut.

Format sheet [ts]:

Isikan ID, Nama dan Posisi atau bila diperlukan bisa ditambahkan nama supervisi, department atau isian lain pada area K2:O3

Pilih Bulan dan Tahun di cell [D2] dan [E2], maka kolom [Tgl] dan [Hari] akan terisi otomatis, kolom berwarna hijau berisi formula, sehingga tidak perlu diisi kolom hijau akan otomatis terupdate jika ada perubahan tanggal termasuk perubahan warna huruf jika tanggal bertepatan dengan hari libur, jumat, sabtu dan minggu.

Kolom yang perlu diisi manual adalah:

Nama Kolom	Keterangan
Kode Hari Kerja	Masukkan angka 5 jika bekerja untuk 5 hari kerja/minggu dan masukkan angka 6 jika untuk bekerja 6 hari kerja / minggu
Masuk , Keluar	Isikan jam masuk, jam keluar sesuai dengan format tanggal di windows
	Kolom [Durasi] akan terisi otomatis jika jam masuk dan jam keluar sudah terisi. Format durasi adalah dalam satuan desima sehingga jika durasinya adalah 8 jam 30 menit, maka masukkan angka 8.5
Koreksi	Isikan koreksi jam kerja jika diperlukan dalam satuan desimal. Misalnya dalam ada koreksi penambahan jam setengan jam atau 00:30 maka dimasukkan 0.5 atau sebaliknya ada pengurang jam 00:30, maka dimasukkan -0.5

Sedangkan tampilan untuk sheet [slip]:

Ada tambahan input yaitu:

Nama Input	Keterangan
Gaji Dasar	Masukkan gaji pokok atau gaji dasar /bulan
Tunjangan Tetap	Masukkan Tunjangan tetap /bulan
Rate Lembur	Masukkan unit rate lembur / jam. *“maaf tulisan di template masih /bulan, mohon untuk dikoreksi /jam”*
Tunj. Makan	Masukkan tunjangan makan /hadir
Tunj. Transport	Masukkan tunjangan transport /hadir
Tunj. Kehadiran	Masukkan tunjangan kehadiran /hadir
PTKP Karyawan	Masukkan golongan PTKP (Penghasilan Tidak Kena Pajak) karyawan sesuai dengan peraturan yang berlaku.

Detail perhitungan gaji disertai dengan perhitungan BPJS dan PPH21 adalah:

Silakan dicoba dan mohon koreksinya jika ada kesalahan.

Terimakasih telah berkunjung di blog saya.

[XLS-PMG-07]: Menghitung upah lembur dengan excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Bagian 2 (Revisi 10 April 2014)

Referensi	:	[GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004)
Platform	:	Excel 2007
Lokasi File	:

Pada posting [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) telah diuraikan cara menghitung lembur atau menentukan faktor pengali lembur untuk 5 hari kerja. Kali ini akan diuraikan cara menghitung faktor pengali lembur untuk 6 hari kerja. Hitungan kali ini memasukkan hari kerja adalah Senin s.d Sabtu dengan asumsi hari Sabtu adalah hari pendek. Jam kerja normal senin sampai sabtu adalah 7 jam, sedangkan untuk hari pendek adalah 5 jam. Kutipan dari Keputusan Menteri Pasal 11 :

Cara perhitungan upah kerja lembur sebagai berikut :
a. Apabila kerja lembur dilakukan pada hari kerja : a.1. untuk jam kerja lembur pertama harus dibayar upah sebesar 1,5 (satu setengah) kali upah sejam; a.2. untuk setiap jam kerja lembur berikutnya harus dibayar upah sebesar 2(dua) kali upah sejam.
b. Apabila kerja lembur dilakukan pada hari istirahat mingguan dan/atau hari libur resmi untuk waktu kerja 6 (enam) hari kerja 40 (empat puluh) jam seminggu maka : b.1. perhitungan upah kerja lembur untuk 7 (tujuh) jam pertama dibayar 2 (dua) kali upah sejam, dan jam kedelapan dibayar 3 (tiga) kali upah sejam dan jam lembur kesembilan dan kesepuluh dibayar 4 (empat) kali upah sejam. b.2. apabila hari libur resmi jatuh pada hari kerja terpendek perhitungan upah lembur 5 (lima) jam pertama dibayar 2 (dua) kali upah sejam, jam keenam 3(tiga) kali upah sejam dan jam lembur ketujuh dan kedelapan 4 (empat) kali upah sejam. c. Apabila kerja lembur dilakukan pada hari istirahat mingguan dan/atau hari libur resmi untuk waktu kerja 5 (lima) hari kerja dan 40 (empat puluh) jam seminggu, maka perhitungan upah kerja lembur untuk 8 (delapan) jam pertama dibayar 2 (dua) kali upah sejam, jam kesembilan dibayar 3(tiga) kali upah sejam dan jam kesepuluh dan kesebelas 4 (empat) kali upah sejam.

ayat a, b atau text berwarna biru adalah sebagai dasar untuk perhitungan lembur. Seperti posting sebelumnya [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004), list hari libur dibuat terlebih dahulu. List hari libur di posting kali ini menggunakan named range dengan type dynamic, bukan static range yang telah dijelaskan sebelumnya. Static Named range mengharuskan kita untuk memlilih range terlebih dahulu sebelum mendefinisikan range, dimensi range (records) akan bertambah jika penambahan data ada di tengah static range atau dengan cara insert rows. Sedangkan Dynamic Named Range, range akan otomatis berubah dimensinya atau bertambah data secara otomatis jika record ditambahkan ataupun diiinsert. Tahapan-tahapan yang dibuat di bawah dikerjakan engan Excel 2007, jika Anda menggunakan excel versi lain, silahkan disesuaikan. Tahapan pembuatan dynamic range adalah :

Buat list daftar hari libur di sheet tersendiri, misalnya daftarnya sebagai berikut :

Pada menu group [Formula], pilih [Define Name]

Masukkan nama range, misal "_LiburNasional" Refers to: =OFFSET($B$1,1,-1,COUNT(A:A),1) maksud dari rumus di atas adalah :— membuat range dengan acuan $B$1, turun 1 baris, kemudian ke kiri 1 kolom. Hasil dari perintah ini adalah $B$2 — dilanjutkan dari B2, membuat range dengan tinggi sebanyak angka atau tanggal di kolom A:A atau COUNT(A:A) dengan lebar 1 kolom. Jika tanggal hari libur di kolom A ada 20 tanggal, maka akhir dari perintah ini adalah $B$2:$B$20

Buat tabel kode hari kerja yaitu kode hari yang membedakan hari normal, sabtu, minggu, hari libur nasional dan hari libur di hari pendek (sabtu)

Buat ‘static named range’ dengan nama yang telah didefinisikan di kolom ‘named range’:

— block [J2:K5]

— dari menu [Formulas], pada group [Define Names], pilih [Create from Selection]

— Pada pilihan ‘Create names from values in the’ , centang (check) hanya di [Right Column]

Memberi kode hari di list tanggal absensi. misal tabel disusun sebagai berikut:

Revisi 10 April 2014

Kolom A dan B diisi manual, sedangkan untuk kolom C:H diisi dengan rumus excel. Rumus untuk kolom C:H adalah: Catatan: rumus atau formula ditulis dalam satu baris dalam excel

Cells	Rumus	Keterangan
[C2]	=IF(COUNTIF(_LiburNasional,A2)>0,IF(WEEKDAY(A2,2)=6,3,2),IF(WEEKDAY(A2,2)=6,1,IF(WEEKDAY(A2,2)=7,2,0)))	menentukan kode hari berdasarkan tanggal di A2
[D2]	=(C2=Normal)(B2>7)+(C2=Sabtu)(B2>5)	jam lembur x 1.5
[E2]	=IF((C2=Normal)(B2>8),B2-8,IF((C2=Sabtu)(B2>6),B2-6,IF(C2=LiburMinggu,IF(B2<7,B2,7),IF(C2=SabtuLibur,IF(B2<5,B2,5)))))+0	jam lembur x 2
[F2]	=(C2=LiburMinggu)(B2>7)+(C2=SabtuLibur)(B2>5)	jam lembur x 3
[G2]	=IF((C2=LiburMinggu)(B2>8), B2-8,IF((C2=SabtuLibur)(B2>6),B2-6))*1	jam lembur x 4
[H2]	=SUMPRODUCT(D2:G2,$D$1:$G$1)	total faktor pengali
	Copy Rumus [C2:H2] ke bawah..

Silahkan download file revisi untuk contoh sample aplikasi perhitungan gaji lengkap dengan hitungan lembur untuk 6 hari kerja. File hasil download adalah simulasi perhitungan dari input daftar karyawan & unit price gaji, list hari libur, form resume absensi dan contoh form slip gaji. Jika ada kesalahan perhitungan, jangan sungkan-sungkan untuk memberi masukan.

==selamat mencoba==

[XLS-PMG-08]: Update Hari Libur di Microsot Project dengan VBA Excel

Referensi	:
Platform	:	Microsoft Excel dan Microsoft Project
Lokasi File	:	download

Salah satu setting yang perlu diperhatikan dalam penggunaan Microsoft Project (MSProject) adalah memasukkan Hari Libur Nasional ke dalam kalender. Apabila proyek yang akan dilaksanakan telah disepakati atau diusulkan bahwa di hari Libur Nasional adalah bukan hari kerja, maka tanggal-tanggal hari libur tersebut harus dimasukkan dalam list exceptions di calendar yang akan dipakai.

Contoh Kalender (Calendar) yang belum dimasukkan daftar hari libur adalah:

Daftar hari libur bisa dimasukkan secara manual di tab Exceptions dengan memasukkan nama hari libur diikuti dengan tanggal awal libur dan akhir libur.

Apabila list hari libur tersimpan dalam Microsoft excel seperi di bawah:

Maka dengan bantuan Visual Basic Application for Excel (VBA Excel), proses update calendar di MSProject bisa dilakukan secara otomatis.

Berikut adalah code VBA Excel yang dimaksud:

Sub UpdateHariLiburNasional()

‘Code berikut berfungsi untuk menambahkan hari libur nasional di microsoft project (MSProject).

‘Pada visual basic editor direferensikan ke Microsoft Project Library agar dapat mengakses object di ‘Microsoft Project.

‘karena saya menggunakan microsoft project 2010, maka nama referensinya : Microsoft Project 14.0 ‘Object Library

‘untuk MSProject versi yang lain, nama referensi hanya berbeda pada angka di belakang tulisan ‘”Microsoft Project XX.X”

‘Alokasi memory untuk object aplikasi MSProject dan Project yang aktif

Dim msApp As MSProject.Application, msPro As Project

‘akan muncul pesan error jika program MSProject belum dijalankan

On Error Resume Next

‘set ke object aplikasi MSProject

Set msApp = GetObject(, “MSProject.Application”)

If Err.Number Then

Err.Clear

MsgBox ” Program Microsoft Projet Belum diaktifkan”

Exit Sub

End If

‘set ke object projek yang aktif

Set msPro = msApp.ActiveProject

‘alokasi memory untuk object kalender

Dim zCal As Calendar, nmCalendar As String

‘set ke object kalender

‘nama kalender yang akan diisi hari libur pada contoh kali ini adalah “Standard”

‘jika nama kalender tidak ditemukan, maka keluar prosedur

nmCalendar = “Standard”

On Error Resume Next

Set zCal = msPro.BaseCalendars(nmCalendar)

If Err.Number Then

Err.Clear ‘bersihkan error

MsgBox “Kalender ” & nmCalendar & ” tidak ada”, vbCritical

GoTo BersihkanAplikasiMSProject

Exit Sub ‘keluar prosedur

End If

‘membaca list tanggal pada Microsoft Excel di sheet yang aktif

‘informasi tangal ada di kolom A, sedangkan informasi keterangan ada di kolom B

‘data dibaca dimulai dari baris ke-dua

Dim rgTanggal As Range, rg As Range

‘set range tanggal di aktif sheet

Set rgTanggal = Range(Range(“a2”), Range(“a2”).End(xlDown))

‘membaca tanggal dan keterangan hari libur

For Each rg In rgTanggal

‘menambahkan exception di object calender

zCal.Exceptions.Add _

Type:=pjDaily, _

Start:=rg.Value, _

Finish:=rg.Value, _

Name:=rg.Offset(, 1).Value

Next rg

BersihkanAplikasiMSProject:

Set msApp = Nothing

End Sub

Seperti telah tercatat di code VBA Excel di atas, syarat-syarat sebelum menjalan VBAExcel di atas adalah:

VBA sudah direferensikan ke object MSProject Library
Program Microsoft Project dan Microsft Excel harus dibuka bersamaan.
Nama Calender yang dipilih harus sudah ada di MSProject yang aktif.

Contoh calendar yang telah diupdate:

Selamat mencoba..

[XLS-PMG-06]:Schedule Proyek dengan Setting Hari Libur dan Hari Kerja di Microsoft Excel

Referensi	:	[XLS-PMG-01]: Schedule Proyek dengan Excel
Platform	:	Minimum Excel 2003
Lokasi File	:	download

Posting [XLS-PMG-01]: Schedule Proyek dengan Excel telah di uraikan langkah-langkah membuat schedule secara otomatis di excel dengan menggunakan fasilitas ‘conditional formating’. Pada posting tersebut prediksi tanggal akhir tiap aktivitas dihitung dengan rumus : [Tanggal Selesai]=[Tanggal Awal]+[Durasi]-1 . Jika menggunakan rumus ini, maka dianggap tidak ada hari libur mulai [Tanggal Awal] sampai [Tanggal Selesai].

Posting kali ini akan dibahas, bagaimana cara membuat schedule otomatis masih menggunakan ‘conditional formatting’ dengan mempertimbangakan hari libur hanya di libur nasional dan hari minggu. Metode atau formula excel yang baru untuk pembuatan manpower schedule dan estimasi biaya juga akan diuraikan lebih lanjut dalam posting ini.

Urutan pembuatan schedule proyek adalah:

Estimasi [Tanggal Selesai] dengan Input [Tanggal Awal] dan [Durasi]
Membuat Schedule Proyek (Gantt Chart)
Memasukkan Rencana Manpower Loading ke Aktivitas

1. Estimasi [Tanggal Selesai] dengan Input [Tanggal Awal] dan [Durasi]

Salah satu fungsi di excel yang populer untuk menentukan tanggal akhir jika dimasukkan parameter durasi adalah WORKDAY(). Untuk pengguna Excel 2003, aktifkan dulu Addin ‘Analysis ToolPak’ sebelum menggunakan fungsi ini.

Bentuk baku rumus WORKDAY() adalah: = WORKDAY(start_date, days [,holidays]). Contoh penggunaan di spread sheet:

Rumus di [G2]=WORKDAY(F2,E2,$A$2:$A$15)

[F2] dan [E2] berturut-turut adalah start_date dan durasi. sedangkan $A$2:$A$15 adalah list hari libur nasional. Hasil dari rumus di atas adalah 28 Maret 2012 yaitu tanggal dimana memperhitungkan hari kerja mulai 26-28 Maret 2012 (3 hari kerja).

Jika diinginkan hari kerja sudah dimulai 22 Maret 2012 dengan libur di hari Hari Raya Nyepi (23 Maret 2012) dan Sabtu-Minggu (24-25 Maret 2012), maka rumus di [G3]=WORKDAY(F3,E3-1,$A$2:$A$15)

Secara default, fungsi WORKDAY() menganggap sabtu dan minggu adalah hari libur sehingga fungsi WORKDAY() tidak bisa dipakai seandainya hari sabtu adalah hari kerja. Salah satu solusi untuk mengatasi masalah tersebut adalah dengan membuat UDF (User Defined Function) menggunakan Visual Basic Application (VBA) atau macro seperti contoh yang aku temukan di internet A Better WORKDAY Function.

Kalo di A Better WORKDAY Function dibuat dengan VBA, maka saya memilih cara lain yaitu dengan menggunakan kombinasi beberapa fungsi menjadi array formula. Karena menggunakan array formula, saat selesai memasukkan atau mengetik formula diakhiri dengan menekan CRTL+SHIFT+ENTER.

Rumus di [G3] adalah:

=IF(E4=0,F4,SMALL(IF((WEEKDAY(ROW(INDIRECT(F4&":"&F4+30)),2)<7)*(COUNTIF($A$2:$A$15,ROW(INDIRECT(F4&":"&F4+30)))=0),ROW(INDIRECT(F4&":"&F4+30))),E4))

Jangan lupa akhiri penulisan atau saat copy rumus di atas dengan CRTL+SHIFT+ENTER. Hasil dari rumus di atas adalah 26 Maret 2012 yaitu tanggal yang sudah memperhitungkan hari sabtu sebagai hari kerja.

2. Membuat Schedule Proyek (Gantt Chart)

Sebelum membuat gant chart, dibuat terlebih dua tabel ‘hari libur nasional’ dan tabel ‘unit price’. Kedua tabel tersebut dibuat dengan fasilitas dynamic range menggunakan fungsi OFFSET() kemudian dynamic range tersebut disimpan dalam ‘named range’. Cara membuat named range telah beberapa kali di posting terdahulu. Misalnya di posting [XLS-PMG-02]: Rumus Terbilang di Excel (Tanpa Macro/VBA) atau di [XLS-MAP-006]: Menampilkan Hasil Koordinat GPS di Chart Excel dengan image dari Google Earth.

2.1. Membuat Tabel ‘Hari Libur Nasional’

Buat sheet baru misalnya [Holidays]

Buat Daftar hari libur misal seperti contoh di atas dimana kolom [A:A] berisi tanggal dan [B:B] adalah keterangan hari liburnya. Buat dynamic range sbb:

Named range

Refers To

Keterangan rumus range

_HariLibur

=OFFSET($B$1,1,-1,COUNT($A:$A))

Membuat dynamic range dimulai dari 1 (satu) baris di bawah cell $B$1 dan 1 (satu) kolom di sebelah kiri cell $B$1 dengan tinggi (atau jumlah record) sesuai dengan data angka di kolom $A:$A.

Hasil Dynamic Range := $A$2:$A$15, range berubah jika ada penambahan data di kolom $A:$A.

2.2. Membuat Table ‘Resources dan Unit Rate’ nya

Contoh table resources dan unit rate dibuat seperti di bawah:

Named range

Refers To

Keterangan rumus range

_Resource

=OFFSET($B$1,1,,COUNT($A:$A),6)

Membuat dynamic range dimulai dari 1 (satu) baris di bawah cell $B$1 di kolom yang sama ($B$1) dengan tinggi (atau jumlah record) sesuai dengan data angka di kolom $A:$A dan lebar = 6 kolom.

Hasil Dynamic Range:= $B$2:$G$4, range berubah jika ada penmbahan angka di kolom $A:$A

_ResID

=OFFSET(_Resource,,,,1)

Membuat dynamic range dari dynamic range _Resource dengan lebar kolom=1.
Hasil dynamic range:= $B$2:$B$4, range berubah jika _resource berubah atau ada penambahan data di kolom $A:$A

2.3. Membuat schedule

Berbeda dengan schedule yang pernah diuraikan di [XLS-PMG-01]: Schedule Proyek dengan Excel, schedule kali ini sudah dilengkapi dengan baris untuk monitoring progress yaitu baris informasi progress actual untuk masing-masing aktivitas.

Berikut snap shot schedule (gantt chart) yang akan dibuat:

Range/Cells	Formula	Keterangan
[J4]	=–OR(WEEKDAY(J$5,2)>6,COUNTIF(_HariLibur,J$5))	hasil formula bernilai 1 jika hari libur nasional dan hari minggu, selain hari tersebut bernilai 0 Copy rumus di [J4] sampai ke [AN4]
[F6]	=IF(D6=0,E6,SMALL(IF((WEEKDAY(ROW(INDIRECT(E6&":"&E6+30)),2)<7)*(COUNTIF(_HariLibur,ROW(INDIRECT(E6&":"&E6+30)))=0),ROW(INDIRECT(E6&":"&E6+30))),D6))	Formula array untuk menentukan tanggal selesai aktivitas setelah dimasukkan durasi di kolom [D:D]. Copy rumus di [F6] sampai di [F28] di baris PLAN
[I6]	=IF(ISBLANK(A6),I5,A6)	membuat ID activity untuk baris PLAN dan ACTUAL
[J6]	=(MEDIAN($E6,J$5,$F6)=J$5)*(J$4=0)	hasil formula bernilai 1 jika hari kerja sesuai dengan jumlah hari kerja yang didefinikan di kolom durasi [D:D]. Copy rumus [J6] sampai [AN6:AN28] di baris
_Task	=OFFSET($A$5,1,,COUNT($A$6:$A$614)*2,2)	Dynamic Range untuk Task. Hasil dari dynamic range adalah [A6:B29]. Note: Isian di kolom ID Task harus berupa angka agar dynamic range bisa menghasilkan range yang benar !!
[E8]	=OFFSET($F$5, SUMPRODUCT((INDEX(_Task,,1)=$G8)*ROW(INDEX(_Task,,1)))-5,)+$H8	Menghasilkan tanggal awal aktivitas dengan mengacu ke aktivitas sebelumnya, kolom [G], dan durasi lag (adjustment), kolom [H]. Jika kolom adjust bernilai 0 (nol), maka start aktivitas akan sama dengan akhir aktivitas sebelumnya. Copy rumus di [E8] sampai [E28] di baris PLAN. Kolom [E] juga bisa dimasukkan tanggal secara manual (constrain starting date). contoh di [E20]

2.4. Membuat conditional format di schedule (gantt chart)

Pilih cell [J5], kemudian atur rule conditional formating sebagai berikut:

Rule Type	Formula	Format	Applies to	Keterangan
Formula	=($C5=$C$7)(J$4=0)(J5>0)		=$J$5:$AN$29	Arsir cell hari kerja sesuai durasi di baris ACTUAL
Formula	=J$4=1		=$J$5:$AN$29	Arsir Hari Libur
Formula	=($C5=$C$6)(J$4=0)(J5>0)		=$J$5:$AN$29	Arsir cell hari kerja sesuai durasi di baris PLAN

Jika dikehendakai, set tampilan worksheet agar angko 0 (nol) tidak ditampilkan dalam worksheet. Cara setting bisa dibaca di [XLS-PMG-01]: Schedule Proyek dengan Excel

3. Memasukkan Rencana Manpower Loading ke Aktivitas

Manpower loading dimasukkan dalam sheet tersendiri. Dalam sheet baru ini ditentukan resourse assignment sesuai dengan aktivitasnya kemudian dihitung manhour atau mandaysnya untuk masing-masing resource. Karena unitprice per days sudah ditentukan di sheet yang lain, maka rencana biaya juga bisa dihitung.

Berikut adalah spreadsheet untuk memasukkan manpower loading:

Range/Cells	Formula	Keterangan
[B3]	=VLOOKUP(A3,_Task,2,0)	Menampilkan task description atau deskripsi pekerjaan sesuai dengan TaskID yang dimasukkan di [A3]
[D3]	=VLOOKUP($C3,_Resource,2,0)	Menampilkan nama resource, sesuai dengan ResourceID yang dimasukkan di cell [C3]
[E3]	=VLOOKUP($C3,_Resource,5,0)	menampilkan unit price, sesuai dengan ResourceID yang dimasukkan di cell [C3]
[F3]	=SUM(G3:AL3)*E3	[Total price]= [total manhours] x [unit price/hour]

Arsiran di mulai cell [G3] harus sama dengan schedule yang telah dibuat sebelumnya (tahapan 2.3), sedangkan untuk isian jam kerja dimasukkan manual di daerah yang berarsir. Seperti pada tahap 2.4, akan digunakan fasilitas conditional formating untuk otomatisasi arsiran.

Sebelum dilakukan proses conditional formating perlu didefinisikan beberapa dynamic range untuk menyamakan schedule dengan manpower assignment. Dynamin range yang dimaksud adalah:

Named Range	ReferTo	Keterangan
_Calendar	=OFFSET(Schedule!$J$5,,,,COUNT(Schedule!$J$5:$ZZ$5))	Dynamic range untuk tanggal di Schedule!$J$5:$ZZ$5
_TaskID	=OFFSET(_Task,,,,1)	Mengambil kolom pertama dari range _Task
_GantChart	=OFFSET(_Calendar,1,,ROWS(_Task),)	mengambil atau mendefinisikan range gantt chart. hasil dari dynamic range adalah range Schedule!$J$6:$AN$29

Pilih cell [G3] kemudian setting condtional formating sebagai berikut:

Rule Type	Formula	Format	Applies to	Keterangan
Formula	=SUMPRODUCT((_Calendar=G$2)(OFFSET(_TaskID,,2)="PLAN")(OFFSET(_TaskID,,8)=$A3)* _GantChart)=1		=$G$3:$AK$32	Arsir cell hari kerja sesuai durasi di baris PLAN

Lakukan pengisian rencana jam kerja, contoh di atas jam kerja dimasukkan 9jam/hari, di cell yang diarsir. Jika ingin berpindah resource assigment yang lain, masukkan TaskID di kolom A, kemudian ResourceID di kolom C.

Jika proses manpower loading/resource assignment selesai, dapat dihitung breakdown biaya per resource atau per activity seperti tabel di bawah.

Silahkan download file untuk dipelajari lebih lanjut.

===selamat mencoba==

[GEN-PMG-06]: Menampilkan Analisa “Cost vs Schedule” dengan Chart Excel

Referensi	:	Earned Value Analysis (Microsoft Project)
Platform	:	Excel 2007
Lokasi File	:

Salah satu kemampuan microsoft project adalah menghitung atau menganalisa earned value diantaranya adalah:

BCWP	: Budgeted Cost of Work Performed
BCWS	: Budgeted Cost of Work Scheduled
ACWP	: Actual Cost of Work Performed

Dari parameter di atas, kemudian dapat dihitung Schedule Performance Index (SPI) dan Cost Performance Index (CPI) dengan rumus sebagai berikut:

		Nilai=1	Nilai<1	Nilai>1
SPI	=BCWP/BCWS	on schedule	behind schedule	ahead schedule
CPI	=BCWP/ACWP	on budget	over budget	under budget

Misal jika nilai SPI >1 dan CPI < 0, maka hasil analisanya adalah “Ahead Schedule and Over Budget”.

Rumus untuk menampilkan status seperti di atas jika di microsoft excel adalah:

=IF(SPI=1,"ON",IF(SPI>1,"AHEAD","BEHIND")) & " SCHEDULE AND " & IF(CPI=1,"ON",IF(CPI>1,"UNDER","OVER")) & " BUDGET "

Apabila akan ditampilkan di microsoft project, maka perlu customize field dengan memasukkan formula (gambar di bawah):

IIf([SPI]=1,"ON",IIf([SPI]>1,"AHEAD","BEHIND")) & " SCHEDULE AND " & IIf([CPI]=1,"ON",IIf([CPI]>1,"UNDER","OVER")) & " BUDGET "

[SPI] dan [CPI] adalah field hasil hitungan dari program microsoft project.

Silahkan dibaca link referensi di atas, jika ingin mempelajari lebih lanjut tentang SPI dan CPI.

Karena dalam microsoft project tidak bisa menampilkan kombinasi hasil perbandingan SPI dan CPI dalam bentuk chart, maka chart perbandingan tersebut dibuat dalam excel yang hasilnya seperti gambar di bawah:

Silahkan download filenya, untuk melakukan simulasi perbandingan SPI dan CPI dengan memasukkan parameter BCWP, BCWS dan ACWS.

===semoga berguna dan menjadi ilmu yang bermanfaat===