John Pinot is the marketing manager who is heading up the magazine promotion for the TZAdvantage tennis racquets. John already worked with a graphic artist and copy editor to produce the full-page color ad for the campaign. John will use the same ad in the five magazines that he has selected as representing the primary target audience. John prepared a worksheet that includes each magazines circulation (the number of distributed copies) and ad cost per issue (the cost of placing one full-page ad in one issue of the magazine). Johns worksheet also indicates how many issues each magazine produces yearly: monthly magazines produce 12 issues and bimonthly magazines produce six issues. John asks you to determine the best way to place ads in each magazine to achieve his primary goal of reaching at least 10 million readers in one year while minimizing the total cost. You need to calculate the total audience reached for each magazine (circulation multiplied by the number of issues in which the ads are placed). You also need to find the total cost for running the specified number of ads in each magazine (the ad cost per issue multiplied by the number of issues in which the ad appears). Johns final requirement is that you must place the ad in at least two issues of each magazine during the promotion. Complete the following: 1. Open the Magazine.xlsx workbook from the Chapter 9 folder, and then save the file as 9-Ex1-MagazineAds-YourName.xlsx. 2. Insert the appropriate formulas in cells F4:F8 to calculate the audience (circulation multiplied by the ad placement) for each magazine. 3. Insert the appropriate formulas in cells G4:G8 to calculate the total cost (ad cost per issue multiplied by the ad placement) for running the specified number of ads in each magazine. 4. Insert formulas in cells E9, F9, and G9 to calculate the total number of issues in which the ad will appear (ad placement), the total audience, and the total cost of the magazine ad campaign. 5. In the constraints table, specify the constraints that John has provided and any other necessary constraints. After determining the objective cell, variable cells, and constraints, use Solver to specify your inputs. 6. Run Solver to calculate your solution and then evaluate your solution. If necessary, add, change, or delete constraints, and then rerun Solver to produce a feasible solution. 7. Save your solution as a scenario named Minimize Cost, and then produce an answer report. 8. John just gave you some new information to include in your solution. He has replaced Magazine 3 with a bimonthly magazine that has a circulation of 235,000 and the ads cost $20,000 per issue. He wants you to recalculate the promotion with this calculate a new solution, save your solution as a scenario named Reduced Issues, and then produce an answer report. 9. John wants the gross audience for the entire promotion to be more than 14 million readers. Without changing any of the constraints in your Solver model, explain why the magazine promotion can or cannot support a gross audience of more than 14 million readers. If you determine that the promotion cannot support an audience of this size, which constraint(s) impedes or binds the goal of increasing the audience? Explain the problem. 10. Save and close the 9-Ex1-MagazineAds-YourName.xlsx workbook.