Spreadsheet Coursework
Spreadsheet Resources - Tutorials and other help
GCSE ICT - an excellent site, of great help to anyone tackling a GCSE ICT project
A Guide to Spreadsheet projects from Thorpe St Andrew School
1. Identify (5 marks)
Explain your problem and name your end-user
Who are you creating this spreadsheet for? What is that person's requirements? What data will you be using? What is the outputted information that you need to produce?
It is very important that you are as detailed as possible when it comes to describing your problem. Your user needs to list several specific requirements that will allow you to carry our an "extension task".
| Example:
My uncle Eric is the manager of a cheese shop. He is completely disorganised and he is always losing important files and scraps of paper. Auntie Ethel bought him a PC for Christmas and I would like to produce a spreadsheet for him that will make his business more efficient. I have talked to Eric about the cheese shop and he has given me these requirements:
|
Say why you are going to use a computer to solve the problem. How will the computer be faster, more accurate, cheaper etc. when compared to the traditional manual method e.g. compare a spreadsheet to pencil, paper and calculator.
A spreadsheet will do calculations with a great degree of accuracy. A spreadsheet takes up less room than a filing cabinet. A spreadsheet can automatically generate graphs.
The disadvantage of computerised systems are that they require training to use and to produce, they rely on electricity and there are security concerns about systems on networks (people can "hack" them).
List at least three "quantitative objectives". These must be objectives that can be measured.
Base these on your user's requirements. Make sure you set yourself measurable objectives.
2. Analyse (9 marks)
Explain the data collection, input and processing that will occur?
What will your data be? (what are the words and figures that you will enter into the spreadsheet?)
Where will it come from? (What form of data collection will you use?)
How will your data be manipulated? (what calculations will your spreadsheet perform?)
What will your backup strategy be?
You explain how you will avoid losing your work. How often will you backup? What media will you backup to? How long will the backup take?
What will your security policy be?
Will you need to password-protect the spreadsheet?
How will you ensure that the end-user does not delete vital data?
What hardware and software will you use and why?
Hardware available to you includes a PC (computer, monitor, keyboard, mouse), a black laser printer, a colour laser printer, a colour inkjet printer, scanner, digital camera, the Internet. Software includes Word, Access, Excel, Internet Explorer, etc.
Say, in detail, why you have chosen the software you have selected. Explain why you have decided to use Excel instead of other applications.
Unlike Word or Publisher, Excel can do calculations. Excel is an excellent application for producing graphs and charts. In Excel, you can "lock" certain areas of the spreadsheet to prevent misuse.
Define file formats for all the files that you are going to produce in this project.
Excel generates XLS (Excel Spreadsheet) files.
What will your output be?
What printouts will your user do and how are you going to make it easy for him/her to print. Remember that Excel can be a tricky application to print from because a spreadsheet is not necessarily in convenient A4 page size.
Draw a flowchart to illustrate the flow of data. You should illustrate the sequence of operations needed to solve the problem.
Instructions here:
How to Draw a Systems Flowchart
You can use a flowchart stencil or you can do a flowchart in Word by clicking View / Toolbars / Drawing. The Drawing toolbar includes all the flowchart shapes.
Remember that you are trying to illustrate the flow of data i.e. what are the inputs, processes and outputs? What is the data, where does it come from and what are you going to do with it?
3. Design (9 marks)
Produce some rough sketches for your spreadsheet to illustrate three different designs for your spreadsheet.
You must say which design you prefer and why.
Produce a detailed and annotated diagrams for your chosen design.
You must show colours, fonts, font sizes, cell formatting, locked cells, functions and formulae.
Show your design to your end-user and ask him/her to comment on it.
Say what changes you will make as a result of these comments.
List, in order, the tasks you have to complete. Break down big tasks into sub-tasks. Say what time-scale you are working to.
Produce a detailed action plan.
Devise a test plan
Produce a table like the one below. Fill in all columns except the "Actual Result" column.
Plan at least 10 tests.
You should test formulae, macros, "IF" functions, printing and security features.
For further guidance see the guide on Testing a GCSE Project (word document)
| Test No. | Purpose of Test | Test Data | Expected Result | Actual Result |
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 |
4. Implement (12 marks)
You must show that you have solved your "problem".
During this process, you must:
Annotate, Annotate, Annotate
On each piece of printed out work, you should write what you did and how you did it. Say what features of the application you used. Say what buttons you pressed and what menu items you selected. The more you can explain your work, the more convinced the examiner will be that you know what you're doing.
Do a screenshot of your work by pressing ALT+Print Screen and then pasting into Word. Remember to annotate the print-out.
Print out evidence to show all queries, forms, tables, reports and macros that you produced..
Show the progression of your work
Save your draft copies! When you make mistakes, you should show how you corrected mistakes.
Follow your Test Plan.
Reproduce your test plan. This time, fill in the "actual result" column. Provide evidence to show how you carried out each test.
It is very important that you compare "expected results" with "actual results"
For further guidance see the guide on Testing a GCSE Project (word document)
Write about any corrections you made as a result of your testing.
Compare your original design with your final outcome.
Explain any differences.
5. Evaluate (5 marks)
Evaluate each of your original "quantitative objectives". Did you achieve them?
What technical problems did you encounter?
Explain any changes to your original design.
Supply evidence that the end-user has seen your work and supplied comments.
Say what you think of the end-user's comments.
How could your work be improved/extended?