Develop a spreadsheet model to address the following scenario. Along with coming up with an analytically$-$substantiated response, the premium here is to develop a clear and concise spreadsheet model that contains relevant information.

Shafer Supplies has four distribution centers, located in Atlanta, Lexington, Milwaukee, and Salt Lake City, and ships to $12$ retail stores, located in Seattle, San Francisco, Las Vegas, Tucson, Denver, Charlotte, Minneapolis, Fayetteville, Birmingham, Orlando, Cleveland, and Philadelphia. The company wants to minimize the transportation costs of shipping one of its higher$-$volume products, boxes of standard copy paper. The per$-$unit shipping cost from each distribution center to each retail location and the amounts currently in inventory and ordered at each retail location are shown in the table below.

$1.$ Develop and solve an optimization model to minimize the total transportation cost and answer the following questions. Respond below this question in RED font

$2.$ What is the minimum cost of shipping? Respond below this question in RED font

$3.$ Which distribution centers will operate at capacity in this solution? Respond below this question in RED font

$4.$ Suppose that $500$ units of extra supply are available $($and that the cost of this extra capacity is a sunk cost$).$ To which distribution center should this extra supply be allocated and why? Respond below this question in RED font

$5.$ Suppose that the cost of shipping from Atlanta to Birmingham increased by $$\mathrm{.}45$ per unit. What would happen to the optimal solution? Respond below this question in RED font