Dokumentasi Audit
Panduan Perhitungan KPI di Google Spreadsheet
Dokumen ini menjelaskan dari mana setiap angka KPI berasal — bagaimana sheet MERGED DATA dibentuk dari data sumber, dan bagaimana rumus di tiap sel sheet KPI menghitungnya. Tujuannya agar auditor bisa menelusuri (trace) setiap angka sampai ke baris data aslinya. Aplikasi web ini dan spreadsheet menghasilkan angka yang sama; spreadsheet dipakai sebagai jejak audit yang dapat ditelusuri.
1Struktur Workbook (tab/sheet)
| Sheet | Peran | Isi |
|---|---|---|
| LEAD TIME | Sumber | Satu baris per PR → PO → penerimaan barang (GRPO). Kolom utama: PR No, PR Date, Item Code, Last Purchased Price, PO No, PO Date, PO Due Date (ETA), PO Quantity, Current PO Price, PR to PO Days, GRPO Date, GRPO Quantity. |
| PO REPORT | Sumber | Detail Purchase Order. Dipakai untuk menentukan apakah sebuah PO Lokal atau Impor (dicocokkan ke LEAD TIME lewat No. PO). |
| MASTER VENDOR | Sumber | Daftar vendor: Kode Vendor, Tanggal Dibuat, Status Vendor. Dipakai untuk KPI 7 (vendor baru). |
| MERGED DATA | Hasil olahan | Gabungan LEAD TIME + kolom turunan yang dihitung. Di sinilah semua perhitungan per-baris terjadi. Tiap baris LEAD TIME menjadi satu baris MERGED DATA. |
| KPI - 2026 / KPI - 2025 | Hasil akhir | Nilai REAL, %, dan CAPAI tiap KPI per bulan. Sel-selnya berisi RUMUS hidup (COUNTIFS / AVERAGEIFS) yang menghitung dari sheet MERGED DATA. |
Catatan teknis. Nilai di MERGED DATA dihitung lalu disimpan sebagai nilai (bukan rumus array) karena lebih dari 13.000 baris terlalu berat bila memakai rumus array. Menu KPI Audit ▸ Refresh data now (atau auto-refresh) menghitung ulang MERGED DATA bila data sumber berubah. Sheet KPI tetap memakai rumus hidup yang mengacu ke MERGED DATA, sehingga dapat ditelusuri.
2Periode Pelaporan (window 21–20)
Laporan TIDAK memakai bulan kalender. Laporan bulan M memakai data dari tanggal 21 dua bulan sebelumnya sampai 20 satu bulan sebelumnya.
| Laporan bulan | Rentang tanggal data |
|---|---|
| Januari | 21 Nov – 20 Des |
| Februari | 21 Des – 20 Jan |
| Maret | 21 Jan – 20 Feb |
| April | 21 Feb – 20 Mar |
| Mei | 21 Mar – 20 Apr |
Aturan penentuan bulan
Sebuah tanggal masuk ke bulan laporan:
- tanggal ≤ 20 → bulan berikutnya
- tanggal ≥ 21 → dua bulan berikutnya
Inilah yang dihitung di kolom RptMonth(PO) dan RptMonth(PR) pada MERGED DATA.
3Sheet MERGED DATA — penjelasan tiap kolom
Tulang punggungnya adalah LEAD TIME (satu baris per penerimaan). Kolom-kolom turunan di bawah ini menyiapkan semua penanda yang dipakai rumus KPI.
Data mentah dari LEAD TIME (kolom A–N)
Disalin apa adanya dari LEAD TIME, hanya dirapikan (tanggal DD.MM.YY → tanggal asli, angka dibersihkan dari pemisah ribuan, tanda “-” dianggap kosong).
| Kol | Nama | Arti / aturan |
|---|---|---|
| A | PR No | Nomor Purchase Request. |
| B | Item Code | Kode barang. |
| C | Item Name | Nama barang. |
| D | PR Date | Tanggal PR. |
| E | PO No | Nomor PO (kosong bila PR belum jadi PO). |
| F | PO Date | Tanggal PO. |
| G | PO Due Date | Tanggal jatuh tempo / ETA PO. |
| H | PO Qty | Kuantitas yang dipesan di PO. |
| I | Last Price | Harga pembelian terakhir (untuk KPI 1). |
| J | Current Price | Harga PO saat ini (untuk KPI 1). |
| K | PR→PO Days | Jumlah hari dari PR sampai PO. |
| L | GRPO Date | Tanggal penerimaan barang. |
| M | GRPO Qty | Kuantitas barang yang diterima. |
| N | Last PO No | Nomor PO pembelian terakhir. |
Penggabungan & penanda (kolom O–S)
Menghubungkan ke PO REPORT dan menentukan bulan laporan.
| Kol | Nama | Arti / aturan |
|---|---|---|
| O | Lokal/Impor | Diambil dari PO REPORT dengan mencocokkan No. PO. Penentu KPI 2 (Lokal) vs KPI 3 (Impor). |
| P | Key PO|Item | Kunci gabungan No. PO + Kode Item — untuk mengelompokkan penerimaan dari PO-item yang sama. |
| Q | Key PR|Item | Kunci gabungan No. PR + Kode Item — untuk KPI 4. |
| R | RptMonth(PO) | Bulan laporan (window 21–20) berdasarkan Tanggal PO. Dipakai KPI 1, 2, 3. |
| S | RptMonth(PR) | Bulan laporan berdasarkan Tanggal PR. Dipakai KPI 4. |
Perhitungan KPI 1 — Price Performance (kolom T–U)
| Kol | Nama | Arti / aturan |
|---|---|---|
| T | Delta% | (Current Price − Last Price) ÷ Last Price × 100. Negatif = lebih murah (bagus). |
| U | KPI1 incl | 1 jika baris dihitung untuk KPI 1: kemunculan pertama PO-item, harga lama & baru > 0, dan bukan outlier (Delta% antara −90% s/d 300%). |
Perhitungan KPI 2 & 3 — Realisasi PO ke BPB (kolom V–AC)
Mengelompokkan penerimaan per PO-item, lalu menentukan apakah barang diterima penuh tepat waktu.
| Kol | Nama | Arti / aturan |
|---|---|---|
| V | Ordered | Kuantitas yang dipesan (maksimum PO Qty untuk PO-item itu). |
| W | CumRecv | Akumulasi kuantitas GRPO sampai tanggal baris ini (penerimaan bisa bertahap). |
| X | Reached | 1 jika akumulasi penerimaan ≥ kuantitas dipesan pada baris ini. |
| Y | Completion | Tanggal GRPO saat akumulasi mencapai kuantitas dipesan = tanggal barang dianggap diterima penuh. |
| Z | Deadline | PO Due Date + toleransi: 7 hari untuk Lokal, 14 hari untuk Impor. |
| AA | OnTime KPI2/3 | 1 jika Completion ≤ Deadline (diterima penuh tepat waktu). |
| AB | Scored KPI2/3 | 1 jika PO-item dinilai. Sudah selesai → dinilai. Belum selesai tapi sudah lewat deadline → dinilai (terlambat). Belum selesai & belum lewat deadline → “Outstanding”, DIKECUALIKAN (0). |
| AC | isPOUnit | Penanda 1 agar tiap PO-item dihitung sekali (kemunculan pertama, ada PO & jatuh tempo, scope Lokal/Impor). |
Perhitungan KPI 4 — Realisasi PR ke PO (kolom AD–AG)
| Kol | Nama | Arti / aturan |
|---|---|---|
| AD | hasPO | 1 jika baris sudah punya No. PO (PR sudah jadi PO). |
| AE | Day used | Jumlah hari PR→PO (dari LEAD TIME, atau Tanggal PO − Tanggal PR). |
| AF | OnTime KPI4 | 1 jika PR sudah jadi PO dalam 0–7 hari. Dinilai sekali per PR-item. |
| AG | isPRUnit | Penanda 1 agar tiap PR-item dihitung sekali (penyebut KPI 4 = semua PR-item, termasuk yang belum jadi PO). |
4Sheet KPI — rumus tiap sel
Susunan: baris 4–10 = KPI 1–7; tiap bulan memakai 4 kolom (STD, REAL, %, CAPAI). Januari mulai kolom E, sehingga REAL KPI 1 Januari 2026 = sel F4. Contoh di bawah memakai Januari 2026 (label bulan "2026-01"); bulan lain hanya berbeda kolom & label.
Standar & bobot
| KPI | Nama | Standar | Bobot |
|---|---|---|---|
| KPI 1 | Price performance (harga PO vs PO terakhir) | Min −1% | 10% |
| KPI 2 | Realisasi PO to BPB (Lokal) | 95% | 20% |
| KPI 3 | Realisasi PO to BPB (Impor) | 95% | 20% |
| KPI 4 | Realisasi PR to PO | 95% | 15% |
| KPI 5 | Quality Compliance (LKM) | Maks 3 | 15% |
| KPI 6 | Substitusi material | Maks 1 / tahun | 10% |
| KPI 7 | Sourcing vendor baru | Min 6 / tahun | 10% |
REAL (nilai nyata) — rumus per KPI
KPI 1 — Price Performance — sel F4
=IFERROR(AVERAGEIFS('MERGED DATA'!$T:$T,'MERGED DATA'!$U:$U,1,'MERGED DATA'!$R:$R,"2026-01")/100,"–")Rata-rata kolom Delta% (T) untuk semua baris yang ditandai dihitung (U=1) dan jatuh di bulan laporan 2026-01 (R). Dibagi 100 agar tampil sebagai persen.
KPI 2 — PO→BPB Lokal — sel F5
=IF(COUNTIFS('MERGED DATA'!$AC:$AC,1,'MERGED DATA'!$O:$O,"Lokal",'MERGED DATA'!$R:$R,"2026-01",'MERGED DATA'!$AB:$AB,1)=0,"–", COUNTIFS(…,'MERGED DATA'!$AA:$AA,1) / COUNTIFS(…))Jumlah PO-item Lokal yang tepat waktu (AA=1) DIBAGI jumlah PO-item Lokal yang dinilai (AB=1), untuk bulan 2026-01, tiap PO-item dihitung sekali (AC=1).
KPI 3 — PO→BPB Impor — sel F6
= sama seperti KPI 2, tetapi kriteria scope "Impor" (toleransi 14 hari)Identik dengan KPI 2 untuk PO Impor.
KPI 4 — PR→PO — sel F7
=IF(COUNTIFS('MERGED DATA'!$AG:$AG,1,'MERGED DATA'!$S:$S,"2026-01")=0,"–", COUNTIFS(…,'MERGED DATA'!$AF:$AF,1) / COUNTIFS('MERGED DATA'!$AG:$AG,1,'MERGED DATA'!$S:$S,"2026-01"))Jumlah PR-item yang jadi PO tepat waktu (AF=1) DIBAGI total PR-item (AG=1) di bulan 2026-01.
KPI 5 — Quality (LKM) — sel F8
=IF(F4="–","–",0)Entri manual. Diisi 0 (kasus terbaik: tidak ada keluhan) di bulan yang ada datanya. 0 ≤ 3 → 100%.
KPI 6 — Substitusi — sel F9
=IF(F4="–","–",1)Entri manual. Diisi 1 (kasus terbaik: maks 1 substitusi/tahun). 1 ≤ 1 → 100%.
KPI 7 — Vendor Baru — sel F10
=COUNTIFS('MASTER VENDOR'!$W$2:$W,1,'MASTER VENDOR'!$Y$2:$Y,1,'MASTER VENDOR'!$X$2:$X,">="&DATE(2026,1,1),'MASTER VENDOR'!$X$2:$X,"<="&DATE(2026,1,1))Jumlah vendor berstatus Aktif (W=1), dihitung sekali (Y=1), yang bulan-dibuatnya (X) jatuh dalam tahun berjalan sampai bulan ini (kumulatif).
% achievement, CAPAI, dan Total
% (mis. G4)
=IF(F4="–","–",MIN(1,MAX(0,F4/(-0.01))))REAL ÷ Standar, dibatasi 0–100%. Standar KPI 1 = −1% (−0,01); KPI 2/3/4 = 95% (0,95); KPI 7 = 6. Untuk KPI 5 & 6: 100% jika REAL ≤ standar, selain itu 0%.
CAPAI (mis. H4)
=IF(G4="–","–",G4*$D4)% dikali Bobot (kolom D). Mis. KPI 2: 77,44% × 20% = 15,49%.
Total bulanan (mis. H11)
=SUM(H4:H10)Jumlah CAPAI semua KPI 1–7 = skor total bulan itu (maksimum 100%).
5Contoh menelusuri sebuah angka
“Dari mana angka 73,57% pada KPI 2 Januari 2026 (sel F5)?”
- Lihat rumus di sel F5 — ia menghitung dari sheet MERGED DATA.
- Buka MERGED DATA, lalu saring (filter) baris dengan kriteria:
RptMonth(PO) = 2026-01,Lokal/Impor = Lokal,isPOUnit = 1,Scored KPI2/3 = 1. - Hitung: jumlah baris dengan
OnTime KPI2/3 = 1dibagi total baris hasil saringan, dikali 100% = 73,57%. - Tiap baris itu bisa ditelusuri balik: No. PO, tanggal, kuantitas ke LEAD TIME; status Lokal/Impor ke PO REPORT.
Prinsip yang sama berlaku untuk semua KPI: sel KPI = rumus COUNTIFS/AVERAGEIFS atas kolom-kolom penanda di MERGED DATA, dan tiap baris MERGED DATA berasal dari satu baris LEAD TIME (digabung dengan PO REPORT / MASTER VENDOR).