Problem:
Non-Linear Programming; Using Excel Solver Add-in:
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.
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?