BTEC Level 3 Unit 5 Data Modelling Assignment 2, 2026 | Pearson

Learning Aim

In this unit you will:

A Investigate data modelling and how it can be used in the decision-making process

B Design a data model to meet client requirements

C Develop a data model to meet client requirements.

Vocational Scenario or Context

You have recently joined a new company as a trainee data analyst. The company imports a range of different specialty cheeses from France and the Netherlands. They supply the cheese to a number of different restaurants and specialist delicatessens throughout the UK. The company faces 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:

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 has found that on average for each 10p rise in the sales price (based on the sales prices for the 0.7 exchange rate) the average monthly sales drops by 20 units. For every 10p decrease in the sales price, sales increase by 20 units.

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 adapt the margin if needed.

Task 1

Following on from the report 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 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 required

• Design documentation
• 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 screenshots 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 model, e.g. before and after screenshots
• 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

Learning Aim B: Design A Data Model To Meet Client Requirements

Functional Specification

In this section you need to define and explain what is required of your data model to meet the client’s requirements. Be specific about what is needed.

You don’t need to put pictures of diagrams in this section, this will go in section 2.

a) The Nature of the Problem

This will be focused on the brief which company gives to me which is based on the cheese company facing a number of challenges. The company faces 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. At the time when the economy changes in a good way or poor when there is less product, it has better sales and is very expensive and also it will work in different ways.

b) Functions the model must perform

I will use the “=VLOOKUP” function because it looks up the value in the first row of the sheet and returns the values set for the number of rows in the same row. This will help me find all the information which I need, especially in big databases. One more thing which I will use will be “=MAX” and “=MIN” . These 2 functions return the highest and the lowest values in a range. This function will be useful for my model because it will help me find minimum and maximum values in all data and this will help me save time because this will be done automatically. The function “=SUM” is used for adding multiple numbers easily and quickly and this will be useful because I will not s[pend a lot of time writing this number and longer time for this . SUM followed by the number of cells to add, separated by commas, followed by the closing line.

c) User Interface

When creating the user interface for this data model, the main priority was to develop a clear and effective dashboard. This allows the user to interact with the spreadsheet without needing to understand how the formulas work or how to use them. While building the dashboard, I considered key factors for those who will use this table in the future, such as clearly separating data entry zones and output zones using different colors, as well as using standard fonts to ensure high readability. Special attention was paid to preventing errors: I implemented Data Validation to stop incorrect data entry, which ensures the stability of the entire model. I also provided easy navigation between sheets using hyperlinks or buttons to make working with the model fast and logical. The Dashboard interface is designed to display only the information necessary for this specific scenario. The table will contain interactive fields for entering variables, summary tables with key performance indicators, and dynamic charts that visualize results in real-time. This will allow the user to immediately see how changes affect the overall result. All technical calculations will be performed in the background on separate sheets using various formulas, for example, IF and IFS for decision-making, or VLOOKUP  to find data from reference tables. This interface fully meets the criteria because it ensures accurate information processing and allows for deep scenario analysis. Thanks to the professional design and structure, the model turns raw data into useful business information.

d) Constraints

For this data model for the cheese importing company, it is necessary to consider several key constraints that directly impact its functionality and final appearance. One of the important criteria is Maintenance of accurate data, as the company’s profit completely depends on the fluctuations of the Euro to Pound exchange rate. Even a minimum error in entering the exchange rate or the cost of cheeses will lead to an error that will then need to be fixed, and these errors lead to incorrect 40% calculations and inaccurate forecasts. To ensure the stability of the model and avoid the human factor, I implemented the Data Validation function, which limits the entry of incorrect values. Another important element is the development of Time & resources, as it is necessary to create an automated system in a short period that considers a complex rule: a change in price for every 10 pence changes the sales volume by 20 units, and this is important for the table because it will not need to be done manually, but everything will be done automatically and the calculations will be more accurate. The use of Software also imposes certain constraints on how the data will be processed. The model must be built to effectively perform scenarios using IF, IFS, and VLOOKUP functions, showing the impact of exchange rate changes on total profit in real-time. Finally, the main constraint for the design is the Skill set of the end user. Since the model is intended for managers who do not know how to correctly use Google Sheets, it is necessary to do everything possible so that in the end it is convenient and clear for them how to use it. Managers do not need to understand the formulas for this program. It is enough for the manager to only change the currency rate on the main panel, and all technical calculations will happen automatically in the background, which should not distract them. Thus, the model must contain important and complex data that will ultimately be transformed into clear and simple figures for the manager.

e) Success Criteria

Success criteria outline the fundamental requirements my model must fulfill to be considered fully complete and ready for use by the client. An important factor is what the model should do. The model must automatically calculate the selling price in pounds based on the Euro exchange rate and a 40% profit margin. The main function is the automatic change in sales volumes for example, by 20 units for every 10 pence change in price so that the manager can see a realistic forecast and understand what could be done with this information to avoid losing money and, instead, increase profit for the company itself. The model must be designed to test two different scenarios, such as maintaining a fixed price or maintaining a fixed profit margin. The second main factor is that the model must take into account accessibility considerations – UI. That is, the interface should be as simple and easy to use as possible for the manager. All data entry zones must be clearly marked so that the user does not get confused and does not make a mistake that could later change the entire meaning of the data. I will use large fonts and contrasting colors so that the information is legible and all important information is easy to understand and see. It is also important for the model to have specific navigation that will help quickly switch between the dashboard and reports without searching for the necessary tabs at the bottom. In conclusion, the model must deliver the following results what must the model deliver. It is important here that the results of the work provide accurate figures for the total profit for each type of cheese. Additionally, the model must create visual charts showing how currency fluctuations affect the company’s financial state to understand what this could lead to. This will allow the client to make decisions quickly without having to look through hundreds of rows of figures and without wasting time on it.