Explore BrainMass

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.


Solution Summary

This posting contains solution to following problem on LPP using excel solver.