The answer written in Question 1 is given so that you can solve Question 2 ~ Question 5 correctly. But you still have to show the work to get the answer written in q1 as well as q2~q5. It's 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

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 don't 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 doesn't 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?).

Should not use Solver on this assignment

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 complete 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 you're looking up, the table you're 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 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 set 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, that's shown on the screen without scrolling. If you take part of an ad, be sure and specify what percentage of the ad you're taking.

Should not use Solver on this assignment

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 can't, it's 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 Moore's 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.

K A D E F G H 1 B C Ad Size Height (pixels) S 90 N 150 220 VLOOKUP Example in cell F6 M 150 looks up height of ad size in cell E6 Ad # Size Border Bid Ht w/o Border 10 M 1 $21.00 S $1.00 12 M $30.00 L $32.00 S $54.00 M $12.00 L $36.00 $51.00 M $12.00 $46.00 $32.00 M $15.00 M $21.00 $39.00 TOHOO O HO O O I KI $18.00 $9.00 M $14.00 18 L $60.00 28 19 $40.00 29 20 M $27.00 30 21 $15.00 31 22 r $5.00 32 23 M $35.00 33 24 M $45.00 34 25 M $17.00 35 M $55.00 36 M $78.00 37 28 $49.00 38 $9.00 39 30 $96.00 40 41 42 43 44 Question 4 Question 5 + 4 Answers Question 1 Question 2 Question 3A B C D E F G H 1 J Ad Size Height (pixels) S 90 W N H M 150 L 220 VLOOKUP Example in cell F61 M =VLOOKUP (E6, E$ 2:F$4,2, FALSE) looks up height of ad size in cell EG Ad # Size Border Bid Ht w/o Border 10 M 1 $21.00 11 $1.00 12 M $30.00 13 $32.00 14 5 $54.00 15 M $12.00 16 L $36.00 L $51.00 M $12.00 $46.00 $32.00 $15.00 $21.00 M $39.00 $18.00 $9.00 $14.00 $60.00 28 $40.00 29 M $27.00 30 21 $15.00 31 22 $5.00 32 M $35.00 33 M $45.00 34 M $17.00 35 M $55.00 36 27 M $78.00 37 28 $49.00 38 29 M $9.00 39 30 $96.00 40 41 42 43 Answers Question 1 Question 2 Question 3 Question 4 Question 5 +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