We will be solving practical challenges through MBA concepts. No theory only applications !
In this blog, we will discuss linear programming on Microsoft Excel to maximize objective function (usually profitability) with constrained resources . It is one of the most fancy and easy to use management tools typically used both by management consultants, entrepreneurs and operations lead.
Interestingly, Rajat Gupta (former Global Head, Mckinsey & Co.) used this extensively during his early Mckinsey days as an Associate to solve operations problems. He shared many such insights during our Management Consulting & Sustainability Masterclass at Global Governance Initiative.
Moving on, lets get into the problem solving mode.
Step 1:
I will run this lesson in a case method. In a retailer setup, the problem data is :
- 100 cases of orange juice glasses require 6 hours of production
- 100 cases of apple juice glasses require 5 hours of production
- Total 60 hours of production capacity is available per week
- The contribution of orange juice is USD 5 per case and apple juice is USD 4.5 per case
- Market demand is capped at 800 cases of orange juice glasses while apple juice can be sold unlimited
Question: How many glasses of each of juice should be produced per week in order to maximize contribution ?
Step 2: To build a linear optimization model, we need to determine-
Decision variables: X1: Number of orange juice glasses | X2 : Number of apple juice glasses
Objective Function: Max (500*X1+ 450*X2)
Constraints: 6X1+5X2< =60 | X1<=8
To find the answer, we can solve above equations
Step 3: Using Excel solver for linear optimization
This is an overly simplistic problem to convey the idea. However practically there will be more than 2 decision variables and multiple constraints.
Here, solver comes handy which will quickly do the math for you. It is an add-on for MS Excel. Frankly Im a huge fan of solver and I use it periodically to solve. Rather than explaining how to go about it step by step, I find this 5 minutes youtube tutorial super useful for those interested.
With this lesson, you will be able to always maximize the objective in your department/function/organization by ope-rationalizing resources in more efficient format. While this a massive complex topic which is dreaded by all ISB students, we have only touched tip of the iceberg which in my view is sufficient for practical purposes :)
If you liked this post, please click that heart icon to show it. I really appreciate it.
If you are new here and wish to know the story how MBA in 2 minutes was started, you can click here.