The situation that of Problem B.38 in the textbook (Tri-State Manufacturing). Please see the textbook for the details and the data for the problem. Please see below for the specific tasks of this assignment.
a). Write the Algebraic LP Formulation. That is, define the decision variables, write the objective function, and write the constraints. Type this out into a text box in your Excel file that you submit (see below for guidelines for submission).
b). Implement a Linear Program for this problem in an Excel spreadsheet, and find the optimal solution using Solver. Use the LP Simplex solver engine. Generate the Answer Report and the Sensitivity Report once you have successfully been able to solve the problem. Make sure your spreadsheet model and Solver settings are correct, flexible, and documented. The user should be able to change any data value of the problem on the spreadsheet itself (not inside Solver), and then re-run the Solver model to find the new optimal solution.
c). Write a short paragraph (in an Excel text box) explaining the optimal plan and the optimal objective value, in the context (language) of the real situation. That is, make it relevant to the decision maker in management.
d). If one factory could be expanded by 1 unit, which one do you recommend for expansion? Why? Respond using the same text box created in (c).