Unit 5 Data Modelling Assignment 2
Introduction
In this assignment, I was recently chosen to work as a trainee data analyst for a new organization.
First and foremost, I am willing to develop an effective spreadsheet model that can forecast the
effects of exchange rate fluctuations on company profit margins. I'll use this information to design a
data model that meets all of the client's criteria, including worksheet structure diagrams and the
ability to include a test plan. The design would then be reviewed with other clients in order to find
and advise changes. Furthermore, I will clarify how the specifications are completely satisfied and
then include a well-detailed and reasoned review of the optimized data model in comparison to
existing client requirements.
Problem
The essence of the problem is that the consumer has previously faced a range of problems, one of
which is the currency change question, which resulted in inaccuracy for both pounds and euros.
Functions must perform
The model must perform a number of functions for the segment. In certain cases, these functions
necessitate a complex calculation that is integrated into the “Microsoft Excel” project. These are the
functions that are currently available in this project.
SUM – By adding the costs of numbers 1 and 2 together, this function will be used to measure the
overall cost. For example, to get the total cost of the product, add all the numbers in cells B1 and F5
and then provide the equals symbol.
AVERAGE – This function averages all of the numbers in cells B1 through F5. This feature can also be
used as a part of a formula in a worksheet cell.
COUNT – This function is typically used to count the number of cells containing numbers, followed
by counting the numbers in the list of arguments. This function will also return the number of entries
in a number field that contains a set of numbers. Entering the following formula to count the
numbers in the range is an example of this.
COUNTIF- This feature is usually used to count the number of numbers in cells B1 through F5 that
are greater than 50. It will also be used to count the number of 70s in each cell from B1 to F5.
IF- This function will run a logical test to see whether the conditions are met, and it returns one of
two values: false or true. If the exchange rate of 0.7 is greater than 0.3 or 0.6, for example, it will
result in a pass, but if it is less, it will result in a fail.
LOOKUP – This function will be used to find a value in one column and return it from another one-
column. In general, the feature "LOOKUP" will be useful in Excel for solving specific problems. For
e.g., if I wanted to find the total currency value (in pounds), I would select that column first, then
another to find the overall value.
,User Interface
A minimum of two or three design layouts would be considered for the user interface. In addition, I
will use Microsoft Excel to organize all of the data into an entity, from which I will create rough
designs for the show of how my data model will look before putting it into practice. In addition, my
table will include links, such as buttons, that will take the user to another page. The other page will
be my form, which will have labels and fields for users to fill out. In addition, I will have a data
analyst who will use graphs to monitor the changes that are being made and to determine if the
costs of sale are lower or higher, forecasting how the results will change over time.
Time - This would be one of the potential constraints because I will not have enough time to
complete the project and make it as accurate and reliable as possible. Furthermore, the time
constraint might impact my calculations because figuring out the data to be measured can take a
long time.
Cost – Another limitation I have is that my budget might not be sufficient to meet all of the hardware
specifications and determine which ones to purchase. The drawbacks with these will prohibit me
from continuing with the data modelling process because the hardware that I currently have may
not work properly after I buy a less expensive hardware.
Software The program I use would be a restriction for me because there might be compatibility
problems with this project.
User Interface design
Here I'll show my design layouts, which typically apply to graphical user interfaces that focus on
looks or style and provide a fast overview of how the display will appear. After that, this design can
be used for optimization.
, Data Validation
Input mask- This is the character string that specifies the format of valid input values. When a cell is
pressed, the cells menu will appear, and format cells will need to be selected from there. Finally,
when I click custom, the form box appears, and I pick the correct type for the input mask, which is
“#”:”00.
Range check- This can be used for data that consists of numbers or dates that must fall within a
certain range.
Combo box – This is a form of dialog box that contains a variety of controls, such as drop-down lists,
that will be useful for my Microsoft Excel data model. Furthermore, it is used to pick one of the
available choices. These validations above will be beneficial to me as it will make my tables and
forms accurate.
Introduction
In this assignment, I was recently chosen to work as a trainee data analyst for a new organization.
First and foremost, I am willing to develop an effective spreadsheet model that can forecast the
effects of exchange rate fluctuations on company profit margins. I'll use this information to design a
data model that meets all of the client's criteria, including worksheet structure diagrams and the
ability to include a test plan. The design would then be reviewed with other clients in order to find
and advise changes. Furthermore, I will clarify how the specifications are completely satisfied and
then include a well-detailed and reasoned review of the optimized data model in comparison to
existing client requirements.
Problem
The essence of the problem is that the consumer has previously faced a range of problems, one of
which is the currency change question, which resulted in inaccuracy for both pounds and euros.
Functions must perform
The model must perform a number of functions for the segment. In certain cases, these functions
necessitate a complex calculation that is integrated into the “Microsoft Excel” project. These are the
functions that are currently available in this project.
SUM – By adding the costs of numbers 1 and 2 together, this function will be used to measure the
overall cost. For example, to get the total cost of the product, add all the numbers in cells B1 and F5
and then provide the equals symbol.
AVERAGE – This function averages all of the numbers in cells B1 through F5. This feature can also be
used as a part of a formula in a worksheet cell.
COUNT – This function is typically used to count the number of cells containing numbers, followed
by counting the numbers in the list of arguments. This function will also return the number of entries
in a number field that contains a set of numbers. Entering the following formula to count the
numbers in the range is an example of this.
COUNTIF- This feature is usually used to count the number of numbers in cells B1 through F5 that
are greater than 50. It will also be used to count the number of 70s in each cell from B1 to F5.
IF- This function will run a logical test to see whether the conditions are met, and it returns one of
two values: false or true. If the exchange rate of 0.7 is greater than 0.3 or 0.6, for example, it will
result in a pass, but if it is less, it will result in a fail.
LOOKUP – This function will be used to find a value in one column and return it from another one-
column. In general, the feature "LOOKUP" will be useful in Excel for solving specific problems. For
e.g., if I wanted to find the total currency value (in pounds), I would select that column first, then
another to find the overall value.
,User Interface
A minimum of two or three design layouts would be considered for the user interface. In addition, I
will use Microsoft Excel to organize all of the data into an entity, from which I will create rough
designs for the show of how my data model will look before putting it into practice. In addition, my
table will include links, such as buttons, that will take the user to another page. The other page will
be my form, which will have labels and fields for users to fill out. In addition, I will have a data
analyst who will use graphs to monitor the changes that are being made and to determine if the
costs of sale are lower or higher, forecasting how the results will change over time.
Time - This would be one of the potential constraints because I will not have enough time to
complete the project and make it as accurate and reliable as possible. Furthermore, the time
constraint might impact my calculations because figuring out the data to be measured can take a
long time.
Cost – Another limitation I have is that my budget might not be sufficient to meet all of the hardware
specifications and determine which ones to purchase. The drawbacks with these will prohibit me
from continuing with the data modelling process because the hardware that I currently have may
not work properly after I buy a less expensive hardware.
Software The program I use would be a restriction for me because there might be compatibility
problems with this project.
User Interface design
Here I'll show my design layouts, which typically apply to graphical user interfaces that focus on
looks or style and provide a fast overview of how the display will appear. After that, this design can
be used for optimization.
, Data Validation
Input mask- This is the character string that specifies the format of valid input values. When a cell is
pressed, the cells menu will appear, and format cells will need to be selected from there. Finally,
when I click custom, the form box appears, and I pick the correct type for the input mask, which is
“#”:”00.
Range check- This can be used for data that consists of numbers or dates that must fall within a
certain range.
Combo box – This is a form of dialog box that contains a variety of controls, such as drop-down lists,
that will be useful for my Microsoft Excel data model. Furthermore, it is used to pick one of the
available choices. These validations above will be beneficial to me as it will make my tables and
forms accurate.
