# Solver modeling for production mix

1. ## Solver modeling for production mix

I'm trying to set up a solver model and can't figure out how to add a certain constraint:

Given are 4 different products (Product A, B, C and D) with its given profit per unit. Let's assume the profit is \$1, \$2, \$3, \$4 respectively. Additionally the time to produce each unit is given, let's assume 1 minute, 2 minutes, 3 minutes and 4 minutes respectively. I'm trying to maximize the profit. the variable cells are the quantities for each product.
Constraints are:
total amount of production time available can't exceed 21600 minutes
Product D >20 units
For every 10 units of Product A and B produced, one unit of Product C must be produced - This is the constraint I can't figure out how to incorporate into the model.  Register To Reply

2. ## Re: Solver modeling for production mix

You could try defining the quantity of C with a formula based on A & B

=MIN(ROUND(B2,-1),ROUND(B3,-1))/10

When I had a go at solving, it suggests just make product D with these profits and times.  Register To Reply

3. ## Re: Solver modeling for production mix

Hi treemaster

As Martin already pointed out your present profit setup will only make solver produce 1260 units of product D so in order to get a more sensible answer I reversed the product proffits.

for the constraint of the C production I used the mod function and as you specified D production > 20 and as solver only has ">=" I specified D as ">=21"

solv_mod.jpg

Alf  Register To Reply

4. ## Re: Solver modeling for production mix

Something strange with the setup, modifying the formula for C production to "=((E3-MOD(E3,10))+(E4-MOD(E4,10)))*0.1 and setting target cell ">=4700" I got a much better profit. A thing one would expect as maximising production of A, minimizing production of D and balancing the C production according to the ratio is the optimal solution.

Don't know why the model did not see that at once? Something to do with the MOD function?

solv_mod2.jpg

Havin tested a bit more I replaced the C constraint with the "INT" function instead of using the "MOD" function so formula for C production is now ``Please Login or Register  to view this content.``
and this seems to work better as I got the target cell value to 4753 without specifying that this value should be ">=4700" and production was A =1127, B =0, C =112 and D =21.

Alf  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 