Question

Klein Chemicals, Inc., produces a special oil-based material that is currently in short supply. Four of Klein’s customers have already placed orders that together exceed the combined capacity of Klein’s two plants. Klein’s management faces the problem of deciding how many units it should supply to each customer. Because the four customers are in different industries, different prices can be charged because of the various industry pricing structures. However, slightly different production costs at the two plants and varying
transportation costs between the plants and customers make a “sell to the highest bidder

strategy unacceptable. After considering price, production costs, and transportation costs, Klein established the following profit per unit for each plant–customer alternative:
Customer

plant D1 D2 D3 D4

Clifton Springs $32 $34 $32 $40

Danville $34 $30 $28 $38

The plant capacities and customer orders are as follows:
plant    Capacity (units) distributor Orders (units)

Clifton Springs 5000    D1 2000

D2 5000

Danville    3000 D3 3000

D4 2000

How many units should each plant produce for each customer to maximize profits? Which customer demands will not be met? Show your network model and linear programming formulation.

I need every step in excel especially solver

Answer 1

The excel solution begins with creating the spreadsheet. Use a model as shown below. This may be slightly different (looks wise) on Windows computer.

The formulas for the spreadsheet is shown below. The above table is the information provided. The table below is the transportation table where decision variables are calculated.

Open the solver window and use the following formulas. Solver window is available under data tab. On windows, you may need to activate the solver plug-in.

The result is shown below. The second table shows the ideal transportation plan to maximize profit. The customer D2 and D3 will not get their complete orders.