Select Page

Solved by verified expert:SITUATION 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. QUESTIONS 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).
question.png

hw13_assignment_lp_01.pdf

Don't use plagiarized sources. Get Your Custom Essay on
Just from \$10/Page

Unformatted Attachment Preview

ADMN 580 HW13 (Linear Programming Homework)
SITUATION
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.
QUESTIONS
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).
GUIDELINES FOR MODEL, ANALYSIS, and SUBMISSION
Put all your work in a single Excel file. Use one sheet for the model and text box responses for parts a, c,
and d. The Answer and Sensitivity Reports will be additional sheets (generated when you run Solver on a
correct/flexible model). Use a text box to write your responses for parts a, c, and d, and put this in the
same worksheet as your model. Label things clearly, including the sheet tabs (i.e., don’t keep them like
“Sheet1”).
As with all spreadsheets, criteria of correct, flexible, and documented will be applied.
TURN IN (Submit to Canvas): Excel File. Name your file lastname_firstname_HW13.xlsx. Submit this to
the Canvas Assignment for HW13.
This assignment counts as two regular homework assignments in the course (i.e., 20 points). Your base
model must be correct, flexible, and documented. Your Solver settings must be correct and flexible (no
changes needed in Solver settings for constraints if the problem data change). Your writeup must be
correct, relevant to management, and the writing quality must be good. The algebraic formulation must be