Cara kami membuat ulang prakiraan buruk menjadi prakiraan sedikit lebih baik (lanjutan)

Dalam artikel sebelumnya, saya berbicara tentang bagaimana, untuk keperluan perkiraan pendapatan, orang membangun file excel yang besar dan kompleks ( Anda dapat membaca di sini ). Kami memutuskan untuk campur tangan dalam rasa malu ini dan mengusulkan untuk mengulang model prakiraan sehingga ada lebih sedikit kesalahan, pengoperasian yang lebih mudah, dan ada fleksibilitas dalam konfigurasi.

Apa masalah utama dalam model yang dijelaskan:

1. Data, model, dan tampilan dicampur menjadi satu entitas. Karena itu, perubahan setidaknya satu elemen menghancurkan seluruh monolit ini.
2. Perhitungan berlebihan untuk pemrosesan manual, yang menimbulkan kesalahan dan kesalahan ketik dalam jumlah besar.

Apa yang kami sarankan:

1. Dalam model awal, data awal yang dibangun tidak muncul di mana pun. Kami mengusulkan untuk memasukkan data ini dalam format bentuk normal ke-2 dalam file Excel itu sendiri ke dalam 2 lembar terpisah (penjualan dan jumlah klien). Untungnya, data penjualan dalam agregasi kami per bulan hanya puluhan ribu baris, bukan jutaan. Kami juga mengonfigurasi untuk menerima data ini menggunakan Power Query langsung dari database.
2. Kami membuat lembar pemodelan, yang terdiri dari 3 blok:
   
   • Tabel Pivot Pendapatan
   • Tabel ringkasan jumlah klien
   • Tabel penyelesaian pemeriksaan rata-rata
     Setiap tabel pivot adalah tabel pivot yang dibangun di atas sumber data dalam rincian yang diperlukan untuk simulasi saat ini oleh departemen dan unit, dalam rincian yang diperlukan pada periode (bulan).
     
3. Dalam Lembar Simulasi, kami membangun model peramalan sederhana berdasarkan seri waktu historis. Kami menambah jumlah pelanggan dan tagihan rata-rata, dan total perkiraan pendapatan dianggap sebagai produk dari nilai-nilai ini. Setelah meninjau data, kami datang dengan 3 model peramalan: median untuk periode terakhir, perataan triple eksponensial dan zeroing (ketika kami membutuhkan 0 perkiraan).
   
4. Perhitungan penerimaan rata-rata (fakta) dan pendapatan (perkiraan) dibuat bukan dengan mereferensikan sel, tetapi menggunakan VLR dan tanda offset, yang membuat perhitungan tahan terhadap perubahan dalam data awal.
   
5. Jelas bahwa sekarang model tidak dapat dibaca oleh pengguna, karena ada terlalu banyak makna. Untuk melakukan ini, kami membangun lembar unit yang terpisah. Setiap lembar memiliki sel yang memilih data mana yang harus dirangkum dalam lembar ini. Berdasarkan VLOOKUP, kami menarik data dari lembar model ke lembar.
6. Pembuatan 30 lembar per unit dilakukan sesuai dengan prosedur khusus. Pertama, lembar pertama dibuat, salah satu unit, yang berisi semua nama departemen yang mungkin. Jika tidak ada departemen di unit, maka rumus menariknya kembali ke 0. Untuk membuat semua 30 unit, kami membuat duplikat dan mengubah nama di sel kontrol (digunakan untuk menghasilkan kunci VLOOKUP) dan kami memiliki data yang diperlukan dalam bentuk presentasi. VLOOKUP dapat menggunakan lebih dari 1 sel sebagai kunci, jika Anda menggunakan triknya: gabungkan sel-sel yang Anda butuhkan menjadi satu menggunakan penggabungan (fungsi CLIP atau simbol &).
7. Elemen telah ditambahkan ke formulir presentasi yang memungkinkan Anda untuk mengelola model: pengganda sederhana dengan nilai prediksi dari rata-rata cek dan angka. Elemen ini dikumpulkan pada lembar teknis khusus menggunakan fungsi INDIRECT, yang memungkinkan Anda untuk menggunakan tautan yang dibuat. Dari lembar teknis ini, semua koreksi ini ditransfer menggunakan VLOOKUP ke lembar model.
   
8. Lembar generalisasi tidak lagi merupakan penjumlahan dari lembar presentasi, tetapi dibuat dengan cara yang persis sama dengan semua lembar lainnya - dengan menjumlahkan data pada lembar model. Akibatnya, representasi adalah representasi murni dan tidak memiliki ketergantungan di antara mereka sendiri.

Apa yang kita dapatkan:

1. Selalu jelas dari angka mana kami mendapatkan data (karena kueri Power Query disimpan).
2. Kami dapat mengubah data tanpa merusak model.
3. Perubahan dalam struktur dan hierarki akan memerlukan modifikasi kecil (Anda hanya perlu mengubah nama dalam 1 lembar presentasi dan kemudian membuatnya duplikat).
4. Kami secara drastis mengurangi jumlah kesalahan potensial, karena Sebagian besar data diisi menggunakan rumus, tautan, dan kunci.
5. Pelanggan menerima ramalan interaktif, di mana ia dapat mengubah nilai sendiri dan segera menerima ramalan.
6. Kami dapat secara bersamaan memenuhi persyaratan bahwa kami membutuhkan data baik dalam hal tahunan maupun bulanan.
7. Dapat digunakan dalam periode anggaran berikutnya.
8. Anda dapat mengubah model peramalan jika ini menurut kami tidak sesuai.

Mengapa kami memutuskan untuk tetap menggunakan Excel, dan tidak mengulanginya pada beberapa teknologi lain?

1. Kami harus meninggalkan file ini dalam operasi karyawan saat ini. Di dalam Excel, lebih mudah bagi kami untuk menunjukkan bagaimana semua ini bekerja dan apa yang dapat mereka perbaiki.
2. Excel berupaya dengan tugas dan solusi lainnya - entitas yang berlebihan.
3. Pelanggan terbiasa dengan formulir ini dan "melatih kembali" biaya tenaga kerja tertentu yang tidak mampu kami bayar.

Berapa lama waktu yang kami perlukan: sekitar 5 hari kerja, di mana 1 orang menghabiskan 2-4 jam sehari, dan pada akhirnya kami melakukan tinjauan terhadap hasilnya.