Background

The agency for Intel AP has provided to plan for getting ad-impressions across 3-ad types (FB marketplace ads, FB premium ads and FB mobile ads) across six countries in the Asia-Pacific region. The plan details for this plan are seen in the Original Plan tab of the excel file Intel AP Media Data Exercise.

The Intel AP team thinks that there is a need to update and optimize the agencys original plan. The first reason is that the agencys plan shows only the ad-spend of $95076 but does not include the agencys fee, which is 9% of the ad-spend. Intels total budget including the agencys fee is $95000. The second reason is that the plan included some assumptions about the typical CTRs for the different types of ads and did not account for the social impression multiplier or the social CTR. The social impression multiplier accounts for the additional social impressions generated through clicks on premium and mobile ads. The social CTR is an estimate of the clicks generated from the ads generated by social impressions. Intels team believed that based on previous data, they have better estimates of these parameters in different countries.

While updating the plan, Intels team wants to ensure that this update should not result in more than 125% of impressions in any country compared to the original plan. Further, the impressions should not fall below 75% of the impressions generated through each category of ad in each country. This introduces a set of constraints that must be included in the solver formulation. In addition, there is another constraint that no mobile ads impressions are to be generated in Korea.

Question(s)

1. Write down and explain the entire optimization problem describing the variables you choose, then write the objective in terms of the variables, and then write the constraints in terms of the variables. (6 points) Use solver to determine the total number of impressions per country in each category while revising the plan offered by the agency. Include your answer. Do you end up spending the entire budget of $95000? Can you explain why, or why not? (4 points)

INTEL ASIA-PACIFIC: THE CATCH & WIN CAMPAIGN Origial Plan Ad Budget Total S Minimum Media/Market Impressions of Original Plan Original Plan - Impressions 103,633 Original Plan Agency Fee $ 8,557 Original Plan Media Spend $ 75% Maximum Total Market Impressions % of Original Plan 58,650,000 Paid impressions only Original Plan - Clicks 95,076 125% 131,623 Indonesia Korea Pakistan Singapore Total 12.9 1.96% 41.1 2.39% Malaysia 21.4 1.82% 26.5 2.64% Philippines 32.4 1.81% 20.6 1.83% 17410 Social Impressions Multiplier Social CTR Revised Plan Marketplace Ads Budget Marketplace Ads CPM Marketplace Impressions - Paid Marketplace CTR Marketplace Estimated Clicks - Total $ $ 3,180$ 0.53 $ 6,000,000 0.09% 5.400 5,280$ 0.88 $ 6,000,000 0.08% 4.800 3,600 $ 0.60 $ 6,000,000 0.11% 6,600 570 $ 0.57 $ 1,000,000 0.12% 1,200 3,360 $ 0.56 $ 6,000,000 0.11% 6,600 1,420 $ 0.71 2,000,000 0.12% 2.400 27,000,000 27,000 $ 71,666 $ $ $ $ $ 16,650,000 Premium Ads Budget Premium Ads CPM Premium Impressions - Paid Premium CTR Premium Estimated Clicks - Paid Premium Impressions - Social Premium Clicks - Total 15,435$ 4.41 $ 3,500,000 0.43% 15,050 194,597 18,864 19,425 5.25 3,700,000 0.23% 8,658 355,584 17,156 11,914 $ 3.22 $ 3,700,000 0.32% 11,655 249,767 16,201 3,087 4.41 700,000 0.59% 4,158 110,312 7,070 14,24 3.85 3,700,000 0.48% 17,649 572,357 28,009 7,56 5.60 1,350,000 0.34% 4,617 95 249 6,360 61,787 93,660 1,200 6,000 $ $ 0.40 $ $ $ 15,000,000 Mobile Ads Budget Mobile Ads CPM Mobile Impressions - Paid Mobile CTR Mobile Estimated Clicks - Paid Mobile Impressions - Social Mobile Clicks - Total 1,200 0.40 3,000,000 0.05% 1,500 19,395 1880 0.05% 1,200 0.40 3,000,000 0.05% 1,500 32,145 2,085 0.40 S 3,000,000 0.05% 1,500 39,795 2.551 1,200 0.40 3,000,000 0.05% 1 500 48,645 2,380 1,200 0.40 3,000,000 0.05% 1,500 30,945 2,066 7,500 10,963 $ $ $ $ $ Total costs Total Impressions - Paid Total Impressions Estimated Total Clicks 19,815S 12,500,000 $ 12,713,992 26,144 24,705 9,700,000 10,055,584 21,956 16,714 $ 12,700,000 $ 12,981,912 24,886 4,857 $ 4,700,000 $ 4,850,107 10.821 18,805 $ 12,700,000 $ 13,321,002 36,989 10,180 6,350,000 6,476,194 10,826 95,076 58,650,000 60,398,790 131,623 6,000,000 3,700,000 Original Plan Marketplace Impressions Premium Impressions Mobile Impressions Total Paid Impressions Maximum Total Paid Impressions Revised Plan Minimums Marketplace Impressions Premium Impressions Mobile Impressions 6,000,000 3,500,000 3,000,000 12,500,000 15,625,000 6,000,000 3,700,000 3,000,000 12,700,000 15,875,000 1,000,000 700.000 3,000,000 4,700,000 5,875,000 6,000,000 3,700,000 3,000,000 12,700,000 15,875,000 2,000,000 1,350,000 3,000,000 6,350,000 7,937,500 27,000,000 16,650,000 15,000,000 58,650,000 9,700,000 12,125,000 4,500,000 2,625,000 2,250,000 4,500,000 2,775,000 4,500,000 2,775,000 2,250,000 750,000 525,000 2,250,000 4,500,000 2,775,000 2,250,000 1,500,000 1,012,500 2,250,000 20,250,000 12,487,500 11,250,000 INTEL ASIA-PACIFIC: THE CATCH & WIN CAMPAIGN Origial Plan Ad Budget Total S Minimum Media/Market Impressions of Original Plan Original Plan - Impressions 103,633 Original Plan Agency Fee $ 8,557 Original Plan Media Spend $ 75% Maximum Total Market Impressions % of Original Plan 58,650,000 Paid impressions only Original Plan - Clicks 95,076 125% 131,623 Indonesia Korea Pakistan Singapore Total 12.9 1.96% 41.1 2.39% Malaysia 21.4 1.82% 26.5 2.64% Philippines 32.4 1.81% 20.6 1.83% 17410 Social Impressions Multiplier Social CTR Revised Plan Marketplace Ads Budget Marketplace Ads CPM Marketplace Impressions - Paid Marketplace CTR Marketplace Estimated Clicks - Total $ $ 3,180$ 0.53 $ 6,000,000 0.09% 5.400 5,280$ 0.88 $ 6,000,000 0.08% 4.800 3,600 $ 0.60 $ 6,000,000 0.11% 6,600 570 $ 0.57 $ 1,000,000 0.12% 1,200 3,360 $ 0.56 $ 6,000,000 0.11% 6,600 1,420 $ 0.71 2,000,000 0.12% 2.400 27,000,000 27,000 $ 71,666 $ $ $ $ $ 16,650,000 Premium Ads Budget Premium Ads CPM Premium Impressions - Paid Premium CTR Premium Estimated Clicks - Paid Premium Impressions - Social Premium Clicks - Total 15,435$ 4.41 $ 3,500,000 0.43% 15,050 194,597 18,864 19,425 5.25 3,700,000 0.23% 8,658 355,584 17,156 11,914 $ 3.22 $ 3,700,000 0.32% 11,655 249,767 16,201 3,087 4.41 700,000 0.59% 4,158 110,312 7,070 14,24 3.85 3,700,000 0.48% 17,649 572,357 28,009 7,56 5.60 1,350,000 0.34% 4,617 95 249 6,360 61,787 93,660 1,200 6,000 $ $ 0.40 $ $ $ 15,000,000 Mobile Ads Budget Mobile Ads CPM Mobile Impressions - Paid Mobile CTR Mobile Estimated Clicks - Paid Mobile Impressions - Social Mobile Clicks - Total 1,200 0.40 3,000,000 0.05% 1,500 19,395 1880 0.05% 1,200 0.40 3,000,000 0.05% 1,500 32,145 2,085 0.40 S 3,000,000 0.05% 1,500 39,795 2.551 1,200 0.40 3,000,000 0.05% 1 500 48,645 2,380 1,200 0.40 3,000,000 0.05% 1,500 30,945 2,066 7,500 10,963 $ $ $ $ $ Total costs Total Impressions - Paid Total Impressions Estimated Total Clicks 19,815S 12,500,000 $ 12,713,992 26,144 24,705 9,700,000 10,055,584 21,956 16,714 $ 12,700,000 $ 12,981,912 24,886 4,857 $ 4,700,000 $ 4,850,107 10.821 18,805 $ 12,700,000 $ 13,321,002 36,989 10,180 6,350,000 6,476,194 10,826 95,076 58,650,000 60,398,790 131,623 6,000,000 3,700,000 Original Plan Marketplace Impressions Premium Impressions Mobile Impressions Total Paid Impressions Maximum Total Paid Impressions Revised Plan Minimums Marketplace Impressions Premium Impressions Mobile Impressions 6,000,000 3,500,000 3,000,000 12,500,000 15,625,000 6,000,000 3,700,000 3,000,000 12,700,000 15,875,000 1,000,000 700.000 3,000,000 4,700,000 5,875,000 6,000,000 3,700,000 3,000,000 12,700,000 15,875,000 2,000,000 1,350,000 3,000,000 6,350,000 7,937,500 27,000,000 16,650,000 15,000,000 58,650,000 9,700,000 12,125,000 4,500,000 2,625,000 2,250,000 4,500,000 2,775,000 4,500,000 2,775,000 2,250,000 750,000 525,000 2,250,000 4,500,000 2,775,000 2,250,000 1,500,000 1,012,500 2,250,000 20,250,000 12,487,500 11,250,000