Question 1 information Jerome Yoong plans to market his custom brand of packaged trail mix in...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 | 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 |
Expert Answer:
Related Book For
Spreadsheet Modeling and Decision Analysis A Practical Introduction to Business Analytics
ISBN: 978-1285418681
7th edition
Authors: Cliff Ragsdale
Posted Date:
Students also viewed these finance questions
-
Tuckered Outfitters plans to market a custom brand of packaged trail mix. The ingredients for the trail mix will include Raisins, Grain, Chocolate Chips, Peanuts, and Almonds costing, respectively,...
-
Manual programming for the following picture : 3.000 2.250 1.500 .750 PART#:00002 ZEE & SLOTS (MANUAL PROGRAMMING) 5.0 REF. .500 5 1.375 3.000 2.250 TITLE: PART#: 00002 (ZEE & SLOTS) DRAWN BY:...
-
Consider the following projects: a. Calculate the profitability index for A and B assuming a 20% opportunity cost of capita l. b. Use the profitability index rule to determine which project(s) you...
-
This exercise concerns TM M 2 , whose description and state diagram appear in Example 3.7. In each of the parts, give the sequence of configurations that M 2 enters when started on the indicated...
-
Stone Brewing Co. is a San Diego brewer that has sold its beers for over two decades. Stone has maintained its trademark and brand from the beginning, registering the STONE mark in 1998. Stone has...
-
Effect of order quantity on special order decision Ellis Quilting Company makes blankets that it markets through a variety of department stores. It makes the blankets in batches of 1,000 units. Ellis...
-
Find an application of OLAP (online analytical processing) for FIU. How can FIU use multidimensional analytical queries to generate BI? Some examples?
-
The proposed rates were not in the range the CEO expected given the pricing analysis. The CEO has asked the pricing actuary to verify the total projected loss cost excluding potential large storm...
-
What is the value of R after each of the following APL statements are executed? R 3 x 5+2 R +/237 R/852
-
Case Study - Managing Multicultural Teams The reading is taken from Harvard Business Review, 2006. Please access the reading from the reading list under "Session 10: Working in multicultural teams."...
-
Consider the following data collected in the lab. Fill in the calculated values. Be sure to record the mass and density of the liquid to the correct number of significant figures. Mass Of Graduated...
-
Your bond portfolio has a probability of default of 3%/year, loss given default of 60%, and a weighted average contractual rate of 5.5%/year. A) What is the expected loss per year? B) What is the...
-
Each summary should include a descriptive and evaluative paragraph on the following attributes: Include the origins of the model (who developed it, when was it developed, and the context under which...
-
Option trading 1 Mike buys 6 call options (contracts) on Boeing. Mike does not hold Boeing stock or other option positions. The options expire in five months. The premium is c=$19.97. How much cash...
-
The city of Westminster is installing new street lights in one of its neighborhoods. This project is considered to be a major general government capital project that the city deems will directly...
-
The power company must generate 100 kW in order to supply an industrial load with 94 kW through a transmission line with 0.09 resistance. If the load power factor is 0.83 lagging, find the...
-
Use Solver to create a Sensitivity Report for question 17 at the end of Chapter 3, and answer the following questions: a. Is the solution degenerate? b. Would the solution change if the price of...
-
Refer to question 24 at the end of Chapter 2. Implement a spreadsheet model for this problem and solve it using Solver.
-
A software company is moving its helpdesk operations center from Newark, New Jersey, to Plano, Texas. Some of its employees will be moved to Plano, and others will be terminated and replaced by new...
-
Graph the following table: a. What is marginal product and average product at each level of production? b. Graph marginal product and average product. c. Label the areas of increasing marginal...
-
If average product is falling, what is happening to short-run average variable cost?
-
If marginal cost is increasing, what do we know about average cost?
Study smarter with the SolutionInn App