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 |