- The implementation Process
2.1
- Front Page
-
Insurance Groups/Cars Worksheet
-
Quotes Worksheet
-
Multipliers Worksheet
-
Customers Worksheet
-
Data Worksheet
-
Customer Details
-
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:
- Start recording (on the front worksheet)
- Switch to the quotes worksheet
- 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
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
REF 2.9
REF 3.0
Test: 3
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
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.