BTEC Unit 5 Data
Modelling - Assignment 2
& 3 Learning Aim B & C
2023 excel
, Unit 5 – Learning Aim B
Data Modelling – Assignment 2
Introduction:
In this assignment, I have recently been chosen to join a new company as a trainee data analyst.
Firstly, I will be willing to produce an efficient spreadsheet model which can predict the potential
impact of exchange rate fluctuations on the company profit margins. From this, I will create designs
for a data model so that each of the client requirements are met successfully which will include
worksheet structure diagrams and being able to provide a test plan. Then, I will be reviewing the
design with other clients to identify and inform improvements.
Furthermore, I will be explaining my justification to how the requirements are fully fulfilled and then
providing a well- detailed and reasoned evaluation of the optimised data model against client
requirements that already exist.
Nature of the problem:
The nature of the problem is that number of challenges that the user has previously been faced and
this would be the currency change issue that resulted with inaccuracy with both pounds and euros.
Functions the model must perform:
For the section, there are many functions that the model must perform. Generally, these functions
require complex calculation that is built in the project named “Microsoft Excel”. The functions that
currently exist in this project are:
SUM – This function will be used to calculate the total cost between number1 and number2 by
adding up these together. An example could be adding all numbers in the cells of B1 and F5 and then
providing the equals sign to get the total cost of the product.
AVERAGE – This function is used to average all the numbers in the cells from B1 to F5. Also, this
function can also be entered as part of a formula in a cell of a worksheet.
COUNT – This function is usually used to count the number of cells that contains numbers and then
counts the numbers within the list of arguments. Additionally, this function will get the number of
entries in a number field that includes range of array of numbers. An example of this could be
entering the following formula to count the numbers in the range.
COUNTIF – This function is generally used to count how many numbers in cells B1 to F5 are higher
than 50. Also, it will also be used to count how many 70s are in the cells from B1 to F5. The counting
process will be
, Unit 5 – Learning Aim B
IF – This function can perform a logical test to check whether the conditions are met and returns one
value of false and another value of true. For instance, if the exchange rate of 0.7 is greater 0.3 or 0.6,
it would result with a pass but if it is less then it will result with a fail instead.
LOOKUP – This function would be used to search one column and would return the corresponding
value from another one-column or one-row range. In general, the function “LOOKUP” would make it
useful for solving certain problems in Excel. For example, if I wanted to search for a value of the total
currency (in pounds), I would select that specific column and then another column to find an overall
value.
User interface:
The user interface will consider including a minimum of at least two or three design layouts. In
addition to this, I will be using Microsoft Excel to provide all the data into an entity and from this, I
will be drawing rough designs to include the display of how my data model will look like before
implementing this. Furthermore, my table will contain links such as including buttons so that it
navigates to another page. The other page will be my form to include the labels and fields for users
to input information. Also, I will also comprise a data analyst such as graphs to track the changes
being made and if the costs of sale are lower or higher predicting the results to be changed over
time.
Constraints:
For this part, I will be considering the constraints that could occur during the process of my data
modelling using the project “Microsoft Excel”. This would help me to improve and make sure that I
do not encounter the same problems again in the future. One of these constraints will include:
Time – This will be one of the constraint issues that can exist as I may not have enough time to finish
off the project and to make it accurate and efficient as possible. Furthermore, the limit of time could
affect me with calculations as it may usually take long to figure out the data to be calculated.
Cost – This would be another issue of my constraint as the amount of budget I have may not be
enough to meet each of the hardware requirements and which ones to buy. The limitations with
these will possibly prevent me to continue with the data modelling process since the hardware that I
have may not function properly after having to purchase a cheaper hardware (PC, Laptop).
Skills - The skills would be another common issue as part of my constraint because I may have a lack
of skills that would need to be required when dealing with the tools that have been provided on the
project “Microsoft Excel”. There are some tools that I may not know about or have a clue, and this
would show that I have less skills which wouldn’t meet the criteria or requirement.
Hardware – The use of hardware that I use may process slower than expected and will be wasting
time as I would have to wait until it loads each time I create a column and inputting the data. If the
loading process takes too long, then it will buffer and result with having to exit the application and
restart.
Modelling - Assignment 2
& 3 Learning Aim B & C
2023 excel
, Unit 5 – Learning Aim B
Data Modelling – Assignment 2
Introduction:
In this assignment, I have recently been chosen to join a new company as a trainee data analyst.
Firstly, I will be willing to produce an efficient spreadsheet model which can predict the potential
impact of exchange rate fluctuations on the company profit margins. From this, I will create designs
for a data model so that each of the client requirements are met successfully which will include
worksheet structure diagrams and being able to provide a test plan. Then, I will be reviewing the
design with other clients to identify and inform improvements.
Furthermore, I will be explaining my justification to how the requirements are fully fulfilled and then
providing a well- detailed and reasoned evaluation of the optimised data model against client
requirements that already exist.
Nature of the problem:
The nature of the problem is that number of challenges that the user has previously been faced and
this would be the currency change issue that resulted with inaccuracy with both pounds and euros.
Functions the model must perform:
For the section, there are many functions that the model must perform. Generally, these functions
require complex calculation that is built in the project named “Microsoft Excel”. The functions that
currently exist in this project are:
SUM – This function will be used to calculate the total cost between number1 and number2 by
adding up these together. An example could be adding all numbers in the cells of B1 and F5 and then
providing the equals sign to get the total cost of the product.
AVERAGE – This function is used to average all the numbers in the cells from B1 to F5. Also, this
function can also be entered as part of a formula in a cell of a worksheet.
COUNT – This function is usually used to count the number of cells that contains numbers and then
counts the numbers within the list of arguments. Additionally, this function will get the number of
entries in a number field that includes range of array of numbers. An example of this could be
entering the following formula to count the numbers in the range.
COUNTIF – This function is generally used to count how many numbers in cells B1 to F5 are higher
than 50. Also, it will also be used to count how many 70s are in the cells from B1 to F5. The counting
process will be
, Unit 5 – Learning Aim B
IF – This function can perform a logical test to check whether the conditions are met and returns one
value of false and another value of true. For instance, if the exchange rate of 0.7 is greater 0.3 or 0.6,
it would result with a pass but if it is less then it will result with a fail instead.
LOOKUP – This function would be used to search one column and would return the corresponding
value from another one-column or one-row range. In general, the function “LOOKUP” would make it
useful for solving certain problems in Excel. For example, if I wanted to search for a value of the total
currency (in pounds), I would select that specific column and then another column to find an overall
value.
User interface:
The user interface will consider including a minimum of at least two or three design layouts. In
addition to this, I will be using Microsoft Excel to provide all the data into an entity and from this, I
will be drawing rough designs to include the display of how my data model will look like before
implementing this. Furthermore, my table will contain links such as including buttons so that it
navigates to another page. The other page will be my form to include the labels and fields for users
to input information. Also, I will also comprise a data analyst such as graphs to track the changes
being made and if the costs of sale are lower or higher predicting the results to be changed over
time.
Constraints:
For this part, I will be considering the constraints that could occur during the process of my data
modelling using the project “Microsoft Excel”. This would help me to improve and make sure that I
do not encounter the same problems again in the future. One of these constraints will include:
Time – This will be one of the constraint issues that can exist as I may not have enough time to finish
off the project and to make it accurate and efficient as possible. Furthermore, the limit of time could
affect me with calculations as it may usually take long to figure out the data to be calculated.
Cost – This would be another issue of my constraint as the amount of budget I have may not be
enough to meet each of the hardware requirements and which ones to buy. The limitations with
these will possibly prevent me to continue with the data modelling process since the hardware that I
have may not function properly after having to purchase a cheaper hardware (PC, Laptop).
Skills - The skills would be another common issue as part of my constraint because I may have a lack
of skills that would need to be required when dealing with the tools that have been provided on the
project “Microsoft Excel”. There are some tools that I may not know about or have a clue, and this
would show that I have less skills which wouldn’t meet the criteria or requirement.
Hardware – The use of hardware that I use may process slower than expected and will be wasting
time as I would have to wait until it loads each time I create a column and inputting the data. If the
loading process takes too long, then it will buffer and result with having to exit the application and
restart.
