Information Management
PGBM23-WS02
PGBM23 – Managing Information
Databases and ‘R’
Workshop No 2 – PGBM23 – Jeff Evans
PGBM23-WS02
PGBM23 - INFORMATION MANAGEMENT WS02-Q01
1. Use the enclosed information to assess the basic premium and no claims bonus and enter the first six sets of information into the forms TELENQ-2A.
Age
18 - 20 21 - 25 26 - 29 30 and above
Basic premium
£350 £250 £180 £140
(all premiums are independent of the size of the engine).
The No Claims Bonus (N.C.B.) works as follows: For up to 1 year N.C.B. the insurance co. offers 2 3 4 5 5% discount 10% 15% 25% 35%
2. Set up a paper based database to house all the data. 3. Calculate/resolve the following information (suggest the use of Microsoft Access and/or Excel): (most – if not all of the following can be solved within MS Access but, MS Excel is the recommended software medium to use – copy and paste from MS Access to MS Excel) The total amount of the quotations, The average amount quoted and standard deviation, Set up a frequency table for ages, with age ranges as follows: 17 to 26, 27 to 36, 37 to 46, 47 to 56 and 57 to 66; d. The percentage of customers with an age ≥ 37, e. The average age and standard deviation of the customers, (use both raw data and grouped frequency data), f. The Median and the Mode(s) of the customer ages, g. The total number of quotations per month, h. The total amount of the quotations per month, i. Plot the frequency of customer ages in a histogram, j. On a scatter plot, plot the number of enquiries against the age of customers, k. Calculate the line of best fit and coefficient of linear correlation for the data in part (j). l. Identify all those individuals who live in a postal code area between NE1 and NE20 inclusive. m. Identify all customers whose last name commences with a ‘T’ Note, you are not expected to resolve all of the above during the seminar. You should however, ensure that you are able to resolve all the above by the end of the contact time
PGBM23 – Managing Information
Databases and ‘R’
Workshop No 2 – PGBM23 – Jeff Evans
PGBM23-WS02
PGBM23 - INFORMATION MANAGEMENT WS02-Q01
1. Use the enclosed information to assess the basic premium and no claims bonus and enter the first six sets of information into the forms TELENQ-2A.
Age
18 - 20 21 - 25 26 - 29 30 and above
Basic premium
£350 £250 £180 £140
(all premiums are independent of the size of the engine).
The No Claims Bonus (N.C.B.) works as follows: For up to 1 year N.C.B. the insurance co. offers 2 3 4 5 5% discount 10% 15% 25% 35%
2. Set up a paper based database to house all the data. 3. Calculate/resolve the following information (suggest the use of Microsoft Access and/or Excel): (most – if not all of the following can be solved within MS Access but, MS Excel is the recommended software medium to use – copy and paste from MS Access to MS Excel) The total amount of the quotations, The average amount quoted and standard deviation, Set up a frequency table for ages, with age ranges as follows: 17 to 26, 27 to 36, 37 to 46, 47 to 56 and 57 to 66; d. The percentage of customers with an age ≥ 37, e. The average age and standard deviation of the customers, (use both raw data and grouped frequency data), f. The Median and the Mode(s) of the customer ages, g. The total number of quotations per month, h. The total amount of the quotations per month, i. Plot the frequency of customer ages in a histogram, j. On a scatter plot, plot the number of enquiries against the age of customers, k. Calculate the line of best fit and coefficient of linear correlation for the data in part (j). l. Identify all those individuals who live in a postal code area between NE1 and NE20 inclusive. m. Identify all customers whose last name commences with a ‘T’ Note, you are not expected to resolve all of the above during the seminar. You should however, ensure that you are able to resolve all the above by the end of the contact time