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

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.

GRADING

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

correct, internally consistent, and consistent with your spreadsheet model.

ACADEMIC HONESTY

This is an INDIVIDUAL assignment. Do your own work, every keystroke (it is very easy to detect toomuch collaboration on spreadsheet-based assignments). You can discuss the assignment with others, but

in no case should you be copying, mimicking, or re-wording the work of someone else. Do not share your

file with anyone electronically or otherwise. Use of materials from a former semester/section or other

source is also a violation of academic honesty. Please see the syllabus, and the Academic Honesty section

of UNH Student Rights, Rules, and Responsibilities (available online) if you if you are unclear about

UNH’s Academic Honesty Policy.

…

Purchase answer to see full

attachment