Excel Solver Exercises: 8 Advanced Problems

In this article, you will get eight exercises related to the Excel solver. These problems are quite difficult to solve. You should have an advanced level of Excel knowledge to solve the problems. To solve the problems, you should know about the following: the SUM, SUMPRODUCT, HLOOKUP, COUNTIF, IF, and OR functions and Enable solver, solver properties, solver example, choosing the best project, portfolio optimization with solver, solver for linear programming, usage of solver to minimize cost, assign work using an evolutionary solver, and schedule optimization. Excel 2010 or a higher version is required to solve all the exercises about Excel solver.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

There are eight exercises related to the solver in this file. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the problems worked out. Additionally, the solver settings for each exercise are provided in the “Reference” sheet. You can load the values from there into the solver for each problem. The following image shows the first problem of the article.

• Exercise 01 Minimize Shipping Cost: A company has warehouses in St. Louis, Los Angeles, and Boston. Six retail outlets are situated all over the United States. These retail outlets take orders from customers. The company then ships products from one of the warehouses. The company aims to supply all six retail locations with the products they require from the stock that is currently on hand in the warehouses. While shipping products to outlets, the company wants to keep shipping charges as low as possible.
  • The first table shows the shipping costs from one port to another. Then, the second table shows the product requirement for each port. After that, there is a table that contains the information about the warehouse inventory. Your task is to find the values using the solver that will minimize the shipping costs from the warehouses to the outlets.
  • Solution: Solver Parameters are as follows
    • Set Objective: G27
    • To: Min
    • By Changing Variable Cells: D15:F20
    • Subject to the Constraints: C15:C20=G15:G20, D25:F25>=0
    • Select a Solving Method: Simplex LP

The following animated image shows the solution to the first problem.

• Exercise 02 Minimize Production Cost: Mix different raw materials to produce different types of steel, such as regular, exclusive, and super quality steel. There is data about the availability and cost of these raw materials, as well as their quality rating. Additionally, establish the required amount of production, the price per ton of different classes of steels, and their minimum rating. The conditions are given in the dataset.
  • Solution: Solver Parameters are as follows
    • Set Objective: I38
    • To: Min
    • By Changing Variable Cells: C32:E34
    • Subject to the Constraints: C35:E35>=C37:E37, C41:E41>=C43:E43,F32:F34<=H32:H34
    • Select a Solving Method: Simplex LP
• Exercise 03 Maximize Profit of Products: Profit per unit is given for 17 products. Your task is to find the maximum profit using the solver.
  • Firstly, the combined production capacity is 300 units per day.
  • Secondly, the company needs 50 units of Product A to fill an existing order.
  • Thirdly, the company needs 40 units of Product B to fill an expected order.
  • Fourthly, the market for Product C is relatively limited. So the company is not interested in producing more than 40 units of this product per day. Additionally, the Product D to Q should be more or equal to 15.
  • Solution: Solver Parameters are as follows
    • Set Objective: E65
    • To: Max
    • By Changing Variable Cells: C48:C64
    • Subject to the Constraints: C48>=50, C49>=40, C50<=40, C51:C64>=15,C65=400
    • Select a Solving Method: Simplex LP
• Exercise 04 Marketing Budget Allocation: Here, the current stats on the left, and the portion where you are going to use the solver is on the right. Your task is to maximize the conversions (value of cell J83). The budget should be higher or equal to sixty thousands and the total solver budget and the total cost should be equal.
  • Solution: Solver Parameters are as follows
    • Set Objective: J83
    • To: Max
    • By Changing Variable Cells: H71:H82
    • Subject to the Constraints: H71:H82>=60000, H83=E83
    • Select a Solving Method: GRG Nonlinear
• Exercise 05 Schedule Optimization: The number of workers needed for a bank is provided. For example, 17 workers are needed on Monday, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. Moreover, all bank employees work five consecutive days. What is the minimum number of employees that this bank can have to meet its labor requirement?
  • The condition is, Total Number of Employee >= Number Needed is given in row 96 and 98.
  • Solution: Solver Parameters are as follows
    • Set Objective: C100
    • To: Min
    • By Changing Variable Cells: B88:B94
    • Subject to the Constraints: B88:B94=integer, D96:J96>=D98:J98
    • Select a Solving Method: Simplex LP
• Exercise 06 Project Selection: Use an excel solver to determine which projects should be undertaken in excel.
  • Firstly, find the maximum value of NPV.
  • Secondly, the year 1 value should be <=50,000,000 and year 2 value should be <=20,000,000.
  • Thirdly, selecting a project means 1 and discarding means 0. So use this.
  • Solution: Solver Parameters are as follows
    • Set Objective: C104
    • To: Max
    • By Changing Variable Cells: B107:B115
    • Subject to the Constraints: B107:B115=binary, C117:D117<=C118:D118
    • Select a Solving Method: Simplex LP
• Exercise 07 Investment Portfolio Optimization Based on Total Yield: The credit union is going to invest $5 million in various sectors. Find how this amount will be allocated using the following conditions.
  • Firstly, the amount that the credit union will invest in new-car loans must be at least three times the amount that the credit union will invest in used-car loans. The reason is: that used car loans are riskier investments.
  • Secondly, car loans should make up at least 15% of the portfolio.
  • Thirdly, unsecured loans should make up no more than 25% of the portfolio.
  • Then, at least 10% of the portfolio should be in bank CDs.
  • Afterward, the total amount invested is $5,000,000.
  • Finally, all investments should be positive or zero.
  • Solution: Solver Parameters are as follows
    • Set Objective: C132
    • To: Max
    • By Changing Variable Cells: D125:D129
    • Subject to the Constraints: D125>=D126*3, D130=C122, F128<=0.25, F129>=0.1, F132>=0.15
    • Select a Solving Method: GRG Nonlinear
• Exercise 08 Assign Department: In this last Excel solver exercises, you will need to assign 40 employees to four Departments. The head of each department has rated each employee’s competence on a 0 to 10 scale (10 equals most competent). Each employee has rated his satisfaction with each job department (again on a 0 to 10 scale). Data is recorded in the Problem worksheet. You are going to assign between 8 to 12 people to each department. The problem is how you can assign employees to workgroups to maximize total satisfaction and ensure that each division has the required number of employees.
  • Solution: Solver Parameters are as follows
    • Set Objective: P146
    • To: Max
    • By Changing Variable Cells: C140:C179
    • Subject to the Constraints: C140:C179<=4, C140:C179=integer, C140:C179>=1, Q141:Q144=0
    • Select a Solving Method: Evolutionary

Finally, the following image displays the solution to the first exercise.

Conclusion

Thank you for reading this article. By completing these Excel Solver exercises, we hope that you have gained knowledge about Excel. You can find more articles similar to this on the ExcelDemy website. Moreover, if you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!