Afia Kona
Linear optimization is one of the key topics of statistics. We can perform predictive analysis using the variables’ available data. In the following article, with easy steps, we will show to you how you can solve a linear optimization model in Excel.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
What Is Linear Optimization?
In a mathematical model, linear optimization is a technique to get the optimal result, such as the highest profit or the lowest price. The requirements for linear optimization are represented by linear relationships. All types of linear optimization models include some main factors. They are given below:
- Decision Variables: We determine the decision variables that minimize or maximize the objective function.
- Objective Function: This is a function that helps us to determine the decision variables. It expresses the relation between the result and the variables.
- Constraints: Constraints are also functions that denote different conditions on possible solutions.
4 Easy Steps to Solve Linear Optimization Model in Excel
Suppose, we have two types of machine, Machine A and Machine B. The Profit Per Unit of Machine A is $8 and for Machine B is $10. Here, Time to Get Raw Materials for Machine A is 2 hours, and for Machine B is 4 hours. Along with that, Time for Making Machine A is 4 hours, and Machine B is 2 hours. If we have a total of 72 hours to Get Raw Materials and a total of 48 hours for making, then what are the maximum number of Machine A and Machine B that can be produced to make the maximum profit?
In the following article, we will describe 4 easy steps to solve the above linear optimization models in Excel. Here, we used Excel 365. You can use any available Excel version.
Step-1: Analyzing the Question
Here, according to the given problem, we have made the following dataset. Next, we will find the optimum Machine A number in cell C5, and the optimum Machine B number in cell D5.
Along with that, we will find the maximum Total Profit in cell E7.
Now, let us describe the problem thoroughly. We have presented the following data table for this.
Decision Variables:
- We take X as the number of Machine A to make.
- We take Y as the number of Machine B to make.
- The question asks us to find out the maximum number of X and Y.
Note: Here, we display X and Y in the dataset so that you understand the problem. However, while performing calculations, we will get rid of these variables and will keep the cell empty.
Objective Function:
- Here, we have to maximize the Total Profit.
- Therefore, Objective function Z= (unit cost for Machine A times X)+(unit cost for Machine B times Y).
- That can be written as Z=(8*X)+(10*Y).
Constraints:
- Here, we have 2 constraints.
- For the first constraint, we can write P=(2*X)+(4*Y) in cell E10.
- And for the second contrast, we can write Q=(4*X)+(2*Y) in cell E11.
- Next, we will use the Solver tool to find out X, Y, Z, P, and Q.
Also, we will show you the actual numerical calculation in the following article.
Read More: Excel Optimization with Constraints (3 Case Scenarios)
Step-2: Adding Solver Tool
By default, the Solver tool is not enabled in Excel. Therefore, we have to enable the Solver tool to solve the linear optimization model in Excel.
- In the beginning, we will go to the File tab.
- After that, select Options.
- At this point, an Add-ins dialog box will appear.
- Then, from Add-ins >> select Go.
- Then, we will mark Solver Add-ins >> click OK.
- Therefore, you can see the Solver tool in the Data tab.
Read More: How to Make Price Optimization Models in Excel (with Easy Steps)
Step-3: Setting Formula in Optimization Model
In this step, we will set formulas in the optimization model. We have already discussed these formulas in Step-1. Now, we will show you the actual calculation of the formulas.
- In the beginning, we will type the following formula in cell E7 to set the Objective.
=($C$5*C7)+($D$5*D7)- This formula simply makes a relation between the Number to Make and Cost Per Unit.
- After that, press ENTER.
- Since cells C5 and D5 have no values, the formula returns 0 in cell E7.
- However, after using Solver, we will get a definite value in cell E7.
- Next, we will set the formula for the Constraints.
- Afterward, we will type the following formula in cell E10.
=($C$5*C10)+($D$5*D10)- This formula simply creates a relation between the Number to Make and Time to Get Raw Materials (hour).
- After that, press ENTER.
- Therefore, you can see the result in cell E10.
- Further, we will drag down the formula with a Fill Handle tool.
- Therefore, you can see cells E10:E11 has 0 values in them.
- Further, we will use the Solver tool to find out the values in these cells.
Step-4: Using Solver Tool to Solve Linear Optimization Model
In this step, we will use the Solver tool to solve the linear optimization model.
- In the first place, we will go to the Data Tab >> select Solver.
- At this point, a Solver Parameters dialog box will appear.
- Then, in the Set Objective box, we will select cell E7.
- Afterward, since we want to maximize the profit, we will select Max.
- After that, in the By Changing Variables Cells box >> we will select cells C5:D5.
- Along with that, we will click on Add.
- At this moment, an Add Constraint dialog box will pop up.
- Then, select cells C5:D5 in the Cell Reference box.
- Along with that, we will select >= in the next box.
- Moreover, in the Constraint box >> select 0.
- This is because none of the variables are less than 0.
- Furthermore, click on Add.
- This will also bring an Add Constraint dialog box.
- Then, we will select cells E5:E11 in the Cell Reference box.
- Along with that, we will select <= in the next box.
- Moreover, in the Constraint box >> we will select cells G10:G11.
- Afterward, click OK.
- At this point, make sure to mark the Make Unconstrained Variables Non-Negative box.
- Furthermore, from the Select a Solving Methods >> select Simplex LP.
- Additionally, click Solve.
- Then, a Solver Results dialog box will pop up.
- Here, make sure to mark the Keep Solver Solution box.
- Then, click OK.
- Therefore, you can see the optimum Machine A number in cell C5, and the optimum Machine B number in cell D5.
- Along with that, you can see the maximum Total Profit in cell E7.
Things to Remember
- The “Solver” feature is not enabled by default. Therefore, we must enable it to perform the task.
- We must set the objective cell.
- We must set the constraints to perform the task.
Practice Section
You can download the above Excel file to practice the explained method.
Conclusion
Here, we tried to show you 4 easy steps for a linear optimization model in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
