HYour answers to all five questions must be in the Answers worksheet of the Ad Bids workbook that you downloaded from Blackboard. In addition, your Excel model coming up with the answers for each of the five questions should be on
Simon Business School University of Rochester
GBA 220, Business Information Systems and Analytics
Fall 2020
Team Spreadsheet Modelling Assignment 1: Selecting Online Ads1
Due Wednesday, September 23rd, at 11:59pm
Your answers to all five questions must be in the Answers worksheet of the Ad Bids workbook that you downloaded from Blackboard. In addition, your Excel model coming up with the answers for each of the five questions should be on the appropriate worksheet in the Ad Bids workbook, i.e., Question 1, Question 2 Its probably simplest to finish Question 1 and then copy it to Question 2 and then solve Question 2, and so on Another way is to solve them all in one worksheet and then once you have a working model for all questions, solve Question 2 and copy it to Question 2, solve Question 3 and copy it to Question 3, and then finally restore the Question 1 worksheet to the answer to Question 1. A good solution will allow you to answer the different questions with very little changes to the model. Also note that you are not to use Solver in this assignment (this assignment is to develop your spreadsheet modeling skills, not your use of Solver (if you are not familiar with Solver dont worry about it)). When Google and other search engines run a search, not only do they search billions of web pages in a fraction of a second, but they also run a real-time auction to choose what ads to place along the search results (Google generates its revenue when users click on these ads). For our purposes, we will assume that Google is paid just for showing ads alongside search results. Ads go on the right side of the search results page and that there are three types of ads: Small (S), Medium (M), and Large (L), and the heights of those ads are 90, 150, and 220, respectively. All ads are 180 pixels wide. In addition, advertisers can request their ads have a border around them. If they do, this border adds 10 pixels to the left, right, top, and bottom of the ad. You can assume that at least the right 200 pixels of the screen is dedicated to ads, so you dont need to concern yourself with the horizontal placement of ads. Initially, we will select ads assuming a typical screen size of 1024 wide by 768 high. Ads that receive full placement can be placed in any order on the right side of the screen from top to bottom. In addition, advertisers have agreed to pay for whatever portion of their ad is partially shown at the bottom of the screen (the rest of their ad becomes visible if the user scrolls, but the advertiser doesnt pay for the part below the bottom of the first screen). For example, if there are 8 ads each of which have a height of 100, 7 ads can be shown in full and 68% of the last ad can be shown at the bottom of the screen and that advertiser would pay 68% of their bid to have 68% of their ad shown. Note that if the advertiser has requested a border, that effectively changes the height of their ad and this is taken into account in the prorating. Remember the border goes on all four sides (how much is the total height of an ad increased if it has a border?). For each ad, you have its Ad #, Size (S, M, L), whether it has a Border (1 is yes and 0 is no), and the Bid, which is the amount the advertiser is willing to pay to have their ad shown. The total height of each ad depends on its size and whether or not it has a border. Essentially for each ad, you need to look up its height, given its size. A good way to do this is the VLOOKUP command in Excel. An example has been provided in cell F6 of the spreadsheet with the ad data on the Question 1 worksheet of the Ad Bids workbook (looking up the height of an ad whose Size is in cell E6). VLOOKUP has 4 parameters: the value youre looking up, the table youre looking it up in (usually the table should be specified in absolute references), the column number of the table that has the value to be returned, and
1 Copyright ?2018-20 by Roy Jones
whether or not you want an approximate match (I always choose FALSE so that I get an exact match). See the example in cell F7 of the data file. Because Border has been coded as 0 = No and 1 = Yes, you can just multiple this by the border height (and an appropriate factor) to calculate the total height of each ad taking into account whether or not it has a border. For each question, try to come up with a general solution, i.e., have the spreadsheet solve as much of the problem as possible in a way that is independent of the data. How many cells in your spreadsheet would have to be changed if ad heights, border size, or screen size changed? There are only 30 bids to make your life a little easier (less scrolling). You should come up with a general solution that would work just as well with 300 requests as 30. It is possible to significantly automate the process, i.e., take any set of bids, fill in some intermediate variables, maybe do a sort or two, and have the spreadsheet show which bids are accepted and the profit. Remember that for the purposes of this assignments, we can accept a fraction of an ad and when we do the bid amount is prorated based on how much of the prorated ad is shown, i.e., the percentage of its overall height, including border, thats shown on the screen without scrolling. If you take part of an ad, be sure and specify what percentage of the ad youre taking. Again, you should not use Solver on this assignment (and doing so will put you at a considerable disadvantage on the first exam).
1. The ad bid data in Appendix A is in the Question 1 worksheet of the Ad Bids workbook includes a set
of representative bids for one search, sorted by their order of arrival. If the ads are selected in the order they received, i.e., Ad # from smallest (1) to highest (30), which ads would be selected? What is the profit?
Answer: Ads 1 5, and 19% of 6, and the profit is 140.24. I would try hard to come up with this answer, but if you cant, its ok to continue with the rest of the assignment.
2. Suppose that you instead choose ads based on the bid amount, from highest to lowest. Which ads
should you choose? What is your profit? 3. Which bids should you choose to maximize profits? What is the profit?
4. We have assumed that the typical screen is 1024 wide by 768 high. However, screen sizes have
increased over time (thanks in part to Moores Law). If we now assume the typical screen is 1152 wide by 864 high, what bids should we choose now to maximize profits? What is the profit? Did you need to resort the bids? Why or why not?
5. Continuing to assume the screen size is now 1152 x 864, another potential way to increase profits is
to shrink the border from 10 pixels on each of the four sides of the ad to just 5 pixels on each side. Which bids should you choose to maximize profits? What is the profit? Did you need to resort the bids? Why or why not?
Be sure and answer the extra questions at the end of questions 4 and 5. Again, your answers for each question need to be entered on the Answers worksheet and those answers should match what is reflected in the worksheet for each question.
Appendix A
Ad # Size Border Bid
1 M 1 $21.00
2 S 0 $1.00
3 M 0 $30.00
4 L 1 $32.00
5 S 0 $54.00
6 M 0 $12.00
7 L 1 $36.00
8 L 1 $51.00
9 M 1 $12.00
10 L 1 $46.00
11 S 1 $32.00
12 M 0 $15.00
13 M 0 $21.00
14 M 0 $39.00
15 S 1 $18.00
16 L 0 $9.00
17 M 0 $14.00
18 L 0 $60.00
19 S 1 $40.00
20 M 0 $27.00
21 L 1 $15.00
22 L 0 $5.00
23 M 0 $35.00
24 M 0 $45.00
25 M 0 $17.00
26 M 1 $55.00
27 M 1 $78.00
28 L 0 $49.00
29 M 1 $9.00
30 L 0 $93.00
