Problem 1: An energy-drink company must decide how to divide its spending between two forms of media: internet or magazine ads. Each internet ad costs $120,000 and will reach 10,000 people, 5,000 of whom are in their prime demographic age group of 15 - 25. A magazine ad costs $40,000 and reaches 5,000 viewers, 1,000 of whom are in the 15 - 25 age group. In addition, the company plans to hold a contest to promote its newest flavor. A requirement for entry into the contest is to enclose a coded label from the new drink flavor. The company believes the magazine ad will be more effective in generating trial purchases and entries. Each magazine spot is expected to produce 500 entries, while each internet spot is expected to generate 250 entries. Finally, the company's goal is to reach at least 600,000 total people and at least 150,000 people in the 15 - 25 age group, while also generating 30,000 or more contest entries. As a company employee in the marketing department, you have formulated the following linear programming model to identify the least cost combination of internet and magazine ads that will meet these requirements. The model is presented below in tableau form. The Excel answer and sensitivity reports for this problem are on the next page. Answer the 10 questions on the following page, using the Excel output for the advertising problem found on page 3. For each question, write your response in the blank on the right side of the page. Give a numeric answer to questions 1 through 7. (10 Points Each) 1. How much will the company spend for advertising using the least cost media combination? 2. How many internet ads will the company buy using the least cost advertising strategy? 3. How many magazine ads will the company buy using the least cost advertising strategy? 4. What is the marginal cost to the company of reaching another person in the 15-25 age group? 5. How much would the company's advertising cost increase if the minimum number of contest entries were increased by 50%-i.e., from 30,000 to 45,000 ? 6. What would the least cost advertising budget be if the minimum number of people in the 1525 age group were increased by 10% - i.e., from 150,000 to 165,000 ? 7. How much could the company reduce it advertising costs by if it reduced the minimum number of people it wanted to reach by 10%-i.e., from 600,000 to 540,000 ? Answer YES or NO to questions 8 through 10. (10 points each) 8. Will the least cost combination of advertising media change if the price for an internet ad falls to $100,000 ? 9. Will the least cost combination of advertising media change if the price for a magazine ad increases to $65,000 ? 10. Will the least cost combination of advertising media change if the minimum number of contest entries were increased to from 30,000 to 60,000 ? Microsoft Excel 16.0 Answer Report Worksheet: [APEC3002 EX 07 Work.xIsx]Sheet1 Report Created: 8/19/2022 5:40:46 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.015 Seconds. Iterations: 4 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 , Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1\%, Assume NonNegative Chinetiun rall (nainl Variahle cells Conetrainte Constraints \begin{tabular}{r|ccrrrrr} 13 & & Final & Shadow & \multicolumn{2}{c}{ Constraint } & Allowable Allowable \\ 14 & Cell & Name & Value & Price & R.H. Side & \multicolumn{1}{c}{ Increase } & Decrease \\ 15 & \$B\$7 Total People: & 600000 & 5.33333333 & 600000 & 150000 & 150000 \\ 16 & $B$8 & 1525 Age Group: & 150000 & 13.33333333 & 150000 & 90000 & 30000 \\ 17 & \$B\$9 Contest Entries: & 52500 & 0 & 30000 & 22500 & 1E+30 \\ \hline & & & & & & \end{tabular} Problem 1: An energy-drink company must decide how to divide its spending between two forms of media: internet or magazine ads. Each internet ad costs $120,000 and will reach 10,000 people, 5,000 of whom are in their prime demographic age group of 15 - 25. A magazine ad costs $40,000 and reaches 5,000 viewers, 1,000 of whom are in the 15 - 25 age group. In addition, the company plans to hold a contest to promote its newest flavor. A requirement for entry into the contest is to enclose a coded label from the new drink flavor. The company believes the magazine ad will be more effective in generating trial purchases and entries. Each magazine spot is expected to produce 500 entries, while each internet spot is expected to generate 250 entries. Finally, the company's goal is to reach at least 600,000 total people and at least 150,000 people in the 15 - 25 age group, while also generating 30,000 or more contest entries. As a company employee in the marketing department, you have formulated the following linear programming model to identify the least cost combination of internet and magazine ads that will meet these requirements. The model is presented below in tableau form. The Excel answer and sensitivity reports for this problem are on the next page. Answer the 10 questions on the following page, using the Excel output for the advertising problem found on page 3. For each question, write your response in the blank on the right side of the page. Give a numeric answer to questions 1 through 7. (10 Points Each) 1. How much will the company spend for advertising using the least cost media combination? 2. How many internet ads will the company buy using the least cost advertising strategy? 3. How many magazine ads will the company buy using the least cost advertising strategy? 4. What is the marginal cost to the company of reaching another person in the 15-25 age group? 5. How much would the company's advertising cost increase if the minimum number of contest entries were increased by 50%-i.e., from 30,000 to 45,000 ? 6. What would the least cost advertising budget be if the minimum number of people in the 1525 age group were increased by 10% - i.e., from 150,000 to 165,000 ? 7. How much could the company reduce it advertising costs by if it reduced the minimum number of people it wanted to reach by 10%-i.e., from 600,000 to 540,000 ? Answer YES or NO to questions 8 through 10. (10 points each) 8. Will the least cost combination of advertising media change if the price for an internet ad falls to $100,000 ? 9. Will the least cost combination of advertising media change if the price for a magazine ad increases to $65,000 ? 10. Will the least cost combination of advertising media change if the minimum number of contest entries were increased to from 30,000 to 60,000 ? Microsoft Excel 16.0 Answer Report Worksheet: [APEC3002 EX 07 Work.xIsx]Sheet1 Report Created: 8/19/2022 5:40:46 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.015 Seconds. Iterations: 4 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 , Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1\%, Assume NonNegative Chinetiun rall (nainl Variahle cells Conetrainte Constraints \begin{tabular}{r|ccrrrrr} 13 & & Final & Shadow & \multicolumn{2}{c}{ Constraint } & Allowable Allowable \\ 14 & Cell & Name & Value & Price & R.H. Side & \multicolumn{1}{c}{ Increase } & Decrease \\ 15 & \$B\$7 Total People: & 600000 & 5.33333333 & 600000 & 150000 & 150000 \\ 16 & $B$8 & 1525 Age Group: & 150000 & 13.33333333 & 150000 & 90000 & 30000 \\ 17 & \$B\$9 Contest Entries: & 52500 & 0 & 30000 & 22500 & 1E+30 \\ \hline & & & & & & \end{tabular}