Advertisements

Excel SOLVER Add in with Examples

Managers of companies want to find the optimum solutions to practical problems. They want to find certain values for a cell that optimize particular objective. To provide hands on solutions to this kind of optimization problems, Microsoft has introduced Excel Solver Add in, which helps to find optimal value for a formula subject to constraints, or limits on the values of other formula cells on a worksheet.

In this section:

Advertisements

  1. What is Excel Solver?
  2. How to install SOLVER Add in?
  3. Solving Optimization Problems without Constraints.
  4. Solving Optimization Problems with Constraints.
  5. Solving Budgeting Problems with Constraints.
  6. Practical Exercise.


Excel SOLVER for budgeting, Excel SOLVER Add in for Optimization Problems.

1. What is Excel SOLVER?

To perform what-if analysis Excel has introduced an add-in, which is called SOLVER. Solver is used to find optimal (maximum or minimum) value for a formula in one cell, the objective cell, subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result users want for the objective cell.

2. How to Install SOLVER?

Your first job is to check whether you already have the SOLVE installed in your Excel. To do so, you need to follow the steps below:
Step 1: Click on Data Tab

Excel SOLVER, SOLVER, optimization

Step 2: Took at the top-right corner and check whether you can see the Solver (as shown in the above image)

After checking the status of the SOLVER, if you do not see it on the top-right corner, you are required to install the add-in. To install, you need to follow the steps below:

Step 1: Click on File Tab–Then click on Option. Now, you can see the Excel Options dialogue box.

On the dialogue box, (1) click on add-in –> (2) click on Go–> (3) Check Solver Add-in, and –> (4) Click OK.

3. Solving Optimization Problems without Constraints:

The Problem: We have the following data related to university student admission, tuition revenue, cost per student, and gross income. We want to earn total $5,000,000 gross income. What is the best solution?

Advertisements

Solutions: To solve this problem, we need to follow the steps below:

Step 1: Start the SOLVER. To do so, click on Data Tab–> Click on Solver. After clicking on Solver, you will see the Solver Parameters as below.

Step 2: Set the parameters:

Explanation:

  1. Set the value: In our example, we want to earn total gross income of $5,000,000. We checked value of and wrote the amount of $5,000,000 in the box.
  2. Set Objective: This is the cell reference of your value, which is D13. We select D13.
  3. Changing Variable Cells: We want to change the number of students admitted and the tuition per student, and the cell reference is D5 and D6. We selected D5:D6.
  4. Solve: Now, we click on “Solve“.

After clicking on “Solve“, the Solver Results dialogue box will be opened, and you have two options:

Excel SOLVER, Solve optimization Problem.
  1. OK: If you like the solutions, you should click on “OK“, OR
  2. If you want to change something, you can click on “Cancel“.

In our example, for now, we like the solution, and we click OK. The results are presented below:

Findings from the solutions:

  1. Students Admitted: The SOLVER suggests that we need to admit 587.76 students. The problem in the number is that number of students CANNOT be fraction. We need an integer.
  2. Tuition per student: Solver’s suggestion is to increase the tuition fee to $10,006.81. However, the potential problem in this solution is that we may need to decrease tuition fee if we want to admit more students.

4. Optimization Problems with Constraints:

Let’s continue with our problem. Suppose, we want to include three constrains, namely:

  1. Number of students cannot be fraction–it should be an integer.
  2. Tuition fee should be less than $9,000.
  3. Cost per student should not be more than $1,450.

Solution:

The steps 1 and 2 are same as they are in problem 1 solved above. We need to add constraint in the step 3. To do so, we need to click on “Add” on the right side of “Subject to the Constraints“. [See image below]

Setting the Constraints:

Advertisements

After clicking on “Add“, the Add Constraint dialogue box will be opened. Let’s see an example.

The first constraint: We want the number of students should not be a fraction. We have given:

Excel SOLVER, Revenue Optimization with Excel SOLVER
Constraints given

After setting the constraints, we click SOLVE, and the Solve has given the following solutions with a warning!!

The tentative Solution
Warning!!

What next?

We found two issues: (1) number of students is still a fraction, (2) cost per student is still $1,500. We can reset the constraints to find an optimum solution.

5. Solving Budgeting Problems with Constraints:

The Problem:

The department of Accounting at the University of Texas is planning budget for six accounting courses. The total budget for the six courses offered in Fall-2022 is $50,000. The administration wants to allocate the total budget of $50,000 to all the courses. The expected instructors’ remuneration, utility cost, depreciation of equipment, total cost per course, and potential constraints are given in the following tables.

Excel Solver, Solving Budgeting Problem in Excel, Allocating budget in Excel Solver, Linear Programming in Excel Solver.

You are required to:

  1. Use Excel SOLVER to allocate the total $50,000 to all the courses.
  2. While solving this problem, you have to consider the following constraints:
    • Total budget should not be more than $50,000.
    • Depreciation should not be more than $100.
    • Minimum utility cost per course is $200.
    • Total cost for a course should not exceed $9,000.

Solution:

Step 1: Start the Excel Solver:

Click on Data Tab–> Then click on Solver.

Advertisements
Start Excel Solver.

After opening the Solver Parameters dialogue box,

Step 2: Set Objective:

The target cell is G12, which should not exceed the value of $50,000. We therefore, select G12 (1 in image) and set the value of $50,000 (2 in image).

Step 3: Set the Changing variables:

We can change:

(I) Instructors’ remuneration in cell D6:D11 (we select D6:D11 as 3 in image below)

(II) Utility cost in cell E6:E11 ( we select E6:E11 as 3 in image below)

Excel Solver, Solving budgeting problem in Excel, Allocating budget in Excel, Capital Budgeting in Excel.

Step 4: Setting constraints:

I) Depreciation should not be more than $100, indicating that F6:611<=100.

II) Utility cost should be more than $200, indicating that E6:E11>=$200.

III) Total cost of a course should not be more than $9,000, indicating that G6:G11<= G18

Step 5: Click on Solve.

Advertisements

Then you can see the solver results dialogue box as follows:

You can check whether you will go with the solutions. We can see in the following image that all constraints have been satisfied. You just click OK and keep the solver solutions.

Exercise:

Optimal Product Mix:

Maxwell Technology produces headphones, speakers, and pen drives. Maxwell’s total monthly available hours is 8,000 whereas monthly production hour is 1,000. The profit, labor, time, and monthly demand information are given in the following table. Considering monthly demand constraints for each product, how can Robert Maxwell his profit?

You are required to:

  1. Solve the profit maximization problem considering the following constraints:
Constraints   
Available labor hours8,000
Available monthly
production hour
1,000
Demand should not be exceeded.   
  1. Before running the SOLVER, prepare a projected solutions as follows:
 HeadphonesSpeakersPen Drives  Totals
Monthly production unit9,0005,5006,900  21,400
Monthly Profit$90,000$44,825$36,570$171,395
Labor hours3,6002,4751,3807,455
Production hours315247.5241.5  804

More resources:

Microsoft Official Post

CONCAT Function

Arraytotext function

13 Responses

  1. I’ve recently started a website, the info you provide on this web site has helped me greatly. Thanks for all of your time & work. “The very ink with which history is written is merely fluid prejudice.” by Mark Twain.

  2. I have been browsing online greater than 3 hours lately, but I by no means discovered any interesting article like yours. It’s lovely worth enough for me. In my opinion, if all website owners and bloggers made just right content material as you probably did, the internet shall be a lot more helpful than ever before.

  3. I have been browsing online more than 3 hours lately, but I by no means discovered any attention-grabbing article like yours. It’s beautiful price sufficient for me. Personally, if all webmasters and bloggers made just right content as you probably did, the net will probably be much more useful than ever before. “A winner never whines.” by Paul Brown.

  4. I’m really loving the theme/design of your web site. Do you ever run into any internet browser compatibility issues? A few of my blog visitors have complained about my website not operating correctly in Explorer but looks great in Opera. Do you have any solutions to help fix this problem?

  5. You could definitely see your skills in the work you write. The world hopes for more passionate writers like you who are not afraid to say how they believe. Always follow your heart.

Leave a Reply

Your email address will not be published. Required fields are marked *

Advertisements