- The current system is inaccurate and most of the time he makes mistakes. For example, when she is working out percentage and grades for each student, she often makes calculation errors.
- It is very time consuming, especially the calculation of percentages attained in marked worked and grades obtained overall.
- As the names of students are not in order he finds it difficult to find the student to record a mark.
- The report could easily get lost.
- Also writing in individual names several times, which is time consuming.
Interviewed Questions
What is your position at this school?
What type of system do you use for your mark tracks?
What method do you use to mark your student work?
What problems are you facing with your current system?
How would you want your current system to be improved?
What do you think is the major failings in your current system?
Have you ever used a computer for tracking marks?
Can you give me any samples of your current system?
Is there a CD ROM drive in your computer?
Do you have a printer?
How certain are you when using computers?
How certain are you when using Excel?
What features and functions are you aware of in Excel?
Do you keep all your records?
Do you have any additional questions?
End User Requirement
Statement of user requirements
Mr Shah feels a computerized system will solve some of her current problems as she thinks she has some knowledge about Excel. She feels I will be able to create an effective, fast, and easy to use automated system to calculate her student’s marks for her, which will enable her to solve her problem. They are the following things she expects to get from a computerized system.
Input
- She wants the new system to enable her to make management decisions that she uses to support student, which need help, whether parents need contacting, whether other action is required e.g. Detention.
- The system should automatically calculate the percentage and grades of each student. This system will therefore make her work more accurate and easy to use.
- Mr Shah wants the new system to be able to enter student personal details, e.g. DOB, tutor group, surname, etc.
- She wants the new system to be able to edit student personal details. Also she wants the system to able to delete student personal details if a student moves form her teaching group.
Output
- Mr Shah the new system to use colour coding when displaying grades of each student, e.g. Green for A*, Blue for A etc.
- Mr Shah would like a report facility where she can select a student from a list and the report will show the student’s name, gender, from group, overall percentages mark, grade obtained and an automatic comment on progress.
- The system should be able to produce chart, which will analysis student overall performance and also compare girls’ and boys’ performance.
Processing
- The system will put the names of student in alphabetical order.
- Mr Shah wants the new system to automatically convert numerical marks to percentages.
Storage
- Mr Shah wants the new system to be able to store 30 students’ personal detail and also store results for up to 70 tests.
- Finally, the new system should not be used by anyone else except Mr Shah, I will make sure the system is safe and I will make sure it has a security code.
Backup protocols are a necessity in the event that the master copy is lost or destroyed. Backup up will be provided by an external hard drive, and the system will be backed up periodically, i.e. every week, or in special circumstances, for example, there is a significant in take of data into the system. Furthermore, the backup copy will be stored of site, so that if there's a natural disaster affecting the main site, the backup copy will remain. Plus, this method of backup can be described as differential backup, which practices a cumulative backup of changes from the last full backup , allowing a full system restore based upon the last full backup.
Due to the non existent computer skills of the employees, an introduction into the use of computers is necessary. To accompany the basic skills they will acquire, I will need to develop a user manual which will provide detailed instructions in performing various tasks in the system as well as maintaining it, which is vital to its integrity and reliability. However, Victor has advised me to provide a manual of minimal size and contents in order to avoid confusion and enable the employees to read it swiftly, so they can get on with the job as soon as possible.
Source Documents
The source document that I have provided is a copy of marks book that Mr Shah uses to record her student marks. As you can see there is a lot of data to be inputted by hand. This is one of the problems that Mr Shah has. The calculations of total mark and overall have to make using calculator, which is time consuming.
Objectives of the new system
- The new system will perform the following tasks:
- It should be able to identify students with poor performance by using a visual alert.
- Ability to edit incorrect mark, so whenever Ms Gammon make an accidental mistake, she will have the ability to correct it.
- The system should be able to produce average performance percentage of each student for her class.
- It should have the ability to Edit and Delete students’ details if required, thorough the use of data forms.
- Easy and clear to use.
- It should automatically calculate percentages and grades from the inputted a student marks and maximum mark available for each test.
- Cell, formulae protected to prevent accidental deletion.
- It should automatically work out a student’s grade, based on overall percentage for all tests taken.
- It should be able to generate an automated report for students showing student performance.
- Password (security) on file access.
- It should automatically colour code student grades.
User Skill Level
Mr Shah has used Microsoft Excel but does not use it to calculate percentage, and grades of her marks. She is an average user, which means she does not understand most of the advanced features. Such as Vlookup, IF Statement, Macros, etc.
Project Constraints
Mr Shah uses school standard PC that I will be using to calculate percentages and grades for the new system. It has a four processor, which has 512 MB of RAM and 30 GB hard disk. It has Microsoft software including the excel 2000. Mr Shah Pc has CD Rom, USB and floppy drive, which are also connected to school network. This will make my work to be easy when loading the new system onto her computer because I can store it on the CD or floppy disk and transfer the system. Ms Gammon has printer in her office, which will be used to print out reports of student performance
Performance Criteria
These are the performance criteria that are going to be used in the new system that Mr Shah asked for:
- MR Shah will be able to correct mistakes that she might make during marking; this will make the system to be more neatly presented and pleasant.
- It should be able to add and delete students if required.
- The system will generate automated reports of each student by click of a button showing their performance that can be sent to parent.
- It will be easy and clear to use.
- The new system will produce an average performance percentage for each student. And also it will automatically convert numerical marks to percentages.
- It should have protection to prevent accidental deletion of important formulae, numbers, table, chart and text. All the sheet and cells in the system must be protected with security to prevent these.
- Graph and charts should be generated quickly and easily to compare boys and girls performance, All student and by form.
- The new system will not take more than 4seconds to view performance and grades for each student.
- The system will generate automated colour coding when displaying grades of each student.
- The system will store 40 student personal details, and also store results for up to 50 tests.
- Mr Shah will be able to select student from a list and the report will show the student’s name, gender, overall percentage mark, grade obtained and an automatic comment on progress.
Design
Choice of Software
I think that Microsoft Excel is the best option of software because it has got many features such as:
-
It has advanced functionality such as pivot table, sort, forms, Vlookup, average, and write protection. This will make things more automated for the user.
-
Pivot: this will hold table for charts. This will not need to be seen by the user. When they select the chart that they require on the student summary sheet, the pivot table on the sheet will change to represent that choice. It will also used to summaries archive data for use in charts showing:
- By gender
- Form
-
Data entry form will be able to edit and delete student records.
-
Conditional formatting will allow automatic shading of cells to give visual message for highlighting grade attained and also table displaying student grades.
-
Macros can be used to perform complex tasks at the press of a single button.
- It can perform complex calculation such as working out the grade for each student.
-
User Forms for controlling actions with macros using buttons.
-
Chart /Graph: can be used to display output of information such as averages, predicted grades etc.
-
The user wants the system to be as error-free as possible. Excel’s Validation facility will allow me to set up range validation to control data input when entering student personal details, overall mark etc.
- Protections of cell so that user cannot accidentally delete information.
Statement of Input, Processing & Output requirement
The input would be:
Personal Details:
First name
Surname
Gender
Date of Birth
Form
Roll number
Student marks:
Assignment marks
Maximum /minimum mark
Grade Boundaries
The Processing would be:
Calculating the grades through a Vlookup to the grade boundaries table.
Sorting, grouping and summarizing data with a pivot table.
Calculate the average of total marks
Using Vlookup work out a grade for the average.
The output would be:
Pivot Chart/Graph showing comparisons between gender, form-by-form and overall year group achievement.
Give students their grades using report card.
Table displaying student grades.
Creating report for using student with performance.
School letter, which would be sent out to parent using conditional formatting to highlighting grade attained.
Data Set
Because of Data Production Act, I cannot be able to use the real detail of Mr Shah Students. For that reason, I will create my own data set of example student details, which I will work with.
I will also use grade boundaries and colour coded:
Other data that I will use is to convert numerical values to percentages. Each test they will take will have minimum and maximum marks.
E.g. 3 three test
Global Diagram
Implementation
In order for me to start my project I need to use spreadsheet to start the project, I clicked on Start and the clicked on programs and then clicked on ICT and opened up Microsoft Excel.
After clicking on Microsoft Excel, the programmed had opened up, as you can see a screenshot below how the spreadsheet looks like.
After opening Excel I started creating my user form, I will show the steps of me creating the form.
As you can see above I clicked on Tools and the clicked on macro and opened up Visual basic editor, as you can see below what had come up next on the screen.
I then chose to add macro buttons in the user form to perform specific tasks; I clicked on one of the icons to create a macro button. As you can see below the icons I chose was the command button.
After adding macro buttons I then chose to add a background picture. I added a picture by clicking on a icon pictured below.
As you can see below after choosing a background it had come up on the screen below. This is the main menu when you open the system up.
