The Nifty Company specializes in the production of a single product, which it produces in three plants. The product is doing very well, so the company currently is receiving more purchase requests than it can fill. Plans have been made to open an additional plant, but it will not be ready until next year. For the coming month, four potential customers (wholesalers) in different parts of the country would like to make major purchases. Customer 1 is the company’s best customer, so his full order will be met. Customers 2 and 3 also are valued customers, so the marketing manager has decided that, at a minimum, at least a third of their order quantities should be met. However, the marketing manager does not feel that Customer 4 warrants special consideration, and so is unwilling to guarantee any minimum amount for this customer. There will be enough units produced to go somewhat above these minimum amounts.

Due largely to substantial variations in shipping costs, the net profit that would be earned on each unit sold varies greatly, depending on which plant is supplying which customer.

Therefore, the final decision on how much to send to each customer (above the minimum amounts established by the marketing manager) will be based on maximizing profit.

The unit profit for each combination of a plant supplying a customer is shown in the body of the table below. The rightmost column gives the number of units that each plant will produce for the coming month (a total of 20,000). The bottom row shows the order quantities that have been requested by the customers (a total of 30,000). The marketing manager needs to determine how many units to sell to each customer (observing these minimum amounts above) and how many units to ship from each plant to each customer to maximize profit.

Plant                            Customer1       Customer2       Customer3       Customer4       Production

1                                 $55                  $42                  $46                  $53                  8,000

2                                 $37                  $18                  $32                  $48                  5,000

3                                 $29                  $59                  $51                  $35                  7,000

Requested Purchase    7,000               9,000               6,000               8,000              

Draw a network representation of the problem

Formulate the problem (that is, develop the objective function and constraints).

Use Excel to solve the problem and describe the solution (no need for answer report and sensitivity report).

(Include Excel or show step by step solution)