Reference no: EM132186791

Guardsman Lock Corporation

The Guardsman Lock Corporation manufactures a popular commercial security lock at plants in Macon, Louisville, Detroit and Phoenix. Table 1 shows the unit cost of production and the annual production capacity at each plant.

Table 1. Unit production costs and annual production capacities by plant

Macon Louisville Detroit Phoenix

Unit Cost $35.50 $37.50 $39.00 $36.25

Capacity (yr.) 18000 22000 25000 20000

Guardsman’s locks are sold to retailers through wholesale distributors in seven cities across the United States. The unit cost of shipping from each plant to each distributor is given in the Table 2.

Table 2. Unit Shipping Costs

Plants Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore

Macon $2.50 $2.75 $1.75 $2.00 $2.10 $1.80 $1.65

Louisville $1.85 $1.90 $1.50 $1.60 $1.00 $1.90 $1.85

Detroit $2.30 $2.25 $1.85 $1.25 $1.50 $2.25 $2.00

Phoenix $1.90 $0.90 $1.60 $1.75 $2.00 $2.50 $2.65

Table 3 shows the sales prices (in $ per unit) along with the forecasted demand (i.e., the maximum quantity the distributor is willing to receive; the distributor may take less) for each distributor for the coming year.

Table 3. Annual demands and sales prices

Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore

Demand 8500 14500 13500 12600 18000 15000 9000

Sales price $42.50 $41.75 $45.00 $42.90 $40.40 $41.85 $44.20

Create a Solver-based spreadsheet model to determine the number of units to be shipped from each plant to each distributor city in order to maximize profit and solve the model.

Comments and Hints:

1. Note that that total shipped from each plant must incur the unit cost of production and the total received at each distributor will earn the sales price.

2. Create formula cells for total production cost, total distribution cost, total revenue and profit.

3. Do not use Integer constraints on changing cells; they are unnecessary in transportation problems as long as supply and demand are integer-valued.