Solver in Excel
Excel provides a powerful tool called Solver, which applies optimization techniques from operations research to help solve various decision-making problems.
Loading the Solver Add-in
Loading the Solver add-in is quite easy; follow these steps:
1. From the File tab, click on Options.
2. Under Add-ins, select Solver Add-in, then click Go.
3. Check the box for Solver Add-in and click OK.
4. Once enabled, you will find Solver in the Data tab under the Analyze group.
Formulating the Model
The problem we aim to solve in Excel is structured as follows:
1. To build this linear programming model, answer these three key questions:
a) What decisions need to be made?
- Excel should determine the quantity of bicycles, mopeds, and child seats to order.
b) What are the constraints on these decisions?
- The capital and storage availability limit how many units can be ordered.
- Example: Each bicycle requires 300 units of capital and 0.5 units of storage.
c) What is the objective measure of performance?
- The goal is to maximize total profit from selling these three products.
2. To enhance clarity, define named ranges for key values in the model.
| Range Name | Cells |
| UnitProfit | C4:E4 |
| OrderSize | C12:E12 |
| ResourcesUsed | G7:G8 |
| ResourcesAvailable | I7:I8 |
| TotalProfit | I12 |
3. Use the SUMPRODUCT function to calculate:
-
- Capital used = SUMPRODUCT(C7:E7, OrderSize)
- Storage used = SUMPRODUCT(C8:E8, OrderSize)
- Total Profit = SUMPRODUCT(UnitProfit, OrderSize)
Trial and Error Approach
With the formulated model, we can test potential solutions.
For instance, if we order 20 bicycles, 40 mopeds, and 100 child seats, the total resource usage remains within limits, yielding a total profit of $19,000.
However, manual testing is unnecessary—Solver can efficiently determine the optimal solution automatically.
Solve the Model Using Solver
To identify the best solution, follow these steps:
1. Go to the Data tab and click Solver under the Analyze group.
2. In the Solver Parameters window:
-
- Set TotalProfit as the Objective.
- Select Max to maximize profit.
- Define OrderSize as the Changing Variable Cells.
3. Click Add to input constraints.
4. Ensure ‘Make Unconstrained Variables Non-Negative’ is checked.
5. Now select the solving method as ‘Simplex LP’
6. Click Solve.
Result
The optimal solution found by Solver is:
✅ 94 bicycles and 54 mopeds should be ordered.
✅ This yields the maximum profit of $25,600.
✅ The solution utilizes all available resources efficiently.
Try it yourself! Download the Excel file and experiment with Solver to find the best outcome.
✅ 1/8 Completed! Learn more about Solver ➝
Next Chapter: Analysis ToolPak