Maximizing Lead Generation With Excel Solver

Generating a Required

Number of Qualified Leads

Using Inbound Marketing

As Cheaply As Possible

The Solver has always been a widely used tool to allocate an advertising budget among outbound broadcast media vehicles of varying Reach, Frequency, and Cost. The Solver can also be used just as effectively to allocate a marketing budget among inbound Internet marketing vehicles.

In this problem, a pay-per-click marketing budget will be divided up among 3 pay-per-click advertising vehicles in order to generate the highest number of qualified leads. Each of the 3 different pay-per-click vehicles has a different cost and effectiveness per click.

 

The Problem

An overall Inbound Marketing budget must be divided among 3 pay-per-click vehicles in order to achieve the highest number of qualified leads. In addition to an overall budget limit, each pay-per-click vehicle has an advertising spending limit as well.

The 3 pay-per-click vehicles are AdWords, Facebook, and LinkedIn. Each one of these has a different cost-per-click and also has a different effectiveness. The objective is to divide the overall Inbound Marketing advertising budget among these 3 pay-per-click vehicles to achieve the highest number of qualified leads.

Following are the specific details about the cost and effectiveness of each of the 3 pay-per-click advertising vehicles:

 (Click On Image To See a Larger Version)

 

Problem Solving Steps

 

Excel Solver Step 1 – Determine the Objective

In this case, the objective is to maximize the total number of qualified leads. The total number of leads is therefore the Objective. The cell that calculates that total number of Qualified Leads obtained is the Objective Cell.

 

Excel Solver Step 2 – Determine the Decision Variables

We are trying to determine how much money to spend on each pay-per-click vehicle in order to maximize the total number of qualified leads while not exceeding the given budget constraints. The Decision Variables are the amounts of money to spend on each pay-per-click vehicle.

 

Excel Solver Step 3 – Build the Excel Equations That Combine the Objective With All Decision Variables

 (Click On Image To See a Larger Version)

The yellow Objective cell (I14) displays the total number of number of expected qualified leads. The green Decision Variable cells (C9 to C11) display the amounts of money (ad spend) spent on each of the pay-per-click vehicles to achieve the objective.

 

Excel Solver Step 4 – List All Constraints

 (Click On Image To See a Larger Version)

The preceding light blue Constraint cells link to the model to limit total ad spend and ad spend for each pay-per-click vehicle.

 

Excel Solver Step 5 – Test the Excel Spreadsheet

Test the Excel spreadsheet completely before adding information to the Solver dialogue box. Make sure that any changes to green Decision Variables produce the correct results in the yellow Objective cell.

 

Excel Solver Step 6 – Insert All Data into the Solver Dialogue Box

Input the Objective cell, Decision Variable cell, and all Constraints into the Solver dialogue box as follows:

 (Click On Image To See a Larger Version)

Integer Constraints ensure that ad spend is kept in whole dollar amounts. Inequality Constraints ensure that ad budgets are not exceeded.

All equations on the Excel spreadsheet are linear (1^st order) so we can use the Simplex LP (Linear Programming) Solver engine for this optimization problem.

Step 3 shows the completed problem with Decision Variables that have been optimized by the Solver to maximize the Objective while staying within the problem’s Constraints.

 

Excel Solver Answer Report

Part 1

Note:

- The Solver Result

- How long Solver took to solve the problem

- The Solver Engine that was used and the Solver Options settings

- Where the Objective Cell was labeled in the Excel model for its name to appear as it does in Part 1 of the Answer Report

 (Click On Image To See a Larger Version)

Part 2

- Note that the Variable Cells contain the Decision Variables

- Note where the labels for each Decision Variable are placed in the Excel model so that the Decision Variable’s name will appear here in Part 2 of the Answer Report as it does

- Note the type of variable - Either Continuous or Integer (Integer, Binary, or Alldifferent)

- Note the Before and After values of each Decision Variable

 (Click On Image To See a Larger Version)

Part 3

- Note how each Constraint is labeled in the Excel model in order for the Constraint’s name to appear here in Part 3 of the Answer Report as it does

- Note which Constraints are binding (had their limits hit) and which aren’t.

- Note how much slack is still available in any Constraint that has not had its limit hit.

- Note any Integer Constraints (Integer, Binary, Alldifferent)

 (Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!