Please Do on excel.
Problem 1 (100 points): Supply chain transportation - Multicommodity Problem Transportation plays a significant role in a supply chain and its efficiency. In particular, reducing the transportation costs between the manufacturers, warehouses, and retailers helps to provide more affordable product prices for customers. A major manufacturing company, MC Inc., produces its star product, Product A, to be sold at five retailers across the state. First, the products are manufactured in one of two plants, then distributed to three warehouses for storage, and finally are distributed to each of the retailers. Each retailer sends a separate order to the manufacture's head office, which is then dispatched from the appropriate warehouse to the retailer. The company has collected data regarding weekly orders and production and would like to find a way to minimize the costs of the entire operation. To expand their market share, the company decided to fabricate a second product, Product B. To fabricate Product B, not only Plants 1 and 2 were adapted accordingly, but also a third plant, Plant 3, was constructed. Considering the degree of specialization and the difficulty associated with making each product, as well as the machines available at each plant, you have calculated the average number of units (of each product) that an average worker would be able to make in an hour. This information is given in Table 1. For example, Table 1 indicates that the average number of units of Product A that a worker can make in one hour in Plant 1 is 0.5. In other words, to produce one unit of Product A, you could have one worker to dedicate two hours of labor, or two workers to dedicate one hour. Table 1. Average number of units produced per person per hour Production Facility Product A (units/hour-person) | Product B (units/hour-person) Plant 1 0.5 0.1 Plant 2 0.5 0.1 Plant 3 0.2 0.4 The number of workers (devoted to production) in each plant is given in Table 2. Also, assume that each employee works 48 hours per week. Table 2. Number of workers per plant Production Facility Workers Plant 1 Plant 2 | 115 Plant 3 76 85 The production of the three plants is delivered to the warehouses on Monday morning before the orders form the retailers are received. The capacity of the warehouses is given in Table 3. Table 3. Warehouse storage capacities Warehouse Facility Total storage capacity of the warehouse (in cubic feet) Warehouse 1 600 Warehouse 2 500 Warehouse 3 700 Keep in mind that a unit of Product A occupies one cubic foot, while one unit of Product B occupies 2 cubic feet. The weekly orders made by the retailers for the first week of October are given in Table 4. Table 4. Weekly orders (in number of units) made by the retailers on the first week of October Product Request Retailer 1 Retailer 2 Retailer 3 Retailer 4 Retailer 5 Product A 180120140100160 Product B 100 150 110 300 250 The transportation costs (per pound) from the plants to warehouses and warehouses to retailers are given in Table 5 and Table 6, respectively. Keep in mind that one unit of Product 1 weighs one pound, while one unit of Product B weighs 3 pounds. Table 5. Transportation costs, per pound, from production plants to warehouses Unit Shipping cost (per pound) From/To Warehouse 1 Warehouse 2 Warehouse 3 Plant 1 $20 $25 Plant 2 $50 $30 $95 Plant 3 $30 $45 $80 $50 Table 6. Transportation costs, per pound, from warehouses to retailers Unit Shipping Cost (per pound) From/To Warehouse 1 Warehouse 2 Warehouse 3 Retailer 3 $60 Retailer 1 $65 $85 $90 Retailer 2 $50 $10 Retailer 4 $75 $75 $35 Retailer 5 $10 $90 $85 $85 $25 $95 Additionally, due to diverse company regulations, there is a maximum weekly weight that can be sent from each plant to each warehouse (shown in Table 7) and from each warehouse to each retailer (shown in Table 8). Table 7. Maximum weekly weight (of products) that can be sent from each plant to each warehouse Maximum weekly amount of product shipped (in pounds) From/To | Warehouse 1 Warehouse 2 | Warehouse 3 Plant 1 120 150 170 Plant 2 150 160 180 Plant 3 150 170 180 M . Table 8. Maximum weekly weight (of products) that can be sent from each warehouse to each retailer Maximum weekly amount of product shipped (per pound) From/To Retailer 1 Retailer 2 Retailer 3 Retailer 4 Retailer 5 Warehouse 1 | 160 190 110 180 150 Warehouse 2 170 190 150 140 120 Warehouse 3 | 140 160 180 120 100 In case you don't fully satisfy the demand of one or more retailers, you agreed to pay the retailers $500 per unit of Product A not supplied, and $800 per unit of Product B not supplied. Using the provided information, you are asked to: 1. (10 points) Draw the network that represents the company's distribution network. Problem 1 (100 points): Supply chain transportation - Multicommodity Problem Transportation plays a significant role in a supply chain and its efficiency. In particular, reducing the transportation costs between the manufacturers, warehouses, and retailers helps to provide more affordable product prices for customers. A major manufacturing company, MC Inc., produces its star product, Product A, to be sold at five retailers across the state. First, the products are manufactured in one of two plants, then distributed to three warehouses for storage, and finally are distributed to each of the retailers. Each retailer sends a separate order to the manufacture's head office, which is then dispatched from the appropriate warehouse to the retailer. The company has collected data regarding weekly orders and production and would like to find a way to minimize the costs of the entire operation. To expand their market share, the company decided to fabricate a second product, Product B. To fabricate Product B, not only Plants 1 and 2 were adapted accordingly, but also a third plant, Plant 3, was constructed. Considering the degree of specialization and the difficulty associated with making each product, as well as the machines available at each plant, you have calculated the average number of units (of each product) that an average worker would be able to make in an hour. This information is given in Table 1. For example, Table 1 indicates that the average number of units of Product A that a worker can make in one hour in Plant 1 is 0.5. In other words, to produce one unit of Product A, you could have one worker to dedicate two hours of labor, or two workers to dedicate one hour. Table 1. Average number of units produced per person per hour Production Facility Product A (units/hour-person) | Product B (units/hour-person) Plant 1 0.5 0.1 Plant 2 0.5 0.1 Plant 3 0.2 0.4 The number of workers (devoted to production) in each plant is given in Table 2. Also, assume that each employee works 48 hours per week. Table 2. Number of workers per plant Production Facility Workers Plant 1 Plant 2 | 115 Plant 3 76 85 The production of the three plants is delivered to the warehouses on Monday morning before the orders form the retailers are received. The capacity of the warehouses is given in Table 3. Table 3. Warehouse storage capacities Warehouse Facility Total storage capacity of the warehouse (in cubic feet) Warehouse 1 600 Warehouse 2 500 Warehouse 3 700 Keep in mind that a unit of Product A occupies one cubic foot, while one unit of Product B occupies 2 cubic feet. The weekly orders made by the retailers for the first week of October are given in Table 4. Table 4. Weekly orders (in number of units) made by the retailers on the first week of October Product Request Retailer 1 Retailer 2 Retailer 3 Retailer 4 Retailer 5 Product A 180120140100160 Product B 100 150 110 300 250 The transportation costs (per pound) from the plants to warehouses and warehouses to retailers are given in Table 5 and Table 6, respectively. Keep in mind that one unit of Product 1 weighs one pound, while one unit of Product B weighs 3 pounds. Table 5. Transportation costs, per pound, from production plants to warehouses Unit Shipping cost (per pound) From/To Warehouse 1 Warehouse 2 Warehouse 3 Plant 1 $20 $25 Plant 2 $50 $30 $95 Plant 3 $30 $45 $80 $50 Table 6. Transportation costs, per pound, from warehouses to retailers Unit Shipping Cost (per pound) From/To Warehouse 1 Warehouse 2 Warehouse 3 Retailer 3 $60 Retailer 1 $65 $85 $90 Retailer 2 $50 $10 Retailer 4 $75 $75 $35 Retailer 5 $10 $90 $85 $85 $25 $95 Additionally, due to diverse company regulations, there is a maximum weekly weight that can be sent from each plant to each warehouse (shown in Table 7) and from each warehouse to each retailer (shown in Table 8). Table 7. Maximum weekly weight (of products) that can be sent from each plant to each warehouse Maximum weekly amount of product shipped (in pounds) From/To | Warehouse 1 Warehouse 2 | Warehouse 3 Plant 1 120 150 170 Plant 2 150 160 180 Plant 3 150 170 180 M . Table 8. Maximum weekly weight (of products) that can be sent from each warehouse to each retailer Maximum weekly amount of product shipped (per pound) From/To Retailer 1 Retailer 2 Retailer 3 Retailer 4 Retailer 5 Warehouse 1 | 160 190 110 180 150 Warehouse 2 170 190 150 140 120 Warehouse 3 | 140 160 180 120 100 In case you don't fully satisfy the demand of one or more retailers, you agreed to pay the retailers $500 per unit of Product A not supplied, and $800 per unit of Product B not supplied. Using the provided information, you are asked to: 1. (10 points) Draw the network that represents the company's distribution network
