Business Analytics (Prescriptive models) The subject is linear programming. Please help. I'm really confused. What do I insert into Excel for A? Also need help for B/C/D and E? I'm really lost. Thanks in advance.
INFOMGMT290 Question Booklet QUESTION 3 A pet food manufacturer makes 2 types of feed for dogs - DeliChoice and Premium. Both of them contain 2 main ingredients: Meat and Jelly Filler. It is stated on the packaging that the DeliChoice Feed contains at least 30% meat in its content, while Premium contains at least 60% meat. Each kg of DeliChoice sells for $40, while each kg of Premium sells for $80. Production of the meat content costs $30/kg, while jelly filler costs $20/kg. This week, the manufacturer has to supply 1 ton of DeliChoice and 1 ton of Premium to its customers. Currently, there are 2 tons of meat and 1 ton of jelly filler available. The manufacturer is not expecting any additional production of meat and jelly filler until next week. a. Formulate the scenario described above as a linear programming optimisation problem, in order to determine the amount of meat and jelly filler (in kg's) to be used in its DeliChoice and Premium products this week. As a result, its profits must be maximised, without violating any constraints. (12 marks) b. The following is the Excel answer report showing the solution to this problem: Objective Cell (Max) Cell Name Original Value Final Value $1$4 Objective Function Totals 65000 0 Original Value 0 Variable Cells Cell Name $D$2 Decision Variables DM $E$2 Decision Variables DJ $F$2 Decision Variables PM $G$2 Decision Variables PJ 0 0 0 Final Value Integer 500 Contin 500 Contin 1000 Contin O Contin Constraints Cell Name Cell Value Formula Status Slack $1$11 Meat Avail Totals 1500 $I$11=$K$7 Not Binding 200 $I$8 Min PM Totals 400 $1$8>=$K$8 Not Binding 400 $I$9 Deli Demand Totals 1000 $I$9=$K$9 Binding 0 $I$10 Premium Demand Totals 1000 $I$10=$K$10 Binding 0 (Note: DM, DJ, PM, PJ are symbols representing kg's of Meat in DeliChoice, Jelly in DeliChoice, Meat in Premium, and Jelly in Premium, respectively). Page 12 of 14 INFOMGMT290 Question Booklet D - ) K v > XXXXX 8 Min PM > XXXXX IX XXXXX XXXXX XXXXX Using the information in this answer report, complete the blanks (i to x) in the following Excel formulation spreadsheet. Note that some of the values in the spreadsheet are disguised with "XXXXX". B F F H 1 DM DJ PM PJ Totals 2 Decision Variables lii iii liv 3 4 Objective Function XXXXX XXXXX XXXXX 65000 5 6 Constraints 7 Min DM XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX vi 9 Deli Demand XXXXX XXXXX XXXXX XXXXX vii 10 Premium Demand XXXXX XXXXX XXXXX XXXXX viii 11 Meat Avail XXXXX XXXXX XXXXX 12 Jelly Avail XXXXX XXXXX XXXXX XXXXX X (5 marks) C. The following is the sensitivity report generated in Excel: A B D E F G H 6 Variable Cells 7 Final Reduced Objective Allowable Allowable 8 Cell Name Value Cost Coefficient Increase Decrease 9 $D$2 Decision Variables DM 500 0 10 0 1E+30 10 $E$2 Decision Variables DJ 500 0 20 1E+30 0 11 $F$2 Decision Variables PM 1000 0 50 1E+30 0 12 $G$2 Decision Variables PJ 0 0 60 0 1E+30 13 14 Constraints 15 Final Shadow Constraint Allowable Allowable 16 Cell Name Value Price R.H. Side Increase Decrease 17 $I$11 Meat Avail Totals 1500 0 2000 1E+30 500 18 $I$12 Jelly Avail Totals 500 10 500 200 500 19 $1$7 Min DM Totals 200 0 0 200 1E+30 20 $1$8 Min PM Totals 400 0 o 400 1E+30 21 $1$9 Deli Demand Totals 1000 10 1000 500 285.7142857 22 $1$10 Premium Demand Totals 1000 50 1000 500 1000 Answer the following parts with justifications based on specific values found in this report. i. List all binding constraints in this solution. Why are they binding? (2 marks) ii. If the manufacturer can purchase additional jelly filler for use this week from a competitor, at $15/kg, how much should it buy in order to further increase profits? Why? (2 marks) iii. An additional 500kg of meat is found in the factory. If the model is re-run with this additional resource, what will be the impact on the optimal profit? Why? (2 marks) Page 13 of 14 INFOMGMT290 Question Booklet d. In light of the optimal solution, if the manufacturer goes ahead accordingly, and if the consumers have found out about such discrepancies from the information stated on the packaging of the products, what will they think? Explain your answer. (4 marks) e. Optimisation models are "static models. Discuss the pros and cons of using a static optimisation model for decision support in this scenario. Your arguments and reasoning should be specific to the context of this scenario. (3 marks) INFOMGMT290 Question Booklet QUESTION 3 A pet food manufacturer makes 2 types of feed for dogs - DeliChoice and Premium. Both of them contain 2 main ingredients: Meat and Jelly Filler. It is stated on the packaging that the DeliChoice Feed contains at least 30% meat in its content, while Premium contains at least 60% meat. Each kg of DeliChoice sells for $40, while each kg of Premium sells for $80. Production of the meat content costs $30/kg, while jelly filler costs $20/kg. This week, the manufacturer has to supply 1 ton of DeliChoice and 1 ton of Premium to its customers. Currently, there are 2 tons of meat and 1 ton of jelly filler available. The manufacturer is not expecting any additional production of meat and jelly filler until next week. a. Formulate the scenario described above as a linear programming optimisation problem, in order to determine the amount of meat and jelly filler (in kg's) to be used in its DeliChoice and Premium products this week. As a result, its profits must be maximised, without violating any constraints. (12 marks) b. The following is the Excel answer report showing the solution to this problem: Objective Cell (Max) Cell Name Original Value Final Value $1$4 Objective Function Totals 65000 0 Original Value 0 Variable Cells Cell Name $D$2 Decision Variables DM $E$2 Decision Variables DJ $F$2 Decision Variables PM $G$2 Decision Variables PJ 0 0 0 Final Value Integer 500 Contin 500 Contin 1000 Contin O Contin Constraints Cell Name Cell Value Formula Status Slack $1$11 Meat Avail Totals 1500 $I$11=$K$7 Not Binding 200 $I$8 Min PM Totals 400 $1$8>=$K$8 Not Binding 400 $I$9 Deli Demand Totals 1000 $I$9=$K$9 Binding 0 $I$10 Premium Demand Totals 1000 $I$10=$K$10 Binding 0 (Note: DM, DJ, PM, PJ are symbols representing kg's of Meat in DeliChoice, Jelly in DeliChoice, Meat in Premium, and Jelly in Premium, respectively). Page 12 of 14 INFOMGMT290 Question Booklet D - ) K v > XXXXX 8 Min PM > XXXXX IX XXXXX XXXXX XXXXX Using the information in this answer report, complete the blanks (i to x) in the following Excel formulation spreadsheet. Note that some of the values in the spreadsheet are disguised with "XXXXX". B F F H 1 DM DJ PM PJ Totals 2 Decision Variables lii iii liv 3 4 Objective Function XXXXX XXXXX XXXXX 65000 5 6 Constraints 7 Min DM XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX vi 9 Deli Demand XXXXX XXXXX XXXXX XXXXX vii 10 Premium Demand XXXXX XXXXX XXXXX XXXXX viii 11 Meat Avail XXXXX XXXXX XXXXX 12 Jelly Avail XXXXX XXXXX XXXXX XXXXX X (5 marks) C. The following is the sensitivity report generated in Excel: A B D E F G H 6 Variable Cells 7 Final Reduced Objective Allowable Allowable 8 Cell Name Value Cost Coefficient Increase Decrease 9 $D$2 Decision Variables DM 500 0 10 0 1E+30 10 $E$2 Decision Variables DJ 500 0 20 1E+30 0 11 $F$2 Decision Variables PM 1000 0 50 1E+30 0 12 $G$2 Decision Variables PJ 0 0 60 0 1E+30 13 14 Constraints 15 Final Shadow Constraint Allowable Allowable 16 Cell Name Value Price R.H. Side Increase Decrease 17 $I$11 Meat Avail Totals 1500 0 2000 1E+30 500 18 $I$12 Jelly Avail Totals 500 10 500 200 500 19 $1$7 Min DM Totals 200 0 0 200 1E+30 20 $1$8 Min PM Totals 400 0 o 400 1E+30 21 $1$9 Deli Demand Totals 1000 10 1000 500 285.7142857 22 $1$10 Premium Demand Totals 1000 50 1000 500 1000 Answer the following parts with justifications based on specific values found in this report. i. List all binding constraints in this solution. Why are they binding? (2 marks) ii. If the manufacturer can purchase additional jelly filler for use this week from a competitor, at $15/kg, how much should it buy in order to further increase profits? Why? (2 marks) iii. An additional 500kg of meat is found in the factory. If the model is re-run with this additional resource, what will be the impact on the optimal profit? Why? (2 marks) Page 13 of 14 INFOMGMT290 Question Booklet d. In light of the optimal solution, if the manufacturer goes ahead accordingly, and if the consumers have found out about such discrepancies from the information stated on the packaging of the products, what will they think? Explain your answer. (4 marks) e. Optimisation models are "static models. Discuss the pros and cons of using a static optimisation model for decision support in this scenario. Your arguments and reasoning should be specific to the context of this scenario
