Question

Claire already has employed a leading advertising firm, J & J, to help design a nationwide promotional campaign that will achieve the largest possible exposure for Crunchy Start--a new breakfast serial. Super Grain will pay this firm a fee based on services performed (not to exceed $1 million of the planning budget) and has allocated an additional $4 million for the advertising budget. J & J has identified the three most effective advertising media for this product: Medium 1: Television commercials on Saturday morning programs for children. • Medium 2: Advertisements in food and family-oriented magazines. • Medium 3: Advertisements in Sunday supplements of major newspapers, Cost TV Ad Magazine Ad Newspaper Ad Ad budget $300,000 $150,000 $100,000 Planning $90,000 $30,000 $40,000 budget Expected number of viewership per 1,300,000 600,000 500,000 ad There is a restriction on TV ads: there are only five spots available for running different ads (one ad per spot) on children television programs Saturday morning (medium 1) during the time of the promotional campaign. (The other two media have an ample number of spots available.)

Answer 1

Data:

	TV Ad	Magazine Ad	Newspaper Ad	Limits
Ad Budget	300000	150000	100000	4000000
Planning Budget	90000	30000	40000	1000000
Expected Number of Viewership per ad	1300000	600000	500000

Linear Programming Formulation:

This is a linear programming problem. The LP needs to be formulated as follows.

1. Decision Variables:

The first step is to assign decision variables. Let T, M and N be the number of ads placed in TV, Magazine and Newspaper respecitvely.

2. Objective Function:

Objective is to maximize the exposure. In other words maximize the expected number of viewership.

Zmax = 1300000T+600000M+500000N

3. Constraints:

Ad Budget Constraint:

300000T+150000M+100000N ≤ 4000000

Planning Budgets Constrait:

90000T+30000M+40000N ≤ 1000000

Number of TV ads:

T ≤ 5

T,M,N ≥ 0

4. Solution:

We will use excel solver plug in to solve this LP:

Here is the spreadsheet model and the formulas used in excel for the reference.

A I Decision Variables TV Ad Number of ads J K L Magazine Newspaper Ad B C TV Ad Magazine Newspaper Ad Limits 300000 150000 100000 4000000 90000 30000 40000 1000000 2 Ad Budget 3 Planning Budget Expected Number of Viewership per 4 ad 1300000 600000 500000 Objective Constraints Ad Budget Planning Budget No. of TV Ads 4000000 1000000 Non negativity no. of ads>=

Jк Magazine Ad Newspaper Ad B TV Ad 300000 90000 C D E Magazine Ad Newspaper Ad Limits 150000 100000 4000000 30000 40000 1000000 Decision Variables TV Ad Number of ads 2 Ad Budget 3 Planning Budget Expected Number of Viewership per ad 1300000 600000 500000 Objective =SUMPRODUCT(B4:D4,12:K2) Constraints Ad Budget Planning Budget No. of TV Ads =SUMPRODUCT(12:K2,B2:D2) =SUMPRODUCT(B3:D3,12:K2) | =12 Non negativity no. of ads

Entering parameters in solver:

In the excel sheet, open solver from the data tab. Enter the parameters as shown in the screenshot:

Solver Parameters Decision Variables TV Ad Number of ads Magazine Ad Newspaper Ad - -------- ---- Set Objective: SISS To: Max Min O Value of: Objective By Changing Variable Cells: SI$2:$K$2 Subject to the Constraints: SIS12 >= $K$12 SI$2:$K$2 >= 0 SI$9:$I$11 <= $K$9:$K$11 Constraints Ad Budget Planning Budget No. of TV Ads Add 4000000 1000000 Change 0<= Delete Non negativity no. of ads > Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Simplex LP Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

After all the parameters are added, click on the solve. It will give you a dialogue box like the one mentioned in the below screenshot. Select sensitivity and select ok.

Solver Results XN Ε TV Ad Magazine Ad Newspaper Ad Limits idget 300000 150000 100000 4000000 ling Budget 90000 30000 40000 1000000 cted Number ewership per 1300000 600000 500000 Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... Solver found a solution. All Constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution.

There will be one sensitivity report generated and the solution will be visible as in the below screenshot.

F G H I Decision Variables TV Ad Number of ads Magazine Ad Newspaper Ad 10 20 BC E TV Ad Magazine Ad Newspaper Ad Limits 2 Ad Budget 300000 150000 100000 4000000 3 Planning Budget 90000 30000 40000 1000000 Expected Number of Viewership per 1300000 600000 500000 Objective 17000000 Constraints Ad Budget Planning Budget No. of TV Ads 4000000 <= 1000000 <= 4000000 1000000 Non negativity no. of ads>= Sensitivity Report 1 Sheet1 - Ready

Question 1: As J&J is an advertising firm responsible for the media campaigning, it will determine the production values of Tv, Magazine and Newspaper ads

Question 2: The media for the adevrtising are dependent up on the Ad Budget and Planning Budget available.

Question 3: There are 3 constraints (Ad Budget, Planning Budget and Number of Tv Ads).

Question 4: Objective is to maximum exposure which also means maximum viewership.

Question 5: The value of the objective at optimal is 17000000 (shown in the above screenshot). This is the maximum viewership.

Question 6: Coefficient of TV ad (or the decision varaible T) is 1300000. (The value with which T is multiplied i the objective function equation)