Formulate the problem as a linear program to determine how the cars should be distributed in order to minimize the distribution costs.

I have done a lot of the leg work however I cannot figure out what exactly the constraints are. I need help can you please be very specific with your explanation?

Cars-to-Dealers Logistics Inc. is the exclusive logistics provider for the Summit Motor Co. and has responsibility for transporting cars from Summit's 3 manufacturing plants in Toledo, OH; Charlotte, NC and Savannah, GA to the 7 regional dealer locations as in the tables below. To do this, Cars-to-Dealers uses three transshipment facilities and all cars in the distribution network pass through these transshipment facilities. The tables below include information about the capacities of each of these transshipment facilities, the demand requirement that the company wants to try and meet at each of the regional dealer locations and the cost to ships one car between the various locations. Manufacturing Plants Dealer Locations Toledo, OH Charlotte, NC Savannah, GA # of cars available each month 75,000 85,000 60,000 Syracuse, NY Jacksonville, FL Los Angeles, AZ Portland, OR Chicago, IL Kansas City, MO Salt Lake City, UT Demand for cars each month 30,000 35,000 45,000 20,000 35,000 25,000 25,000 Transshipment Facilities Capacity (cars handled) each month Louisville, KY 50,000 Denver, CO 90,000 Phoenix, AZ 80,000 To Cost to ship each car Transshipment from the Toledo plant Facilities ($) Louisville, KY 302 Denver, CO 1234 Phoenix, AZ 1927 Cost to ship each car from the Charlotte plant ($) 473 1500 2050 Cost to ship each car from the Savannah plant ($) 600 1649 2093 To Dealer Locations Cost to ship each car from the Louisville facility ($) Cost to ship each car from the Denver facility ($) Cost to ship each car from the Phoenix facility ($) 677 Syracuse, NY Jacksonville, FL Los Angeles, AZ Portland, OR Chicago, IL Kansas City, MO Salt Lake City, UT 767 2086 2301 298 507 1579 1667 1747 1016 1239 1002 602 517 2343 2043 373 1335 1753 1207 657 In addition to supply of cars (manufacturers) and demand for cars (dealers) there are two types of constraints for the transshipment facilities: 1. Balance flow constraints: dictate that all cars flowing into a transshipment facility should also flow out of the transshipment facility - i.e. no inventory of cars should remain at any of the transshipment facilities. 2. Inflow=Outflow. In other words, sum of what quantities going in should be equal to sum of what quantities going out to each transshipment facility. 2. Capacity constraint: Above constraint does not account for capacity of each Transshipment facility. For example, what is going can be equal to what is going out in Louisville, but total cars going through (sum of inflows) should not exceed the capacity of Louisville. Formulate the problem as a linear program to solve the Cars-to-Dealers Logistics Inc. transshipments cost to optimality. Note: You may use Question_1_shell.xlsx file to get started Questions a. Draw a network flow model to represent this problem. (Any way is OK, for example you can draw it on a piece of paper and insert it as a picture etc.,) b. Implement your model in Excel and solve it. a) Create a conditional formatting (Data Bars) for the all the Decision Variables b) Create Slicers filters for Origin and Destination" cities c) Generate a sensitivity report for the results d) What is total cost under the optimal plan? Which manufacturing plant and transshipment center does not use all its available capacity? c. Explain why the shadow price for the Toledo constraint. (Hint: What would happen if there were one more extra car available at Toledo? Re-run Solver to find out.) If an option existed to expand the capacity at Toledo by 10,000 cars at a total cost of $4 million, would it be worth doing? What if the total cost is only $3 million? Then would it be worth doing? Justify your answer. d. Now reset the supply of cars available at Toledo to the original 75,000 cars. Explain the shadow price for Phoenix in a similar way. e. Would the company rather see an increase in demand at the Syracuse location or the Chicago location? Why? Cars-to-Dealers Logistics Inc. is the exclusive logistics provider for the Summit Motor Co. and has responsibility for transporting cars from Summit's 3 manufacturing plants in Toledo, OH; Charlotte, NC and Savannah, GA to the 7 regional dealer locations as in the tables below. To do this, Cars-to-Dealers uses three transshipment facilities and all cars in the distribution network pass through these transshipment facilities. The tables below include information about the capacities of each of these transshipment facilities, the demand requirement that the company wants to try and meet at each of the regional dealer locations and the cost to ships one car between the various locations. Manufacturing Plants Dealer Locations Toledo, OH Charlotte, NC Savannah, GA # of cars available each month 75,000 85,000 60,000 Syracuse, NY Jacksonville, FL Los Angeles, AZ Portland, OR Chicago, IL Kansas City, MO Salt Lake City, UT Demand for cars each month 30,000 35,000 45,000 20,000 35,000 25,000 25,000 Transshipment Facilities Capacity (cars handled) each month Louisville, KY 50,000 Denver, CO 90,000 Phoenix, AZ 80,000 To Cost to ship each car Transshipment from the Toledo plant Facilities ($) Louisville, KY 302 Denver, CO 1234 Phoenix, AZ 1927 Cost to ship each car from the Charlotte plant ($) 473 1500 2050 Cost to ship each car from the Savannah plant ($) 600 1649 2093 To Dealer Locations Cost to ship each car from the Louisville facility ($) Cost to ship each car from the Denver facility ($) Cost to ship each car from the Phoenix facility ($) 677 Syracuse, NY Jacksonville, FL Los Angeles, AZ Portland, OR Chicago, IL Kansas City, MO Salt Lake City, UT 767 2086 2301 298 507 1579 1667 1747 1016 1239 1002 602 517 2343 2043 373 1335 1753 1207 657 In addition to supply of cars (manufacturers) and demand for cars (dealers) there are two types of constraints for the transshipment facilities: 1. Balance flow constraints: dictate that all cars flowing into a transshipment facility should also flow out of the transshipment facility - i.e. no inventory of cars should remain at any of the transshipment facilities. 2. Inflow=Outflow. In other words, sum of what quantities going in should be equal to sum of what quantities going out to each transshipment facility. 2. Capacity constraint: Above constraint does not account for capacity of each Transshipment facility. For example, what is going can be equal to what is going out in Louisville, but total cars going through (sum of inflows) should not exceed the capacity of Louisville. Formulate the problem as a linear program to solve the Cars-to-Dealers Logistics Inc. transshipments cost to optimality. Note: You may use Question_1_shell.xlsx file to get started Questions a. Draw a network flow model to represent this problem. (Any way is OK, for example you can draw it on a piece of paper and insert it as a picture etc.,) b. Implement your model in Excel and solve it. a) Create a conditional formatting (Data Bars) for the all the Decision Variables b) Create Slicers filters for Origin and Destination" cities c) Generate a sensitivity report for the results d) What is total cost under the optimal plan? Which manufacturing plant and transshipment center does not use all its available capacity? c. Explain why the shadow price for the Toledo constraint. (Hint: What would happen if there were one more extra car available at Toledo? Re-run Solver to find out.) If an option existed to expand the capacity at Toledo by 10,000 cars at a total cost of $4 million, would it be worth doing? What if the total cost is only $3 million? Then would it be worth doing? Justify your answer. d. Now reset the supply of cars available at Toledo to the original 75,000 cars. Explain the shadow price for Phoenix in a similar way. e. Would the company rather see an increase in demand at the Syracuse location or the Chicago location? Why