Question 1 information Jerome Yoong plans to market his custom brand of packaged trail mix in Australia's major supermarkets (Coles, Woolworths, Aldi, and Costco), given his current success in the US market. The ingredients for the trail mix will include Raisins, Grain, Chocolate Chips, and Peanuts, costing $1.20, $1.20, $1.70, and $1.60 per pound, respectively. The vitamins, minerals, and protein content of each of the ingredients (in grams per pound) is summarised in the following table, along with the calories per pound of ingredient: Raisins Vitamins 15 6 Minerals 10 9 10 Protein 5 Calories 400 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Question 1 working.xlsx]Normal 3 4 Variable Cells Cell $B$13 Ingredients (pounds) Raisins $C$13 Ingredients (pounds) Grain $D$13 Ingredients (pounds) Chocolate $E$13 Ingredients (pounds) Peanuts Grain Name 20 5 Name 5 150 11 12 Constraints 13 14 Cell 15 $B$19 at least 35 grams of vitamins LHS 16 $B$20 at least 15 grams of minerals LHS 17 $B$21 at least 15 grams of protein LHS 18 $B$22 at least 625 calories LHS 19 $B$23 two-pound package LHS 20 $B$24 at least 5% of the weight of the package: raisins LHS 21 $B$25 at least 5% of the weight of the package: grain LHS D Jerome Yoong would like to identify the least costly mix of these ingredients. The trail mix should have at least 35 grams of vitamins, 15 grams of minerals, 15 grams of protein, and 625 calories per two-pound package. Additionally, for each ingredient, there must be at least 5% and no more than 50% of the weight of the package. Final Value Chocolate Chips The LP model has been solved, and the sensitivity analysis report has been generated. 0.1 0.8 20 1 0.1 15 10 500 Reduced Cost 0 0 0 0 Final Shadow Constraint Price Value 40 R.H. Side 35 21 15 15 15 690 625 2 0.1 0.1 0 0 0.1 0 0.7 Peanuts 2 0.1 1.11022E-16 0.8 25 0 10 5 300 Objective Allowable Allowable Coefficient Increase Decrease 1.2 1E+30 1.11022E-16 1.2 1.11022E-16 1.7 1.6 1E+30 1E+30 Allowable Increase ddo 5 6 0 0.928571429 65 0.1 AN 0.7 1E+30 0.5 0.4 0.7 Allowable Decrease 1E+30 1E+30 1E+30 0 0.1 1E+30 Fi A B 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Question 1 working.xlsx] Normal 3 4 Variable Cells 5 678 Cell $B$13 Ingredients (pounds) Raisins $C$13 Ingredients (pounds) Grain 9 $D$13 Ingredients (pounds) Chocolate 10 $E$13 Ingredients (pounds) Peanuts 11 12 Constraints 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Name Cell $B$19 at least 35 grams of vitamins LHS $B$20 at least 15 grams of minerals LHS $B$21 at least 15 grams of protein LHS $B$22 at least 625 calories LHS Name $B$23 two-pound package LHS $B$24 at least 5% of the weight of the package: raisins LHS $B$25 at least 5% of the weight of the package: grain LHS $B$26 at least 5% of the weight of the package: chocolate chips LHS $B$27 at least 5% of the weight of the package: peanuts LHS $B$28 no more than 50% of the weight of the package: raisins LHS $B$29 no more than 50% of the weight of the package: grain LHS $B$30 no more than 50% of the weight of the package: chocolate chip LHS $B$31 no more than 50% of the weight of the package: peanuts LHS D Final Value 0.1 0.8 1 0.1 Final Value E Reduced Cost Shadow Price 0 0 0 0 0 0 0.1 0 0.7 40 21 15 690 2 0.1 1.11022E-16 0.8 1 0.1 0.1 0.8 1 0.1 0 0 0.4 Oooo F Objective Coefficient Allowable Increase 1.2 1.2 1.11022E-16 1.7 1.6 Constraint R.H. Side 35 15 15 625 2 0.1 0.1 0.1 0.1 1 1 1 1 1E+30 1E+30 1E+30 Allowable Increase H Allowable Decrease 1.11022E-16 1E+30 0.5 0.4 Allowable Decrease 5 6 0 0.928571429 65 0.1 0.7 0.7 0.9 0.7 1E+30 1E+30 1E+30 -3.55271E-16 1E+30 1E+30 1E+30 1E+30 0 0.1 1E+30 1E+30 0.1 0.9 0.2 0.9 Question 1 Question 1a Please refer to Question 1 information (above) and answer the following question. Q1a. Formulate a linear programming (LP) model to determine how much each ingredient should be used to produce the least costly trail mix for a two-pound package. (8 marks) You do not need to solve the model in Excel. Please type out your answers. Edit View Insert Format Tools Table 12pt Paragraph BI I U 8 pts T : Question 2 Question 1b Please refer to Question 1 information (above) and answer the following question. Q1bi. What is the amount of each ingredient being utilised and the total cost of this custom brand of packaged trail mix? In your answer, clearly label your answers. (2 marks) Q1bii. Define the Range of Feasibility in your own words. (1 mark) Q1biii. Write down the range of feasibility for the "at least 5% of the weight of the package" constraints. Label your answers clearly. (4 marks) Please type out and clearly label your answers. Edit View Insert Format Tools Table 12pt Paragraph | BI U A < 7 pts T : Question 3 Question 1c Please refer to Question 1 information (above) and answer the following question. Q1c. Jerome Yoong received not-so-good news from his supplier. He was informed that Raisins would increase in price. Would the optimal solution change if Raisins were $1.90 per pound? What would you expect to happen with the total cost if Raisins' price increased to that amount per pound? (4 marks) Please type out your answers. Edit View Insert Format Tools Table 12pt v Paragraph BI U A TV : 10 words 4 pts Question 4 Question 1d Please refer to Question 1 information (above) and answer the following question. Q1d. Jerome Yoong is considering varying the amount of Vitamins in the trail mix to make his product more attractive. Would the optimal solution and total cost change if the requirement of Vitamins were increased to 38 grams? Explain your answer. (4 marks) Please type out your answers. Edit View Insert Format Tools Table 12pt Paragraph B I UA 4 pts TV |