Unit 5: Data Modelling Assignment 2
Introduction: I recently started an IT apprenticeship at a new hardware company. My job
includes creating a useful spreadsheet to help predict how changes in Gross Value and Net
Value might affect the company's profits. I will make a data model based on what the client
needs, including a test plan and worksheet structure diagrams. To make sure my designs are
good and meet the client's needs, I will review my designs with others and make
improvements based on their feedback.
Scenario Overview
You have just started an IT apprenticeship at a hardware company. Your main task is to
create a spreadsheet system to manage the company's stock and sales. This system needs to
keep track of which products are popular, manage stock levels, and monitor sales
performance, all while keeping the data safe and secure.
Key Requirements
The first thing the system needs to do is track how popular the products are. This means
finding out and recording which products are selling the most and which are selling the
least. The system will automatically update the stock levels and remove products that are no
longer popular. By keeping accurate sales records, the company can better understand which
products are in demand.
Next, the system needs to manage stock. This means keeping an up-to-date list of hardware
items with important details like the number in stock, the cost of the device, payment
method (full payment or three payments), product return options, date of purchase, and
whether the item can be insured. Whenever items are sold or removed, the stock levels will
update automatically to ensure the data is always correct.
The system also needs to track sales performance. It will identify and record the best-
performing sales staff so they can get bonuses. A worksheet will track what each sales team
member sells, providing clear records of their performance.
The system will handle automated calculations too. When a hardware item is selected, its
cost will show up automatically. The system will calculate the total cost for the customer,
including payment details, insurance cover if needed, VAT, and the final total cost. It will
manage cases where customers buy more than one of the same item and will alert users if
there is not enough stock to complete an order.
For processing orders and updating stock, the system will update stock levels when an order
is completed. It will record the number of items sold and total sales by each sales team
,member. This data will be used to calculate the commission for sales staff based on their
performance.
Data security is very important. The system will ensure that only administrators can view
sensitive information like commission due, stock levels, and the number of devices sold. This
will be done by protecting sensitive worksheets with passwords and access controls,
restricting access based on user roles.
Implementation Considerations
To design the spreadsheet, you will create separate worksheets for different tasks like
inventory, sales tracking, and commission calculation. Formulas and functions will be used to
automate updates and calculations, making sure the system is efficient and accurate.
For data input and automation, data validation will be used to ensure that data entry is
accurate and consistent. Macros or scripts will automate updates when items are added or
removed from the inventory.
User alerts and notifications will be included to improve the user experience. Conditional
formatting or alerts will notify users when stock levels are low, and clear messages will be
provided when there is not enough stock to complete an order.
Security measures will include protecting sensitive worksheets with passwords or access
controls and restricting access to specific data based on user roles to make sure only
authorised people can view sensitive information.
Finally, the system will generate reports to analyse product popularity and sales
performance. Charts and graphs will be used to show sales data and trends, giving useful
insights to help manage stock and sales strategies effectively.
By following these requirements and considerations, the spreadsheet system will effectively
manage the company's stock, track sales performance, and ensure data security and
integrity.
Design 1
,Design 2
Design 3
The Nature of the problem
The nature of the problem provides important context for the data model, making it a
important section of the documentation. This section should summarize the scenario, users’
needs, and client requirements clearly and concisely.
I have recently joined a new IT hardware company as an IT apprentice. The company sells a
variety of hardware items and needs an efficient system to manage product popularity, stock
levels, and sales performance.
The company needs to keep accurate records of both the least and most popular products
sold regularly. This information is important for maintaining appropriate stock levels and
discontinuing unpopular items. Accurate stock level maintenance is important, and the
system should automatically update stock levels when items are added or removed,
ensuring real-time accuracy. Additionally, identifying and rewarding top-performing sales
staff is important, so the system must track sales by staff members and calculate bonuses
based on their performance.
The spreadsheet must include detailed information for each hardware item, such as the
number in stock, cost of the device, payment method (full payment or three payments),
product return option, date of purchase, and insurable status. The system needs to display
the cost of selected hardware items automatically, along with the number in stock. It must
, calculate the total cost, including any insurance and VAT, and handle payment processing,
especially for empty purchases. Alerts should notify users if the requested stock is
unavailable.
Once an order is completed, the system should update the number in stock, the number of
devices sold, and the total sales by each sales team member. This will help in calculating
commissions due. Due to the personal types of some data, the system must ensure that only
administrators can view sensitive information such as commission due, stock levels, and the
number of devices sold.
To address these challenges, I will design and create a spreadsheet model that integrates
these functionalities. The model will include a dynamic product list that automatically
updates when items are added or removed based on popularity. It will allow identification of
sales members and hardware items from a list and display the cost of selected hardware,
calculate payments including insurance and VAT, and handle multiple unit purchases. The
system will notify users when requested stock levels are insufficient and adjust stock levels,
record devices sold, and update total sales for commission calculations. Furthermore, it will
ensure that only administrators can access confidential data. This model will be user-friendly,
designed for individuals who may not be spreadsheet experts, and will include a simple
interface to manage these tasks efficiently.
Original Design
Client Review
Question Feedback
Q1: Can you explain whether the layout is I think the layout is user-friendly. It's very
user-friendly? minimal, and the user inputs are clearly
labelled. However, there is space for
improvement.
Q2: Do the designs meet the client Yes, the designs meet the client
requirements? requirements. The layout is simple and well
structured, but the colours could be better.
Q3: Are you happy with the layout and Yes, I believe the layout looks simple and
presentation of the model design? well structured, but a few improvements
can be made.
Q4: Are there any improvements that could Yes, a few improvements can be made to
be made to the design? the user interface. The main one is the
Introduction: I recently started an IT apprenticeship at a new hardware company. My job
includes creating a useful spreadsheet to help predict how changes in Gross Value and Net
Value might affect the company's profits. I will make a data model based on what the client
needs, including a test plan and worksheet structure diagrams. To make sure my designs are
good and meet the client's needs, I will review my designs with others and make
improvements based on their feedback.
Scenario Overview
You have just started an IT apprenticeship at a hardware company. Your main task is to
create a spreadsheet system to manage the company's stock and sales. This system needs to
keep track of which products are popular, manage stock levels, and monitor sales
performance, all while keeping the data safe and secure.
Key Requirements
The first thing the system needs to do is track how popular the products are. This means
finding out and recording which products are selling the most and which are selling the
least. The system will automatically update the stock levels and remove products that are no
longer popular. By keeping accurate sales records, the company can better understand which
products are in demand.
Next, the system needs to manage stock. This means keeping an up-to-date list of hardware
items with important details like the number in stock, the cost of the device, payment
method (full payment or three payments), product return options, date of purchase, and
whether the item can be insured. Whenever items are sold or removed, the stock levels will
update automatically to ensure the data is always correct.
The system also needs to track sales performance. It will identify and record the best-
performing sales staff so they can get bonuses. A worksheet will track what each sales team
member sells, providing clear records of their performance.
The system will handle automated calculations too. When a hardware item is selected, its
cost will show up automatically. The system will calculate the total cost for the customer,
including payment details, insurance cover if needed, VAT, and the final total cost. It will
manage cases where customers buy more than one of the same item and will alert users if
there is not enough stock to complete an order.
For processing orders and updating stock, the system will update stock levels when an order
is completed. It will record the number of items sold and total sales by each sales team
,member. This data will be used to calculate the commission for sales staff based on their
performance.
Data security is very important. The system will ensure that only administrators can view
sensitive information like commission due, stock levels, and the number of devices sold. This
will be done by protecting sensitive worksheets with passwords and access controls,
restricting access based on user roles.
Implementation Considerations
To design the spreadsheet, you will create separate worksheets for different tasks like
inventory, sales tracking, and commission calculation. Formulas and functions will be used to
automate updates and calculations, making sure the system is efficient and accurate.
For data input and automation, data validation will be used to ensure that data entry is
accurate and consistent. Macros or scripts will automate updates when items are added or
removed from the inventory.
User alerts and notifications will be included to improve the user experience. Conditional
formatting or alerts will notify users when stock levels are low, and clear messages will be
provided when there is not enough stock to complete an order.
Security measures will include protecting sensitive worksheets with passwords or access
controls and restricting access to specific data based on user roles to make sure only
authorised people can view sensitive information.
Finally, the system will generate reports to analyse product popularity and sales
performance. Charts and graphs will be used to show sales data and trends, giving useful
insights to help manage stock and sales strategies effectively.
By following these requirements and considerations, the spreadsheet system will effectively
manage the company's stock, track sales performance, and ensure data security and
integrity.
Design 1
,Design 2
Design 3
The Nature of the problem
The nature of the problem provides important context for the data model, making it a
important section of the documentation. This section should summarize the scenario, users’
needs, and client requirements clearly and concisely.
I have recently joined a new IT hardware company as an IT apprentice. The company sells a
variety of hardware items and needs an efficient system to manage product popularity, stock
levels, and sales performance.
The company needs to keep accurate records of both the least and most popular products
sold regularly. This information is important for maintaining appropriate stock levels and
discontinuing unpopular items. Accurate stock level maintenance is important, and the
system should automatically update stock levels when items are added or removed,
ensuring real-time accuracy. Additionally, identifying and rewarding top-performing sales
staff is important, so the system must track sales by staff members and calculate bonuses
based on their performance.
The spreadsheet must include detailed information for each hardware item, such as the
number in stock, cost of the device, payment method (full payment or three payments),
product return option, date of purchase, and insurable status. The system needs to display
the cost of selected hardware items automatically, along with the number in stock. It must
, calculate the total cost, including any insurance and VAT, and handle payment processing,
especially for empty purchases. Alerts should notify users if the requested stock is
unavailable.
Once an order is completed, the system should update the number in stock, the number of
devices sold, and the total sales by each sales team member. This will help in calculating
commissions due. Due to the personal types of some data, the system must ensure that only
administrators can view sensitive information such as commission due, stock levels, and the
number of devices sold.
To address these challenges, I will design and create a spreadsheet model that integrates
these functionalities. The model will include a dynamic product list that automatically
updates when items are added or removed based on popularity. It will allow identification of
sales members and hardware items from a list and display the cost of selected hardware,
calculate payments including insurance and VAT, and handle multiple unit purchases. The
system will notify users when requested stock levels are insufficient and adjust stock levels,
record devices sold, and update total sales for commission calculations. Furthermore, it will
ensure that only administrators can access confidential data. This model will be user-friendly,
designed for individuals who may not be spreadsheet experts, and will include a simple
interface to manage these tasks efficiently.
Original Design
Client Review
Question Feedback
Q1: Can you explain whether the layout is I think the layout is user-friendly. It's very
user-friendly? minimal, and the user inputs are clearly
labelled. However, there is space for
improvement.
Q2: Do the designs meet the client Yes, the designs meet the client
requirements? requirements. The layout is simple and well
structured, but the colours could be better.
Q3: Are you happy with the layout and Yes, I believe the layout looks simple and
presentation of the model design? well structured, but a few improvements
can be made.
Q4: Are there any improvements that could Yes, a few improvements can be made to
be made to the design? the user interface. The main one is the
