# Information Management

Topics: Field, Visual Basic for Applications, Microsoft Office Pages: 10 (2335 words) Published: May 1, 2013
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

£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 with your seminar leader. 4. Discuss your findings. Do you think you could successfully forecast the number of enquiries using the age of customers? a. b. c.

Workshop No 2 – PGBM23 – Jeff Evans

PGBM23-WS02

Assume you are working for an Insurance telephone call company and that you have the following customers: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Name John Smith Mary Hall George Tweddle Jim Long Rob Woodall Mark Stevens Steven Thompson Maggie Smith Alan Armstrong Bernie Callaghan Derek Trotter Trevor Sinclair Claire Allen Craig Davies Lyn Tennett David Fawcett Donna Reddin Lyndsey Thomson Georgilios Triantifillou Jimmy Peng Frederick Foster Kevin Lawrence John Smith Jenny George Janet Murray Alfonso Theofilos Margaret Hay Derek Walton Age 20 27 33 38 20 23 22 33 55 38 34 20 60 37 29 34 57 32 41 38 61 64 62 38 58 17 50 34 N.C.B 1 4 3 5 0 3 2 4 5 5 4 1 1 4 4 1 1 2 1 4 5 5 4 1 4 0 1 3 quote date 13/01/10 21/01/10 05/02/10 05/02/10 15/03/10 18/03/10 22/05/10 03/07/10 04/07/10 28/07/10 02/08/10 10/08/10 17/08/10 20/08/10 21/08/10 30/08/10 05/09/10 12/09/10 16/09/10 30/09/10 03/10/10 10/10/10 22/10/10 29/10/10 29/10/10 05/11/10 07/11/10 08/11/10 dob 30/01/82 24/08/75 22/07/69 09/11/64 02/06/82 10/01/79 19/04/80 17/09/69 01/07/47 28/07/64 02/06/68 23/06/82 13/06/42 02/07/65 07/09/73 08/06/68 06/10/45 18/06/70 26/07/61 17/04/64 24/07/41 24/10/38 16/01/40 11/01/64 22/07/44 10/01/85 25/10/52 07/03/68

Workshop No 2 – PGBM23 – Jeff Evans

PGBM23-WS02

You may use the first 28 addresses to support the names of the customers above. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 House No. 60 58 53 41 43 33 113 134 107 135 68 62 75 123 88 26 122 Flat 1 4 1 127 91 135 40 14 34 135 145 36 82 Street First Avenue Regent Street Waltham Terrace Druridge Drive William Street Coronation Street Renwick Road Avenue Road Plessey Road Bolam Road Newcastle Road Bewick Street Wansbeck Road Kings Road Oxford...