Can someone show me how to set this up in excel and solver set up. Thank you.
A food manufacturing company is trying to figure out how many of each snack items, i.e, Cookie, Bar, CupCake,Muffin to make. Profit per unit is $45, $115, $80, and $40 respectively. It has already committed to
make atleast 30 units of Bar, 15 units of CupCake, and 10 units of Muffin. The marketing department feels that they cannot sell more than 23 units of Cookie. These products need the 4 resources shown below
which have limited capacities. The amount of each resource, each unit of snack item needs is also provided.
QUESTION 7 A food manufacturing company is trying to figure out how many of each snack items, i.e, Cookie, Bar, CupCake, Muffin to make. Profit per unit is $45, $115, $80, and $40 respectively. It has already committed to make atleast 30 units of Bar, 15 units of CupCake, and 10 units of Muffin. The marketing department feels that they cannot sell more than 23 units of Cookie. These products need the 4 resources shown below which have limited capacities. The amount of each resource, each unit of snack item needs is also provided. Resource Cookie Bar CupCake Muffin Capacity Available Milk 5 5 10 5 580 gallons Oven 5 15 10 5 730 hours Sugar 50 40 30 20 2920 pounds Flour 50 75 25 50 6050 pounds Solve this problem using Solver in the excel HW file and use the output to answer the following questions here. Note: Penalty for wrong answers. The lower limit of production of 15 units on CupCake is a good thing in terms of profit. If an extra hour of Oven is available at $7.5, the management should get it. The production quantities will change when profit from Bar is in between $125 to $130. The production quantities will not change if the profit from CupCake increases to $85. It is okay to sell 20 hours of Oven to someone if they pay $5.75 /hour. It is okay to sell 200 pounds of Flour to someone if they pay $1.27 pound. The maximum profit that can be obtained is $5,770. The production quantities will not change as long as the profit from Muffin is in between $35 or lesser The total profit will decrease if the minimum requirement of Bar becomes 34 instead of 30. There is no benefit in terms of profit if we get rid of the minimum production quanitity for Bar. The next door manager asked for 10 hours of Oven. The manager would be okay with that since it doesnot effect profit. Tha Oven constraint is binding. QUESTION 7 A food manufacturing company is trying to figure out how many of each snack items, i.e, Cookie, Bar, CupCake, Muffin to make. Profit per unit is $45, $115, $80, and $40 respectively. It has already committed to make atleast 30 units of Bar, 15 units of CupCake, and 10 units of Muffin. The marketing department feels that they cannot sell more than 23 units of Cookie. These products need the 4 resources shown below which have limited capacities. The amount of each resource, each unit of snack item needs is also provided. Resource Cookie Bar CupCake Muffin Capacity Available Milk 5 5 10 5 580 gallons Oven 5 15 10 5 730 hours Sugar 50 40 30 20 2920 pounds Flour 50 75 25 50 6050 pounds Solve this problem using Solver in the excel HW file and use the output to answer the following questions here. Note: Penalty for wrong answers. The lower limit of production of 15 units on CupCake is a good thing in terms of profit. If an extra hour of Oven is available at $7.5, the management should get it. The production quantities will change when profit from Bar is in between $125 to $130. The production quantities will not change if the profit from CupCake increases to $85. It is okay to sell 20 hours of Oven to someone if they pay $5.75 /hour. It is okay to sell 200 pounds of Flour to someone if they pay $1.27 pound. The maximum profit that can be obtained is $5,770. The production quantities will not change as long as the profit from Muffin is in between $35 or lesser The total profit will decrease if the minimum requirement of Bar becomes 34 instead of 30. There is no benefit in terms of profit if we get rid of the minimum production quanitity for Bar. The next door manager asked for 10 hours of Oven. The manager would be okay with that since it doesnot effect profit. Tha Oven constraint is binding
