Audit Data Analytics dengan R – Aging Schedule

by Posted on

Modern Problems Require Modern Solutions tampaknya menjadi kutipan favorit di blog ini. Kali ini “modern solution” yang diadvokasi adalah R dan RStudio sebagai alat bantu audit.

Kode Lima Detik

# Set Directory
setwd("D:/basangdata/audit_with_r_intro")

# Load Data
Sales.SalesOrderHeader <- read.delim("Sales.SalesOrderHeader.csv")

# Max DueDate
max(Sales.SalesOrderHeader$DueDate)
# Min DueDate
min(Sales.SalesOrderHeader$DueDate)
# Range DueDate
range(Sales.SalesOrderHeader$DueDate)

# 2011 Sales
sales2011 <- Sales.SalesOrderHeader[Sales.SalesOrderHeader$OrderDate <= "2011-12-31",]
# Fiscal year ended December 31st 2011
end.of.period <- as.Date('2011-12-31', '%Y-%m-%d')

# Convert DueDate column to Date
sales2011$DueDate <- as.Date(sales2011$DueDate, '%Y-%m-%d')
# DueDate to Dec 31st 2011
sales2011$ElapsedDays <- end.of.period - sales2011$DueDate

# Age 0-30 days
sales2011$elap.0.30 <- sales2011$TotalDue
sales2011$elap.0.30[sales2011$ElapsedDays > 30] <- 0
# Age 31-60 days
sales2011$elap.31.60 <- sales2011$TotalDue
sales2011$elap.31.60[sales2011$ElapsedDays < 31 | sales2011$ElapsedDays > 60] <- 0
# Age 61-90 days
sales2011$elap.61.90 <- sales2011$TotalDue
sales2011$elap.61.90[sales2011$ElapsedDays < 61 | sales2011$ElapsedDays > 90] <- 0
# Age >90 days
sales2011$elap.90 <- sales2011$TotalDue
sales2011$elap.90[sales2011$ElapsedDays < 91] <- 0

# Save to csv
write.csv(sales2011, 'sales2011.csv')
Akan Dibahas hide
1 Urgensi
2 Kenapa RStudio
3 Instalasi
4 Aging Schedule
4.1 Set Directory
4.2 Load Data
4.3 Create a Script
4.3.1 Set Directory and Load Data
4.3.2 Due Date Range
4.3.3 2011 Sales
4.3.4 December 31st 2011
4.3.5 Aging Schedule
4.3.6 Save
5 Konklusi
6 Referensi

Urgensi

Kita sudah sampai pada era dimana Microsoft Excel® mulai kesulitan menangani data yang perlu diuji auditor. Microsoft sendiri sudah merespon hal tersebut dengan berbagai produk seperti Power Query, Power Pivot, Power BI dan beberapa lainnya.

Namun punya alternatif tidak pernah merugikan. Dalam kasus ini, auditor yang harus menangani data “besar” (ukurannya adalah Ms Excel mulai lambat memproses), memilik opsi lain seperti Python (dengan Pandas) dan R.

Dalam tulisan ini kita akan menggunakan R sebagai alat bantu audit. Secara singkat, berikut beberapa keunggulan R dibanding Ms Excel.

  • R dapat mengotomasi dan memproses data lebih cepat.
  • Mayoritas pekerjaan dengan data telah disediakan Library R oleh komunitas.
  • Visualisasi data lebih kompleks dan beragam bisa/lebih mudah dilakukan pada R.
  • R, melalui Shiny framework, dapat membuat web interaktif untuk menceritakan data.
  • R (dan RStudio) gratis.

Namun daftar seperti di atas hanya akan memantik lagi perseteruan abadi antar pengguna militan karenanya kita cukupkan.

Kenapa RStudio

Kenapa tidak?

Karena sudah ada, banyak, artikel yang membahas manfaat RStudio untuk melakukan analisis data, penulis akan memberikan opini penulis soal RStudio.

RStudio adalah pilihan menarik karena semua kebutuhan telah lengkap dalam satu tempat. Menulis kode, menampilkan grafik/visualisasi data dan menampilkan data dalam bentuk tabel (seperti Ms Excel) semuanya ada di RStudio.

Selain itu di RStudio masih ada banyak hal yang dapat dijangkau dengan klik, hal tersebut tentu “sedikit” melegakan bagi banyak orang. Berbeda dengan Jupyter (Notebook/Lab) yang memiliki lebih sedikit lagi antarmuka (interface) grafis.

Bagi mayoritas, ketiadaan antarmuka grafis yang dapat diklik adalah hambatan utama dalam mengakses teknologi.

Instalasi

Instalasi R dan RStudio sangat mudah. Pada tulisan sebelum ini telah dijelaskan prosesnya, yang secara mengejutkan belum berubah sampai sekarang.

Jika proses instalasi berhasil, kita akan mendapatkan RStudio dengan tampilan seperti ini.

Untuk mempermudah pada tulisan ini kita menggunakan konvensi penomoran panel pada RStudio.

Keterangan masing-masing panel adalah sebagai berikut.

  1. Area untuk menampilkan R Script (kode yang akan kita tulis) atau Variable View (menampilkan variabel, atau biasanya data dalam bentuk tabular).
  2. Secara default pada area ini terdapat tab Console dan Terminal. Console adalah R Console, tempat kode akan dieksekusi. Sedang Terminal sama dengan Command Prompt/Terminal/Console pada OS kita.
  3. Pada area ketiga terdapat tab Environment, History, Connection dan Tutorial. Paling kiri, dan sering digunakan, adalah Environment yang menampilkan variabel yang telah dieksekusi di R Console.
  4. Files, Plots, Packages, Help dan Viewer adalah tab yang termuat dalam area ini. Melalui tab Files kita dapat menavigasi ke folder tempat kita akan bekerja (me-load data, menyimpan data, menyimpan grafis). Pada tab Packages kita dapat melakukan instalasi Packages (seperti library pada Python) dan operasi lainnya seperti melalukan update, melihat daftar packages yang telah diinstal.

Aging Schedule

Pengujian umur piutang adalah prosedur pemeriksaan standar pada entitas yang memiliki piutang.

Dalam tulisan ini kita akan membuat umur piutang berdasarkan data penjualan Adventure Works. Berdasarkan umur piutang, masing-masing penjualan akan dibagi menjadi empat kelompok.

  • 0 – 30 hari
  • 31 – 60 hari
  • 61 – 90 hari
  • >90 hari

Set Directory

Pertama kita perlu menentukan directory/folder tempat bekerja. Langkah ini akan membantu proses selanjutnya yaitu saat me-load data dari file csv dan menyimpan data yang telah selesai diproses.

RStudio menyediakan antarmuka grafis yang memudahkan langkah ini. Pada panel no 4, tab Files, pilih menu Go to directory (tiga titik di kanan ikon Home).

Lalu pilih folder yang akan digunakan, dalam contoh adalah D:\basangdata\audit_with_r_intro.

Agar default folder tersebut dikenali oleh sesi yang sedang digunakan, kita perlu melakukan Set Working Directory melalui menu Session > Set Working Directory > To Files Pane location.

Menu tersebut akan mengeksekusi fungsi setwd dengan parameter berupa alamat folder yang telah dipilih melalui panel Files. Di Windows, berbeda dengan command prompt, R Console menggunakan “/” sebagai pemisah antar folder alih-alih “\”.

setwd("D:/basangdata/audit_with_r_intro")

Dari sini kita tahu bahwa RStudio memberikan jalan pintas untuk pelbagai fungsi R, melalui menu atau antarmuka grafis. Menggunakan perspektif berbeda, kita selalu dapat mengetik kode dan kemudian menjalankannya di R Console untuk mendapatkan hasil yang sama.

Load Data

Selanjutnya kita akan mengambil data dari file Sales.SalesOrderHeader.csv dari Adventure Works. Jika belum memiliki, silahkan mengunduh melalui pranala ini.

Pada panel nomor 3, tab Environment, klik Import Dataset kemudian pilih file yang akan digunakan.

Selanjutnya akan muncul window berisi data yang akan ditampilkan beserta beberapa pilihan di sebelah kiri. Kita dapat menentukan nama variabel yang akan digunakan, encoding, heading dan beberapa lainnya.

Klik Import jika telah sesuai, kemudian RStudio akan menampilkan variabel berisi data yang baru saja di-load.

Seperti saat menentukan direktori/folder tempat bekerja, saat me-load data menggunakan menu tadi, RStudio menulis dan mengeksekusi kode di R Console. Fungsi yang digunakan adalah read.delim. untuk me-load data dari csv file dan fungsi View untuk menampilkannya.

Pada tab Environment sekarang terdapat satu variabel Sales.SalesOrderHeader berisi 31.465 baris data.

Create a Script

Pada dasarnya kita cukup mengetik perintah pada R Console dan kemudian mendapatkan hasil yang diinginkan namun pada kebanyakan skenario kita perlu memiliki catatan kode apa saja yang perlu dijalankan untuk mencapai tujuan. Karena itu kumpulan kode tersebut perlu dihimpun dalam satu file yang dapat digunakan kembali.

Manfaat lainnya dari mengumpulkan pada sebuah script adalah kita dapat mengevaluasi kode yang digunakan atau disalin-tempel untuk keperluan lain.

Untuk membuat R Script pilih menu File > New File > R Script.

Pada panel nomor 1 sekarang terdapat sebuah R Script yang belum bernama.

Set Directory and Load Data

Sebelumnya kita telah memilih folder kerja dan membuat sebuah variabel berisi data dari Sales.SalesOrderHeader.csv melalui menu di RStudio. Di masa depan jika perlu melakukan lagi, kemungkinan kita akan berada di situasi ini:

  • lupa langkah apa saja yang diperlukan;
  • merasa langkah-langkah tersebut terlalu banyak dan memakan waktu.

Kita tahu di balik layar RStudio akan menuliskan kode saat kita melakukan klik sana dan klik sini. Karena itu kita ketik saja kode tersebut pada R Script

# Set Directory
setwd("D:/basangdata/audit_with_r_intro")

# Load Data
Sales.SalesOrderHeader <- read.delim("Sales.SalesOrderHeader.csv")

Kode yang diawali tanda pagar tidak akan dieksekusi oleh R Console. Bagian tersebut diisi dengan komentar agar kita di masa depan tahu apa yang dikerjakan oleh kode di bawahnya.

Untuk menjalankan kode tersebut pada R Console, block/select bagian kode tersebut lalu tekan ikon Panah Hijau (Run) atau gunakan tombol keyboard Ctrl+Enter.

Due Date Range

Kolom yang akan digunakan untuk menghitung umur piutang adalah DueDate, berisi tanggal jatuh tempo masing-masing piutang.

Sebelumnya kita perlu tahu kolom ini memuat data dari dan hingga tanggal berapa. Fungsi min dan max dapat digunakan untuk mengetahui tanggal paling tua dan termuda. Selain itu fungsi range juga dapat digunakan untuk mendapatkan kedua tanggal tersebut.

# Max DueDate
max(Sales.SalesOrderHeader$DueDate)

# Min DueDate
min(Sales.SalesOrderHeader$DueDate)

# Range DueDate
range(Sales.SalesOrderHeader$DueDate)

Data penjualan tersebut memuat piutang dengan jatuh tempo 12 Juni 2011 hingga 12 Juli 2014.

Pada R untuk menggunakan/mengakses kolom dapat menggunakan tanda $ setelah nama variabel disusul dengan nama kolom yang diperlukan.

2011 Sales

Untuk mempermudah tulisan ini, diilustrasikan audit dilakukan pada 9 Januari 2012 sehingga penjualan yang akan diuji hanya yang terjadi pada periode 1 Januari 2011 hingga 31 Desember 2011.

Kita hanya perlu memfilter pada kolom OrderDate.

# 2011 Sales
sales2011 <- Sales.SalesOrderHeader[Sales.SalesOrderHeader$OrderDate <= "2011-12-31",]

Sekarang kita punya variabel sales2011 yang memuat data penjulan pada tahun 2011, sebanyak 1.607 baris.

December 31st 2011

Karena laporan keuangan menggunakan periode yang berakhir pada 31 Desember (2011) maka kita perlu mengetahui selisih hari antara 31 Desember 2011 dan kolom DueDate.

# Fiscal year ended December 31st 2011
end.of.period <- as.Date('2011-12-31', '%Y-%m-%d')

Kode di atas menggunakan fungsi as.Date untuk membuat variabel tanggal. Fungsi tersebut membutuhkan setidaknya teks tanggal dan format (dalam contoh ‘%Y-%m-%d’). Untuk mengetahui bagaimana penggunaan format tersebut (misal urutan tahun, bulan dan tanggalnya berbeda) dapat merujuk ke sini.

Selisih hari (DueDate dan 31 Desember 2011) akan kita tampilkan dalam sebuah kolom baru, kita beri nama ElapsedDays.

# Convert DueDate column to Date
sales2011$DueDate <- as.Date(sales2011$DueDate, '%Y-%m-%d')

# DueDate to Dec 31st 2011
sales2011$ElapsedDays <- end.of.period - sales2011$DueDate

Pertama kita perlu mengubah kolom DueDate menjadi tanggal, caranya mirip dengan kode sebelumnya, menggunakan as.Date.

Selanjutnya menambahkan kolom baru, dapat langsung menggunakan namavariabel$kolombaru (sales2011$ElapsedDays) dan memberikan nilai yang diinginkan.

Jika kita lihat variabel sales2011, kolom paling kanan, terdapat kolom ElapsedDays yang baru kita buat.

Tips: jika merasa kolom yang ditampilkan terlalu banyak, kita dapat menggunakan kode untuk menampilkan hanya kolom yang diinginkan. Kode di bawah ini akan menampilkan kolom DueDate dan ElapsedDays saja.

# View some columns
View(sales2011[c('DueDate', 'ElapsedDays')])

Aging Schedule

Sekarang kita siap untuk mengelompokkan piutang berdasarkan umurnya. Kita akan membuat kolom untuk masing-masing kelompok, kolom tersebut diisi dengan nilai piutang dari kolom TotalDue.

# Age 0-30 days
sales2011$elap.0.30 <- sales2011$TotalDue
sales2011$elap.0.30[sales2011$ElapsedDays > 30] <- 0

# Age 31-60 days
sales2011$elap.31.60 <- sales2011$TotalDue
sales2011$elap.31.60[sales2011$ElapsedDays < 31 | sales2011$ElapsedDays > 60] <- 0

# Age 61-90 days
sales2011$elap.61.90 <- sales2011$TotalDue
sales2011$elap.61.90[sales2011$ElapsedDays < 61 | sales2011$ElapsedDays > 90] <- 0

# Age >90 days
sales2011$elap.90 <- sales2011$TotalDue
sales2011$elap.90[sales2011$ElapsedDays < 91] <- 0

Kita diskusikan beberapa yang menarik. Kode di bawah ini menambahkan kolom elap.0.30 yang awalnya berisi apapun isi dari kolom TotalDue. Kemudian pada baris yang kolom ElapsedDaysnya bernilai lebih besar dari 30 (hari), kolom elap.0.30nya diubah menjadi 0.

# Age 0-30 days
sales2011$elap.0.30 <- sales2011$TotalDue
sales2011$elap.0.30[sales2011$ElapsedDays > 30] <- 0

Untuk kolom selanjutnya, yaitu umur piutang 31 hari sampai 60 hari, kolom yang menampung nilai penjualan adalah kolom elap.31.60. Berbeda dengan kolom elap.0.30, pada kolom elap.31.60 perlu dinolkan baris yang memuat data ElapsedDays kurang dari 31 dan lebih dari 60.

# Age 31-60 days
sales2011$elap.31.60 <- sales2011$TotalDue
sales2011$elap.31.60[sales2011$ElapsedDays < 31 | sales2011$ElapsedDays > 60] <- 0

Save

Setelah selesai kita dapat menyimpan data yang telah diolah menjadi file csv dengan kode seperti ini.

# Save to csv
write.csv(sales2011, 'sales2011.csv')

Jangan lupa juga untuk menyimpan R Script yang berisi kode tadi agar dapat dimanfaatkan di masa depan.

Konklusi

Relatif mudah, hanya butuh <40 baris kode. Dan bagusnya lagi, kode tersebut dapat didaur ulang untuk data lain dengan sedikit perubahan (misal mengubah tanggal akhir periode pelaporan akuntansi).

Akan lebih terasa manfaat, dari kerepotan mengetik kode, saat kita berhadapan dengan data berukuran besar yang tak dapat lagi ditangani oleh Excel di komputer kita.

RStudio merupakan alat bantu yang sangat disesalkan jika tak digunakan karena memberikan kompromi yang memadai untuk sesiapa yang tak ingin kehilangan kendali (dengan menulis kode untuk mengolah data) namun memberikan cukup banyak antarmuka grafis untuk mempermudah hidup (menulis semua hal melalui kode tidaklah nyaman).

Referensi

Cover Photo by Dmitry Vechorko on Unsplash

Published by Aan Subarkah

One Reply to “Audit Data Analytics dengan R – Aging Schedule”

  1. Pingback: Audit Data Analitics dengan R – Query pada Database

Leave a Reply

Your email address will not be published. Required fields are marked *