Q4

For the mathematical models either use Word Equations, or write down the formulas on a paper, take a photo, and copy the photo.

you MUST upload the word file and an Excel Workbook. For the optimization problems, you must use the Excel solver.

Question 4) (25 points) Consider a transportation problem. There are a set of suppliers and a set of customers in a region. The customers have their known demand of a specific product. Suppliers have supply capacities for the product. An operations manager wants to make shipment decisions, i.e., how many items to ship from each supplier to each customer, subject to the demand requirements and supply capacities. The demand and supply information are presented below. Suppliers Capacity Supply Customers Demand 1 2 3 SIA 600B 950 | 900 D 700 JE 650 F 400 250 350 450 400 450 4 S 6 300 A shipping cost is charged for shipping each unit of item from a supplier to a customer. The cost information is summarized below. According to this cost matrix, for example to ship 100 units of the item from supplier 2 to customer B, a shipping cost of is charged. The goal is to minimize the total transportation costs. Cost Matrix From TO ABCDEFG 110210310310 21 914 514 314 6 3 3 5 3 5 1188 4217181927 41 5 366 3210 682241084 1- How many items should be shipped from each supplier to each customer to minimize the total transportation costs? Formulate a mathematical model for this problem and use Excel to solve the problem. 2- What is the excess capacity of supplier 2 in the optimal solution