Explore BrainMass

# LPP using excel solver

Not what you're looking for? Search our solutions OR ask your own Custom question.

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

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.

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 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.

## SOLUTION This solution is FREE courtesy of BrainMass!

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.
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.
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.

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!