Assessment Task 4: Assignment
This assignment contributes 30% to your final mark. You can work as a group of no more than two students, or individually. Group members should be from the same workshop group. Only one submission is required for each group. All work must be submitted through Canvas before 11:59pm Monday 19 April. No late work will be accepted. For group submission, the cover page must include the name and student ID of each member of the group.
You must accept the declaration below for submission.
DECLARATION
I (We) hereby certify that this assignment is my (our) own work, based on my (our) personal study and/or research and that I (we) have acknowledged all material and sources used in the preparation of this assignment. I (We) also certify that the assignment has not previously been submitted for assessment and that I (we) have not copied in part or whole or otherwise plagiarised the work of other students or authors.
Documents to be submitted:
Cover sheet with student name and ID
PDF file of the report
EXCEL spreadsheet
Problem O.J. Juice Company sells bags of oranges and cartons of orange juice. Oranges are purchased from different suppliers, and then transported to O.J.’s warehouses. At the warehouses, the oranges are sorted (separated according to grades) before they are transported to O.J.’s factory. The production process is illustrated in the graph below.
O.J. grades oranges on a scale of 1(poor) to 10(excellent). The oranges from a supplier is a mix of all grades with known percentages. Each supplier cannot provide more than its capacity. The capacity, cost and grade mix of the oranges purchased from a supplier are given as an example in the Table below.
Supplier capacity Cost $/lb grade 3(%) grade 6 grade 8 grade 10
2000
According to this Table, Supplier A cannot provide more than 2,000 lb of oranges; if O.J. purchase 1,000 lb oranges from supplier A, after sorting (separating the oranges according to grade), O.J. can have 50 lb oranges of grade 3, 200 lb of grade 6, 500 lb of grade 8 and 250 lb of grade 10.
The purchased oranges are transported from suppliers to O.J’s warehouses.
The transportation cost per lb is shown as an example in the Table below
Shipping cost($ per lb)
Warehouse A Warehouse B Warehouse C
supplier1 0.10 0.15 0.05
supplier2 0.25 0.09 0.12
supplier3 0.13 0.12 0.10
The sorting process takes place in O.J’s different warehouses at different cost rate. The amount of oranges sorted by each warehouse must be within its capacity.
The sorted oranges are transported to OJ’s plant to produce orange juice and bag of oranges. The average quality of oranges sold in bags must be at least 6, and the average quality of the oranges used to produce orange juice must be at least 7.5. The average quality is calculated as the weighted average of the grades of oranges blended together. For example, the average quality of blending 2 lb of grade 2 oranges and 5 lb of grade 9 oranges is
The transportation cost from warehouse to plant is $0.05 per lb. Each lb of orange can yield 0.95 lb of bags of orange at a cost of $0.1, while each lb of orange can yield only 0.6 lb of orange juice at a cost of $0.4. The orange juice can be sold at $3.5 per lb, and the bag of oranges is sold at $1.5 a pound. O.J.’s plant can process no more than 30,000 lb of oranges, and must produce at least 3000 lb of bags of oranges. Oranges not used must be sent to a recycling centre at the cost of $0.1 per lb.
O.J. needs to find the optimal production and shipping plan to maximise profit.
The detailed data can be found in the Excel file with the name 2021AssignmentData.xlsx.
Questions
1 Investigate how to solve O.J.’s problem with Linear Programming
2 O.J. is considering to build a new plant. Discuss what data is required for assessing this strategic decision.
ASSESSMENT TASKS:
• Construct EXCEL models to solve the optimisation problem
• Answer the questions posed above. Incorporate these into a report of the problem and its solution.
ASSESSMENT ELEMENTS
EXCEL solution (15 marks)
• Appropriate structure of EXCEL model (2 marks)
• Correct data (2 marks)
• Correct Solver models and proper modeling techniques(10 marks)
• Presentation and style (1 mark): colours, fonts, annotation, etc.
Report (15 marks)
• Presentation and style (2 marks): PDF format, typesetting nicely, etc. • Literacy (2 marks): Grammar, spelling, punctuation, vocabulary
• Correctness of analysis (11 marks): 9 marks for question 1; 2 marks for question 2
The report should follow the structure suggested below:
1. Title
2. Problem description: rewrite the problem according to your understanding
3. Formulation of the problem: clearly describe all assumptions; statedecision variables; establish and explain the objective function; determine all constraints
4. Analysis and discussions based on EXCEL results
5. Summary
