LP resolution with excel
To solve the linear program with Excel (LP resolution with Excel), you need the Solver module. On the File tab, click Options. Under Add-ins, select Solver Add-in and click the OK button. Check the solver add-in and click OK. If the maneuver does not work, you can click on Solver then Go to.
You will find the solver on the Data tab in the Analysis group, as shown in the following image.
Write a LP with Excel
To formulate a problem of linear programming, consider the following three questions:
- What are the decisions to be made? To resolve this issue, Excel needs to determine the amount of each product to order (bikes, mopeds, and child seats).
- What are the constraints on these decisions? The constraints here are that the amount of capital and storage used by products cannot exceed the limited amount of capital and storage (resources) available. For example, each bicycle uses 300 units of capital and 0.5 units of storage.
- What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products. The objective is therefore to maximize this quantity.
In the following example, the decision variables are in yellow, the constraints in light blue, and the objective function in dark blue.
Select each range and on the Formulas tab, in the Defined Names group, click Define Name:
| Range Name | Cells |
|---|---|
| UnitProfit | C4: E4 |
| OrderSize | C12: E12 |
| ResourcesUsed | G7: G8 |
| ResourcesAvailable | I7: I8 |
| Total Profit | I12 |
Insert the following SUMPRODUCT function (in order to create constraints and objective functions):
Solve the model
To find the optimal solution, perform the following steps (you must adapt each step to your model).
- On the Data tab, in the Analysis group, click Solver.
- Enter the solver parameters.
- You have the choice of entering the range names or clicking on the cells in the worksheet. Enter the total profit for the goal.
- Click on Max.
- Enter the direction of inequalities for the variables
- Add the constraints according to this process
- Check the box "Define non-negative unconstrained variables" and select "Simplex LP".
- Click on Solve
The solver fills the table with an optimal solution.
