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 B: Design a data
model to meet client
requirements
Table of Contents
B1 Functional specification....................................................................................................................5
• nature of the problem....................................................................................................................5
• functions the model must perform.................................................................................................5
• user interface and navigation.....................................................................................................7
• success criteria................................................................................................................................9
B2 Spreadsheet model design...............................................................................................................9
• Producing worksheet structure diagrams that show:...................................................................10
layout and presentation...............................................................................................................10
processing and output.................................................................................................................17
test data.......................................................................................................................................18
Test data......................................................................................................................................19
Test data......................................................................................................................................20
data entry and validation.............................................................................................................20
Test data......................................................................................................................................21
Test data......................................................................................................................................21
B3 Reviewing and refining data model designs...................................................................................22
•Communicating with clients, e.g. email, verbal communication....................................................22
•Gathering feedback from client(s) and potential users on the extent to which the design meets
requirements...................................................................................................................................23
•Scheduling and documenting meetings.........................................................................................24
•Agreeing and adjusting timescales................................................................................................25
•Refining ideas and solutions.........................................................................................................25
•Updating design specification documentation, based on review and feedback............................27
4
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
B1 Functional specification
Designing a functional specification to meet requirements:
• nature of the problem
I have recently joined a new company as a trainee data analyst. The company import a range of
different speciality Cheeses from France and the Netherlands. They supply the cheese to several
different restaurants and specialist delicatessens throughout the UK. The cheese company face
many challenges due to currency exchange rate fluctuations between the Euro, that they buy the
cheese in and the pound that they sell it in also 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.
The issue with the exchange rate indicates that it has an impact against the cheese company's profit
margins since how much they can make depends on how much they buy and sell the cheeses. The
fluctuations can make their profit and demand change from time to time, as they might consistently
change depending on the exchange rate. For example, if it increases then the cheese company might
decrease their margin to keep the costs the same and vice versa. Therefore, improving the reliability
of the company. In contrast, they could increase the prices of cheeses to keep the profit margin the
same whilst reducing the sales volume.
• functions the model must perform
The data must be entered throughout the whole spreadsheet so that it can process meaningful
information so that the cheese company can interpret and base informed decisions. The data will be
sourced from the cheese and other sources like the internet which provides live updates of the
exchange rate. It's essential to find the necessary functions that the spreadsheet can perform. This
can be done by planning an informative design to minimise errors and to critic the solution. Firstly,
the spreadsheet will have a menu interface. The purpose of this is to navigate through the solution
to the other sheets and to make it user-friendly. The second part will require profits of the cheese
company and the exchange rate but in this case, the inputs and outputs will be mixed between
different sections to make it clearer for the employees. The third sheet will hold charts/graphs and
other outputs based on the data are given in the second part also live exchange rate from the
Internet. This is good as it enables them to make informed decisions based on real-time, up-to-date,
accurate information. The last section will contain entry spaces for inputs for the type of cheeses,
exchange rate and the economy. This is needed if the cheese company wanted to perform any
What-If Analysis. These would be valuable to conduct analysis such as a cash flow forecast. However,
these proposed designs will change over time due to feedback from the cheese company.
Required Inputs:
Type of cheese-If the cheese company wanted to find out more information on the types of cheeses,
then it can be set up as a text in a drop-down list. As a result, the validation rule will only allow the
cheese company to select a value from the drop-down list and if not done correctly it will then show
the validation message saying "Must choose a value from the drop-down list".
5
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
Cost per kilo of cheeses-The cost per kilo of cheeses will be set as the currency format which will
round the figures to 2 decimal places. The validation rule will be set up as ≥ 0 and ≤ 1 which will give
a validation message telling the operator "Must enter a positive value between 0 and 100" as the
weight cannot be negative.
Profit margin-The profit margin has to be fixed with the percentage format which means its
validation will be ≥ 0 and ≤ 100. If the cheese company attempts to enter anything outside of these
boundaries it will then show a validation message saying "Must be a positive value between 1 and
100" to assist them to change the value of their input.
Margin-If the cheese company wanted to adjust the margin applied, then it can be done via using a
spin button. A spin button can be used to increment a number in a cell. The validation rule for the
spin button will be between 0 and 100 since it’s a percentage and the incremental change will be
number 1 to suit their users’ needs when applying the margin. The margin applied will be set as a
percentage format.
Exchange rate- The exchange rate will be set as a number with format with two decimal places this is
because of the way currencies are structured across the globe. Similar to the margin applied, the
cheese company can change the exchange rate through using the spin button tool or typing it
manually for quick and easy use.
Formulae- The formulae I will be using is a great way for the cheese company to make calculations
quickly and helps the data model become more accurate. The bullet points below show the function
the model will perform in terms of calculations.
The cost per kilo in Pounds will be calculated by Exchange rate Cost per kilo in Euros
The mark-up per kilo in Pounds will be calculated with cost per kilo in Pounds * Mark-up.
The total cost per kilo in Pounds will be calculated via cost per kilo in Pounds + Mark-up per
kilo.
The forecast profits will be calculated through Total cost per kilo in Pounds* Average
monthly
Sheet 1- Main Menu Sheet:
Four big buttons that can be used to navigate through the spreadsheet
Sheet 2- Profits & Conversion sheet:
Profits and Conversion sheet must be locked so that it cannot be modified by anyone expect
a special member of staff from the cheese company. This would be done by locking the
sheet in the program using a password that will be strong and suitable for the cheese
company so that it will never be forgotten and guessed easily.
Must have the cost of each cheese per kilo in euros and pounds which has to be displayed in
a clear way
The calculation which will work out the total profit of each cheese in pounds.
A calculation that sums all of the total profits of each cheese into one number to find out the
overall profit of the cheeses in pounds.
There needs to be a calculation will work out the sales price of each cheese in British pounds
A calculation that is going to work out the total sales of each type of cheese.
6
,Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
The sheet should also include a calculation that outputs the profit per kilo of each cheese in
pounds taking into consideration the exchange rate provided and margin entered by the
user.
A calculation that finds out the sales rise, which will be displayed in a small table on the
right, with the difference of price in 10ps with it.
A calculation which changes the average monthly sales unit, depending on the input
Sheet 3- Graph & Chart sheet:
Graphs that will display information from the other sheets in a suitable way the user can
interpret
Sheet 4- Inputs sheet:
There should the ability to change the margin and exchange rate for the table so that it can
give different outcomes.
Sheet 5- Outputs sheet:
Must display the calculation which changes the average monthly sales unit, depending on
the input
The sheet should include a calculation that will output the sales price of each cheese in
pounds, taking into consideration the exchange rate and margin that can be modified to give
different outputs by the user.
• user interface and navigation
The user interface of the solution will have to be clear and easy to use since the cheese company
don't have any experience in IT. Therefore, meaning very little technical skill is needed to navigate
and use the solution. This can be done many ways such as hiding the methods the solution will do so
that the cheese company only sees simple inputs and outputs and not complex means.
Another way is by providing a navigation button. This is helpful as it means the employees will not
need to open or close sheets to get their desired destination since they can click a button which
takes them where they need to go. But to do this the navigational buttons has to be clearly labelled
without any obstructions.
Alternatively, making the design clear by separating and highlighting areas where the employees
have to input values can make entering data easier. This can be also enhanced by colouring the
headers so the employees know how the data is grouped and affects each other.
Sheet 1- Main Menu: Will be the first sheet that opens up when the spreadsheet opens up. The title
will be at the top centre of the page presented so that it stands out and cheese company know that
is the title. Under the title, there will be four big buttons laid out on each side. The buttons will be
used to navigate the database and take the cheese company from sheet to sheet.
Sheet 2- Profits & Conversion: The Background of sheet has not been confirmed yet and will mostly
likely to be changed based on the outcome. The layout of this sheet will be simple with the table of
data being evenly spaced out horizontally, with a black filter and “Century Gothic” white font colour
to highlight the tables and make it stand out for the cheese company so that it's easier to visualise
and conceptualise.
7
, Name: Coursework2u
Subject: IT
Unit 5: Data Modelling
Sheet 3- Graph & Chart: The layout will be graphs. All the graphs will have a coloured bar to
represent each cheese and enhance the look of the graphs and make some essential data stand out.
Sheet 4- Inputs Sheet: Above it with a spin button on the top right corner to help the cheese
company change the value of the margin and exchange rate
Sheet 5- Output Sheet: Will have a clear box which outputs figures. There will be a text saying
“Predicted sales price” and “Predicted Profit Per month”
Navigation - To make navigation around the database simple and efficient for the cheese company,
there will be a macro-enabled button. In total there will be five buttons saying: " Main Menu”,
“Profits & Conversion", "Graph & Chart”, “Inputs Sheet” and "Output Sheet”. This is to make it easier
for the cheese company to switch between sheets. Theses button will be included on each sheet so
that there is no confusion in navigation.
• constraints
There will be many constraints that I will encounter which would affect the way I produce the model.
For instances, time as I would have to meet all deadline and regularly discuss with the cheese
company so that I can finish within the allocated time and produce a reliable data model.
Another factor is my knowledge since I am a trainee data analyst employed to create the data
model, it's still possible that my limited expertise with Excel can be used to create the data model
which can be doable. This is because I can practice what I know and research things I don't know
about Excel via the Internet or books.
In contrast, another constraint is the exchange rate. This is because the exchange rate varies from
time to time which makes it difficult to inputs up-to-date figures so that the data model can be
accurate for the cheese company to base their decisions. Hence, to solve this issue a live exchange
rate data can be sourced to make the data model entirely accurate and reduces the number of
possible errors made.
Likewise, the profit margin is a limitation as it depends on many formulae in the data model which
has to be confirmed with the cheese company which has to be accepted by them since they're going
to use it and they might calculate their profit margins differently compared to others.
Finally, the most common constraints would be human-error as this will change how the data model
will work the most because humans can make various errors like inputting data in the wrong area.
However, this can be reduced via validation and verification as they prevent errors from occurring
when handling or recording data which makes the data model accurate.
8
