# (Solved) (Part A) The ABC Corporation manufactures a popular commercial security lock at plants in Macon, Louisville, Detroit, and Phoenix. the annual...

I need help with this problem. Please see attachment. Thanks!Â

(Part A) The ABC Corporation manufactures a popular commercial security lock at plants in Macon,
Louisville, Detroit, and Phoenix. the annual production capacity at each plant is 18,000, 15,000, 25,000,
and 20,000 respectively. ABC's locks are sold to retailers through wholesale distributors in seven cities
across the U.S. The unit cost of shipping from each plant to each distributor is summarized in following
table with the forecasted demand from each distributor for the coming year.
Plants Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore Capacity
Macon \$2.50 \$2.75 \$1.75 \$2.00 \$2.10 \$1.80 \$1.65 18000
Louisville \$1.85 \$1.90 \$1.50 \$1.60 \$1.00 \$1.90 \$1.85 15000
Detroit \$2.30 \$2.25 \$1.85 \$1.25 \$1.50 \$2.25 \$2.00 25000
Phoenix \$1.90 \$0.90 \$1.60 \$1.75 \$2.00 \$2.50 \$2.65 20000
Demand 8500 14500 13500 12600 18000 15000 9000
ABC Corporation wants to determine the least expensive way of shipping locks from their plants to the
distributors. Because the total demand from distributors exceeds the total production capacity for all the
plants, ABC Corporation realizes that even when all capacities are used, they will not be able to satisfy all
the demand for their product, but wants to make sure each distributor will have the opportunity to fill at
least 80% of the orders they receive when all capacities of four plants are used. Please create an Excel
spreadsheet model for this problem and solve the optimal solution with minimum total (shipping) cost.
(Part B) Now consider the perâ€unit cost of production at each plant is \$35.50, \$37.50, \$39.00 and \$36.25,
respectively. Please create an Excel spreadsheet model for this problem and solve the optimal solution
with minimum total (shipping + production) cost.
Unit Producti
Plants Cost
Macon \$35.50
Louisville \$37.50
Detroit \$39.00
Phoenix \$36.25
(Part C) Now consider the perâ€unit sales commission cost of selling product in each city (see table
below). Please create an Excel spreadsheet model for this problem and solve the optimal solution with
minimum total (shipping + production + sales commission) cost.
Unit Sales Commission Costs to
Distribution Center in:
Tacoma San Diego Dallas Denver St. Louis Tampa Baltimore \$5.90 \$6.75 \$5.00 \$3.50 \$2.65 \$4.90 \$3.60

Solution details:
STATUS
QUALITY
Approved

This question was answered on: Mar 27, 2022

Solution~000200058392.zip (25.37 KB)

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free solution (Deadline assured. Flexible pricing. TurnItIn Report provided)

STATUS

QUALITY

Approved

Mar 27, 2022

EXPERT

Tutor