uardsman 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 Unit Cost Annual Macon $35.50 18000 Louisville 37.50 22000 Detroit 9.00 25000 Phoenix 36.25 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 Shi Costs Plants Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore Macon Louisville $1.85 $1.90 $1.50 $1.60 $1.00 $1.90$1.85 Detroit Phoenix 2.50$2.751.75$2.002.10 1.80 $1.65 2.30$2.25$1.85$1.251.502.25 $2.00 1.90$0.90 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 Baltimore 9000 TacomaSan Diego Dallas Denver St. Louis Demand8500 Sales price $42.50 $41.75 14500 13500 12600 1800015000 1.85 .00 $42.90 $40.40 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. Do not use Integer constraints on changing cells; they are unnecessary in transportation problems as long as supply and demand are integer-valued. 3