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 Annual capacity 18000 22000 25000 20000 Guardsmans 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. This is a slight extension of the classic cost minimization transportation problem. 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