Membuat aplikasi pengendalian Pekerjaan borongan konstruksi dengan 4 fungsi excel

Kelas Tukang

Pekerjaan Konstruksi termasuk salah satu pekarjaan yang rumit, karena pekerjaan ini secara prakteknya memiliki banyak alur seperti Kas, belanja, kontrol absensi pekerja, akomodasi dan material sehingga menuntut para pemborong terus melakukan cek setiap saat.

Nah, pada postingan ini ZIS Masjid Assalaam mencoba membuat aplikasi manajemen sederhana untuk pekerjaan konstruksi kelas tukang dengan bantuan excel yang dapat  membantu menyelesaikan beberapa masalah, khusunya terkait keuangan,  material serta pencapaian taget,  melakukan kontrol jumlah pekerja, absen, laporan per minggu, per bulan, pertahun hingga laporan penuh suatu pekerjaan, yang dalam aplikasi ini cukup menggunalan 4 fungsi excel yaitu: SUMIFS, MAXA, COUNTIFS DAN VLOOKUP dengan sedikit tambahan makro (bisa dilihat paling bawah postingan ini).

Memuat data unik.

Agar data terintegrasi dengan baik, sebaiknya dibuat daftar data unik sebagai data acuan yang bersifat konstan/tetap dan tidak berubah yang digunakan untuk menghubungkan berbagai data tabel melalui jendela query maupun melalui logikaExcel. 

Minggu ke untuk setiap input data.

Walaupun excel memiliki fungsi mengembalikan tanggal ke minggu, tetapi data yang dihasilkan menghitungnya dalam setahun sesuai penanggalan. Hal ini menjjadi kendala tersendiri saat para pemborng melakukan pembayaran gaji hanya dilakukan per hari tertentu misalnya hari kamis, jika menggunakan system excel maka kemungkinan ada dua transaksaksi ketika minggu tersebut jatuh diantara dua bulan. Maka untuk mengatasinya cara paling sederhana dengan menginputnya, sesuai mingguan yang anda terapkan, misalnya per hari jumat, hari kamis, atau per hari sabtu, cara ini akan lebih fleksibel.

Sumber data asli berupa nota faktur dll.

Agar memudahkan setiap terjadi masalah input data, berilah nomor untuk setiap nota maupun catatan lainnya.

Pengelompokan data akhir.

Hasil akhir dari aplikasi ini diharapkan dapat melihat seluruh aktifitas yang terjadi khusunya arus kas dan keuangan lainnya.

Dalam aplikasi pengendalian keuangan pekerjaan borongan dan konstruksi yang saya buat memuat beberapa tabel:

1. Tabel pekerja.

Tabel ini berfungsi mengumpulkan seluruh karyawan dengan jumlah tak terhingga, dari berbagai jenis pekerjaan yang anda miliki, bahkan anda bisa menyimpan daftar nama-nama pekerja tersebut selamanya.berikut tabel daftar pekerja.

2. Tabel Nama Pekerjaan.

Tabel ini berfungsi mengumpulkan seluruh pekerjaan yang dilaksanakan yang anda miliki atau yang sedang anda anda kerjakan, sehingga anda dapat mengumpulkan seluruh pekerjaan hanya dalam satu file Excel saja, misalnya: anda memiliki 2 pekerjaan perbaikan jalan, gedung atau renovasi bangunan lainnya.

3. Tabel Jurnal

Tabel jurnal merupakan tabel yang digunakan untuk mencatat seluruh transaksi yang terjadi, seperti arus kas, belanja, kasbon pekerja, pengeluaran upah dan lainnya yang dapat disesuaikan menurut kebutuhan anda. Sebenarnya pencatan serta pengelolaan data cukup dilakaukan dalam jurnal, tetapi agar lebih mudah saat kontrol saya memisahkan di beberapa tabel, yaitu tabel kas, tabel absen, tabel gajian. Yang ketiga tabel ini akan menyusun secara sistematis terhadap laporan berikutnya. Jika anda ingin malakukannya dengan satu sumber jurnal, cukup menambahkan dilaporan berikutnya.

Keterangan gambar

Dalam aplikasi ini anda melakukan input setiap transaksi yang terjadi dan dikelompokan menurut data unik di tabel transaksi seperti apakah transkasi masuk kategori kas, utang, piutang atau lainnya. Anda bisa menentukan sendiri penaaannya yang sekiranya mudah dipahami diri anda.untk kontrol data anda bisa menambahkan satu kolom data error dengan menabahkan fungsi =VLOOKUP([@[nama proyek]],Tabel11[[NAMA PROYEK]:[TANGGAL MULAI ]],2)&", "&TEXT([@tgl],"[$-421]dd mmmm yyyy").

4. Tabel absen

Tabel absen digunakan untuk mengumpulkan sejumlah informasi kehadiran pekerja dan mengelompokkannya sebagai bagian arus keuangan utama.

Keterangan tabel

Kolom NAMA PROYEK  sampai dengan kehadiran merupakan tabel input stiap setiap pekerja, sementara kolom berikutnya merupakan kolom yang ditambahkan yang berguna mengontrol jika terasa ada kejanggalan seperti nilai gaji dan jabatan yang dimungkinkan salah input di tabel nama pekerja.

Untuk kolom jabatan dan gaji pokok anda bisa menggunakan fungsi =VLOOKUP([@nama],Tabel5[[nama]:[mulai kerja]],3) dan untuk kolom gaji pokoknya cukup mengganti angka 3 dengan angka 4 di fungsi kolom jabatan.

Kolom kontrol data, merupakan kolom pengingat error jika terjadi data ganda dengan input yang sama, untuk fungsinya anda bisa menggunakan =[@nama]&", "&VLOOKUP([@[NAMA PROYEK]],Tabel11[[NAMA PROYEK]:[TANGGAL MULAI ]],2)&" "&IF(WEEKDAY([@tanggal],16)=7,"jatuh di hari libur",(" "&TEXT([@tanggal],"dd-mm-yyyy"))&", minggu ke"&" "&[@[mg ke]])

5. Tabel Kasbon

Tabel yang digunakan untukengumpulkan sejumlah informasi pinjaman para pekerja sebelum waktu pembayaran upah tercapai. Dalam pembuatan tabel ini anda juga bisa disatukan dalam tabel absen, untuk aplikasi ini saya buat secara terpisah untuk lebih memudahkan control.

6. Tabel pengolah data

Semua tabel input memiliki fungsi penting terhadap beberapa tabel selanjutnya, yang merupakan tabel kunsi pengolahan data yang dperlukan seperti saat akan melakukan pembayaran gaji, membuat laporan mingguan, bulanan, tahunan bahkan laporan setelah selesai pekerjaan.

1. Tabel gaji

Saat akan melakukan pembayaran gaji, maka anda cukp melihat tabel gajian dengan menginput nama-nama pekerja, secara otomatis data absen, jumlah pinjaman pekerja terekam dalam tabel ini, sehingga anda cukup melihanya berapa sisa yang harus dibayar setiap orang pekerja.

Keterangan gambar

Anda hanya perlu menginput tiga data unik yang terdiri dari nma pekerjaan nama pekerja dan gaji yang akan dibayar dengan melihat hasil yang ditampilkan aplikasi dalam kolom sisa.

Sementara kolom total yang harus dibayar merupakan hasil otomatisasi system terhadap transaksi yang terjadi yang diperoleh dari tabel absen, untuk fungsinya anda bisa menggunakan fungsi =SUMIFS(Tabel4[total (axb)],Tabel4[NAMA PROYEK],[@[Nama proyek]],Tabel4[nama],[@nama],Tabel4[mg ke],[@[minggu ke]]). Untuk kolom kasbonnya diperoleh dari tabel kasbon melalui fungsi =SUMIFS(Tabel9[jumlah kasbon],Tabel9[Nama proyek],[Nama proyek],Tabel9[nama],[@nama],Tabel9[minggu ke],[@[minggu ke]]).

2. tabel balance

tabel ini berfungsi untuk mengetahui seluruh jumlah trasaksi yang terjadi setiap bulannya. Anda bisa membuatnya seperti tabel berikut:

Fungsi yang dierlukan:

Kolom Nama proyek dan bulan di input.

Kolom jumlah kas menggunakan fungsi: =SUMIFS(Tabel2[jumlah kas],Tabel2[NAMA PROYEK],[nama proyek],Tabel2[kontrol],"*"&[tgl laporan]&"*")

Kolom jumlah gaji dibayar setelah dipoton jumlah kasbon, fungsi yang digunakan: =SUMIFS(Tabel13[sisa gaji dibayar (a)],Tabel13[Nama proyek],[nama proyek],Tabel13[kontrol error],"*"&[tgl laporan]&"*")

Kolom gaji belum terbayar, jika terjadi pembayaran gaji tergantung dan belum terbayarkan, bisa menggunakan fungsi: =SUMIFS(Tabel13[sisa

(b-c)],Tabel13[Nama proyek],[nama proyek],Tabel13[kontrol error],"*"&[tgl laporan]&"*")

Kolom kasbon dibaayar, anda bisa melihat berapa jumlah pinjaman pekerja setiap bulannya sehingga dapat menaksir kebutuhan kas tersedia setiap harinya untuk setiap bulan, dalam kolom ini menggunakan fungsi =SUMIFS(Tabel9[jumlah kasbon],Tabel9[Nama proyek],[nama proyek],Tabel9[kontrol error],"*"&[tgl laporan]&"*") 

Dan sisanya merupakan data dari tabel jurnal untuk fungsnya : =SUMIFS(Tabel3[jumlah],Tabel3[nama proyek],[nama proyek],Tabel3[jenis],Tabel12[[#Headers],[Material]],Tabel3[kontrol data ganda dan error],"*"&[tgl laporan]&"*") perhatikan kata Tabel12[[#Headers],[Material]] merupakn judul kolom yang namanya disamakan dengan nama jenis yang ada di kolom jurnal, jadi jika ingin menambahkan atau mengubah jenis transaksi anda cukup menambahkan kolom berikutnya atau mengubahnya, sesuai nama jenis transaksi.

3. Tabel balance tahunan

Prinsipnya sama dengan tabel laporan bulanan, anda akan melihat tansaksi setiap tahun.

Selebihnya anda bisa berkreasi sendiri sehingga berapappun jumlah proyek anda akan tetap memiliki kontrol keuangan sehingga anda terasa nyaman dan pekerjaan terkendali.

6. Tabel absen angkutan

Tabel ini bisa ditambahkan sebagai salah bagian aplikasi yang memuat daftar kendaraan maupaun angkutan lainnya yang biasanya memiliki rutinitas tersendiri, seperti: angkutan pasir, urugan, kayu, batako, semen dan lainnya. Hal ini penting untukengontrol volume bahan tertentu.

Link aplikasi Aplikasi pengaturan keuangan dan borongan excel

Penggunaan 4 fungsi excel untuk aplikasi keuangan konstruksi

1. Fungsi SUMIF
2. Fungsi SUMIFS

SUMIFS, berfungsi untuk menjumlahkan dengan beberapa kriteria sebagai syarat condisionalnya..

3. Fungsi COUNTIFS

Fungsi countifs digunakan untuk menghitung jumlah data berupa angka dalam sebuah field dengan beberapa kriteria.

4.  Fungsi MAXA, fungsi MAXA digumnakan untuk menghitun nilai maksimal sebuah bialangan atau angka dal sebuah field atau tabel dengan beberapa kriteria.

Makro excel untuk inisialisasi kalimat

Umumnya inisialisasi digunakan agar kalimat yan panjang menjadi lebih sederhana sehingga mudah di ingat, nah dalam excel selain dengan fungsi logika bisa juga digunakan dengan menggunakan modul yang berisi makro dengan fungsi sbb:

Option Explicit

Function Singkatan(S As String)

Dim Ar, i As Integer

Ar = Split(Trim(S), " ")

For i = 0 To UBound(Ar)

Singkatan = Singkatan & UCase(Left(Ar(i), 1))

Next

End Function [1]

---

[1] Sumber xl mania