Excel Solver solver is messing up my optimization

问题:

I have set up an optimization problem but i must be doing something wrong and I could use your help. I have three firms: alpha, Bravo, Charlie. They each complete three tasks: Milling, Inspecting, Drilling. They each require different amounts of minutes to complete each task. Alpha requires 12 minutes to mill, 5 minutes to inspect and 10 minutes to drill. Bravo requires 10 minutes to mill, 4 to inspect, and 8 to drill. Charlie requires 8 to mill, 4 to inspect, and 16 to drill. After each firm completes all of these tasks they will earn a certain amount of profit, Alpha will earn $2.40, Bravo will earn $2.50, and Charlie will earn $3.00. All three firms have a maximum allotted time of 1200 minutes to mill, 900 to inspect, and 1440 to drill. The goal is to maximize the profit of these three firms. I have set it up so that the sums of the tasks will take away from the available time left when changed by the solver. I have also set constraints within the solver to cap each task to the allotted time allowed per task. I must be missing a vital step however because it keeps trying to just max out the allotted time for an individual firm, not taking in to account the opportunity cost of the other firms or something. Please help! (shown in photos)

Data

Solver

After executing Solver

回答1:

I have changed the logic a bit different in order to take the minimum unit into consideration:

UNITS portion are the variable cells. Since the final produced unit will be the minimum of these cells, E9 formula is =MIN(B9:D9) and copied down.

TIME portion is multiplication of Unit Times and Units. So the formula of B14 is =B9*B2 and copied down & right.

I9:I11 are the earnings calculated by multiplying the unit earning with the minimum units

I12 is our total earning and is our Objective cell.

Please also be careful about the constraints since when you do not set an integer constrain, finding a solution becomes more difficult and of course our units should be integer in any case.

And also fill B9:D11 cells with some values such as 100, since otherwise iteration does not start correctly and solver ends up with a very small objective cell.

回答2:

I have just had a go at this and I get a different answer as I have made the assumption that to achieve the profit the company must complete a milling process, then inspect, then drill and once all are complete then that is 1 unit for the profit - I hope that is valid.

But if not, then this layout may help you anyway. Note I have set this as a Linear model for the solver and also note the use of integer and non-negative.

It was fun anyway !