Share
Explore BrainMass

Non-Linear Programming using Excel Solver Add-in

Problem:
A company makes products C and D from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. One unit of C costs $30 to make and demand is estimated to be 50 - .09 * Price of C. One unit of D costs $20 to make and demand is estimated to be 30 - .14 * Price of D. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 90 and 140.

Product C D Available resources
Labor (hr/unit) 2 4 150
Material (ounces/unit) 2 8 220

Manufacturing cost($/unit) 30 20
Demand (units) 50 - 0.09*P1 30 - 0.14*P2

(Part a) Let X1 = demand for C's and X2 =demand for D's. Let P1 = price for C's and P2 = price for D's. Provide an algebraic formulation for the NLP.

(Part b) Implement and solve the problem in Excel. Describe the optimal solution.

(Part c) Comment on the values of the Lagrange multipliers for the Labor and Material availability constraints. What is worth noting?

The following problem requires the use to the Solver add-in within Excel. No other software application(s) will suffice for the purposes of this learning exercise. Please show all work and label the answers, so the student can benefit from context. Note: The problem is also posted in the attached MS Word document, so that it is sure to be legible in transmission. The formatting is disrupted in this text field, but the MS Word document clearly conveys the columns, etc.

Attachments

Solution Summary

Non-Linear Programming using Excel Solver Add-in is investigated. The solution is detailed and well presented. The response received a rating of "5/5" from the student who originally posted the question.

$2.19