Exercise Ch9-2: Level 2 Radio Advertisement

The marketing group has allocated a $225,000 budget for the radio promotion that it has planned as part of the product launch for TZ-Advantage. The marketing group has provided you with a worksheet that shows the cost estimates for producing and placing radio ads with the three major radio networks that the marketing manager plans to use.

The one-time cost of each ad appears in the Production Cost column. The AQH Persons column identifies the average number of people listening to each network during any 15 minutes. The Cost per Ad column indicates the cost of running an ad once on each network. The Number of Ads column identifies the number of times that the ad will run on the network, and the Total Cost column shows the cost of running the specified number of ads. The Gross Impressions column is the product of multiplying the AQH

Persons value by the Number of Ads value and indicates the potential reach of the ad. Gross impressions represent the number of times an ad is heard during a promotion. This shouldnt be confused with how many people have heard it. Your goal is to maximize the number of gross impressions by varying the number of ads purchased from each radio network. The number of ads you can purchase from each network is limited by the following requirements:

Do not exceed the $225,000 budget

Achieve at least 15,000,000 gross impressions

Purchase at least 50 ads from each network.

Purchase no more than 100 ads on each network

Complete the following:

1. Open the Radio.xlsx workbook from the Chapter 9 folder, and then save the file as 9-Ex2-RadioAds-YourName.xlsx.

2. Enter the appropriate formulas in the Radio worksheet to calculate the gross impressions for each radio network.

3. Enter the appropriate formulas to calculate the total cost for running the specified number of ads on each network.

4. Insert formulas in cells E7, F7, and G7 to calculate the total number of ads in the campaign, the total gross impressions, and the total cost for the radio campaign.

5. Insert formulas in cells H4:H6 to calculate the percentage of ads placed on each network.

6. Create a constraints table that identifies the constraints that you must use in your solution. After determining the objective cell, the variable cells, and the constraints, use the Solver Parameters dialog box to specify your inputs.

7. Use Solver to calculate your solution and then evaluate your solution. If necessary, add, change, or delete constraints and rerun Solver to produce a feasible solution. Balance the need to stay at or under the budget with the desire to reach a level of at least 15,000,000 gross impressions.

8. Produce an answer report for your solution

9. The marketing manager just gave you some new information about the radio promotion. The advertising managers from two of the three radio networks have limited the number of units (advertising spots) available to no more than 40 ads per client for Radio Network 2 and no more than 50 ads per client for Radio Network 3 during the time in which TheZone wants to run its promotion. The manager raised the limit on Radio Network 1 ads to no more than 125. Save your first Solver model as a scenario named 50 Ads per Network, and then change your Solver model and find a new solution that limits the number of ads for the second and third networks accordingly. Save the scenario as Limited Ads and keep your Solver solution.

10. Run the Solver Answer Report and examine it. Which constraint(s) are binding? Which ones are policy constraints? Explain your findings, and then recommend some changes that might create a better solution in a text box inserted into the answer report worksheet.

11. Add your name and date at the end of the exercise

12. Save and close the 9-Ex2-RadioAds-YourName.xlsx workbook

Note For your help the initial Solver Model is: a) Objective Cells: Maximize Gross Impressions ($F$7) b) Variable Cells: Number of Ads ($E$4:$E$6) c) Constrain: $225,000.00 integer int Total Cost (G7) Number of Ads (E4:E6) Min Number of Ads (E4:E6) Max Number of Ads (E4:16) >= 15,000,000