Foodie Goodie is a small food processing plant. It produces three kinds of snacks: sunflower seeds, peanuts and broad beans. The plant’s production capacity is as follows: • if all the equipment produces sunflower seeds, it can produce 6 kg a day; • if all the equipment produces peanuts, it can produce 5 kg a day; • if all the equipment produces broad beans, it can produce 3 kg a day. Production is planned on a weekly basis (i.e., 5 working days), and the output of the week must be stored before delivery. It takes • 1.8 litres of space to store 1 kg of sunflower seeds; • 1.9 litres of space to store 1 kg of peanuts; • 1.3 litres of space to store 1 kg of broad beans. The total storage space available is 100 litres. Due to a commercial agreement with a restaurant, Foodie Goodie must deliver at least 5 kg of broad beans every week. The marketing department estimates that the weekly demand for sunflower seeds, peanuts and broad beans does not exceed 11 kg, 15 kg and 8 kg, respectively, so the plant does not want to produce more than these quantities of sunflower seeds, peanuts and broad beans. In addition, the plant observes that sunflower seeds and peanuts are often purchased by consumers at the same time, so it hopes that the output level of these two products can be similar. Specifically, the weekly output level of sunflower seeds can be higher or lower than that of peanuts, but the deviation should be at most 2kg. Finally, the net profit per kilogram of sunflower seeds, peanuts and broad beans are $4, $6 and $7, respectively. The plant wants to determine the production quantity of each product in a week in order to maximize the total net profit. (a) Develop a linear programming (LP) formulation for the problem. (b) Summarise how much each product should be produced per week after solving the problem with Excel Solver. Please paste here three screenshots, (1) The problem worksheet just before clicking the Solver command: after developing the Excel Solver model, press Ctrl + ~ (tilde) to show formulas in cells, and then take a screenshot with row and column headings; (2) The Solver Parameters dialog just before clicking Solve; and (3) The Answer Report created by the Excel Solver. Please limit the answer to within three pages.
Foodie Goodie is a small food processing plant. It produces three kinds of snacks: sunflower seeds, peanuts and broad beans. The plant’s production capacity is as follows:
• if all the equipment produces sunflower seeds, it can produce 6 kg a day;
• if all the equipment produces peanuts, it can produce 5 kg a day;
• if all the equipment produces broad beans, it can produce 3 kg a day.
Production is planned on a weekly basis (i.e., 5 working days), and the output of the week must be stored before delivery. It takes
• 1.8 litres of space to store 1 kg of sunflower seeds;
• 1.9 litres of space to store 1 kg of peanuts;
• 1.3 litres of space to store 1 kg of broad beans.
The total storage space available is 100 litres. Due to a commercial agreement with a restaurant, Foodie Goodie must deliver at least 5 kg of broad beans every week. The marketing department estimates that the weekly demand for sunflower seeds, peanuts and broad beans does not exceed 11 kg, 15 kg and 8 kg, respectively, so the plant does not want to produce more than these quantities of sunflower seeds, peanuts and broad beans. In addition, the plant observes that sunflower seeds and peanuts are often purchased by consumers at the same time, so it hopes that the output level of these two products can be similar. Specifically, the weekly output level of sunflower seeds can be higher or lower than that of peanuts, but the deviation should be at most 2kg. Finally, the net profit per kilogram of sunflower seeds, peanuts and broad beans are $4, $6 and $7, respectively. The plant wants to determine the production quantity of each product in a week in order to maximize the total net profit.
(a) Develop a linear programming (LP) formulation for the problem.
(b) Summarise how much each product should be produced per week after solving the problem with Excel Solver. Please paste here three screenshots,
(1) The problem worksheet just before clicking the Solver command: after developing the Excel Solver model, press Ctrl + ~ (tilde) to show formulas in cells, and then take a screenshot with row and column headings;
(2) The Solver Parameters dialog just before clicking Solve; and
(3) The Answer Report created by the Excel Solver. Please limit the answer to within three pages.
Step by step
Solved in 3 steps with 6 images
