Revolutionizing Sales Reporting: Automating Chatime Store Sales Analysis in Excel

Pendahuluan

Dalam lingkungan bisnis yang kompetitif, pemahaman yang mendalam tentang kinerja penjualan merupakan elemen kunci bagi kesuksesan sebuah toko. Sebagai seorang Assistant Store Manager di CHATIME, saya telah terlibat dalam mengelola dan menganalisis data penjualan dengan cermat untuk memastikan operasi toko berjalan lancar dan mencapai target penjualan yang ditetapkan. Pengalaman saya dalam mengekstrak data penjualan dari SAP dan mengolahnya dalam format Excel telah memperlihatkan betapa pentingnya efisiensi dan akurasi dalam proses pelaporan penjualan.

Dengan tujuan untuk meningkatkan efisiensi dan akurasi laporan penjualan, saya memulai proyek ini dengan fokus pada otomatisasi proses pelaporan penjualan toko. Proyek ini bertujuan untuk menciptakan sistem otomatis yang menghasilkan laporan penjualan yang rinci dan informatif secara otomatis. Melalui proyek ini, saya berharap dapat meningkatkan kualitas pengambilan keputusan manajerial dengan memberikan akses yang lebih cepat dan lebih akurat terhadap data penjualan.

Meskipun data penjualan telah dihasilkan melalui sistem Point of Sale (POS), staf toko masih harus memecah dan memasukkan data tersebut secara manual ke dalam laporan penjualan. Proses ini memakan waktu dan meningkatkan risiko kesalahan.

Dengan tujuan untuk meningkatkan efisiensi dan akurasi dalam pelaporan penjualan, proyek ini bertujuan untuk membuat formulir otomatis dan melakukan penambahan fungsi atau fitur pada report sales yang sudah ada sebelumnya menjadi report sales otomatis yang dapat mengambil data langsung dari formulir otomatis yang datanya didapat dari sistem POS dan mengisi tabel sales report secara otomatis.

-Gambar Sales Report Toko

Gambar di atas Menampilkan sales report toko awal. Pada kolum biru merupakan kolum proyeksi dan tanggal untuk mendapatkan target harian dan target month to date. Untuk medapatkan target proposisi yang ideal, pada hari weekday diberi nilai 1 dan pada hari weekend dimana merupakan terjadinya peningkatan customer diberi nilai 1,87. Untuk pengisian nilai dari tabel ini adalah manual, yaitu memasukan satu persatu sesuai dengan kondisi atau kategory value yang sesuai dihari tersebut. Ini bisa menjadi masalah disetiap pergantian bulan yang dimana mengganti tanggal, jumlah tanggal, kategori value dan kategory hari secara manual akan memakan waktu dan berulang.

Pada kolum hijau merupakan rumus yang tidak perlu diisi (sudah ada rumus sebelumnya) dan kolum biru muda merupakan kolum yang harus diisi oleh staff dimana tetap memerlukan input manual oleh staf. Hal ini tidak hanya memakan banyak waktu, tetapi juga berpotensi untuk menghasilkan kesalahan saat memasukkan data berulang setiap bulan.

Khususnya, untuk menghitung Total Transaksi (TC) dan Jumlah Transaksi, staf harus melakukan perhitungan manual karena tidak ada ringkasan atau total transaksi yang disediakan oleh sistem Point of Sale (POS). Hal yang sama berlaku untuk transaksi melalui mesin EDC bank, di mana staf harus menghitung jumlah transaksi secara manual dari seluruh bank dan pembayaran lainnya.

Untuk mengatasi masalah tersebut, saya bertujuan untuk:

1. Menambahkan fitur otomatisasi yang memungkinkan file sales report untuk secara otomatis menyesuaikan tanggal pada setiap pergantian bulan.
2. Membuat sebuah formulir baru yang akan menghitung total transaksi dan jumlah transaksi secara otomatis, dan hasilnya akan langsung terintegrasi ke dalam sales report, menghilangkan kebutuhan untuk input manual oleh staf.

Dengan langkah-langkah tersebut, saya berharap dapat mengurangi beban kerja staf, meningkatkan efisiensi, dan mengurangi potensi esalahan dalam proses pembuatan laporan penjualan.

Menambahkan fitur otomatisasi yang memungkinkan file sales report untuk secara otomatis menyesuaikan tanggal pada setiap pergantian bulan.

Langkah awal untuk membuat fitur ini yaitu membuat tabel tambahan sebagai indikator tabel lainya seperti tabel input bulan, tabel jumlah hari, tabel dummy angka, tabel master kondisi kategori, tabel tanggal merah dan tabel hari.

Tabel Input Bulan

Tabel ini merupakan tabel input atau masukan untuk memasukan angka yang sesuai dengan masing masing bulan sehingga kolum tanggal akan berubah otomatis mengikuti tabel ini. Contoh inputnya adalah 1 untuk bulan januari, 2 untuk bulan Februari dan seterusnya sampai dengan 12 untuk bulan Desember.

Tabel Jumlah Hari

Tabel Jumlah hari adalah tabel dengan berisikan jumlah hari pada bulan tersebut. Tabel ini akan saya isikan dengan formula =DAY dan =EOMONTH untuk mendapatkan angka terakhir pada bulan yang dipilih

Tabel Dummy Angka

Tabel dummy angka adalah tabel yang berisikan angka yang fungsinya sebagai pembantu kebutuhan tabel lainya

Tabel Kategori Value

Tabel ini merupakan sebagai indikator angka kategori value untuk membedakan nilai weekdays dan weekend.

Kategori Value untuk Weekdays (1): Angka ini mewakili bobot standar untuk hari kerja. Dalam kebanyakan kasus, nilai ini diatur menjadi 1 untuk menunjukkan bahwa setiap hari kerja memiliki bobot yang sama dalam perhitungan target harian.

Kategori Value untuk Weekend (1,8604651): Angka ini mewakili bobot yang lebih tinggi untuk akhir pekan. Dalam banyak bisnis ritel, penjualan cenderung lebih tinggi pada akhir pekan, sehingga perlu memberikan bobot yang lebih besar untuk hari-hari tersebut dalam perhitungan target harian. Angka ini dihitung berdasarkan proporsi target bulanan yang diinginkan untuk akhir pekan. Tetapi nantinya angka ini terutama pada weekend bisa diatur atau dikondisikan tergantung kebutuhan bisnisnya.

Tabel Tanggal Merah

Tabel Tanggal Merah merupakan tabel input yang dimasukan oleh user untuk memasukan tanggal merah pada tanggal tersebut. tabel ini juga berfungsi sebagai indikator tambahan pada hari weekend. Jadi jika saya mendefinisikan Weekdays sabagai hari kerja yaitu Senin sampai Jumat dan Weekend sebagai hari libur Sabtu dan Minggu, dengan adanya tabel tanggal merah, jika hari tersebut merupakan weekdays(contoh hari senin) tetapi pada hari itu adalah tanggal merah dan user memasukan tanggal tersebut pada kolum tanggal merah maka status dari hari senin ini adalah weekend bukan weekdays.

Tabel D2 Merupakan tabel input bulan yang baru dibuat. tabel tersebut menggunakan data validasi list untuk memunculkan list angka bulan yang tersedia dengan cara mengunakan source Dummy Numb pada tabel B8:B19 untuk mendapatkan angka 1 sampai dengan 12.

Tabel "Bulan" pada G1 yang sebelumnya String / karakter biasa berupa nama bulan contoh "April" saya ganti menjadi formula =Date agar bisa mengikuti input bulan pada tabel "input bulan" dengan formula =DATE(H1;D2;1). Formula Date berfungsi mengembalikan date time dengan 3 buah parameter yaitu (Tahun;Bulan;Tanggal). H1 Merupakan kolum Tahun yaitu 2024, D2 Merupakan kolum Bulan yang merupakan Tabel Input bulan yang dibuat sebelumnya dan 1 merupakan tanggal yang saya masukan.

Tabel D4 Merupakan tabel jumlah hari yang berfungsi sebagai indikator dan pembatas total hari dalam dalam report karena setiap bulan memiliki jumlah tanggal yang berbeda, maka dari itu perlu dibuatkan tabel jumlah hari sebagai pembatas dengan formula =DAY(EOMONTH(G1;0)). Fungsi formula EOMONTH adalah mengembalikan nilai tanggal terakhir pada bulan tersebut. Dalam parameternya terdapat kolum G1 yaitu tempat tabel date time / bulan yang sebelumnya dibuat dan nilai 0 untuk mengembalikan tanggal akhir bulan dari tanggal yang dibuat. karena disini mencari bulan sekarang, maka nilainya adalah 0. Lalu saya membungkusnya dengan formula DAY untuk mendapatkan format hari(day) dari rumus tersebut.

Merubah Nilai dari Setiap Tabel dari Manual menjadi Formula

Setelah saya selesai membuat tabel tambahan untuk fungsi pendukung lainya, sekarang saya akan merubah semua nilai atau value yang sebelumnya dilakukan dengan manual jika berganti bulan dengan formula agar jika berganti bulan semua nilai atau valuenya bisa berubah secara otomatis

-Kolum Date dan Hari

Seperti yang terlihat pada gambar diatas, kursor menunjukan pada F8 kolum date tanggal 1 April 2024. Value atau nilainya masih berformat umum tanggal yaitu 01/04/2024. Jika berganti bulan user akan melakukan pergantian tanggal satu persatu sebanyak jumlah tanggal pada bulan itu. Maka dari itu disini saya akan menggantinya dengan formula =DATE($H$1;$D$2;B8) agar menjadi otomatis.

H1 Merupakan kolum Tahun, diberikan tanda $ atau absolut cell agar bisa dicopy untuk cell bawahnya.

D2 Merupakan kolum input bulan diberikan tanda $ atau absolut cell agar bisa dicopy untuk cell bawahnya.

B8 Merupakan kolum Dummy numb yang sesuai dengan masing masing angka. Tidak perlu diberikan $ agar cellnya bersifat relatif mengikuti angka Dummy numb dibawahnya.

Sekarang Kolum Date akan berubah otomatis mengikut inputan bulan jika dilakukan perubahan bulan.

Untuk kolum "Hari" saya menggunakan formula "=" atau mirroring untuk mencerminkan nilai yang sama dengan kolum Date tetapi diberikan tambahan format cell agar menampilkan Hari(day)nya saja. Seperti contoh pada kolum Hari D8 berisikan formula =F8, yang berarti nilai D8 sama dengan F8. Tetapi karena adanya format cell 'day' maka nilai hari pada D8 menjadi "Senin" bukan 1 April 2024. copy formulanya untuk menyalin pada cell dibawahnya

-Kolum Kategori Value dan Value

Kolum Kategori Value dan Value merupakan kolum indikator untuk menentukan nilai apakah dihari tersebut merupakan weekdays atau weekend. Kolum kolum ini saling berhungan.

Senin - Jumat merupakan Weekdays maka bernilai 1

Sabtu - Minggu meruapakn Weekend maka bernilai 1,8604651.

Jadi disini saya bisa menggunakan Formula IF pada kolum Kategori untuk menentukan Kategori Value dengan gambaran formula:

JIKA Cell N bernilai "Sabtu" ATAU "Minggu" Maka mengembalikan nilai "Weekended", SELAIN dari itu makan Weekdays

Lalu bagaimana jika Hari itu merupakan Weekdays tetapi tanggal tersebut adalah tanggal merah? maka hari tersebut adalah Weekend dan bernilai 1,8604651.

Untuk itu saya akan membuat kulom tambahan bernama "Dummy Days" yang berisikan Indikator tanggal untuk bisa menerapkan tanggal merah walaupun hari itu adalah weekdays.

Setelah membuat Kolum "Dummy Days", saya mengisi kolum tersebut dengan formula =TEXT(F8;"dddd"). Fungsi formula text adalah mengembalikan nilai berupa teks dengan 2 parameter yaitu teks dan format. Pada parameter teks berisi cell F8 yaitu kolum Date dan pada format berisi "dddd" yang berarti mengambil format day karena saya ingin menampilkan hari berdasarkan kolum date dengan tipe data "teks" agar bisa diolah lebih lanjut.

Selanjutnya setelah membuat kolum dummy day, sekarang saya membuat formula if untuk menentukan kategori "Weekend" atau "Weekdays" dengan formula =IF(OR(A8="Saturday";A8="Sunday");$A$3;$A$2).

Untuk penjelasan formula ini sama dengan gambaran formula yang sudah saya jelaskan sebelumnya, yaitu JIKA Cell N bernilai Sabtu ATAU Minggu Maka nilainya Weekended SELAIN ITU Weekdays

Karena sudah mendapatkan nilai dari Kategori, sekarang saya akan mencari kolum Kategori Value dengan Formula =IF(E8=$A$2;$B$2;$B$3). Penjelasan formula ini adalah dibaris yang sama, jika kolum kategori value adalah Weekdays, maka akan memberikan nilai 1, selain dari weekdays maka memberikan nilai 1,8604651.

Sekarang semua cell dari setiap kolum sudah berganti menjadi formula dengan otomatis mengikuti pergantian indikator tabel "input bulan". Untuk korelasi antara Hari, Kategori dan Kategori Value juga sudah sesuai dimana:

Senin - Jumat adalah Weekdays maka Kategori Valuenya = 1

Sabtu - Minggu adalah Weekend maka Kategori Valuenya = 1,8604651.

Tetapi terdapat 1 masalah yaitu kondisi tanggal merah. Sebagai contoh seperti pada gambar sebelumnya, dimana pada tabel "Tanggal merah" saya memasukan tanggal 10 dan 11 bertepatan dengan Hari Raya Idul Fitri yang mana seharusnya untuk tanggal merah harus memberikan kondisi weekend dan bernilai 1,8604651 tetapi pada gambar bernilai weekdays dan bernilai 1. Masalah ini terjadi karena pada formula sebelumnya tidak ada indikator atau filter yang membedakan tanggal merah dan tanggal biasa. Sekarang saya akan mencoba menambahkan formula agar pada tanggal tersebut bisa menerapkan tanggal merah atau weekend pada hari weekdays.

Setelah saya menambahkan formula berganda atau nesting pada kolum Dummy Days, Status pada tanggal 10 dan 11 sudah berubah sesuai dengan ketentuan tanggal merah. formula ini adalah:

=IF(OR(B17=$F$1;B17=$F$2;B17=$F$3;B17=$F$4);"Saturday";TEXT(F17;"dddd"))

Penjelasan dari formula tersebut adalah:

Dibaris yang sama, Jika pada kolum dummy numb nilainya sama dengan salah satu atau lebih nilai pada tabel "tanggal merah"(disini dimasukan 10 dan 11), makan nilai dummy days akan berubah menjadi "Saturday" yang membuat nilainya menjadi "weekdend". Selain dari itu maka akan memberikan nilai yang sama dengan kolum "Hari".

Dengan ini Menambahkan fitur otomatisasi yang memungkinkan file sales report untuk secara otomatis menyesuaikan tanggal pada setiap pergantian bulan telah selesai. Sekarang saya akan mencoba merubah bulan nya untuk melakukan percobaan dengan ketentuan:

1 .Sales report baru untuk bulan Mei

2 .Target untuk bulan mei adalah 10 JT

3 .Untuk bulan Mei memiliki jumlah total 31 Hari

4 .Sebagai contoh, Tanggal 29 - 31 Mei merupakan tanggal merah

Setelah malakukan percobaan:

1 .Sales report baru untuk bulan Mei:

Kolum "tanggal", "hari" serta "kategori" berhasil ikut berubah sesuai dengan ketentuan

2 .Target untuk bulan mei adalah 10 JT:

Berdasarkan pembagian target yang sudah ditetapkan yaitu 10JT, telihat setelah di breakdown menjadi target harian dan dilakukan SUM atau total Month to Date(MTD), jumlah target sesuai

3 .Untuk bulan Mei memiliki jumlah total 31 Hari:

jumlah tanggal berhasil berubah dimaa pada bulan sebelumnya yaitu April hanya memiliki 30 tanggal, saat dirubah kebulan Mei jumlah tanggal juga berubah menjadi 31

4 .Sebagai contoh, Tanggal 29 - 31 merupakan tanggal merah:

Terlihat pada gambar diatas pada tanggal 29 - 31 yang jatuh pada hari Rabu - Jumat seharusnya memiliki kondisi weekdays tetapi berhasil berubah menjadi weekend sesuai ketentuan tanggal merah.

Saya juga menambahkan conditional formating pada kolum "Kategori" dimana jika berstatus weekend akan berwarna merah agar lebih mudah dilihat.

Membuat sebuah formulir baru yang akan menghitung total transaksi dan jumlah transaksi secara otomatis

Lanjut menyelesaikan permasalahan kedua yaitu Membuat sebuah formulir baru yang akan menghitung total transaksi dan jumlah transaksi secara otomatis, dan hasilnya akan langsung terintegrasi ke dalam sales report, menghilangkan kebutuhan untuk input manual oleh staf.

-Gambaran report POS

Sebelum membut form untuk menghitung transaksi, terlebih dahulu melihat gambaran report dari POS untuk melihat kolum apa saja yang dibutuhkan untuk menampung transaksi data dari POS. Gambar diatas merupakan tampilan report by payment dari POS, maka dari itu disini saya akan membuat form report di sheet baru berdasarkan tabel report POS. Nantinya saya akan membuat 2 jenis Tabel yaitu tabel form input untuk menampung data transaksi dari POS dan form hasil report untuk menampilkan hasil perhitungan dari form input

Form Input report

Berdasarkan gambar yang saya tampilkan diatas, pada form input report ini terdapat 6 kolum yang saya buat untuk menampung input transaksi dari POS yaitu:

Kolum Receipt : Berisi nomer transaksi

Kolum Doc no : Berisi no DOC

Payment : Berisi tipe Pembayaran

ID : Berisi ID pembayaran

Holder : Berisi holder atau pengguna pembayaran

Amount : Berisi nominal jumlah transaksi

Sebenernya pada praktek langsung saya hanya memerlukan kolum Receipt, Payment dan Amount untuk melakukan perhitungan. tetapi agar mempermudah memindahkan data atau copy data dari POS ke Form ini, saya membuatnya sesuai agar bisa fit in sesuai kolumnya.

Form Hasil Report

Setelah berhasil membuat form input report, sekarang saya akan membuat Form hasil report untuk menampilkan hasil perhitungan dari transaksi. sebelum membuatnya, saya perlu melihat pada kolum apa saja yang dibutuhkan dan diperlukan untuk mengisi report sales yang sudah dibuat sebelumnya

Berdasarkan Gambar diatas untuk mengisi kolum yang diperlukan untuk sheet sales report bulanan adalah Kolum:

Act Sales = Aktual sales / nominal omset Store

TC ALL = Transaction Count, merupakan jumlah transaksi keseluruhan

Transportasi Makanan Online(GRAB,GOJEG,SHOPPE) = sales / nominal omset pada masing masing transportasi online

TC Transport Makanan Online= Transaction Count, merupakan jumlah transaksi pada masing masing transportasi online

Pembayaran bank dan lain lain = Aktual sales / nominal omset dari bank yang tersedia seperti bca mandiri dll serta pembayaran cash

Setelah mendapatkan gambaran apa saja yang dibutuhkan untuk membuat Form hasil report, sekarang saya akan membuat formnya

Gambar diatas merupakan tampilan dari form hasil report yang berikan 4 kolum utama yaitu Payment, TC, SALES dan PERSENTASE. Ada beberapa Payment tambahan seperti Total OJOL untuk menghitung semua Sales dan total transaksi ojol dan Summary group untuk menghitung jumlah sales dan tc seperti GROSS, NET, TC dan AC. Sebagai contoh, sheet ini akan diberi nama "TGL 1". Karena nantinya untuk sistem kerja form ini adalah setiap cell yang terdapat pada form hasil report akan di link dengan file sales report bulanan yang sebelumnya saya buat sesuai dengan tanggalnya. maka dari itu nantinya perlu membuat sheet yang sama dari "TGL 1" - "TGL 31" yang cell nya terhubung atau link sesuai dengan sheet dan tanggalnya.

Sebagai contoh saya hanya akan membuat untuk sheet "TGL 1". kedepanya jika ingin membuat sheet tanggal lainya, cukup melakukan copy sheet dan sedikit penyesuain pada formulanya.

Membuat Formula pada Form Hasil Report

Sekarang saya akan membuat formula untuk cell cell pada form hasil report. Sebagai contoh, saya akan membuat formula TC, Sales dan persentase pada baris payment "OVO MOBILE" dan Group Summary(GROSS,NETT,TC,AC).

-TC adalah transaksi count, untuk mendapatkan jumlah transaksi dari OVO MOBILE saya menggunakan rumus =COUNTIF dengan indikator kolum payment OVO MOBILE.

-Sales adalah jumlah nominal / sales transaksi, untuk mendapatkan jumlah nomilan transaksi dari "OVO MOBILE", saya menggunakan =SUMIF untuk menjumlahkan nominal dengan indikator kolum payment "OVO MOBILE"

-Persentase adalah Persentase sales dibandingkan dengan total seluruh transaksi. untuk mendapatkan nilai transaksi adalah nilai sales "OVO MOBILE"/ GROSS * 100

-GROSS adalah total seluruh sales sebelum kena pajak atau pendapatan kotor

-NETT adalah total seluruh sales setelah dikurangi pajak atau pendapatan bersih. untuk mendapatkan NETT adalah GROSS / 1,1(Nilai Pajak)

-AC adalah Average Count, didapatkan dengan NETT/TC

Gambar diatas merupakan penerapan Formula untuk kolum payment "OVO MOBILE" yang Form input salesnya telah diisi data dummy sebagai contoh penerapan.

Sekarang saya akan coba melengkapi semua cell pada hasil report dengan melakukan copy dan sedikit penyesuaian cell absolut dan relatif.

-Gambar Form Hasil Report yang sudah terisi

Sekarang semua cell telah terisi dengan formula dan Form dapat digunakan. Saya akan melakukan uji coba mengisi form input data dengan data dummy yang saya dapatkan lewat AI Chat GPT dengan ketentuan khusus:

ketentuan khusus yaitu:

Payment : OVO MOBILE

Jumlah Transaksi : 10

Jumlah Amount / Nominal : 1000000

Berikut Gambaran untuk meminta Data Dummy lewat Chat - GPT

Berikut adalah hasil generate data Dummy oleh Chat GPT

Sekarang saya akan mencoba memasukan semua data ini ke form input dan melihat apakah seluruh perhitungan dan formula sudah benar. Sebagai tambahan informasi, karena data yang didapatkan dari AI sedikit berantakan dan tidak bisa langsung diterapkan ke form input, saya menggunakan "Text To Column" di excel agar sedikit rapih dan bisa masuk ke form yang sudah saya buat.

Seperti yang terlihat pada gambar, pada form hasil report telah sesuai dengan ketentuan. Lalu saya akan menghubungkan semua cell yang diperlukan report sales pada tanggal 1 dengan sheet "TGL 1". Sebagai contoh pada sheet "Report Sales" kolum Act Sales, TC ALL dan grab pada baris 1, saya memasukan formula "=" untuk membuatnya terhubung / link dengan sheet "TGL 1" cell sales bari GRAB sehingga pada sheet report sales otomatis terisi sesuai dengan ini yang ditampulkan Form hasil.

Berikut gambar untuk Sales reportnya

Seperti yang terlihat pada gambar diatas, pada baris 8, semua input yand ditandai dengan cell berwarna abu abu sudah terisi secara otomatis. Dengan ini staff tidak harus melakukan input manual ataupun diketik. Cara ini juga mempercepat pekerjaan staff dan meminimalisir Human error.

Untuk baris pada tanggal lainya yang belum dibuat (TGL 2 - TGL31), untuk membuatnya cukup melakukan Copy pada Sheet TGL 1 dan dilakukan sedikit penyesuaian seperti nama sheet dan formulanya.

Untuk sentuhan akhir, saya menambahkan beberapa chart untuk keperluan analisis toko. Chart ini juga terhubung dengan data Sales report sehingga saya bisa melihat perkembangan sales lewat chart ini. berikut adalah gambar chartnya

Penutup

Dengan penuh dedikasi dan kerja keras, proyek ini berhasil diselesaikan. Ini adalah tonggak penting dalam perjalanan saya dalam memperluas keterampilan saya dalam analisis data dan pengembangan solusi yang efisien. Saya berharap bahwa proyek ini tidak hanya menjadi bukti kompetensi saya, tetapi juga menjadi landasan untuk pencapaian lebih besar di masa depan. Terima kasih atas kesempatan ini dan semoga proyek ini memberikan manfaat yang signifikan bagi saya dan bagi orang lain