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.
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:
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
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
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:
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 (1st 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
- 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
- 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
- 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)
Excel Master Series Blog Directory
You Will Become an Excel Statistical Master!