Excel Solver: Intro Optimization Models: Linear

What is an optimization model? An optimization model is a mathematical model designed to provide the optimal solution based on a information provided. You can use it answer questions like the optimal number of employees needed to working a shift or the optimal amount a product to produce that will bring in the most profit.

In this lesson we will be using Excel Solver ( a free ad-on) to develop an optimization model. If you do not have Solver loaded already, follow the instructions below:

Click File Button

adOpt1.jpg

Click Options

adOpt3

Click Add-Ins in the left column, find Solver Add-in. Click Go at the bottom next to Manage: Excel Add-ins

adOpt4.jpg

Check the box next to Solver Add-in – Click Ok and go back to main Excel page

adOpt5

To check that it installed, go to Data on the Ribbon and check for Solver in the Analysis box

adOpt6.jpg

Get the Excel File

You can make the file yourself, or download the template here: AdOptModel

Let’s look at our file:

This is a very simple example to start with. I am looking to run advertisements on a local radio station. The station provides two ad packages A and B. Package A is promised to reach 69,000 potential customers per airing and costs $156 dollars. Package B will reach 79,000 and cost $173.

I want to know how many of each package to run to reach the most customers. My only constraint is that I have a solid budget of  $10,000 that I cannot exceed.

adOpt2.jpg

Now you do not have to use the colors, I just learn this way and it makes sense to me. To understand the coloring:

  • Blue = input cells – you need to provide this information to your model
  • Pink = changing cells. These are the boxes Solver is going to use to try to work the problem out – do not put any formula in these boxes
  • Gray = Result cell – you can only have one result cell.

Build the model

Now we are going to build the model. We need to add some calculations to our existing sheet for the model to work.

Step 1: This step is optional. I always place some number value in my changing cells just to make sure my formulas are inputted  correctly.

adOpt7.jpg

Step 2: Constraint. Our budget is 10000, so we need a cell that calculates how much we are spending based on values in our change cells (Number of each ad we plan on running). To calculate this we are going to use a formula call Sumproduct()

What we want to do is multiply (# of ads for A * cost per Ad A) then add that to (# of ads for B * cost per Ad B)

That is what Sumproduct gives us (B5*B8)+(C5*C8)

adOpt8.jpg

We are going to do the same thing for our Result cell, except this time it will be Number of Ads * Customer Reach

adOpt9.jpg

Use Solver

Now it is time to use Solver, click on solver in the Data Tab of the Ribbon bar (under data analysis).

Let’s walk through this slow. In the top box, Set Objective, set this to the Result Cell. You can type it in or just click on the cell.

In the To: line, select Max since we are looking for the maximum customer reach here

adOpt10.jpg

adOpt11.jpg

Now set your Changing Variable Cells

adOpt12.jpg

To the right of big white box in the middle of the window, select Add

Place our constraint cells in this box.

adOpt13.jpg

Finally, make sure you check Make Unconstrained Variables Non-Negative and set our Solving Method to Simplex LP

Click Solve

adOpt14.jpg

Solver found a solution — Yay

Check – Keep Solver Solution

Hit okay

adOpt15.jpg

Here is our answer. Running 57.80347 Package B ads will get us 4566.474 Customers Reached.

adOpt16.jpg

But how do you run 0.8 of an ad?

Well, this is where we add in an integer constraint.

Reopen solver. Hit the Add button next to constraints

In Cell Reference, put our changing cells.

From the drop down pick “int”

adOpt17.jpg

adOpt18.jpg

Click Ok and run Solver again.

Now look at our new results: 2 of Package A and 56 of Package B

adOpt19.jpg

 

 

 

 

 

 

 

2 thoughts on “Excel Solver: Intro Optimization Models: Linear

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