Help! Excel Solver problem

Hi guys,

I am a first year Business student, currently taking a Management Science course. Recently, I came across this problem as I was doing my homework, formulated the Linear Programming (we are only allowed to use LP) model and tried solving it using Excel Solver. I came across repeated "Excel could not find a feasible solution" as well as objective and constraint errors as I changed the formulas for the cells. I don't understand why this is so, could I get some help with this?

Thanks in advance!

Assignment1_2014.doc
Assignment1_Template.xls

As the forum rules states that

In this forum we cannot allow direct help with homework or assignment questions, if we tell you how to do it you will never learn how to do it! We can answer specific questions and point you in the right direction,

I'll try to pass on some hints that may be of help. You have set range B6:D6 to be the cells to change. Why this range? Should not this be the result range for how much "Grade I" and "Grade II" you use in the production of each quality?

You also need to rethink you average grade formula since you get an error by the zero division and solver is not happy with that.

Alf

I appreciate the advice, I do want to learn how to create these Excel models so that I might be able to do the same in my tests as well.

1) The copy that I attached varied B6:D6, but in my attempts I did choose to vary $B$4:$D$5 too.

2) I honestly have no clue how to generate that average formula which doesn't involve a division by 0; also shouldn't Excel generate values for my variables which would avoid having to divide by 0 in the final solution?

I guess what I really need help with now is with thinking of a formula that allows me to avoid the div0 error

Well if the one constraint is that quality of 75% Choc. should be 8 the you could change it to a constraint like this

Please Login or Register  to view this content.

where B12=B4*B10+B5*B11 and B13=8*B6 i.e. tons of Grade I * quality Grade I + tons of Grade II * quality of Grade II >= tons produced 75 % Choc. * 8

Alf

Thanks Alf for that tip, managed to get answers for a few questions.

Just need help once more:

Is there a way to format cells such that the value in one cell is x% of the value in another cell?

Thanks

Yes by adding a constraint i.e. cell D4 should be equal to or less than 75% of cell B3 so add a formula F3=B3*.75 and add the constraint

Please Login or Register  to view this content.

The more interesting part of this exercise is how to set up solver as the production cost for baking chocolate is $8410 but you can only sell it for $8000 so this is making a loss of $410 for each ton sold of baking chocolate. On the other hand if no baking chocolate is sold this will generate a surplus of cocoa beans and there is a loss of $1850 for each ton of cocoa beans not sold.

In my opinion solver should be set up so that all 240 ton of cocoa beans are "consumed"

Alf

Ps If this fixed you problem could you please mark thread "Solved" as per forum rules.

To mark your thread solved do the following:

-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Interesting to know but with a price tag of around US$ 3000 for the software I for one will stick to the Excel solver.

Alf

Thanks for the link! Have dowloaded the basic version and will have a go at it. Sounds interesting!

Regards

Alf