Project I: Materials Planning Problem Tasty Coffee Drink decides to operate a shop at Hong Kong and sells three of their most popular beverages - Cappuccino, Latte and Mocha, which are composed of the following ingredients: (i) Cappuccino: Coffee Bean (40g), Milk Foam (10g), Steamed Milk (10g) (ii) Latte: Coffee Bean (25g), Milk Foam (5g), Steamed Milk (20g) (iii)Mocha: Coffee Bean (30g), Steamed Milk (20g), Chocolate Powder (15g) To reduce risk of getting out-of-stock, Tasty Coffee Drink has to place order with supplier to ensure that there is sufficient coffee bean, milk foam, steamed milk and chocolate powder in shop. As shown in Table 1, there are different costs for each ingredient, in which coffee bean costs $14 per kg, milk foam costs $8 per kg, steamed milk costs $6 per kg and chocolate powder costs $5 per kg. As a promotion, the company can order from the supplier using a discount price, i.e. 15% off, for each type of ingredient on every Thursday. However, no order can be made on every Tuesday and Friday. As the company can order more than the daily demand and store the ingredient in shop for future use, the inventory holding cost for each ingredient is incurred. There is no inventory held before Monday. Table 1. Costs for each ingredient Coffee Milk Ingredients Bean Foam Ingredient Cost per kg (Except Thursday) $14 $8 Ingredient Cost per kg (Thursday only) $11.9 $6.8 Inventory Holding Cost per kg $2.6 $0.5 Steamed Milk $6 $5.1 $1 Chocolate Powder $5 $4.25 $0.3 Tasty Coffee Drink is going to develop a system to prepare the ordering plan based on the previous weekly demands, as shown in Table 2. Cappuccino Latte Mocha Table 2. Weekly demands for each beverage Mon Tue Wed Thu Fri 52 40 45 56 106 48 70 41 58 88 68 40 41 57 112 Sat 133 111 160 Sun 109 134 128 What to do: 1. Being the representative of your company, you are required to prepare a material ordering plan so as to determine when to order and the amount to order for each ingredient, aiming to minimize the overall cost (i.e. ingredient cost and holding cost). Formulate a LP model for this problem. 2. [Excel] Create a user interface to allow the manager to change the daily demand for planning the new ordering schedule. 3. [Excel] Add more functions to your system to allow decision making for different scenarios