ITLS6008_2019_S2_Take-home_Quiz_Instructions
Objectives of the assessment:
• To help students enhance their understanding of warehouse operations, specifically, key inbound and
outbound processes
• To help students understand the key functions of a warehouse management system and insights
necessary from the system to facilitate managerial decision making
• To help students develop essential skills for managing and utilising master data and transactional data
required for operations
Key dates:
Your UoS outline provides the following dates:
Consultation and feedback dates for the take-home quiz:
Note: Questions you cannot ask tutors:
• How many tables should I include?
• What fields should I include inmy tables?
• What queries should I include?
These are design decisions that each student is expected to make on his/her own.
Date Time Location
Tuesday, 24 September, 2019 18:45 – 20:45 ITLS (378 Abercrombie Street, Darlington, NSW
2008 – opposite the ABS building), call Jyoti’s office
when you get here (02 9114 1879)
Tuesday, 1 October, 2019 18:45 – 20:45 ITLS
Friday, 4 October, 2019 18:00 – 21:00 H69 Computer Lab 1 (shared with ITLS6003
students)
Wednesday, 16 October, 2019 19:00 – 21:00 ABS Interactive Learning Studio 1090
Wednesday, 23 October, 2019 19:00 – 21:00 ABS Interactive Learning Studio 1090
Tuesday, 29 October, 2019 18:00 – 20:00 H69 Computer Lab 1
ITLS6008_2019_S2_Take-home_Quiz_Instructions
Assumed knowledge for the Take-home Quiz:
All content in modules for Week 3 to Week 7. In particular, it is important that you understand all the
lecture slides and the two following readings –
(i) Reading on warehouse operations – ‘Reading – Warehouse operations.pdf’ (see Week 3
module)
(ii) Reading on entity-relationship models (see link on Week 3 Slide 56)
Take-home Quiz Case Description:
Apromo Trading (http://apromotrading.com.au/about-apromo.php) is a small-to-medium sized
business headquartered in Sydney. They supply European ingredients and semi-finished products to
chocolate manufacturers, premium hotels and patisseries in Australia. They also supply gourmet food
and packaged confectionery to department stores, confectionery retailers, independent grocers and
delicatessens in Australia and New Zealand. The company has a small team of full-time staff in both
Sydney and Melbourne (http://apromotrading.com.au/our-team.php).
The company has one warehouse in Sydney and a newer one in Melbourne
(http://apromotrading.com.au/facilities.php). Assume both warehouses have approximately equal
storage capacity. The warehouse operations are supported by dozens of part-time workers. The
warehouses maintain areas at different temperatures based on the requirements for different product
ranges. Temperatures are monitor and logged in line with HACCP food safety certification
requirements.
The company imports two shipping containers and various plane shipments every week, and processes
approximately 150 orders per day. Its delivery vans that are fully refrigerated and all temperatures are
recorded at the time of delivery to ensure the products arrive in optimum condition. The vans service
the CBD and inner suburbs daily and outlying areas weekly. Deliveries to regional customers are done
via the dedicated cold chain freight company Kerry Logistics. Customers can also collect their orders
from the warehouses.ITLS6008_2019_S2_Take-home_Quiz_Instructions
With increasing order volumes, the company requires a system that will scale with growth and
provide real-time item-level visibility, keep track of regulatory and food safety certification
compliance. The existing warehouse management processes are manually recorded and prone to
data entry errors. This leads to problems in order completion, lack of adequate visibility into items in
stock and delays in order completion.
The company needs acquiring a WMS but solutions from market leaders such as SAP are cost
prohibitive and more complex than necessary for the company’s operations. You have been hired as
a supply chain specialist to design a scalable solution without requiring additional software
investments. Given your training at the University of Sydney, you have advised your manager that
you will be designing the solution using SQL Server Express and R and the system will be easy to use,
expand (in terms of capabilities) and maintain as the company continues to expand.
You have been provided with some Excel data files by your manager to develop and test your solution
and have been asked to create additional dummy data if required for demonstrating the system’s
capabilities. The Excel files contain the following data:
- Distributors and retailers for Gourmet FoodTraders
- Ingredients and food service products carried by Gourmet FoodTraders
- Retail products carried by Gourmet Food Traders
Your WMS should include:
(i) Tables: Master and transactional data tables necessary for managing the company’s
inbound and outbound operations as well as stock counting. The relevant tables should
have the ability to incorporate GS1 standards at a later stage.
(ii) Data: Include all data provided (after any processing ifnecessary). You will need to create
dummy (i.e., fake/placeholder data for testing purposes – this is common practice in
system design) for tables that are necessary for your design but for which you have not
received any data from your manager. You should create at least one line of dummy data
for each table for which you have not been given any data.
(iii) Queries: 10 queries that demonstrate the most important insights that your system will be
able to generate in the context of warehouse and logistics operations and as well as
associated chart/table outputs via Shiny. Your queries should not be the same as the ones
covered in class.
(iv) Your design should include the ability to manage returns and conduct ABC analysis.
See next page for the expected deliverables.
ITLS6008_2019_S2_Take-home_Quiz_Instructions
Expected deliverables for this assessment:
A. Your design files:
(i) The .bacpac file representing the database layer of your system.
(ii) The .R file(s) that are necessary for the application and presentation layers of your
system
B. A Word document (use the template provided) addressing the following instructions:
(i) Question 1 (15 marks)
a. Provide a screenshot of the relationship diagram
b. Provide the business rationale for your design
(ii) Question 2 (10 marks)
Please describe the output and report the business rationale for each query in a
list of 10 queries.
(iii) Question 3 (5 marks)
Provide a list of the 5 best examples of how your presentation layer presents
essential information from your database layer.