I really need help with doing the excel part and the formulas used in and around solver. I am not good with excel. Thank you for your help.

To actually be able to see the excel spreadsheet set up with the formulas would be helpful.

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\mathrm{.}50,$$$37\mathrm{.}50,$$$39\mathrm{.}00,$ and $$36\mathrm{.}25,$ respectively, while the

annual production capacity at each plant is $18,000,15,000,25,000,$ and $20,000,$

respectively. Sentry'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 summarized in the following table along with the forecasted demand

from each distributor for the coming year.

Sentry wants to determine the least expensive way of manufacturing and shipping

locks from its plants to the distributors. Because the total demand from distributors

exceeds the total production capacity for all the plants, Sentry realizes it will not be

able to satisfy all the demand for its product, but wants to make sure each distributor

will have the opportunity to fill at least $80\%$ of the orders received.

a$.$ Create a spreadsheet model for this problem and solve it$.$

b$.$ What is the optimal solution?