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:
Known Info
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
Changing Variable
In this model, our changing variable is how many of each cabinet we will build.
Objective
Next, let’s set up out Objective. In our model, the object will be total profit.
Calculations
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)
Run Solver:
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
Constraints
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:
Hit OK
Solution
Now you have a solution. According to Solver, you should build 560 Wall and 1200 Floor cabinets to maximize your profit.
