the question doesnt need more information, thats all there is to the question.

RAINYDAY MOTOR INSURANCE CASE INTRODUCTION A motor vehicle operating on the road in Jamaica is legally required to have motor insurance. Local insurers offer, directly or through insurance brokers, a range of coverage options for private, private commercial, and public commercial vehicles. These include variations of comprehensive and third party policies. Comprehensive insurance is extensive and covers damage to your vehicle, liability to a third party, as well as bodily injury. Benefits may include payment of legal fees for defending charges of misconduct, payment for alternative transportation, payment for medical expenses, and providing loyalty bonuses. On the other hand, third party insurance is more basic and usually covers injury or damage to others or property caused by your vehicle. A local agent for one of the smaller players in the market, Rainy Day Ltd., has recently set up operations and provides a limited range of motor insurance coverage, namely comprehensive and standard third party coverage. The company's rates are competitive and its customer base is steadily growing. This is due, in part, to its liberal comprehensive policy. Vehicles up to 17 years old and valued at $300,000 or more are eligible for comprehensive coverage. Otherwise, only standard third party coverage, also known as basic coverage, is available. Rainy Day also offers special rates to employees and students of two esteemed tertiary institutions, who account for the majority of its business. Additionally, drivers can earn a no-claim discount of up to 60%. The staff complement at this agency is quite small and the calculation of annual premiums and tracking of payments is currently done manually. This is becoming more tedious as business grows and there is the need to automate these activities. Subsequently, you have been commissioned by the CEO of Rainy Day, Mr. Cory Phelps, to develop a two-part solution that will improve operations. The scope of your solution is small it will be developed for private vehicles only. However, if the staff members are impressed with this preliminary solution, you may be contracted to expand the system to include commercial vehicles. You are provided with sample data for 2021 in a workbook named Insurance Data Section A contains the specifications and requirements for the Annual Premiums Module, which should be developed using MS Excel Section B contains the specifications and requirements for the Premium Payments Module, which should be developed using MS Access SECTION A: ANNUAL PREMIUMS MODULE [45 marks] Design Specifications At Rainy Day, customers are required to get a valuation two (2) years. Also, owners usually opt for comprehensive coverage whenever possible. Annual insurance premium is based on a number of factors namely age and value of vehicle, as well as age, gender, and accident history of the insured. Basic Coverage All vehicles incur the basic coverage fee (which is also the third party fee.) This fee is $18,500 for University staff/students and $45,000 for others, however it is subject to escalation based on the age and gender of the insured. A survey of accidents revealed that the majority are committed by males under the age of 40, the main offenders being males in their 20s. For these high-risk clients, Rainy Day increases their basic coverage fee by a percentage as follows: Males 21 - 29 years old are subject to a 25% increase and those between the ages of 30 and 39 are subject to a 15% increase Age of the insured is determined on policy start date. Figure 1 1 summarizes factors that contribute to basic coverage fee calculation . Basic Coverage Fee Increase (%) Figure 1: Basic Coverage Rates Basic Coverage/Third Party Fee Gender Age University Not a University (Years) Affiliate Affiliate Female All Ages 21-29 $18500 $45000 Male 30-39 40 and over None 25% 15% None Comprehensive Coverage If the vehicle is eligible for comprehensive coverage, a comprehensive coverage fee is added to the basic coverage fee. The comprehensive coverage fee is a percentage of the vehicle's current value. This percentage varies inversely with the value of the vehicle i.e. it decreases as the value of the vehicle increases. Comprehensive Rates are displayed in Figure 2. Figure 2: Comprehensive Coverage Rates Vehicle Value Comprehensive Rate(% of Vehicle Value) =$2M 5% No-Claim Discount & GCT A driver's accident history has a direct effect on his/her no claim discount. Drivers carn 20% in year 1, and 10% in each subsequent year that he/she remains accident-free, up to a cumulative maximum of 60%. On the contrary, no-claim discounts can also be reduced based on the nature and size of accident claims. No-claim discount is applied to the sum of the basic coverage fee and the comprehensive coverage fee. That balance is subject to GCT at a rate of 15% Scenarios Consider the following scenarios. Scenario #1: A $320,000 20 year old vehicle is owned by a 42 year-old male university staff member who is entitled to a no-claim discount of 30%. Scenario #2: A $4.2 million 5 year-old vehicle whose 35 year-old male owner is not affiliated to a university and does not have a no-claim discount. Scenario #3: A $900,000 10 year-old vehicle owned by a female university student who is entitled to a 50% no-claim discount. Figure 3 outlines the key premium components. Figure 3: Premium Calculation Scenarios Premium Components Scenario #1 Scenario #2 Scenario #3 Type of Coverage Comprehensive Comprehensive Basic Coverage Fee $18,500 $51,750" $18,500) Comprehensive Coverage Fee 5% of $4.2M 11% of $900,000 (1) No Claim Discount Amount 30% of $18,500 n/a SOX of (){) Third Party n/a *Increased by 15% due to age and gender of insured You have been provided with test data in the Report Data worksheet of the Insurance Data workbook and are asked to develop a Premium Report for the year 2021. For the sake of confidentiality, vehicle and owner details have been kept to a minimum. A sample of the test data is displayed in Figure 4, and Figure 5 contains the report columns. Figure 4. Premium Report Sample Data No- Insured Insured University Policy Start Claim Year Gender DOB Affiliate? Date Discount (%) 2017 $ 1,950,000.00 Female 9/5/1957 1/5/2021 60% 9415 2016 $ 1,850,000.00 12/29/1988 1/31/2021 2017 $ 2,100,000.00 Male 7/17/1962 2/14/2021 2015 S 1,450,000.00 Female 10/12/1947 Yes 2/28/2021 0% Vehicle Vehicle Vehicle Value (5) ID 9944 No No Male 40% 50% No 3928 7540 Figure 5. Premium Report Specifications Report Columns Description Vehicle ID Vehicle Identifier. Given. Vehicle Year Model Year. Given. Vehicle Value ($) Current Value. Given Insured Gender Gender of insured - "Male" or "Female". Given. Insured DOB Insured's date of birth in mm/dd/yyyy format. Given. University Affiliate? "Yes" if insured is a University staff/student. "No" otherwise. Given. Policy Start Date Start Date in mm/dd/yyyy format. Given. No-Claim Discount (%) Indicates % of no-claim discount insured has earned. Given. Current Age of Insured (Years) Age (in years) at policy start date. No decimal places. Should NOT be rounded up. Calculated. Type of Coverage Policy Type: "Comprehensive" or "Third Party". Calculated. Basic Coverage Fee - Regular($) Basic Coverage Amount based on university affiliation. should be $18,500 or $45,000. Calculated. Additional Basic Coverage Fee - High-Risk($) Additional Basic Coverage for high-risk clients. Calculated. Comprehensive coverage Fee (S) Comprehensive coverage Amount. Calculated. Premium Coverage Due After Tax ($) Annual Insurance Premium after tax. Calculated. Implementation Requirements 1. Create a separate worksheet named QlInputs that contains all inputs used to develop the Premium Report 2. Create the Premium Report in a worksheet named Q2Premium Report that contains all the columns outlined in Figure 5, appropriately presented. Do not add extra columns. 3. In a worksheet named Q3Summary, Mr. Phelps would like to see the number of vehicles and average premium by coverage type by vehicle year. 4. In a worksheet named Q4Third Party, determine what proportion of vehicles has third party coverage. 5. Display number of customers and average no-claim discount % by gender in a sheet named Q5ByDemographics. 6. In a sheet named Q6High Value, display the vehicle with the highest value for each vehicle year. 7. Determine how many high-risk males are in each of the respective age ranges. Name this sheet Q7High Risk. Also, conditionally format the rows of these individuals. RAINYDAY MOTOR INSURANCE CASE INTRODUCTION A motor vehicle operating on the road in Jamaica is legally required to have motor insurance. Local insurers offer, directly or through insurance brokers, a range of coverage options for private, private commercial, and public commercial vehicles. These include variations of comprehensive and third party policies. Comprehensive insurance is extensive and covers damage to your vehicle, liability to a third party, as well as bodily injury. Benefits may include payment of legal fees for defending charges of misconduct, payment for alternative transportation, payment for medical expenses, and providing loyalty bonuses. On the other hand, third party insurance is more basic and usually covers injury or damage to others or property caused by your vehicle. A local agent for one of the smaller players in the market, Rainy Day Ltd., has recently set up operations and provides a limited range of motor insurance coverage, namely comprehensive and standard third party coverage. The company's rates are competitive and its customer base is steadily growing. This is due, in part, to its liberal comprehensive policy. Vehicles up to 17 years old and valued at $300,000 or more are eligible for comprehensive coverage. Otherwise, only standard third party coverage, also known as basic coverage, is available. Rainy Day also offers special rates to employees and students of two esteemed tertiary institutions, who account for the majority of its business. Additionally, drivers can earn a no-claim discount of up to 60%. The staff complement at this agency is quite small and the calculation of annual premiums and tracking of payments is currently done manually. This is becoming more tedious as business grows and there is the need to automate these activities. Subsequently, you have been commissioned by the CEO of Rainy Day, Mr. Cory Phelps, to develop a two-part solution that will improve operations. The scope of your solution is small it will be developed for private vehicles only. However, if the staff members are impressed with this preliminary solution, you may be contracted to expand the system to include commercial vehicles. You are provided with sample data for 2021 in a workbook named Insurance Data Section A contains the specifications and requirements for the Annual Premiums Module, which should be developed using MS Excel Section B contains the specifications and requirements for the Premium Payments Module, which should be developed using MS Access SECTION A: ANNUAL PREMIUMS MODULE [45 marks] Design Specifications At Rainy Day, customers are required to get a valuation two (2) years. Also, owners usually opt for comprehensive coverage whenever possible. Annual insurance premium is based on a number of factors namely age and value of vehicle, as well as age, gender, and accident history of the insured. Basic Coverage All vehicles incur the basic coverage fee (which is also the third party fee.) This fee is $18,500 for University staff/students and $45,000 for others, however it is subject to escalation based on the age and gender of the insured. A survey of accidents revealed that the majority are committed by males under the age of 40, the main offenders being males in their 20s. For these high-risk clients, Rainy Day increases their basic coverage fee by a percentage as follows: Males 21 - 29 years old are subject to a 25% increase and those between the ages of 30 and 39 are subject to a 15% increase Age of the insured is determined on policy start date. Figure 1 1 summarizes factors that contribute to basic coverage fee calculation . Basic Coverage Fee Increase (%) Figure 1: Basic Coverage Rates Basic Coverage/Third Party Fee Gender Age University Not a University (Years) Affiliate Affiliate Female All Ages 21-29 $18500 $45000 Male 30-39 40 and over None 25% 15% None Comprehensive Coverage If the vehicle is eligible for comprehensive coverage, a comprehensive coverage fee is added to the basic coverage fee. The comprehensive coverage fee is a percentage of the vehicle's current value. This percentage varies inversely with the value of the vehicle i.e. it decreases as the value of the vehicle increases. Comprehensive Rates are displayed in Figure 2. Figure 2: Comprehensive Coverage Rates Vehicle Value Comprehensive Rate(% of Vehicle Value) =$2M 5% No-Claim Discount & GCT A driver's accident history has a direct effect on his/her no claim discount. Drivers carn 20% in year 1, and 10% in each subsequent year that he/she remains accident-free, up to a cumulative maximum of 60%. On the contrary, no-claim discounts can also be reduced based on the nature and size of accident claims. No-claim discount is applied to the sum of the basic coverage fee and the comprehensive coverage fee. That balance is subject to GCT at a rate of 15% Scenarios Consider the following scenarios. Scenario #1: A $320,000 20 year old vehicle is owned by a 42 year-old male university staff member who is entitled to a no-claim discount of 30%. Scenario #2: A $4.2 million 5 year-old vehicle whose 35 year-old male owner is not affiliated to a university and does not have a no-claim discount. Scenario #3: A $900,000 10 year-old vehicle owned by a female university student who is entitled to a 50% no-claim discount. Figure 3 outlines the key premium components. Figure 3: Premium Calculation Scenarios Premium Components Scenario #1 Scenario #2 Scenario #3 Type of Coverage Comprehensive Comprehensive Basic Coverage Fee $18,500 $51,750" $18,500) Comprehensive Coverage Fee 5% of $4.2M 11% of $900,000 (1) No Claim Discount Amount 30% of $18,500 n/a SOX of (){) Third Party n/a *Increased by 15% due to age and gender of insured You have been provided with test data in the Report Data worksheet of the Insurance Data workbook and are asked to develop a Premium Report for the year 2021. For the sake of confidentiality, vehicle and owner details have been kept to a minimum. A sample of the test data is displayed in Figure 4, and Figure 5 contains the report columns. Figure 4. Premium Report Sample Data No- Insured Insured University Policy Start Claim Year Gender DOB Affiliate? Date Discount (%) 2017 $ 1,950,000.00 Female 9/5/1957 1/5/2021 60% 9415 2016 $ 1,850,000.00 12/29/1988 1/31/2021 2017 $ 2,100,000.00 Male 7/17/1962 2/14/2021 2015 S 1,450,000.00 Female 10/12/1947 Yes 2/28/2021 0% Vehicle Vehicle Vehicle Value (5) ID 9944 No No Male 40% 50% No 3928 7540 Figure 5. Premium Report Specifications Report Columns Description Vehicle ID Vehicle Identifier. Given. Vehicle Year Model Year. Given. Vehicle Value ($) Current Value. Given Insured Gender Gender of insured - "Male" or "Female". Given. Insured DOB Insured's date of birth in mm/dd/yyyy format. Given. University Affiliate? "Yes" if insured is a University staff/student. "No" otherwise. Given. Policy Start Date Start Date in mm/dd/yyyy format. Given. No-Claim Discount (%) Indicates % of no-claim discount insured has earned. Given. Current Age of Insured (Years) Age (in years) at policy start date. No decimal places. Should NOT be rounded up. Calculated. Type of Coverage Policy Type: "Comprehensive" or "Third Party". Calculated. Basic Coverage Fee - Regular($) Basic Coverage Amount based on university affiliation. should be $18,500 or $45,000. Calculated. Additional Basic Coverage Fee - High-Risk($) Additional Basic Coverage for high-risk clients. Calculated. Comprehensive coverage Fee (S) Comprehensive coverage Amount. Calculated. Premium Coverage Due After Tax ($) Annual Insurance Premium after tax. Calculated. Implementation Requirements 1. Create a separate worksheet named QlInputs that contains all inputs used to develop the Premium Report 2. Create the Premium Report in a worksheet named Q2Premium Report that contains all the columns outlined in Figure 5, appropriately presented. Do not add extra columns. 3. In a worksheet named Q3Summary, Mr. Phelps would like to see the number of vehicles and average premium by coverage type by vehicle year. 4. In a worksheet named Q4Third Party, determine what proportion of vehicles has third party coverage. 5. Display number of customers and average no-claim discount % by gender in a sheet named Q5ByDemographics. 6. In a sheet named Q6High Value, display the vehicle with the highest value for each vehicle year. 7. Determine how many high-risk males are in each of the respective age ranges. Name this sheet Q7High Risk. Also, conditionally format the rows of these individuals