Actuarial Computing Excel Access VBA Repo
1.0.0
คณิตศาสตร์ประกันภัย (MAT 253 , ISU)
แล็บโค้ดนี้มุ่งเน้นไปที่การใช้ VLOOKUPS เพื่อกรอกตารางที่ระบุไว้ภายใต้จำนวนการอ้างสิทธิ์ โดยอ้างอิงตารางแรกบนแท็บข้อมูล โดยเกี่ยวข้องกับการใช้ค่าในแถวเพื่อทำให้พารามิเตอร์ตัวที่ 3 ของฟังก์ชัน VLOOKUP สมบูรณ์ นอกจากนี้ยังเกี่ยวข้องกับการใช้การอ้างอิงเซลล์แบบสัมบูรณ์และแบบสัมพันธ์อย่างถูกต้อง เพื่อให้สามารถคัดลอกฟังก์ชันเดียวกันทั่วทั้งพื้นที่สีเหลืองทั้งหมดได้
Code Lab นี้ยังเน้นไปที่การใช้ HLOOKUP เพื่อกรอกตารางโดยใช้ข้อมูลจากตารางที่สองของแท็บ Data 1 สำหรับพารามิเตอร์ตัวที่ 3 ของ HLOOKUP เราใช้ฟังก์ชัน MATCH พร้อมด้วยคีย์การจับคู่ที่เหมาะสมและการอ้างอิงอาร์เรย์ไปยังเวกเตอร์พร้อมรายการปีที่มีอยู่
รหัสเกี่ยวข้องกับการปฏิบัติตามคำแนะนำด้านล่าง:
ใน Code Lab นี้ เราสร้างกราฟที่แสดงความถี่ของการอ้างสิทธิ์จริงและความรุนแรงของการอ้างสิทธิ์จริงบนแกน y
เนื่องจากขนาดของแต่ละซีรี่ส์แตกต่างกันมาก เราจึงใช้แกนสองแกนที่แตกต่างกันเพื่อแสดงซีรีส์ที่แตกต่างกัน
แกน x แสดงจุด # (กลุ่ม A) แต่ละชุดจะแสดงเป็นจุดและมีเส้นเชื่อมต่อกัน
แต่ละชุดจะมีป้ายกำกับความถี่หรือความรุนแรงตามความเหมาะสม
เมื่อใช้คำสั่ง IF เราจะคำนวณมูลค่าปัจจุบันตามหลักคณิตศาสตร์ประกันภัยสำหรับบุคคลแต่ละคนในรายการบนแท็บ "ปัญหา 1" - สูตร APV = มูลค่าที่ตราไว้ * ขวาน - ขวานจะแตกต่างกันไปตามเพศและสถานะผู้สูบบุหรี่ และสามารถพบได้ใน 4 แท็บสำหรับแต่ละกรณี ในการตรวจสอบคำตอบ ผลลัพธ์ของนโยบายแรกควรมี APV = 1,1238.0 บนแท็บ “ปัญหา 1” คอลัมน์ A มีสตริงข้อความที่ต่อกันของ 4 ช่องที่แตกต่างกัน ได้แก่ Policy_Num, Effective_Date, Expiration_Date, Premium ใช้เครื่องหมายจุลภาค (,) เป็นตัวคั่นเพื่อแยกออกเป็น 4 คอลัมน์ คุณสามารถใช้เครื่องมือหรือฟังก์ชั่นใดก็ได้ภายใน Excel เพื่อทำสิ่งนี้
เราตั้งค่ารายงาน PivotTable ในแผ่นงานใหม่ชื่อ “ปัญหา 1” จากข้อมูลบนแท็บ 'คอลเลกชัน' (ช่วง A1:D2771) ใส่ 'จำนวนคอลเลกชัน' ในป้ายกำกับแถว และสร้าง 4 คอลัมน์: 1. ผลรวมของเบี้ยประกันภัย 2. ผลรวมของการสูญเสีย 3. อัตราส่วนการสูญเสีย = การสูญเสีย / เบี้ยประกันภัย 4. จำนวนนโยบาย แสดงเป็น % ของคอลัมน์
บนแท็บ "การถดถอย" ให้ใช้เทคนิคการถดถอยเชิงเส้นอย่างง่าย (y=a+bx) เพื่อทำนายน้ำหนักของบุคคลโดยใช้ส่วนสูง คุณสามารถใช้วิธีการใดก็ได้ที่มีอยู่ใน Excel เพื่อรับการประมาณค่าพารามิเตอร์
คุณเป็นนักคณิตศาสตร์ประกันภัยด้านการกำหนดราคาให้กับ ABC Insurance Company ซึ่งเป็นบริษัทประกันภัยรถยนต์ส่วนบุคคลขนาดเล็กที่มีรายได้เบี้ยประกันภัยประมาณ 300 ล้านเหรียญสหรัฐต่อปี หน้าที่รับผิดชอบประการหนึ่งของคุณคือการพัฒนาตัวบ่งชี้ระดับอัตราเป็นระยะๆ รวมถึงการปรับเปลี่ยนปัจจัยการให้คะแนนของคุณ เจ้านายของคุณขอให้คุณรวบรวมกระบวนการเพื่อปรับปรุงกระบวนการบ่งชี้สำหรับการพัฒนาอัตราที่ระบุสำหรับปี 2011 เพื่อดำเนินการดังกล่าว เขาได้ให้คำแนะนำต่อไปนี้ตลอดจนรายละเอียดของสิ่งที่เขาต้องการให้สเปรดชีตมีหน้าตาเป็นอย่างไร
เขายังขอให้คุณจัดเตรียมแนวทางแยกต่างหากสำหรับเขาในการติดตามแนวโน้มของพรีเมี่ยมอย่างแท้จริงในทุกรัฐ และเปรียบเทียบกับแนวโน้มทั่วประเทศ (CW) เขาต้องการประเด็นง่ายๆ และ
คลิกวิธีการเพื่อทำสิ่งนี้ ดังนั้นคุณได้แนะนำ PivotChart เพื่อจุดประสงค์นี้
การพัฒนาตัวบ่งชี้อัตราที่ ABC เกี่ยวข้องกับขั้นตอนไม่กี่ขั้นตอน ได้แก่: • การวิเคราะห์แนวโน้ม • การพัฒนาปัจจัยการคาดการณ์การสูญเสียตามแนวโน้ม • การพัฒนาปัจจัยที่ระบุในการหักลดหย่อนและระดับ (อายุและเพศ) • การพัฒนาผลตอบแทนการลงทุน • การพัฒนาตัวบ่งชี้อัตราโดยรวม
เพื่อพัฒนาตัวบ่งชี้อัตรา คุณได้รับข้อมูลต่อไปนี้: • แผนกไอทีได้จัดเตรียมข้อมูลเบี้ยประกันภัยและการสูญเสียโดยละเอียดสำหรับกรมธรรม์ทั้งหมดปี 2007-2009 ในรูปแบบไฟล์ข้อความที่มีความกว้างคงที่ ไฟล์นี้มีประมาณ 1 ล้านเรกคอร์ด ดังนั้นจึงต้องประมวลผลใน Access ก่อน • คุณยังมีสำเนาของข้อมูลแนวโน้มอุตสาหกรรม Fast Track ล่าสุดในฐานข้อมูล Access • คุณมีสเปรดชีต Excel ที่มีการถือครองและการซื้อหุ้นของบริษัท รวมถึงราคาในอดีตของหุ้นเหล่านั้นในช่วง 4 ปีที่ผ่านมา
-กระบวนการบ่งชี้อัตราของคุณจะรวมถึงผลลัพธ์ต่อไปนี้ (อธิบายในรายละเอียดเพิ่มเติมด้านล่าง): • ฐานข้อมูล Access ที่มีการสอบถามที่ส่งออกข้อมูลที่สามารถคัดลอกลงใน Excel สำหรับแต่ละรัฐได้ • สเปรดชีต Excel ที่แสดงการคำนวณผลตอบแทนการลงทุนเฉลี่ยสำหรับปี 2550-2552 • สเปรดชีต Excel ที่คำนวณการเปลี่ยนแปลงอัตราที่ระบุ หลังจากวางผลลัพธ์ของการสืบค้นการเข้าถึงและผลตอบแทนจากการลงทุนลงไป
สเปรดชีตนี้ควรอนุญาตให้ผู้ใช้สามารถวางเอาต์พุตการเข้าถึงสำหรับสถานะอื่นลงใน Excel และสร้างอัตราที่ระบุโดยอัตโนมัติโดยไม่ต้องอัปเดตใดๆ เพิ่มเติม • สเปรดชีต Excel พร้อม PivotChart ที่แสดงทั้งแนวโน้ม CW และแนวโน้มสถานะ มีตัวอย่างว่าผลลัพธ์จากเวิร์กชีตตัวบ่งชี้อัตราควรมีลักษณะอย่างไร
มีฐานข้อมูล Access ให้ไว้ ฐานข้อมูลนั้นมีตารางชื่อ TrendData ซึ่งมีข้อมูลแนวโน้มอุตสาหกรรมอยู่แล้ว คุณยังได้รับข้อมูลนโยบายโดยละเอียดใน Policydata.txt เค้าโครงสำหรับไฟล์ข้อความอยู่ด้านล่าง: Pos ฟิลด์ 1-2 คีย์ 3-4 สถานะ 5-8 หักลดหย่อน 9-14 รหัสคลาส 15-18 ปี 19-24 พรีเมียม 25 ตัวบ่งชี้ว่ากรมธรรม์มีการอ้างสิทธิ์หรือไม่ 26-35 จำนวนเงินที่เรียกร้อง
** หมายเหตุเกี่ยวกับฟิลด์คีย์ **
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.
ใน Access คุณควรสร้างแบบสอบถามที่แสดงผลข้อมูลต่อไปนี้:
ข้อมูลเบี้ยประกันภัย/การสูญหายของบริษัท: รัฐ (กลุ่มตาม) ปี (กลุ่มตาม) หัก (กลุ่มตาม) ประเภท (กลุ่มตาม) จำนวนนโยบาย (นับ) เปรม (ผลรวม) CLAIM_IND (ผลรวม) LOSS_AMOUNT (ผลรวม)
คุณควรตั้งค่าการสืบค้นให้มีคำสั่ง Where สำหรับสถานะ คุณสามารถเปลี่ยนสถานะเป็นสถานะใดก็ได้ที่คุณกำลังทำงานอยู่ ข้อมูลแนวโน้มอุตสาหกรรมอย่างรวดเร็ว: STATE (กลุ่มตาม) YYYYQ (กลุ่มตาม) Cov (กลุ่มตาม) CW_CARYEARS (Sum) CW_PDCOUNT (Sum) CW_PDAMT (Sum) STATE_CARYEARS (Sum) STATE_PDCOUNT (Sum) STATE_PDAMT (Sum)
ฟิลด์ CW เป็นการสรุปตามข้อมูลทั้งหมดสำหรับทุกรัฐ ช่องสรุป STATE คือผลรวมของช่องสำหรับรัฐนั้นๆ อีกครั้ง คุณควรตั้งค่าแบบสอบถามสำหรับส่วนคำสั่ง Where เพื่อระบุสถานะที่จะส่งออก
โปรดทราบว่าหากต้องการรับทั้งข้อมูลสรุป CW และข้อมูลสรุป STATE ในคำค้นหาเดียวกัน คุณจะต้องรวมผลลัพธ์ของการสืบค้นที่แยกกัน 2 รายการ (รายการหนึ่งที่ระดับรัฐ และอีกรายการหนึ่งที่ระดับ CW) และรวมผลลัพธ์ตาม YYYQ และ COV
สเปรดชีตที่ให้มามีสองตาราง ตารางหนึ่งมีราคาหุ้นในช่วงเวลาหนึ่งสำหรับหุ้นใน S&P 500 โดยบริษัท ABC เป็นเจ้าของหุ้นบางส่วนเหล่านั้น ฝ่ายลงทุนได้จัดให้มีสรุปหุ้นที่ถือ ณ ต้นปี (BOY) ปี 2549 และหุ้นที่ซื้อเมื่อวันที่ 1/1/2550, 1/1/2551 และ 1/1/2552 คุณต้องคำนวณอัตราผลตอบแทนการลงทุนสำหรับปี 2550, 2551 และ 2552 และค่าเฉลี่ยเลขคณิตของอัตราผลตอบแทน 3 ปี มีการสาธิตการคำนวณพร้อมเอกสารประกอบคำบรรยาย คุณควรกรอกสเปรดชีตเกี่ยวกับการคำนวณอัตราผลตอบแทนการลงทุนของแผ่นงาน ค่าที่คุณคำนวณในเวิร์กชีตนี้จะถูกป้อนลงในเวิร์กชีตการบ่งชี้อัตรา
ควรวางเอาต์พุตจาก Access ลงในแท็บข้อมูลป้อนข้อมูลของเวิร์กชีต คุณสามารถเพิ่มคอลัมน์ดัชนีใดๆ ลงในแท็บนี้ได้ซึ่งอาจเป็นประโยชน์กับคุณในภายหลัง คุณควรจะสามารถป้อนชื่อรัฐบนแท็บนั้นได้และให้ชื่อรัฐที่เป็นผลลัพธ์ไหลไปยังส่วนหัวของแผ่นงานทั้งหมดในแผ่นงาน (ดังนั้นหากคุณวางข้อมูลสำหรับสถานะใหม่ คุณจะต้องเปลี่ยนชื่อรัฐเพียงครั้งเดียวใน แผ่นงานแทนที่จะต้องอัปเดตทุกแผ่น) โปรดทราบว่าไม่จำเป็นต้องทำการเปลี่ยนแปลงอื่นใดเมื่ออัปเดตสถานะ ลองนึกถึงความเป็นไปได้ที่ข้อความค้นหาสำหรับสถานะต่างๆ จะส่งกลับจำนวนแถวที่แตกต่างกัน คุณอาจจำเป็นต้องใช้การอ้างอิงไปยังตาราง InputData ที่ใหญ่กว่าที่คุณต้องการสำหรับข้อมูลสถานะที่มีอยู่แล้ว สิ่งที่รวมอยู่ในเอกสารประกอบคำบรรยายคือตัวอย่างลักษณะเอาต์พุต Excel สำหรับแท็บแผ่นงานอื่นๆ ฉันได้แสดงเคล็ดลับบางประการในการกรอกเอกสารแต่ละแผ่นในเอกสารแจกแล้ว
รับข้อมูลแนวโน้มจากผลลัพธ์ของแบบสอบถามแบบติดตามด่วน บริษัทของคุณใช้เฉพาะข้อมูลอุตสาหกรรมสำหรับการวิเคราะห์แนวโน้ม และชั่งน้ำหนักประสบการณ์สถานะกับประสบการณ์ CW เพื่อพัฒนาแนวโน้ม
ใช้สูตร LINEST และ INTERCEPT เพื่อคำนวณค่าที่เหมาะสม คุณสามารถใส่ดัชนี (1,2,3,…) ในคอลัมน์ A สำหรับค่า X ของคุณได้ตามใจชอบ ค่า Y ของคุณควรเป็นคอลัมน์ Pure Premium โปรดจำไว้ว่า Pure Premium = จำนวนการสูญเสีย / ปีรถยนต์ ใช้ค่าเหล่านี้เพื่อคำนวณคอลัมน์ค่าที่พอดี การเปลี่ยนแปลงประจำปีคือ 4 x ความชัน (เป็นเวลาสี่ช่วง) แสดงสิ่งนี้เป็นแนวโน้ม % โดยหารจำนวนเงินรายปีด้วยมูลค่าที่ติดตั้งล่าสุด
สร้างกราฟตามที่แสดงในเอกสารประกอบคำบรรยายด้วยชุดข้อมูล 4 ชุด ได้แก่ สถานะและ CW พอดีและตามจริง
สร้างการจัดแสดงเทรนด์สำหรับความครอบคลุมทั้งหมดที่แสดง โปรดทราบว่าคุณสามารถคัดลอกแท็บแรกที่คุณกรอกไว้ได้โดยคลิกขวาที่แท็บนั้น แล้วพูดว่าย้ายหรือคัดลอก จากนั้นจึงทำสำเนา ถ้า
คุณเขียนโค้ดแท็บแรกทางขวา คุณน่าจะคัดลอกมันได้ เปลี่ยนการอ้างอิงความครอบคลุม และคุณไม่จำเป็นต้องทำงานที่เหลือซ้ำอีก
แผ่นงานปัจจัยการประมาณการการสูญเสีย แนวโน้มที่คำนวณสำหรับแต่ละความครอบคลุมควรดึงผ่านไปยังแผ่นงานนี้ มีการคำนวณน้ำหนักความน่าเชื่อถือในสเปรดชีตนี้ ความน่าเชื่อถือที่มอบให้กับ
ประสบการณ์ของรัฐหนึ่งๆ ขึ้นอยู่กับจำนวนการเรียกร้องของรัฐนั้นในช่วงเวลาล่าสุด (ตัวอย่างเช่น หากจำนวนการเรียกร้องของรัฐในไตรมาสที่ 1 ปี 2010 สำหรับ BI คือ 123,245;
น้ำหนักความน่าเชื่อถือที่กำหนดควรเป็น 0.4) สิ่งเหล่านั้นควรดึงมาจากแผ่นงานแนวโน้มหรือข้อมูลดิบบนแท็บข้อมูลอินพุต
สูตรสำหรับแนวโน้มถ่วงน้ำหนัก = แนวโน้มสถานะ * น้ำหนักความน่าเชื่อถือ + แนวโน้ม CW * (น้ำหนัก 1-ความน่าเชื่อถือ)
-คุณควรรวมจำนวนเงินที่ขาดทุนในช่วงเวลาล่าสุดด้วย ใช้เพื่อคำนวณแนวโน้มเฉลี่ยถ่วงน้ำหนักสำหรับความครอบคลุมทั้งหมด (เซลล์ H13) โดยยึดตาม
การกระจายความคุ้มครองของรัฐ
รับข้อมูลจำนวนกรมธรรม์ เบี้ยประกันภัย และข้อมูลการสูญหายของทั้งสามปีจากข้อมูลประสบการณ์ของบริษัทบนแท็บข้อมูลที่ป้อน คำนวณอัตราส่วนการสูญเสีย การเปลี่ยนแปลงที่ระบุ และปัจจัยด้านอัตราที่ระบุ การคำนวณการเปลี่ยนแปลงที่ระบุจะแสดงอยู่ในสเปรดชีต ปัจจัยตัวบ่งชี้ = ปัจจัยปัจจุบัน x (1 + ระบุการเปลี่ยนแปลง) ในเวิร์กชีตทั้งสอง ให้เพิ่มการจัดรูปแบบตามเงื่อนไขลงในคอลัมน์การเปลี่ยนแปลงที่ระบุเพื่อเน้นเซลล์ที่มีการเพิ่มขึ้นมากกว่า 10% หรือลดลงน้อยกว่า -10%
ดึงข้อมูลพรีเมียมและการสูญเสียจากข้อมูลประสบการณ์ของบริษัทบนแท็บข้อมูลอินพุต ดึง LPF ออกจากแท็บ Loss Projection Factor คำนวณผลขาดทุนที่คาดการณ์ไว้ = ผลขาดทุนจริง x LPF
ใช้อัตราส่วนการสูญเสียที่คาดการณ์ไว้สำหรับระยะเวลา 3 ปีในสูตรการเปลี่ยนแปลงที่ระบุที่ด้านล่างของแผ่นงาน ป้อนผลตอบแทนการลงทุนด้วยตนเองจากแผ่นงานผลตอบแทนการลงทุนของคุณ สำหรับค่าอื่นๆ ในสูตร ให้ใช้ค่าในตัวอย่างที่แนบมา
- เจ้านายของคุณต้องการวิธีติดตามแนวโน้ม โดยไม่ต้องทำงานทั้งหมดที่เกี่ยวข้องกับการจัดทำแผ่นงานข้อบ่งชี้ คุณตกลงที่จะสร้าง PivotChart ที่แสดงแนวโน้มระดับพรีเมียมอย่างแท้จริง
-ในการสร้างข้อมูลต้นฉบับสำหรับ PivotChart นี้ คุณควรจะสามารถใช้แบบสอบถามเดียวกันกับที่คุณใช้ในการสร้างข้อมูลแนวโน้มที่คุณวางลงในแผ่นงานตัวบ่งชี้ ข้อแตกต่างหลักคือ คุณควรลบสถานะเฉพาะออกเมื่อดำเนินการค้นหานั้น แบบสอบถามควรส่งคืนค่าสำหรับทุกรัฐ รวมถึงคอลัมน์ที่มีค่า CW วางผลลัพธ์ของแบบสอบถามลงในสมุดงาน Excel ใหม่
PivotChart ควรมีช่องหน้าเป็นความครอบคลุมและสถานะ ช่วงเวลา (YYYQ) ควรแสดงที่ด้านล่างของแผนภูมิ องค์ประกอบข้อมูลในพื้นที่แผนภูมิควรรวมพรีเมียมแท้ของรัฐ และพรีเมียมแท้ CW
