Unit 5
Assignment 2
Victor Anosike
Unit 5: Data Modelling Assignment 2 – Design and
develop a data model to meet client requirements
In this report, I will be showcasing the techniques and skills I’ve used to design
my data model, as well as specifying what was required and the planning
process of the spreadsheet. I will also be showing the completed spreadsheet
and evidential testing of its operation whilst reviewing it.
Functional specification
This is where I will be explaining all the resources that will be needed within the
data model needed that will meet the client’s requirements.
Nature of the problem
The data model I am designing is for The Fine Cheese Co, which means that I
need to understand what exactly the client wants. The primary objective is for
me to develop a few modes that can help support the company in accurately
predict the future cheese sales of their company.
Functions the model must perform
The data model must perform a variety of functions depending on the scenario.
To begin, the company's average monthly sales of its five most popular cheeses
must be included as well as the cost per kilo in euros. Additionally, the UK sales
price is calculated using a 40% profit margin. The company also wants a model
to process the results of the exchange rate dropping below 0.7, which would
result in a similar increase in sales units, and another model to process the
results of the exchange rate dropping below 0.7, which would result in a similar
increase in sales units.
User interface
The user interface is very important when it comes to developing the data model
because the users intended to use it will not have the knowledge or skill
compared to IT developers to navigate and operate a spreadsheet so, I must
make it simplistic and easy to understand. The data model should give users the
ability to change and enter the exchange rate and prices. To ensure that these
actions work, I’ll implement a variety of functions in the spreadsheet that will
prove beneficial to the users. I plan to create a dashboard within the spreadsheet
(Appendix A1). This will make interpreting data easier because it will have all the
necessary information in one place. Doing this will make it less time-consuming
to look at and very easy to understand which would make analysing the data
simpler.
The dashboard will include the date and time in real-time. To achieve this, I used
the formula '=now()' twice, one for date and the other for time so that the date
and time will automatically update every time the Dashboard is opened
(Appendix 2).
A 'Spin button' would also be a very useful tool to use (Appendix A3). This allows
users to quickly change the exchange rate value with a click of a button to either
increase or decrease the markup value percentage. Another function I will use is
setting limits to how low and high the markup value can be linking it to the
correct cells to ensure that it functions properly. I plan to give users the ability to
Page 1 of 14
, Unit 5
Assignment 2
Victor Anosike
check the exchange rate from Euro to Great British Pounds and vice versa by
entering any amount they want (Appendix A4).
A pivot table, which is a very effective means of summarising a huge amount of
data and allowing people to interact with it, is the last tool I'd like to use/create
(Appendix A5). I'll need to present all of the following values in percentage for
each cheese in this pivot table:
Sum of Monthly Sales of Cheese in Pounds (%)
Sum of Average Monthly Sales in (%)
Sum of Average Monthly Sales 10p in/decrease (%)
Sum of Average Monthly Sales 10p in/decrease in Pounds (%)
Users will be able to effortlessly transition between each sheet by using buttons
(this can be done using the sheet tab below) (Appendix A6). Users will be able to
just click the desired button and it will transition to the desired sheet.
Constraints
Throughout the development of my data model, I encountered some constraints
which increase the time it took for me to complete it. One constraint that
hindered me the most was the timescale in which the data model needed to be
fully created (31 March 2022 to 11 May 2022) because I had to complete other
projects and do exams in between those dates. Another constraint that heavily
put me at a disadvantage was my prior knowledge of spreadsheets was not very
good as I did not know how to do a vast majority of the functions. This proved
the most difficult as I had to understand a lot of key components while
developing my data model which gave me a lot of extra stress on top of the
initial one in completing this project within the deadline. Other minor
inconveniences I faced were doing formulas, adjusting cells and altering
diagrams I had created.
Success Criteria
The success criteria are that I can compare what I've done to what the company
considers an appropriate and full data model. I can claim that my data model
accurately duplicates what The Fine Cheese Co has asked me to provide,
including all of the necessary data and more (Appendix A1).
Spreadsheet model design
In this section, I will be showing off my data model designs and explaining how
they should look and perform. This will give the client a better insight into how
the model may look and function. This will also help me later when I am
developing and organising the work that has been done.
Worksheet model design
Below I will be displaying my 3 designs and each stage for them.
Design 1
Layout and presentation
Page 2 of 14
Assignment 2
Victor Anosike
Unit 5: Data Modelling Assignment 2 – Design and
develop a data model to meet client requirements
In this report, I will be showcasing the techniques and skills I’ve used to design
my data model, as well as specifying what was required and the planning
process of the spreadsheet. I will also be showing the completed spreadsheet
and evidential testing of its operation whilst reviewing it.
Functional specification
This is where I will be explaining all the resources that will be needed within the
data model needed that will meet the client’s requirements.
Nature of the problem
The data model I am designing is for The Fine Cheese Co, which means that I
need to understand what exactly the client wants. The primary objective is for
me to develop a few modes that can help support the company in accurately
predict the future cheese sales of their company.
Functions the model must perform
The data model must perform a variety of functions depending on the scenario.
To begin, the company's average monthly sales of its five most popular cheeses
must be included as well as the cost per kilo in euros. Additionally, the UK sales
price is calculated using a 40% profit margin. The company also wants a model
to process the results of the exchange rate dropping below 0.7, which would
result in a similar increase in sales units, and another model to process the
results of the exchange rate dropping below 0.7, which would result in a similar
increase in sales units.
User interface
The user interface is very important when it comes to developing the data model
because the users intended to use it will not have the knowledge or skill
compared to IT developers to navigate and operate a spreadsheet so, I must
make it simplistic and easy to understand. The data model should give users the
ability to change and enter the exchange rate and prices. To ensure that these
actions work, I’ll implement a variety of functions in the spreadsheet that will
prove beneficial to the users. I plan to create a dashboard within the spreadsheet
(Appendix A1). This will make interpreting data easier because it will have all the
necessary information in one place. Doing this will make it less time-consuming
to look at and very easy to understand which would make analysing the data
simpler.
The dashboard will include the date and time in real-time. To achieve this, I used
the formula '=now()' twice, one for date and the other for time so that the date
and time will automatically update every time the Dashboard is opened
(Appendix 2).
A 'Spin button' would also be a very useful tool to use (Appendix A3). This allows
users to quickly change the exchange rate value with a click of a button to either
increase or decrease the markup value percentage. Another function I will use is
setting limits to how low and high the markup value can be linking it to the
correct cells to ensure that it functions properly. I plan to give users the ability to
Page 1 of 14
, Unit 5
Assignment 2
Victor Anosike
check the exchange rate from Euro to Great British Pounds and vice versa by
entering any amount they want (Appendix A4).
A pivot table, which is a very effective means of summarising a huge amount of
data and allowing people to interact with it, is the last tool I'd like to use/create
(Appendix A5). I'll need to present all of the following values in percentage for
each cheese in this pivot table:
Sum of Monthly Sales of Cheese in Pounds (%)
Sum of Average Monthly Sales in (%)
Sum of Average Monthly Sales 10p in/decrease (%)
Sum of Average Monthly Sales 10p in/decrease in Pounds (%)
Users will be able to effortlessly transition between each sheet by using buttons
(this can be done using the sheet tab below) (Appendix A6). Users will be able to
just click the desired button and it will transition to the desired sheet.
Constraints
Throughout the development of my data model, I encountered some constraints
which increase the time it took for me to complete it. One constraint that
hindered me the most was the timescale in which the data model needed to be
fully created (31 March 2022 to 11 May 2022) because I had to complete other
projects and do exams in between those dates. Another constraint that heavily
put me at a disadvantage was my prior knowledge of spreadsheets was not very
good as I did not know how to do a vast majority of the functions. This proved
the most difficult as I had to understand a lot of key components while
developing my data model which gave me a lot of extra stress on top of the
initial one in completing this project within the deadline. Other minor
inconveniences I faced were doing formulas, adjusting cells and altering
diagrams I had created.
Success Criteria
The success criteria are that I can compare what I've done to what the company
considers an appropriate and full data model. I can claim that my data model
accurately duplicates what The Fine Cheese Co has asked me to provide,
including all of the necessary data and more (Appendix A1).
Spreadsheet model design
In this section, I will be showing off my data model designs and explaining how
they should look and perform. This will give the client a better insight into how
the model may look and function. This will also help me later when I am
developing and organising the work that has been done.
Worksheet model design
Below I will be displaying my 3 designs and each stage for them.
Design 1
Layout and presentation
Page 2 of 14
