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

Click Options

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

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

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

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.

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.

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)

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

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

Now set your Changing Variable Cells

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

Place our constraint cells in this box.

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

Click Solve

Solver found a solution — Yay

Check – Keep Solver Solution

Hit okay

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

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”

Click Ok and run Solver again.

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