Using simple Linear Optimization in MS EXCEL to help make business decisions (PART 2)
In the last article, I used a simple linear optimization of demand and revenue using the Solverfunction in Excel. To revise, you can find the link here.
This article will be an extension of the last one. I will use the same example but with a few new constraints. The difficulty of this example, in understanding the concepts and performing in Excel should be slightly more challenging than the last example. However, the fundaments will be the same.
In the last article, we used linear optimization for revenue maximization. The problem was simple, maximize the revenue of a concert event by selling tickets at two different price levels. The constraints of the problem were venue capacity, respective demand for the ticket at the given price, and non-negativity.
Now, let’s notch up a bit. Suppose that the town council decided to purchase a few tickets for themselves. The council has asked for at least 350 seats. The council will buy each ticket at 1800 but has asked you to ensure they get seats closer to the stage and have separate space from the rest of the crowd. They have also instructed that you anyhow reserve 500 regular tickets for the university students and reduce the premium fee by 100. At 900, you expect demand to go up to 2700.
The council’s decision is going to impact your optimization. You have to make a new space. You have decided that you will make space for 350–700 seats for the council’s people. The demand for the ticket should be a total of 700, including the 350 reserved seats. However, segregating a separate space for the council’s ticket has some added difficulty. Every added space will result in the loss of either two premium or two regular seats, as you will have to adjust from the back. And remember, you must make space for at least 500 regular seats.
Now, you have to handle this new complexity. We will make linear functions similar to the last one, updating the new constraints. Before moving forward, let us write down in model in simple English and make a mathematical form.
Equation in English
Objective: Maximize Revenue from the three types of seat
Constrains:
Premium seats must be less than or equal to the demand
Regular seats must be less than or equal to the demand and more than or equal to 500
Council seats must be less than or equal to the demand and more than the reserved seat of 350
Total tickets will be 2650, after deducting 500 for regular and 350 for council
A council ticket will reduce 2 regular tickets or 2 premium tickets (of course wesacrifice the regular one)
The equationin Mathematical form
Objective: Z= max(900*P+400*R+1800*C) C= council seats
Constraints: P<=2500; R => 500 and R<=5000; C=>350 and C<=700
C+R+P = 2650, 4C+R(or P) <= 2650, so R <= 2650 and C<=662 (we did this manually here, but later on we can assign this to the solver as well)
Now, let us set up these variables in excel. Can you do the math for this problem without working it out on paper or in software? If you can, good for you. I cannot.
Find the Excel sheet here: https://github.com/AlabhyaMe/LinearProgram
Step 1: Outline Values
Like in the previous example, we list demand and price and input the formula for the objective function. Like before, we leave the sales column empty to be solved by the Solver and use sumproduct(price column, sales column) formula(function) for revenue on the objective cell.
Step 2: Outline Constrains Table
Now we make a constraint table and list down all the constrain values. Here, all the values on the left-hand side should be redirected to the Sales column from above. Based on my sheet, all council values are redirected from E7, regular from E6 ,and Premium from E5 .
Step 3: Add the constraint values
We will have to add the values on the constraint columns based on our model. We have some constraints whose numerical values we will manually input and others we borrow from the above cells.
Step 4: Feeding Values to the Solver
Now, we input the variables to the solver function. The solver function is found in the data tab of Excel. Here, first, click on the objective box and input the objective cell. On the second line, check Max since this is a maximization function.
Based on the input we made for constraints, we will add the constraints as well. The first constraints will look like this-
Step 5: Finding the value.
After all the input is inserted, hit Solve . Excel will provide you with the solution.
From the solver, you will get the result as shown in the picture above. The maximum possible output is 2,900,000, with sales of 1,676 premium tickets, 500 regular tickets, and 662 council tickets.
From the result, you can see that we should have 662 council tickets, the maximum sellable, as it brings the most money to the program. You can also see that we sold just 500 regular tickets, the bare minimum that was required, as these tickets do not bring much income. All the solutions are automatically updated from the solver.
Just that simply, you solved yet another linear optimization.
From this example, I hope you understand how linear optimization can be done in Excel and how easy it can be to find the solution. Note that this example may be mathematically more complex than the last one, but is still a very basic example. In the next article, I will provide an example with more challenging constraints and will be in a different format than the one in this example.
