Individual Assignment 2 - Task 3

This is Task 3:

Here is a decision scenario that should be supported with optimisation analysis.

The Denver advertising agency promoting the new Breem dishwashing detergent wants to get the best exposure possible for the product within the $100,000 advertising budget ceiling placed on it.

To do so, the agency needs to decide how much of the budget to spend on each of its two most effective media:

1. Television spots during the afternoon hours, and

2. Large ads in the city's Sunday newspaper.

Each television spot costs $3,000, while each Sunday newspaper ad costs $1,250. The expected   exposure, based on industry ratings, is 35,000 viewers for each TV commercial and 20,000 readers for each newspaper ad.

The agency director, Deborah Kellogg, knows from experience that it is important to use both media in order to reach the broadest spectrum of potential Breem customers. She decides that at least 5,  but no more than 25 television spots should be ordered, and that at least 10 newspaper ads should be contracted. The number of ads purchased for both media must be non-negative integers.

How many times should each of the two media be used to obtain maximum exposure while staying within the budget and the frequencies specified by Kellogg?

Requirements for this task:

A. In a Word/.pdf document, state the Decision Variable(s), Objective, and Constraint(s) for this optimisation analysis.

B. In Excel, construct a deterministic base-case model to predict the resulting consumptions of the advertising budget and the number of potential customers reached if 10 units of Television spots and 10 units of Sunday Newspaper ads were purchased.

C. Using the base-case model from part B, on a separate worksheet, carry out the required            optimisation analysis to obtain a recommendation about how many units of Television spots and Sunday Newspaper Ads should be purchased, in order to maximise total customers reached     while satisfying all relevant constraints mentioned in the scenario.