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

 

Advertisements

3 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

  2. Gregg Johns

    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

    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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s