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
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
Answers
Please show two digits to the right of the decimel point for your profit answers.
Your answers should be reflected on the worksheets for the individual questions.
Question 1
Profit 140.24
Which jobs? 1 – 5 and 19% of 6.
Question 2
Profit
Which jobs?
Question 3
Profit
Which jobs?
Question 4
Profit
Which jobs?
Did you need to resort?
Why or why not?
Question 5
Profit
Which jobs?
Did you need to resort?
Why or why not?
Question 1
Ad Size Height (pixels)
S 90
M 150
L 220
VLOOKUP Example in cell F6 M 150
looks up height of ad size in cell E6
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
Question 2
Question 3
Q
Question 4
Question 5 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
