Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
1
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
2
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
3
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
Learning aim C: Develop a data
model to meet client
requirements
Contents
C1 Developing a data model solution....................................................................................................6
•Processing features and requirements:...........................................................................................6
Formulae........................................................................................................................................6
functions........................................................................................................................................8
logical functions.............................................................................................................................9
data manipulation, e.g. sorting, grouping, filtering, pivoting data.................................................9
Slicer............................................................................................................................................10
importing and exporting data......................................................................................................10
Cell referencing and autofill.........................................................................................................11
using macros to initiate procedures.............................................................................................11
using buttons to initiate procedures............................................................................................12
use of data entry forms................................................................................................................13
restricting data input to acceptable values..................................................................................13
protecting cells by hiding, locking and password protecting.......................................................14
ease of use techniques, e.g. list boxes and drop-down menus....................................................14
automated data transfer between sheets or applications...........................................................14
adding user prompts and messages.............................................................................................14
•Output requirements:....................................................................................................................15
worksheet layout including: graphics, colours, borders and shading, charts and graphs............15
This is the table sheet which will do all the calculation based on the live exchange rate, data
which has been already gathered from the cheese company and what the cheese company
inputs as the margin. The cells in the tables will be overloaded with data obtained from
functions and formulae which is added already. The prediction sales will be displayed on the
dashboard and will be used to find out the average monthly sales using table lookup. The
baseline sales are what the prediction sales is compared against, to calculate how many 10p the
sales price has risen by. The 3rd bottom table will be used to calculate the average monthly
sales, as for every 10 pence rise there is in the sales price, the number of sales will logically
change while greater or fewer people can afford to buy the cheeses at the current price. The
table sheet has a title so that it matches with the other sheets so that it looks consistent........16
.....................................................................................................................................................16
4
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
C2 Testing the data model solution.....................................................................................................19
•Testing to establish whether:.........................................................................................................19
the solution meets all of the requirements of the functional specification.................................19
Test data......................................................................................................................................21
Test data......................................................................................................................................22
Test data......................................................................................................................................23
all the functions and formulae work correctly.............................................................................24
Test data......................................................................................................................................24
•Other factors to consider:..............................................................................................................25
selection and use of appropriate test data..................................................................................25
Valid.............................................................................................................................................25
Invalid..........................................................................................................................................26
C3 Reviewing and refining the data model solution............................................................................26
•Refining the model to take account of issues raised during testing...............................................27
•Refining the model to take account of the feedback and client requirements..............................27
•Factors that could be used to extend the model...........................................................................28
C4 Skills, knowledge and behaviours...................................................................................................29
•Planning and recording, including setting relevant targets with timescales, how and when
feedback from others will be gathered............................................................................................29
•Reviewing and responding to outcomes, including the use of feedback from others, e.g.
professionals who can provide feedback on the quality of the data model and its suitability against
the design requirements..................................................................................................................30
•Demonstrate own behaviours and their impact on outcomes to include professionalism,
etiquette, support of others, timely and appropriate leadership, accountability and individual
responsibility...................................................................................................................................30
•Evaluating outcomes to help inform high-quality, justified recommendations and decisions.......30
•Evaluating targets to obtain insights into own performance.........................................................31
•Media and communication skills, including:..................................................................................33
use of tone and language for verbal and written communications, to convey intended meaning
and make a positive and constructive impact on audience, e.g. positive and engaging tone,
technical/vocational language suitable for intended audience, avoidance of jargon..................33
responding constructively to the contributions of others, e.g. supportive, managing
contributions so all have the opportunity to contribute, responding to objections, managing
expectations, resolving conflict....................................................................................................33
5
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
C1 Developing a data model solution
•Processing features and requirements:
o Formulae
6
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
7
, Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
Formulae Screenshot
Multiply
To figure out how much cheese would cost
per kilo in pounds (cost per kilo in euros × 4
Average monthly sales Based on a Euro to
Pound exchange rate of 0.7)
Add
To find out the sale price of each cheese in
pounds (cost per kilo in (£) + sale price of
cheese in (£))
Subtract
To figure out the profit for each cheese in
pounds (Total cost per kilo in (£) - cost per
kilo in (£))
Divide
To find out the percentage of monthly
sales of each cheese (Monthly sales of
cheese in (£) ÷ total Monthly sales of
cheese in (£))
o functions
ROUNDDOWN
ROUNDDOWN function returns a number rounded down to a
specified number of digits. Such as if =ROUNDDOWN (3.2, 0), then it
would round 3.2 down to zero decimal places which would give the
result as 3
ROUNDUP
The ROUNDUP function works like the ROUND function, except the
ROUNDUP function will always round numbers up. If =ROUNDUP (3.6,
0), then it would round 3.6 up to zero decimal places which would
give the result as 4
SUM
The SUM function adds all the numbers in the range of cells and
returns the result of the addition. For instances, or example:
=SUM (H10:H14) Adds the values in cells H10:H14 to find the total
monthly sales of cheese
8
Subject: IT
Unit 5: Data Modelling
1
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
2
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
3
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
Learning aim C: Develop a data
model to meet client
requirements
Contents
C1 Developing a data model solution....................................................................................................6
•Processing features and requirements:...........................................................................................6
Formulae........................................................................................................................................6
functions........................................................................................................................................8
logical functions.............................................................................................................................9
data manipulation, e.g. sorting, grouping, filtering, pivoting data.................................................9
Slicer............................................................................................................................................10
importing and exporting data......................................................................................................10
Cell referencing and autofill.........................................................................................................11
using macros to initiate procedures.............................................................................................11
using buttons to initiate procedures............................................................................................12
use of data entry forms................................................................................................................13
restricting data input to acceptable values..................................................................................13
protecting cells by hiding, locking and password protecting.......................................................14
ease of use techniques, e.g. list boxes and drop-down menus....................................................14
automated data transfer between sheets or applications...........................................................14
adding user prompts and messages.............................................................................................14
•Output requirements:....................................................................................................................15
worksheet layout including: graphics, colours, borders and shading, charts and graphs............15
This is the table sheet which will do all the calculation based on the live exchange rate, data
which has been already gathered from the cheese company and what the cheese company
inputs as the margin. The cells in the tables will be overloaded with data obtained from
functions and formulae which is added already. The prediction sales will be displayed on the
dashboard and will be used to find out the average monthly sales using table lookup. The
baseline sales are what the prediction sales is compared against, to calculate how many 10p the
sales price has risen by. The 3rd bottom table will be used to calculate the average monthly
sales, as for every 10 pence rise there is in the sales price, the number of sales will logically
change while greater or fewer people can afford to buy the cheeses at the current price. The
table sheet has a title so that it matches with the other sheets so that it looks consistent........16
.....................................................................................................................................................16
4
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
C2 Testing the data model solution.....................................................................................................19
•Testing to establish whether:.........................................................................................................19
the solution meets all of the requirements of the functional specification.................................19
Test data......................................................................................................................................21
Test data......................................................................................................................................22
Test data......................................................................................................................................23
all the functions and formulae work correctly.............................................................................24
Test data......................................................................................................................................24
•Other factors to consider:..............................................................................................................25
selection and use of appropriate test data..................................................................................25
Valid.............................................................................................................................................25
Invalid..........................................................................................................................................26
C3 Reviewing and refining the data model solution............................................................................26
•Refining the model to take account of issues raised during testing...............................................27
•Refining the model to take account of the feedback and client requirements..............................27
•Factors that could be used to extend the model...........................................................................28
C4 Skills, knowledge and behaviours...................................................................................................29
•Planning and recording, including setting relevant targets with timescales, how and when
feedback from others will be gathered............................................................................................29
•Reviewing and responding to outcomes, including the use of feedback from others, e.g.
professionals who can provide feedback on the quality of the data model and its suitability against
the design requirements..................................................................................................................30
•Demonstrate own behaviours and their impact on outcomes to include professionalism,
etiquette, support of others, timely and appropriate leadership, accountability and individual
responsibility...................................................................................................................................30
•Evaluating outcomes to help inform high-quality, justified recommendations and decisions.......30
•Evaluating targets to obtain insights into own performance.........................................................31
•Media and communication skills, including:..................................................................................33
use of tone and language for verbal and written communications, to convey intended meaning
and make a positive and constructive impact on audience, e.g. positive and engaging tone,
technical/vocational language suitable for intended audience, avoidance of jargon..................33
responding constructively to the contributions of others, e.g. supportive, managing
contributions so all have the opportunity to contribute, responding to objections, managing
expectations, resolving conflict....................................................................................................33
5
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
C1 Developing a data model solution
•Processing features and requirements:
o Formulae
6
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
7
, Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
Formulae Screenshot
Multiply
To figure out how much cheese would cost
per kilo in pounds (cost per kilo in euros × 4
Average monthly sales Based on a Euro to
Pound exchange rate of 0.7)
Add
To find out the sale price of each cheese in
pounds (cost per kilo in (£) + sale price of
cheese in (£))
Subtract
To figure out the profit for each cheese in
pounds (Total cost per kilo in (£) - cost per
kilo in (£))
Divide
To find out the percentage of monthly
sales of each cheese (Monthly sales of
cheese in (£) ÷ total Monthly sales of
cheese in (£))
o functions
ROUNDDOWN
ROUNDDOWN function returns a number rounded down to a
specified number of digits. Such as if =ROUNDDOWN (3.2, 0), then it
would round 3.2 down to zero decimal places which would give the
result as 3
ROUNDUP
The ROUNDUP function works like the ROUND function, except the
ROUNDUP function will always round numbers up. If =ROUNDUP (3.6,
0), then it would round 3.6 up to zero decimal places which would
give the result as 4
SUM
The SUM function adds all the numbers in the range of cells and
returns the result of the addition. For instances, or example:
=SUM (H10:H14) Adds the values in cells H10:H14 to find the total
monthly sales of cheese
8
