Question

Background: 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

Question:

I need to know the green usage column and the yellow decision variables row.

Answer 1

In a distribution company's LP problem, the 'Usage' column is calculated with the SUMPRODUCT function combining costs and quantities, while Solver optimizes the decision variables to minimize transportation costs while considering economies of scale.

When setting up a Linear Programming (LP) problem to minimize transportation costs for a distribution company, we need to consider several economic principles. The task involves determining the optimal way to distribute a product from factories to warehouses based on the production function and cost considerations. The company should aim for economies of scale, where increasing the quantity of output decreases the cost per unit, thereby reducing average costs and increasing profits.

In the LP problem, the decision variables, represented in a yellow table, will indicate the number of products transported from each factory to each warehouse. The green 'Usage' column would be calculated using the SUMPRODUCT function in a spreadsheet, multiplying the shipping costs from the pink table with the amounts from the yellow table, which Solver will optimize. To set up the constraints for Solver, one would include the supply limits for factories and the demand requirements for warehouses, ensuring that the number of products shipped does not exceed the factory capacity and meets the warehouse demands.