Actuarial Computing Excel Access VBA Repo
1.0.0
Komputasi Aktuaria (MAT 253, ISU)
Lab kode ini berfokus pada penggunaan VLOOKUPS untuk mengisi tabel yang tercantum di bawah untuk jumlah klaim, dengan merujuk pada tabel pertama pada tab data. Ini melibatkan pemanfaatan nilai dalam baris untuk menyelesaikan parameter ke-3 dari fungsi VLOOKUP. Ini juga melibatkan penggunaan referensi sel absolut dan relatif yang benar sehingga fungsi yang sama dapat disalin di seluruh area KUNING.
Lab kode ini juga berfokus pada penggunaan HLOOKUP untuk mengisi tabel menggunakan data dari tabel kedua pada tab Data 1. Untuk parameter ke-3 HLOOKUP, kita menggunakan fungsi MATCH dengan kunci pencocokan yang sesuai dan referensi array ke vektor dengan daftar tahun yang tersedia
Kodenya melibatkan mengikuti instruksi di bawah ini:
Dalam code lab ini, kita membuat grafik yang menunjukkan frekuensi klaim aktual dan tingkat keparahan klaim aktual pada sumbu y.
Karena skala untuk masing-masing rangkaian ini sangat berbeda, kami menggunakan dua sumbu berbeda untuk menampilkan rangkaian yang berbeda.
Sumbu x menunjukkan periode # (kolom A). Setiap rangkaian ditampilkan sebagai titik, dengan garis penghubung.
Setiap rangkaian diberi label frekuensi atau tingkat keparahan yang sesuai.
Dengan menggunakan pernyataan IF, kami menghitung nilai sekarang Aktuaria untuk masing-masing orang dalam daftar pada tab "masalah 1". - Rumus APV = Nilai nominal * Kapak - Kapak bervariasi berdasarkan jenis kelamin dan status perokok dan dapat ditemukan pada 4 tab untuk setiap kasus. Untuk memeriksa jawabannya, hasil kebijakan pertama harus memiliki APV = 1,1238.0 Pada tab “Masalah 1”, kolom A berisi string teks yang merupakan gabungan dari 4 bidang berbeda: Nomor_Polis, Tanggal_Efektif, Tanggal_Kedaluwarsa, Premium. Gunakan koma (,) sebagai pembatas untuk memisahkannya menjadi 4 kolom. Anda dapat menggunakan alat atau fungsi apa pun dalam Excel untuk melakukannya.
KAMI menyiapkan laporan PivotTable di lembar kerja baru yang disebut "Masalah 1" dari data pada tab 'Koleksi' (kisaran A1:D2771). Cantumkan 'Jumlah Pengumpulan' pada label baris, dan buat 4 kolom: 1. Jumlah Premi 2. Jumlah Kerugian 3. Rasio Kerugian = Kerugian / Premi 4. Jumlah Polis, ditampilkan dalam % kolom.
Pada tab “Regresi”, gunakan teknik regresi linier sederhana (y=a+bx) untuk memprediksi berat badan seseorang menggunakan tinggi badannya. Anda bisa menggunakan metode apa pun yang tersedia di Excel untuk mendapatkan estimasi parameter.
Anda adalah aktuaris penetapan harga untuk Perusahaan Asuransi ABC, sebuah perusahaan asuransi mobil pribadi kecil dengan pendapatan premi sekitar $300 juta per tahun. Salah satu tanggung jawab pekerjaan Anda adalah mengembangkan indikasi tingkat tarif secara berkala, serta penyesuaian terhadap faktor penilaian Anda. Atasan Anda telah meminta Anda menyusun suatu proses untuk menyederhanakan proses indikasi untuk mengembangkan tarif yang diindikasikan untuk tahun 2011. Untuk melakukannya, dia telah memberikan instruksi berikut serta gambaran tampilan spreadsheet yang dia inginkan.
Dia juga meminta Anda untuk memberikan cara terpisah baginya untuk mengawasi tren premium murni di semua negara bagian, dan membandingkannya dengan tren nasional (CW). Dia ingin poin sederhana dan
klik metode untuk melakukan ini, jadi Anda telah menyarankan PivotChart untuk tujuan ini.
Mengembangkan indikasi tarif di ABC melibatkan beberapa langkah termasuk: • Analisis Tren • Pengembangan Faktor Proyeksi Kerugian berdasarkan Tren • Pengembangan Faktor Deductible dan Kelas (Usia & Jenis Kelamin) yang terindikasi • Pengembangan Hasil Investasi • Pengembangan indikasi tarif keseluruhan
Untuk mengembangkan indikasi tarif, Anda telah diberikan informasi berikut: • Departemen TI telah memberikan informasi rinci premi dan kerugian untuk semua polis 2007-2009 dalam file teks dengan lebar tetap. File ini memiliki sekitar 1 juta catatan, sehingga harus diproses terlebih dahulu di Access. • Anda juga memiliki salinan data tren industri Jalur Cepat terbaru di database Access. • Anda memiliki spreadsheet Excel yang berisi kepemilikan dan pembelian saham perusahaan, serta harga historis saham tersebut selama 4 tahun terakhir.
-Proses indikasi tarif Anda akan mencakup output berikut (dijelaskan secara lebih rinci di bawah): • Database Access yang memiliki kueri yang menghasilkan data yang dapat disalin ke Excel untuk setiap negara bagian. • Spreadsheet Excel yang menunjukkan perhitungan rata-rata hasil investasi tahun 2007-2009. • Sebuah spreadsheet Excel yang menghitung perubahan tarif yang ditunjukkan, setelah menempelkan output dari permintaan akses dan hasil investasi ke dalamnya.
Spreadsheet ini akan memungkinkan pengguna untuk menempelkan keluaran akses untuk negara bagian lain ke dalam Excel, dan secara otomatis menghasilkan tarif yang ditunjukkan tanpa pembaruan tambahan apa pun. • Spreadsheet Excel dengan PivotChart yang menampilkan tren CW dan tren Negara. Terdapat contoh tampilan keluaran dari lembar kerja indikasi tarif.
Database Access disediakan. Basis data tersebut sudah berisi tabel bernama TrendData, yang berisi data tren industri. Anda juga telah diberikan data kebijakan terperinci di policydata.txt. Tata letak file teksnya adalah sebagai berikut: Bidang Pos 1-2 Kunci 3-4 Negara 5-8 Deductible 9-14 Kode Kelas 15-18 Tahun 19-24 Premi 25 Indikator apakah polis mempunyai klaim 26-35 Jumlah Klaim
**Catatan pada bidang Kunci**
Please use Access to add a primary key. The keys field in the input dataset is truncated. (Thus not unique to each record.) However, it will not impact your calculations.
You should import the text file with the policy data into an Access table.
Di Access, Anda harus membuat kueri yang menghasilkan informasi berikut:
Informasi Premi/Kerugian Perusahaan: NEGARA (Kelompok Berdasarkan) TAHUN (Kelompok Berdasarkan) DEDUCT (Kelompok Berdasarkan) KELAS (Kelompok Berdasarkan) Hitungan Polis (Hitungan) PREM (Jumlah) CLAIM_IND (Jumlah) LOSS_AMOUNT (Jumlah)
Anda harus menyetel kueri agar memiliki klausa Where untuk negara bagian. Anda dapat mengubah status ke status mana pun yang sedang Anda kerjakan. Informasi Tren Jalur Cepat Industri: STATE (Kelompokkan berdasarkan) YYYYQ (Kelompokkan berdasarkan) Cov (Kelompokkan berdasarkan) CW_CARYEARS (Jumlah) CW_PDCOUNT (Jumlah) CW_PDAMT (Jumlah) STATE_CARYEARS (Jumlah) STATE_PDCOUNT (Jumlah) STATE_PDAMT (Jumlah)
Bidang CW adalah ringkasan berdasarkan semua data untuk semua negara bagian. Bidang ringkasan STATE adalah jumlah bidang untuk negara bagian tertentu. Sekali lagi, Anda harus menyiapkan kueri untuk klausa Where untuk menentukan status yang akan dihasilkan.
Perhatikan bahwa untuk mendapatkan ringkasan CW dan ringkasan STATE pada kueri yang sama, Anda harus menggabungkan keluaran dari dua kueri terpisah (satu di tingkat negara bagian, dan satu lagi di tingkat CW) dan menggabungkan hasilnya berdasarkan YYYQ dan COV.
Spreadsheet yang disediakan memiliki dua tabel. Satu tabel berisi harga saham dari waktu ke waktu untuk saham-saham di S&P 500. Perusahaan ABC memiliki sebagian dari saham-saham tersebut. Departemen Investasi telah memberikan ringkasan saham-saham yang dimiliki pada awal tahun (BOY) 2006, serta saham-saham yang dibeli pada 1/1/2007, 1/1/2008, dan 1/1/2009. Anda perlu menghitung hasil investasi untuk tahun 2007, 2008, dan 2009, serta rata-rata aritmatika dari hasil 3 tahun. Demonstrasi perhitungan disertakan dalam handout. Anda harus mengisi spreadsheet pada lembar kerja Perhitungan Hasil Investasi. Nilai yang Anda hitung pada lembar kerja ini akan dimasukkan pada lembar kerja Indikasi Tarif.
Output dari Access harus ditempelkan ke tab Input Data di lembar kerja. Jangan ragu untuk menambahkan kolom indeks apa pun ke tab ini yang mungkin berguna bagi Anda nanti. Anda juga harus bisa memasukkan Nama Negara pada tab itu dan membuat nama Negara yang dihasilkan mengalir ke semua header Lembar Kerja di lembar kerja (jadi jika Anda menempelkan data untuk negara bagian baru, Anda hanya perlu mengubah nama negara bagian satu kali dalam lembar kerja, daripada harus memperbarui setiap lembar). Ingatlah bahwa tidak ada perubahan lain yang diperlukan saat memperbarui suatu negara. Pikirkan tentang kemungkinan kueri untuk negara bagian berbeda yang menghasilkan jumlah baris berbeda. Anda mungkin perlu menggunakan referensi yang lebih besar ke tabel InputData dibandingkan dengan data status yang sudah ada di sana. Di dalam handout disertakan contoh tampilan keluaran excel untuk tab lembar kerja lainnya. Saya telah mencantumkan beberapa tip untuk menyelesaikan setiap lembar pada handout.
Dapatkan informasi tren dari output kueri Fast Track. Perusahaan Anda hanya menggunakan data industri untuk analisis tren, dan mempertimbangkan pengalaman negara bagian dengan pengalaman CW untuk mengembangkan trennya.
Gunakan rumus LINEST dan INTERCEPT untuk menghitung nilai yang sesuai. Jangan ragu untuk meletakkan indeks (1,2,3,…) di kolom A untuk nilai X Anda. Nilai Y Anda harus berupa kolom Premium Murni. Ingat, Premi Murni = Jumlah Kerugian / Tahun Mobil. Gunakan nilai-nilai ini untuk menghitung kolom nilai yang dipasang. Perubahan tahunannya sebesar 4 x kemiringan (untuk empat periode). Nyatakan ini sebagai % tren dengan membagi jumlah tahunan dengan nilai terkini
Buat grafik seperti yang ditunjukkan pada handout dengan 4 seri, Status dan CW, pas dan aktual.
Buat pameran tren untuk semua liputan yang ditampilkan. Ingatlah bahwa Anda dapat menyalin tab pertama yang Anda selesaikan dengan mengklik kanan tab tersebut, dan ucapkan pindahkan atau salin, lalu buat salinannya. Jika
Anda mengkodekan tab pertama dengan benar, Anda seharusnya dapat menyalinnya, mengubah referensi cakupan, dan Anda tidak perlu mengulangi pekerjaan yang tersisa.
Lembar Kerja Faktor Proyeksi Kerugian Tren yang dihitung untuk setiap cakupan harus dimasukkan ke dalam lembar kerja ini. Terdapat perhitungan bobot kredibilitas pada spreadsheet ini. Kredibilitas yang diberikan kepada
pengalaman suatu negara bagian didasarkan pada jumlah klaim atas negara bagian tersebut pada periode terakhir. (Misalnya, jika jumlah tagihan negara bagian Q1 tahun 2010 kepada BI adalah 123.245;
bobot kredibilitas yang ditetapkan harus 0,4.) Bobot tersebut harus diambil dari lembar kerja tren, atau data mentah di tab data masukan.
Rumus tren tertimbang = Tren Negara * Bobot Kredibilitas + Tren CW * (1 Bobot Kredibilitas).
-Anda juga harus memasukkan jumlah kerugian untuk periode terakhir. Ini digunakan untuk menghitung tren rata-rata tertimbang untuk semua cakupan (sel H13), berdasarkan pada
distribusi cakupan negara.
Dapatkan informasi jumlah polis, premi, dan kerugian selama tiga tahun dari data pengalaman perusahaan di tab input data. Hitung rasio kerugian, perubahan yang ditunjukkan, dan faktor tingkat yang ditunjukkan. Perhitungan perubahan yang ditunjukkan ditampilkan pada spreadsheet. Faktor indikator = Faktor Saat Ini x (1 + menunjukkan Perubahan). Pada kedua lembar kerja, tambahkan format bersyarat ke kolom perubahan yang ditunjukkan untuk menyorot sel yang mengalami peningkatan lebih dari 10%, atau penurunan kurang dari -10%.
Tarik informasi premi dan kerugian dari data pengalaman perusahaan pada tab input data. Tarik LPF dari tab Loss Projection Factor. Hitung proyeksi kerugian = Kerugian aktual x LPF.
Gunakan proyeksi rasio kerugian untuk periode 3 tahun pada rumus perubahan yang ditunjukkan di bagian bawah lembar kerja. Masukkan hasil investasi secara manual dari lembar kerja hasil investasi Anda. Untuk nilai lain dalam rumus, gunakan nilai pada contoh terlampir.
-Atasan Anda juga menginginkan cara untuk melacak tren, tanpa harus melakukan semua pekerjaan yang terkait dengan menyiapkan lembar kerja indikasi. Anda setuju untuk membuat PivotChart yang menunjukkan tren premium murni.
-Untuk menghasilkan data sumber untuk PivotChart ini, Anda harus bisa menggunakan kueri yang sama seperti yang Anda gunakan untuk menghasilkan data tren yang Anda tempelkan ke lembar kerja Indikasi. Perbedaan utamanya adalah Anda harus menghapus status tertentu saat menjalankan kueri tersebut. Kueri harus mengembalikan nilai untuk semua negara bagian, serta kolom yang berisi nilai CW. Tempelkan output kueri ke dalam buku kerja Excel baru.
PivotChart harus memiliki bidang Halaman Cakupan dan Status. Periode waktu (YYYQ) harus ditampilkan di bagian bawah grafik. Elemen data pada area grafik harus mencakup premi murni negara, dan premi murni CW.
