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.