# LPP using excel solver

Exercise Assignment 1:

Please use Monet.xls. This is the example that was worked in Class. You must have Solver on your machine.

1. You will first need to re-enter all of the solver parameters. Do that, but do not add a non-negativity constraint. Solve the problem without the non-negativity constraint. When you send me the solution to the exercise, explain what this answer means. Solver didn't give you an infeasible message. You got (or should have) an optimal answer. So what do these numbers suggest or tell you. Remember Solver just gives you numbers, you give those numbers meaning.

Then add the non-negativity constraint and re-solve the problem. This should be your optimal answer:

Frame Type 1 1000

Frame Type 2 800

Frame Type 3 400

Frame Type 4 0

The marketing VP looks at this and tells you that you are crazy. (Marketing people frequently think that Operations people are crazy!) Retail outlets need to carry the full line of frames and that any one frame cannot be any less than 10% of the total number of frames made.

2. Revise the model to incorporate this new constraint. (You may have to add new cells and/or new constraints)

3. Create a one-way solver table that shows the effect on profit and the production quantities for that new constraint. Vary the percentage from 0% to 15%, in increments of 1%. That is, what if the number of any one frame couldn't be any less than 2% or 5% or 8%, etc. of the total number of frames. (You should see how important it is to put common data in a cell!) And be a little careful with the percentages. SolverTable will not "read" the formatting. So use the decimal equivalents instead of whole number percentages, i.e., 0.01 for 1%.

4. Save the file with the table and the new optimal answer. Post your file to the Dropbox.

Here is what I am looking for:

? An explanation of the answer you got when you didn't have the non-negativity constraint. I don't need to see this model, but you can attach it to the email and send me if you want to.

? A spreadsheet with the addition of the 'marketing' constraint.

? A SolverTable showing the profit and production quantities when you vary the 'marketing' constraint. You can attach one spreadsheet that has both b and c.

See attached for full problem description.

Â© BrainMass Inc. brainmass.com December 24, 2021, 6:26 pm ad1c9bdddfhttps://brainmass.com/business/business-management/excel-solver-operational-management-113491

## SOLUTION This solution is **FREE** courtesy of BrainMass!

Please see attached files.

Exercise Assignment 1:

Please use Monet.xls. This is the example that was worked in Class. You must have Solver on your machine.

1. You will first need to re-enter all of the solver parameters. Do that, but do not add a non-negativity constraint. Solve the problem without the non-negativity constraint. When you send me the solution to the exercise, explain what this answer means. Solver didn't give you an infeasible message. You got (or should have) an optimal answer. So what do these numbers suggest or tell you. Remember Solver just gives you numbers, you give those numbers meaning.

Then add the non-negativity constraint and re-solve the problem. This should be your optimal answer:

Frame Type 1 1000

Frame Type 2 800

Frame Type 3 400

Frame Type 4 0

The marketing VP looks at this and tells you that you are crazy. (Marketing people frequently think that Operations people are crazy!) Retail outlets need to carry the full line of frames and that any one frame cannot be any less than 10% of the total number of frames made.

2. Revise the model to incorporate this new constraint. (You may have to add new cells and/or new constraints)

3. Create a one-way solver table that shows the effect on profit and the production quantities for that new constraint. Vary the percentage from 0% to 15%, in increments of 1%. That is, what if the number of any one frame couldn't be any less than 2% or 5% or 8%, etc. of the total number of frames. (You should see how important it is to put common data in a cell!) And be a little careful with the percentages. SolverTable will not "read" the formatting. So use the decimal equivalents instead of whole number percentages, i.e., 0.01 for 1%.

4. Save the file with the table and the new optimal answer. Post your file to the Drop box.

Here is what I am looking for:

â€¢ An explanation of the answer you got when you didn't have the non-negativity constraint. I don't need to see this model, but you can attach it to the email and send me if you want to.

Monet_113491_1.xls contains both the models: with non-negativity constraint and without non-negativity constraint.

With no non negativity constraint, we find the following optimal solution with total profit of $9250.

Frame 1 Frame 2 Frame 3 Frame 4

1000 1000 500 -250

The solution may be optimal but not feasible practically as it is not possible to manufacture something in negative numbers. Thus, this solution does not have any meaning.

â€¢ A spreadsheet with the addition of the 'marketing' constraint.

Sheet titled "With marketing constraint 10%" in file Monet_113491.xls contains the marketing constraint for all 4 frames.

Constraint for frame 1 would be:

F1 >= 0.1(F1+F2+F3+F4)

Which comes down to:

0.9F1 -0.1F2 -0.1F3-0.1F4>=0

Where F1= no of frame1s produced and so on.

Similarly the constraints for other 3 frames are also entered.

â€¢ A SolverTable showing the profit and production quantities when you vary the 'marketing' constraint. You can attach one spreadsheet that has both b and c.

To create a solverTable, assumption is made that the marketing constraint is only for frame 4. Thus, the constraint: -0.1F1-0.1F2 -0.1F3+0.9F4>=0 is only entered in the spread sheet.

Then, using SolverTable add in, the one way table is created where the input cell is the one having value of co-efficient of F4(0.9). And select the range as 1 to 0.85 as we want the variation between 1 to 15%. The output cells are the number of frames1,2,3 and 4 and profit cell.

The table is created and stored on the spread sheet titled "SolverTable_marketing constraint" in the file Monet_113491.xls.

https://brainmass.com/business/business-management/excel-solver-operational-management-113491