The most satisfying and powerful thing you can do with Excel is to model a real-world problem in a way that helps you make a better decision. For a model to be effective, it needs to expose inputs in a way that makes sense, and generate results that are easy to interpret. In this article, we’ll use the seldom-seen Data Table to build a simple model that helps you visualize the profitability of a Google Adwords campaign.
Just like those word problems from your school days, the key is setting up the problem correctly.
A quick Adwords primer
Adwords is Google’s primary advertising service. For the purpose of this article, we’ll be discussing only Pay-per-click (PPC) advertising on Google’s search network. When people search for things on Google, they use certain keywords. For example, they might search for “best todo list manager” or “japanese survival knife”. With Adwords, advertisers can bid on specific keywords. If the bid is high enough, the ads are displayed on the search result page, along with the search results. If a user clicks on an ad, the advertiser pays Google the amount of the bid. If a user doesn’t click on the ad, the advertiser pays nothing.
Making money with Adwords
In order to make money by advertising with Adwords, an advertiser must “convert” enough users into customers to pay for the clicks that don’t result in a sale, and still make a profit. For example, if you, the advertiser have a product that costs $100, and you bid on a keyword that costs $1.00 per click, you need to make a sale at least every 100 clicks just to break even: 100 clicks @ $1.00 = $100 = 1 sale If you’re able to get more than one sale every 100 clicks, you’ll make money. If you get less than one sale per 100 clicks, you’ll lose money. If you have to pay $2.00 per click, you’ll need to make a sale at least every 50 clicks. As you can see, if you’re going to use Adwords PPC to advertise, it’s important to understand the break-even point - that is, the conditions under which you’ll start to make money. Note: To keep this article to the point, I am skipping a lot of potential complexity that could be part of Adwords. For example, you would normally bid on more than keyword at the same time, the actual cost per click varies due to competition, etc.
Modeling the problem
Based on the information above, there are 3 key drivers of profit: (1) the price of the product (2) the average cost per click (3) the percentage of clicks than can be converted to a sale We can think of these as inputs (variables) for our problem. Intuitively, we know that a lower cost per click will allow us to buy more clicks per sale, and a higher conversion rate means we’ll need to buy fewer clicks to get each sale. But what’s the best way to model the problem in Excel, so that we can see how the cost per click and conversion rate work together to determine profit? As usual, there is more than one way to skin the cat with Excel. However, the modest Data Table turns out to be a pretty good way to visualize the problem.
Setting things up
First things first, we need to provide an area on the worksheet meant for inputs. I like to group inputs together and labeled in one area of the worksheet. For this problem, we’ll have:
Now we need to add an Outputs area. This is an area on the worksheet where will run the main calculations with fixed inputs. Specifically, we’ll figure out:
With these formulas in place, we can actually figure out the break-even point manually, by fiddling around with the average cost per click and our estimated conversion rate. But we don’t want to do that manually. We want Excel to do the hard work for us! Enter the Data Table.
The Data Table
A Data Table is a dynamic table that shows the results of various input cells. Data Tables can handle either 1 or 2 inputs. In our case, a 2-input Data Table is a good fit, since we can cost per click as one input, and conversion rate as the other. To create the data table:
When you click OK, Excel will fill the table with Net Profit values that correspond to each pair of Average CPC and Conversion rate values. By using a currency number format with negative values displayed in red, you can easily see at a glance which combinations are profitable. Better yet, you can continue to adjust any of the main inputs, and all values plotted in the table will be updated dynamically.
Conclusion
Data Tables take a bit of work to set up, and they are limited to 2 inputs only, but they are a nice way to model a problem in a simple visual format. Is this worksheet helpful to you? Would you approach this problem differently? Let me know in the comments below. Credits: This model was inspired by an example in John Walkenbach’s excellent book, Excel 2010 Bible.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
