Information Technology Practical Exercise 2001: PostQuick Parcels
Information Technology Practical Exercise 2001: PostQuick Parcels
DESIGN
Requirements
There are three main tasks that need to be dealt with in producing this new system:
. It should check and price each parcel consignment
2. It should produce an itemised consignment note for each customer
3. It should produce daily management statistics on the number of parcels, total weight and total revenue
Software
With this in mind I have decided to use Excel to produce the PostQuick system. Excel is a widely known and used program, and thus there is greater security in using a system produced on this package. It is also likely to integrate with any existing software/any software that may later be added.
Moreover, as Excel is so widely-known, if any enhancements need to be made in the future and the original designer and creator is not available to make these changes, then it is unlikely that the PostQuick management will have trouble finding a replacement to make the necessary alterations. The system will be easy to modify and add new functions to.
As Excel is such an established package, it is also unlikely that the system produced on it will have many bugs that aren't simple to correct. It is also known to have good access times when retrieving data from a database. The files do not take up much disk space, and thus the hardware specification will not be hard to fulfil. Of course the better the hardware, the better the system's performance will be.
As a spreadsheet package it performs the mathematical formulae needed to carry out tasks such as calculating prices and statistics. In addition to its mathematical usefulness Excel performs functions such as filter, subtotal and macros needed to produce the application. These functions will be necessary to produce the itemised consignment note for each customer.
Lastly, Excel is easy to use - both to design a system and also for the users (even novices) who will be operating the system. The users will benefit greatly, especially in terms of time - tasks such as producing the consignment note will take far less time than if one were to do it manually. Also, repetitive tasks such as recording transactions will have less scope for mistakes due to the validation in the system.
Hardware
The requirements are not very hard to meet. As Excel is being used, there does not need to be a very large hard disk. One or two gigabytes comes as standard these days on new machines, and this is far more than there needs to be. A printer is needed. As there will not need to be large quantities of printing occurring, provided it is fairly fast an inkjet would suffice. A keyboard and mouse will also be needed to use the system.
The System - Technical Documentation
There will be six worksheets:
. Parcels
This will hold all the main transaction details, including parcel weight and dimensions, parcel price and end address, consignment number of parcel and date of transaction. The sheet will have links to the Prices sheet.
2. Consignments
This links to the Parcels and Prices worksheets, and details the number of parcels in a consignment, the total consignment weight, total consignment price and the end address(es).
3. Prices
This worksheet stands on its own, and holds the table of prices in relation to the weight of a parcel. This can be changed as it needs to be.
4. Daily Statistics
This worksheet links to the Parcels worksheet. It summarises the main information of the sheet in relation to a specific date, and shows the number of parcels that come through in a day, their combined weight and the total revenue of the day.
5. Receipt
This worksheet links to the Parcel worksheet through a filter. It produces an itemised receipt for a customer, and then subtotals the weight and total amount of money due.
6. Consignment Note
This worksheet links to the Parcels and Consignments worksheets through a filter. It produces an itemised delivery consignment note, and then subtotals the Consignment weight and price.
7. Client Details
This worksheet stands separately from the others, and simply lists the details of customers so that they may be contacted if a problem arises.
All the data entered into the system is done so through a keyboard, that is it is typed in.
PARCELS (See attached drawing of proposed design [ATTACHMENT 1])
N.B. There are two columns holding the Consignment Number. This is because the consignment sheet needs to be linked to the Parcels sheet. The most effective way of doing this is using function 'VLOOKUP' using the consignment number. For this to be done the consignment number must be the leftmost column in the Parcel worksheet. At the same time, the Client ID should be the first column in the system that should be seen. Therefore this first column containing the consignment number will be hidden, and only one column showing the consignment number can be seen.
Column
Formulae
Validation
Consignment Number
As this column will be hidden (this is done by reducing the width of the column to nought), it should be linked to the other consignment number column. Therefore if the other 'Consignment Number' column is column H, and this one is column A, then the formula for cell A2 will be '=H2'
Custom validation; (using previous column designation), AX=HX, where X is the row number.
Client ID
None
Text length; between 1 and 5 characters long.
Parcel Weight (kg)
None
Custom validation; The value entered should be greater than or equal to 1, less than or equal to 30, and (to ensure that the total consignment weight is less than 200kg) this value and its corresponding value in the 'Running Weight' column should be less than or equal to 200.
e.g. if Parcel Weight is column C, and Running Weight is ...
This is a preview of the whole essay
Custom validation; (using previous column designation), AX=HX, where X is the row number.
Client ID
None
Text length; between 1 and 5 characters long.
Parcel Weight (kg)
None
Custom validation; The value entered should be greater than or equal to 1, less than or equal to 30, and (to ensure that the total consignment weight is less than 200kg) this value and its corresponding value in the 'Running Weight' column should be less than or equal to 200.
e.g. if Parcel Weight is column C, and Running Weight is column K, cell C2's validation would be
'=AND(C2>=1, C2<=30, C2+K2<=200)'
Also, as the 'Running Weight' will not work unless a value there is a value in the 'Consignment Number,' and both are linked to the 'Parcel Weight' there will be an input message which comes up when any 'Parcel Weight' cell is selected saying 'Enter Consignment Number FIRST.'
Date
None
Date; greater than 01/01/2001
Parcel Length (cm)
None
Custom validation; (Assuming the length is the largest side) The value entered should be less than or equal to 150cm. Also, this cell plus its counterparts breadth and height should all add up to less than or equal to 300cm. So, if Length, Breadth and Height are respectively columns E, F, and G then for cell E2 the validation would be '=AND(E2<=150, E2+G2+F2<=300)'
Parcel Breadth (cm)
None
This would be similar to the validation for length. Again, assuming that length is the longest side, the validation will ensure that the value entered for the breadth is smaller than the value entered for the length. Also it will be validated so that the breadth plus its counterparts length and height all add up to less than or equal to 300cm.
Using the same column designations as above, the validation for F2 would be
'=AND(F2<=E2, E2+F2+G2<=300)'
Parcel Height (cm)
None
This would be similar to the validation for length. Again, assuming that length is the longest side, the validation will ensure that the value entered for the height is smaller than the value entered for the length. Also it will be validated so that the height plus its counterparts length and breadth all add up to less than or equal to 300cm.
Using the same column designations as above, the validation for F2 would be
'=AND(G2<=E2, E2+F2+G2<=300)'
Consignment Number
None
Whole number; greater than or equal to 1.
Parcel Price
Function 'VLOOKUP':
Lookup value: 'Parcel Weight' specified.
Table array: Both columns on 'Prices' worksheet
Column Index No: The 'Price per Parcel' column on the 'Prices' worksheet will be in the B column (The lookup value- Weight in this case- must be the leftmost column), so it's column number will be 2.
Range Lookup: True, as only the closest match in weight needs to be found.
Decimal/
The cells are also formatted as currency, and to 2 decimal places.
End Address
None
None
Running Weight
Function 'SUMIF':
Range: The first 'Consignment Number' Column on the Parcel worksheet
Criteria: the 'Consignment No.' of the corresponding cell.
Sum Range: Column 'Parcel Weight' on Parcel worksheet
e.g. (formula for one cell)
=SUMIF(A:A,A2,C:C)
The value entered, and its corresponding value in the column 'Parcel Weight' should add up to less than or equal to 200kg. If columns 'Running Weight' and 'Parcel Weight' are columns K and c respectively, then the validation for cell K2 would be
'=C2+K2<=200'
CONSIGNMENTS (See attached drawing of proposed design [ATTACHMENT 2])
Column
Formulae
Validation
Client ID
Function 'VLOOKUP':
Lookup value: 'Consignment Number' specified.
Table array: All columns on 'Parcel' worksheet
Column Index No: The number of the 'Client ID' column on the 'Parcel' worksheet; that is if 'Client ID' was in column B, it's column number would be column 2.
Range Lookup: False, as an exact match needs to be found
Text length; between 1 and 5 characters long.
Consignment Number
None
Whole number; greater than or equal to 1.
Number of Parcels
Function 'COUNTIF':
Range: Column 'Consignment Number' on Parcel worksheet
Criteria: 'Consignment Number' specified
e.g. (formula for one cell)
=COUNTIF(Parcels!H:H,B2)
Whole number; greater than or equal to 1.
Consignment Price
Function 'SUMIF':
Range: Columns 'Parcel Price' and 'Consignment Number' on Parcel worksheet
Criteria: 'Con. No.' specified
Sum Range: Column 'Parcel Price' on Parcel worksheet
e.g. (formula for one cell)
=SUMIF(Parcels!H:I,B2,
Parcels!I:I)
The cells are formatted as currency, and to 2 decimal places.
Consignment Weight
Function 'SUMIF':
Range: Column 'Consignment Number ' on Parcel worksheet
Criteria: 'Con. No.' specified.
Sum Range: Column 'Parcel Weight' on Parcel worksheet
e.g.(formula for one cell)
=SUMIF(Parcels!A:A,B2,
Parcels!C:C)
Decimal; between 1 and 200.
The cells are also formatted to 2 decimal places.
End Address
Function 'VLOOKUP':
Lookup value: 'Consignment Number' specified.
Table array: All columns on 'Parcel' worksheet
Column Index No: As for 'Client ID,' the number of the 'End Address' column on the 'Parcel' worksheet.
Range Lookup: False, as an exact match needs to be found
None
PRICES
Column
Formulae
Validation
Weight not over (kg)
None
Whole Number; Between 1 and 30.
The cells are also formatted to 2 decimal places.
Price per Parcel
(See Below)
Decimal; greater than 1.00
The cells are also formatted as currency, and to 2 decimal places.
PROPOSED DESIGN:
A
B
Weight not over (kg)
Price per Parcel
2
£ 7.50
3
2
£ 7.50
4
4
£ 8.00
5
6
£ 8.55
6
8
£ 9.10
7
0
£ 9.70
8
2
£ 10.35
9
4
£ B8 + .90
0
6
£ B9 + .90
1
8
£ B10 + .90
2
20
£ B11 + .90
3
22
£ B12 + .90
4
24
£ B13 + .90
5
26
£ B14 + .90
6
28
£ B15 + .90
7
30
£ B16 + .90
DAILY STATISTICS (See attached drawing of proposed design [ATTACHMENT 3])
Column
Formulae
Validation
Date
None
Date; greater than 01/01/2001
Number of Parcels
Function 'COUNTIF':
Range: Column 'Date' on Parcels sheet.
Criteria: The Date specified
e.g. (formula for one cell) =COUNTIF(Parcels!D:D,A2)
None
Total Weight
Function 'SUMIF':
Range: Column 'Date' on Parcel worksheet
Criteria: the Date specified
Sum Range: Column 'Weight' on Parcel worksheet
e.g. (formula for one cell) =SUMIF(Parcels!D:D,A2,
Parcels!C:C)
The cells are formatted to 2 decimal places.
Total Revenue
Function 'SUMIF':
Range: Column 'Date' on Parcel worksheet
Criteria: the Date specified
Sum Range: Column 'Parcel Price' on Parcel worksheet
e.g. (formula for one cell) =SUMIF(Parcels!D:D,A2,
Parcels!C:C)
The cells are formatted as currency and to 2 decimal places.
Receipt (See attached drawing of proposed design of worksheet [ATTACHMENT 4] and actual receipt [ATTACHMENT 5])
Column
Formulae
Validation
Client ID
Filtered, copy and paste
Text length; between 1 and 5 characters long.
Consignment Number
Filtered, copy and paste
Whole number; greater than or equal to 1.
Date
Filtered, copy and paste
Date; greater than 01/01/2001
Weight
Filtered, copy and paste
The cells are formatted to 2 decimal places.
Price per Parcel
Filtered, copy and paste
The cells are formatted as currency, and to 2 decimal places.
End Address
Filtered, copy and paste
None
Total (for weight and price only)
Function 'SUBTOTAL-->SUM'
The cells are formatted to 2 decimal places. The price total was also formatted to show currency.
In addition, there will be a button called 'Produce Statement.' It will be assigned a macro, so that when a client ID is typed into cell A2 and the button is pressed it will produce the consignment note. The macro will delete the old statement and delete the old filter results on the Parcels worksheet. It will then produce a new filter ('copy to another location' [on Parcels worksheet], criteria is the ID that has just been typed in). It will select the columns mentioned in the table above from the filter results, and copy and paste them in the same order as above. Finally it will produce the subtotal as mentioned above.
CONSIGNMENT NOTE (on system shortened to Con. Note)
(See attached drawing of proposed design of worksheet [ATTACHMENT 6] and actual receipt [ATTACHMENT 7])
Column
Formulae
Validation
Consignment Number
Filtered, copy and paste
Whole number; greater than or equal to 1.
Weight
Filtered, copy and paste
The cells are formatted to 2 decimal places.
Price per Parcel
Filtered, copy and paste
The cells are formatted as currency, and to 2 decimal places.
Parcel Length
Filtered, copy and paste
None
Parcel Breadth
Filtered, copy and paste
None
Parcel Height
Filtered, copy and paste
None
End Address
Filtered, copy and paste
None
Total (for weight and price only)
Function 'SUBTOTAL-->SUM'
The cells are formatted to 2 decimal places. The price total was also formatted to show currency.
In addition, there will be a button called 'Produce Consignment Note.' It will be assigned a macro, so that when a consignment number is typed into cell A2 and the button is pressed it will produce the consignment note. The macro will delete the old not and the old filter results on the Parcels worksheet. It will then produce a new filter ('copy to another location' [on Parcels worksheet], criteria is the consignment number that has just been typed in). It will select the columns mentioned in the table above from the filter results, and copy and paste them in the same order as above. Finally it will produce the subtotal as mentioned above.
CLIENT DETAILS (See attached drawing of proposed design [ATTACHMENT 8])
Column
Formulae
Validation
Client ID
None
Text length; between 1 and 5 characters long.
Surname
None
Text length; less than or equal to 20 characters long.
Forename
None
Text length less than or equal to 20 characters long.
Address
None
None
Contact No.
None
Text length; less than or equal to 14 (including spaces) characters long.
Design
The same design will be present on each of the six worksheets: the font 'Arial' will be used, font size 11, as it is easy to read. All the title cells' fonts will be in bold, while all normal cells will have normal the font style (no italics, underlining or bold).
The title row will have a light grey background and will be double the height of the other rows to highlight it. The title cells will also be frozen in place using freeze panes, so that however far you scroll down the user can still see them in the same place at the top of the worksheet.
The width of each column will be the smallest each can be whilst showing all the information. This can be done by double clicking on the width handles - this automatically re-sizes the column in relation to what's in it.
All the user has to do is type in the necessary details in the right cells. All the columns are titled clearly. The only things that are slightly different are the receipt and the consignment note. For this, the user types in the client ID of the customer (for the receipt) or the consignment number (for the consignment note) and then presses the button in the top right hand corner to generate the results.
Security
The 'Prices' sheet will be protected by a password (that needs to be changed regularly by the senior management). It needs to be protected so that it has unrestricted access (thus allowing a current price list to be printed out when needed) and yet only senior management will be able to alter the prices. There is a protection function in Excel that allows this to be done. The correct password (this will be 'modify') has to be entered to be able to update the data. Thus the sheet effectively becomes read-only to most people. However the management must remember to re-protect the sheet after modification.
What you would see if an unauthorised person tried to modify the prices:
The system also has to be backed up each night; the 'grandfather, father, son' method is recommended. The backup done on the first night is called the son. When the backup occurs on a new disc on the second night the son becomes the father, and a new son is created. Similarly, the third night's backup uses a new disc and becomes the new son, the old son becomes the new father and the old father becomes the grandfather. Only three generations are kept, and so the grandfather becomes the new son on the fourth night's backup. This ensures that even if the system is wiped out (for whatever reason) the majority of the data can be restored.
IMPLEMENTATION
View hard copy output attached to see the correct working of the system [ATTACHMENT 9] and hard copy of solution [ATTACHMENT 10].
Testing
The test data given was entered into the system to test the application. The highlighted was not accepted:
Customer
Parcel Weight (kg)
Length
(cm)
Breadth
(cm)
Height
(cm)
A
.5
25
2
5
A
5
00
60
40
B
35 (1)
20
70
50
B
20
90 (4)
30
20
B
a (2)
5
5
5
B
0.4 (3)
5
5
3
There were many other areas that needed testing also. I have created and will now document the test data and results.
The error message that comes up when the user enters something that the validation will not allow is:
When someone tries to modify a protected worksheet the error message is:
Testing
To test...
Method of testing
Test results
Consignment Number (AX=HX) validation on Parcel worksheet.
X = any number
Type in a numbers 2, 7, 13 in H column, see if it appears in the A column
As expected, all worked.
Client ID validation-all sheets (should be between 1 - 5 characters)
Enter 1, 2, 3, 4, 5, 6 and 7
characters-long client ID. (A, Cy, Bob, Eric, Daria, George and Freddie)
As expected, error message comes up for the 6 and 7-letter long client IDs. These are too big.
Parcel weight (should be between 1 and 30 inclusive). (Further testing)
Type in 0, 1, 16, 30, and 32.
Also done Using Test data Given. See above.
As expected, error message comes up for 0 and 32. These are too small and big respectively.
Parcel weight: 'Enter Consignment number FIRST' message should come up when you select a cell
Select three or four 'Parcel Weight' cells.
Expected response, showing that the validation works.
Consignment Weight.
i.e. Running Weight and Parcel weight (does not go over 200kg)
Under Client F and consignment 3, entered 7 parcels, each of 30kg.
(7 x 30 = 210)
As expected, the first six parcels were allowed, the last was not.
Length is less than or equal to 150cm.
Using Test Data Given. See Above.
Using Test Data Given. See Above.
Breadth and height should both be less than or equal to the length
Entered length as 120cm, then breadth and heights of 100cm, 120cm and 130cm.
As expected, error message comes up when 130 was entered, both for breadth and height.
Total Dimensions (do not go over 300cm)
Enter values
50cm, 50cm, 50cm; 150cm, 100cm, 50cm; and 150cm, 100cm, 100cm in length, breadth and height cells respectively.
As expected, error message comes up when height 100cm is entered.
Consignment Number and length, height and breadth are whole numbers
Type in 1.5 for Consignment Number, 111.1 for length breadth and height.
As expected, error message comes up for all four tests.
Parcel price on Parcel worksheet
Type in weight 9.50 (£9.10 should come up), and weight 25 (£15.75 should come up).
Both tests worked. Expected answers were given.
Running Weight formula (should sum the weights of all parcels in the consignment number specified, add the weight just entered and be <=200kg)
Same as Consignment Weight, tested earlier. Under Client F and consignment 3, entered 7 parcels, each of 30kg.
(7 x 30 = 210)
As expected, the first six parcels were allowed, the last was not.
Consignment Worksheet -
Type in Consignment no. and all other information should come up.
Enter consignment numbers 7 and 8. Compare results generated to those that should be given.
As expected, for both consignment numbers the correct data comes up.
Weight validation on Prices worksheet (only values between 1 and 30 should be entered)
Enter values 0, 16 and 32.
As expected, error message comes up for values 0 and 32.
Price formula on Prices worksheet
Calculate manually what answers should be for first 3 cells whose prices are 0.9 more for each 2kg extra.
As expected, the results calculated and the results given by the formula are the same.
Protection on Prices worksheet
Attempt to type something in.
As expected, an error message comes up (see above).
Daily Stats. No. of Parcels
Enter date '15/3/01' and compare result generated with actual answer.
Record a transaction with date '16/03/01' on the Parcels worksheet. Enter '16/03/01' on Daily Stats. and compare result generated with actual answer.
As expected, both answers generated gave the same result as the manual results.
Daily Stats. Total Weight
Enter date '15/3/01' and compare result generated with actual answer.
Enter '16/03/01' on Daily Stats. and compare result generated with actual answer.
As expected, both answers generated gave the same result as the manual results.
Daily Stats Total Revenue
Enter date '15/3/01' and compare result generated with actual answer.
Enter '16/03/01' on Daily Stats. and compare result generated with actual answer.
As expected, both answers generated gave the same result as the manual results.
Receipt Macro
Enter Client ID 'A,' press 'Produce Receipt' and compare results generated to transactions on Parcel worksheet.
As expected, results generated are of all the 'A' transactions on the Parcel Sheet.
Consignment Note Macro
Enter the consignment number '11,' press 'Produce Note,' and compare results generated to transactions on Parcel worksheet.
As expected, results generated are of all the transactions on the Parcel Sheet to do with the consignment number 11.
Surname and Forename on Clients worksheet (should be less than or equal 20 characters long)
Enter names 15, 20 and 22 characters long in both Surname and Forename fields.
As expected, error message comes up for Surname and Forename of 22 characters.
Contact Number on Clients worksheet (should be less than or equal 14 characters long)
Enter a telephone number of 14 and 16 characters (including spaces) long.
As expected, error message comes up for telephone number of 16 characters.
Testing As A System
The results of this are shown in the 'unit testing'. It is possible to see that the whole system works together. This is especially shown in the macro for the consignment note and on the consignment worksheet; both of which largely link to the other sheets.
EVALUATION
Effectiveness
There were three main tasks that need to be dealt with in producing this new system. Looking at the design and working of the system I feel that the application created fulfils all the requirements set. The system does what it is supposed to do.
The system designed:
* will both checks and prices each parcel consignment
* will produce an itemised consignment note for each customer
* will produce daily management statistics on the number of parcels, total weight and total revenue
The system reduces the time it would take to do the same tasks by hand. The consignment note especially shows this. It is much quicker to type in a name and press a button to get a receipt than it is to look up all the transactions of a particular customer and copy them out.
Usability
The system is easy to use. The validation ensures that there is minimum scope for error. Little or no training is required to use the system; it is all very self-explanatory. This shows the presence of a good user interface.
As shown above the system reduces the amount of time it takes to carry out a task. It also cuts out tedious and repetitive tasks, which will result in far fewer mistakes being made. The system also gives users quick access to information they need, as shown by the Prices worksheet (quickly gives you access to a current price list which can easily be printed out) and the Daily Statistics worksheet (gives up-to-date information on daily transactions).
Maintainability
The system can be modified as needed without much trouble. This can be done by anyone with good knowledge of the package Excel. There are quite a few people that would fulfil this specification, so even if the original creator of the application were not around to make the modifications it would not be hard to find someone who could.
As the system has been thoroughly documented this is especially true.
Overall
Overall, this is a very effective system. It fulfils - if not exceeds - the original requirements, is user-friendly and should be relatively easy to maintain.
