Unit 3 Spreadsheet Design
You are expected to produce all assignments to the set deadlines. If you are unable to meet these deadlines for any good reason for example, prolonged illness, then you should contact your ICT teacher as soon as possible.
This assignment involves the specification of user requirements, design, implementation, testing and evaluation of a spreadsheet.
This unit is a coursework based unit and is internally assessed. It is worth 33% of your marks for the year 12 part of the course.
I recommend that the spreadsheet you choose to produce is a Quotation System for a business of your choice.
You should use the following deadlines to produce your own time plan for completion of this unit.
|
Task |
Completion date |
|
1. Produce time plan |
1st November |
|
2. User requirements |
16th November |
|
3. Design |
16th December |
|
4. Implementation |
20th February |
|
5. Testing |
15th March |
|
6. Evaluation |
28th March |
|
7. Project planning evidence |
28th March |
It is essential that throughout this unit you :
What you need to do :
|
Components |
Criteria |
Mark |
|
Section 1 User
Requirements Background |
Show a
clear understanding of the background to the problem -
Give some background information about the company you are producing
the spreadsheet for -
Why does this company need a spreadsheet & how would it help their
business ? -
How is information currently captured and processed by the business ? |
1 |
|
Expected
outcomes / aims and objectives |
Required
inputs, processes and calculations, outputs & reports -
What are main aims / objectives of your spreadsheet ? -
What input information is required ?* -
What output information is required ?* -
What calculations do you need to do to get the required output ?* -
What processes are needed to get the required output ?* -
What will your output report be like ? -
What will your chart be like ? -
What automated process do you intend to include to improve efficiency
of use ? -
What user aids could you provide to improve ease of data entry / use ? |
5 |
* I suggest you use the following structure to define your inputs, outputs, calculations and processes :
Inputs
|
Data Item |
Data description |
Data type |
Data example |
|
Date of quotation |
This is the date that the customer is given a quotation |
date |
12/12/2005 |
|
|
|
|
|
Outputs
|
Data Item |
Data description |
Data type |
Data example |
|
Total
per item |
This is the number of items multiplied by the cost per
item |
Currency |
£24.55 |
|
|
|
|
|
Calculations
|
Calculation |
Calculation description |
Data involved details of calculation |
|
VAT |
This is the VAT that the customer would have to pay |
The subtotal needs to be multiplied by 0.175 to get 17.5% of the subtotal |
|
|
|
|
Processes
|
Process |
Process description |
|
Printout quotation |
The user requires a process to print a quote onto a well laid out sheet in the form of a letter. |
|
|
|
|
Section 2 Design Design of
inputs |
Produce
initial designs of each sheet in the workbook showing labels data, formula
and any special features used -
you should produce a hand drawn design of all of the sheets in your
spreadsheet including data input sheet, userform, lists for VLOOKUP, record
of quotes, chart and printout. -
Make sure that your designs are well presented and in formula view
with named ranges -
You should include any user aids you intend to use Show multiple sheets and the use of 3D
referencing between them -
You must use more than one sheet in your spreadsheet and clearly show
the links between each sheet, this should be done on your hand drawn designs Produce a
table explaining the use of §
Lookup / vlookup tables §
Drop down list boxes §
Spinners for data entry §
Logical True or False / Tick
Boxes I suggest
you use the following structure :
Produce a
design for the startup user interface -
this is your userform and you should do a hand drawn design (with
details of functions) on a piece of A4 Design of a
data entry form -
this is the data input section of your spreadsheet, make sure you show
user aids and where they output to |
2 1 4 1 1 |
||||||||||||||
|
Design of processes |
Produce a
table explaining the purpose of each formula or function used Including two forms of referencing §
absolute cell
referencing §
3D referencing §
named cell ranges and use of four functions eg :
§
AVERAGE §
RAND §
SQUARE §
SINGLE IF §
MULTIPLE
IF §
DATE §
MEDIAN §
VLOOKUP §
CONCATENATE I suggest
that you use the following structure for your table :
Design of
validation techniques and error messages produced -
You should
validate data that you input directly into cells -
The lists
sheet is the most likely criteria for validation I suggest you use the following
structure for your validation table :
Design of sort process You need to sort data and give reasons for doing
so : - Ranges in the lists sheet should be sorted
into alphabetical order so that VLOOKUP works correctly define the criteria
you would use for this sort - Give a reason why the quotelog may be sorted
and define the criteria used for the sort Design of search for criteria -You should search (filter or find tools) the data on your lists sheet or quotelog, give a reason for doing so (eg customer phoned up) and define the criteria you will use |
6 2 1 1 |
||||||||||||||
|
Design of
automated processes using programming code |
Features such as: Simplified data input Production of printed or screen
reports Control buttons to initiate
events -
These are your
macros, you should use a table to define these :
-
please make
sure you mention some code here I suggest you use the msgbox code to inform
the user that the printout has been printed successfully -
Also include
the code you intend to use to open your userform when the spreadsheet starts
(Auto_open) |
2 |
||||||||||||||
|
Design of outputs |
Design of report with appropriate
use of House style Headers and footers Page orientation Margins -
Do a hand
drawn design of your printout and make sure you clearly specify the features
in the list above. Design of, purpose and type of
graph -
Do a hand
drawn design of your chart, and underneath explain what the chart is for,
your input range and why the type of chart you have chosen is suitable |
2 1 |
||||||||||||||
|
Section 3 Implementation |
User
Interface -
Take a screenshot of your userform and input (quoteform) sheet Data entry Creation of
sheets Use of multiple sheets and the
use of 3D referencing between them §
Lookup / vlookup tables §
Drop down list boxes §
Spinners for data entry §
Tick Boxes Data entry
form -
For
the above points you should printout your entire spreadsheet in number &
formula view, you may also need to take screenshots to show non-printable
items. Processes Validation
techniques -
Take a screenshot to show : setting up validation rules, input messages
in your sheet and error messages in your sheet Use of two
cell referencing and four standard functions -
Take
screenshots to specifically show the use of relative/absolute references,
named ranges and the use of functions listed in Section 2 Automated processes Outputs -
Include screenshots of any outputs in your
spreadsheet Reports and
presentation style -
Include your printouts and any reports you have produced Graph -
Include a printout of your chart |
2 2 2 1 1 1 1 1 2 6 2 2 1 |
||||||||||||||
|
Section 4 Testing to
a Test Plan |
Tests
against original stated aims. The test plan should also include
testing for: §
Test validation methods with
valid, invalid and extremes of data -
Please refer
to table in appendix 1 for the layout I suggest you use. §
Test all calculations produce by
formulas or functions -You should use the trace
precedents / dependants tool and use a printout of your spreadsheet with hand
drawn calculations / VLOOKUP results on it. §
Test all macros using the
internal programming capabilities of the software - Run Debug, Breakpoint & Watch in Visual Basic, give screenshots of you doing this and report on results §
Test sorting and searching
routines - carry out the sorts and
searches you specified in the design section, take screenshots of this and
comment upon the results §
Test all output reports Produce the output you specified in the
design section and make sure it meets your specification comment upon
result |
3 3 2 2 1 |
||||||||||||||
|
Section 5 Evaluation |
Candidates
are expected to reflect critically upon their implemented solution -
what was
the main aim of your spreadsheet have you met this aim ? -
how could
your spreadsheet be improved ? -
what are
the poor aspects of your spreadsheet ? -
could your
spreadsheet be used in the real world ? |
3 |
||||||||||||||
|
Section 6 Project
planning & standard ways of working |
Candidates must manage their work effectively and include a project
time plan. - hand in your completed time plan, showing
clearly how you met your deadlines They will be expected to:
- take screenshots of your user area & backups, give a list of
sources for material you have used. |
2 |
||||||||||||||
|
Total |
|
70 |
|
Validation rule |
Applicable to (cell / range) |
Valid data test |
Valid data test result |
Invalid data test |
Invalid data test result |
Extreme data test |
Extreme data test result |
|
Whole number between 1 -10 |
Lists sheet A2:A9 |
I will enter the number 7 |
The number 7 was accepted |
I will enter the letter f |
An error message appeared saying: You must enter a number between 1 and 10 |
I will enter the number 89 |
An error message appeared saying: You must enter a number between 1 and 10 |