Context within the Course: Andrew Carter is actually a very simple mixed integer program which we can solve in a single step. However, it is presented here before integer programming so that we can recognize the value of more incremental solutions and also appreciate the ability later to program directly to the global solution. Here the easiest solution without integer programming is likely to be four separate models and I suggest you each build a model for each of the four scenarios, without the fixed cost and then compare the output of the four models manually.

Problem: Andrew$-$Carter, Inc. $($A$-$C$)$ is a major Canadian producer and distributor of outdoor lighting fixtures. Its fixture is distributed throughout North America and has been in high demand for several years. The company operates three plants that manufacture the fixture and distribute it to five distribution centers.

During the past few years, A$-$C has seen a major drop in demand for its fixture as the housing market has declined. Based on the forecast of interest rates, the head of operations feels that demand for housing and thus for its product will remain depressed for the foreseeable future. A$-$C is considering closing one of its plants, as it is now operating with a forecasted excess capacity of $34,000$ units per week. The forecasted weekly demands for the coming year are $9,000$ units, $13,000$ units, $11,000$ units, $15,000$ units, and $8,000$ units for warehouses $1$ to $5,$ respectively. The regular time plant capacities $($in units per week$)$ are $27,000$ units, $20,000$ units, and $25,000$ units for plants $1$ to $3,$ respectively. The overtime plant capacities $($in units per week$)$ are $7,000$ units, $5,000$ units, and $6,000$ units for plants $1$ to $3,$ respectively.

If A$-$C shuts down any plants any plants, its weekly costs will change, as fixed costs are lower for a non$-$operating plant. Table $1$ shows production costs at each plant, both variable at regular time and overtime, and fixed when operating and shut down. Table $2$ shows distribution costs from each plant to each distribution center. Question $14$ pts

What is the Total cost $($Variable $+$ Fixed$)$ of keeping all plants open? Question $22$ pts

What is the total cost of closing only Plant $1?$ Question $32$ pts

What is the total cost of closing only Plant $2?$ Question $42$ pts

What is the total cost of closing only Plant $3?$ Question $50$ pts

Interpret the data and tell me if you were the decision maker for AC what action would you take based on what you know about the company after running the models. Be explicit about what factors you considered. You my tell me this in a short word DOC or simply on the first sheet in your excel submission $($preferred$).$ Upload you excel spreadsheet supporting the above answers. I NEED ALL OF THE QUESTIONS SOLVED IN EXCEL