BTEC Assignment Brief
Pearson BTEC Level 3 National Extended Certificate in Information
Technology
Qualification
Pearson BTEC Level 3 National Foundation Diploma in Information
Technology
Unit number and title Unit 5: Data Modelling
B: Design a data model to meet client requirements
Learning aim(s) (For NQF
C: Develop a data model to meet client requirements
only)
Design, create and evaluate a data model
Assignment title
Margaret Francis
Assessor
Tuesday 22 April 2025
Issue date
Sunday 1 June 2025
Hand in deadline
Vocational Scenario or You have recently joined a new company as a trainee data analyst.
Context The company import a range of different speciality Cheeses from
France and the Netherlands. They supply the cheese to a number of
different restaurants and specialist delicatessens throughout the UK.
The company face a number of challenges due to currency exchange
rate fluctuations (between the Euro, that they buy the cheese in, and
the pound that they sell it in) and the variations in economic growth.
Their product is relatively expensive and sells better when economic
growth is good and less well when it is poor.
You have been asked to design and create a spreadsheet model
which can predict the potential impact of exchange rate fluctuations
on the company profit margins.
The model should use the data collected by the company on average
monthly sales of the top five best-selling cheeses in their range:
Cheese Cost per Kilo in Average monthly sales
Euros based on a Euro to
Pound exchange rate of 0.7
Camembert 28 900
Emmental 32 850
Gruyere 35 875
Edam 29 825
Gouda 31 700
The typical margin applied to calculate the UK sales price is 40%.
Profit is calculated by subtracting the cost per kilo in pounds from the
sales price. The company have found that on average for each 10p
rise in the sales price (based on the 0.7 exchange rate) the average
monthly sales drops by 20 units. If the exchange rate drops below 0.7
then the sales increase by a similar amount.
When the exchange rate increases the company can do one of two
things:
• decrease the margin so prices stay the same
• increase the prices keeping the margin the same, but this will
reduce sales volume
Your model should show the impact of doing each of these on the
overall profit the company makes.
The model you develop should be designed to be used by people who
are not experts in spreadsheets and should include a user interface
which allows the user to enter the exchange rate and then choose to
either change the margin or the prices.
Following on from the presentation you created for the manager of
the company you now need to:
• Produce a design for the data model that meets the requirements
described above including worksheet structure diagrams and a
test plan. Your design should include a number of alternatives, for
example to the way the user interface is designed.
• Review your design for the model with two other people and
record their feedback and use it to improve your design.
• Write a justification of the design decisions you made and how
you arrived at the final design.
• Develop the model based on your final design.
Test the model using the test plan you created, checking that it
functions as it should and that it produces the correct results.
• Demonstrate your completed model to a user and collect their
feedback, using their feedback and results of your testing to
refine and optimise your model.
• Write an evaluation of the design and the final optimised version
Task 1 of your data model considering how well it meets the client’s
requirements.
Present evidence that you have shown individual responsibility,
effective time-management in your design and development of a data
model making high-quality justified recommendations and decisions.
For example, you need to show how you have:
• Planned and managed your time and met targets.
• Reviewed and responded to outcomes including the use of
feedback from others
• Behaved appropriately while completing the assignment –
including professionalism, etiquette, supportive of others, timely
and appropriate leadership, accountability and individual
responsibility
• Evaluated outcomes to help inform high-quality justified
recommendations and decisions
• Used appropriate methods of communication effectively
Checklist of evidence Design documentation
required Feedback from the design review from at least two people and
evidence of how you used this to improve the design, e.g. before
and after designs
Written justification of your design decisions
Evidence of developing the model, e.g. annotated screen shots
showing the development process
Feedback for the review of the model by a user
Evidence of how you used the feedback to refine and improve the
2
BTEC Assignment Brief v3
BTEC Internal Assessment QDAM June 2017
model, e.g. before and after screen shots
An evaluation of the design and the completed model
Evidence that you have shown individual responsibility, effective
time-management and made high-quality justified
recommendations and decisions
Criteria covered by this task:
Unit/Criteria
To achieve the criteria you must show that you are able to:
reference
5/BC.D2 Evaluate the design and optimised data model against client requirements.
Demonstrate individual responsibility, creativity, and effective self-management
5/BC.D3
in the design, development and review of a data model.
Justify decisions made, showing how the design will fulfil its purpose and client
5/B.M2
requirements.
5/C.M3 Optimise the data model to meet client requirements.
5/B.P3 Produce designs for a data model which meet client requirements.
5/B.P4 Review the designs with others, to identify and inform improvements.
5/C.P5 Develop a data model to meet client requirements.
5/C.P6 Test the data model for correctness, functionality and acceptance.
5/C.P7 Review the extent to which the data model meets client requirements.
Evidence Report format
Sources of information BTEC Course Book
to support you with Teams - Resources
this Assignment
Other assessment Completed spreadsheet tasks
materials attached to
this Assignment Brief
3
BTEC Assignment Brief v3
BTEC Internal Assessment QDAM June 2017
