In order to accomplish my task I will do the following:
Research: I will need to find out the price of admission, student/teacher ratio, and the cost of coaches. I will do this by going on the Internet and phoning the companies.
Collect information: I will need to find out the number of pupils. I will do this by sending letters home and asking for a deposit to ensure commitment.
Enter information: After I find out the information I need, I will enter it into a system using Key-to-disk.
Test: after the system is organised I will test the system to make sure it works correctly. I will test it using data validation and data verification checks.
Evaluation: I will make sure that the system meets its criteria. I will also solve any problems and make improvements.
Specification
What must the new system be able to do?
- Enable data to be changed. E.g. the admission price
- Tell if there is a profit or loss, this is I have to break-even or make profit for a successful trip
- Apply the student/teacher ratio, this is because it can make the difference between the final cost of a trip
- Calculate the total cost per person, this is so that I can inform the parents about the cost of the trip
- I will need to calculate the total cost of coaches. I will need more than one coach, therefore I will need my spreadsheet to tell me the total number of coaches once I know the number of seats in a coach.
- The information will need to be displayed in an organised way so that it is user friendly and the data is easily accessible.
- The system must be able to save the information so that any changes made can be saved.
-
The new system needs to be exact, because it will carry out important calculations. If any faults are made then the profit/loss can be wrong.
- It also has to perform quickly, when any of the values are changed, the relevant data should automatically change.
How useful will a computer-based system be?
The computer system will allow anyone to calculate the cost of a trip easily and efficiently. This will mean that the system can solve any problems straight away. With a computer-based system, the organizer will have better control.
There will be some tests that will need to be carried out to test if the new system. The person who will be using the system will test to see if it is easy for him/her to use and whether it is user friendly. If there are any errors, adjustments will be made to improve the system. The system must be accurate; I will then test using valid and invalid data to see if the system outputs the correct responses.
Design of ICT System
Write a letter home or phone home informing the parents about the trip and the rough cost. Ask for a small deposit it the child is coming.
I can start to find out the cost of coaches and discounts and other important things once I know the total number of people coming.
Find out the student/teacher ratio so that I can work out the total number of tickets I will need, find out if I can get any discount.
Once all data is gathered, I can begin to design my spreadsheet.
ask for the rest of the money once the final cost of the trip is worked out
Receive the rest of the money so that all the facilities can be paid for
Go on the trip.
Software Resources
I will look at different software to see if it is suitable for the system.
Word processor (Word 97) - This type of software is mainly used for letter writing and essays. It is used for typing text, which can be edited, rearranged and also printed. The word processor can check for spelling mistakes or grammar mistakes; it also contains a dictionary and thesaurus. I have selected to use Word 97 because:
- I can present findings
- Text can be formatted easily
- It is compatible with Excel 97 and can merge files (cut and paste)
- Spell-check
- Produce different styles of text, different sizes, fonts and colours.
Spreadsheet (Excel 97) - This is made up of rows and columns of cells that can be used to contain text, numbers and most importantly formulas. These formulas can be used to calculate figures, look up information from other cells and to carry out validation checks. The rows are given numbers and columns are given letters, which together give the position of the cell e.g. (A3). With formulas, instead of inserting direct numbers, cell references are given so that if the value changes, all those that are affected automatically change, this makes it much easier for the user. The information in a spreadsheet can also be used to show a graph or a chart. The spreadsheet is good for use in our system because it contains formulas that make it easy to calculate any profit or loss also if any data is entered or changed, the application automatically calculates the results. Excel can do all this easily and quickly, which is why I have chosen it.
Screen thief - To capture graphical images and be able to paste them into a word document.
I have chosen to use Excel 97 because it is popular and user friendly. I have also chosen to use Word 97 because it is easy to use and also widely available. They are compatible and files can be easily transferred from one application to another. I will also use screen thief to put in graphical pictures in this project. I will be able design the system when I am in school and when I am at home. Other people and schools also have this software so it would be ideal.
Hardware Resources
I will need the following hardware to complete these tasks:
-
VDU - will enable me to look at the information in the computer I have entered and use the Windows Icons Menus and Pointers.
-
KEYBOARD – so I will be able to enter the characters via keystrokes and change case quickly.
-
MOUSE – to make moving from programs and selecting options much quicker and easier.
-
DESKTOP –the store of the CPU (central processing unit), backing store, programs, files, and documents etc.
-
PRINTER – to print out documents.
-
FLOPPY DISKS – to save work and to ensure documents can be transported from home and school.
-
4.1 GB Hard Drive – to store the OP and software. E.g. Word.
-
Printer – to print out work.
-
Monitor – to view the programs on the system.
-
Pentium 2, 266 MHz Processor – Processes the information.
The hardware I am using is sufficient to run word and excel. Other people and schools have this hardware; therefore the user will recognizable it.
There are various ways of entering the information onto a computer so that the data can be processed or stored. I have chosen the one below:
-
Key-To-Disk - This is typing the data onto the computer using a keyboard, although this isn’t expensive and is easy to use, it does take quite a lot of time and errors are often caused by this method. But still, this method is practical and simple and can be used for our system.
Data Collection and Input
The data I have collected Are:
- The number of pupils – this was found by sending and receiving letters from parents asking for a deposit if they are interested, and phoning them to double check
- Coach Price – I rang different companies and went online to find the coach with the best value for money.
- No. Of seats in a coach – I rang different companies and went online to find the coach with the best value for money.
- Admission price – I rang the Museum and went on the Internet to find the cheapest price possible. I found that I would save £5 in I ordered online.
- Student/teacher ratio – I asked teachers that had gone on trips before and asked for their opinion on which was a suitable student/teacher ratio.
I made an editing table to manage my data easily, and it is also easy to change:
Validation and Verification
Data Validation
There are many methods to minimise any mistakes or errors in the information put onto the system, these are a few that I have used:
-
Character type check: This makes sure that the right types of characters have been entered. On my spreadsheet some of the columns are specially formatted for text, currency and numbers.
-
Range check: This makes sure the numbers are between specified ranges. On my spreadsheet the cost per person has been conditionally formatted so that nothing greater than £15 can be entered. If it is, an error message comes up:
-
Spell check: Makes sure that words have been spelt accurately. I used this on my Word document.
-
Grammar check: checks traditional grammar in text, it looks for the most frequent error made e.g. deleting repeated word.
I also used conditional formatting to recognise if I have put in unreasonable data.
This is what appeared once unreasonable data had been entered.
Data Verification
Data Verification is checking that what is on the spreadsheet is the same as what has been entered onto the spreadsheet, this can be done by looking at it etc. I did this by Re-checking that the correct data has been entered. This can be time consuming, as two people have to type in the information. I and someone else checked my spreadsheet but there weren’t any mistakes available.
Data and Program Structure
- The editing table is used to change data; it is there so that if I wanted to change the price of a coach to £50 the other sections that are linked to it would also change. This is done this by typing the data you wish to correct into the box.
-
The look up table is in under the editing table. It is the one with formulas. This is there so that it is easier to look up things, e.g. if I wanted to take 53 people, it would tell you if there was a profit/loss without having to look for it in the main spreadsheet. This will save time for the trip organizer and make it more efficient. The formula I have used to look up a the total cost is =VLOOKUP(B7,A15:I115,7,FALSE) B7 is the place where the number of students is located. A15 AND J115 is the whole spreadsheet not including the editing and lookup table. 7 is the column number which, in this case is the teacher number. FALSE is the word that should appear if the number of students isn’t in the table.
- Number of Students is the total number of students I plan to take, this can be changed and you can also see if there would be profit or loss if you plan to take that many number of students.
-
Total number of people is the number of students and the number of staff added together. To work out the number of staff needed, you do the total number of students rounded by the student/teacher ratio; you then round it up to a whole number. The formula used is =VLOOKUP(B7,A15:I115,7,FALSE), this has been explained above.
-
I also want to work out the number of coaches I will need depending on the number of students that are going. Dividing the amount of people going and the seat number per coach does this. The formula I used is =ROUNDUP(C15/$B$2,0). I have used this formula as it rounds the sum up to the nearest whole number. C15 is the column where it says the total number of people going. $B$2 is the cell in the editing table which refers to the number of seats on a coach, I have also used the absolute cell reference so that the number of seats in a coach stays the same all the way down.
-
To work out the price of the coaches, I multiplied the number of coaches by the coach price. The formula I used was =D10*$B$3. D10 is the cell which tells the number of coaches needed depending on the number of people and $B$10 is the price of a coach.
-
I needed to work out the total price of admission. I did this by multiplying the price per ticket, which is located on the editing table by the total number of people coming. This is the formula I used, =($B$3*B55)+($B$4*A55), $B$3 is the admission price for an adult, this is multiplied with the number of staff coming B55 as all of the staff are adult. This total is added together with $B$4 which is the admission price for a child, this is the multiplied with A55 as all the students are children.
-
The total amount of the trip is all the costs specified above added together. The formula I used is =E55+F55, this is the cost of coaches and the admission price added together.
-
The other column I need to work out was the price of person. To work this out I divided the total cost by the number of people. The formula I used was =G55/A55.
-
To work out if I had profit or not, I used the formula =IF($B$6>H55,"profit","loss"), this means that if the amount I charge per students is greater then the actual cost per student say profit, if it is less, then say loss.
-
The final thing I needed to work out was the amount of profit increase/decrease. I used the formula =$B$6-H55 to work this out. What it means is the price I am charging per student minus the actual price per student.
OUTPUT FORMAT
I used 3 different output formats; one of them was the lookup table. This allows the user to check information quickly and efficiently. This also contains conditional formatting which prevents silly mistakes.
The other output format I did was to write a letter home to parents informing them about the final price.
I also created a graph using Excel to show the trend of the cost per student depending on the number of people I take.
The graph shows a general downward fall as the number of people attending the trip increases, the price falls steadily. Nevertheless, when a new coach is introduced, the price rises gradually. Using a graph is useful because it illustrates to the user the amount of people not to take, for example, when a new coach is introduced.
Safety and Making Backups Of Work
Safety is among the most important aspects of a well-designed spreadsheet. To make back up copies of your work is priceless and I made absolutely sure that I always has a spare copy of work by not only saving it to different formats of my PC (i.e. My computer, My Briefcase), but saving it onto two different floppy disks, one main one and one master copy. I kept a saved version of it at school in my username login area as an extra precaution.
To keep my spreadsheet safe from others (i.e. anyone messing it up or changing data) I locked the cells by going to Tools, Protection and protecting the sheet. Here I put in a password that only I knew. I then tested my protection by trying to press a letter into the first cell and a menu box came up as can be seen below:
Testing of solution
To check that the new system will work a number of tests will need to be carried out. The person who will be using the system (head of history) will test to see if it is easy for her to use, and whether it is user friendly. If there are any errors, modifications will be made to improve the system. The system must be accurate, test valid and invalid data to see if the system out puts the correct response.
I will change the price per person from £7 to £20 to see what effect this has on my profit. I predict that as the price per person increases, the profit should also increase.
I will change the number of seats on a coach from 48 to 60 to see what effect this would have on the profit. As there would be more seats in a coach, the profit should also increase because more people would be able to fit in one coach which is £200 rather that paying £400.
I will change the student/ teacher ratio from 20 to 7 to see if the other figures change. Overall, the profit increase would decrease because more teachers would have to come.
In order to see of the spreadsheet worked, I changed the price per person from £7 to £20. I can see that as the price changed the column showing whether I would have profit/loss also changed, the higher the price per person = the higher amount of profit which is exactly what I predicted would happen.
Before
After
When I changed the number of seats on a coach from 48 to 60 the profit/loss column changed. As there were more seats there was more profit and as there were less seats there was less profit. This also proves that my prediction was correct.
Before
After
Changing the student/teacher ratio from 8 to 20 had an effect on almost every column. As the Student/Teacher ratio was higher the profit/loss column changed, the profit/loss column had more profit than before, this is because there are less teachers needed, which means less people would have to come. The following also change if there are more people coming:
- Cost Of Coaches
- Admission Cost
Before
After
From this, I can see that my spreadsheet works.
Evaluation
In my opinion, this assignment has been a very successful one. I met the user’s need, which was my original and main target; I also obtained the results I expected. These needs were met with a sufficient amount of detail in the time allocated. I was satisfied with the way in which my spreadsheet was set up, and with the variety of formulas and functions that were related to the work. These functions allowed me to set the spreadsheet to calculate data automatically in most cases, which is essential as it makes calculating the cost of a trip quick and efficient.
In the specification part, I wanted the system to be able to calculate the profit and loss. I also wanted the system to be user friendly and organised. I believe I have met the specification target.
I think that I could have improved my spreadsheet by making it more accurate in certain aspects. For instance, I could have got real data like found the actual cost of a coach, this would have made my spreadsheet more accurate.
If I were to do this project again, I would perhaps choose an overseas location, like France. This would provide a greater challenge, as there would be different column headings dealing with conversions of pounds Stirling to french currency/Euros, etc.
Overall, I have found this project a very motivating and fascinating one. I am proud to say that I have met the user’s needs, and thus have met the main target set out at the start of this project.
