Question

The Sullivan brothers run a distribution company. They are responsible for transporting the product from three factories to four possible warehouses for a private company. The private company has already told them how much product each warehouse requires (demand) and how much each factory produces (supply). They’ve been told it doesn’t matter which factory supplies

what warehouse, and warehouses can be supplied by multiple factories. The shipping costs are listed below. Since the brothers get paid the same amount no matter how they choose to ship, their goal is to minimize their costs (thus increasing their profit). Your goal is to setup the LP problem and solve the optimal schedule for the brothers.
To fill in the pink table next to Cost Function, you need to use the shipping costs from each factory to each warehouse given to you above.
If a good or, in this case, factory/warehouse is involved, write down 1 in the related cell, and 0 otherwise. For example, to fill in cell E19, think whether Factory A is involved in the case of A:3 (i.e., when the product is being transported from Factory A to warehouse 3). Since the answer is yes, we would write down 1 in cell E19.
To fill in the green table (Usage), use SUMPRODUCT function. Note that the number of products transported from each factory to each warehouse should go into the yellow table (you need to use Solver to find the optimum numbers).
Use SUMPRODUCT to fill in the Minimize Total Cost cell (i.e., green cell). Note that the costs come from the pink table (one array in SUMPRODUCT), and number of products transported from each factory to each warehouse comes from yellow table (the other array in SUMPRODUCT).
Finally, use Solver to find the optimum values and minimum total transportation cost. Make sure you are adding proper constraints

Answer 1

To set up the LP (Linear Programming) problem and solve the optimal schedule for the Sullivan brothers, we need to follow these steps:

1. Identify the decision variables: We need to determine how much product should be transported from each factory to each warehouse. Let's denote this by Xij, where i represents the factory (1, 2, or 3) and j represents the warehouse (1, 2, 3, or 4).

2. Define the objective function: The objective is to minimize the total transportation cost. We can use the SUMPRODUCT function to calculate the total cost, considering the shipping costs from each factory to each warehouse (provided in the pink table) and the number of products transported from each factory to each warehouse (to be filled in the yellow table).

3. Set up the constraints: The constraints are as follows:

- Supply constraint: The total amount of product transported from each factory should not exceed the supply capacity of that factory.

- Demand constraint: The total amount of product received at each warehouse should meet the demand of that warehouse.

4. Solve the LP problem using Solver: Use the Solver tool in Excel (or any other LP solver) to find the optimum values for Xij that minimize the total transportation cost, while satisfying the supply and demand constraints.

Once you have the optimum values for Xij, you can fill in the yellow table with those values. Then, use the SUMPRODUCT function to calculate the minimum total transportation cost in the green cell.

Please note that without the specific shipping costs, supply capacity, and demand of each factory and warehouse, I cannot provide an exact solution to this LP problem. However, following the steps outlined above should help you set up and solve the problem in Excel or a similar tool.