1.1. Description of the Problem
CL Performance Car Insurance Ltd. is a growing specialised sports car insurance company. It deals with exotic and high performance sports cars and has a loyal customer group and has built up a good reputation. At this moment in time, the firm's records are kept by hand, and all calculations etc. are done using a calculator. Some of these calculations can be complex and errors arise. In order to aid efficiency and ultimately save time, the owner of the company would like an automated system whereby quotes can be issued to clients via a PC system through a printed quote.
1.2. Input, Output and Processing of the Task
INPUT
* Customer details, including; Forename, Surname, Address, Gender etc.
* Insurance details, including the Insurance Group (1-20) for the brands of cars.
* The multiplying factors need to be allocated for the gender, risk, age and the type of insurance the client requires. The multiplying factors are the numbers used and multiplied to calculate the insurance quote for a customer, e.g.: a multiplying factor in this quotation system requires the computer to multiply the age group 17-19 (value: 4.95) by the gender; for example male (value: 1.35). These values are purely estimates.
PROCESSING
* Details of Insurance Groups need to be looked up from a table.
* Details of the multiplying factors also need to be looked up.
* Cost of quote needs to be calculated with and without any no claims bonus.
* Details are to be stored for future reference, i.e. the addresses of clients etc.
OUTPUT
* Quote details are to include customer forename, surname, address, car details and cost of insurance with and without any no claims bonus.
* A customised formatted printed invoice. The invoice will contain the date of issue, expiry date, the company address, telephone number, fax number, e-mail address and the quote details. The invoice will also contain the company logo and it will be signed by a member of staff for verification purposes.
1.3. End User Requirements
The following criteria need to be met in order for the quotation system to fulfil the end user requirements:
i. User must be able to move freely between worksheets.
ii. To issue an accurate insurance quote. This involves details such as customer gender, age and car model all being correct e.g. All the factors that influence the price of a quote.
iii. Must be user friendly, e.g. be as easy for the user to use as possible.
iv. Completed quotes need to be stored for future reference; these contain customer details and the actual quote details e.g. the car etc.
v. The quote's invoice must look professional.
1.4. Design
Hard copy output design
* The printout that will need to be produced is the invoice. A mock example of what this may look like is shown at the rear of this section on blank A4 paper labelled fig 1.1.
Screen output design
The following worksheets will need to be produced:
1. Front Page: a simple opening to the system.
2. Insurance Groups/Cars Worksheet: containing cars, and insurance group costs, which are always subject to change due to fluctuating insurance premiums.
3. Quotes Worksheet: the interface that the end-user will be working with in order to make quotes.
4. Multipliers Worksheet: used to make the calculations for a quote, again subject to change.
5. Customers Worksheet: stored quotes used for further reference.
6. Data Worksheet: a temporary store of quotes.
7. Customer Details: a sheet containing the details of prospective clients. This is purely for sample use in the project.
8. Quote to Print Worksheet: displaying the quote to be printed.
Pencil drawn layouts of each of the sheets mentioned above can be found overleaf on the squared paper. The number next to each sheet corresponds to the numbers above.
Processing design
* The LOOKUP function will be used on the quotes sheet in order to call up data from the groups/cars worksheet. The data on this sheet will be spit into 2 ranges which will be named to aid calculation.
* Details of the multiplying factors also need to be looked up.
* Cost of quote needs to be calculated with and without any no claims bonus.
* The customer details that need to be entered into the quotes worksheet will be looked up from a customer number.
* Details are to be stored for future reference, i.e. the addresses of clients etc.
1.5. ICT Skills of the User
The intended end user of this system will have had to have some experience in computer literacy and therefore be fairly fluent in the programs the database uses. So in this case, a sound understanding of Microsoft Excel is essential.
The layout of the worksheets must suit the skill level of the end user. Therefore the layout must be as user-friendly as possible. This means that the layout has to be clear, and simple to understand.
1.6. Resources Available
* To save time and money, a specific printer setting could be saved to enable each quote to look the same as the last one printed.
* For back up purposes, zip-drives drives could be used as they have more storage capacity than floppy disks and are more durable. A fire proof storage facility would need to be put in place in case of such incidents.
Section 2: Implementation
1. Hardware & Software to be Used
* Hardware that will be required to run the system will be a Windows PC with the minimum requirements being: Windows 98 browser, Pentium 2 Processor, and 32MB RAM. Other requirements include a suitable visual display unit (VDU monitor), a keyboard, a mouse, a 31/2 inch and a CD-Rom drive. To prevent eyestrain, a screen filter should be attached to the monitor. In order to aid efficient printing, a printer with approximately a 12 pages per minute (ppm) printing speed should be used. A high quality printer should be used for presentation purposes.
2. The implementation Process
2.1
1. Front Page
2. Insurance Groups/Cars Worksheet
3. Quotes Worksheet
4. Multipliers Worksheet
5. Customers Worksheet
6. Data Worksheet
7. Customer Details
8. Quote to Print Worksheet
The screenshots below show the worksheets in their final format. The number next to each screenshot corresponds to the numbers above: (The subsections are labelled with Roman Numerals, e.g.: i)).
1.
As you can see, the front sheet of the system has been formatted to improve its appearance. The whole sheet has been filled in blue. The company name appears as WordArt so it stands out. The company logo is positioned centrally to give a uniform feel. This sheet has had all standard and formatting option screen defaults removed to get rid of any excess clutter from the screen. The front sheet also has a macro labelled enter. When pressed, this brings the end user directly to the quotes sheet. The steps in the macro are very simple and consist of:
1. Start recording (on the front worksheet)
2. Switch to the quotes worksheet
3. Stop recording.
Then I made a button by selecting the View menu, Toolbars, Forms, and pressing the Button Tool (menu shown opposite). Then I simply assigned the macro to the name; enter.
Button tool. This is also used for all
subsequent macros.
Option Button tool.
Combo Box tool.
Check Box tool.
List Box tool
Spinner tool
2.
This sheet consists of the cars catered for at a particular time, the insurance group for each and the basic insurance group cost, from 1-20. As these two sets of figures will have to be looked up in order to process quotes, naming them was an obvious option instead of having to select the range for every formula.
The shaded area on the previous page is a named range called Groups. The blank range is called Costs. This saved a lot of time when inputting the VLOOKUP function to calculate quotes. The formulae in column 'D' bring the car brand and model into the cell. The speech marks in the formulae have a space in between them so the car name appears in full, e.g. =B2&" "&C2 gives the result BMW M5. This is needed for the quotes interface worksheet in the car selection area; where the details of the cars are needed. Column 'E' is inserted to separate the 2 named area ranges. This makes it easier for the end user to navigate.
3.
i) The screen shot below shows the Quotes sheet. This is the user interface that the end user will use to make quotes. It is heavily formatted with lines, a text box, colour fills and forms such as Combo Boxes, a List Box, a Spinner and a Check Box. The form toolbar which enables you to include these forms is shown on page 5. The macros shown are fully explained on the next page. The labels on this page correspond to the labels below and overleaf:
1) Customer Details Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to the Customer Details Worksheet.
iii. Stop recording.
2) View Quotes Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to the Customers Worksheet
iii. Stop recording.
3) Print Quote Macro:
i. Start recording (on Quote to Print worksheet).
ii. File, Print, OK.
iii. Stop recording.
4) Back to Front Page Macro:
i. Start Recording (on Quotes worksheet).
ii. Switch to the Front Page Worksheet.
iii. Stop recording.
5) Store Quote Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to the Customers worksheet.
iii. Select row 2 and insert a row.
iv. Switch to the Data worksheet
v. Select row 2 and Edit, Copy.
vi. Switch to the Customers worksheet.
vii. Select cell A2 and click Edit, Paste Special Values, OK.
viii. Turn off bold text, (as column headers are in bold).
ix. Click onto free space.
x. Stop recording.
Opposite is the macro code for the 'store quote' button. The 'customers' sheet is selected and then row 2 is selected. Whilst row 2 is highlighted, a new row is inserted, which shifts the row highlighted down. Then the 'data' sheet is selected and same row insertion is applied except the data entered on the customer sheet is copied here whilst a quote is being written. Then the cell A2 is selected from the 'customers' sheet and the values copied before are pasted here, by selecting the paste special values option on the edit toolbar.
6) View Current Quote Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to the Data worksheet.
iii. Stop recording.
7) View Quote To Print Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to the View Quote to Print worksheet.
iii. Stop recording.
8) Clear Screen Macro:
i. Start recording (on Quotes worksheet).
ii. Select range E13:E23, Edit, Clear, Contents Del.
iii. Select cell D25, Edit, Clear, Contents Del.
iv. Select cell D6.
v. Stop recording.
The macro code generated for the clear screen button is shown opposite. The cells E13-E23 are selected and their contents are cleared. Next, the cell D25 is cleared of its contents. Finally, the cell D6 is selected to return the user to the customer number cell in order to begin a new quote.
9) Modify Groups/Cars Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to the Groups/cars worksheet.
iii. Stop recording.
10) Modify Multipliers Macro:
i. Start recording (on Quotes worksheet).
ii. Switch to Multipliers worksheet.
iii. Stop recording.
ii)
The formula in the 'E' column above calculates the number of years that drivers have not made an insurance claim, i.e. No Claims Bonus. The spinner scrolls through the years (1-6). The VLOOKUP looks into the table range C32:D38, column 2 in the Multipliers sheet. This then adjusts the total cost of the quote accordingly. The spinner is linked to cell D25.
iii)
This part of the Quotes worksheet is where the customers' details appear. The VLOOKUP function has been used again to call up the customer details. This is done by using a customer number system. A range in the Customers worksheet has been named after the sheet and the details have been looked up in this way. So entering the customer number 0115 calls up the following details opposite:
These can be seen later in the project, in the Customer Details worksheet analysis.
iv)
Once again the VLOOKUP function is used to call up the information for the quote. The named range Groups, mentioned earlier is used to calculate the brand, model, and insurance group for the car. The Multipliers sheet is looked up to calculate the gender, risk area and the age of the driver. It is also used to calculate the type of insurance chosen by the customer. An IF statement is used to calculate whether or not the customer is insuring the car with another driver.
The totals are calculated easily by multiplying for the total without discount and no claims discount areas, and subtracting these two to find the total cost of the quote.
v)
As you can see, various forms have been set for the end user to select the customers' gender and car etc. The properties of the forms are listed below. The numbers on the right hand side of the screenshot, the word "TRUE" and the number 1 opposite the spinner are all cell links. I formatted these cells to the colour fill of this area so they appear hidden as they are not required to be seen.
* Gender Option Button: (Male/Female): 3D shading, cell link: E13; 1=Male, 2=Female.
* Car Combo Box: 3D shading, drop down lines: 9, cell link: E15, input range: (Groups&Cars)E2:E27.
* Age Combo Box: 3D shading, drop down lines: 8, cell link: E17, input range: (multipliers)C7:C17.
* Insurance Risk Area Combo Box: 3D shading, drop down lines: 4, cell link: E19, input range: (multipliers)C20:D22.
* Insurance Type List Box: 3D shading, cell link: E21, input range: C26:C28.
* Extra Driver Check Box: 3D shading, cell link: E23.If Checked= TRUE, if not checked= FALSE.
* No Claims Bonus Spinner: Min Value: 0, Max Value: 6, Incremental Change: 1, cell link: D25.
4.
This is the Multipliers worksheet. As you can see all the figures used to calculate the quote according to gender, age, area risk, insurance type, and no claims bonus are found here. They can be changed at any time, which is essential as insurance ratings and premiums change regularly. Each table is lined with an approximately 1.75mm thickness so they stand out. The text point size is 9.
5.
i)
This is the Customers worksheet where quotes are stored for further reference. The details above were just stored using the 'store quote' macro. They are transferred to this sheet.
ii)
As more quotes are stored, they are added to the 'customers' worksheet. The quotes store downwards; so the first quote entered into the system will appear at the bottom of the list; as above.
6.
Above is the Data worksheet. The formulae above call in the data from the Quotes sheet as it is being written. This allows the end user to look at the quote clearly in a linear form before storing it into system. Only one quote is ever processed in this sheet. When the 'Clear Screen' macro is pressed, the quote on this sheet also clears.
7.
The Customer Details worksheet is shown opposite. As you can see, each customer has a customer number. When this number is entered into the quote interface, the details of that particular person appear. The actual details (excluding the column headers) make up the table range that is used in the quotes sheet. The data above was supplied by my college.
8.
The Quote to Print worksheet is shown below. The formulae shown are looked up from the Quotes worksheet. The current day date and expiry date are also included. As you can see, the customer has 18 days after the quote is written in order to decide to take the insurance or not. Company name, address, telephone/fax and e-mail contacts are all shown. This gives the print out a professional look. Finally, the end user signs at the bottom of the quote to confirm it. This sheet also has a 'return to quote' macro on it.
A copy of the final printed quote is shown on the next page with some sample data given in the previous section.
Section 3: Testing
This section of the project tests that what has been created in the system actually works. The references mentioned can be viewed at the end of each numbered test.
N.B: the references are in the order they are mentioned so therefore may not be always in numerical order.
Test No: 1
Purpose of Test: User must be able to move freely between worksheets (see if all macros work as they should (see end user req. 1.3, i)).
Test Data
Reason for Choice
Expected Outcome
Actual Outcome (screen shot reference)
Comments (Including corrective action taken)
Press ENTER button on 'front page' sheet
Valid (reasonable)
This button should bring the user to the quotes interface.
Enter button (Ref 1.1) brings user to quotes interface (Ref 2.1)
The mouse has to be clicked on the button in order for the user to get to the quotes interface.
Press VIEW QUOTE TO PRINT button on 'quotes' worksheet
Valid (reasonable)
This button should bring the user to the print quote worksheet and give the user a preview of the printed quote.
View quote to print button (Ref 2.1) brings user to the quote to print worksheet (Ref 2.2)
The mouse was clicked on the button so the user could see a preview of the quote to be printed.
Press CLEARSCREEN button on 'quotes' worksheet
Valid (reasonable)
This button should clear the current details on the quotes worksheet ready for the next quote to be made. All the values entered except the customer details and the cursor is returned to the customer number cell.
Clear Screen button (Ref 2.1) clears the current screen (Ref 1.2) to become clear again and returns the cursor to the customer number cell (Ref 2.1)
Clicking the mouse on the clear screen button clears all the values entered and returns the cursor back to the customer number cell.
Press CUSTOMER DETAILS button on 'quotes' worksheet
Valid (reasonable)
This button should bring the user to the customer details sheet.
Customer details button (Ref 2.1) brings user to customer details sheet (Ref 2.3)
Press VIEW QUOTES button on 'quotes' worksheet
Valid (reasonable)
This button should bring the end user to the 'customers' sheet.
View quotes button (Ref 2.1) and the customers worksheet (Ref 2.4)
Press PRINT QUOTE button on 'quotes' worksheet
Valid (reasonable)
This button should enable the end user to print the quote off.
Print quote button (Ref 2.1) and the quote printing (Ref 2.5)
Test Data
Reason for Choice
Expected Outcome
Actual Outcome (screen shot reference)
Comments (Including corrective action taken)
Press STORE QUOTE button on 'quotes' worksheet
Valid (reasonable)
This button should enable the user to store quote details for future reference in the 'customers' worksheet.
Store quote button (Ref 2.1) and the customers worksheet (Ref 2.4)
Press MODIFY GROUPS/CARS button on 'quotes' worksheet
Valid (reasonable) data
This button should bring the user to the Groups & Cars worksheet.
Modify groups & cars button (Ref 2.1) and the groups cars worksheet (Ref 2.6)
Press MODIFY MULTIPLIERS button on 'quotes' worksheet
Valid (reasonable) data
This button should bring the user to the Multipliers worksheet.
Modify multipliers button (Ref 2.1) and the multipliers worksheet (Ref 2.7)
Press VIEW CURRENT button on 'quotes' worksheet
Valid (reasonable) data
This button should allow the user to see the quote currently being written in linear form.
View current quote button (Ref 2.1) and the data worksheet (Ref 2.8)
REF 1.1 REF 2.1
REF 2.2 REF 1.2
REF 2.3 REF 2.4
REF 2.5 REF 2.6
REF 2.7 REF 2.8
Test No: 2
Purpose of Test: To issue an accurate insurance quote. This involves details such as customer gender, age and car model being correct; all the factors that influence the price of a quote.
(To see if all values expected to appear, appear in the quotes worksheet interface in the right place, (see end user req. 1.3, ii)).
Test Data
Reason for Choice
Expected Outcome
Actual Outcome (Screen shot reference)
Comments (including corrective action taken)
INPUT 0118 into customer number section on 'quotes' worksheet
Valid
(reasonable) data
The customer number 0118 should call up the following data; Sarah Davies, 3 Merrydown Terrace, Bath, BA8.
Data received accurate (Ref 2.9)
SELECT male in gender option box.
SELECT 'Ferrari 575M in car combo box. SELECT 45-49 in age combo box.
SELECT low risk in risk area combo box. SELECT fully comprehensive in insurance type list box.
SELECT/UNCHECK extra driver check box. SELECT 5 years no claims bonus in spinner.
CALL UP Total without discount, CALL UP no claims discount and CALL UP total cost.
Valid (reasonable) data
Car data should appear in cells: G7, G8 and G9 through lookups (Ref 3.0). Age data should appear in cell G11 through lookups (Ref 3.0) Insurance risk should appear in cell G12 through lookups. Insurance type should appear in cell G15 through lookups (Ref 3.0). Extra driver data should appear G13 through an IF statement (Ref 3.0). No claims data should appear in G14 again through an IF statement (Ref 3.0).
Total w/o discount should appear in cell H17, NCD in cell H19 and total cost in cell H21. All of these through the Multiplying and subtracting.
All data in correct place (Ref 2.9)
REF 2.9
REF 3.0
Test: 3
Purpose of Test: Must be user friendly, e.g. be as easy for the user to use as possible.
(To check that nothing other than what is needed appears on the quotes interface (see end user req. 1.3, iii)).
Test Data
Reason for choice
Expected outcome
Actual outcome (screen shot reference)
Comments (including corrective action taken)
Only customer details are entered, e.g. 0101 calls up: John Jones, 25 Main Street, Bristol, BS7
Valid (reasonable) data
All other fields in the interface should be blank
A green triangle appears in cell D6.
In cells H17, H19 and H21 green triangles and the sign: #VALUE! Appear (REF 3.1)
(Get cell positions from previous reference (2.9)).
In order to eradicate these errors, I took away background error checking by un-checking the enable background error checking box (REF 3.2) which causes the green triangles. Secondly, I used Conditional Formatting to take away the #VALUE! signs.(Ref 3.3)
No customer details or other details are entered at all in the quotes interface.
Valid (reasonable) data
All cells blank.
#N/A symbols appear in customer details section, the adjacent section and the totals section. Also a % value that should not be seen at this stage is shown. (Ref 3.4)
To take these symbols away, IF statements had to be used. (Ref 3.5) They were placed at the beginning of the existing formulae (Ref 3.6)
REF 3.1 REF 3.2
REF 3.3 REF 3.4
REF 3.5
REF 3.6
N.B:- the IF statements at the beginning of each formula used to eradicate the #N/A symbols that appear when no data is entered into the cells. I obtained this formula from the following source:
Hodder & Stoughton
Spreadsheet Projects in Excel for Advance Level 2nd Edition.
Julian Mott & Ian Rendell
ISBN: 0-340-81202-8
Test: 4
Purpose of Test: Completed quotes need to be stored for future reference; these contain customer details and the actual quote details e.g. the car etc.
Test Data
Reason for choice
Expected outcome
Actual outcome (screen shot reference)
Comments (including corrective action taken)
Press STORE QUOTE button on 'quotes' worksheet
Valid (reasonable) data
This button should enable the user to store quote details for future reference in the 'customers' worksheet.
Store quote button (Ref 2.1: Page 5) and the customers worksheet (Ref 3.7)
REF 3.7
Section 4: Evaluation: Achieving the User Requirements
The end user requirements that were made at the beginning of the project are shown below. Under each one, there is a description of how well that particular requirement has been met:
* User must be able to move freely between worksheets (all macros used in the system must work).
As the testing section of the project shows previous, all the macros do work as intended and they bring the user to where they are supposed to (see Test 1: screenshots Refs 1.1-1.8). This inevitably saves the user a substantial amount of time and from the information mentioned in the problem section of the project; it proves much quicker and more efficient than if they had to be written by hand. The macros working correctly is an essential factor in allowing the end user to move freely between worksheets, hence the system fulfils this criterion well.
* To issue an accurate insurance quote.
As the testing has shown previously, the values that are expected to appear in certain places do so. This means the customer no. system works as it should as it calls up the customer details as shown in testing (Ref 2.9). Also the various formulae used in the quotes system such as the Lookups and IF statements were used correctly in order to call up the necessary data from the other sheets in the system. The forms used in the quotes interface also work as expected meaning they were correctly assigned to the areas of data they incorporate, e.g. the car combo box (testing Ref 2.9) needed to be assigned to the car name column in the groups & cars worksheet (testing Ref 2.6).
This all contributes in making the quote accurate. Improvements that could have been made in this area of the system would be to add a combo box to the customer number system that way the number does not have to be entered and also to validate this cell so that an error message appears if the data entered is invalid. This would aid the end user. Although the introduction of new customers has to be taken into account and the combo box system may prove difficult to accommodate this.
* Must be user friendly (nothing other than what is needed appears on the quotes interface).
In order for the system to be user friendly, the end user needs to be able to understand and operate the system easily.
The system allows the user to do this by not including anything on the screen which may confuse or mislead the end user.
Once more the testing shows that the system needs some adjustments in order to meet the end user requirements. These adjustments can be seen in the testing section; Test 3: Refs 3.0-3.5. These minor changes in formulae and formatting were essential in making the system as easy to understand for the end user as possible. The error messages that have been hidden as a result of the formulae and formatting could have easily misled the user into thinking they had done something wrong or that there was an error with the system. All of this makes the system easier to use, thus making the system user friendly.
* Completed quotes need to be stored for future reference
The 'customers' worksheet (testing Ref 2.4) in the system allows quotes to be stored for future reference. This sheet can be searched via scrolling through quotes that have been issued. This works very effectively as the end user can view quotes that have been made before and view that particular customer's details. Storing quotes is very simple and is done by pressing the 'store quote' button on the 'quotes' worksheet (testing ref 2.1) after a quote has been completed to the customers requirements. The quote is stored permanently in the system via exiting the system and SAVING changes. One factor that could have been improved in this section is that the quotes could have been dated to view the oldest/newest quotes. Although the system works in a way that the oldest quotes are stored towards the bottom as they filter downwards.
* The quote's invoice must look professional.
The printed invoice (Fig 1.2 overleaf from Page 19) looks quite professional in its appearance and overall layout. The invoice does not use excessive colours to try and get the 'business' look and feel. If the invoice were to be used in its proper environment, it would need a copy of the invoice attached for the firm's future reference. This would mean that the printer settings for the printing of an invoice would be set to 2 copies. It would also be printed on much thinner paper giving the invoice 'feel'. If I had more time on this section I would have added some terms and conditions that many invoices have, e.g. that the prices of insurance quotes are subject to change without notice etc.
