Friday, January 14, 2011

Using Excel Solver To Optimize Your Marketing Budget

Using the Excel Solver To

Optimize Your Marketing

Budget


The Excel Solver allows you to calculate how to allocate your resources to get an optimal result. It is such a useful and versatile tool that almost any manager can put it use effectively but not many are familiar with how to use it.

This article will provide a simple example of how an Internet marketing manager can use the Excel Solver to optimize the allocation of marketing budget funds among 4 pay-per-click networks to achieve the highest number of conversions (sales).


Solver Problem Summary

In summary, here’s what we are trying to do:

1) Maximize the total number of conversions from all 4 pay-per-click vendors.

2) Not spend more than $500 on each individual pay-per-click provider


3) Not spend more than $1,500 total


We are also provided with the average cost per click and average conversion rate (percentage of clicks that convert to sales) for each network. The information is placed on a spreadsheet as follows:

Click On the Image To See a Larger Version

Our Solver Optimization Task

We have a total monthly paid search budget of $1,500 that must be allocated between the 4 following paid search providers: Google AdWords Search Network, Google AdWords Content, Facebook paid search, and LinkedIn paid search. We want to keep our pay-per-click efforts diversified; we will spend no more than $500 per month any single pay-per-click providers, but no more than $1500 total.

We know our average cost-per-click and conversion rate for each of the 4 pay-per-click vendors. Conversion rate is the percentage of clicks that convert (buy and become customers).


Running the Problem on the Excel Solver

Once the Solver has been installed as an add-in (To add-in Solver: File / Options / Add-Ins / Manage / Excel Add-Ins / Go / Solver Add-In), you can access the Solver in Excel 2010 by: Data / Solver.

The following blank Solver dialogue box appears:

Click on Image To See Larger Version

The Solver dialogue box has the following 5 parameters that need to be set:

1) The Objective Cell – The is the target cell that we are either trying to maximize, minimize, or achieve a certain value.

2) Whether we want to minimize or maximize the target, or attempt to achieve a certain value in the Objective cell.

3) Decision Variables – A set of variables that will be changed by the Excel Solver in order to optimize the target cell.

4) Constraints – These are the limitations that the problem subjects the Solver to during its calculations

5) Solving Method to be used.
Here once again is the Excel spreadsheet input data that will be used:


Click On Image To See Larger Version


Objective:

Maximize the total number of conversions from all 4 pay-per-click vendors (green cell H10)


Decision Variables:

By varying the budget amounts allocated to each of the 4 pay-per-click networks (tan cells – C6 to F6)
 
Objective and Decision Variables Selected in Solver


Click on Image To See Larger Version


Choice of Solver Method

Click on Image To See Larger Version

a1x1 + a2x2 + … + anxn = objective

We can tell that we are dealing entirely with linear equations in this example because all equations which used to determine the objective (the final number of conversions) are first-order. These basic equations are as follows:

Number of Clicks = (Average Cost Per Click) * (Amount Spent)

Number of Conversions = (Average Conversion Rate) * (Number of Clicks)

The GRG Nonlinear method is used when the equation producing the objective is not linear but is smooth (continuous). Examples of smooth nonlinear functions are:
=1/C1, =Log(C1), and =C1^2

These functions have graphs that are curved (nonlinear), but have no breaks (smooth)

The Evolutionary method is used for non-smooth functions. These are functions whose graph is discontinuous at any point.

The use of the GRG Nonlinear and Evolutionary methods would require much more discussion than time permits in this article. We will therefore work exclusively with the Simplex LP method here.

Constraints

Constraints must be added separately. To add a new constraint, just click the Add button and select the cell that will have the constraints applied to it. You then select the type of constraint, for example, the constrained cell must be less than a certain number. Finally you specify the constraining value, for example, the number that the constrained variable must be less than.

When setting constraints, it is important to remember which variables cannot take a negative number and then specify this as a constraint. In this case, the Amount Spent for any of the pay-per-click networks cannot take a negative value.

The constraint highlighted in blue ( $C$4:$F$4 Greater Than or Equal To 0) illustrates this type of constraint in this example:
Click On Image To See   Larger Version



Close-Up of Constraints Input Into Solver

Click On Image To See Larger Version

Solve

You are now ready to hit the Solve button and get the final result. Solver changes the Decision Variables to optimize the Objective Cell. When Solver changes the Decision Variables, all other variables which depend upon these Decision Variables will be recalculated. By definition, the Objective Cell must be a variable that is derived from the Decision Variables. The end result of running the Solver are as follows:


Click On Image To See Larger Version


Final, Optimized Result

We can see that the Excel Solver calculated the maximum possible number of conversions to be 392. Solver did this by setting the Decision Variables as follows:

Amount Spent – Google AdWords Search: $500
Amount Spent – Google AdWords Content: $500
Amount Spent – Facebook Paid Ads: $300
Amount Spent – LinkedIn Paid Ads: $500


Constraints Satisfied

The constraints on the upper limits of each Amount Spent, Total Amount Spent, and non-negative constraint for each Amount Spent have all been satisfied.


Available Reports

There are 3 available reports that can be attached to the Solver’s result. These 3 reports are Answer, Sensitivity, and Limits as seen in the Solver Completion dialogue box below:


Click On Image To See Larger Version


We will briefly evaluate only a part of 1 of those reports in this article: the Sensitivity Report.



The Sensitivity Report

Part of the Sensitivity Report is shown below:


Click On Image To See Larger Version


The Objective Function column shows the coefficients for each Amount Spent to produce the Total Number of Conversions. Since this is a first-order equation, the result looks like this:

0.2 * (Amount Spent on Google AdWords Search) +
0.2857143 * (Amount Spent on Google AdWords Content) +
0.1066667 * (Amount Spent on Facebook Paid Ads) +
0.2333333 * (Amount Spent on LinkedIn Paid Ads) =


= Total Number of Conversions


We can see from the Objective Coefficients that an amount spent on the Google AdWords Content Network would have the greatest effect on the Total Number of Conversions.


Summary


The Excel Solver at its basic level is a simple tool to use to determine the optimal allocation of your available resources.


If you would like to create a link to this blog article, here is the link to copy for your convenience:

Using Solver To Optimize Your Marketing Budget


Excel Model Building - Experts vs. Non-Experts
Please post any comments you have on this article. Your opinion is highly valued!




If You Like This, Then Share It...
Excel Model Building - Experts vs. Non-Experts Excel Model Building - Experts vs. Non-Experts Excel Model Building - Experts vs. Non-Experts Excel Model Building - Experts vs. Non-Experts Excel Model Building - Experts vs. Non-Experts Excel Model Building - Experts vs. Non-Experts Excel Model Building - Experts vs. Non-Experts

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

27 comments:

  1. real i don't understand , the number of click since, number of click is equal to amount spent divided by pay per click....
    but why are you multiplying payper click into amount allocation.... i think there is some mistake in explanation

    ReplyDelete
    Replies
    1. How can I settle my payday loan?
      Settling your payday loans is an easy process with a few simple steps. First, you need to list your payday loans and calculate the total amount you owe. Then, you should contact a good law firm to settle your payday loans legally. Next, stop sending payments to creditors and start saving money in a trust account.

      Delete
  2. Corporate finance management focuses on the efficient allocation of mega-convert.com resources, maximization of firm's value along with covering the financial risk management. It caters to resolve the financial problem of every type of organization irrespective of its business nature. Corporate financier plays an important role in maximizing the profit and growth of the company and also optimizes the wealth of the stakeholders.

    ReplyDelete
  3. Deciding to start a business can be one of the most exhilarating decisions you make in your life. We are living in a world wherever everyone wants to make extra money and add to his income. Most people have achieved this by acquiring great business ideas. When one starts up a company, he must be ready to meet competition. It is important to note that you would not need to become rich or popular to succeed in at yahoo but have to think smartly. But there are a lot of moving parts and many different elements to consider.

    ReplyDelete
  4. Payday Loans: No Credit Check No cash advance colorado springs - Trying to decide whether or not to get a payday loan? This article may help you decide.

    ReplyDelete
  5. Getting an online entrepreneurship degree is very worth doing if you plan to create an offline business and you want for it to be a success. The truth is that online entrepreneurship doesn't need to be followed if you simply want to make money from the comfort of your own home. Most people think that all they need to do is follow a simple guide, and they'll be making money online in no time. The truth is that getting an online entrepreneurship degree is very beneficial for your future offline business owners, but having an online business is great How to patent an idea you don't need to spend so much on your education when you learn how to make money online.

    ReplyDelete
  6. It is high time now that marketers and especially digital marketers should realize the full potential of digital marketing and bridge the gap between knowledge of Dubai Real Estate and marketing approach which would immediately provide better results. The second step includes researching on the appropriate target audience for a specific jobs for stay at home moms and justifying the time spent on this research which would discriminate quality versus quantity at large.

    ReplyDelete
  7. That interest/finance charge typically is somewhere between 15% and 20%, depending on the lender, but could be higher. State laws regulate the maximum interest a payday lender may charge.

    ReplyDelete
  8. เว็บไซต์คาสิโนออนไลน์ที่ได้คุณภาพอับดับ 1 ของประเทศ
    เป็นเว็บไซต์การพนันออนไลน์ที่มีคนมา สมัคร Gclub Royal1688
    และยังมีหวยให้คุณได้เล่น สมัครหวยออนไลน์ ได้เลย
    สมัครสมาชิกที่นี่ >>> Gclub Royal1688
    ร่วมลงทุนสมัครเอเย่นคาสิโนกับทีมงานของเราได้เลย

    ReplyDelete
  9. โปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
    เพียงแค่คุณสมัคร Gclub กับทางทีมงานของเราเพียงเท่านั้น
    ร่วมมาเป็นส่วนหนึ่งกับเว็บไซต์คาสิโนออนไลน์ของเราได้เลยค่ะ
    สมัครสล็อตออนไลน์ >>> goldenslot
    สนใจร่วมลงทุนกับเรา สมัครเอเย่น Gclub คลิ๊กได้เลย

    ReplyDelete
  10. An online business isn't for everyone. It takes hard work and dedication to make a success of it. But the benefits far outweigh the drawbacks. Working from anywhere with a laptop and never having to work for anyone else again are two of my most compelling reasons to keep working hard at my home based business. best weekend jobs

    ReplyDelete
  11. Ready to spotsilver.net your new adventure in business? Here's a starting point to get you on your way.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Buying a business does not have to be a complicated endeavor when the proper process and methodology is followed. In this article, we outline eleven specific steps that should be adhered to when buying a business and bank financing is planned to be utilized. vladimir vrbaski republika

    ReplyDelete
  14. 2014 is shaping up to be a banner year for baby boomer business owners who want to sell their businesses and retire. What are some of the factors that are coming together to make 2014 the "year of the seller?" If you are a baby boomer business owner who is thinking about exiting https://Silverpriceperounce.net business learn why you might want to do so this year.

    ReplyDelete
  15. Having the potential to turn the sails in favour of neophytes and bring down incumbents, Digital Disruption is reshaping markets with the speed and force that has no match in history! Survivors Click here those businesses that are pro-actively adopting digital, but in order to thrive, they must also innovate new business models to keep pace with the digital age!

    ReplyDelete
  16. There are many home improvements you can do to boost your property value. Though not every homeowner agrees with the value of home improvements, most agree that you will get a better price for your home, in the event of a sale, if you make a few changes. Proforma Invoice Vs Invoice

    ReplyDelete
  17. The world is full of businesses but do you know how important they are? You know there are very big ones like Apple, BP and Amazon and very small ones like your local shop or window-cleaner. Grandad explains how all these businesses contribute to our daily life as importantly as the air we breathe and the food we eat. Without businesses he believes we would still be living in caves or mud huts, we would spend all our time searching for food and water. There would be no schools, no doctors, no police, no government,no cars, no television, nearly nothing, This is one of a series of articles aimed by Grandad at grandchildren everywhere. Grandad has learned many things over his lifetime in business. These articles can give you a flying start in life. Cash app help

    ReplyDelete
  18. The dream of business ownership is alive and well these days, especially in Colorado. As of 2015, the state is home to nearly 600,000 small businesses which employ almost 1,000,000 people! Every day you hear about a new start up being launched or a company taking its headquarters to Colorado. blue sky loans is good! So for today's discussion, we thought it would be important to address the benefits of buying an established business over starting a business

    ReplyDelete
  19. convert money online Loans: No Credit Check No Fuss Loans - Trying to decide whether or not to get a payday loan? This article may help you decide.

    ReplyDelete
  20. Having the potential to turn the sails in favour of neophytes and bring down incumbents, Digital Disruption is reshaping markets with the speed and force that has no match in history! Survivors are those businesses that are pro-actively adopting digital, but https://autocad.buycheapcad.com/autocad-electrical.html order to thrive, they must also innovate new business models to keep pace with the digital age!

    ReplyDelete
  21. This internet site is my aspiration, very excellent design and Perfect articles. gujarati garba dance video free download

    ReplyDelete
  22. The dream of business ownership is alive and well these days, especially in Colorado. As of 2015, the state is home to nearly 600,000 small businesses which employ almost 1,000,000 people! Every day you hear about a new start up being launched or a company taking its headquarters to Colorado. Business is good! So for today's discussion, free cash app money thought it would be important to address the benefits of buying an established business over starting a business

    ReplyDelete
  23. General information on putting a business plan together, groups that help polish rough draft plans cod mw key codes funding sources to consider once your plan is done. This includes marketing, legal basics, expenditure lists, etc.

    ReplyDelete
  24. Technically flash games are interactive games created using the Adobe Flash software. Macromedia originally owned and developed the Flash software for use in animation programming and was being used to create movies and TV shows and complex business presentations. Soon after, Flash was picked up by programmers to create web-based interactive games. discord nitro hack

    ReplyDelete
  25. Google has launched a new portal catering to smaller, local businesses, designed to streamline the management of your Google presence. From this new single interface, you can now control your Google Search presence, Google+ and Google Maps, updating your business information across multiple platforms simultaneously. Minecraft pe free the obvious time savings, Google's My Business portal holds more promising potential for SMBs.

    ReplyDelete