Problem 4 - Transshipment - Grandma's Cheese Shed (15%) Your team has recently accepted a consulting role at Grandma's Cheese Shed and has been tasked with helping their logistics department. Recently, Grandma's Cheese Shed has added two new cheeses as part of their "Grandmas Pack'n HEAT" line of spicy foods, these two cheeses are 'Carolina Reaper' Cheddar and 'Heat is Real' Muenster. These new cheese varieties have been so popular, Grandma's Cheese Shed has been struggling to keep them in stock. Your team is tasked with producing a transportation model to find the lowest possible daily cost of shipping these new spicy cheeses to the many cheese sheds scattered across Ohio. The head of the logistics department has provided you with some information to help your team build the transportation model. First, you are informed that while there are no capacity constraints associated with the company's two warehouses in Columbus and Canton, the connections between the various cheese factories, warehouses, and cheese sheds do have a limited capacity. The refrigerated trucks that will be used to ship the spicy cheeses must share space with other products and therefore, each truck can only carry 1000 blocks of spicy cheese at a time. You are also told that each facility connection has one truck assigned to it that runs twice a day, with two deliveries being made. Second, you are informed about an agreement that exists between Grandma's Cheese Shed and Trader Terry's, a large grocery store chain in the Midwest. This agreement allows Grandma to sell her products in Trader Terry's if Grandma delivers the products to Trader Terry's Distribution center. For each block of cheese delivered to Trader Terry's Distribution Center, Grandma's Cheese Shed will earn 50 cents. Furthermore, you are informed that 15% of total spicy cheese sales are for the 'Heat is Real' Muenster. Lastly, its not possible for Grandma to sell a partial block of cheese! Be sure to make this an integer program. In the template, the following variables represent the respective cheeses: �= 'Carolina Reaper' Cheddar �= 'Heat is Real' Muenster The decision variable will work like so: X 'product type' - 'start node' - 'end node' USE SOLVER AND EXCEL FORMULAS TO COMPLETE THE TABLE. Transshipment - Grandma's Cheese ShedX A-1-5X A-2-5X A-2-6X A-3-6X A-4-6 XA-4-12 XA-4-13X A-5-6X A-5-7X A-5-8X A-5-9 X A-6-5XA-6-10 XA-6-11 XA-6-12 X A-6-13X B-1-5X B-2-5X B-2-6X B-3-6X B-4-6X B-4-12X B-4-13X B-5-6X B-5-7 X B-5-8X B-5-9X B-6-5X B-6-10 X B-6-11X B-6-12X B-6-13Optimal solution000000000000000000Min$0.15$0.12$0.25$0.30 $0.35$0.20 $0.25$0.25$0.25$0.10$0.05$0.25$0.05 $0.15$0.25$0.30$0.15$0.12$0.25$0.30$0.35$0.20$0.25$0.25$0.25$0.10$0.05 $0.25$0.05$0.15$0.25$0.30$0.00LHSConstriantProduction tableconstr (factory)Demand table constr(shed)Warehouse constr15%B constr11Additional constrevery decision variable is integar, =0,

Question

Problem 4 - Transshipment - Grandma's Cheese Shed (15%) Your team has recently accepted a consulting role at Grandma's Cheese Shed and has been tasked with helping their logistics department. Recently, Grandma's Cheese Shed has added two new cheeses as part of their &#34;Grandmas Pack'n HEAT&#34; line of spicy foods, these two cheeses are 'Carolina Reaper' Cheddar and 'Heat is Real' Muenster. These new cheese varieties have been so popular, Grandma's Cheese Shed has been struggling to keep them in stock. Your team is tasked with producing a transportation model to find the lowest possible daily cost of shipping these new spicy cheeses to the many cheese sheds scattered across Ohio. The head of the logistics department has provided you with some information to help your team build the transportation model. First, you are informed that while there are no capacity constraints associated with the company's two warehouses in Columbus and Canton, the connections between the various cheese factories, warehouses, and cheese sheds do have a limited capacity. The refrigerated trucks that will be used to ship the spicy cheeses must share space with other products and therefore, each truck can only carry 1000 blocks of spicy cheese at a time. You are also told that each facility connection has one truck assigned to it that runs twice a day, with two deliveries being made. Second, you are informed about an agreement that exists between Grandma's Cheese Shed and Trader Terry's, a large grocery store chain in the Midwest. This agreement allows Grandma to sell her products in Trader Terry's if Grandma delivers the products to Trader Terry's Distribution center. For each block of cheese delivered to Trader Terry's Distribution Center, Grandma's Cheese Shed will earn 50 cents. Furthermore, you are informed that 15% of total spicy cheese sales are for the 'Heat is Real' Muenster. Lastly, its not possible for Grandma to sell a partial block of cheese! Be sure to make this an integer program. In the template, the following variables represent the respective cheeses: �= 'Carolina Reaper' Cheddar �= 'Heat is Real' Muenster The decision variable will work like so: X 'product type' - 'start node' - 'end node' USE SOLVER AND EXCEL FORMULAS TO COMPLETE THE TABLE. Transshipment - Grandma's Cheese ShedX A-1-5X A-2-5X A-2-6X A-3-6X A-4-6 XA-4-12 XA-4-13X A-5-6X A-5-7X A-5-8X A-5-9 X A-6-5XA-6-10 XA-6-11 XA-6-12 X A-6-13X B-1-5X B-2-5X B-2-6X B-3-6X B-4-6X B-4-12X B-4-13X B-5-6X B-5-7 X B-5-8X B-5-9X B-6-5X B-6-10 X B-6-11X B-6-12X B-6-13Optimal solution000000000000000000Min$0.15$0.12$0.25$0.30 $0.35$0.20 $0.25$0.25$0.25$0.10$0.05$0.25$0.05 $0.15$0.25$0.30$0.15$0.12$0.25$0.30$0.35$0.20$0.25$0.25$0.25$0.10$0.05 $0.25$0.05$0.15$0.25$0.30$0.00LHSConstriantProduction tableconstr (factory)Demand table constr(shed)Warehouse constr15%B constr11Additional constrevery decision variable is integar, >=0, <=2000-0.15-0.15-1-1-1-1-1-1-0.15-0.15-0.15-0.15-0.15-1-0.15-1-0.150.850.85RHS016150s26350S15300S00 S850S4650s1700s220ΟΙ3800s12000S3800 S116004600S1000-1-1-1-10E0-1-1-1-10=00.850.850.850.850.850.850.85ΟΙ=0

Accepted Answer

Grandma's Cheese Shed is struggling to keep up with the demand for their new spicy cheeses. To…