Solver in MS Excel

Solver in MS Excel

Solver is part of what-if analysis tools in MS Excel. Solver provides you an optimal (max or min) possible values for a formula in one cell known as objective cell, subject to limits or constraints on the values placed by any other formula on a worksheet.

Solver works on Cells group, called Decision Variables that takes part in calculating the formulas in the objective and constraint cells. The values in decision variable cells are adjusted by Solver to satisfy the limits on constraint cells. It produces the desired result for the objective cell.

Loading Solver add-in MS Excel

Solver is not available by default and has to be included through add-ins. To add Solver, follow the steps below.

1. Go to the File Tab

2. Select Options from the Menu

3. Click on Add-ins

4. From the Add-ins pane, select “Excel Add-ins” in Manage drop down box and click Go button.

5. This will open Add-in pop-up box. Select Solver Add-in and click OK.

6. This will add Solver add-in under the Data Tab.

Formulate a Model

The data model for this tutorial is as follows:

1.         Before start using Solver, you need to put three questions onto yourself and get the answers.

a.         What decisions to be made?

In this scenario, you need MS Excel to find out what number of orders to be placed for each product (mobile, accessories and packages).

b.        What are the possible constraints for these decisions?

Constraints here are that the amount of capital and storage used by the products cannot exceed the limited amount of capital and storage (resources) available. For example, each mobile uses 600 units of capital and 1 unit of storage.

c.         What is the overall measure of performance for these decisions?

The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.

2.         To make the model simpler and easy to understand, name the following ranges. This tutorial will cover one example of setting up range for “Unit_Profit” for range C4:E4.

To set a range, follow the steps below:

• Select the cell range from C4 to E4

• Right click on selected range and select “Define Name” from the context menu

• Type the name for the range in New Name window and press OK.

• Repeat the same steps for defining other ranges.

3.         Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the SUMPRODUCT of the range C7:E7 and Order_Size. The amount of storage used equals the SUMPRODUCT of the range C8:E8 and Order_Size. Total_Profit equals the SUMPRODUCT of Unit_Profit and Order_Size.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if the order is placed for 60 mobiles, 100 accessories and 80 packages, the total amount of resources used, will not exceed the available amount of resources. This solution has a total profit of 80000.

Using trial and error is not necessary.

Solve the Model

To determine the optimum solution, follow the steps below.

1. On the Data tab, click Solver.

Enter the solver parameters (read on). The result should be consistent with the picture below.

2. Enter “Total_Profit” for the Objective.

3. Click “Max” in To: section

4. Enter “Order_Size” for the Changing Variable Cells.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

5. Click Add to enter the following constraint.

6. Check ‘Make Unconstrained Variables Non-Negative’ and select ‘Simplex LP’.

7. Finally, click Solve.

Result:

The optimum solution would be:

Conclusion: it is optimal to order 416 mobiles and 41 accessories. This solution gives the maximum profit of 108333. This solution utilizes all the available resources.