# Linear programming: Formulation and excel solution

Danson furniture buys 2 products for resale: big shelves (B) and medium shelves (M). Each big shelf costs $500 and requires 100 cubic feet of storage space, and each medium shelf costs $300 and requires 90 cubic feet of storage space. The company has $75000 to invest in shelves this week, and the warehouse has 18000 cubic feet available for storage. Profit for each big shelf is $300 and for each medium shelf is $150. What is the optimal purchase quantity of each shelf and what is the maximum profit?

© BrainMass Inc. brainmass.com October 24, 2018, 7:32 pm ad1c9bdddfhttps://brainmass.com/business/operations-research/linear-programming-formulation-and-excel-solution-70936

#### Solution Preview

Hello

Here is the algebraic formulation

Decision variables:

B - Number of big shelves produced and sold

M - Number of medium shelves produced and sold

Objective ...

#### Solution Summary

Excel file contains formulation and optimal solution of linear programming problem. Excel solver is used.

Linear Programming Formulation

Below is a linear programming formulation for a problem with two variables, a and b, and with three constraints, referred to as constraints #1, #2,and #3. Each of the three constraints is a ">=" (greater than or equal to) inequality; the objective is to minimize Z, where the objective function Z is shown in the formulation.

Minimize Z = a + 2b

such that a >= 5 (constraint #1)

b >= 5 (constraint #2)

2a + b >= 20 (constraint #3)

a, b >= 0 (non-negativity constraints)

In this linear programming problem, for the questions that follow: the `solution' refers to the values of the variables, a and b, and the `objective function value' refers to the value of Z at the solution. Please consider each question below as separate from and independent of all others; the changes described are not cumulative. For each change consider it a change to the original problem. Show any work that you can to support your answers.

a. Set up this problem in excel; find the optimal solution using excel solver, and the solution's sensitivity report. What are your solution and your objective function value? For each constraint: what is its shadow price, what is its slack (or, you may call it surplus since these are `>=' constraints), and is it tight (yes or no)?

b. Based on your sensitivity report in part a, what do you anticipate will happen to the solution and objective function value in part a if the right-hand side of constraint #1 goes up by 1? Please be specific.

c. Increase the right-hand side of constraint # 1 by 1 (from 5 to 6) and resolve the problem. What are your solution and your objective function value? Is this consistent with what you anticipated in part b?

d. Based on your sensitivity report in part a, what do you anticipate will happen to the solution and objective function value in part a if the right-hand side of constraint #2 goes up by 1? Please be specific.

e. Return the RHS (right hand side) of constraint 1 to its original value. Increase the right-hand side of constraint # 2 by 1 (from 5 to 6) and resolve the problem. What are your solution and your objective function value? Is this consistent with what you anticipated in part d?

f. Based on your sensitivity report in part a, what do you anticipate will happen to the solution and objective function value in part a if the right-hand side of constraint #3 goes up by 1? Please be specific.

g. Return the RHS (right hand side) of constraint 2 to its original value. Increase the right-hand side of constraint # 3 by 1 (from 20 to 21) and resolve the problem. What are your solution and your objective function value? Is this consistent with what you anticipated in part f?

h. Based on your sensitivity report in part a, what do you anticipate will happen to the solution and objective function value in part a if the right-hand sides of all three constraints go up by 1? Why? Please be specific and show work to support your answer.

i. Increase the right-hand sides of all three constraints by 1 and resolve the problem. What are your solution and your objective function value? Is this consistent with what you anticipated in part h?

View Full Posting Details