Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Contents
1. Background............................................................................................1
2. Identification of the problem..................................................................1
3. Identification of client, end user and audience......................................1
4. Identification of training needs / skills and methods used.....................2
5. User requirements as I/P/O.....................................................................2
6. Explanation of potential solution............................................................3
7. Initial Test plan.......................................................................................3
8. Completed test plan and evidence of testing.........................................4
9. Evidence of corrective action.................................................................7
Background
Ms Simpson is the manager of the local convenience store FRESH, which is located in centre
of the busy market in Whitechapel road. This store has been running for almost 10 years also
over the past few years the store has gained a very good reputation and receives a large amount
of customers as the store is right near the Whitechapel underground station. At the moment Ms
Simpson is using paper based system to record the employees detail, salary and the hours that
they work each week. All together she has 12 employees, 5 of whom are part time and 7 full
times also their salary is decided by the hours they work and loyalty (the employees that have
been working for over a year gets paid higher salary). Therefore, it can be time consuming
recording all this data into a paper and the paper based system is less reliable as paper based
system is more likely to have more errors. Therefore this creates a noticeable quantity of human
error such as getting the part-time worker salary mixed up with the full time-worker salary.
Identification of the problem
Ms Simpson is finding it difficult to keep track of all the employees record, as manual paper
based system can have a lot of errors. Ms Simpson is now concerned that this could lead to
further problems, which could affect her business. This is because she had to recruit more
employees seen shes getting more customers than usual.
The paper based system is especially time consuming also it can cause many problems such as:
Miscalculation of the overall wages can increase the business expenses and therefore this will
have an effect on the business profit.
If the right wages isnt given to the right employees than the employee could quit their job as
they wont be satisfied with their payment.
Finally, it will be harder to identify the numbers of hours each employee work every week as
paper work can be lost or there could be a human error on the paper work.
These problems can be avoided by having a virtual system that records all the data for example
using spread sheet to calculate the total salary over the year and keep record of the employees
absences.
Identification of client, end user and audience
My client for this project is the store FRESH. Ill be creating a system that will help my client
to keep a track on staff absences and total salary over the year more efficiently. The end user will
be Ms Simpson herself as she will be the only one who will have access to this system. The
Page 1 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
audience will be the staff as their detail will be recorded also using the system their salary and
hours that they have worked will be calculated.
Identification of training needs / skills and methods used
I had an interview with Ms Simpson to identify the level of I.T skill that she has also I gave her a
questionnaire, with specific question that will help me to identify her training need. Moreover,
she has the basic knowledge and therefore I am going to make a user menu to guide her through
the system.
User requirements as I/P/O
1.
A password is required to open the system, this
way the data is kept secure from anyone except
my client. Therefore the data isnt deleted and
changed accidently.
System Security:
Input: The user enters the password
Processing: The systems will checks the data
and see if the password is correct or incorrect.
Output: If the password is correct access will
be granted and if its wrong access will be
denied.
2.
House style:
The colour of font, house style and colours will Input: Insert same logo, colour and font.
be similar to companys name and logo
Process: Make all the colours same on every
displayed on every sheet.
document of the system.
Output: Name and logo displayed on the
screen with the selected colours.
Navigation/ Hyperlinks:
3.
The client wants the system to be
Input: Add shortcuts such as v-lookup and
created in such way that anyone can hyperlinks so that the user can easily find what
they are looking for.
navigate thorough it easily.
Process: Spread sheet will hyperlink it
to the sheet you have set to and
therefore it will automatically guide
the end user through the spread
sheet.
Output: Navigate through the system easily
and the user dont have to look for things
manually e.g. hyperlinks will take him to page
automatically.
Page 2 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
4.
The client wants the system to reduce error
while operating thorough the system and
inputting data.
5.
The system should be able to calculate all the
calculation by itself to help the client to reduce
human error and produce accurate calculations.
6.
The system should be able to determine and
present the number of years each employee
worked to make it easier for the client to
identify the number of years each employee
worked.
7.
The system should be able to allocate the right
amount of wages each employee receives
based on the number of years they have
worked.
Validation checks/rules:
Input: Add validation rules to reduce the
chance of entering wrong data.
Process: The data entered is processed.
Output: An error message will pop up saying
the data entered isnt valid also tell the user
what to enter for example enter a number
rather than word.
Calculations (Staff wages ):
Input: The amount of hours an employee
worked.
Process: The total weekly hour is calculated
and then the wages is worked out from hours
worked.
Output: Total weekly wage before and after
tax is shown for each employees.
Conditional formatting:
Input: A number is entered on the column
where it says Number of years in
employment.
Process: The number is then checked by the
computer and then decided if it is below 1 or
above 1.
Output: Colour indication to show if the
number is below 1 or above 1. For example 3
would be highlighted with the colour green to
show that its above one.
IF Statement
Input: A number is entered on the column
where it says Number of years in
employment.
Process: The number is then checked by the
computer and then decided if it is below 1 or
above 1. After wards the computer allocates
the correct wages of the employee.
Output: All the employees wages based on
the number of years theyve worked is
displayed on the screen.
Page 3 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Explanation of potential solution
The new system will allow the user to record all the employees detail also it will make it easier
to calculate the weekly wages of all the employees. The system uses if statement to figure out
how much the each employees gets paid based on the number of years that they worked for
example if they worked more than 1 years they get paid 8.50 whereas if they worked less than 1
years they get paid 6.50. Afterwards, the system uses this pay scale to calculate each of the
employees weekly wages. Ive also considered using website and database to create the solution
of my client problem. However one of the main issues was that I didnt have enough experience
and knowledge to create such a system. Secondly, after questioning my client, I found out that
my client is a novice user and therefore Ive to create a system that is easy to use and wont put
my clients into any problems.
Page 4 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Initial Test plan
Test
numbe
r
Purpose
Type of data
Normal data
1
Check to see if the
password works
Erroneous data
Normal data
2
To see if the
Validation rule
works (range
checks)
Erroneous data
Extreme data
Normal data
3
To see if the
Validation rule
works (format
checks)
DD/MM/YEAR
Erroneous data
Normal data
4
To see if conditional
formatting and if
statement works
Extreme data
Erroneous data
Normal data
To see if V lookup
works
Erroneous data
Normal data
6
Calculation
Erroneous data
House style
Normal data
Expected outcome
The spread sheet will allow
access to the information.
The spread sheet wont allow
access to the information
The employees detail is
displayed
An error message is displayed
The employees detail is
displayed
The system will accept the
date
Error message will be
displayed
If the number 0 is entered the
cell changes to red and if it
above 1 the cell will change
into red.
The cell will have no fill, it
will stay white.
A error message will be
displayed
The detail of the employee is
displayed e.g. the wages
Error message pops up saying
that the value entered isnt
valid.
The value will be correct:
Multiply =58.50
Adding= 1676.40p
Subtracting= 48.50
It would give the wrong
value.
2.50
The hyperlinks work
7
Page
Erroneous
data5 of 14
The hyperlinks dont work
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Completed test plan and evidence of testing
Page 6 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Test
number
1
Purpose
Check to see
if the
password
works
Type of data
Test data
Expected
outcome
Actual Result
Normal data
Password
The spread sheet
will allow access
to the
information.
The spread sheet
wont allow
access to the
information
The employees
detail is displayed
An error message
is displayed
System access
granted
The employees
detail is displayed
As expected
The system will
accept the date
Error message
will be displayed
As expected
If the number 0 is
entered the cell
changes to red
and if it above 1
the cell will
change into red.
The cell will have
no fill, it will stay
white.
A error message
will be displayed
As expected
That number
employee is
displayed on the
screen also if he
It works
Erroneous data Password10
Normal data
2
To see if the
Validation
rule works
(range
checks)
3
To see if the
Validation
rule works
(format
checks)
DD/MM/YE
AR
Entering a number
between 1 to 12
Erroneous data Entering a letters
instead of numbers
e.g. A
Extreme data
Entering a number
close to the boundary
such as 1 and 12
Normal data
05/09/2013
Erroneous data 2013/09/05
Normal data
A number is entered
(below 1 or above 1)
e.g. 0 and 3
Extreme data
If the number 1 is
entered.
4
To see if
conditional
formatting
and if
statement
works
To see if V
lookup works
and drop
Erroneous data A letter is entered
instead of number
e.g. A
Normal data
Entering or selecting
a number e.g. 5
Page 7 of 14
Access
blocked
As expected
As expected
As expected
As expected
As expected
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
down list
6
Calculation
House style
7
is part time or full
time worker e.g.
the wages
Erroneous data A letter is entered
Error message
instead of a number.
pops up saying
E.g. G
that the value
entered isnt
valid.
Normal data
Multiply Sum= (9*
The value will be
6.50)
correct:
Adding all the total
Multiply =58.50
Adding=
wages after tax
Subtracting= (58.50- 1676.40p
20%of 58.50)
Subtracting=
48.50
Erroneous data Using wrong function It would give the
e.g. for multiplying
wrong value.
2.50
(9-6.50)
Normal data
The font and the
The hyperlinks
colour is similar e.g.
work
green
The hyperlinks are
linked correctly
Erroneous data The font and the
The hyperlinks
colour are different
dont work
e.g. blue, red and
black
The hyperlinks isnt
linked correctly
1) Password protection
I have created password to protect my spread
sheet so that the detail is kept safe and private. As
you can see if the password entered correctly the
If a wrong password is entered than Page 8 of 14
spread sheet opens. (Normal data)
an error message will be displayed
and as you can see in the screen
that it will not allow access to the
It doesnt work
Right
calculation
Wrong
calculation
As expected
As expected
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
2) Validation rule (range check)
If a number is entered between 1 to 12 the
detail of the employees will be displayed in
the screen e.g. how much they get paid in an
hour. (Normal/ Extreme data)
If a number is entered that is
above 12 and below one, than an
error message will pop up saying
that the number has to be
between 1 and 12. The
information of the employees
wont be displayed as the data
3) Validation rule (format check)
The format checks make sure that the
date is in the right format
(dd/mm/year), this will make it easier
for everyone to read and understand.
(Normal data)
If the date is entered in a wrong
format than an error message will
be displayed in the screen and it
will tell you to enter in right
format. (Erroneous data)
Page 9 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
4) Conditional formatting and if statement
I have added conditional formatting to
make it easier for the user to identify
which employee has been working for
more than years ( highlighted in green)
and which employees has been working
for less than one year(highlighted in red).
(Normal data).
Afterwards, using if statement the right
pay scale is given to the employees e.g.
below 1 year, the employee would be
paid
6.50 an hour and above 1year
the employees would be paid
8.50
If letters are entered
instead of number than the
data will be erroneous
data.
5) V- Lookup and drop down list
I have added v lookup to
the system so that it
would be easier for the
user to identify if the
employee is working part
time and full time.
Drop down list is added
to list all the employees
in one cell. For example
if you a number, then
6) Calculation
that employee with that
number will be displayed
Page 10 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
To work out the weekly wages before
tax, Ive multiplied the number of
hours by the pay per hour e.g. 9 hour *
6.50 =58.50. (Normal data)
To work out the weekly
wages after tax, Ive
used this formula Sum
=G13-(G13*$J$6). E.g.
20% of 58.50 and
subtract it from original
value = 46.80.
(Normal data)
Page 11 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
House style
7) House style
The system have similar colour to
the original company logo also Ive
added similar font to keep the house
style similar
Page 12 of 14
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Evidence of corrective action
Ive added conditional formatting
to colour code the numbers of
years each employees worked e.g.
if an employee worked less than 1
it would be coloured red and if the
employee worked more than one it
would coloured as red. When
putting command for the
conditional formatting, Ive put
both of the variables as equal to
Page 13 of 14
instead of greater than 1 and less
than 1. This caused the conditional
formatting not work the way I have
expected to work. (Erroneous data)
Naim Ahmed, Candidate number, AS ICT INFO 1
Cambridge Heath Sixth Form
Ive corrected my mistake by
correcting the command to greater
than and less than. After the
correction the conditional
formatting is able to identify which
colour put in the cell to represent
the number of years.
Page 14 of 14
