TechCo specializes in producing and distributing a specific electronic component. The supply chain involves three key echelons:

• Suppliers: There are two suppliers located in Austin (AUS) and Dallas (DAL).
• Plants: TechCo operates two manufacturing plants located in Philadelphia (PHL) and Baltimore (BAL).
• Customers: The end customers are in Boston (BOS) and Buffalo (BUF).

Resource Consumption, Availability and Cost at Plants

For the manufacturing plants, two types of resources are essential: labor and machine. The following tables provides how much the product consumes each resource type (hours/unit), total availability of resources, and cost of producing product at the two plants:

Resource Consumption (hours/unit)
Plant	Machine	Labor
PHL	2	4
BAL	3	5

Total Availability (hours)
Plant	Machine	Labor
PHL	1200	1000
BAL	800	1200

Plant	Cost per unit
PHL	$ 20
BAL	$ 17

Purchasing Costs from Suppliers

TechCo has the option to purchase the finished product from its suppliers. The cost per unit for procuring from each supplier and suppliers capacity are as follows:

Supplier	Cost/ Unit	Capacity (units)
AUS	$ 22	450
DAL	$ 21	450

Transportation Costs

The following table provides the transportation cost (cost per unit) from suppliers to plants:

From/To	PHL	BAL
AUS	$ 9	$ 8
DAL	$ 11	$ 10

And the subsequent table shows the transportation cost (cost per unit) from plants to the end customers:

From/To	BOS	BUF
PHL	$ 4	$ 3
BAL	$ 5	$ 4

Demand and Selling Price

The demand for the product at each customer location along with the selling price per unit is:

Customer	Demand (units)	Selling Price / Unit
BOS	500	45
BUF	400	40

Questions

1. Minimize Total Cost: What is the optimal strategy for sourcing components, production, and shipping to minimize the total cost?
2. Maximize Profits: Assuming all produced or procured units will be sold, what is the optimal production and shipping strategy to maximize profit?
3. Compare Objectives: Explain how and why the production and transportation plan varies (or does not vary) when focusing on minimizing costs versus maximizing profits.

Hint: This problem involves three main components: suppliers, plants, and customers. First, products are purchased from suppliers and transported to the plants. Additionally, these plants can manufacture products, limited by available labor and machine hours. The next step is to transport both purchased and manufactured products to their final destinations, the customers. Therefore, we have three types of decision variables: 1. Variables for Transporting Purchased Products from Suppliers to Plants (2 suppliers * 2 plants = 4 variables) 2. Variables for Production at Plants (2 variables - one for each plant; note that we have just one product that can be made in two different plants, so two variables are needed) 3. Variables for Transporting Products from Plants to Customers (2 plants * 2 customers = 4 variables) In total, there are 10 variables. Key: Each plant's outbound shipments must not exceed the sum of its purchased products and its own production. In other words, the number of products going out of each plant should be less than or equal to the products purchased for that plant plus the products produced by that plant.

CREATE AN EXCEL SOLUTION