Foodie Goodie

Question 1

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. Please limit the answer to within one page.

(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.

Question 2

Since the Covid-19 outbreak, the use of sports facilities and venues has dropped significantly.

You have a group of friends who are frequent visitors to a swimming pool and they are interested in understanding the impact of Covid-19 on the income of the swimming pool operator. Knowing that you have learned the knowledge related to making forecasts, your friends ask for your opinion on the following questions.

Please use relevant data in the Excel workbook Q2_input_file.xlsx in Canvas (next to this question paper) to answer the following questions.

(a) Comment on the underlying pattern in the swimming pool attendances over 2016 1Q – 2019 4Q. Please limit the answer to within one page.

(b) Use a suitable method to forecast the swimming pool attendances for every quarter between 2020 1Q and 2021 2Q, assuming that actual data in that range is not yet available. After that, combined with the actual data, estimate the income loss of the swimming pool for each quarter in that range, assuming that each attendance brings in income of $5 for the swimming pool. Please limit the answer to within two pages.

(c) Name another time series method that is suitable for forecasting the quarterly swimming pool attendances for Part (b) and briefly describe the steps. Then compare the method you used in Part (b) with this method: when the former is more appropriate; when the latter is more appropriate.

You do not need to show any detailed calculations for Part (c). You can show general formulas for easy description