Let’s build a more complex model. Here is our problem:
You own a cabinet company and you are currently making 2 types of cabinets this month: wall and base. The wall cabinet sells for $300 and the base sells for $450. The wall unit cost $150 in labor to build, while the base cabinet costs $225.
The cabinets are built from a combination of plywood (which cost $11 per sq foot) and oak (which costs $15 per sq foot). The wall unit needs 5 sq ft of plywood and 1 sq ft of oak. The base unit needs 6 sq ft of plywood and 2 sq ft of oak.
You bought your materials ahead of time to get the best rate. You have 10000 sq ft of plywood and 3000 sq ft of oak available. Finally, based on previous sales records, you estimate the most you will be able to sell is 600 wall cabinets and 1200 base cabinets this month.
So you want to know – how many of each cabinet should I build to maximize my profit?
Let’s Model It
Okay, I know. This just gave you horrible flash backs to math class and word problems. But the truth is, this is what your math class was trying to prepare you for. We are going to use math to create a model that mimics the real world and solves a problem.
Open up Excel and let’s start.
***Note: I suggest you try building this model yourself first. If you need help, just follow my steps below, but only follow me as needed.
Let’s start by inputting some of the information we know:
Prefilled Excel for Known Info: cabinet1
Price of materials:
Amount of Available Materials
Now add what we know about building the cabinets
Finally, input your build limits
Here is our model so far
In this model, our changing variable is how many of each cabinet we will build.
Next, let’s set up out Objective. In our model, the object will be total profit.
Now you need to add the calculations that will make your model work.
Excel File with Calculations: cabinet_with_equations
First let’s calculate material use. I will use SUMPRODUCT() to do this. In the example below, I am multiplying the number of cabinet types built(changing cells F9:G9) by the material requirements (plywood B7:C7)
Then repeat for oak.
Now calculate profit for each cabinet.
Here is how I did it. Selling Price – Labor Cost – (plywood cost per sq ft * sq ft used + oak price per sq ft * ft used)
I repeated for Floor cabinets
Finally I am calculating the final profit. This is done by using Sumproduct() again. This time it is sumproduct(Profit, Actual Build)
Click on Data and Solver in Ribbon up top.
Now set the Objective to you Profit Cell (A15), and the Change Variables to your Build cells (PINK – F9:G9). Click Max and set the Solving Method to Simplex LP
Hit Add to the right of the Constraints window and add your constraints
Looking at your spreadsheet, your constraints below state
- plywood used <= plywood available
- oak used <= oak available
- Wall built <= max Wall Build
- Floor built <= max Floor Build
Now Hit Solve
Check Keep Solver Solution:
Now you have a solution. According to Solver, you should build 560 Wall and 1200 Floor cabinets to maximize your profit.
3 thoughts on “Excel Solver: Optimization Models: Linear Programming 1”
It’s a pity you don’t have a donate button! I’d certainly donate to this
excellent blog! I guess for now i’ll settle for bookmarking and adding your RSS feed to my Google account.
I look forward to fresh updates and will talk about this blog with my Facebook group.
Hello there analytics4all.org
SEO Link building is a process that requires a lot of time.
If you aren’t using SEO software then you will know the amount of work load involved in creating accounts, confirming emails and submitting your contents to thousands of websites in proper time and completely automated.
With THIS SOFTWARE the link submission process will be the easiest task and completely automated, you will be able to build unlimited number of links and increase traffic to your websites which will lead to a higher number of customers and much more sales for you.
With the best user interface ever, you just need to have simple software knowledge and you will easily be able to make your own SEO link building campaigns.
The best SEO software you will ever own, and we can confidently say that there is no other software on the market that can compete with such intelligent and fully automatic features.
The friendly user interface, smart tools and the simplicity of the tasks are making THIS SOFTWARE the best tool on the market.
IF YOU’RE INTERESTED, CONTACT ME ==> MoneyRobotSubmitter@mail.com
Regards, Gregg Johns
Italy, VA, Azzio, 21030, Via Sacchi 31