Excel Solver: Optimization Models: Linear Programming 1

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:

lp1.jpg

Amount of Available Materials

lp2

Now add what we know about building the cabinets

lp3.jpg

Finally, input your build limits

lp4

Here is our model so far

lp5.jpg

Changing Variable

In this model, our changing variable is how many of each cabinet we will build.

lp6

Objective

Next, let’s set up out Objective. In our model, the object will be total profit.

lp67.jpg

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.

lp8

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

lp9.jpg

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

lp11

Constraints

Hit Add to the right of the Constraints window and add your constraints

lp12

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

lp13.jpg

Check Keep Solver Solution:

Hit OK

lp14.jpg

Solution

Now you have a solution. According to Solver, you should build 560 Wall and 1200 Floor cabinets to maximize your profit.

lp15.jpg

 

2 thoughts on “Excel Solver: Optimization Models: Linear Programming 1

  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.
    Chat soon!

    Like

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s