please help with this problem. the correct answers are total production is $2,901,500 and total shipping cost is $109,860. total cost is $3,011,360. thank you

The Sentry Lock Corporation manufactures a popular commercial security lock at plants in Macon, Louisville, Detroit and Phoenix. The per unit cost of production at each plant is $35.⁵⁰, $37. ⁵⁰, $39. ⁰⁰ and $36. ²⁵, respectively, while the annual production capacity of each plant is 18,000, 15,000, 25,000 and 20,000 respectively.

Sentrys 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 summarized in the following table along with the forecasted demand for each distributor for the coming year.

	Unit Shipping Cost to Distributor in
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	$.190	$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
Demand	8,500	14,500	13,500	12,600	18,000	15,000	9,000

Sentry would like to determine the least expensive way of shipping locks from their manufacturing plants to their distributors. Because the total demand from distributors exceeds the total production capacity for all the plants, Sentry realizes they will not be able to satisfy all the demand for their product; but would like to make sure that each distributor will have the opportunity to fill at least 80% of the orders they receive.

Q: create the spreadsheet for the model and provide the optimal solution?