After our first foray to the solver four days ago, we find today's ability to find the solution to an optimization problem.
We have three factories and four warehouses (A, B, C and D). The table in B2: E14 represents unit transport costs of each plant to each warehouse. Line 5 represents the needs of each warehouse and column G which is available at each plant.
The problem to be solved is to find the quantities to be transported (block B9: F11) that will, within the constraints of need and availability, minimize total cost in G14 evaluated by the formula = SUMPRODUCT (B2 : F4, B9: F11) . B12: F12 and G9: G11, finally, we are the vertical and horizontal.
The problem is solved easily by using the solver as we see below with the result, then by setting the solver who has to get it:
Note - Do not you satisfied with the settings above! You must also, before clicking the button "Solve" button
go through the "Options" and check the options " Assume Linear Model "and" Assume Non-negative ".
If you miss the last option, the Excel solver fails to converge! Indeed, he tried in vain to carry negative quantities to reduce the total cost.
If you forget to specify that the model is linear, there will be small rounding errors, and in addition, you will not benefit - after the resolution - all useful information (in particular, the marginal values ).
0 comments:
Post a Comment