ITLS6008_2020_S1_Take-home_Quiz_Instructions
Deadline: 5/25
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.
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.
ITLS6008_2020_S1_Take-home_Quiz_Instructions
The company needs 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. The solution will be easy to use, expandable (in terms of capabilities), and simple to 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 for demonstrating the system’s capabilities. The Excel files contain the following data:
– Distributors and retailers for Apromo Trading.
– Ingredients and food service products carried by Apromo Trading.
– Retail products carried by Apromo Trading.
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 if necessary). 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 two lines 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. Your queries should not be the same as the ones covered in class, and should draw information from more than one table (use JOIN in all of your queries).
(iv) Your design should include the ability to manage returns and conduct ABC analysis.
See next page for the expected deliverables.
ITLS6008_2020_S1_Take-home_Quiz_Instructions
These are the sequential steps you should follow to make sure you have created a robust and practical database design for the WMS:
1) Make sure you have read the documents listed under ‘Assumed knowledge for the Take-home Quiz’. There are additional references listed in the Take-home Quiz module in Canvas which you can use (if necessary) once you start working on the design.
2) Read the case description in this document, visit the company website, examine the data provided in Excel files in the Take-home Quiz module in Canvas.
3) In an Excel worksheet, create an initial diagram for the database for the WMS. Make sure it includes both the table and field names. (You have seen that I created my partial design in PowerPoint. For a more complete initial design it is quicker to use an Excel worksheet.)
4) Identify tables you will need for which management has not provided you with any data
5) Create dummy data for these additional table. Add these tables to your database diagram
6) Iterate between Steps 3, 4, 5 till you think your design is reasonably complete. You can refer to the readings and additional references if necessary.
7) In an Excel worksheet list the 10 queries that you think will be most helpful in determining operational efficiencies for the case organisation.
8) Check if you have all the tables necessary to create these queries. If not, repeat Steps 4 and 5 and then Step 3.
9) Now import the tables into the SQL Server environment.
10) Create relationships, the database diagram and queries. This step is quick if you have done Steps 1-8 well. Prepare the Word document and .bacpac/.bak file.