| Referensi |
: |
|
| 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==
