Using simple Linear Optimization in MS EXCEL to help make business decisions (PART 1)
Quantitative analytics has become of major importance for business decision-making. With an abundance of data readily available, firms not taking advantage of data will face a severe disadvantage against a data-centric competitor. One of the business analytics tools that make use of data and can improve the decision-making of a business is Linear Optimization. In this article, I will present how linear optimization can be performed in MS Exel for output maximization.
Linear optimization is also called linear programming. It is a technique for optimizing the outcome of a linear function with constraints. Refer to this link for more information on linear programming.
Linear programming in Excel is very intuitive. It will be automatically performed by a ‘Solver’ tool that is available in the workbook. However, it is important to feed proper value to the Solver. Like any computer function, linear optimization in Excel also follows the same GIGO — Garbage In Garbage Output philosophy, so it is essential that we feed the system with proper inputs.
Enable Solver in MS Excel
Before getting started with performing linear optimization in Excel, your Excel must have the Solver tool enabled. To enable Solver, go to the Option in Excel. Then from there, go to the Add-ins, select Solver Add-In , and at the bottom left, click Go… . Tick the “Solver Add-In” check -box and you will see a Solver option in the Data tab of Excel. Refer here for more help.
Now that we are all set, let’s get the real thing going. To make this article more engaging, I will provide an example of the step-wise run-through of linear optimization.
Suppose you are an event manager. Popular British band Glass Animals is giving a concert this summer at the university and your task is to maximize revenue from the event. You know that the students love Glass Animals but may not be able to afford to pay a high price for the concert. But you also know that university staff and other people nearby can pay a little extra for the event. So, being a money-hungry business person, you have decided to charge two prices on tickets- one ordinary ticket which most people can afford, and another a bit expensive ticket that fewer would demand but comes with the guarantee of the front row. Based on experience, you are pretty confident about the demand for the ticket at the two different price rates.
So, you decide to sell tickets at two different prices. The two prices will be NRs 1000 for a premium and NRs 400 for a regular ticket. Based on experience, the demand for a ticket at a premium rate is 2000, whereas the demand for a ticket at a regular rate is 5000. However, the venue has a capacity of 3500 only. You will have to allocate the seats in such a way that you generate the highest possible revenue.
The decision is easy, right? You sell 2000 tickets on premium and the remaining 1500 at the regular rate. If you are thinking there may be some GRE trick that you might have missed, I can assure you it is not. The problem is simple, but we are looking at simple examples like this because it allows us to get a better intuition of how linear optimization actually works. Understanding the concepts enables you to replicate the model in more challenging problems.
Before going to Excel, we note down maths for linear programming.
The first is the objective function. Remember your objective is to maximize revenue. Thus the objective function will be
Z = max (1000*P + 400*R); where P is the premium ticket and R is the regular ticket and (1000*P+400*R) is the revenue function.
You must have realized that we also have a few constraints. The most obvious is the number of tickets we can sell. The total capacity of the event is only 3500. Moreover, you won’t be able to sell more seats than what is demanded at the price. And ultimately, you cannot sell a negative amount of tickets. Thus we have the following constraints.
P+R <= 3500 (premium and regular tickets cannot exceed the demand)
P <= 2000 (demand for premium tickets)
R <= 5000 (demand for regular tickets)
P >=0 & R >= 0; (non negative)
In non-mathematical notation, the linear function can be written as
Maximize: Total concert revenue
Subjected to: Tickets cannot exceed capacity
Tickets cannot exceed demand
Tickets cannot be negative
Working in MS Excel
Find the Excel sheet here: https://github.com/AlabhyaMe/LinearProgram
Step 1: It is a good practice to list the variables in the Excel sheet before running the linear optimization model. First, for this exercise, you should list the demand and price in the Excel sheet like below.
Note that the sales tab is empty as we will let the solver decide it.
Step 2: Now, we will add the objective function. As the objective is to maximize revenue, our objective function will be the SUMPRODUCT() function. SUMPRODUCT() takes two column values and then multiplies the corresponding row values. Here, the price and the sales are the required columns.
Step 3: We now add constraints. We will list all the constraints in the Constrains Values column. Note that the solution values are empty because it is to be solved by the solver.
The Total Tickets constraint is 3500. Refer to the notation above to find the constraints for each cell.
It is also a good practice to use pull value whenever possible. What I mean by pulling values is rather than righting numerical values, I redirect the cells to the values above (See the image above). This way, we can reuse the function when the values changes. Also note that the inequalities sign is for general reference, and does not impact the solution.
Now, we will also fill in the values on the Solution Valuescolumn. Here, the Total Tickets is the sum of the sales of Premium and Regular tickets. I sum the value of the two cells. The Premium Demands and Regular Demands are the sales values that are yet to be decided. It is the number of tickets that we would like to sell under each category. Like before, I redirect the cell values (to E5 and E6 respectively based on the image above). The non-negative cells should also be linked to the same cells (E5 &E6).
Step 4: Before performing the linear optimization, your worksheet should look something like this. I have colored the cells that will be solved. The objective and the sales cell will be completed by the solver and the cells in the blue will be updated.
Step 5: Now we follow a series of steps to perform the linear optimization.
Open the solver from the Data tab.
The Solver will open a new box, which should be like this –
Click the objective cell to set the objective function. Also, since this is the maximum function, check the Max box on the second line. As we are trying to distribute the sales of the premium and the regular tickets, select the two cells on the field By Changing Variable Cells: Your solver should look something like this –
Finally, we add the constraints. Go to the Add in the Solver Parameters. Select the cells whose values we are finding on the left-hand side and the constraints on the right-hand side. Note, you can select multiple columns, but be careful with the inequalities. If inequalities changes, click Add and select the required columns in the new box. When everything is done, hit Ok.
Step 6: Click the solve button. You will be prompted to a dialogue box. Check the Keep Solver Solution. Now the results are updated in the worksheet.
This is the illustration of simple linear optimization in Excel. As we figured before, we sell 2500 premium and 1000 regular tickets. The maximum revenue is calculated in the Objective cell. As long as the premium tickets do not exceed the capacity, we are always better off selling the premium tickets.
Here we saw how linear optimization can be performed in MS Excel. The example was quite easy and we got the answer without even solving it in Excel. However, real-world problems are not always that simple. What if adding one extra premium seat means losing 3 regular seats? What would be the optimal solution in such cases? And as constraints add up, and when variables increase, the difficulty of linear optimization increases. Such problems become very difficult to calculate manually. In such cases, the ability to perform linear optimization in Excel becomes very handy.
In the next article, I shall extend the example with a more difficult problem. But the basics of doing linear optimization in Excel should be clear to you for now. Leave questions and compliments in the comments.
Find Part 2 in the link: https://alabhya.medium.com/using-simple-linear-optimization-in-ms-excel-to-help-make-business-decisions-part-2-782af2aec5f4
